Top 100 Data Engineer Interview Questions

INNER keeps only matched rows. LEFT keeps every left row and fills NULL where the right side has no match. FULL keeps everything from both sides. Then give the row-count expectations: INNER can return fewer rows than either input; LEFT returns at least the left row count, and more than it when the right side has duplicate keys. That last clause is the setup for the fanout question that usually follows.

Classic fanout: the joined table has multiple rows per key (one order, three shipments), so every order row repeats and SUM inflates. Diagnose by comparing COUNT(*) before and after the join, or COUNT(DISTINCT order_id) against COUNT(*).

The fix is structural: aggregate the many-side down to the join's grain in a CTE, then join one-to-one. A DISTINCT inside the SUM treats the symptom and breaks the moment two shipments legitimately share an amount.

NOT EXISTS with a correlated subquery, LEFT JOIN with WHERE b.key IS NULL, and NOT IN. Then disqualify NOT IN yourself: if the subquery returns a single NULL, three-valued logic makes the whole predicate UNKNOWN and you silently get zero rows. NOT EXISTS is NULL-safe and usually plans best.

SQL equality with NULL evaluates to UNKNOWN, not true, so join keys containing NULL never match, including against other NULLs. Rows quietly vanish from INNER joins. If NULL should mean 'same', make it explicit: COALESCE both keys to a sentinel, or use IS NOT DISTINCT FROM where the dialect supports it. Mentioning that this is three-valued logic, and that WHERE filters also drop UNKNOWN, signals real depth.

JOIN employees e1 to e2 on e1.manager_id = e2.manager_id with e1.id < e2.id. The strict inequality removes self-pairs and keeps each pair exactly once. With != instead, every pair appears twice (once in each order), and that bug usually surfaces only when someone counts the output.

WHERE filters rows before aggregation; HAVING filters groups after. WHERE can use indexes and shrinks the data before the expensive grouping; HAVING cannot. The practical rule: any predicate that does not reference an aggregate belongs in WHERE, and moving it there is one of the cheapest query optimizations that exists.

COUNT(*) counts rows. COUNT(col) skips NULLs. COUNT(DISTINCT col) deduplicates and is dramatically more expensive at scale because it must track every value seen. On billions of rows the working tool is APPROX_COUNT_DISTINCT (HyperLogLog, roughly 2% error, constant memory); know where the error is acceptable, e.g. dashboard audience counts yes, billing no.

SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed, one expression per output column. It is portable across every dialect, unlike the PIVOT operator, and it composes with other aggregates in the same SELECT. Postgres's FILTER (WHERE ...) is a cleaner spelling of the same idea.

GROUP BY the key with HAVING COUNT(*) > 1 finds the keys; to get the rows themselves, join back or use a windowed COUNT(*) OVER (PARTITION BY key) > 1. Add the NULL caveat: GROUP BY treats NULLs as equal but joins do not, so the join-back can miss NULL-keyed duplicates the GROUP BY found.

Divide by NULLIF(impressions, 0) so division by zero yields NULL instead of an error, then COALESCE the result if the report needs a 0. Also cast to float first: integer division silently floors to 0 in several dialects and is one of the most common silent-wrong-answer bugs in screens.

On a tie, ROW_NUMBER picks an arbitrary winner, RANK leaves gaps (1, 1, 3), DENSE_RANK does not (1, 1, 2). Map the requirement to the semantics: 'second highest salary' wants DENSE_RANK; 'exactly one row per user' wants ROW_NUMBER plus a unique column at the end of the ORDER BY, because without that tiebreaker the query returns different rows on different runs.

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, id DESC) and keep rn = 1. The trailing id in the ORDER BY is the deterministic tiebreaker for same-timestamp rows. This exact shape is the bread and butter of warehouse work (CDC dedup, snapshot compaction), which is why it appears in so many screens.

Aggregate revenue per (category, product), apply DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC), filter rank <= 3 in an outer query. DENSE_RANK rather than ROW_NUMBER, because 'ties included' means two products with equal revenue both stay; ROW_NUMBER would cut one of them arbitrarily.

WITH revenue AS (
  SELECT p.category, p.product_name, SUM(t.total_amount) AS revenue
  FROM transactions t
  JOIN products p ON p.product_id = t.product_id
  GROUP BY p.category, p.product_name
)
SELECT category, product_name, revenue
FROM (
  SELECT *, DENSE_RANK() OVER (
    PARTITION BY category ORDER BY revenue DESC
  ) AS rk
  FROM revenue
)
WHERE rk <= 3;

QUALIFY filters on window-function results directly, replacing the wrap-in-a-CTE-and-filter dance. It exists in Snowflake, BigQuery, and DuckDB; Postgres, MySQL, and SparkSQL need the subquery, because window functions evaluate after WHERE and HAVING and neither clause can see their output.

Correlated count: SELECT COUNT(*) FROM t t2 WHERE t2.grp = t1.grp AND (t2.score > t1.score OR (t2.score = t1.score AND t2.id < t1.id)) + 1. Some loops (Meta's SQL round has a reputation for this) ask you to do window work in plain ANSI SQL to prove you understand what the window is doing rather than reciting syntax.

Truncate to month, SUM, then LAG(revenue) OVER (ORDER BY month) and compute (current - previous) / NULLIF(previous, 0) * 100. Two details to handle without being prompted: NULLIF guards the first month, and 'months with no sales' need a decision, since LAG over a series with missing months silently compares across the gap.

AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Two edge cases decide the grade: the first six days average over a partial window, which may or may not be acceptable, and ROWS counts physical rows, so missing days silently widen the time span unless you join a date spine first.

They differ exactly when ORDER BY has ties. ROWS counts physical rows; RANGE includes all peer rows with the same sort value, so duplicate timestamps make RANGE windows larger than you expect. And the default frame when you write ORDER BY with no frame is RANGE, not ROWS, which is how running totals end up jumping in steps. Write the frame explicitly every time.

LAST_VALUE(reading IGNORE NULLS) OVER (PARTITION BY device ORDER BY ts ROWS UNBOUNDED PRECEDING), where the dialect supports IGNORE NULLS (Snowflake, BigQuery, Oracle). Postgres lacks it, so use the two-step trick: a windowed COUNT over non-NULL readings assigns every row to a fill group, then MAX(reading) within the group recovers the carried value.

revenue / SUM(revenue) OVER (PARTITION BY category): a windowed aggregate without ORDER BY spans the whole partition, letting you mix row-level and group-level values in one pass with no self-join. This 'ratio to total' shape generalizes to percent-of-day, percent-of-cohort, and contribution analysis.

Two passes: LAG the previous event time per user, flag a new session where the gap exceeds 30 minutes (or is NULL), then a running SUM of the flag gives each row its session number. This flag-then-cumulative-sum shape is the most important composite pattern in DE SQL; it also solves status changes, trip segmentation, and outage windows.

Deduplicate to one row per user-day, then compute date minus ROW_NUMBER() (as days) per user: consecutive days share the same difference, which becomes the island key. GROUP BY user and island key, HAVING COUNT(*) >= 3. Be ready to say why the subtraction works: both sequences advance by one exactly when days are consecutive, so the difference stays constant within a streak and jumps at every gap. Reciting the trick without that explanation reads as memorization.

LEAD(changed_at) OVER (PARTITION BY entity ORDER BY changed_at) gives each state its end time; duration is the difference, with the final open state ending at NOW() or NULL by business choice. If the log repeats the same status, first compress runs with the islands trick so 'in state X' means contiguous time, not per-row time.

Generate the calendar (generate_series in Postgres, GENERATOR in Snowflake, a recursive CTE anywhere), LEFT JOIN the facts onto it, COALESCE counts to zero. Aggregation can only describe rows that exist, so 'show me the zeros' always means manufacturing the missing rows first. Cross join the spine with the dimension when you need zeros per user, not just per day.

Either MIN/MAX windowed per partition for timestamps, or FIRST_VALUE/LAST_VALUE for full-row attributes. LAST_VALUE needs an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame, because under the default frame it just returns the current row; that gotcha is the reason this question gets asked. Two ROW_NUMBERs (ASC and DESC) filtered to 1 is the workhorse alternative.

Cohort users by signup date, LEFT JOIN activity 7 days later, then COUNT(DISTINCT active) / COUNT(DISTINCT cohort). Pin the definition before writing SQL: active exactly on day 7, active within 7 days, and active during the day-7 window are three different metrics, and the prompt is often left ambiguous deliberately to see whether you ask.

Take each user's first A (MIN or ROW_NUMBER), join B events requiring b.ts > a.ts AND b.ts <= a.ts + 7 days, count distinct users. The ordering constraint is the trap: 'did both A and B' is a different (and wrong) query. For long funnels, mention the windowed approach: MIN(CASE WHEN event = 'B' ...) OVER per user avoids one join per step.

Exact: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY latency); PERCENTILE_DISC returns an actual observed value while CONT interpolates. At scale: APPROX_PERCENTILE / APPROX_QUANTILES, t-digest under the hood, small bounded error, constant memory. On a billion rows exact p99 stops being worth its memory, and sketches have a second advantage: they merge across groups, which exact percentiles cannot.

The trap: COUNT(DISTINCT) cannot be windowed in most engines. The workable options are a self-join of days to a 30-day event window (correct, expensive), a date-spine cross join with EXISTS, or HLL sketches unioned across days where the platform supports merging. Only the sketch approach scales, because sketches merge and distinct sets do not.

Two aggregations: first count orders per user, then count users per order-count, with a CASE bucket for the 3+ tail. Double aggregation trips people who try to do it in one GROUP BY. Add the zero bucket honestly: users with no orders are not in the orders table, so the spine or a LEFT JOIN from users is required to report them.

CTEs for readability and reuse within one statement; temp tables when you reuse an intermediate across statements, want stats for the optimizer, or need to break a pathological plan; subqueries for one-off scalar or EXISTS checks. Add the optimization-fence note: old Postgres (pre-12) materialized CTEs and blocked predicate pushdown, modern engines mostly inline them, and MATERIALIZED is now an explicit hint.

Anchor: the root (manager_id IS NULL) at depth 0. Recursive member: join the CTE to employees on cte.id = e.manager_id, depth + 1. Add the safety rail without being asked: a cycle in the data loops forever, so cap the depth or track the visited path. Recursion appears rarely in screens, but when it does it is a deliberate L5 filter.

State the dialect first, because syntax diverges: Postgres ->> and jsonb_path_query, Snowflake colon-and-dot paths with explicit casts, BigQuery JSON_VALUE. Then the architecture point: extracting the same JSON field in every downstream query is a smell. Shred hot fields into typed columns at load time and keep the raw payload for schema drift.

UNNEST (or LATERAL FLATTEN in Snowflake, EXPLODE in Spark) turns each array element into a row; count per element. The trap is doing other aggregates in the same exploded query: a 3-tag order now contributes 3x its revenue. Explode in an isolated CTE, or aggregate the rest before the explode.

A LATERAL subquery can reference columns from preceding FROM items, so it runs per row: 'top 3 orders for each user' becomes a correlated LIMIT 3 instead of a window-plus-filter. CROSS APPLY is the same idea in SQL Server, and LATERAL is also the idiomatic way to call a table function per row. It beats the window approach when N is small, groups are huge, and an index on (user, ts) lets each probe stop after three rows.

In order: scan type (full scan where you expected pruning), join strategy (broadcast vs shuffle/hash vs nested loop), and where the row-count estimates diverge from reality, because misestimates cascade into bad join orders. Name sargability: a function wrapped around the filtered column (WHERE DATE(ts) = ...) defeats index use and partition pruning; rewrite as a range predicate.

Diagnose with COUNT(*) GROUP BY key ORDER BY count DESC, or one straggler task in the Spark UI. Fixes in escalation order: broadcast the small side if it fits; isolate-and-union the hot keys; salt the hot keys with a random suffix on the big side and an exploded suffix on the small side. Mention AQE skew-join handling in Spark 3 and its limits (it splits partitions, it cannot fix a single hot key inside one row group).

MERGE target USING staged ON business key: WHEN MATCHED AND data changed THEN UPDATE, WHEN NOT MATCHED THEN INSERT. The property under test is idempotence: re-running the same batch must not duplicate or corrupt, which MERGE on a stable key gives you and blind INSERT does not. Postgres spells it INSERT ... ON CONFLICT DO UPDATE. Know MERGE's failure mode: duplicate keys in the staged batch make it error (or worse, nondeterministically pick), so dedup staging first.

Result cache is free but exact-match and falls over on any data change. Materialized views auto-refresh declaratively but constrain query shape and cost compute on every base change. Incremental tables (dbt incremental, MERGE pipelines) are the workhorse: you own freshness, cost, and backfill semantics explicitly. Decide on three axes: freshness SLA, read/write ratio, and who owns the refresh when it breaks.

All three bring ACID transactions, schema evolution, and time travel to files on object storage by managing snapshots of file manifests. Iceberg is the open, multi-engine spec the industry is converging on; Delta is deepest in Databricks/Spark; Hudi optimized for upsert-heavy CDC ingestion. The format war is mostly settled (everything is growing Iceberg-compatible), so the conversation that matters is the mechanics underneath: snapshot isolation, compaction, and metadata pruning, not vendor trivia.

defaultdict(list), one pass, append by key. The contrast they want: sort-then-groupby is O(n log n) and only groups adjacent runs, so the dict approach wins for unsorted input. Mention dict ordering is insertion-stable since 3.7, which often removes the need to sort at all.

Dict keyed on the tuple (user_id, event_id); replace the stored record when the incoming timestamp is newer. One pass, O(n) time, O(unique keys) space. It is the Python twin of the SQL ROW_NUMBER dedup: same problem, same grain reasoning, different tool.

latest = {}
for e in events:
    key = (e["user_id"], e["event_id"])
    if key not in latest or e["ts"] > latest[key]["ts"]:
        latest[key] = e

Recurse over dict items, building the joined key path; on non-dict values, emit (path, value). The decisions are the content: lists can be exploded into rows, index-keyed (tags.0), or serialized, and each choice changes the downstream schema. Name the production failure too: a.b arriving as a scalar in one record and an object in the next.

Build a dict index on the smaller list (O(m)), then stream the larger list and look up by key (O(n)): a hash join, O(n + m) total versus O(n*m) for nested loops. Building on the smaller side is the same decision a query engine makes when it broadcasts the small table.

Counter(items).most_common(n) is the one-liner; the depth is in the ties. most_common breaks ties by insertion order, which for your data means arbitrarily. If ties matter, sort by (-count, key) explicitly, or return everything above the Nth count, and ask which behavior the user wants, because the prompt almost never says.

Stream it: iterate the file handle line by line (or csv.reader, which is already lazy), aggregate as you go, never materialize a list. If the consumer needs batches, write a generator that yields chunks of N rows. Memory stays O(chunk) instead of O(file); the file handle's laziness is the whole trick, and naming that is the difference between using the idiom and understanding it.

A generator computes values on demand and holds one at a time; a list holds everything. Lazy wins for pipelines (read, parse, filter, write) where stages compose without intermediate materialization, and for early exit (first match in a huge file). Name the two traps: generators are single-use, and an unconsumed generator has done nothing yet, including raising its exceptions.

heapq.merge: lazy, O(total log K), constant memory beyond the heap of K heads. This is the merge step of external sort and exactly how log shards and sorted partition files get combined. Hand-rolling it with a heap of (value, source_index) tuples is the whiteboard version; knowing the stdlib name is the production version.

Single pass: track previous timestamp per user, increment a session counter when the gap exceeds the threshold or the user changes. O(n) after sorting, and the same computation as the SQL LAG-plus-running-SUM version. The round usually continues into what happens when the input is not sorted, and what changes when it never ends.

Compile the pattern once outside the loop, use named groups so the extraction is self-documenting, and route non-matching lines to a dead-letter list with a counter instead of raising. Malformed input is data, not an exception; of the three options (raise, drop, route), silently dropping is the one that corrupts trust in everything downstream.

OrderedDict: move_to_end on get, popitem(last=False) on eviction. Or dict plus doubly linked list if they want the internals. Connect it to the job: this is exactly what functools.lru_cache wraps, and caching expensive lookups (schema fetches, dimension rows) is everyday pipeline work. State the O(1) claim and where it comes from (hash map plus list surgery).

Sort by start, sweep once: extend the current interval when the next start is within the current end, else emit and restart. O(n log n) from the sort. It recurs in DE work as collapsing outage windows, merging booking ranges, and compacting time-partitioned files, which is why it is the most common 'real algorithm' in DE screens.

Running sum: add the entering element, subtract the leaving one, O(n). For max value per window, a monotonic deque keeps candidates in decreasing order, evicting dominated values, amortized O(n). Both are the in-memory twin of a SQL window frame.

Kahn's algorithm: compute in-degrees, queue zero-degree nodes, pop and decrement dependents, appending newly-zero nodes. If you finish with fewer nodes than you started with, there is a cycle: that is your DAG validation. Airflow's scheduler is this algorithm, which is why it keeps appearing in staff-level loops.

Two heaps: a max-heap for the lower half, min-heap for the upper, rebalanced so sizes differ by at most one; median reads off the tops in O(1), inserts are O(log n). For percentiles at real scale the answer changes shape: t-digest or P2, accepting bounded error for constant memory, which is the same trade APPROX_PERCENTILE makes in the warehouse.

Outer-merge source and target on the natural key with indicator=True, compare tracked columns to classify unchanged, changed, and new. Expire changed rows (set valid_to, is_current False) and append new versions. Point-in-time correctness hinges on half-open intervals: the new valid_from equals the old valid_to exactly, so an as-of join can never double-match a moment in time.

pd.pivot_table(df, index, columns, values, aggfunc, fill_value=0) is groupby plus unstack with the rough edges sanded off: it handles duplicate index pairs by aggregating (that is the aggfunc) where pivot() would raise. fill_value matters because missing combinations become NaN, which silently poisons downstream sums and type expectations.

merge_asof joins each left row to the most recent right row at or before its timestamp: 'the price as of the trade', 'the feature as of the label'. It is the leakage guard: a plain join on nearest timestamp happily reads the future and trains a model that cannot be reproduced in production. Sorted inputs are required; the direction and tolerance parameters are the knobs to mention.

First make pandas smaller: categoricals for low-cardinality strings, downcast numerics, load only needed columns. Then chunk: partial aggregates per chunk, combine at the end (works because SUM and COUNT compose; medians do not). Then change engines: DuckDB or Polars on the same machine handles 10-50x pandas' practical limit, and Spark comes last, when one machine is truly done. Jumping straight to Spark skips two stages that solve most cases on one machine at a hundredth of the cost.

iterrows materializes a Series per row and pays Python interpreter cost per element; vectorized operations run the loop in C over contiguous arrays, routinely 100-1000x faster. Escalation: built-in vectorized ops, then np.where/np.select for conditionals, then .map for dict lookups, and .apply last because it is the same Python loop wearing a costume.

asyncio with a Semaphore(20) wrapping each fetch, gathered together: I/O-bound work wants event-loop concurrency, not threads-per-request. Explain WHY the cap exists (you are protecting the downstream API and your own sockets, not your CPU), and add per-request timeouts and retry-with-backoff or the first hung connection stalls the batch.

CPU-bound: multiprocessing (the GIL serializes Python bytecode in threads). I/O-bound with many connections: asyncio. I/O-bound with blocking libraries you cannot rewrite: threads. Two footnotes keep the answer current: C extensions (numpy, parquet readers) release the GIL, so threads do help there, and Python 3.13's free-threaded build is changing this calculus but is not yet the production default.

Wrap the call in a loop: on listed-as-transient exceptions, sleep base * 2^attempt plus random jitter, re-raise after max attempts with the original exception chained (raise ... from e). Jitter is load-bearing, not decoration: without it every failed worker retries in lockstep and you DDoS your own dependency. And only idempotent operations get retried at all; that constraint belongs in your first sentence, not your last.

Anything with a teardown that must run on failure: connections, transactions, file handles, locks, temp directories. @contextmanager with try/finally around the yield is the five-line version. The pipeline-specific example worth giving: a transaction context that commits on clean exit and rolls back on exception, so partial loads cannot leak into the warehouse.

Classify failures: malformed rows go to a dead-letter table with the raw payload and reason; transient infra errors retry with backoff; logic errors fail the run loudly. Wrap the load in a transaction or write-then-swap so consumers never see partial data, emit row-count and dead-letter-rate metrics, and alert on thresholds. The stance underneath: errors are an expected input class with their own data path, not an exception path.

The grain is the sentence 'one row represents exactly one X': one order line, one daily account snapshot, one impression. It comes first because every other decision (which dimensions can attach, which measures are additive, the table's size) is downstream of it. Interviewers at modeling-heavy companies fail candidates who start drawing boxes before saying this sentence.

Declare the grain (one row per order line item), then the fact (quantities, amounts, FKs only), then dimensions: product, customer, date, promotion, each denormalized. Narrate the choices: why line item and not order (item-level margin and product analysis), why date is its own dimension (fiscal calendars, holiday flags), what stays out of the fact (descriptions, anything not additive or an FK).

Transaction facts record events as they happen (one row per order). Periodic snapshots record state at intervals (account balance per day) for levels you cannot reconstruct by summing events cheaply. Accumulating snapshots track one row per process instance with milestone timestamps that update in place (order placed, shipped, delivered) for funnel and lag analysis. Pick by the question the business asks, and name a real example for each.

Additive sums across every dimension (revenue). Semi-additive sums across some but not time (account balance: sum across accounts, never across days; you take last-value or average over time). Non-additive cannot sum at all (ratios, percentages: store the numerator and denominator instead and derive at query time). Getting balances wrong is the classic BI bug, which is why this is asked.

Fact at order grain joined to both shipments and payments (each many-per-order) cross-multiplies rows: 3 shipments times 2 payments is 6 rows, and every measure double-counts. The fix is structural, not a DISTINCT: aggregate each many-side to the shared grain before joining, or model them as separate fact tables queried through conformed dimensions (drill-across). This is also why a DISTINCT inside a revenue query is a smell: it usually marks a grain mismatch someone papered over.

Type 1 overwrites (no history). Type 2 versions rows with valid_from/valid_to and is_current (full history, point-in-time joins). Type 3 keeps a previous-value column (exactly one change, rarely right). Choose by the question the business will ask: 'what is it now' is Type 1, 'what was it when the fact happened' is Type 2. Be honest about cost: Type 2 multiplies row count, and every consumer join picks up a temporal predicate that someone will eventually get wrong.

Surrogate key per version, natural key, valid_from, valid_to (half-open: NULL or 9999-12-31 for current), is_current flag. Facts store the surrogate key captured at load time, so historical joins are exact without temporal predicates; alternatively join on natural key with fact_date >= valid_from AND fact_date < valid_to. Half-open intervals (new valid_from equals old valid_to) prevent the double-match bug.

Surrogates insulate you from source-system key reuse and type changes, enable SCD Type 2 (the natural key repeats across versions, something must distinguish them), and make fact-dimension joins compact integers. Keep the natural key as an attribute with an index for lookups. The crisp version: surrogates are about ownership; your warehouse controls its keys instead of inheriting another system's decisions.

Insert an inferred member: a placeholder dimension row with the natural key, defaults elsewhere, flagged inferred. Facts join to it normally; when the real attributes arrive, update the placeholder in place (Type 1 on the inferred row) or version it properly. The alternatives both fail quietly: dropping the fact corrupts totals, holding it in limbo delays them. The same problem reappears as a follow-up inside pipeline rounds.

Snapshotting the whole dimension daily costs storage but makes every point-in-time query trivial (WHERE snapshot_date = X), parallelizes loads, and survives bad upstream data by reload. SCD2 is compact and precise but every consumer must get temporal joins right. With object storage as cheap as it is, snapshot-everything is increasingly the pragmatic default; SCD2 earns its complexity on huge dimensions or sub-daily change tracking. There is no clean winner, only a choice per dimension with a stated cost.

A bridge table between the fact and the dimension group: visit to diagnosis-group, group to diagnoses, optionally with an allocation weight so summed measures do not multiply. Then have the weight conversation: allocate 1/n evenly, weight by a primary-diagnosis flag, or report deliberately overlapping totals. Each is a business decision someone has to own; the bridge table just makes the choice visible instead of accidental.

An order fact carries order_date, ship_date, and delivery_date, all FKs to the single physical date dimension, each join aliased as its role. One physical table, multiple semantic roles: no duplication, consistent calendar attributes everywhere. Views per role keep BI-tool joins sane. The same pattern covers from/to airports and buyer/seller users.

The same dimension (same keys, same attributes, same hierarchy) shared across fact tables, so 'revenue by customer' from sales and 'tickets by customer' from support can be compared in one query (drill-across). Without conformance, each mart invents its own customer and cross-domain questions become reconciliation projects. Conformance is 80% organizational discipline (one owner per dimension) and 20% modeling, which is why it fails at companies with strong modelers and no ownership.

Low-cardinality flags (gift wrap, channel, payment type) get combined into one junk dimension holding the distinct combinations, instead of five tiny dimension tables or fact-bloating columns. The order number itself is a degenerate dimension: it stays in the fact with no dimension table, because it has no attributes but you need it for grouping and tracing back to source. Both terms are pure Kimball vocabulary; the question exists because people who have shipped a real warehouse know them and people who have only read about warehouses usually do not.

Parent-child (adjacency) is natural but needs recursion per query. For warehouses: flatten fixed-depth hierarchies into columns (level_1 to level_n), or build a closure/bridge table of every ancestor-descendant pair with depth for variable-depth rollups without recursion. Trade write-time explosion for read-time simplicity; in a read-heavy warehouse that trade is usually right.

OBT (pre-joined wide tables) wins for dashboard latency, ML feature consumption, and column-store engines that make width nearly free to scan. Stars win for governed reuse: conformed dimensions update in one place, while OBT denormalizes them into every table and a customer-name fix becomes a backfill campaign. The working synthesis: model in stars (dbt staging and marts), generate OBTs for the hot read paths, and never let the OBT become the source of truth.

Partition by the column most queries filter on, almost always event date, at a granularity that keeps partitions in the hundreds-of-MB-to-GB range. Then cluster or Z-order within partitions by the next most-filtered columns (tenant, customer) for file-level pruning. Anti-patterns to name: partitioning by high-cardinality keys (millions of tiny partitions, small-files death) and partitioning by something queries never filter on.

Bronze: raw, immutable, append-only, schema-on-read, your replay buffer. Silver: cleaned, deduplicated, conformed entities with enforced schemas. Gold: business-ready marts and metrics. The promises that matter: bronze means you can rebuild everything downstream from scratch, silver means consumers stop re-cleaning, gold means metric definitions live in one place. Call out the failure mode: layers without contracts just triple your storage.

Kimball stars remain the default for consumption: analysts understand them and BI tools assume them. Data Vault (hubs, links, satellites) earns its complexity in regulated, multi-source environments needing full auditability and parallel loading, at the cost of query-time assembly. The honest 2026 answer: most teams run Kimball-shaped marts on a lakehouse with dbt, vault-like raw history in bronze, and OBT serving layers, and the interview is checking you choose by org shape (team size, sources, audit burden), not by ideology.

Additive changes (new nullable columns) flow freely; breaking changes (renames, type narrowing, dropped columns) require a contract: schema registry with compatibility rules in streaming, dbt model contracts or versioned views in the warehouse. The mechanism matters less than the principle: producers cannot unilaterally break consumers, and the check runs in CI, not in an incident retro. Tie it to table formats: Iceberg/Delta make column adds metadata-only, which is what makes additive-by-default cheap.

Extract incrementally (updated_at watermark or CDC), land raw and immutable, transform with deterministic logic stamped by run_id, load via MERGE on business keys. The property everything hangs on is idempotence: re-running any day produces identical results, because retries and backfills are not exceptions, they are Tuesdays. Close with orchestration (Airflow/Dagster), alerting on volume anomalies, and a stated freshness SLA.

Backfill is a first-class mode, not a hack: parameterize every job by logical date, write to partition-scoped outputs so each day overwrites only itself (or write to a shadow table and swap atomically), and throttle parallelism so the backfill does not starve the daily run. The senior additions: version the transform code so you can say which logic produced which partition, and decide upfront whether downstream consumers see partial backfill state.

Dual writes (app writes DB and Kafka) cannot be atomic without distributed transactions: one write succeeds, the other fails, and the systems silently diverge forever. CDC reads the database's own log (Debezium on the WAL), making the DB the single source of truth and the stream a faithful derivative, including deletes. The outbox pattern is the middle path to name: one transactional write, relayed asynchronously.

Three tiers: schema violations dead-letter with raw payload and reason; statistical anomalies (volume, null-rate, distribution drift) warn or block by severity; business-rule failures route to owners. Two constraints define the design: a poison row must never block the partition, and it must never silently vanish. Dead-letter rate is itself a monitored metric with an alert threshold.

Kafka partitioned by user_id (ordering per user where it matters), a stream processor (Flink) for sessionization and enrichment with checkpointed state, sink to object storage for the lake plus an OLAP store for live dashboards. Then walk the three failure modes before anyone asks: consumer lag (scale partitions and consumers), hot keys (one power user skews a partition), and replay (offsets plus idempotent sinks make reprocessing safe).

Event time is when it happened; processing time is when you saw it; mobile clients make the gap minutes to days. Windows must close on event time, so the system tracks a watermark ('I believe I have seen everything up to T') as a heuristic. Events later than the watermark get a declared policy: allowed lateness updates the window, beyond that they route to a side output or a correction job. The fatal interview answer is windowing on processing time and calling it done.

Delivery is at-least-once in any real distributed system; 'exactly-once' means exactly-once PROCESSING EFFECTS, achieved by pairing at-least-once delivery with idempotent or transactional sinks (Flink two-phase commit into Kafka, MERGE on event_id into the warehouse, dedup tables at the edge). The accurate term is effectively-once: duplicates happen, their effects do not.

groupByKey ships every value across the network and materializes whole groups per key; reduceByKey (and DataFrame aggregations) combine map-side first, shuffling only partial aggregates. One hot key with 100M values is a dead executor under groupByKey and a non-event under combining aggregation. In DataFrame terms Catalyst handles this, which is itself the answer: use the declarative API and let the optimizer do map-side combine.

Open the Spark UI before touching code: find the slow stage, then read its task-time distribution. One straggler task means skew (find the hot key, salt or isolate it). Uniform slowness means data growth, spill to disk (check shuffle spill metrics, raise partitions or memory), or a join that flipped from broadcast to sort-merge when a table crossed the autoBroadcast threshold. Narrating plan-reading (EXPLAIN, AQE decisions) instead of guessing config flags is what passes.

Measure first: per-job, per-table, per-team attribution (query tags, cluster labels), because untagged spend is unfixable spend. The usual offenders in order: small files (compaction), full scans from missing partition filters (pruning audits), oversized always-on clusters (right-size, auto-stop), hot storage holding cold data (tiering policies), and dashboards refreshing unwatched (cache or kill). Frame it as an SLO trade: cost is a feature you budget like latency, and saying 'spend X to keep freshness Y' is the staff-level sentence.

STAR with numbers an engineer would respect: latency from X to Y, dollars saved, teams unblocked, incidents prevented. At L5+ the difference is the postmortem you add without being asked: what you would do differently, stated specifically. Practice the 90-second version; rambling sinks more behavioral rounds than weak content does.

Pick a real disagreement with stakes, show you argued with data, listened, and either changed their mind or changed yours; both endings work. What is being measured is ego under pressure. 'I have never really had a conflict' reads as either dishonest or unseasoned, and both get marked down.

A pipeline you broke, data you corrupted, an estimate you blew, with the actual cost named. Then root cause, the process change that outlived you, and what you would tell someone in the same setup today. Polished faux failures ('I worked too hard') get marked down immediately; a specific blast radius plus a fix that outlived your tenure is what a convincing answer looks like.

Show the moves, not the buzzwords: the assumptions you wrote down, the cheapest experiment that de-risked the big unknown, the decision you committed to before certainty existed, and how you communicated the risk. 'We did agile' is a non-answer; what convinces is a concrete decision made under uncertainty and the reasoning you can still reconstruct.

A migration you championed past skeptics, a standard you spread, an engineer you mentored to a promotion. Name who pushed back, what their legitimate concern was, and how you brought them along rather than rolling them over. End six months later: durable outcomes are the evidence; the moment of winning the argument is not.