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