Data Lakehouse

Context: FIT3003_MOC · data-lake storage plus warehouse-style managed tables · the cloud successor to the unit’s Oracle “Model 3” pipeline (Data Engineering) · loaded by ELT · sibling of Cloud Data Warehousing · (W12 emerging-topics deck — not a book chapter) Parent Framework: Data Warehouse

Quick Revision

• 🎯 Objective: combine cheap, any-format lake storage with warehouse table management ➔ one shared copy of the data serves SQL/BI and ML, with less duplication between workloads.
• 📦 Core Components: lake = schema-on-read | warehouse = schema-on-write | layers Raw (Bronze) ➔ Cleaned (Silver) ➔ Facts & Dims (Gold) | open table formats (Parquet · Iceberg · Delta).
• ⚡ Key Constraint: same star schema, different engine — facts, dimensions, SCD, slicing and aggregation (Ch 2–18) carry over unchanged; only storage, compute and tooling change. A lake still needs metadata, security, quality controls and lifecycle management, or it cannot be trusted.

📝 How It Works

1. Warehouse vs lake

Aspect	Data warehouse (schema-on-write)	Data lake (schema-on-read)
Holds	curated, structured analytical data	data in original or transformed form
Data types	structured	structured, semi-structured, unstructured
Structure	defined schemas, controlled transformations	structure applied when read
Storage	warehouse engine	low-cost cloud object storage
Serves	SQL, reporting, BI, governed business metrics	exploration, large-scale processing, ML

2. The lakehouse pipeline

• Five steps ➔ ① sources produce operational and analytical data ② raw data lands in a cloud data lake ③ pipelines clean and transform ④ managed tables expose curated datasets ⑤ SQL engines, BI tools and ML consume them.
• Ingestion ➔ Kafka — real-time event bus, Spark streams it into Raw · Fivetran — scheduled pulls from DBs and SaaS · Auto Loader — picks up new files in storage.
• Storage $+$ compute ➔ S3 / ADLS: cheap storage, slow per file but fast in parallel · Spark: the compute that reads it in parallel (Hadoop and Spark) · Delta: the table format that adds ACID (ACID Properties).
• Platforms ➔ Databricks on AWS, Azure or GCP · AWS = S3 $+$ Glue $+$ Athena · Azure = ADLS $+$ Fabric · ⛔ not DynamoDB — that is an operational DB (NoSQL Databases).

3. Open table formats — four layers

Layer	Defines	Example
File format	how data is represented in files	Apache Parquet (columnar)
Table format	how files $+$ metadata form one logical table	Apache Iceberg · Delta Lake
Catalog	where tables are registered and discovered	—
Query engine	executes SQL or other analytics	Apache Spark

• Benefits ➔ many tools read the same tables · schema evolution and table snapshots (implementation-dependent) · interoperability.
• Challenge ➔ compatibility, governance and operational integration still need careful design.

4. Shared dimensions, streaming facts

• Conformed dimensions ➔ $\text{SalesFACT}$ (TotalSales, updated every minute) and $\text{InventoryFACT}$ (StockLevel, loaded nightly) join the same Time, Product and Store dimensions ➔ Multi-Fact Star Schemas.
• Different clocks ➔ dimensions are shared and change slowly (SCD); the fact is fluid, refreshed by the stream.

Streaming upsert into the fact (Spark, micro-batch per minute)

def upsert(batch, batch_id):
    agg = batch.groupBy('TimeID', 'ProductNo', 'StoreID') \
               .agg(sum('Amount').alias('Sales'))
    # MERGE INTO dw.sales_fact f USING agg ON keys
    # WHEN MATCHED THEN UPDATE TotalSales += Sales
    # WHEN NOT MATCHED THEN INSERT
(spark.readStream.table('cleaned.sales_events')
     .writeStream.foreachBatch(upsert)
     .trigger(processingTime='1 minute').start())

💡 Common Mistake: Overwriting instead of accumulating ➔ WHEN MATCHED must add the batch’s Sales to the stored TotalSales; a plain SET keeps only the last minute.

Micro-batch	Event	Fact row (Store 3, Product 17, today)
10:00	key unseen ➔ NOT MATCHED ➔ insert	TotalSales = \120$
10:01	key exists ➔ MATCHED ➔ add	\185$
10:02	MATCHED ➔ add	\240$

🗺️ Layer & Dataflow

Model 3 (Oracle, on-prem)	Lakehouse block	Databricks name	What happens
sources (public · 3rd party · producers)	sources $+$ app events, IoT, SaaS APIs	—	same producers, more of them
Temp DB (staging)	Raw	Bronze	as landed, append-only, $+$ load time and source file (raw JSON of every lab login)
ETL (PL/SQL scripts) — in progress	Cleaned	Silver	typed, de-duplicated, conflicts resolved (one valid row per login, known StudentID)
Data warehouse (star schema)	Facts & Dims	Gold	star built by ETL from cleaned tables (lab_fact: NumOfLogins by semester, period, degree)
OLAP / BI (reports, cubes)	SQL warehouse / BI	—	compute serving SQL to Power BI or Tableau (Databricks SQL, Snowflake, Athena)

flowchart LR
  S[Sources<br/>DBs · APIs · events · files] --> I[Ingestion<br/>Kafka · Fivetran · Auto Loader]
  subgraph L[Storage S3/ADLS · Delta · compute Spark]
    B[Raw / Bronze] --> SV[Cleaned / Silver] --> G[Facts & Dims / Gold]
  end
  I --> B
  G --> Q[SQL warehouse / BI · ML]

⚠️ Common Mistakes

• 💡 “A lake needs no governance” ➔ the deck’s warning: without metadata, security, quality and lifecycle controls the lake cannot be trusted ➔ Data Management and Data Governance.
• 💡 Thinking the lakehouse replaces dimensional modelling ➔ Gold is a star schema; the design decisions of the unit are unchanged.

🧠 Active Recall

Map the unit's Oracle pipeline onto Bronze / Silver / Gold, and say what does NOT change.

Answer

• Short answer: Temp DB ≈ Raw/Bronze · ETL in progress ≈ Cleaned/Silver · data warehouse ≈ Facts & Dims/Gold; the star schema itself does not change.
• Why: Same star, different engine ➔ facts, dimensions, SCD, slicing and aggregation carry over; the change is storage (object store $+$ Delta instead of Oracle tables), compute (Spark) and tooling (Kafka, Fivetran, Auto Loader instead of SQL*Loader and PL/SQL).

Why is a warehouse called schema-on-write and a lake schema-on-read?

Answer

• Short answer: a warehouse imposes its schema when data is loaded; a lake stores data as-is and imposes structure only when it is queried.
• Why: Where transformation sits ➔ the warehouse’s controlled transformations make it fit governed BI metrics; the lake’s raw retention makes it fit unstructured data, exploration and ML — the lakehouse keeps the raw lake and adds managed tables on top.