DATA ENGINEERING · LAKEHOUSE
Medallion Lakehouse & dbt Models
How can source sales data become versioned records and analytical tables?
7 snapshots · 3 gold models · Portfolio case study

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
- Declared bronze sources and configured seven silver snapshots with a check strategy, hard-delete invalidation, and Delta storage settings.
- Built current-record customer and product dimensions by filtering snapshot validity and joining related records.
- 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 transformedDownload SQL ↓Based on supplied portfolio materials. Findings describe the dataset and report; recommendations are proposed next steps, not measured business outcomes.