Data Lake and Warehouse Architecture Questions

Designing scalable data platforms for analytical and reporting workloads including data lakes, data warehouses, and lakehouse architectures. Key topics include storage formats and layout including columnar file formats such as Parquet and table formats such as Iceberg and Delta Lake, partitioning and compaction strategies, metadata management and cataloging, schema evolution and transactional guarantees for analytical data, and cost and performance trade offs. Cover ingestion patterns for batch and streaming data including change data capture, data transformation approaches and compute engines for analytical queries, partition pruning and predicate pushdown, query optimization and materialized views, data modeling for analytical workloads, retention and tiering, security and access control, data governance and lineage, and integration with business intelligence and real time analytics. Also discuss operational concerns such as monitoring, vacuuming and compaction jobs, metadata scaling, and strategies for minimizing query latency while controlling storage cost.

HardTechnical

76 practiced

A data product requires exactly-once semantics for event ingestion into an analytical table. Explain the difference between at-least-once, at-most-once, and exactly-once delivery. Then propose how you would achieve exactly-once semantics end-to-end using Kafka, Debezium (CDC), and Iceberg or Delta Lake as the sink.

Sample Answer

First, the definitions.

- At-most-once: Each event is delivered zero or one time. Messages may be lost but never duplicated. Fast but not durable.- At-least-once: Each event is delivered one or more times. No loss (assuming retries), but duplicates are possible and the consumer must deduplicate.- Exactly-once: Each event has effect exactly once despite retries/failures. Achieved by coordination between producer, broker, and consumer or by idempotent/transactional sinks.

Proposed end-to-end design (Kafka + Debezium CDC + Iceberg/Delta):

1. Source (Debezium)- Debezium streams DB changes to Kafka topics using the DB primary key and includes CDC metadata (transaction id, LSN/SCN, op type, commit timestamp). Configure Debezium to emit transaction boundaries (txn metadata).

2. Kafka- Use Kafka with idempotent producers (acks=all, enable.idempotence=true) and transactional producers for multi-partition atomicity when needed. Debezium is a producer; ensure connector config uses safe producer settings.- Retain offsets and enable compaction on topics keyed by primary key so the latest record per key remains.

3. Stream processing / Sink connectorOption A — Structured Streaming (Spark) or Flink:- Use Spark Structured Streaming with Kafka source and output to Delta/Iceberg via a transactional write: - Use Spark’s checkpointing to store progress. - For Delta Lake: write as MERGE INTO (upsert) inside foreachBatch so each micro-batch is applied atomically. Use the CDC transaction id + primary key to dedupe (apply only records with greater LSN or unseen txn id). - For Iceberg: use the Iceberg write API that supports atomic commits and snapshot isolation; write upserts via merge-on-read or overwrite-by-row-id.- Ensure the stream engine supports end-to-end exactly-once: it must read Kafka offsets atomically with the sink commit (two-phase commit or transactional write), or store the committed Kafka offset inside the same transaction as the sink commit. Flink has a 2PC sink connector; Spark’s foreachBatch + idempotent MERGE achieves effectively-once.

Option B — Kafka Connect Sink with Transactional Writes:- Use a Kafka Connect sink that supports idempotent upserts to Iceberg/Delta (or a custom connector).- Connector should use CDC metadata (txn id, LSN) to dedupe and perform idempotent MERGE operations. Commit offsets only after sink transaction succeeds.

4. Deduplication & Idempotency- Rely on primary key based upserts (MERGE) in Delta/Iceberg so reprocessing same event is a no-op.- Use CDC transaction id + LSN ordering to ensure event order and to ignore older duplicates.- For out-of-order delivery, use compare-and-apply logic: only apply an event if its LSN/txn timestamp > current stored LSN for that PK.

5. Operational safeguards- Checkpointing of stream engine (offsets) stored reliably.- Monitor consumer lag, failed commits, connector errors.- Test by killing tasks mid-commit to validate no duplicate rows and no lost rows.

Failure modes & trade-offs- Exactly-once requires coordination; performance and complexity increase.- If sink cannot support atomic upsert/commit, you must implement dedupe store (e.g., a commit-log table storing applied txn ids) or use Kafka transactions + transactional sink supported by processing engine.

Summary: Use Debezium → Kafka (idempotent/transactional) → stream processor or Kafka Connect that performs idempotent MERGE writes to Delta/Iceberg using CDC txn+LSN for dedupe and atomic commits/checkpoints. That combination yields end-to-end exactly-once semantics in practice.

MediumTechnical

62 practiced

A report team needs daily snapshots of a slowly changing dimension (SCD Type 2) in your warehouse. Explain how you'd implement SCD Type 2 using a data lake/lakehouse architecture. Discuss keys, effective date ranges, updates vs inserts, and how to query the current and historical state efficiently.

Sample Answer

Start by modeling SCD Type 2 rows as immutable versions: each change creates a new row for that business entity, and prior rows remain for history.

Schema & keys- Natural/business key: e.g., customer_id (identifies the real-world entity).- Surrogate/version key: e.g., customer_sk (unique per row) or generated UUID.- Effective date range: effective_from (inclusive), effective_to (exclusive or null for current).- Status flag / is_current boolean for fast current-row queries.- Optional metadata: source, change_type, load_timestamp, version_number.

Storage / architecture- Use a columnar format with ACID/merge support (Delta Lake, Iceberg, Hudi) on the lakehouse.- Partition by a stable dimension (e.g., load_date or hash of business key) and cluster by business key for read performance.- Store base SCD table as parquet/Delta table and maintain incremental ingestion via MERGE/UPSERT.

Updates vs inserts (ingest logic)- For each incoming change batch: - Identify incoming rows that differ from latest stored row (compare business key + business attributes). - For changed entities: set existing current row’s effective_to = incoming.effective_from and is_current = false (update). - Insert a new row with effective_from = change_time, effective_to = NULL, is_current = true (insert).- Implement via single transactional MERGE (Delta/Hudi/Iceberg) to atomically update old row(s) and insert new one.- For non-changed rows: do nothing.

Example (Delta Lake MERGE pseudo-SQL)

sql

MERGE INTO scd_customers T
USING updates S
ON T.customer_id = S.customer_id AND T.is_current = true
WHEN MATCHED AND (T.attr1 <> S.attr1 OR ...) THEN
  UPDATE SET T.effective_to = S.effective_from, T.is_current = false
WHEN NOT MATCHED THEN
  INSERT (customer_sk, customer_id, attr1, ..., effective_from, effective_to, is_current)
  VALUES (uuid(), S.customer_id, S.attr1, ..., S.effective_from, NULL, true)

(You may need two-step MERGE to avoid race conditions: mark old row then insert.)

Querying current vs historical- Current row (fast): filter is_current = true OR effective_to IS NULL with partition pruning: SELECT * FROM scd_customers WHERE is_current = true;- State at a point-in-time t: SELECT * FROM scd_customers WHERE effective_from <= t AND (effective_to > t OR effective_to IS NULL);- Daily snapshot for reporting: either run the point-in-time query with t = report_date OR maintain a materialized daily snapshot table (date-partitioned) produced by an incremental job that writes current state each day (cheap reads for reports).- Efficient pattern: keep an indexed/clustered copy of current rows to serve reports; historical queries hit partitioned table using predicates on effective_from/effective_to.

Operational concerns- Use CDC or event-driven ingestion to capture changes; deduplicate within micro-batches.- Compact small files and periodically vacuum old file versions.- Retain enough data versions if relying on time-travel.- Monitor merge performance; tune partitioning and file sizes.- Add test coverage for edge cases: out-of-order events, late-arriving rows, identical consecutive updates.

This design gives atomic, queryable history, efficient current-row access, and scales on a lakehouse using ACID storage and MERGE semantics.

MediumTechnical

71 practiced

Explain how materialized views can reduce query latency in a data warehouse. Describe maintenance strategies: on-demand refresh, scheduled incremental refresh, and write-through updates. For each, discuss cost and freshness trade-offs and a use-case where it is most appropriate.

Sample Answer

Materialized views (MVs) store precomputed query results so read queries avoid expensive joins/aggregations over raw tables, reducing CPU, I/O, and network latency — especially for complex analytics or high-concurrency dashboards. They act like managed caches inside the data warehouse with query planners typically able to rewrite queries to use MVs automatically.

On-demand refresh- Description: Recompute the MV only when an explicit REFRESH is called.- Freshness vs cost: Highest staleness until refreshed; lowest recurring compute cost since refreshes happen infrequently.- Use-case: Ad-hoc reporting or backfills where correctness at refresh time matters (e.g., monthly financial close) and you want to avoid continuous compute costs.

Scheduled incremental refresh- Description: Periodic refreshes that apply only deltas (INSERT/UPDATE/DELETE since last refresh) using change data capture (CDC) or partition-aware algorithms.- Freshness vs cost: Tunable — frequent schedules reduce staleness but increase compute cost; incremental reduces cost compared to full refresh while keeping near-real-time freshness.- Use-case: Near-real-time dashboards (hourly/near-minute) for product metrics where some bounded staleness is acceptable but cost must be controlled.

Write-through updates- Description: Maintain MV synchronously or transactionally at write time: when source tables change, MV is updated immediately (via triggers, materialized view logs, or pipeline logic).- Freshness vs cost: Lowest staleness (near-zero) but highest write latency and complexity; increased cost due to additional work on every write and potential contention.- Use-case: SLA-critical operational analytics (fraud detection, real-time inventory) where consumers require the freshest possible view.

Practical considerations- Choose based on SLA: acceptable staleness, read QPS, and cost budget.- Favor incremental/scheduled where CDC and partitioning exist; fall back to on-demand for infrequent needs.- Monitor MV usage and hit-rates; evict or drop unused MVs to save cost.- Ensure correctness handling for rewrites, schema changes, and downstream consumers.

MediumTechnical

66 practiced

Explain Change Data Capture (CDC) and describe two patterns to apply CDC events into a data warehouse or lakehouse (e.g., event-driven upsert vs micro-batch consolidation). For each pattern, state its advantages, challenges, and the type of analytical workload it suits.

Sample Answer

Change Data Capture (CDC) is the technique of capturing row-level changes (inserts, updates, deletes) from a source system (often via database transaction logs) and publishing them as events so downstream systems can apply those changes incrementally. Tools like Debezium read database logs and emit change events to Kafka; these events preserve ordering, timestamps, and primary keys, enabling downstream delta application without full table scans.

Pattern 1 — Event-driven upsert (streaming, per-event apply)- How it works: CDC events are streamed into a message bus (Kafka), consumed by a streaming job that performs idempotent upserts into the target table/lakehouse (e.g., use MERGE INTO on Delta/Iceberg/Hudi or update rows in a DW).- Advantages: - Low end-to-end latency (near real-time). - Fine-grained lineage and auditability (each change retained). - Fits operational analytics and dashboards that require fresh data.- Challenges: - Must ensure idempotency and correct ordering (use primary keys, timestamps, Kafka partitions). - Higher writer pressure on the target (many small updates) — may increase cost. - Handling schema evolution and deletes consistently is complex.- Suited workloads: real-time dashboards, fraud detection, feature stores, operational analytics.

Pattern 2 — Micro-batch consolidation (batched compaction)- How it works: CDC events are ingested to a raw event store (Kafka or object storage). Periodic micro-batches (e.g., every 5–30 minutes) read events, deduplicate/resolve by key and timestamp, then write a consolidated snapshot or upsert via bulk MERGE into the warehouse/lakehouse.- Advantages: - Better throughput and cost efficiency (bulk writes/compactions). - Simpler handling of late-arriving events via windowing and watermarking. - Easier to optimize partitions and compaction for analytical queries.- Challenges: - Higher latency (minutes instead of seconds). - Requires careful windowing/dedup logic to avoid losing events. - Operational complexity: scheduling, backpressure, and storage of raw events.- Suited workloads: reporting, BI dashboards with minute-level freshness, large-scale analytics where write efficiency matters.

Trade-offs summary:- Choose event-driven upsert when freshness and per-change auditability matter; invest in idempotency, ordering, and write scaling.- Choose micro-batch consolidation when cost/throughput and efficient analytic layout matter and slightly higher latency is acceptable.

Practical notes:- Use Kafka compaction topics for primary-key retention; combine with Debezium for reliable capture.- Use lakehouse formats (Delta/Iceberg/Hudi) that support transactional MERGE and time-travel to simplify correctness and replay.

MediumTechnical

70 practiced

You have multiple teams writing to the same data lake table using different compute engines. Outline governance patterns (locking, transactional metadata, write protocols) to prevent corruption and ensure consistent schema evolution. Which open-table-format features would you rely on?

Sample Answer

Situation: Multiple teams and compute engines (Spark, Flink, Presto, Glue) need to write concurrently to the same data-lake table without corrupting data or breaking downstream consumers.

Recommended governance patterns (high-level):- Transactional metadata + atomic commits: use an open-table format with a transaction log (Delta Lake, Apache Iceberg, Apache Hudi). All writes go through the table’s commit protocol so readers see only consistent snapshots.- Concurrency control: prefer optimistic concurrency with snapshot isolation (reject conflicting commits) plus retry logic in writers. For long-running jobs use short-lived transactions and incremental commits.- Locking where necessary: use lightweight coordinator locks for metadata-only operations (schema change, partition rewrite, vacuum). Implement via external coordination (Zookeeper, DynamoDB/Blob lease, etc.) to prevent simultaneous schema migrations.- Write protocols: - Append-only for most pipelines (idempotent writers using file-level UUIDs or writer tokens). - Replace/overwrite must be fenced behind exclusive lock or use partition-expressions with isolation guarantees. - Use multipart/atomic file uploads (cloud object store semantics) and manifest/manifest-list to avoid partial visibility.- Schema evolution governance: - Enforce a schema registry or policy: allow additive changes automatically; require approval/lock for breaking changes (type narrowing, field removal). - Use table-format features for schema evolution (Iceberg/Delta support adding fields, renames with metadata). - Implement pre-commit schema validation (compatibility checks) and migration scripts stored in version control.- Operational best practices: - Use idempotent write IDs, deduplication via primary keys or change-ids. - Housekeeping: compaction/rollback controlled by scheduled jobs, not ad-hoc writers. - Observability: commit audit logs, metrics on conflicts/retries, alerting for schema-change attempts.

Open-table-format features to rely on:- Snapshot isolation & atomic commit protocol (Delta’s transaction log, Iceberg’s snapshot/manifest model, Hudi’s timeline).- Manifest/manifest-list and file-level metadata to track physical files and enable fast snapshot reads.- Schema evolution APIs (Iceberg’s schema/partition evolution, Delta’s schema merge controls).- Partition-spec evolution and hidden partitioning (Iceberg) to change partitioning without breaking queries.- Time-travel / versioned snapshots for safe rollbacks and reproducible reads.- Catalog integration and table-level locking primitives (where supported) for governance and discovery.

Example enforcement flow:1. Writer stages files to temp prefix with unique writer-id.2. Writer reads latest snapshot, does optimistic commit via table API (includes file list + schema).3. Commit is validated by table-format engine; conflicting commits fail — client retries.4. Schema-change attempts require a metadata-lock acquired via coordinator; once acquired, commit proceeds and catalog updated.

This combination gives multi-writer safety, consistent snapshots for readers, controlled schema evolution, and operational observability suitable for enterprise data lakes.

Unlock Full Question Bank

Get access to hundreds of Data Lake and Warehouse Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.