data engineering

SCD2 tells apart engineers who have actually run pipelines from those who have only read about them. The answer rests on a single atomic MERGE that closes the prior row while opening the new one, and holds up when the job retries.

Sessionization tells apart candidates who reach for a self-join from those who know LAG plus a running sum. Below is the two-pass pattern that holds up across billions of events, along with the edge cases interviewers press on.

Pipelines fail and retry, so the real question is whether a retry corrupts your data. What interviewers look for is idempotent writes (not 'exactly-once delivery') alongside watermarks for late data. Here is how to keep re-runs safe without double-counting.

The Databricks-flavored performance question. What interviewers want is a candidate who heads straight for the usual suspects (skew, shuffle, spill) through the Spark UI rather than guessing. Here is the diagnostic order and the fixes that genuinely move the needle.

Top-N-per-group is the window-function question every data round asks, and the trap is RANK vs ROW_NUMBER vs DENSE_RANK. What interviewers want is picking the right ranking function for ties and understanding window frames. Here is the pattern and the tie nuance.

A hard streaming-systems question: dedup at high throughput while keeping state bounded. What interviewers reward is a tiered state design (a probabilistic filter ahead of durable state) plus watermark-driven eviction. Here is the architecture that does not OOM.

A pipeline-design question that rewards fitting the architecture to the freshness requirement rather than chasing real-time for its own sake. What interviewers want is the latency-vs-complexity tradeoff and an understanding of why Kappa arose to kill Lambda's dual codebase. Here is the decision.

What separates a query that scans a terabyte from one that scans a gigabyte is usually layout, not the engine. Interviewers listen for partition pruning, columnar formats, and the small-files problem.

Keeping a warehouse in sync with a live OLTP database is a CDC problem, and the naive 'full reload nightly' or 'query by updated_at' answers leave real gaps. The signal is log-based CDC and idempotent merges.

A new column is safe; a rename quietly corrupts every dashboard downstream. The signal is knowing which changes stay compatible, enforcing data contracts in CI, and running expand-contract migrations for the breaking ones.

Wide tables with a column per period are easy to read and hard to query. The signal is unpivoting to long format with UNPIVOT or a UNION/cross-join trick, and knowing how NULLs and types bite you.

Pick the wrong partition key and you end up with millions of tiny files or lopsided giants. The signal is partitioning on a low-cardinality filter, clustering inside partitions on the next predicate, and keeping an eye on file size.

At-least-once creates duplicates and at-most-once drops data; everyone asks for exactly-once yet few can explain how Kafka provides it. The signal is idempotent producers, transactional read-process-write, and the read-committed isolation that binds them together.

Each of the three brings ACID to object storage, yet they were built to solve different problems. The signal is fitting the format to the workload (engine neutrality, Spark-native ACID, or streaming upserts) instead of naming a favorite.

Downstream pipelines shatter when an upstream team renames a column at 2am. The signal is a data contract: a versioned, enforced agreement on schema and semantics, verified at the producer before bad data spreads.

When a metric looks off, lineage points you to the upstream table at fault. The signal is separating table from column lineage and knowing the three capture methods: SQL parsing, runtime hooks, and metadata APIs.

Overlapping subscription or session windows have to collapse into clean periods, and a naive self-join runs quadratic. The signal is the gaps-and-islands trick: a running max end-time that marks where each new island begins.

Events arrive minutes or days after they happened, and a window that has already closed reports wrong counts. The signal is event-time vs processing-time, watermarks with allowed lateness, and how you fix already-emitted aggregates.

A backfill that double-counts or knocks over the live pipeline is worse than the bug it fixes. The signal is idempotent partition-scoped rewrites, keeping backfill compute isolated from live runs, and validating before the swap.

Pulling a scalar by path is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows plus a clear rule for when to flatten versus query in place.

Plenty of candidates know only 'use Debezium.' The signal is comparing three CDC mechanisms on how each captures deletes, loads the source, and preserves ordering, plus the snapshot-to-stream stitching that trips up real deployments.

Anyone can say 'filter and dedup.' The signal is the funnel arranged by cost, the MinHash/LSH banding math, and recognizing that the shuffle across billions of documents is what actually runs up the bill, plus the benchmark decontamination people forget until their eval numbers get challenged.