LEAD DATA ENGINEER - Snowflake

Offshore Resource Requirement
Estia Health Data Platform Program
Contents
Resource Requirement Summary 3
1. Senior Data Engineer ? Offshore / Vendor 4
Role Purpose 4
Minimum Experience Required 4
Core Responsibilities 5
Core Technical Skills Required 5
Nice to Have Skills 7
Expected Deliverables 7
Must Not Be 7
2. Senior BI Analyst / Senior BI Developer ? Offshore / Vendor 9
Role Purpose 9
Minimum Experience Required 10
Core Responsibilities 10
Core Technical Skills Required 11
Migration / Metric Rationalisation 12
Mandatory Delivery Expectations 12
Nice to Have Skills 13
Expected Deliverables 13
Must Not Be 13
Resource Requirement Summary
We require 3 offshore/vendor delivery resources to support a modern data platform migration and build program.
Role
Count
Primary Focus
Senior Data Engineer
2
Snowflake, dbt, ADF, SQL, stored procedure migration, Kimball modelling
Senior BI Analyst / Senior BI Developer
1
Power BI semantic model, report migration, SQL, dimensional modelling, metric migration
The resources must have prior experience in data platform migration projects, specifically moving from legacy SQL Server / stored procedure-heavy warehouse logic into a modern cloud data platform.
Senior Data Engineer ? Offshore / Vendor
Number of Resources -2
Seniority - Senior
Primary Technology Stack
Snowflake
dbt Cloud or dbt Core
Azure Data Factory
SQL Server / T-SQL
Azure DevOps / Git
Kimball dimensional modelling
Role Purpose
The Senior Data Engineer will support the migration of complex legacy SQL Server stored procedure logic into modular, governed and reusable data platform layers across Bronze, Silver and Gold.
This is a hands-on build and delivery role, not a design-only consulting role. Solution design, architecture decisions and modelling standards will be owned by the internal technical lead. The resource is expected to implement, test, validate, document and deliver agreed designs using Snowflake, dbt and Azure Data Factory.
The role requires genuine data engineering experience, not report support, dashboard development or light SQL analysis.
Minimum Experience Required
Minimum 4+ years hands-on data engineering experience.
Minimum 1+ years Snowflake or modern cloud data warehouse experience.
Minimum 1+ years dbt Cloud or dbt Core experience.
Minimum 1+ years Azure Data Factory experience.
Must have delivered at least one data platform migration or modernisation project.
Must have migrated or refactored SQL Server stored procedures, or similar legacy stored procedure logic, into modern data platform patterns such as Snowflake, Databricks, Microsoft Fabric or AWS/Azure-native data platforms.
Must have hands-on experience building facts, dimensions, SCDs, incremental models and batch pipelines.
Must be comfortable working from incomplete or complex legacy logic and converting it into clean, governed data models.
Core Responsibilities
Analyse complex SQL Server stored procedures and identify embedded business rules.
Convert procedural SQL logic into reusable Snowflake and dbt models.
Build Bronze, Silver and Gold transformation layers.
Build Kimball-style facts, dimensions and star schemas.
Implement SCD Type 1 and Type 2 patterns.
Build incremental, batch and CDC-style processing patterns.
Build Azure Data Factory ingestion and orchestration pipelines.
Build dbt models, macros, tests, snapshots, documentation and YAML configuration.
Support source-to-target mapping, reconciliation and data validation.
Implement data quality checks and validation rules.
Support deployment automation, Git and CI/CD processes.
Maintain mandatory technical documentation covering lineage, transformation rules, reconciliation outcomes, deployment steps and handover notes.
Work with the internal technical lead to follow engineering standards, naming standards and modelling patterns.
Core Technical Skills Required
The candidate must have strong hands-on delivery experience across SQL, Snowflake, dbt, Azure Data Factory and dimensional modelling.
SQL / Data Engineering
Strong SQL engineering experience, including complex joins, CTEs, window functions, stored procedure analysis, performance tuning and reconciliation.
Experience migrating or refactoring legacy SQL Server stored procedures into modern cloud data platform patterns.
Experience building ETL/ELT pipelines, batch loads, incremental loads and CDC-style processing.
Ability to read complex legacy logic, extract embedded business rules and rebuild them into clean governed data models.
dbt
Hands-on experience with dbt Cloud or dbt Core.
Ability to build dbt models across staging/source-clean, intermediate, canonical and mart layers.
Practical experience with ref(), source(), incremental models, snapshots, materialisations and model dependencies.
Experience writing and maintaining dbt YAML files, including model documentation, column documentation, source definitions, tests and freshness checks.
Experience with dbt tests such as not_null, unique, accepted_values, relationships and custom generic tests.
Experience creating and using dbt macros to reduce duplicated SQL logic.
Ability to follow a structured dbt project layout using models, macros, tests, snapshots, seeds and documentation.
Azure Data Factory
Experience building ADF ingestion and orchestration pipelines.
Experience with parameterised pipelines, datasets, linked services, variables, dependencies and environment-specific configuration.
Experience implementing error handling, retry logic, audit logging, incremental loads and batch orchestration.
Experience integrating ADF with Azure DevOps / Git and supporting CI/CD promotion across environments.
Snowflake
Strong Snowflake SQL development experience.
Experience creating and managing databases, schemas, tables, views and transformation layers.
Understanding of Snowflake warehouses, compute/storage separation, roles, access control, stages, file formats and COPY INTO.
Experience with incremental loading patterns, query performance tuning, warehouse sizing, auto-suspend / auto-resume and cost-aware development.
Data Warehouse / Modelling
Strong Kimball dimensional modelling experience.
Experience designing and building facts, dimensions, star schemas, conformed dimensions and surrogate keys.
Experience with Type 1 and Type 2 slowly changing dimensions.
Experience with transaction facts, snapshot facts, accumulating snapshot facts and bridge tables where required.
Strong understanding of fact table grain, source-to-target mapping, lineage, reconciliation and control totals.
Nice to Have Skills
Power BI awareness.
HR, workforce or rostering data experience.
dbt package experience.
dbt Labs certification or formal dbt training.
Data observability experience.
Data quality framework experience.
Metadata-driven ingestion framework experience.
Audit logging and operational monitoring experience.
Expected Deliverables
ADF ingestion pipelines and reusable pipeline templates.
Snowflake raw, clean and curated transformation tables.
dbt model files, macros, tests, snapshots and YAML documentation.
Bronze-to-Silver and Silver-to-Gold mapping logic.
Kimball facts and dimensions.
SCD Type 1 and Type 2 logic.
Incremental and batch load logic.
Reconciliation reports and data quality checks.
Mandatory documentation pack covering source-to-target mappings, dbt model documentation, YAML definitions, macro documentation, pipeline documentation, reconciliation evidence, deployment steps and handover notes.
Must Not Be
The Candidate Must Not Be Primarily:
a Power BI-only developer,
a dashboard developer,
a report support analyst,
a production support-only resource,
a manual tester,
a business analyst with light SQL,
a SQL analyst who has not built pipelines or data models,
someone who has only consumed facts/dimensions but not built them
someone whose recent experience is mostly non-engineering BI work
a design-only consultant who produces recommendations but does not build, test, validate, document and deliver working pipelines, models or data platform assets.
Senior BI Analyst / Senior BI Developer ? Offshore / Vendor
Number of Resources - 1
Seniority - Senior
Primary Technology Sta

Work arrangement
No