Lakehouse and the modern stack

The lakehouse only works because the file layer got smart. Two layers of format, worth keeping straight:

• Parquet - the file format. A columnar file: column-organized bytes, compressed, with min/max metadata baked in. Effectively a warehouse storage layer you can email, drop in S3, or hand to any engine. You will write one in Demo 1.
• Iceberg and Delta Lake - the table formats. A layer of metadata ON TOP of many Parquet files that adds what files alone lack: transactions, schema evolution, time travel, safe concurrent writers. They turn a folder of files into a reliable TABLE. Name-drop level tonight - no hands-on; know that "lakehouse" in a vendor pitch almost always means "Iceberg or Delta over Parquet".

• Small team, BI-first workload (Daybreak today): a plain warehouse - or honestly DuckDB over Parquet - is simplest and cheapest. Lakehouse machinery earns nothing here yet.
• Warehouse + heavy ML/data-science on raw data: lakehouse shines - one copy serves SQL dashboards and Python training jobs without export pipelines.
• Petabyte scale, many engines, many teams: lakehouse is winning this tier decisively; storing everything twice (lake for ML, warehouse for BI) is the pattern it killed.
• Either way: the modeling layers of this course carry over unchanged - the lakehouse changed WHERE tables live, not what a good star schema looks like.

• One Big Table wins when the team is small, questions are stable, and the BI tool (or the analysts) hate joins. Modern columnar engines make the width nearly free to store - column pruning means unused columns cost nothing to query. It loses when dimensions change (update every row?), when grain questions get subtle, and when five OBTs quietly disagree about what "revenue" means.
• Data Vault wins in large, multi-source integration programs: hubs hold business keys, links hold relationships, satellites hold attributes with full history - extremely auditable and parallel-loadable. It loses on query ergonomics so completely that every Vault shop builds star schemas on top for analysts. It is a back-room organizing system, not a serving layer.
• Star remains the default because it balances all of it: understandable grain, reusable conformed dimensions, one-join queries, SCD history where needed. Kimball's shape has survived every platform shift since the 90s - including this one; lakehouse tables are very often star-shaped Parquet.

• Snowflake: external tables and Iceberg tables read lake files in place; regular tables use its managed storage.
• BigQuery: external tables over GCS files, BigLake for the governed version.
• Databricks: everything is Delta files on object storage - the lakehouse pitch in its purest form.
• The shared idea: storage and compute are separate purchases. Files are the contract between them - which is exactly what you are about to exploit in Demo 1.

Runs as written - then make it yours: change the grouping to p.roast, or filter to one channel. The point to feel: the file participates in the join exactly like a warehouse table.

COPY (SELECT * FROM fct_order_line)
TO 'fct_order_line.parquet' (FORMAT PARQUET);

SELECT p.category,
       ROUND(SUM(f.line_revenue), 0) AS revenue
FROM 'fct_order_line.parquet' f
JOIN dim_product p ON f.product_key = p.product_key
WHERE f.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;

Part 2 made the OBT argument in words; make it in SQL. One CTAS flattens the fact and all three dimensions into a single wide table, then counts what you built:

CREATE TABLE obt_order_line AS
SELECT f.order_id, f.quantity, f.unit_price, f.line_revenue,
       f.status, f.channel,
       d.date_key, d.year, d.month, d.month_name, d.is_weekend,
       c.name AS customer_name, c.city, c.country, c.plan,
       p.name AS product_name, p.category, p.roast
FROM fct_order_line f
JOIN dim_date d     ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p  ON f.product_key = p.product_key;

SELECT count(*) AS row_count,
       (SELECT count(*) FROM information_schema.columns
        WHERE table_name = 'obt_order_line') AS column_count
FROM obt_order_line;

Now the tradeoff, out loud: 18 columns, no joins - lovely for a BI tool. But city is repeated on every line Ava ever ordered, so when she moves, an UPDATE has to find them all (the star changed ONE dim row in b5). And if marketing builds a second OBT with a slightly different refund filter, "revenue" now has two definitions. Generate OBTs FROM the star; never let them become the source of truth.