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
