N. Okoro
← All projects

DATA ENGINEERING · LAKEHOUSE

Medallion Lakehouse & dbt Models

How can source sales data become versioned records and analytical tables?

SQLData EngineeringdbtDelta Lake

7 snapshots · 3 gold models · Portfolio case study

Supplied medallion architecture showing bronze raw ingestion, silver cleaning, and gold business aggregates, with Azure, Databricks, and dbt
Supplied reference architecture · the uploaded implementation substantiates dbt snapshots and SQL models; the cloud deployment is not verified.

The question & data

AdventureWorks source declarations, dbt snapshots, and gold-layer SQL models demonstrate the transformation layer of a bronze–silver–gold lakehouse design.

Nine SalesLT source tables are declared. Seven snapshot SQL files and three gold model SQL files are supplied, alongside model-test YAML and a dbt project configuration.

The approach

  1. Declared bronze sources and configured seven silver snapshots with a check strategy, hard-delete invalidation, and Delta storage settings.
  2. Built current-record customer and product dimensions by filtering snapshot validity and joining related records.
  3. Defined a sales model combining order details, product attributes, and order headers, plus YAML declarations intended to test uniqueness and nullability.

What the work reveals

Customer and product dimensions select current snapshot records through dbt_valid_to IS NULL.

The sales model mixes a detail snapshot with raw product and order-header sources; historical consistency requires review.

From findings to next steps

Align model and column names with the YAML tests, define the fact-table grain, and review snapshot filtering and stable surrogate keys before validating in the target environment.

Read the results in context

YAML names dim_customers, dim_products, and dim_sales do not match the supplied SQL filenames. Some test column names also differ. Order-level uniqueness is unsuitable for a multi-line sales fact. Azure Data Factory, Databricks, and ADLS Gen2 appear in the documented design, but cloud deployment exports and run evidence were not supplied. No dbt execution or passing tests are claimed.

Learning resource: Project reference linked in the supplied README ↗

Explore the SQL implementation
{{
    config(
        materialized = "table",
        file_format = "delta",
        location_root = "/mnt/gold/products"
    )
}}

with product_snapshot as (
    select
        productId,
        name,
        standardCost,
        listPrice,
        size,
        weight,
        productcategoryid,
        productmodelid,
        sellstartdate,
        sellenddate,
        discontinueddate
    from {{ ref("product_snapshot") }}
    where dbt_valid_to is null
),

product_model_snapshot as (
    select
        productmodelid,
        name,
        CatalogDescription,
        row_number() over (order by name) as model_id
    from {{ ref("productmodel_snapshot") }}
    where dbt_valid_to is null
),


transformed as (
    select
        row_number() over (order by p.productId) as product_sk,
        p.name as product_name,
        p.standardCost,
        p.listPrice,
        p.size,
        p.weight,
        pm.name as model,
        pm.CatalogDescription as description,
        p.sellstartdate,
        p.sellenddate,
        p.discontinueddate
    from product_snapshot p
    left join product_model_snapshot pm on p.productmodelid = pm.productmodelid
)

select * from transformed
Download SQL ↓

Based on supplied portfolio materials. Findings describe the dataset and report; recommendations are proposed next steps, not measured business outcomes.

Next case study Customer Shopping Behavior ↗