Job Openings SQL SERVER DATA WAREHOUSE DEVELOPER

About the job SQL SERVER DATA WAREHOUSE DEVELOPER

ROLE OVERVIEW

We are looking for a hands-on SQL Server Data Warehouse Developer to design and build a structured, production-grade data warehouse on Microsoft SQL Server. Data from Oracle Fusion has already been ingested into SQL Server via Python-based REST API pipelines. Your role is to take this raw ingested data and architect a robust Medallion (Bronze, Silver, Gold) layered warehouse, implement dimensional models, enforce data quality, and deliver clean, reliable data to the BI reporting layer.

This is a hands-on development role. You must be comfortable writing complex SQL, building stored procedures, designing fact and dimension tables, and owning data quality end-to-end.

KEY RESPONSIBILITIES

1. Bronze Layer — Raw Staging

  • Receive raw ingested data from source SQL Server tables populated by the existing Python pipeline.
  • Define and enforce primary keys, foreign keys, and constraints at the staging level.
  • Implement watermark tables to track last-loaded timestamps and support incremental data loading.
  • Create error tables to capture rejected, malformed, or duplicate records for review and reprocessing.
  • Implement data quality checks at ingestion: null checks, type validation, duplicate detection, referential integrity.

2. Silver Layer — Conformed & Cleansed

  • Design and build conformed dimension and staging fact tables with SCD Type 2 history tracking where applicable.
  • Apply deduplication, standardisation, and business rule transformations using stored procedures.
  • Create and maintain views that expose clean, joined, and validated datasets for downstream use.
  • Write complex multi-table SQL joins across Finance, Supply Chain, and Warehouse data domains.
  • Implement error handling within stored procedures with transaction management and rollback logic.

3. Gold Layer — Business-Ready

  • Build optimised fact tables and dimension tables following star schema design principles.
  • Enforce referential integrity, business rule validation, and data quality gates before data is promoted to Gold.
  • Implement incremental load patterns using watermark columns and MERGE/UPSERT statements to avoid full reloads.
  • Create reporting views and aggregation layers consumed by Power BI and SSAS cubes.
  • Maintain audit columns (load timestamp, source system ID, batch ID, record hash) across all Gold layer tables.

4. Data Quality & Governance

  • Design and maintain a data quality framework: completeness, accuracy, consistency, and timeliness checks at each layer.
  • Build and maintain error logging tables with actionable metadata (error type, source record, timestamp, resolution status).
  • Implement reconciliation checks comparing source record counts to loaded counts at each layer transition.
  • Document data lineage and source-to-target mapping for all key entities.

5. Performance & Maintainability

  • Optimise SQL queries and stored procedures for performance: indexing strategies, query plan analysis, partition design.
  • Write modular, reusable, and well-commented stored procedures that other team members can maintain.
  • Maintain technical documentation: runbooks, data dictionaries, stored procedure specs, and layer-by-layer design docs.
  • Collaborate with the BI team to ensure Gold layer data structures support Power BI and SSAS reporting requirements.

MANDATORY TECHNICAL SKILLS

Core SQL Server Skills

  • T-SQL (complex joins, CTEs, window functions)
  • MERGE / UPSERT statements
  • Stored procedures & functions
  • Views & materialised views
  • Primary / foreign keys, constraints
  • Indexing & query optimisation

Data Warehouse Skills

  • Medallion architecture (Bronze/Silver/Gold)
  • Fact & dimension table design
  • SCD Type 1 & Type 2
  • Watermark-based incremental loading
  • Error tables & data quality checks
  • Reconciliation & audit columns

Supporting Skills

  • SQL Server (2016+)
  • Power BI integration awareness
  • Git / version control
  • Technical documentation
  • Agile / sprint-based delivery

EXPERIENCE REQUIREMENTS

  • 5+ years of hands-on SQL Server development experience in a data warehousing context.
  • Demonstrable experience building layered warehouse architectures (Bronze/Silver/Gold or equivalent staging/ODS/DW patterns).
  • Strong proficiency in writing and debugging complex T-SQL: multi-table joins, CTEs, subqueries, window functions.
  • Proven experience with MERGE/UPSERT for incremental loading and watermark-based change tracking.
  • Experience implementing SCD Type 2 dimension history tracking.
  • Experience designing and maintaining error tables, audit columns, and data quality validation logic.
  • Prior experience delivering to a BI layer (Power BI, SSRS, SSAS, or equivalent).

Nice to Have

  • Familiarity with Oracle Fusion data models (Finance, Supply Chain, Procurement) as source context.
  • SSAS Tabular model awareness for downstream cube compatibility.
  • Experience with Prefect or similar Python orchestration tools.
  • Power BI data modelling awareness (star schema alignment).

Vertical

Technology


Or refer someone