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