Data Engineering
Data platform: multiple-choice review
Crux Cross-track synthesis: pick the right store, format, and contract per workload across OLTP/OLAP, ELT, Parquet, MVs, event sourcing, search, and vectors.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole track. Each one is a design call you make when one fact has to live in several stores at once — not a definition, but a choice of store, format, or contract under a real workload.
Goal
Confirm you can route a workload to the right store and layout, and reason about the seams between them — the synthesis the OLTP/OLAP, ELT, Parquet, MV, event-sourcing, search, and vector units all built toward.
Quiz
Completed
A single product fact must serve point lookups in the checkout path AND a full-table revenue scan for analytics. What is the senior architecture?
Heads-up A column extension helps scans but you still pay row-store write amplification on the OLTP path, and you couple analytics load to the transactional database. The track's first lesson is precisely that one storage layout can't optimise tiny indexed writes and huge column scans at once.
Heads-up A replica copies the same row-oriented layout, so the revenue scan still reads every column of every row off disk. Replicas isolate load, not access pattern — the scan stays slow because the layout is wrong for it.
Heads-up Columnar stores answer a single-row lookup by touching many row groups and reconstructing columns — high per-row latency and no real point-index. The checkout path needs OLTP; the warehouse is for scans.
Quiz
Completed
Your team is choosing between ETL (transform in a separate engine before load) and ELT (load raw, transform in-warehouse with dbt). The data is messy and the business keeps changing the definition of 'active user'. Which fits, and why?
Heads-up Transforming before load discards the raw data, so when the 'active user' definition changes you cannot replay — you must re-ingest from source if it still exists. ELT keeps raw bronze precisely so redefinitions are a re-run, not a re-ingest.
Heads-up Modern warehouses + dbt express arbitrarily complex SQL/UDF transforms. The industry flipped to ELT because warehouse compute got cheap and elastic; where the T runs is a cost/replay/fidelity decision, not a capability ceiling.
Heads-up Pushing business-definition logic into the app couples it to deploys and loses the raw audit trail entirely. The point of the bronze/silver/gold split is that definitions live in versioned, replayable transforms over retained raw data.
Quiz
Completed
A nightly dashboard query filters event_date = '2026-05-01' AND country = 'US' over a 2 TB Parquet/Iceberg table and still scans most of the data. What is the highest-leverage fix?
Heads-up Parquet has no per-row B-tree index; pruning is done by partition layout plus row-group footer statistics. The OLTP indexing instinct doesn't transfer — you make predicates prunable, not indexed.
Heads-up Row format makes a column scan worse: you read every column of every row. Columnar plus footer stats is what lets the engine read only event_date/country and skip non-matching row groups.
Heads-up 2 TB rarely fits in memory and you'd still scan it all; caching masks the real problem. Pruning means never reading the irrelevant 99% in the first place, which footer stats plus partitioning deliver.
Quiz
Completed
A gold materialized view serving a dashboard refreshes every 6 hours. A finance lead complains the number 'is wrong' versus a live SQL count. Both are internally correct. What did the design get wrong?
Heads-up If both numbers are internally correct, the gap is time, not logic. The MV is right for its 6-hour-old snapshot and the live count is right for now — check and declare freshness before suspecting the transform.
Heads-up MVs are fine for finance when the staleness fits the question. The failure is leaving staleness undeclared and unmonitored, not using an MV at all — the cost you manage is refresh and the staleness it leaks.
Heads-up Per-write refresh defeats the point of an MV and can cost more than the original query. The senior move is to declare an SLA that matches the question's freshness need, not to chase zero staleness.
Quiz
Completed
A service writes an order to Postgres, then publishes 'OrderPlaced' to Kafka so search and analytics react. Occasionally the search index never learns about an order. What is the root cause and the fix?
Heads-up The event is lost before it reaches Kafka — the process died after the Postgres commit but before publishing. Broker durability can't save an event that was never sent; you need to make the write and the event atomic via an outbox.
Heads-up That just inverts the dual-write: now a crash after publish but before commit emits an event for an order that doesn't exist. Reordering two non-transactional writes never closes the gap — only a single atomic write (outbox) plus reconciliation does.
Heads-up Polling rebuilds CDC by hand, bounds freshness to the poll interval, and misses deletes unless you also poll tombstones. The fix is to make the event path reliable (outbox plus idempotent consumer), with reconciliation as the safety net.
Quiz
Completed
Catalog search must match misspelled product names AND the RAG assistant must answer 'a laptop good for video editing'. One team proposes using the vector index for both. What is the correct split?
Heads-up Embeddings capture semantics but blur exact tokens, SKUs, and rare terms, so a precise model-number query degrades. Inverted indexes excel at exact/lexical match and BM25 ranking; vectors excel at meaning. They solve different halves of relevance.
Heads-up BM25 ranks by term overlap, so 'good for video editing' won't match a description that says 'high-performance GPU and 32 GB RAM' with no shared words. Semantic retrieval needs vector similarity, not term frequency.
Heads-up A substring scan can't rank, can't tolerate misspellings, can't match semantically, and full-scans the OLTP store. Search is matching plus ranking and semantic retrieval is similarity — neither is a SQL LIKE.
Recap
The track’s through-line is one habit: route each workload to the store and layout that fits it, then design the contract at every seam. Row-store OLTP for point writes, columnar Parquet for scans (with footer-stat pruning), ELT over retained raw for replayable definitions, MVs for read latency with a declared freshness SLA, an outbox to kill the dual-write, inverted indexes for lexical search, and vector ANN for semantic retrieval. Each store is correct for its job; the system stays correct only when you own the schema, delivery guarantee, freshness SLA, and reconciliation between them.