Batch versus streaming: how do you decide?
Decide by the consumer's clock. Seconds-fresh dashboards and enforcement need a stream. Batch costs less and restates cleanly, which suits daily reports and backfills. Most real answers use both, split at an immutable log, with the batch path as the source of corrected truth.
What does idempotency mean in a pipeline, concretely?
Re-running yesterday today produces the same result. Mechanically, every write is a merge keyed on a natural or event id. Blind appends break that, as do counters incremented on consume. Idempotency is why a retry or a replay is a routine operation instead of an incident, and backfills depend on it the same way.
Exactly-once: real or marketing?
Real as an end-to-end outcome, not as a transport guarantee. Delivery is at-least-once almost everywhere, so exactly-once output has to be composed: the source must be replayable and the processing deterministic, and the sink has to be idempotent or transactional. The interview answer describes that composition, and claiming a broker does it for you is the mistake interviewers listen for.
Where does the watermark fit, and what does it bound?
A watermark is the pipeline's declaration of how late it will wait for event-time stragglers. It bounds streaming state (dedup and window buffers can be dropped past it) and defines when a window can close. Without one, exactly-once dedup state grows forever, which is the follow-up it exists to answer.
Why do interviewers keep asking about backfills?
Because backfills expose every weak joint at once: mutable raw data, non-idempotent writes, hardcoded dates, aggregates with no restatement policy. If your design answers 'rerun the last 30 days' with 'run the same jobs with different parameters', the architecture is sound.
Completeness signals versus cron: what is the difference?
Cron assumes the data is ready; a completeness signal proves it. Upstream writes a manifest or success marker per partition, and downstream triggers on it. Every consolidation question, like the multi-region one on this page, turns on completeness signals.
What is a dead-letter queue for, and what goes wrong without one?
Events that fail parsing or validation route to a quarantine with enough context to replay them after a fix. Without one you choose between halting the pipeline on one bad record and silently dropping it; both are wrong answers in a round. Mention the replay path, not just the queue.
How do you keep a raw layer trustworthy?
Immutable and append-only, exactly as received, with the schema it arrived in. Normalization happens beside it, never over it. The raw layer is what makes every downstream mistake recoverable, which is why audit-heavy prompts, like the trading-data one here, make immutability an explicit requirement.
How do you make a SQL data pipeline idempotent?
Make the write replace a deterministic slice rather than append to it. In practice that's a MERGE keyed on the business key, or a delete-then-insert scoped to the partition being rebuilt, both inside one transaction so a mid-run failure leaves nothing half-applied. The anti-pattern is a bare INSERT on the retry path: the first partial run leaves rows behind and the retry doubles them.
What is the difference between ETL and ELT?
ETL transforms before loading, so the warehouse only ever sees modeled data, which suited expensive warehouses with limited compute. ELT loads raw first and transforms inside the warehouse, which is the modern default because cloud warehouses separate storage from compute and because keeping the raw layer makes every transformation replayable. The tradeoff is governance: raw data in the warehouse still needs access control.
What is change data capture and why use it over polling?
CDC reads the source database's write-ahead log to stream every row change as it commits, deletes included. Polling with a query on updated_at misses hard deletes entirely, misses intermediate states between polls, and puts read load on the production database. CDC captures every change in order with near-zero source impact, at the cost of operating a connector and handling schema changes in the log.
How do you handle schema evolution?
Treat the schema as a contract. Additive changes such as a new nullable column are backward compatible and can flow through automatically. A rename or a type change is breaking, and so is a dropped field; each needs a version and a migration window where both shapes are accepted. A schema registry with compatibility checks enforces this at the producer, which is far better than discovering it when the consumer fails.
How do you detect and mitigate data skew in a distributed job?
Detect it by counting rows per join key and by looking for a task whose runtime and shuffle bytes dwarf the median. Mitigate by broadcasting the small side to remove the shuffle, by salting the hot key with a random suffix and re-aggregating after the join, or by isolating the hot keys into a separate job. Adding executors doesn't help: one key still lands on one task.
What is partition pruning and how do you design for it?
Pruning is the engine skipping partitions that can't satisfy the query's filter, turning a full-table scan into a read of one day. To get it, partition on the column queries actually filter on. That is usually event date. Keep the filter a direct comparison against a literal or bound parameter, because wrapping the partition column in a function or casting it defeats pruning silently.
What are the data quality checks you would add to a pipeline?
4 families: freshness (did data arrive in the window), volume (is the row count within expected bounds against history), schema (are the columns and types what the contract promised), and distribution (did null rates or cardinality cross a threshold, or a key metric move past one). Each should fail the run rather than warn, because a warning nobody reads is the same as no check.
How do you monitor a pipeline, and what do you alert on?
Alert on the SLA the consumer cares about, not on task success. A job that succeeds while producing zero rows is the failure worth paging for. A practical alert set covers freshness against the promised delivery time and row-count deviation from the trailing baseline. Add quality check failures, and for streaming, end-to-end latency. Everything else is a dashboard, not a page.
What is the difference between at-least-once, at-most-once, and exactly-once?
At-most-once may drop messages and never duplicates them. At-least-once never drops but may duplicate, which is what most transports actually give you. Exactly-once is the effect you engineer on top of at-least-once by making the sink idempotent or transactional, so replaying a duplicate is a no-op. Saying that exactly-once is an end-to-end property rather than a transport setting is the senior answer.
How do you design a backfill that will not take production down?
Make it partition-scoped and idempotent so each unit can be retried alone, run it on separate compute or with a concurrency cap so it doesn't starve the scheduled load, and process in chronological chunks with checkpointing so a failure resumes rather than restarts. Then verify against the same quality checks the incremental path uses before publishing.
How do you build a Slowly Changing Dimension Type 2 load in a pipeline?
Compare each incoming row against the current version for that natural key. Unchanged rows are skipped; changed rows close the existing version by setting valid_to and clearing the is_current flag, then insert a new version with a fresh surrogate key and valid_from at the change time. Both writes belong in one transaction, and the whole step should be a MERGE keyed on the natural key so a re-run is idempotent rather than duplicating history.
What is orchestration, and what does a DAG give you over cron?
Cron fires on a clock and knows nothing about whether the upstream data arrived. A DAG expresses dependencies, so a task runs when its inputs are ready, and it gives you retries with backoff, backfill over a date range, per-task SLAs, and a visible failure surface. In the interview, say that the DAG encodes completeness while a schedule only assumes it.
