Data Architect

Ancilar Inc.; India; Remote; INR 3,500,000 - 5,500,000; Full time

Job summary

Ancilar Inc. seeks a highly experienced Data Architect to design and implement an on‑premises SQL Server 2025–driven data warehouse with a Bronze–Silver–Gold data lakehouse approach. The role focuses on NetSuite integration, ETL/ELT pipelines, and enterprise Power BI reporting across a remote engagement.

You will own architectural standards, data quality, governance, and performance for high-volume analytics, while collaborating with global teams and ensuring secure, scalable data delivery.

Qualifications

• 12+ years in Data Architecture/Data Warehousing or related roles.
• Deep SQL Server knowledge and performance optimization skills.
• Hands-on NetSuite integration and ERP data modeling experience.
• Experience with ETL/ELT architectures and metadata-driven pipelines.
• Proficiency in Power BI, DAX, and enterprise reporting models.

Responsibilities

• Design on‑premises SQL-first Medallion Architecture (Bronze–Silver–Gold).
• Lead NetSuite data integration, including SuiteAnalytics Connect and SuiteQL.
• Develop Kimball‑style dimensional models and star/snowflake schemas.
• Build idempotent ETL/ELT pipelines with scalable, auditable processes.
• Architect Power BI semantic models and secure data access via RLS.

Skills

Data architecture

Medallion architecture

Dimensional modeling

Power BI

T-SQL

ETL/ELT frameworks

NetSuite integration

Data governance

Performance tuning

Security & RLS

Tools

SQL Server 2025/2022

SSIS

NetSuite ODBC

SuiteAnalytics Connect

SuiteQL

Power BI semantic models

Row-Level Security (RLS)

Fivetran

Data gateway

Job description

Data Architect – SQL Server, Power BI & NetSuite

**Experience:** 12+ Years

**Location:** Remote

**Duration:** 6 Months

**Working Hours:** 11:00 AM – 8:00 PM IST

**US Overlap:** Candidate must be flexible to provide up to 4 hours of overlap with US EST hours, as required.

**Engagement:** Contract

Role Overview

We are looking for a highly experienced **Data Architect** with strong expertise in **Microsoft SQL Server, Power BI, NetSuite ERP, and enterprise data warehousing**.

The candidate will be responsible for architecting and implementing an **SQL-first Medallion Architecture (Bronze, Silver, Gold)** on an on-premises **SQL Server 2025 Standard Edition** environment. The role requires hands‑on expertise in data architecture, ETL/ELT, NetSuite data integration, data quality, dimensional modeling, and Power BI.

The ideal candidate should be comfortable taking architectural ownership, defining technical standards, and making critical decisions around data ingestion, transformation, security, performance, and reporting.

Key Responsibilities

1. Data Architecture & Medallion Framework

• Design and enforce an **on-premises SQL-first Medallion Architecture** consisting of Bronze, Silver, and Gold layers.
• Architect scalable and maintainable data warehouse solutions using **SQL Server 2025 Standard Edition**.
• Establish architectural standards for data ingestion, transformation, quality, governance, and consumption.
• Ensure the architecture supports reliable, scalable, and high-volume analytical workloads.

2. NetSuite ERP Integration

• Architect metadata-driven ingestion frameworks for extracting data from **NetSuite ERP**.
• Work extensively with **SuiteAnalytics Connect, SuiteQL, and NetSuite data models**.
• Design mechanisms for **hard-delete detection** and incremental data extraction.
• Understand NetSuite ODBC connectivity, data structures, limitations, and connection/concurrency considerations.
• Integrate historical ERP data with new cloud ERP operational data.
• Develop advanced **T‑SQL frameworks** for:
  • Data validation
  • Data matching
  • Design automated and **idempotent ETL/ELT pipelines**.
• Separate extraction logic from business transformation logic to improve maintainability and scalability.
• Work with **temporal tables and columnstore indexes** for analytical workloads.
• Configure and optimize **SQL Server Resource Governor** where applicable.

4. Data Warehouse & Dimensional Modeling

• Design **Kimball-style dimensional models** using Star and Snowflake schemas.
• Define fact tables, dimension tables, surrogate keys, relationships, and slowly changing dimensions.
• Design the Gold layer for efficient downstream reporting and analytics.
• Develop strategies for integrating legacy ERP historical data with new ERP data while maintaining consistency and traceability.

5. ETL/ELT & Ingestion Tooling

• Evaluate and implement high-volume data ingestion solutions.
• Determine the appropriate balance between:
  • SaaS-based/hybrid ingestion tools such as **Fivetran**
  • Custom SQL/T‑SQL solutions
• Design ingestion architecture within strict enterprise network and security boundaries.
• Ensure pipelines are reliable, restartable, auditable, and scalable.
• Design optimized **Power BI semantic models** for enterprise reporting.
• Develop and optimize **DAX measures and calculations**.
• Implement **Row-Level Security (RLS)**.
• Design Power BI models for efficient consumption of Gold-layer data.
• Architect and plan **Enterprise On-Premises Data Gateway** connectivity and capacity.
• Troubleshoot Power BI performance and data refresh issues.

Technical Prerequisites

Mandatory Skills

• **12+ years of overall experience** in Data Architecture/Data Engineering/Data Warehousing.
• Strong expertise in **Microsoft SQL Server**, preferably SQL Server 2022/2025.
• Deep understanding of SQL Server internal architecture and performance optimization.
• Hands‑on experience with:
  • T‑SQL
  • SSIS
  • SQL Server Resource Governor
  • Temporal Tables
  • Columnstore Indexes
  • Data warehouse architecture
• Strong knowledge of **ETL/ELT architecture and metadata‑driven pipelines**.
• Proven experience with **NetSuite ERP integration**.
• Hands‑on knowledge of:
  • SuiteAnalytics Connect
  • SuiteQL
  • NetSuite ODBC connectivity
  • Incremental extraction
  • NetSuite concurrency/connection limitations
• Strong experience with **Power BI** including:
  • Semantic Models
  • DAX
  • Row-Level Security
  • Data modeling
  • Performance optimization
  • Gateway capacity planning
• Strong understanding of **Kimball dimensional modeling**.
• Experience with Star and Snowflake schemas.
• Experience migrating and integrating **legacy ERP historical data with cloud ERP data**.
• Strong understanding of data quality, matching, deduplication, and survivorship.
• Experience building **idempotent and restartable ETL/ELT pipelines**.

Preferred Skills

• Experience working with **SQL Server 2025 Standard Edition**.
• Experience replacing or modernizing legacy **Microsoft DQS/MDS** implementations.
• Experience with **Fivetran or similar SaaS ingestion platforms**.
• Experience working within strict enterprise network/security environments.
• Strong stakeholder management and technical leadership skills.

Ideal Candidate Profile

The ideal candidate should be a **hands‑on Data Architect**, not only a high-level architect. They should be capable of understanding the existing data landscape, defining the target architecture, reviewing technical implementations, troubleshooting complex SQL/ETL issues, and guiding the engineering team toward a stable production‑ready solution.

The candidate should also demonstrate strong **technical ownership, architectural decision-making, and leadership**, particularly in environments involving ERP migration, data warehouse modernization, and business‑critical reporting.

**Database:** Microsoft SQL Server 2025 Standard Edition

**ERP:** NetSuite

**NetSuite Integration:** SuiteAnalytics Connect, SuiteQL, ODBC

**Architecture:** SQL-First Medallion Architecture

**Data Warehouse:** Kimball, Star Schema, Snowflake Schema

**BI:** Power BI Service, DAX, Semantic Models, RLS

**Programming/Query:** Advanced T‑SQL

Working Requirements

• Remote position.
• Candidate must be flexible to provide **up to 4 hours of overlap with US EST working hours**, when required.
• Immediate/short-notice availability is preferred depending on project requirements.
• Strong communication and leadership skills are required.