Data Lakes, Data Warehouses & the Lakehouse

Databricks/Delta, Snowflake, BigQuery, Redshift — with practical Kotlin + Java + Spring Boot you can walk through in an interview. Version-anchored to mid-2026 (Spark 4.1.x, Delta 4.x, Unity Catalog OSS, Iceberg REST).

The mental model (the one idea everything else derives from)#

Analytics is a scan problem, not a lookup problem. Your operational systems answer "give me row 42 and mutate it" thousands of times a second. Your analytical systems answer "scan two billion rows and give me one number." Those are opposite physics, so they get opposite machines. Every design choice below — columnar files, partitioning, MPP, separation of storage and compute, warehouse-vs-lake-vs-lakehouse — is one of two moves: scan less data, or scan it in more parallel.

Two corollaries fall straight out of that and carry most of the interview:

1. Warehouse vs lake is one axis: when is structure enforced — on write or on read? A warehouse enforces schema and quality on write (you model first, load clean data, query fast — but you pay up front and can't store what you didn't model). A lake stores raw files on cheap object storage and enforces schema on read (store anything now, decide structure later — but with no guardrails it rots into a "swamp"). Neither is strictly better; they trade flexibility now against reliability later.

2. The lakehouse dissolves the axis by adding a transaction log over the files. Delta Lake / Apache Iceberg / Apache Hudi keep the lake's cheap open columnar files (Parquet on S3) but lay an ACID metadata layer on top, so you get warehouse guarantees — transactions, schema enforcement, time travel, MERGE — on lake economics. That's why "the lakehouse" is the default modern answer, and why the four big warehouses (Snowflake, BigQuery, Redshift, Databricks) are all racing to read/write the same open table formats.

If you can only remember one sentence for the interview: "Operational systems optimize row lookups; analytical systems optimize column scans; a warehouse pushes structure to write-time, a lake pushes it to read-time, and a lakehouse gets both by putting an ACID log over open columnar files."

Keep one more distinction crisp, because interviewers probe it: a table format (Delta/Iceberg/Hudi) is not a file format (Parquet/ORC) and not a query engine (Spark/Trino/Snowflake). The file format is how one file is laid out on disk; the table format is the metadata/log that turns a directory of files into a transactional table; the engine is the compute that reads them. The 2026 story is that all three layers are decoupling and becoming interchangeable.

Table of contents#

1. The operational/analytical split — why analytics needs its own machine
2. Columnar storage & file formats — the physical foundation (Parquet/ORC/Avro + code)
3. The data warehouse — dimensional modeling, star schemas, SCD, MPP
4. The data lake — object storage, schema-on-read, and the swamp
5. The lakehouse & Delta Lake — ACID on object storage (the centerpiece + code)
6. Spark — the compute engine behind Databricks (shuffle, skew, streaming + code)
7. Getting data in — ETL vs ELT and ingestion patterns (CDC/Kafka + code)
8. The warehouses compared — Snowflake / BigQuery / Redshift / Databricks SQL
9. How your Spring Boot services actually integrate (the code you'll be asked about)
10. Staff+ concerns — cost, performance, governance, reliability
11. Problems & how to solve them (Problem → Root cause → Fix)
12. Interview framing — how to sound senior
13. Cheat-sheets
14. Self-test

1. The operational/analytical split — why analytics needs its own machine#

OLTP vs OLAP#

Your microservices sit on OLTP (Online Transaction Processing) stores — Postgres, MySQL, DynamoDB. They're tuned for many small, concurrent, latency-sensitive transactions that read and write a few rows by key: place an order, debit a balance, update a charging session. Rows are stored together (row-oriented) because a transaction touches most columns of a few rows.

Analytics is OLAP (Online Analytical Processing): few users, huge queries, each scanning a large fraction of a table to aggregate: "revenue by region by month for three years." You touch a few columns of billions of rows. Storing rows together is now the wrong layout — to sum one column you'd drag every other column through memory.

The #1 interview trap: "Why not just run the analytics query on the production Postgres replica?" Weak answer: "it's slow." Staff answer: "Three reasons. (1) Physics — a row store reads whole rows off disk to sum one column, so a big aggregate does far more I/O than a column store; (2) isolation — an OLAP scan holds resources and trashes the buffer cache that OLTP point-reads depend on, so you couple the reliability of your analytics to your checkout path; (3) modeling — the normalized 3NF schema that's right for transactional integrity forces expensive joins for every analytical question. Different access pattern → different machine. You bridge them with a pipeline, not a replica."

The two-plane architecture (and how the planes connect)#

Picture two planes:

• Operational plane: microservices + their OLTP stores + Kafka. Source of truth. Low latency, high concurrency, row-oriented.
• Analytical plane: lake / warehouse / lakehouse. Derived data. High throughput, columnar, cheap-per-TB.

They are connected by ingestion, and how you connect them is itself a system-design decision you should be able to defend:

• Batch export / bulk load — nightly dump of a table to the lake. Simple, high-latency, re-reads everything.
• Change Data Capture (CDC) — stream the OLTP write-ahead log (Debezium → Kafka → lake). Near-real-time, low load on the source, exactly what you want for keeping an analytical copy fresh. (This is the bridge; it's why CDC, outbox, and Kafka show up in every serious data-platform design — see your CDC and inbox/outbox guides.)
• Event streaming — services emit domain events to Kafka; a streaming job lands them in the lake. The lake becomes the durable, replayable record of everything that happened.

The Staff-level insight: the analytical plane is a giant read model. It's CQRS at platform scale — the operational plane owns writes and invariants; the analytical plane is an eventually-consistent, denormalized, query-optimized projection. Everything you know about read models, idempotency, and eventual consistency transfers directly.

2. Columnar storage & file formats — the physical foundation#

This is the layer interviewers use to tell "used a warehouse" from "understands one." Get it right and the rest of the guide is downstream of it.

Row vs columnar layout — the physics#

Take a table (user_id, country, amount). On disk you can lay bytes out two ways:

For SELECT SUM(amount) WHERE country='US':

• Row store must read every row (every column) off disk, discard user_id, filter country, keep amount. I/O ≈ whole table.
• Column store reads only the country and amount column files. user_id is never touched. I/O ≈ 2/3 less here, orders of magnitude less on wide tables (100s of columns, you read 3).

Three compounding wins make columnar dramatically faster for OLAP — this is the list to recite:

1. Projection / less I/O — read only the columns the query references. Wide tables make this enormous.
2. Compression — a column holds one data type with low cardinality and local similarity, so it compresses far better than a heterogeneous row. country is 200 distinct strings across a billion rows → dictionary encoding (store the 200 once, then tiny integer codes) + run-length encoding (RLE) collapses long runs. Less bytes on disk = less I/O = faster. (10× compression is routine.)
3. Vectorization — a column is an array of same-type values, so the engine processes it in tight CPU-cache-friendly loops using SIMD, instead of chasing per-row branches. (This is what Databricks Photon and modern engines exploit.)

Plus predicate pushdown / data skipping: the format stores min/max statistics per chunk, so WHERE amount > 1000 can skip entire chunks whose max is ≤ 1000 without reading them. Partitioning + clustering (Section 10) make this brutally effective.

Parquet — the format you must be able to describe#

Apache Parquet is the de-facto open columnar file format (ORC is the close alternative from the Hive world). Interview-level internals:

• A Parquet file is split into row groups (a horizontal slice of rows, typically ~128 MB). Row groups are the unit of parallelism — one task per row group.
• Within a row group, each column is a column chunk, stored contiguously (that's the columnar part).
• Each column chunk is split into pages (the unit of encoding/compression, ~1 MB).
• The footer holds the schema and per-row-group, per-column statistics (min/max, null counts) — read last, it's the map that enables data skipping and lets a reader jump straight to the pages it needs.
• Encodings: dictionary encoding, RLE, bit-packing, delta encoding — chosen per column. Compression (Snappy is the default in Spark/Hive/Databricks, Zstd for a better ratio, gzip) is applied on top, per page.

Nested/repeated fields use definition & repetition levels (the Dremel model — the same idea that powers BigQuery). You don't need the algorithm; you need to know Parquet represents nested structures natively without flattening.

The three formats and when each is right#

The distinction to say out loud: Avro is row-based — it's for moving records (Kafka topics, the write path, schema-registry-governed messages — see your schema-evolution guide) where you consume the whole record. Parquet is columnar — it's for querying at rest. A common pipeline is Avro on the wire → Parquet at rest.

Code — writing and reading Parquet directly (Java + Kotlin)#

You rarely hand-roll Parquet in app code (Spark/warehouses do it for you), but being able to is a strong signal you understand it's just files. Using parquet-avro over the Hadoop APIs:

Java

Kotlin

The point to make in the room: "It's just files on object storage plus a footer of statistics. There's no server. Whoever reads it — Spark, Trino, Snowflake, DuckDB, my Spring app — brings its own compute. That decoupling of storage from compute is the whole reason the lake is cheap and open."

3. The data warehouse — dimensional modeling, star schemas, SCD, MPP#

A data warehouse is a system that enforces schema-on-write: you define tables and types up front, transform data to fit them, load clean/conformed data, and get fast, governed SQL over it. Historically these were Teradata/Oracle appliances; today they're cloud services (Snowflake, BigQuery, Redshift, Databricks SQL) that share three architectural traits:

• Columnar storage (Section 2) for scan speed.
• MPP — Massively Parallel Processing. The query is split across many nodes/workers that each scan a shard of the data in parallel, then results are merged. Scan-more-in-parallel, made physical.
• Separation of storage and compute (the cloud-era shift — more in Section 8). Data lives on cheap object storage; compute clusters spin up against it and scale independently. This is why you can run a huge query without keeping a huge cluster on 24/7.

Dimensional modeling — the language of the warehouse#

Kimball dimensional modeling is the modeling vocabulary interviewers expect. You split the world into:

• Facts — the measurements of a business process, one row per event, at a defined grain. fact_sales = one row per line item, with measures (quantity, amount) and foreign keys to dimensions. Facts are tall and thin and append-mostly.
• Dimensions — the descriptive context you filter/group by. dim_customer, dim_product, dim_date, dim_store. Wide, short, textual, denormalized.

Arrange facts in the center with dimensions around them and you get a star schema:

If you further normalize dimensions into sub-tables (e.g. dim_product → dim_category), you get a snowflake schema — more normalized, fewer redundant strings, but more joins and usually not worth it on columnar storage where the redundant strings compress away. Default to a star; reach for snowflake only when a dimension is huge and volatile.

Why denormalize at all, when your OLTP brain screams 3NF? Because the analytical access pattern is "filter and group by descriptive attributes," and a star answers any such question with one join layer — predictable, fast, and easy for a BI tool to navigate. Normalization optimizes for write-integrity you don't need here (the warehouse is a read model; the OLTP source already enforced integrity).

Grain is the single most important modeling decision — "what does one fact row mean?" Declare it explicitly ("one row per order line per shipment") and every measure's additivity follows. Measures come in three flavors, and mixing them up is a classic bug:

• Additive — sum across all dimensions (amount, quantity).
• Semi-additive — sum across some dimensions but not time (account_balance, inventory_on_hand — you don't add Monday's balance to Tuesday's; you take the latest or an average).
• Non-additive — can't be summed at all (ratio, percentage, unit_price — store the numerator and denominator as additive facts and compute the ratio at query time instead).

Slowly Changing Dimensions (SCD) — the classic interview question#

A customer moves from Berlin to Munich. A sale happened last year while they lived in Berlin. If you overwrite the city, last year's revenue silently teleports to Munich and your history is now a lie. SCD is how you handle dimension attributes that change over time:

Type 2 is the money answer. Say: "I'd model it Type 2 — a new dimension row per change with valid_from, valid_to, and is_current, keyed by a surrogate key. The fact stores the surrogate that was current at event time, so 'revenue by city' is historically correct and I can still filter is_current for the present. Type 1 is a lossy overwrite — only when history genuinely doesn't matter."

Type-2 upserts are exactly where the lakehouse MERGE (Section 5) earns its keep. Canonical shape:

Kimball vs Inmon vs Data Vault (name-drop, don't lecture)#

• Kimball (bottom-up): build dimensional marts per business process, tie them together with conformed dimensions. Fast to value, BI-friendly. The one you'll use.
• Inmon (top-down): build one normalized (3NF) enterprise warehouse first, derive dimensional marts from it. More governance up front, slower.
• Data Vault: hubs/links/satellites — an auditable, insert-only, highly agile integration layer for large enterprises. Know the name and that it's audit/lineage-oriented.

One line that sounds senior: "Kimball dimensional modeling still describes the gold layer even in a lakehouse — the storage moved to Parquet/Delta on S3, but facts, conformed dimensions, and SCD Type 2 didn't stop being the right model for BI."

4. The data lake — object storage, schema-on-read, and the swamp#

A data lake inverts the warehouse's contract: store raw data of any shape (structured, semi-structured JSON/CSV, unstructured logs/images) as files on cheap object storage (Amazon S3, Azure Data Lake Storage Gen2, Google Cloud Storage), and enforce schema on read — whoever queries it imposes structure at query time.

Why anyone wanted this:

• Cost & scale. Object storage is ~$0.02/GB/month, effectively infinite, and durable (11 nines). You dump everything and worry later.
• Schema-on-read flexibility. You don't have to model before you land data. Store the raw clickstream / IoT / logs now; a data scientist decides the schema when they explore it. You never lose data because you "didn't have a column for it."
• Open formats + decoupled compute. Files are Parquet/JSON/Avro that any engine reads — Spark, Trino/Presto, Athena, Snowflake external tables, DuckDB. No vendor owns your bytes. Compute is BYO and elastic.
• Any workload. ML/data science wants raw files and Python, not SQL tables. The lake feeds pandas/Spark/PyTorch as happily as it feeds SQL.

Typical layout is zones (raw/landing → cleaned/curated → serving), which foreshadows the medallion architecture.

Why the raw lake broke — the "data swamp"#

Schema-on-read's flexibility is also its curse. A bare directory of files on S3 has no table semantics, and that absence bites in specific, nameable ways — this list is the motivation for the lakehouse, so know it cold:

• No ACID / no transactions. Two writers appending concurrently, or a job that dies mid-write, leave half-written, corrupt, or duplicated data. A reader can see a partial write. There's no atomic "commit."
• No schema enforcement. Nothing stops someone writing amount as a string in today's files. Readers discover the poison at query time — "schema-on-read" degrades into "schema-on-cross-your-fingers."
• No consistency guarantees historically. (S3 became strongly read-after-write consistent in Dec 2020, but listing a prefix to find "the current set of files" still has no atomicity — a reader can list a file that's mid-replacement.)
• The small-file problem. Streaming appends produce millions of tiny files. Every query pays a per-file open/list overhead and metadata lookups swamp the actual scan. Performance collapses. (See Problems §11.)
• No updates or deletes. Files are immutable; there's no UPDATE/DELETE/MERGE. GDPR "delete this user" means rewriting whole partitions by hand.
• No isolation / versioning. No snapshot isolation, so a long read racing a write sees a shifting, inconsistent picture; no time travel to reproduce yesterday's numbers.

The result is a data swamp: technically all the data is there, but it's untrustworthy, slow, ungoverned, and no one can get a correct answer out of it. This failure — lake economics but no reliability — is precisely the gap the lakehouse was invented to close.

5. The lakehouse & Delta Lake — ACID on object storage (the centerpiece)#

The lakehouse is one sentence: warehouse guarantees on lake storage. Keep the cheap, open, columnar files on object storage; add a transaction log that turns that directory of files into a real table with ACID transactions, schema enforcement, updates/deletes, and time travel. The three implementations are Delta Lake (Databricks), Apache Iceberg (Netflix-origin, now the open-ecosystem favorite), and Apache Hudi (Uber-origin, streaming-upsert heritage). Databricks = Delta, so we go deep on Delta and compare at the end.

How Delta Lake actually works (this is the part that impresses)#

A Delta table is a directory:

• The data is just Parquet files. No magic on the data side.
• _delta_log/ is an ordered, append-only transaction log. Each commit is a JSON file listing actions: add a file, remove a file, change metadata/schema, set a transaction marker. The current state of the table = replay the log (which files are logically present, what's the schema). Files on disk that aren't add-ed in the log don't exist as far as the table is concerned — that's how updates/deletes work without mutating Parquet.
• A commit is atomic because writing the next log file (...0011.json) is a single atomic "create if not exists." Two writers who both try to create 0011.json — one wins, the loser retries against the new state. That's optimistic concurrency control, and it's what gives you ACID + snapshot isolation on top of dumb object storage. (On S3, atomic "put-if-absent" historically needed a coordination service; S3 added conditional writes in 2024, which Delta/Iceberg now use, removing that wart.)
• Checkpoints periodically collapse the log into a Parquet snapshot so readers don't replay thousands of JSONs — read the latest checkpoint + the few JSONs after it.
• Reads are snapshot-isolated: a reader pins the log version it started at, so a concurrent writer never shows it a torn picture. Time travel is just "read the table as of log version N / timestamp T."

That mechanism — immutable data files + an atomic, append-only log of which files are live — is the single most valuable thing to be able to explain. It's the same "the log is the source of truth" idea as event sourcing and Kafka; Delta is a log-structured table.

What that buys you (the feature list, mapped to the swamp problems)#

• ACID transactions → no torn/duplicate writes; concurrent writers are serialized by OCC.
• MERGE / UPDATE / DELETE → upserts and GDPR deletes become one statement (rewrites only the affected files, records the swap in the log).
• Schema enforcement (rejects writes that don't match) + schema evolution (mergeSchema to add columns deliberately) → no more poison files. (See your schema-evolution guide — same "past writer vs future reader" tension, enforced at the table.)
• Time travel → VERSION AS OF / TIMESTAMP AS OF for reproducibility, audits, and rollback of a bad load.
• OPTIMIZE (compaction) → coalesces small files into ~128 MB–1 GB files, killing the small-file problem. Z-ordering / liquid clustering co-locates related values so data-skipping prunes harder.
• VACUUM → physically deletes files that the log no longer references and are older than a retention window (default 7 days). Note the interaction: VACUUM bounds how far back time travel works.
• Change Data Feed (CDF) → read the row-level changes a table produced, so downstream tables/streams consume just the delta (CDC, but for your gold tables).
• Deletion vectors → mark rows deleted in a side file instead of rewriting the whole Parquet ("merge-on-read"), making deletes/updates cheap; the rewrite happens later at OPTIMIZE.

The medallion architecture (bronze/silver/gold)#

The reference way to organize a lakehouse — name it in any lake design question:

• Bronze — raw, append-only, exactly as ingested (schema-light, full history, replayable). Your durable landing zone.
• Silver — cleaned, conformed, deduplicated, typed, joined. The queryable "single source of truth" for the org.
• Gold — business-level aggregates / dimensional marts (star schemas!) tuned for specific consumers (BI dashboards, ML features, a reverse-ETL feed).

Data flows bronze → silver → gold, each a Delta table, each transformation an auditable, restartable job. This is where Kimball's star schema lives in the modern stack: the gold layer.

Table-format wars & the 2026 convergence (say this and you sound current)#

• Delta Lake — tightest with Databricks/Spark; excellent engine integration; open-sourced under the Linux Foundation.
• Apache Iceberg — the open-ecosystem winner of the format war: hidden partitioning, robust schema/partition evolution, and a REST catalog spec that every engine is adopting. Snowflake, BigQuery, Redshift, Trino, Flink, Databricks all read/write it.
• Apache Hudi — strongest heritage in streaming upserts / incremental pulls (copy-on-write vs merge-on-read); more niche now.

The headline: the formats are converging, not fragmenting. Databricks acquired Tabular (the Iceberg company founded by Iceberg's creators) in 2024; Delta UniForm writes Delta that Iceberg and Hudi clients can read by generating their metadata alongside; Unity Catalog was open-sourced (2024) and implements the Iceberg REST Catalog API; Snowflake pushed its Polaris catalog to the Apache Foundation (Apache Polaris) and ships Iceberg tables + an Open Catalog / Horizon. The Staff-level reading: "The bet in 2026 is on open table formats + an open REST catalog so storage is truly decoupled from the engine. I'd store gold as Iceberg or Delta-with-UniForm behind an open catalog, and let Databricks, Snowflake, or Trino query the same bytes — that's how you avoid lock-in while keeping best-of-breed compute."

Code — Delta Lake on Spark (Java + Kotlin)#

Spark has first-class Java and Kotlin APIs (Kotlin just calls the Java/Scala API). This is the "inside Databricks" code.

Java — create, upsert (SCD-friendly MERGE), time travel

Kotlin — same operations, plus schema evolution and a streaming append

The exactly-once claim is worth defending: Structured Streaming records the Kafka offsets it processed in the checkpoint, and the Delta commit that adds the output files is atomic and carries a transaction id, so a replay after failure neither drops nor double-writes. That's the streaming version of the idempotency you know from inbox/outbox.

6. Spark — the compute engine behind Databricks#

Databricks is a managed Apache Spark platform (plus Photon, Delta, Unity Catalog, notebooks, jobs). You will be asked how Spark executes, because that's where analytical performance is won or lost.

The execution model#

• Cluster: one driver (plans the job, holds the SparkSession, schedules tasks) + many executors (JVM processes that actually scan data and run tasks, each with slots/cores and memory).
• Abstractions: the low-level RDD (distributed collection) is now mostly internal; you write DataFrame/Dataset (a distributed table with a schema) and Spark SQL. Same engine underneath.
• Lazy evaluation. Transformations (select, filter, join, groupBy) build a logical plan but run nothing. Only an action (count, collect, write, show) triggers execution. This lets Catalyst, the query optimizer, rewrite the whole plan (predicate pushdown, column pruning, join reordering) before a byte moves.
• Catalyst → Tungsten → AQE. Catalyst optimizes the plan; Tungsten does whole-stage codegen + off-heap memory for CPU efficiency; Adaptive Query Execution (AQE) re-optimizes at runtime using actual data stats (coalesces shuffle partitions, switches join strategies, splits skewed partitions). Databricks' Photon is a C++ vectorized engine that replaces parts of this for big speedups.

Jobs → stages → tasks, and the thing that dominates performance: the shuffle#

An action becomes a job, split into stages at shuffle boundaries, each stage a set of tasks (one per partition) run in parallel on executors.

• Narrow transformations (map, filter) need only the local partition — no data movement, cheap.
• Wide transformations (groupBy, join, distinct, repartition) require rows with the same key to meet on the same executor → a shuffle: data is repartitioned by key and sent across the network, spilling to disk if it doesn't fit in memory.

The shuffle is the enemy. It's network + disk + serialization, and it's where most Spark jobs spend their time and most Spark jobs fail (OOM, spill, straggler tasks). Two shuffle-related pathologies you must be able to name and fix:

• Data skew — one key has vastly more rows than others (a null customer_id, a hot product). Its partition's task runs 100× longer than the rest; the whole stage waits on that one straggler, and it may OOM. Fixes: AQE skew join handling (on by default in Spark 3.2+/4.x), salting the hot key (append a random suffix to spread it, then aggregate twice), filtering nulls, or pre-aggregating.
• Big-table joins — a shuffle-sort-merge join reshuffles both sides. If one side is small, a broadcast hash join ships the small side to every executor and skips the shuffle entirely — often a 10× win. Spark auto-broadcasts under spark.sql.autoBroadcastJoinThreshold; you can force it with a hint.

Other levers: partitioning (right number of partitions — too few underuses the cluster, too many drowns in scheduling overhead; ~128 MB per partition is a good target), caching (persist() a DataFrame reused across actions so you don't recompute the DAG), and file layout (partition pruning + Z-order so tasks scan less).

Structured Streaming (in one breath)#

Spark's streaming model is a micro-batch over an unbounded table (a continuous mode exists but micro-batch is the norm). It gives exactly-once with a checkpoint (offsets + state) plus an idempotent/transactional sink (Delta), event-time windows with watermarks to bound late data, and stateful ops (transformWithState in Spark 4.x). It's how you keep the bronze/silver layers fresh from Kafka.

Version anchor (say the number, flag that it moves)#

Latest stable is Spark 4.1.2 (May 2026); Spark 4.0 (May 2025) made ANSI SQL mode the default (errors on overflow/bad casts instead of silently returning null — a correctness win), added the VARIANT type for semi-structured data, shipped a lightweight Spark Connect Python client (Spark Connect — the thin gRPC client that lets your app talk to a remote Spark cluster without bundling the whole engine — itself reached GA back in 3.5), a Python Data Source API, and streaming transformWithState. Flag versions as version-dependent — the concepts (lazy DAG, shuffle, Catalyst, skew) are stable; the release numbers aren't.

Code — a Spark aggregation with a broadcast join (Java + Kotlin)#

Java

Kotlin

Narration that signals seniority: "filter and column selection get pushed down into the Parquet/Delta scan so we read fewer bytes; the broadcast avoids shuffling the big fact table by shipping the small dimension to every executor; the only shuffle is the groupBy on category, which is low-cardinality so it's cheap. If category were skewed I'd lean on AQE or salt the hot key."

7. Getting data in — ETL vs ELT and ingestion patterns#

ETL vs ELT — and why ELT won#

• ETL (Extract → Transform → Load): transform data before loading, on a separate compute tier, so only clean/modeled data lands in the warehouse. The classic on-prem model — you had to, because warehouse storage/compute was scarce and expensive.
• ELT (Extract → Load → Transform): load raw data into the lake/warehouse first, then transform it in place using the warehouse's own elastic compute (usually SQL, orchestrated by dbt). This is the modern default.

Why ELT won: cloud warehouses/lakehouses made storage cheap and compute elastic, so there's no reason to throw away the raw data or stand up a separate transform tier. Land everything (bronze), transform with the warehouse engine (silver/gold), and you keep full history, reprocess by just re-running SQL when logic changes, and let analysts own transformations in version-controlled SQL (dbt) instead of waiting on a data-engineering ETL team. The trade: you're storing raw data you may never use, and undisciplined ELT recreates the swamp — which is why table-format governance and the medallion layers matter.

Ingestion patterns (pick per freshness/source)#

• Batch / bulk load — periodic dump (nightly COPY, a Spark job over yesterday's files). Simplest; high latency; fine for slow-moving dimensions.
• CDC (Change Data Capture) — stream the OLTP redo/WAL via Debezium → Kafka → a streaming job that MERGEs into a silver Delta/Iceberg table. Near-real-time, minimal load on the source, keeps the analytical copy continuously fresh. This is the backbone of "keep the warehouse in sync with production." (See your CDC guide; the warehouses now ship managed versions — Snowflake Snowpipe/Streams, Redshift/BigQuery Zero-ETL from operational DBs.)
• Streaming ingest — services emit domain events to Kafka; Spark/Flink Structured Streaming lands them in bronze. The lake becomes the durable, replayable event record.
• File ingestion — vendors/partners drop files in S3; an autoloader picks them up incrementally.

Orchestration & transformation (name them)#

Airflow / Dagster (DAG schedulers), dbt (SQL transformations, tests, lineage, docs — owns silver→gold in ELT), Databricks DLT / Jobs (declarative pipelines). The interview point: ingestion and transformation are separate concerns from storage; orchestration makes the medallion flow reproducible and observable.

Code — the ingestion boundary from a Spring Boot service#

Two idioms show up constantly. First, don't write analytical files from a request thread — publish an event; a streaming job lands it. Second, if you must micro-batch from a service, buffer and write whole files.

Kotlin — service emits analytics events to Kafka (the right boundary)

Java — a scheduled micro-batch export to the lake (when streaming is overkill)

Both snippets carry Staff signals: outbox-not-dual-write at the boundary (see your inbox/outbox guide), read-replica-not-primary for exports, ShedLock so a scheduled job fires once across replicas (see your locking guide), and batch-into-large-files to avoid the small-file problem.

8. The warehouses compared — Snowflake / BigQuery / Redshift / Databricks SQL#

All four are columnar + MPP + (increasingly) storage/compute-separated. What differs is how they separate, how they bill, and how open the storage is. This is the comparison you asked for and the one that separates "I've used one" from "I can pick the right one."

Snowflake — the separation-of-storage-and-compute archetype#

Three physically decoupled layers:

1. Storage — your data as columnar micro-partitions (50–500 MB of uncompressed data each, stored compressed) on cloud object storage, with automatic per-column min/max metadata for pruning. You don't manage partitions/indexes; Snowflake does.
2. Compute — virtual warehouses: independent MPP clusters (T-shirt sized XS…6XL) you spin up/down and resize per workload. Many warehouses read the same storage with zero contention — ETL, BI, and data science each get their own cluster, isolated. This is the killer feature.
3. Cloud services — the brain: query optimizer, metadata, security, transactions, and result cache.

Signature features: per-second credit billing with auto-suspend/auto-resume (a warehouse costs nothing while idle), Time Travel (query/restore up to 90 days), zero-copy cloning (instant metadata-only copies of a table/DB for dev/test), Snowpark (Java/Scala/Python DataFrame + UDFs pushed into Snowflake), and a strong marketplace/data-sharing story. In 2026 it reads/writes Apache Iceberg tables and ships the Open Catalog (managed Apache Polaris) + Horizon governance — its answer to openness. Runs on AWS/Azure/GCP. Wins when: you want a near-zero-ops SQL warehouse with effortless workload isolation and multi-cloud.

BigQuery — the serverless extreme#

No clusters to manage at all — fully serverless. Architecture is the disaggregation poster child:

• Dremel execution engine turns SQL into a tree of parallel workers ("slots").
• Colossus (Google's distributed file system) stores data as columnar Capacitor format; the Jupiter petabit network is fast enough that compute and storage being physically separate doesn't hurt — you get storage/compute separation as a given, not a feature you configure.
• You buy compute as slots, either on-demand (priced per byte scanned) or capacity/editions (Standard/Enterprise/Enterprise Plus, reserved slots).

Signature: truly zero-ops elasticity, BigQuery ML (train models in SQL), BigLake + native Iceberg for lakehouse, streaming inserts, and on-demand pricing by bytes scanned — which is a double-edged sword. Wins when: GCP shop, spiky/unpredictable workloads, want zero cluster management. Watch: the per-byte-scanned model means a careless SELECT * on a huge table is a cost event, not just a slow query — partition, cluster, and select only needed columns religiously.

Amazon Redshift — the MPP veteran that caught up#

Originally a shared-nothing MPP cluster: a leader node plans, compute nodes (split into slices) scan in parallel, and you tuned distribution keys (how rows spread across nodes — colocate join keys to avoid redistribution) and sort keys (how rows order within a node — the pruning lever). Older/DC2 nodes coupled storage and compute.

It modernized hard: RA3 nodes with Redshift Managed Storage (RMS) put data on S3-backed managed storage so you scale compute and storage independently (closing the gap with Snowflake); Redshift Serverless removes cluster sizing; Spectrum queries S3 (lake) data directly via external tables; Zero-ETL integrations auto-replicate from Aurora/RDS; and it reads/writes Iceberg. Wins when: deep in AWS — tight IAM/S3/Glue/Kinesis integration and you want the warehouse next to the rest of your AWS stack. Watch: dist-key/sort-key tuning still matters more than in the "no knobs" competitors (though serverless/auto hides some of it).

Databricks SQL — the lakehouse as a warehouse#

Not a separate storage engine — it's SQL compute (serverless SQL warehouses) over your Delta/Iceberg lakehouse, accelerated by the Photon vectorized C++ engine, governed by Unity Catalog. Same tables your Spark/ML jobs use; no copy into a proprietary warehouse. Wins when: you're already a lakehouse/Spark/ML shop and want BI on the same open tables without a second system, or you explicitly want open formats to avoid lock-in. Watch: SQL-only BI ergonomics have historically trailed Snowflake/BigQuery (closing fast); it shines when unified data+AI on one copy matters.

The comparison table#

Honorable mentions (know the names)#

• Trino / Presto — distributed SQL query engine (no storage) that federates over lakes, warehouses, and DBs; the open way to "SQL over the lake." Athena is managed Trino/Presto on S3.
• ClickHouse / Apache Druid / Apache Pinot — blazing real-time OLAP for low-latency dashboards and user-facing analytics (sub-second on huge data).
• DuckDB / MotherDuck — "SQLite for analytics"; an in-process columnar engine that queries Parquet/Iceberg locally — increasingly used to avoid spinning up a cluster for medium data. A great Staff-level "right-size the tool" mention.
• Microsoft Fabric / Synapse — Microsoft's lakehouse (OneLake, Delta-based).

The decision heuristic to state: "There's no 'best' — it's ecosystem + workload + billing model. GCP + spiky → BigQuery. AWS-native → Redshift. Ops-free multi-cloud SQL with isolation → Snowflake. Already a Spark/ML lakehouse or I want open formats → Databricks. Medium data that doesn't need a cluster → DuckDB/Trino over the lake. And in 2026 I'd bias toward whichever lets me keep data in open Iceberg/Delta behind an open catalog, so the engine stays swappable."

9. How your Spring Boot services actually integrate#

This is where a backend engineer earns the Staff+ grade: not "I can write Spark," but "I know where the analytical plane belongs in a microservices architecture and, crucially, where it doesn't."

The golden rule: the warehouse/lake is not in your request path#

An analytical query scans seconds-to-minutes of data, a warehouse cluster costs money per query, and it's built for tens of concurrent heavy queries, not thousands of API calls. So never put a Snowflake/BigQuery/Databricks query behind a synchronous user-facing endpoint. Doing so couples your API's latency and availability to a batch system and can run up a bill per page-load. Interviewers plant this trap; the senior move is to refuse it and reach for a serving layer.

Pattern A — the serving-layer / read-model pattern (the one to lead with)#

The analytical plane computes aggregates on its schedule; a job pushes the results into a low-latency operational store (Postgres, Redis, DynamoDB, or a real-time OLAP store like ClickHouse) that your API reads in single-digit ms. This is CQRS at platform scale — the gold table is the write model of your read model.

Pattern B — reading a warehouse via JDBC (for internal/batch/back-office, not user requests)#

Legitimate when the consumer is a batch job, an internal admin tool, or a data export — anything that tolerates seconds and low concurrency. Every warehouse ships a JDBC driver; from Spring it's ordinary JdbcTemplate/DataSource code.

Java — query Snowflake/Databricks SQL over JDBC (internal reporting job)

Kotlin — same idea, Databricks SQL warehouse

Note the deliberately tiny connection pool — a warehouse is not Postgres; you want a handful of heavy queries, not a hundred. (jOOQ works here too and gives you typesafe SQL over the warehouse — see your jOOQ guide.)

Pattern C — writing to the lake (through Kafka, never from the request thread)#

Covered in Section 7: the service publishes a domain event (via outbox, not a dual-write) and a streaming job lands it in bronze Delta/Iceberg. The service stays fast and transactional; the lake stays consistent.

Pattern D — reverse ETL#

The mirror of ingestion: sync computed insights back from the warehouse into operational tools (CRM, marketing, a feature store, the serving DB). Tools like Census/Hightouch do this; conceptually it's "the gold layer feeds the operational plane." Mention it to show you see the full loop: operational → analytical → back to operational.

Pattern E — query engines over the lake (skip the warehouse entirely)#

For ad-hoc SQL over lake files without loading a warehouse, point Trino/Athena/Databricks SQL at the Parquet/Iceberg tables and hit them via JDBC exactly like Pattern B. For medium data, embedding DuckDB in a JVM service (or a small sidecar) to query Parquet directly is a sharp "don't spin up a cluster for 5 GB" move.

The through-line for the interview: your services touch the analytical plane at exactly two seams — emit events into it (async, via outbox→Kafka) and read precomputed results out of it (from a serving store, or JDBC for batch). The heavy scan compute never sits in a user request.

10. Staff+ concerns — cost, performance, governance, reliability#

Senior engineers make things work; Staff+ engineers own cost, performance at scale, governance, and reliability. This section is disproportionately what gets tested at the level you're targeting.

Cost is the loudest Staff signal#

Analytical systems fail financially far more often than technically. Know the two billing shapes and how to control each:

• Scan-priced (BigQuery on-demand, Athena) — you pay per byte scanned. Control it by partition pruning (only touch relevant partitions), clustering (data-skipping), never SELECT * (columnar means unused columns are free only if you don't select them), materializing common aggregates, and setting per-query/-project byte limits.
• Compute-priced (Snowflake credits, Databricks DBUs, Redshift node-hours) — you pay for cluster time. Control it with auto-suspend (don't pay for idle), right-sizing (don't run a 4XL for a small query), result caching, workload isolation (a runaway data-science query shouldn't force you to over-provision the BI warehouse), and killing always-on clusters.

The sentence that lands: "At Staff level I treat the query optimizer's job as mine too — cost per query is an SLO. Most 'the warehouse is expensive' incidents are unpartitioned tables, SELECT *, oversized or never-suspended warehouses, and missing materializations, not the vendor's price."

Performance — the levers, in order#

1. Partitioning — partition by a low-cardinality, frequently-filtered column (usually date). Enables partition pruning. Anti-pattern: partition by high-cardinality (user_id) → millions of tiny partitions → the small-file problem.
2. File sizing & compaction — target ~128 MB–1 GB files; run OPTIMIZE/compaction to fight small files from streaming.
3. Clustering / Z-order / liquid clustering / sort keys — physically co-locate rows you filter together so data-skipping prunes more.
4. Statistics — keep min/max/cardinality stats fresh so the optimizer prunes and picks join strategies well.
5. Materialized views / gold aggregates — precompute the expensive rollups.
6. Broadcast joins & skew handling (Spark, Section 6).

Governance & the catalog#

At scale you need a catalog: the metadata layer that tracks tables, schemas, lineage, and access control across engines. Unity Catalog (Databricks, now OSS + Iceberg REST), AWS Glue Data Catalog, Snowflake Horizon/Open Catalog (Apache Polaris). It's how you enforce column/row-level security, mask PII, audit access, and let multiple engines share governed tables. Add data contracts (schemas producers must honor — ties to your schema-evolution guide) and data-quality expectations (dbt tests, Delta constraints, Great Expectations) so bad data is caught at the boundary, not in a dashboard.

GDPR / right-to-be-forgotten on immutable files (a favorite curveball)#

"Files are immutable and time travel keeps old versions — how do you actually delete a user?" The answer has layers:

• Table format DELETE/MERGE rewrites (or marks via deletion vectors) the affected files and records it in the log — the row is gone from the current table.
• But time travel and un-VACUUMed files still hold it, so you must VACUUM past the retention window to physically purge, and be careful that CDF/backups don't retain it.
• Crypto-shredding is the scalable pattern: encrypt each user's data with a per-user key; "delete" = throw away the key, rendering the bytes unrecoverable without rewriting petabytes. Mention this and you sound like you've done it for real.

Reliability & correctness#

• Idempotent ingestion + exactly-once via streaming checkpoints + transactional table commits (Section 5) — the analytical version of inbox/outbox.
• Schema enforcement + evolution so a producer change doesn't silently corrupt the table.
• Time travel as your undo button — a bad load is a rollback to version N, not an incident.
• Idempotent MERGE on a business key so re-processing a batch doesn't double-count.

Open formats to avoid lock-in (the closing Staff argument)#

Storing gold in open Iceberg/Delta behind an open catalog means the bytes aren't captive to one engine — you can run Databricks and Snowflake and Trino against the same tables and swap compute as economics change. In 2026, with UniForm, Unity Catalog OSS, and Apache Polaris all converging on open formats + REST catalogs, "keep the storage open, let the engine be a commodity" is the architecturally mature default.

11. Problems & how to solve them (Problem → Root cause → Fix)#

P1 — Queries got 10× slower over weeks; the cluster is busy but scanning little.

• Root cause: the small-file problem. Streaming/append ingestion created millions of tiny files; per-file open/list/metadata overhead now dwarfs the scan.
• Fix: compact — OPTIMIZE (Delta) / rewrite_data_files (Iceberg) into ~128 MB–1 GB files; batch writes (buffer before writing); partition by low-cardinality columns; on streaming, set a sane trigger interval and run scheduled compaction. Prevent by never partitioning on high-cardinality keys.

P2 — A Spark join runs 95% fast then one task hangs for an hour and OOMs.

• Root cause: data skew — one key (a null customer_id, a mega-customer) owns a huge partition; that straggler task blocks the stage.
• Fix: enable/trust AQE skew handling (default in Spark 3.2+/4.x); salt the hot key (append random suffix, aggregate in two passes); filter/route nulls separately; broadcast the small side to avoid the shuffle entirely.

P3 — The BigQuery bill 20×'d overnight.

• Root cause: someone ran SELECT * (or an unpartitioned filter) on a huge table under on-demand, bytes-scanned pricing; every run re-scanned terabytes.
• Fix: partition + cluster the table; select only needed columns; add require_partition_filter; set per-project/-query byte limits and budget alerts; materialize the common query. On compute-priced engines the analog is an oversized/never-suspended warehouse — right-size + auto-suspend.

P4 — The lake has everything but no one trusts it; every number needs a caveat.

• Root cause: a data swamp — raw files with no schema enforcement, no ACID, no catalog, no ownership.
• Fix: adopt a lakehouse table format (Delta/Iceberg) for ACID + schema enforcement; impose the medallion structure (bronze/silver/gold); register tables in a catalog with ownership and lineage; add data contracts + quality tests at the bronze→silver boundary.

P5 — Two jobs wrote the same raw-Parquet table concurrently and now rows are duplicated/missing.

• Root cause: no transactions on a bare file directory; interleaved/failed writes left partial state; a reader saw a torn listing.
• Fix: use a table format so writes are atomic commits under optimistic concurrency; conflicting writers retry against the new snapshot; readers get snapshot isolation. (This is exactly the swamp problem the transaction log solves.)

P6 — Legal needs a user fully deleted, but files are immutable and time travel keeps old copies.

• Root cause: immutability + retention/time-travel keep historical bytes around after a logical delete.
• Fix: DELETE/MERGE (or deletion vectors) to drop the rows now; VACUUM past retention to physically purge; audit CDF/backups; for scale, crypto-shred (per-user key, delete the key).

P7 — The service updates Postgres and the lake separately; they drift.

• Root cause: a dual write — two systems, no shared transaction; a crash between them diverges truth.
• Fix: write once (to the OLTP DB) and derive the lake via outbox → Kafka → streaming MERGE or CDC. Never write both from app code. (See your inbox/outbox and CDC guides — same disease, same cure.)

P8 — "Revenue by city" changed for past months after a customer moved.

• Root cause: the city was overwritten in place (SCD Type 1) so history retroactively changed.
• Fix: model the dimension SCD Type 2 (versioned rows with valid_from/valid_to/is_current, surrogate keys); facts reference the version current at event time. Implement with MERGE.

P9 — The streaming pipeline occasionally double-counts after a restart.

• Root cause: at-least-once processing without idempotent output — reprocessed offsets wrote rows twice.
• Fix: Structured Streaming checkpoint (offsets + state) + transactional Delta commit = exactly-once; and/or MERGE on a business key so re-writes are idempotent.

P10 — A dashboard endpoint times out under load / racks up warehouse cost per page-view.

• Root cause: the API queries the warehouse synchronously in the request path.
• Fix: precompute in the lakehouse, sync gold aggregates to a serving store (Redis/Postgres/ClickHouse), serve from there in ms (Pattern A). The warehouse is never in the hot path.

P11 — Redshift query is slow despite being columnar and partitioned-ish.

• Root cause: bad distribution key (join keys not colocated → data redistributed across nodes every join) and/or missing sort key (no zone-map pruning).
• Fix: set dist key to the common join key (or ALL for small dims), sort key to the common filter/range column; run ANALYZE/VACUUM; consider RA3/serverless auto-optimization.

12. Interview framing — how to sound senior#

The 30-second positioning (memorize the spine):

"Operational systems are OLTP — row-oriented, tuned for low-latency lookups and writes by key. Analytics is OLAP — a scan problem, so you want columnar storage, MPP, and separation of storage and compute. A warehouse enforces schema on write (model first, query fast, less flexible); a lake stores raw files on cheap object storage with schema on read (flexible, but degrades into a swamp without guardrails); a lakehouse puts an ACID transaction log — Delta or Iceberg — over open columnar files, so you get warehouse guarantees on lake economics. That's the modern default, and in 2026 the whole industry is converging on open table formats behind an open REST catalog so the engine becomes swappable."

When they ask "lake or warehouse for X?" Don't pick tribally. Say: "Both planes — operational OLTP as the source of truth, an analytical plane as a derived read model, bridged by CDC or outbox→Kafka. For the analytical plane I'd default to a lakehouse: medallion layers on Delta/Iceberg, Kimball star schemas in gold, and a serving store in front of any user-facing reads. I'd only run a pure classic warehouse if the org is all-SQL BI with no ML/raw-data needs, and a pure raw lake never — that's the swamp."

Tie it to system design (this is what earns Staff):

• The analytical plane is CQRS at platform scale — a denormalized, eventually-consistent read model.
• CDC / outbox is the bridge; dual writes are the anti-pattern (your inbox/outbox and CDC guides plug in directly).
• The serving layer keeps the warehouse out of the request path.
• Cost per query is an SLO you own; open formats keep the engine a commodity; governance/lineage/contracts keep the platform trustworthy.

Common wrong answers / traps to avoid:

• "A data lake is just a big database." → No — it's files on object storage; there's no server, compute is BYO.
• "Schema-on-read means no schema." → There's always a schema; you just enforce it at read time, which is why raw lakes rot.
• "The lakehouse replaced the warehouse." → It replaced the dichotomy; warehouses (Snowflake/BigQuery/Redshift) adopted lakehouse ideas (Iceberg, separation) and remain excellent — this is convergence, not conquest.
• "Just query the warehouse from the API." → Never in the request path (latency, cost, concurrency).
• Confusing file format (Parquet), table format (Delta/Iceberg), and engine (Spark/Trino/Snowflake) — keeping these three layers distinct is itself a seniority signal.
• Forgetting cost. Junior answers stop at "it works"; Staff answers price it.

Version-dependence to flag out loud (shows currency): Spark is at 4.1.x (mid-2026); S3 got conditional writes (2024) so table formats no longer need external commit coordination; Unity Catalog is open-source and speaks Iceberg REST; Snowflake/BigQuery/Redshift all read/write Iceberg now. Say "as of 2026" and note these move.

13. Cheat-sheets#

Lake vs Warehouse vs Lakehouse

Parquet vs ORC vs Avro

Delta vs Iceberg vs Hudi

Warehouses at a glance

SCD types: 0 = never change · 1 = overwrite (no history) · 2 = new versioned row (full history — default) · 3 = previous-value column · 4 = history table · 6 = 1+2+3.

ETL vs ELT: ETL transforms before load (scarce compute era); ELT loads raw then transforms in-warehouse with dbt (cloud default — cheap storage, elastic compute, keep raw history).

Spark: transformations are lazy (map/filter narrow, groupBy/join wide→shuffle); actions (count/collect/write) trigger execution; the shuffle and data skew dominate performance; broadcast the small side; Catalyst optimizes, Photon accelerates, AQE re-optimizes at runtime.

File-layout hygiene: partition by low-cardinality (date) · target ~128 MB–1 GB files · OPTIMIZE/compact against small files · cluster/Z-order by common filters · keep stats fresh.

14. Self-test#

1. Why is a columnar layout faster than a row layout for SELECT SUM(amount) WHERE country='US' — name the three compounding wins plus data skipping.
2. Distinguish file format, table format, and query engine, with an example of each.
3. Walk through what happens on disk when you MERGE into a Delta table. How does the _delta_log give you ACID on plain object storage?
4. Two Spark writers commit to the same Delta table at once. What mechanism prevents corruption, and what does the loser do?
5. A customer changes address. Show how SCD Type 2 keeps historical revenue correct, and contrast with Type 1.
6. Your streaming job created 4 million 200 KB files and queries crawled. Diagnose and give three fixes.
7. Explain data skew in a Spark join and three distinct ways to fix it.
8. Why did ELT overtake ETL? What new risk does ELT introduce and how do you contain it?
9. Compare the billing models of BigQuery vs Snowflake and give the top cost trap and mitigation for each.
10. A PM wants a live revenue dashboard on the homepage. Why is querying the warehouse per request wrong, and what do you build instead?
11. How do you fully delete a user (GDPR) from an immutable-file lakehouse, including time travel and backups?
12. Your service must update Postgres and land the same data in the lake. Why not write both directly, and what's the correct pattern?
13. What does "separation of storage and compute" mean physically, and why did it change warehouse economics? How do Snowflake, BigQuery, and Redshift RA3 each achieve it?
14. What is the medallion architecture, and where do Kimball star schemas live in it?
15. Give the 30-second answer to "data lake or data warehouse?" that a Staff+ interviewer wants to hear.

Sources & lineage note#

Conceptual lineage: Kimball & Ross, The Data Warehouse Toolkit (dimensional modeling, SCD, grain); Armbrust et al., Lakehouse (CIDR 2021) and the Delta Lake VLDB paper (transaction-log design); Melnik et al., Dremel (2010, the columnar-nested + tree-execution ideas behind BigQuery and Parquet); Google BigQuery, Snowflake's elastic-warehouse architecture paper, and AWS Redshift RA3 docs.

Version-anchored to mid-2026 and verified against current sources: Apache Spark 4.1.2 (May 2026, latest stable; 4.0 GA May 2025 brought ANSI-default, VARIANT, and a lightweight Spark Connect client — Spark Connect itself GA'd in 3.5); Unity Catalog open-sourced (2024) implementing the Iceberg REST Catalog API; Delta UniForm exposing Delta as Iceberg/Hudi; Databricks' Tabular acquisition (2024); Snowflake Apache Polaris / Open Catalog / Horizon + native Iceberg tables; BigQuery on-demand bytes-scanned pricing + editions/slots + BigLake/Iceberg; Redshift RA3 + Redshift Managed Storage for storage/compute separation, Serverless, Spectrum, Zero-ETL; S3 conditional writes (2024) removing the external-commit-coordination requirement for table formats. Treat all product/version/pricing specifics as time-sensitive — re-verify before an interview. The core physics (OLTP vs OLAP, columnar storage, the shuffle, ACID via a transaction log, dimensional modeling) is stable.

Pairs with your existing guides: CDC, inbox/outbox, CQRS (the analytical plane as a read model), schema evolution (data contracts on the ingestion boundary), Kafka/EDA (the ingestion backbone), locking (ShedLock for once-per-fleet jobs), and jOOQ (typesafe SQL over a warehouse).