Warehouse
Snowflake, BigQuery, Databricks — where raw and transformed data actually lives, built for large analytical scans rather than fast transactional reads.
Lesson 2 of 3 · ⭐ Gap priority
Modern Data Stack Foundations
Joining tables in a warehouse you've never touched to answer a one-off business question is a lot less intimidating once you know what to look for first.
The stack, layer by layer
Four layers cover most of what a 'modern data stack' actually means in practice.
ELT (not ETL)
Modern stacks load raw data first, then transform it inside the warehouse — flipping the older ETL order now that warehouse compute is cheap enough to do transforms there.
dbt
The transformation layer — SQL that turns raw loaded tables into clean, tested, documented models, version-controlled like application code.
BI / semantic layer
Where a business question gets answered against the transformed models, without every analyst reinventing the same join.
Reading someone else's warehouse for the first time
Orient yourself by naming convention before you write a single query.
Look for a schema or dataset naming pattern — raw, staging, and mart (or marts) layers are close to universal, even when the exact prefix differs. If dbt is in use, its generated docs site and lineage graph will show you exactly how a mart model traces back to its raw source, faster than reading the SQL yourself. Distinguish fact tables (events, transactions) from dimension tables (descriptive attributes you join facts against) before you write your first query.
Exercises
Look at the sanitized query below, then work through both exercises.
-- unfamiliar warehouse: staging/mart naming tells you where to start
select
d.plan_name,
count(*) as customers
from analytics.fct_subscriptions f
join analytics.dim_customers d
on f.customer_id = d.customer_id
where f.status = 'active'
group by d.plan_name;Concept check
Answer in your head first, then click a question to reveal the answer.
Scenario checklist
Work through the sample query and check each item off. Progress is saved in your browser.
Go deeper
These primers are a starting point. Read the primary sources to build real fluency.
Snowflake Docs: Key concepts and architectureWarehouses, databases, and how Snowflake separates storage from computedbt Developer Hub: What is dbt?The transformation layer this lesson's exercises are built aroundGoogle Cloud: BigQuery overviewA second major warehouse, for comparison against Snowflake
Training Notes
What building this project taught me.
This is a flagged gap-priority module
Modern data stack fluency was Tom's #4 self-identified gap across a scan of 30+ job descriptions. It's split across this lesson (the warehouse and transformation layer) and the next one (pipelines and orchestration).
Naming conventions below are common, not universal
stg_/fct_/dim_ prefixes come from dbt's own style guide and dimensional modeling generally — a real warehouse may use different conventions, but the underlying raw/staging/mart distinction is nearly universal.