SQL SERVER DATA WAREHOUSE DEVELOPER

SQL Server Data Warehouse Developer

Client: Our client is a leading alcohol distribution company in the UAE.

Location: Fully Remote

Contract Duration: 6 Months

Start Date: 1st August 2026

Working Hours: Monday to Thursday: 8:00 AM – 5:30 PM | Friday: 8:00 AM – 3:30 PM

Salary: AED 13,000 per month

Job Description

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

• 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, and 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 covering 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 through indexing strategies, query plan analysis, and partition design.
• Write modular, reusable, and well-documented stored procedures.
• Maintain technical documentation including runbooks, data dictionaries, stored procedure specifications, and warehouse design documentation.
• Collaborate with the BI team to ensure Gold layer 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
• 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 environment.
• Proven experience building layered warehouse architectures (Bronze/Silver/Gold or equivalent).
• Strong proficiency in complex T-SQL including multi-table joins, CTEs, subqueries, and window functions.
• Experience implementing 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 frameworks.
• Experience delivering data to BI platforms such as Power BI, SSRS, SSAS, or equivalent.

Nice to Have

• Familiarity with Oracle Fusion data models (Finance, Supply Chain, Procurement).
• SSAS Tabular Model experience.
• Experience with Prefect or similar Python orchestration tools.
• Power BI data modelling and star schema knowledge.