Practice Questions

Requirements before boxes. Functional: what data, which sources, which consumers, which queries and SLAs. Non-functional: volume/velocity/variety, freshness, availability, durability, consistency, budget, compliance. Then a back-of-envelope estimate to size the thing. See 02-requirements-and-estimation. The Senior candidate lists requirement categories; the Staff candidate asks the two or three clarifying questions whose answers actually pre-decide the later forks — "what's the freshness SLA and who's on the hook for it?" quietly settles batch-vs-streaming before you draw anything.

No, and conflating them is a red flag. Freshness is how stale the served data is (end-to-end lag from event to query). Latency is how long a single read or a single pipeline stage takes. Availability is uptime of the serving layer. Durability is not losing committed data (replication factor, backups). Consistency is whether readers see a coherent view. A dashboard can tolerate 6-hour freshness but demand low query latency; a fraud feature demands seconds of freshness. Naming the right one per consumer is what drives the architecture.

50M × 1KB = 50 GB/day raw. × 365 × 2 ≈ 36 TB over two years. Columnar + compression on the analytics copy typically knocks that down 3–5×, so call it ~8–12 TB queryable, but the raw/bronze landing zone keeps the full 36 TB. Then add replication (×2–3 depending on store) and headroom. At object-storage prices (~$20–25/TB/mo) the raw tier is cheap — tens of dollars a month — which is the point: land raw cheaply, spend compute on the modeled layer. The number itself matters less than showing you can sanity-check every later choice against it. See 02.

Point lookups (give me user X's current state) want an indexed/key-value serving store; large scans and aggregations want columnar storage and a partition/clustering scheme aligned to the filter columns; high-QPS dashboard reads want pre-aggregated marts or a cache, not repeated full scans. The mistake is designing one store for all three. Estimate read QPS and peak-vs-average per pattern, then pick the serving layer per pattern — the warehouse/mart for BI, a KV store or feature store for online lookups, the lake for exploratory scans.

Pull (batch/connector polling: Fivetran/Airbyte hitting a SaaS API or a DB on a schedule) fits sources you don't control and low-freshness needs — simple, but bounded by poll interval and API limits. Push (the source emits into a log/queue: app events into Kafka/Kinesis/Pub-Sub) fits high-volume, low-latency, source-you-control cases and decouples producers from consumers. Most real platforms run both: CDC + connectors pull the databases and SaaS, an event backbone receives the pushed clickstream. See 03-ingestion.

The log is a durable, replayable buffer that decouples producers from consumers. It absorbs bursts (backpressure lands in retention, not on the source), lets many consumers read the same stream at their own pace via consumer groups, preserves per-key ordering within a partition, and makes reprocessing a matter of resetting an offset. Partition by a key with even cardinality to spread load and keep related events ordered. Writing straight to the warehouse couples ingest rate to warehouse write throughput and loses replay.

End-to-end exactly-once is expensive and often unnecessary. The pragmatic pattern is at-least-once delivery + an idempotent sink = effectively-once: give every event a stable dedup key (source PK + offset/LSN, or a natural business key) and MERGE/upsert on it, so replays and retries collapse. Reserve true transactional exactly-once (e.g. Flink's checkpoint + transactional sink) for cases that genuinely can't tolerate a duplicate. The Staff move is stating the guarantee you actually need per consumer rather than promising exactly-once everywhere.

Schema drift should degrade gracefully, not break silently. Use a schema registry (Avro/Protobuf/JSON Schema) with a compatibility mode — backward-compatible additive changes flow through; a breaking change is rejected at the producer or routed to a dead-letter queue rather than corrupting downstream. Land raw in a schema-on-read zone so you never lose the payload, and surface the drift as an alert. Data contracts push the enforcement left to the producer's CI; see 09 and the pipelines guide's validation chapter.

Three families. Query-based / batch diff (poll updated_at high-water-mark or snapshot-diff): dead simple, no source cooperation, but misses hard deletes and intermediate states, and loads the source. Trigger-based: captures every change including deletes, but is invasive and adds write-path latency to the OLTP DB. Log-based (read the WAL/binlog/redo via Debezium or a native connector): low source impact, complete, ordered, captures deletes — the default for a real platform. Lay it out as a matrix and pick against requirements. See 04.

Heuristic: query-based is fine when the table has a reliable monotonic updated_at, you don't need to capture hard deletes (or deletes are soft), intermediate states between polls don't matter, the source can absorb the query load, and the freshness SLA is measured in minutes-to-hours. The moment you need deletes, sub-minute freshness, or every intermediate transition — or the source is too hot to poll — you move to log-based. Reaching for Debezium when a nightly high-water-mark pull meets the SLA is over-engineering; not reaching for it when you silently lose deletes is a correctness bug.

The snapshot + stream handoff. Take a consistent snapshot while recording the log position (LSN/binlog offset) at snapshot start, then begin streaming changes from that position — overlapping changes get replayed on top and are reconciled by the idempotent upsert. Debezium's incremental snapshot (watermark-based) does this without a long lock. Because the sink MERGEs on primary key, replaying an already-applied change is a no-op, so the overlap is safe. Land the raw change log append-only, then MERGE to a current-state table — merge mechanics live in the pipelines guide's incremental-processing chapter.

That's the dual-write problem: two writes to two systems with no shared transaction, so a crash between them leaves the DB and the stream inconsistent. The fix is the transactional outbox: write the business row and an outbox row in one local transaction, then let CDC tail the outbox table and publish — one atomic write, one source of truth, the log guarantees eventual delivery. It turns "app emits events" into a CDC problem you already know how to solve. See 04.

Freshness SLA weighed against cost, complexity, correctness, and operational burden — not fashion. Choose batch when the SLA is hours and nightly/hourly runs meet it: cheaper, simpler, trivially reprocessed. Choose streaming when a consumer genuinely needs seconds-to-minutes freshness (fraud, real-time features, ops alerting) and the value clears the higher cost and 24/7 operational cost. Micro-batch (Spark Structured Streaming) sits between. The common trap: reaching for streaming when nightly batch meets the SLA at a fraction of the cost. See 05.

Lambda runs a batch layer (accurate, reprocessable) and a speed layer (fresh, approximate) in parallel and merges them — the cost is maintaining two codebases with the same logic. Kappa treats the stream as the single source of truth and reprocesses by replaying the log through one streaming codebase, eliminating the duplication. The modern trend is streaming-first / unified engines and incremental table formats that give you fresh-and-correct without two pipelines. The Staff answer names why the industry moved off Lambda: dual-logic drift is a maintenance and correctness tax.

Processing time is when your system saw the event; event time is when it actually happened. They diverge because of network delay, mobile buffering, and retries, so windowed aggregations keyed on processing time silently misattribute late events. Streaming engines handle this with event-time windows plus watermarks (a heuristic for "how late is too late") and a policy for stragglers. Say it briefly and defer the deep treatment to the pipelines guide's late-arriving-data chapter rather than re-deriving watermarks on the whiteboard.

Warehouse (Snowflake/BigQuery/Redshift): schema-on-write, SQL, great BI and governance, managed compute — pay for convenience and some lock-in. Lake (object storage + open files): cheap, any format, schema-on-read, needs its own query engine and can rot into a swamp without discipline. Lakehouse (open table formats — Iceberg/Delta/Hudi — over the lake): ACID, time-travel, schema/partition evolution, queried by many engines — lake economics and openness with warehouse-like guarantees. Answer with a decision matrix keyed to workload and team, not a favorite. See 06.

Raw Parquet is just files: no atomic multi-file commits, no isolation between a reader and a concurrent writer, no way to update/delete a row without rewriting partitions, and no safe schema evolution. Iceberg/Delta/Hudi add a metadata/manifest layer that gives ACID snapshots, time-travel, hidden partitioning and partition evolution, safe schema evolution, and efficient upserts/deletes (needed for CDC and GDPR erasure). That's what turns a lake into a lakehouse — you get warehouse-grade table semantics while the data stays in open files any engine can read.

Medallion = bronze (raw, append-only, immutable landing), silver (cleaned, deduped, conformed), gold (modeled marts for consumption). It makes lineage, reprocessing, and quality gates obvious per layer. The small-files problem: streaming or frequent micro-batch writes produce thousands of tiny files, and query planners choke on metadata and per-file overhead. Mitigate with compaction/OPTIMIZE jobs, right-sized target file sizes (~100MB–1GB), and partitioning/clustering aligned to query filters — not so granular that each partition is a handful of tiny files.

OLTP row stores (Postgres/MySQL) optimize single-row reads/writes for the application — fast point lookups, transactions. OLAP columnar formats (Parquet/ORC in the lake, or a columnar warehouse) store each column contiguously so analytical scans read only the needed columns and compress well, but are terrible at single-row mutation. The platform's job is to move data from the row-oriented source (via CDC) into columnar analytics storage — you don't run analytics on the OLTP DB, and you don't serve the app from the warehouse.

Total cost of ownership, not license price: headcount to build and operate, opportunity cost, time-to-value, and reliability. Build what's core/differentiating, buy what's commodity. Weigh team size and maturity, SLA needs, lock-in and exit cost, data gravity/egress, and compliance. Default heuristic: buy to start, build when the bill or a hard constraint justifies it. The spectrum runs fully-managed SaaS → managed OSS → self-hosted OSS → in-house. See 07. Staff signal: naming the ongoing operational cost, not just the sticker.

When scale economics flip: at extreme volume the SaaS bill (e.g. per-row ingestion pricing on billions of rows) dwarfs the cost of engineers running OSS. When you have a genuinely unique requirement no vendor serves. When lock-in or data-residency/compliance constraints are existential. When the capability is core differentiation. Otherwise buying wins on time-to-value and total cost. The mistake is a small team building a bespoke platform to save a license fee, then spending its whole headcount operating it.

Typical early-stage buy-heavy stack: ingestion → buy (Fivetran/Airbyte) unless volume is huge; storage/compute → buy (Snowflake/BigQuery) or lakehouse (Databricks); transform → dbt (open, low-cost, usually keep); orchestration → managed Airflow/Dagster or buy; catalog/governance → buy or adopt OSS as it matures; quality → OSS + contracts; BI → buy. Then revisit component-by-component as the bill grows — ingestion at high row counts and warehouse compute are the two that most often justify building or moving to self-hosted OSS. Answer as a table, keyed to volume and team.

Skew: one key (a whale tenant, a null/default, a celebrity user) gets a disproportionate share, so one partition/consumer/reducer is the bottleneck while others idle. Diagnose by looking at per-partition throughput and lag. Fixes: salting the key (append a bucket suffix and fan out, aggregating in a second pass), a composite key with higher cardinality, isolating the hot key onto its own partition, or two-phase aggregation. The real fix is key design up front — choose a partition key with even cardinality and no natural hotspot. See 08.

CAP: under a network partition you trade consistency for availability. PACELC adds the everyday case: Else (no partition) you still trade Latency vs Consistency. For analytics platforms this usually cashes out as: the source-to-analytics pipeline is eventually consistent, and there's inherent lag between an OLTP write and its appearance in the warehouse. That's fine for BI, not for a read-after-write online feature — which is why online features often need a low-latency store fed separately. The Staff answer names the acceptable lag per consumer instead of hand-waving "eventually consistent."

RPO = how much data you can afford to lose (drives replication frequency and log retention); RTO = how fast you must recover (drives multi-region standby vs cold restore). Durability comes from replication factor and cross-region backups; recoverability comes from replayable logs (reset the offset and reprocess) and reproducible transformations. Because the event log is retained and transforms are idempotent, "recover" often means replay rather than restore. Delivery-semantics depth lives in the pipelines guide's reliability chapter.

Dimensional (star/snowflake) is the default for BI: conformed facts and dimensions, intuitive for analysts, great for slice-and-dice. Data Vault suits many volatile sources needing full auditability and agile integration — more joins, more complexity, usually a raw/integration layer feeding dimensional marts. One Big Table / wide denormalized fits columnar engines and ML feature consumption where join cost dominates and storage is cheap. Model for the consumer: star for BI dashboards, wide tables or a feature store for ML. See 09.

A producer↔consumer agreement on schema, semantics, and SLA — the shape, meaning, freshness, and quality guarantees of a dataset — versioned and owned. Enforce it shift-left: in the producer's CI (schema-registry compatibility check, tests) and at ingest (reject or dead-letter violations) rather than discovering breakage in a downstream dashboard. It reframes schema evolution from "hope nothing breaks" to "breaking changes fail the producer's build." Test mechanics live in the pipelines guide's validation chapter.

An append-only raw lake and "just keep everything" collide with the right to be forgotten. You need row-level delete, which is exactly why open table formats (Iceberg/Delta/Hudi) with efficient deletes matter — targeted erasure without rewriting whole partitions by hand. Complement with PII tokenization/pseudonymization so the raw payload holds a token and the mapping lives in one deletable vault (crypto-shredding), column masking and access control for who sees PII, and retention/deletion policies enforced automatically. Governance is a design constraint, not a bolt-on. See 09.

Run the method, don't dump an architecture. (1) Requirements + clarifiers: freshness per consumer, volumes, correctness bar, budget. (2) Size it. (3) Sketch sources → ingestion (log-based CDC via Debezium off Postgres + events into Kafka) → lakehouse bronze → transform (silver/gold) → warehouse marts + a feature store → BI / ad-hoc / online serving, with orchestration and catalog/governance underneath. (4) Decisions defended against requirements: log-based CDC (need deletes + freshness), batch for BI, streaming only for the near-real-time features, lakehouse (mixed BI + ML, open formats), buy-to-start per component. (5) Deep dives on hotspots, schema evolution, DR, cost. This is the fully worked capstone in 10-the-design-session.

Bulleted attack. Requirements: which sources matter, freshness (almost certainly hours, not seconds → batch), analyst consumers → SQL warehouse. Ingestion: buy a connector platform (Fivetran/Airbyte) — 30 bespoke API integrations is exactly the commodity work to not build; watch per-row pricing at volume. Storage: warehouse-first (Snowflake/BigQuery) — structured, BI-facing, no ML-at-scale driver yet. Transform: dbt with staging → marts, tests and contracts. Governance: catalog, PII handling, cost monitoring (this stack's bill creeps). Batch orchestration on a schedule; reconciliation against source row counts. The whole thing is a buy-heavy modern-data-stack case — the interesting decisions are connector cost at volume and where to draw the modeling layers, not novel infrastructure.