Support Intel
An ELT pipeline with traceable lineage into Snowflake
Support-ticket data lands from Azure Blob Storage into Snowflake and is transformed through dbt staging, intermediate and mart layers, orchestrated by Airflow. Ten models produce dimension and fact tables with lineage you can follow from source to consumption, which is what makes it possible to say precisely which questions the warehouse can answer.
Verified: each of these is checkable in the repository
- 10
- dbt models across 3 layers5 staging views, 1 intermediate view, 4 mart tables
- 20k
- source tickets, multi-languageHuggingFace customer-support-tickets dataset
- 5
- raw tables landed via external stageCOPY INTO from @RAW.azure_stage
Warehouse pipelines fail quietly. A model runs, rows appear, a dashboard renders a number, and nothing tells you the join silently dropped most of the data. I wanted to build the layered structure properly and then trace it end to end to see what it actually supported.
Support tickets come from a public dataset; customers, agents, products and SLA policies are synthesised with a fixed seed. All five land as CSVs in Azure Blob Storage, are copied into Snowflake raw tables through an external stage, and transformed by dbt: staging views normalise and cast, an intermediate view enriches tickets with SLA policy, and mart tables materialise facts and dimensions. Airflow orchestrates load, then each dbt layer in sequence.
Why it is built this way
01Views for staging, tables for marts
Staging and intermediate materialise as views so they cost nothing to keep current; marts materialise as tables because they are read repeatedly. The layer boundary and the materialisation boundary line up.
02Cast defensively at the edge
Staging uses try-cast variants and filters null keys, so malformed source rows fail at the boundary rather than propagating a type error into a mart.
03Derive categorisation once, upstream
Complexity buckets and priority ranks are computed in the intermediate layer, so every downstream consumer inherits one definition instead of each dashboard inventing its own.
This is the project I would most want to be asked about, because it is where lineage earned its keep. Following the keys from source through each layer showed me exactly which questions the marts could answer and which the source data could never support: before anything was built on top of them. That is the whole argument for layered pipelines: the structure surfaces the modelling problem while it is still cheap to fix.
Next project
DataPilot
Natural language to governed SQL over BigQuery