data engineering

SCD2 separates people who have run pipelines from people who have read about them. The whole answer hinges on one atomic MERGE that closes the old row and opens the new one, and stays correct when the job retries.

Sessionization separates people who reach for a self-join from people who know LAG plus a running sum. Here is the two-pass pattern that scales to billions of events, plus the edge cases interviewers push on.

Pipelines fail and retry; the real question is whether a retry corrupts your data. The signal is idempotent writes (not 'exactly-once delivery') plus watermarks for late data. Here is how to make re-runs safe without double-counting.

The Databricks-flavored performance question. The signal is going straight to the usual suspects (skew, shuffle, spill) via the Spark UI, not guessing. Here is the diagnostic order and the fixes that actually 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. The signal is choosing the right ranking function for ties and knowing window frames. Here is the pattern and the tie nuance.

A hard streaming-systems question: dedup at high throughput with bounded state. The signal is a tiered state design (probabilistic filter in front of durable state) plus watermark-driven eviction. Here is the architecture that does not OOM.

A pipeline-design question that rewards matching the architecture to the freshness requirement, not chasing real-time for its own sake. The signal is the latency-vs-complexity tradeoff and knowing why Kappa emerged to kill Lambda's dual codebase. Here is the decision.

The difference between a query that scans a terabyte and one that scans a gigabyte is usually layout, not the engine. The signal is partition pruning, columnar formats, and the small-files problem.

Syncing a warehouse with a live OLTP database is a CDC problem, and the naive 'full reload nightly' or 'query by updated_at' answers have real holes. The signal is log-based CDC and idempotent merges.

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

Wide tables with a column per period are painless to read and painful 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.

Partition on the wrong key and you get millions of tiny files or skewed giants. The signal is partitioning on a low-cardinality filter, clustering within partitions on the next predicate, and watching file size.

At-least-once duplicates and at-most-once loses data; everyone wants exactly-once and few can explain how Kafka delivers it. The signal is idempotent producers plus transactional read-process-write plus the read-committed isolation that ties them together.

All three give you ACID on object storage, but they were built for different problems. The signal is matching the format to the workload (engine neutrality, Spark-native ACID, or streaming upserts) rather than naming a favorite.

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

When a metric looks wrong, lineage tells you which upstream table to blame. The signal is distinguishing table vs column lineage and knowing the three ways to capture it: SQL parsing, runtime hooks, and metadata APIs.

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

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

A backfill that double-counts or takes down the live pipeline is worse than the original bug. The signal is idempotent partition-scoped rewrites, isolating backfill compute from live runs, and validating before swapping.

A flat path extract is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows and a clear rule for when to flatten vs query in place.

Most candidates only know 'use Debezium.' The signal is comparing three CDC mechanisms by how they capture deletes, load the source, and handle ordering, plus the snapshot-to-stream stitching that trips up real deployments.

Anyone can say 'filter and dedup.' The signal is the funnel in cost order, the MinHash/LSH banding math, and knowing the shuffle at billions of documents is what actually costs you, plus benchmark decontamination people forget until their eval numbers get challenged.