A Razorpay-style payments team gets a Monday morning alert: last week's data platform cost jumped 4x even though no major pipeline shipped and no schema changed. Two separate, unrelated things happened in the same week. First, an analyst ran a bad `MERGE` against a lakehouse table and the team had to restore last Tuesday's version to recover - except nobody had actually set up a real backup, they were relying on the warehouse's short built-in history window, which had already expired for the oldest data they needed. Second, a raw Parquet folder that had been accumulating small files for months finally got slow enough that every query against it multiplied in cost, and the compute burning through that data added directly to the same bill. Neither of these is a new problem. Both are solved by ideas covered in this module - and both are also commonly misunderstood in ways that make things worse, not better, which is exactly why this module spends real time on what these tools are not, not just what they are. * This module covers two connected ideas: how a modern cloud warehouse works internally (the concepts shared across Snowflake, BigQuery, and Redshift, not a syntax reference for any one of them), and how a **lakehouse** brings warehouse-like reliability to data sitting in a plain data lake, using **Delta Lake** as the reference implementation. * Both ideas exist because raw files in cloud storage and traditional warehouses each solve half the problem - files are cheap and flexible but historically lack table-level guarantees under concurrent writes, warehouses are reliable but can create tighter platform coupling. * You reach for these concepts any time you are debugging a cost spike, designing how raw data becomes analysis-ready data, or deciding whether a table belongs in a warehouse, a lakehouse, or a plain data lake. > 📌 **Remember:** This module is explicitly tiered. Warehouse architecture and the > Medallion Architecture with Delta Lake are Must Know - build real depth there. > Multi-cluster concurrency, cross-account sharing, Iceberg, and deep performance > tuning are Good to Know or Optional - you should recognise them, not master them > here. ---
### Compute you can pause, and data that survives being wrong The Data Warehouse Design module already covered storage/compute separation as a general idea. In platforms that expose an independently managed compute cluster as a first-class concept - Snowflake is the clearest example - this compute resource is called a **virtual warehouse**. Other platforms achieve the same underlying separation differently: some expose serverless, automatically-scaled compute instead of a cluster you size and name yourself. The diagram below shows the general shape of the idea, not one vendor's exact implementation. +------------------+ +------------------+ +------------------+ | compute resource | | compute resource | | compute resource | | (ETL, large) | | (BI, small) | | (ad hoc, medium) | +------------------+ +------------------+ +------------------+ \ | / \ | / +---------------------+----------------------+ | shared underlying storage | +----------------------------------------------+ Because compute is separate from storage, a team can run its heavy nightly ETL on one compute resource, its BI dashboards on a second, and an analyst's exploratory queries on a third - all against the exact same data, with largely isolated compute, which reduces direct contention between those workloads. They can still share underlying storage systems, metadata or control-plane services, and other platform-level resources, so "isolated" does not mean "completely unaffected by each other" - it means the heavy lifting of query execution does not queue behind unrelated work the way it would on one shared cluster. ### Loading data - bulk and continuous patterns Warehouses generally support two loading shapes: a **bulk load** pattern (a `COPY`-style command that reads compressed files from cloud storage in parallel, the same pattern covered in the Data Warehouse Design module), and a **continuous or streaming load** pattern that ingests smaller batches of new data automatically and frequently, so a table stays close to real time without a person manually triggering each load. Exact continuous-load mechanisms differ by vendor - some use a managed always-on ingestion service, others rely on frequent small scheduled loads - but the shape of the trade-off is the same everywhere: continuous loading keeps data fresher at the cost of more, smaller write operations, which can fragment a table's physical layout over time if left unmanaged. ### Time Travel **Time Travel** lets you query a table as it looked at a past point in time, because the warehouse retains previous versions of the table's data for a configurable retention window, rather than immediately discarding old data the moment a row is updated or deleted. ```text -- CONCEPTUAL PATTERN: syntax differs by vendor SELECT * FROM payments_fct AT (TIMESTAMP => '2026-08-14 09:00:00'); SELECT * FROM payments_fct BEFORE (STATEMENT => 'a1b2-bad-merge-statement-id'); ``` > 🔴 **Common Mistake:** Treating Time Travel as a backup strategy. It has a > retention window - often a matter of days, sometimes longer on higher tiers - and > it exists for debugging a recent mistake or recovering from an accidental change > a few hours or days old, not for long-term disaster recovery or compliance > retention. The fix: use Time Travel to investigate and recover from *recent* > incidents, and maintain a genuine backup or archival strategy - a separate copy of > critical data, actually tested for restoration - for anything that needs to > survive longer than the retention window, or that needs to survive the retention > window itself being misconfigured. ### Query performance basics * **Clustering keys** influence how data is physically organised, similar to the clustering concept from the Data Warehouse Design module, letting the engine skip data more effectively for queries filtering on that key. * **Result caching**, where a platform supports it, means a query that matches an earlier one closely enough to satisfy that platform's cache-validity rules - the query text, and the underlying data not having changed, are common conditions - can return a cached answer instead of fully recomputing it, avoiding some or all of the recomputation cost. Exactly when a cache hit applies is platform-specific. * **Reading a query profile** - the execution plan a warehouse shows you after a query runs - is how you actually diagnose whether a slow query is a data-scanned problem or a compute problem, the same two-dimension framework from the Data Warehouse Design module. ### Cost management Compute pricing varies meaningfully by platform. Some warehouses charge for active compute cluster runtime, others charge for serverless compute consumption, and some query-on-storage engines price primarily by data scanned rather than compute time at all. **Auto-suspend** - where a platform offers it - automatically pauses idle provisioned compute after a period of inactivity, and **auto-resume** brings it back the moment a new query arrives. Auto-suspend matters most specifically on platforms where idle provisioned compute keeps accumulating cost regardless of whether a query is running against it. > 📌 **Engineering Decision:** Where auto-suspend is available, set it aggressively > - a short idle timeout, often a minute or two - for any compute resource used for > ad hoc or exploratory work, since idle compute time with no query running is pure > waste under a runtime-based pricing model. Consider a longer timeout, or a > dedicated always-on resource, only where the cost of frequent resume delays > genuinely outweighs the cost of idle compute - a dashboard serving continuous > traffic through business hours, for example. Misconfigured or disabled > auto-suspend is one of the most common silent sources of warehouse cost bloat, > precisely because nobody notices idle compute the way they notice a slow query. **Concept check:** A compute resource has auto-suspend set to 60 minutes instead of 1 minute, on a platform that bills for active runtime. What is the actual cost impact if queries only run for a few seconds every hour? ---
You do not need hands-on mastery of these - recognise them and know what problem each one solves. * **Zero-copy cloning** creates an instant, full copy of a table or database for development or testing, without physically duplicating the underlying data - changes to the clone do not affect the original, and the storage cost only grows as the clone diverges. This is what makes "spin up a full copy of production for testing" cheap instead of a multi-hour data copy job. * **UNDROP-style recovery** restores an accidentally dropped table within the same retention window Time Travel uses, without needing a full backup restore. * **Streams and Tasks** (or an equivalent, depending on vendor) provide lightweight change tracking and scheduling built into the warehouse itself - a "stream" marks which rows changed since it was last read, and a "task" runs on a schedule, together forming a simple in-warehouse alternative to a full CDC pipeline for cases that do not need Debezium-level sophistication. * **Multi-cluster warehouses** scale a single logical warehouse across multiple compute clusters automatically when concurrent query load increases, so many simultaneous users do not queue behind each other. * **Cross-account data sharing** lets one organisation grant another read access to specific data without physically copying or exporting it - useful for a company sharing a curated dataset with a partner or vendor without operating a separate export pipeline. These features solve real problems mainly at larger scale - many concurrent users, frequent dev/test cycles, formal external data-sharing agreements - and different warehouse vendors implement each of them differently or not at all. Awareness here is the goal, not implementation depth. ---
A plain data lake stores files - often Parquet, for the columnar benefits covered in the Data Warehouse Design module - directly in cloud storage, with no additional layer managing them. This is cheap and flexible, and it breaks down in three specific, common ways once real pipelines start writing to it concurrently. * **No ACID transactions.** If a Spark job is halfway through overwriting a folder of Parquet files when it crashes, readers querying that folder at that moment can see a mix of old and new files - an inconsistent, partially-written state, with no mechanism to prevent it. * **No schema enforcement.** Nothing stops a new pipeline run from writing a file with a renamed or differently-typed column into the same folder as files from the old schema, silently corrupting downstream reads. * **No efficient row-level updates or deletes.** Parquet files are immutable once written - correcting a single wrong row means rewriting the entire file that row lives in, which does not scale to frequent updates. **Delta Lake** solves all three by adding a transaction log and table protocol on top of the Parquet files themselves. Writers record every table change - an insert, an update, a delete, a schema change - as a versioned transaction entry in this log. Readers use the log to resolve a consistent snapshot: exactly which set of underlying files belongs to the table version they are reading. It is this combination of versioned transaction records plus a defined protocol for resolving them - not the log file alone - that produces the ACID guarantee on top of files that individually offer none. your_table/ ├── _delta_log/ <- the transaction log: what makes it "Delta" │ ├── 00000000000000000000.json │ ├── 00000000000000000001.json │ └── ... ├── part-0001.snappy.parquet <- still plain Parquet underneath ├── part-0002.snappy.parquet └── ... > **Note:** This is also the mechanism behind Delta Lake's own Time Travel. Because > every version of the table is just a specific point in this log, querying "the > table as of an hour ago" means reading the table state as of that version - the > same underlying idea as warehouse Time Travel above, implemented on top of files > you control instead of inside a managed warehouse. ### MERGE INTO - the operation that makes updates practical `MERGE INTO` runs an insert, update, or delete as a single atomic transaction against the Delta log - either the whole operation succeeds and the log records one consistent new version, or it fails and the table is unchanged. This is the ACID guarantee a plain Parquet folder cannot offer, and it is the mechanism behind CDC upserts in a lakehouse table. ```sql -- Delta Lake MERGE: upsert changed customer records from a CDC source, keyed -- so each source row already carries the action to take MERGE INTO dim_customer AS target USING customer_updates AS source ON target.customer_id = source.customer_id AND target.is_current = true WHEN MATCHED AND target.city != source.city THEN UPDATE SET target.valid_to = source.effective_date, target.is_current = false WHEN NOT MATCHED THEN INSERT (customer_id, city, valid_from, valid_to, is_current) VALUES (source.customer_id, source.city, source.effective_date, '9999-12-31', true) ``` > 🔴 **Common Mistake:** Assuming the `MERGE` shown above fully implements SCD Type > 2 by itself. It does not, and this is a genuinely common trap. When a source row > matches an existing current row and the city has changed, the `WHEN MATCHED` > branch expires the old row - but that same source row does not then separately > become "not matched" within the same `MERGE`, so the new current version is never > inserted by this statement alone. A single `MERGE` can express *either* "expire > the old row" *or* "insert the new row" for a given source row, not both from one > match. The fix is to run this as two coordinated steps - identify which rows > actually changed first, then expire the old versions and insert the new ones as > two separate, correctly sequenced operations. The Hands-On Lab below implements > this correctly, step by step. ### Schema evolution Delta Lake can enforce schema on write - rejecting a write with an unexpected column - or, when explicitly enabled, evolve the schema to accommodate new columns cleanly, recorded as a new entry in the transaction log rather than silently mixing incompatible files together the way a plain Parquet folder would. **Concept check:** A Spark job crashes halfway through writing a large batch to a plain Parquet folder. What might a query running at that exact moment see? What would the same situation look like against a Delta table instead? ---
The **Medallion Architecture** is a widely used pattern for organising lakehouse pipelines into three progressively cleaner layers, especially common in the Delta Lake and Databricks ecosystem. It is not the only valid way to structure a lakehouse, but it is common enough that most Delta Lake pipelines you encounter in the field will follow this shape or something close to it. Bronze Silver Gold +-------------+ +----------------+ +---------------------+ | raw, as- | -----> | cleaned, joined,| ---> | business aggregations| | received | | deduplicated, | | ready for dashboards | | | | validated | | and reporting | +-------------+ +----------------+ +---------------------+ * **Bronze** holds data as close to exactly as it arrived from the source as practical - raw JSON from an API, a raw CDC feed, an unmodified file drop. It is generally append-oriented, avoiding destructive transformations or overwrites of raw history unless a real requirement - retention limits, privacy regulation, a legal deletion request - genuinely demands it. This preserves the ability to reprocess history from scratch if a downstream transformation turns out to be wrong, which is the entire point of keeping it, while still acknowledging that "never overwritten" is a strong default, not an absolute law with no exceptions. * **Silver** applies cleaning, joins, deduplication, and validation - null handling, type casting, standardising formats - producing data that is correct but not yet shaped for any specific business question. * **Gold** holds business-ready aggregations - daily revenue by merchant, daily active users - the tables analysts and dashboards actually query, conceptually similar to the mart layer from the dbt module. ```python ## PySpark: writing the Bronze layer - append-oriented, close to raw raw_events_df.write.format("delta").mode("append").save("/lake/bronze/upi_events") ## PySpark: Silver layer - cleaned and deduplicated from Bronze from pyspark.sql.functions import col silver_df = ( spark.read.format("delta").load("/lake/bronze/upi_events") .filter(col("amount_rs").isNotNull()) ## drop rows with no amount .dropDuplicates(["transaction_id"]) ## remove duplicate events .withColumn("amount_rs", col("amount_rs").cast("decimal(10,2)")) ) silver_df.write.format("delta").mode("overwrite").save("/lake/silver/upi_transactions") ``` > **Note:** This lab-scale example uses `mode("overwrite")` for Silver to keep the > pipeline simple to follow. Production pipelines more often update only the > affected partitions, or use incremental `MERGE` logic to upsert just the changed > and new records, rather than rewriting the entire table on every run - full > overwrites get expensive fast as a table grows. > 🔴 **Common Mistake:** Skipping the Bronze layer and transforming directly from > source to Gold, because it feels like fewer moving parts. When a bug is later > discovered in the transformation logic, there is no raw history left to reprocess > from - the original data was never preserved anywhere, only its transformed > output. The fix: always land raw data in Bronze first, even when the Silver and > Gold transformations feel simple enough to do in one step. > 💡 **Practice:** Sketch what Bronze, Silver, and Gold would each contain for a > Hotstar-style video streaming events pipeline - raw player events, cleaned > sessions, and a business-ready daily active users table. Write one sentence per > layer describing what changes between it and the layer before it. **Real-world troubleshooting scenario:** A Zomato-style Gold table showing daily order counts by city is suddenly reporting far more orders than expected for one specific day. The Silver layer transformation runs a `dropDuplicates()` on `order_id`, but a source system bug that day assigned the same `order_id` to two genuinely different orders placed seconds apart. What happened, and what would having Bronze data actually let you do about it? *(The deduplication step correctly removed what it believed were duplicate `order_id` values, but the underlying assumption - that `order_id` uniquely identifies one order - was violated by a source system bug, silently dropping one of the two real orders. Because Bronze still holds every raw event exactly as received, the team can inspect the original records for that `order_id`, confirm they represent two distinct events using other fields such as timestamp or amount, fix the deduplication logic to key on a combination of columns that is actually unique, and reprocess Silver and Gold from Bronze - none of which would be possible if the pipeline had written straight to Gold and discarded the raw data.)* ---
**Apache Iceberg** solves largely the same problem as Delta Lake - ACID transactions, schema evolution, and time travel on top of files in a data lake - as an open table format with broader engine support across the ecosystem, versus Delta Lake's origin and especially deep integration within the Databricks/Spark ecosystem. Both are legitimate, actively developed choices; which one fits depends on which compute engines and platforms a team already uses, not on one format being categorically better. | Aspect | Delta Lake | Apache Iceberg | |:---|:---|:---| | Origin | Databricks | Netflix, now Apache Software Foundation | | Strongest integration | Spark and Databricks-native tools | Broad multi-engine support: Spark, Trino, Flink, and others | | Core capability | ACID transactions, schema enforcement/evolution, Time Travel | ACID transactions, schema evolution, Time Travel | **Compaction** combines many small files that accumulate from frequent small writes - like the continuous-load pattern from earlier - into fewer, larger files, which read faster because the engine spends less time opening and closing many small files relative to the amount of data in each. **Z-ordering** physically co-locates data that is similar across multiple columns at once, improving skip effectiveness for queries that filter on more than one column together - a deeper, more advanced version of the clustering idea from the Data Warehouse Design module. Neither compaction strategy details nor Z-ordering tuning are required for a working understanding here - know that both exist as performance levers, and that unmanaged small-file accumulation from frequent writes is a real, common cause of a lakehouse table quietly getting slower over months. ---
A Razorpay-style payments team gets a Monday morning alert: last week's data platform cost jumped 4x even though no majo...
Compute you can pause, and data that survives being wrong The Data Warehouse Design module already covered storage/compu...
You do not need hands-on mastery of these - recognise them and know what problem each one solves. Zero-copy cloning crea...
A plain data lake stores files - often Parquet, for the columnar benefits covered in the Data Warehouse Design module - ...
The Medallion Architecture is a widely used pattern for organising lakehouse pipelines into three progressively cleaner ...
Apache Iceberg solves largely the same problem as Delta Lake - ACID transactions, schema evolution, and time travel on t...
Prerequisites: PySpark and Delta Lake installed locally. > 💡 Tip: For a reproducible production environment, pin specif...
Concept What it does Tier Compute/storage separation Independently sized, independently billed compute Must Know Time Tr...
Treating Time Travel as a backup strategy happens because it feels like a backup - you can query an old version - but it...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.