SQL & 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.

A data-warehouse fundamentals question that separates people who model for analytics from people who only know OLTP normalization. The signal is facts vs dimensions, the star schema, and why analytics denormalizes where transactional systems normalize.

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.

Bad data silently poisons everything downstream (dashboards, models, decisions), and 'it ran without error' is not 'it's correct.' The signal is the categories of checks and failing loud at the boundary.

Query tuning is a core data-engineering skill, and the signal is reading the execution plan before changing anything, then applying the right fix. Here is the diagnostic method that separates guessers from engineers.

Dedup is a daily data-engineering task and a common screen. The signal is ROW_NUMBER to keep the right record (not just DISTINCT), defining what 'duplicate' actually means, and handling near-duplicates at scale.

Hierarchies (org charts, bill-of-materials, category trees) need recursion, and a self-join only goes one level deep. The signal is the recursive CTE with its anchor plus recursive members, and knowing how it terminates.

Pivoting (rows to columns) is constant in analytics and reporting, and the portable trick is conditional aggregation, not a vendor PIVOT clause. The signal is the CASE-inside-aggregate pattern and knowing its one real limit.

The canonical product-analytics SQL question. The signal is grouping users by signup cohort, computing each activity's period offset, and counting distinct returners per offset to build the retention triangle.

Joins are the heart of SQL, and the real signal is precise semantics: semi/anti joins (EXISTS / NOT EXISTS), the NOT IN null trap, and the fan-out bug that silently doubles your sums.

The subtlety in rolling aggregates is the window frame clause, especially ROWS vs RANGE. The signal is the correct frame for a moving window and the tied-rows gotcha that corrupts running totals.

Means lie on skewed data like latency and spend, and SQL has dedicated functions for the truth. The signal is PERCENTILE_CONT/DISC for exact quantiles, NTILE for bucketing, and why p50/p99 beat the average.

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.

You cannot wait for all the data, and buffering an unbounded stream will OOM. The signal is windowed joins with watermarks for stream-stream, lookup joins for stream-table, and a clear story for late and out-of-order events.

Set operations and NULL semantics trip up even senior engineers. The signal is UNION vs UNION ALL and its dedup cost, plus three-valued logic where NULL equals nothing, not even itself.

The top cause of mysteriously slow Spark jobs: one partition does most of the work while the rest idle. The signal is reading the symptom (a few straggler tasks) and reaching for the right fix, salting, broadcast, or AQE.

Materialized views trade storage and freshness for query speed by precomputing results. The signal is knowing the view-vs-materialized-view tradeoff and the refresh/staleness question.

Time-based analysis is everywhere in analytics, and time zones and bucketing are the common traps. The signal is DATE_TRUNC for bucketing, interval math, and storing UTC.

Correlated subqueries read naturally but can execute per row (O(n squared)), a classic slow-query cause. The signal is recognizing them and rewriting as joins or window functions.

Reporting often needs subtotals and grand totals across several dimensions, and stitching that with many UNIONs is wasteful. The signal is GROUPING SETS, ROLLUP, and CUBE in one pass.

Exact COUNT(DISTINCT) over billions of rows tracks every unique value, so it gets slow and expensive. The signal is knowing when a sketch like HyperLogLog buys huge memory and speed wins for a sub-percent error budget.

A LATERAL join lets a FROM-clause subquery reference columns from the table before it, which a normal join cannot. The signal is recognizing the per-row use cases: top-N per group, table functions, unnesting arrays.

Modern warehouses store JSON natively, and querying it well separates strong data engineers from the rest. The signal is path access plus unnesting, and a clear call on when to flatten hot fields versus keep the schema-on-read flexibility.

You cannot filter on a window function in WHERE, so people wrap queries in subqueries. QUALIFY filters window results directly. The signal is knowing why WHERE cannot do it and writing top-N-per-group cleanly.

MERGE handles insert-or-update-or-delete in one statement, the backbone of incremental loads and slowly-changing dimensions. The signal is the matched/not-matched logic and what makes a retry safe.

Modern table formats let you query a table as of a past version or timestamp, the backbone of reproducibility, audits, and recovery. The signal is the versioned-snapshot mechanism and where it breaks.

The single most common way SQL leaks the future into a training set is a careless join to a feature table. Point-in-time correctness is the fix, and it is an as-of join. Here is how to write it.

Funnels look like simple counts until you require the steps happen in order and within a window. Counting each step independently overstates conversion. Here is the ordered-funnel query.

Consecutive-run problems (active streaks, uptime windows, price-stable periods) all reduce to one trick. Once you see the row-number difference, they collapse to a window function and a group-by.

You don't always need a dedicated vector database. When embeddings live next to your relational data, nearest-neighbor in SQL makes filters and joins trivial. Here is how, and the scale where it breaks.

Building a training set from a billion-row table means sampling, and the naive 'ORDER BY random() LIMIT n' sorts the whole table. Stratified sampling needs more care still. Here are both, done efficiently.

Defining the label is half the modeling problem, and the SQL hides two leakage traps: looking into the future for features, and a label window that isn't fully observed yet. Here is the correct query.

Bucketing turns a skewed continuous feature into something a model (or a report) handles well, and the choice between equal-width and equal-count bins changes everything on skewed data. Here is both in SQL.

Production drift monitoring often lives in the warehouse, not a fancy tool. Comparing two distributions in SQL with a metric like PSI is a few CTEs. Here is how to compute it.

Days with zero events simply don't appear in an event table, so a 7-day average silently averages the wrong 7 rows. A date spine makes the missing days explicit. Here is the pattern.

The applied data-wrangling screen: here is a grubby database, produce a clean analysis-ready table. The signal is profiling before transforming, doing set-based cleaning in SQL and row-level fixes in Python, joining without fanning out rows, and validating the output instead of trusting it.

An org-chart recursion walks a tree, but a general graph has multiple paths and back-edges. The signal is accumulating the visited path to prune cycles and ranking paths by cost to get the shortest one.

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.

Two queries that look identical except for ROWS vs RANGE return different numbers, and most candidates cannot say why. The signal is knowing RANGE groups by value (peers) while ROWS counts physical rows.

Everyone says 'add an index,' but a strong engineer reads the plan first. The signal is finding the dominant operator, comparing estimated vs actual rows, and recognizing the seq-scan, bad-join, and spill patterns.

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.