Data Engineer (Cloud Data Warehouse | SQL | Medallion Architecture)

Role Summary

We are seeking a hands-on Data Engineer with strong experience in enterprise data platforms, Azure Databricks preferred (not mandatory), advanced SQL, and Medallion (Bronze/Silver/Gold) architecture. This role will support Phase 3 data domain and data product design initiative by reverse engineering complex SQL transformation logic and producing detailed, consumable source-to-target mapping and transformation documentation across additional enterprise domains.

Key Responsibilities

• Analyse and reverse engineer complex SQL scripts and multi-step transformation pipelines (often spanning multiple hops) from source systems through the current data platform.
• Document detailed source-to-target mappings across Medallion layers (Bronze → Silver → Gold), including field-level lineage, joins, filters, aggregations, and derivations.
• Capture transformation rules and business logic embedded in SQL (and/or Spark SQL) and translate them into clear, structured mapping artifacts for downstream engineering teams.
• Partner with data product, architecture, and domain SMEs to validate mapping assumptions, clarify business definitions, and resolve data ambiguities.
• Produce high-quality data mapping deliverables (e.g., mapping sheets, rule catalogs, lineage summaries) that are traceable, reviewable, and audit-friendly.
• Identify data quality checks and reconciliation approaches (e.g., row counts, control totals, null/duplicate checks) to confirm transformations align to intended outcomes.
• Contribute to reusable documentation patterns/standards to improve consistency across domains (naming conventions, mapping templates, and documentation structure).
• Support knowledge transfer to build/engineering teams that will implement or operationalize the mapped transformations in Databricks.

Required Qualifications

• Strong hands-on expertise in advanced SQ L (complex joins, window functions, CTEs, nested queries, performance considerations) and ability to interpret production-grade transformation logic.
• Proven experience with data mapping and source-to-target documentation for enterprise-scale platforms, including transformation rules and field-level lineage.
• Working experience of Lakehouse/Medallion approach, including Bronze/Silver/Gold layering concepts.
• Experience performing data lineage analysis (end-to-end tracing of fields across transformations and tables).
• Experience defining/implementing data quality checks and validation strategies aligned to transformations and business rules.
• Working experience with data contracts
• Strong documentation skills with attention to detail ; ability to produce clear artifacts consumable by multiple teams.
• The lead resource should have flexibility to travel to Toronto as needed.

Preferred Qualifications

• Experience working with banking / financial services data domains (e.g., customer, accounts, transactions, risk, finance, regulatory reporting).
• Experience in enterprise data domain and data product design initiatives (data contracts, domain-aligned datasets, standardized definitions).
• Familiarity with metadata, cataloguing, and governance practices ( e.g., data dictionaries, lineage documentation, stewardship inputs).
• Experience collaborating with geographically distributed teams and stakeholders across business and technology functions.

Education & Certifications

• Bachelor’s degree in Computer Science, Engineering, Information Systems, or related discipline (or equivalent practical experience).
• Preferred (not mandatory): Azure/AWS data certifications (e.g., Azure data engineering credentials).

Expected Deliverables

• Field-level source-to-target mapping sheets for Bronze, Silver, and Gold datasets (including data types, nullability assumptions, keys, and standardization rules as available).
• Transformation rule catalogue capturing joins, filters, aggregations, calculation logic, and derived fields.
• Data lineage summaries (table-to-table and column-to-column) across multi-hop transformations.
• Data validation / reconciliation checklist aligned to each mapped flow (counts, control totals, key checks, and exception handling notes).
• Open items / assumptions log and clarification questions for SMEs and domain owners.

Core Competencies

• Strong analytical and problem-solving skills; ability to break down complex SQL logic into understandable business and technical rules.
• Excellent written and verbal communication, with a focus on producing crisp, unambiguous documentation.
• Stakeholder management and collaboration skills to work with business SMEs, data architects, and engineering teams.
• High attention to detail, quality mindset, and comfort working with ambiguity in enterprise environments.
• Ability to manage deliverables across multiple domains with predictable execution and clear status reporting.