Data Lakehouse Concepts Cheat Sheet

from delta import DeltaTabledf.write.format("delta").mode("overwrite").save("/lake/orders")# time travel to a previous versionhistory_df = spark.read.format("delta").option("versionAsOf", 3).load("/lake/orders")dt = DeltaTable.forPath(spark, "/lake/orders")dt.history().show(truncate=False)

MERGE INTO lake.orders AS targetUSING staging.orders_cdc AS sourceON target.order_id = source.order_idWHEN MATCHED THEN  UPDATE SET target.status = source.status, target.updated_at = source.updated_atWHEN NOT MATCHED THEN  INSERT (order_id, status, updated_at) VALUES (source.order_id, source.status, source.updated_at);

CREATE TABLE catalog.sales.transactions (  txn_id BIGINT,  amount DECIMAL(10,2),  event_ts TIMESTAMP)USING icebergPARTITIONED BY (days(event_ts));-- schema evolution without rewriting dataALTER TABLE catalog.sales.transactions ADD COLUMN currency STRING;

-- initial partitioning by dayCREATE TABLE catalog.sales.events (  event_id BIGINT,  event_ts TIMESTAMP,  region STRING)USING icebergPARTITIONED BY (days(event_ts));-- later, evolve to also partition by region — old data files are untouched,-- only new writes use the new partition specALTER TABLE catalog.sales.events ADD PARTITION FIELD region;-- query planning transparently combines both partition specsSELECT * FROM catalog.sales.eventsWHERE event_ts >= DATE '2026-06-01' AND region = 'us-east';

-- bin-pack small files produced by streaming/incremental writesOPTIMIZE lake.orders;-- co-locate rows by high-cardinality filter columns for data skippingOPTIMIZE lake.ordersZORDER BY (customer_id, order_date);-- remove files no longer referenced by the transaction log,-- respecting the default 7-day retention windowVACUUM lake.orders RETAIN 168 HOURS;

hudi_options = {    "hoodie.table.name": "orders",    "hoodie.datasource.write.recordkey.field": "order_id",    "hoodie.datasource.write.precombine.field": "updated_at",    "hoodie.datasource.write.partitionpath.field": "order_date",    # COPY_ON_WRITE: rewrites base files on every update — fast reads, slower writes    # MERGE_ON_READ: appends to delta log files, compacted later — fast writes, reads merge on the fly    "hoodie.datasource.write.table.type": "MERGE_ON_READ",    "hoodie.datasource.write.operation": "upsert",}df.write.format("hudi").options(**hudi_options).mode("append").save("/lake/orders")

-- drop snapshots older than 7 days, keeping the last 10 regardless of ageCALL catalog.system.expire_snapshots(  table => 'sales.transactions',  older_than => TIMESTAMP '2026-07-14 00:00:00',  retain_last => 10);-- rewrite manifest files so metadata reads don't fan out across thousands of small manifestsCALL catalog.system.rewrite_manifests('sales.transactions');-- remove orphaned data files left by failed/aborted writesCALL catalog.system.remove_orphan_files(table => 'sales.transactions');