Batch versus streaming: how do you decide?
By the consumer's clock, not the technology's appeal. Seconds-fresh dashboards and enforcement need a stream; daily reports and backfills want batch, which is cheaper, simpler, and restates cleanly. Most real answers are 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: writes keyed on a natural or event id with merge semantics, no blind appends, no counters incremented on consume. It is the property that turns retries, replays, and backfills from incidents into routine operations.
Exactly-once: real or marketing?
Real as an end-to-end outcome, not as a transport guarantee. Delivery is at-least-once almost everywhere; exactly-once output is composed from replayable sources, deterministic processing, and idempotent or transactional sinks. Saying that composition is the interview answer; claiming a broker does it for you is the trap.
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, is quietly a completeness-signal question.
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 is 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 inserts, updates, and deletes as they commit. 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. Breaking changes such as a type change, a rename, or a dropped field need 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 does not 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 cannot 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, usually event date, and keep the filter a direct comparison against a literal or bound parameter. 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?
Four 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, cardinality, or a key metric move beyond a threshold). 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. Practical set: freshness against the promised delivery time, row-count deviation from the trailing baseline, quality check failures, and end-to-end latency for streaming. 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 does not 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 actually ready, and it gives you retries with backoff, backfill over a date range, per-task SLAs, and a visible failure surface. The interview point is that the DAG encodes completeness, and a schedule alone only encodes hope.
