Data Warehouses and Data Lakes

Compare warehouse, lake and lakehouse roles

Compare warehouse, lake and lakehouse roles

A warehouse emphasises managed analytical tables and SQL workloads; a lake stores files/objects across formats; a lakehouse approach adds table-management capabilities over lake storage. These terms describe architectures, not automatic quality guarantees. A CSV folder is not equivalent to a governed lakehouse, and a local SQLite database is not a distributed warehouse. Choose according to data contracts, latency, workload, governance and operational needs rather than the newest label. The local exercise models roles without provisioning services.

Follow a concrete example

Compare the raw CSV, validated line table and monthly SQL result as three distinct artifacts. Their existence does not imply a managed catalogue, transactions, access policies or historical table versions. Write which capabilities a real workload needs before applying a warehouse/lake/lakehouse label. The local example deliberately avoids paid service provisioning.

Further reading: Airflow 2.10.5 fundamentals, Spark SQL guide. The case and exercises are authored for DataCareerHub.

Your task

Map raw CSV, prepared lines and monthly outputs to raw/prepared/serving roles. State what the local fixture does not implement.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Model facts, dimensions and historical context

Model facts, dimensions and historical context

A fact represents an event or measurement at a declared grain. A dimension provides descriptive context with a defined key and history policy. Haven line facts reference orders and products; historical transaction price stays on the line. A star serving model may denormalise useful descriptive attributes but must retain unique dimension keys and consistent history. Slowly changing descriptions require a deliberate time-validity approach in real systems; simply joining all events to today’s label can rewrite past interpretation.

Follow a concrete example

A month aggregate and a line fact cannot be summed after a naive join without repeating the monthly amount. Keep a declared grain on every serving table. If a dimension description changes, decide whether reporting uses current or historical context and identify the source evidence needed. A convenient current lookup cannot reconstruct unavailable history.

Inspect the reference implementation

Language: Python 3.12. The complete downloadable file includes the named helpers and the fresh-output command-line runner. Trace the source, transformation and result against this lesson’s contract before running it locally.

def de_storage(source,out):
 connection=sqlite3.connect(':memory:');connection.executescript((Path(source)/'setup.sql').read_text())
 result=[dict(zip(['month','value_cents'],r)) for r in connection.execute("SELECT substr(o.order_date,1,7),sum(l.quantity*l.unit_price_cents) FROM orders o JOIN order_lines l USING(order_id) WHERE status='completed' GROUP BY 1")]
 connection.close();return {'warehouse_demo':result,'raw_layer':'unchanged CSV files','prepared_layer':'validated line-grain records','serving_layer':'category/month or order-grain aggregates','format_note':'SQLite is a local relational analogue; it is not a distributed warehouse or lakehouse.'}

Further reading: Airflow 2.10.5 fundamentals, Spark SQL guide. The case and exercises are authored for DataCareerHub.

Your task

Sketch a line-grain analytical model and a monthly serving aggregate. Explain historical price and category-change policies.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Define partitions, retention and quality contracts

Define partitions, retention and quality contracts

Partitioning can reduce scanned data when filters align with partition keys, but excessive tiny partitions add overhead. Retention and access rules depend on permitted use and source requirements, not arbitrary convenience. Define schema evolution, late arrivals, reprocessing and deletion before claiming an operational storage design. The exercise uses April/May logical groups and a local SQL summary; it does not measure warehouse speed or create cloud resources. Keep raw evidence and curated outputs distinguishable through paths, manifests and permissions.

Follow a concrete example

Use an architecture decision record to capture assumptions and unresolved owners. Partition selection, schema evolution and retention are separate choices. A monthly partition may suit a monthly query, but a tiny fixture cannot prove scan-cost savings. Retention and deletion rules require authorised requirements; the course does not invent them.

Further reading: Airflow 2.10.5 fundamentals, Spark SQL guide. The case and exercises are authored for DataCareerHub.

Your task

Write a storage decision record covering partition key, retention owner, schema changes, reprocessing and failure handling. Reconcile the serving summary.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.