Data engineer interview questions by company, deduplicated and answered

Expect to give three approaches and say when each one breaks.

• DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) = N is the default. It treats ties correctly, so the “second highest” is the second distinct value.
• A correlated subquery, WHERE N - 1 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e2.salary > e1.salary AND e2.dept = e1.dept), works without window functions. It is quadratic per group, so say so.
• ORDER BY salary DESC LIMIT 1 OFFSET N-1 over SELECT DISTINCT salary is fine for the overall case and awkward per department.

The trap: ROW_NUMBER() returns a wrong answer when two people share the top salary, because it gives them ranks 1 and 2. The “without RANK or a subquery” variant (TCS, IBM) wants a self-join: MAX(e1.salary) where a strictly greater e2.salary exists.

For the values 100, 100, 90:

They agree only when the ORDER BY key is unique within the partition. Once there are ties, a DENSE_RANK() = 1 filter returns several rows per group where ROW_NUMBER() = 1 returns one.

The follow-up is determinism. ROW_NUMBER() over a non-unique sort key picks an arbitrary row among the ties, and in Spark that choice can change between runs. Add a tiebreaker column.

Find: GROUP BY key HAVING COUNT(*) > 1.

Keep the latest:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY business_key
                               ORDER BY updated_at DESC, ingest_id DESC) AS rn
  FROM src) t
WHERE rn = 1;

Delete in place depends on the engine. In SQL Server, a CTE with ROW_NUMBER() and DELETE ... WHERE rn > 1 works. In Delta or Iceberg, write the deduplicated result with MERGE or INSERT OVERWRITE rather than issuing row-by-row deletes.

The follow-up: an exact duplicate (every column equal) and a key duplicate (same key, different values) are different problems. The first is a DISTINCT. The second needs a rule for which version wins, and that rule belongs to the business, not to you.

SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

State the frame explicitly. With an ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That includes every peer row with the same txn_ts, so two transactions in the same second both show the combined total. ROWS gives a true row-by-row balance.

For a balance, debits must be negative before the sum. Say that you would check the sign convention in the source.

Two standard techniques:

• LAG/LEAD comparison. For “three rising months”, compare usage with LAG(usage, 1) and LAG(usage, 2) in the same row.
• Gaps and islands. date - ROW_NUMBER() OVER (PARTITION BY user ORDER BY date) is constant across a run of consecutive dates. Group by that difference and keep groups with COUNT(*) >= k.

Gaps and islands generalises to any run length. LAG needs one comparison per step. Deduplicate dates first: two orders on the same day break the ROW_NUMBER() arithmetic.

NOT EXISTS or a LEFT ANTI JOIN is the correct shape. NOT IN is the trap: if the subquery returns a single NULL, x NOT IN (...) is never true and the query returns zero rows.

LEFT SEMI JOIN returns left rows that have a match, with no right-hand columns and no duplication when the right side has several matches. LEFT ANTI JOIN returns left rows with no match.

At 100M rows in Spark, both are hash or sort-merge joins on the key. Prune the right side to distinct keys for February before the join, and partition by month so January and February are each a single scan. EXCEPT on the key columns also works, at the cost of a full deduplication.

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_ts DESC) = 2.

Expect the follow-up: what about a user with only one login? This query drops them. If the requirement is “one row per user, null when there is no second”, left join the result back to the distinct users.

WITH RECURSIVE chain AS (
  SELECT emp_id, manager_id, 1 AS depth FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, c.depth + 1
  FROM emp e JOIN chain c ON e.manager_id = c.emp_id)
SELECT * FROM chain;

Name the two failure modes: a cycle in the data recurses forever, so cap the depth or track visited ids, and the recursion cost grows with depth. Leaf nodes are rows whose id never appears as a parent: an anti-join on manager_id.

Spark 3.5 does not support WITH RECURSIVE. There, the usual answer is an iterative self-join loop in the driver that stops when an iteration adds no new rows.

Take the latest audit row per account with ROW_NUMBER(), left join it to accounts, and keep rows where COALESCE(latest_status, 'ACTIVE') = 'ACTIVE'.

The test is whether you notice that an inner join silently drops accounts that were never audited, which is the “count as active” clause.

DENSE_RANK() <= 3 if ties should all be returned, ROW_NUMBER() <= 3 if exactly three rows are required. Ask which. In PySpark the same logic is Window.partitionBy("region").orderBy(F.desc("sales")) with F.dense_rank().

Work from evidence, in this order:

1. Compare the plans. Get the execution plan now and, if possible, from when it was fast. A changed join strategy or a lost index or partition filter explains most regressions.
2. Check what changed. Data volume, statistics (stale statistics lead to bad join orders), a new predicate that stopped partition pruning, a function wrapped around a filter column.
3. Reduce the bytes read. Partition and cluster on filter columns, select only needed columns, push filters below joins.
4. Fix the joins. Pre-aggregate before joining, filter early, broadcast the small side, check for row explosion from many-to-many keys.

The “it used to be fast” framing is deliberate. The interviewer wants to hear that you start with the plan diff, not with adding indexes.

• Comparison: NULL = NULL is NULL, not true, so a filter drops the row. Use IS NULL, Spark’s null-safe <=>, or eqNullSafe in PySpark.
• Joins: null keys never match in an equi-join. Two rows with a null customer_id do not join to each other.
• Aggregates: COUNT(col) and AVG(col) skip nulls, while COUNT(*) counts every row. An average that “drops” rows is usually this.
• Grouping: GROUP BY puts all nulls into one group.

On a lakehouse, the index question usually becomes partitioning, clustering and file-level statistics, because Parquet tables have no B-tree indexes.

Two aggregates at different grains, joined on customer: total over the last 90 days, and average of monthly totals over a baseline period. Then filter on recent_90d > 1.5 * avg_month * 3, or compare per-month rates.

The question is testing whether you define the baseline before writing SQL. “Average monthly purchase” over which months, and including months with zero purchases? Months with no rows do not exist in a GROUP BY, so the average is overstated unless you generate a month calendar.

• Order-preserving dedupe: list(dict.fromkeys(items)), since dicts keep insertion order. A seen set with a loop is the explicit version.
• Frequency: collections.Counter(items).
• Duplicates by key: group into a defaultdict(list) keyed by ID, keep groups with more than one record.
• Consecutive ranges: sort, then start a new range whenever x != prev + 1.

State the complexity for each: all are O(n) after the O(n log n) sort in the last case.

For “first non-repeating element in a stream” (PayPal), the follow-up is memory: an ordered dict of candidates plus a set of seen-twice values.

• Nested JSON: a recursive function that joins parent keys with a separator, and turns lists into either indexed keys or exploded rows. Decide which with the interviewer, because it changes the row count.
• Large CSV: stream with csv.reader line by line, or pandas.read_csv(chunksize=...), keeping only aggregates in memory. For anything that needs a sort or join, say you would hand it to Spark or DuckDB.
• Top routes without pandas: a defaultdict(float) of route to revenue, then heapq.nlargest(3, ...).

• Decorator: a function that takes a function and returns a wrapped one; use functools.wraps to keep the name and docstring. A retry or timing decorator is a good example to give.
• Threads vs processes: CPython’s GIL lets only one thread run Python bytecode at a time, so threads help I/O-bound work and processes help CPU-bound work.
• Mutability: lists, dicts and sets are mutable; tuples, strings and frozensets are not. The classic bug is a mutable default argument shared across calls.

1. spark-submit launches the driver, which creates the SparkSession.
2. The driver asks the cluster manager (YARN, Kubernetes, standalone) for executors.
3. Code builds a logical plan; nothing runs until an action.
4. On an action, Catalyst analyses and optimises the plan and picks a physical plan.
5. The DAG scheduler cuts the plan into stages at shuffle boundaries.
6. The task scheduler sends one task per partition per stage to executors.
7. Shuffle files are written at each stage boundary and read by the next stage.
8. Results go back to the driver or are written to storage.

The follow-up is always “where does it break?”: driver memory on collect(), executors at shuffle, and the cluster manager when resources are not granted.

Work it from the shuffles. repartition(2) is one exchange and groupBy is another, so the action produces three stages: the scan stage (one task per input split), a stage of 2 tasks after the repartition, and the post-aggregation stage with spark.sql.shuffle.partitions tasks (200 by default), which AQE may coalesce to fewer.

Reading a CSV with schema inference adds its own job before any of this, because Spark has to read the file to infer the types. That extra job is the detail the question is designed to catch.

A narrow transformation (filter, select, withColumn) computes each output partition from one input partition. A wide one (groupBy, join, distinct, repartition) needs data from many, which forces a shuffle and a stage boundary.

Transformations only add to the plan; actions (collect, count, write) trigger execution. Laziness is what lets Catalyst push filters into the scan and collapse projections before anything runs.

RDDs are opaque objects that Spark cannot optimise. DataFrames carry a schema, so Catalyst can prune columns and push filters, and Tungsten stores rows in a compact binary format. A Dataset is a typed DataFrame in Scala and Java; in Python only the DataFrame exists.

SparkSession (since 2.0) is the entry point for SQL, DataFrames and the catalog. It wraps a SparkContext, which is the lower-level connection to the cluster.

Catalyst turns a query into a parsed plan, resolves it against the catalog (analysed plan), applies rule-based rewrites (optimised logical plan), then picks a physical plan with strategies and cost estimates. Whole-stage code generation then compiles parts of the physical plan to JVM bytecode.

Predicate pushdown moves filters as close to the data as possible: into the Parquet reader, where row-group min/max statistics skip data, or into a JDBC source’s WHERE clause. Show it with df.explain(): PushedFilters in the scan node.

• File scans: splits are bounded by spark.sql.files.maxPartitionBytes, 128 MB by default, adjusted by openCostInBytes so many small files are packed together. File count does not set partition count directly.
• Shuffles: spark.sql.shuffle.partitions, 200 by default, which AQE coalesces toward spark.sql.adaptive.advisoryPartitionSizeInBytes (64 MB).
• Explicit: repartition(n) and coalesce(n).

A useful target is 100 to 200 MB of input per task. Too small means scheduling overhead dominates; too large means spills and long tasks.

The trap: coalesce(1) before a write does not just produce one file. With no shuffle to separate them, it pulls the upstream stage down to one task, so the whole computation runs single-threaded. repartition(1) keeps upstream parallelism and pays for one shuffle instead.

The driver collects the small side, builds a hash relation and sends it to every executor, so the large side joins locally with no shuffle. Spark picks it automatically below spark.sql.autoBroadcastJoinThreshold (10 MB by default), or when you add a broadcast() hint.

Avoid it when the “small” side is large after decompression, because it must fit in driver memory and in every executor. A forced hint on a wrong size estimate is a common source of driver OOM.

When neither side is small: filter and project both sides first, pre-aggregate if possible, bucket both tables on the join key to avoid the shuffle on repeated joins, and let AQE switch strategy at runtime once actual sizes are known.

Partitioning splits a table into directories by column value, which lets queries skip directories. It suits low-cardinality filter columns like date.

Bucketing hashes a column into a fixed number of files per partition. Two tables bucketed the same way on the join key can sort-merge join with no shuffle. It suits high-cardinality join keys.

Broadcasting is a per-query choice for one small table. Bucketing is a layout choice paid once at write time for repeated large joins.

A bloom filter is a compact bit array that answers “definitely not present” or “possibly present”. Built from the join keys of the smaller side, it lets the larger side drop rows that cannot match before the shuffle. Spark added a runtime bloom-filter join optimisation in 3.3, controlled by spark.sql.optimizer.runtime.bloomFilter.enabled.

Parquet and Delta also use bloom filters on columns, for point lookups on high-cardinality fields that min/max statistics cannot prune.

Find where it fails before changing anything. An executor OOM during a sort-merge join is usually skew: one key’s rows do not fit in one task. A driver OOM is usually a broadcast that was too large.

Then: remove a forced broadcast hint, filter and project before the join, check for a many-to-many key that multiplies rows, handle skew (AQE or salting), and raise shuffle partitions so each task holds less. Increasing executor memory is the last step, not the first.

In the stage’s task table, compare the max with the median for duration, shuffle read size and records. A max many times the median, on a stage that follows a shuffle, is skew. Spill on that one task confirms it.

Then find the key: df.groupBy(key).count().orderBy(desc("count")) usually shows one value, often a null or a default like 0 or UNKNOWN.

AQE skew join (on by default since Spark 3.2) splits a shuffle partition that is both 5 times the median (skewedPartitionFactor) and larger than 256 MB (skewedPartitionThresholdInBytes) into smaller pieces, and replicates the matching partition from the other side. It only acts on sort-merge joins, and only above those thresholds.

Salting by hand: add a random salt 0..N-1 to the skewed side’s key, explode the other side’s matching rows N times with the same salt range, join on (key, salt), then aggregate.

Choosing N (HDFC asked this directly): roughly the hot key’s size divided by the target partition size. Too small leaves the hot partition hot; too large multiplies the other side by N for no benefit.

When one key is 60% of the data, check whether that key should be joined at all. If it is a null or placeholder, filter it out and union it back after the join. That is often the whole fix.

cache() is persist() with the default level. For a DataFrame the default is memory and disk (PySpark shows it as MEMORY_AND_DISK_DESER); for an RDD it is MEMORY_ONLY. persist(level) lets you choose.

When it does not fit: with a memory-and-disk level, evicted blocks spill to local disk; with memory only, they are dropped and recomputed from lineage when next needed. Cached storage competes with execution memory in the unified pool, so caching too much slows shuffles.

Release with unpersist(). The follow-up: caching is lazy, so nothing is stored until an action runs over the cached DataFrame.

Executor heap minus 300 MB reserved is split by spark.memory.fraction (0.6) into a unified pool shared by execution (shuffles, joins, sorts) and storage (cache). Execution can evict storage; storage cannot evict execution below spark.memory.storageFraction. Off-heap overhead (spark.executor.memoryOverhead) covers JVM internals, Python workers and network buffers.

OOM with ample cluster capacity is a per-task problem: one skewed partition, an oversized broadcast, collect() on the driver, or Python UDF workers exceeding overhead, which shows up as a container killed by YARN or Kubernetes rather than a Java heap error.

1. What changed? Input volume, code, cluster, configs, upstream schema. Most regressions are a change nobody announced.
2. Compare the Spark UI for a good and a bad run, stage by stage. Find the stage whose time grew.
3. In that stage, check input size, shuffle size, spill, task time distribution (skew), GC time and failed or retried tasks.
4. Check the storage layer: small files accumulating, a partition filter that stopped pruning, a table growing without compaction.
5. Fix the cause and verify on comparable data.

Interviewers listen for a hypothesis tested against evidence, not a list of tuning parameters.

Things that grow without bound: the number of small files in the input table, streaming state size (no watermark, so state is never dropped), the checkpoint or metadata log, Delta or Iceberg metadata without maintenance, and an ever-growing lookup table that was once small enough to broadcast.

Prevent: repartition by the table’s partition columns before writing, use optimised or auto-compacting writes, and give streaming triggers a sensible interval so each micro-batch writes fewer, larger files.

Repair: run compaction (OPTIMIZE in Delta, rewrite_data_files in Iceberg, clustering in Hudi) partition by partition. Table formats commit the rewrite atomically, so readers see old or new files and never a mix. That is how you get “no downtime”.

At 50 billion files, add that listing itself is the bottleneck. Moving to a table format replaces directory listing with a metadata scan, and compaction then runs in batches ordered by query value.

Size from partitions: 100 GB at about 128 MB per task is roughly 800 tasks. A few waves of those across executors with 4 to 5 cores each sets the cluster size. Then check shuffle volume, which is often larger than input.

For a 500 GB CSV: give an explicit schema (inference reads the data twice), make sure the file is splittable (gzip is not), and convert to Parquet once if it will be read again.

For cost: autoscaling, spot or preemptible workers for stateless stages, file compaction so fewer tasks run, and removing unnecessary caching. Measure cost per run before and after.

Exactly-once is an end-to-end property, not a setting. In Structured Streaming it needs a replayable source (Kafka offsets), checkpointed progress, and an idempotent or transactional sink. Spark’s own Kafka sink is at-least-once, so writing back to Kafka needs downstream deduplication.

In practice: write to a Delta, Iceberg or Hudi table, whose commits are tied to the batch ID, or MERGE on the event ID. On the producer side, Kafka’s idempotent producer and transactions stop duplicates entering the topic.

dropDuplicatesWithinWatermark (Spark 3.5+) or dropDuplicates on the event ID plus event time with a watermark, so state is bounded. Then make the write idempotent: MERGE on the event ID inside foreachBatch, or Delta’s txnAppId and txnVersion options so a replayed batch is skipped.

To repair existing duplicates, rewrite the affected partitions with a ROW_NUMBER() dedupe.

Event time is when it happened; processing time is when Spark saw it. Window aggregations should use event time, or a delayed batch puts events in the wrong window.

A watermark (withWatermark("event_ts", "2 hours")) tells Spark how late data may arrive. State older than the watermark is dropped, and events later than it are discarded.

For Amazon’s version (“30% of events arrive 45 minutes late”): either widen the watermark past 45 minutes and pay for the state, or keep a tight watermark for the real-time view and correct with a batch job that re-aggregates late data into the affected windows.

The checkpoint holds the offset log (what each batch will read), the commit log (which batches completed) and the state store for stateful operators. Restart reads it to resume exactly where the query stopped.

Delete it and the query starts as new: offsets come from startingOffsets, so you reprocess or skip data, and all aggregation state is lost. Duplicates in a non-idempotent sink are the usual result.

Compare input rate with processing rate in the streaming query progress. If processing is slower: check for skew across Kafka partitions, too few partitions for the consumers, slow sinks, and state growth. Spark reads at most one task per Kafka partition unless minPartitions splits them.

Fixes: more topic partitions, maxOffsetsPerTrigger to bound batch size, a cheaper sink path, and scaling executors. A lag graph that grows steadily means capacity; a sawtooth means slow batches.

Micro-batch (the Structured Streaming default) runs small batches with second-level latency and exactly-once with a suitable sink. Continuous processing targets millisecond latency, but supports only map-like operations with at-least-once guarantees, and is rarely used.

Choose streaming when the consumer acts on data within minutes. Otherwise a scheduled batch is cheaper and easier to backfill. Many teams run streaming with availableNow triggers to get streaming semantics at batch cost.

Aggregate views by (user, product) over a one-hour event-time window, keep counts of three or more, and anti-join against purchases for the same (user, product) and window. In streaming, a watermark bounds the state.

Clarify “one hour”: tumbling windows miss three views that straddle a boundary, so a sliding window or a session-style range check is the honest answer.

Every change is a new JSON commit file in _delta_log, numbered in sequence. A table version is the result of replaying commits, with Parquet checkpoints every so often to keep that fast.

Readers pick a version and read exactly its files, so they never see a partial write. Writers use optimistic concurrency: they write data files, then try to create the next commit file atomically. If another writer took that version first, Delta checks whether the two touched conflicting files, and either retries or fails with a ConcurrentModificationException subclass.

• Time travel: VERSION AS OF or TIMESTAMP AS OF reads an old version, for audits, reproducing a report or undoing a bad write with RESTORE.
• OPTIMIZE: compacts small files into larger ones.
• Z-order: OPTIMIZE ... ZORDER BY (col) co-locates values so file-level statistics skip more files on filters.
• VACUUM: deletes files no longer referenced by any version within the retention period, 7 days by default.

The trap: VACUUM below the retention period removes the files time travel needs, and can break long-running readers.

Check in this order: small files (count files per partition), filters that do not match the partitioning or clustering, stale or missing column statistics, a long transaction log without recent checkpoints, and wide rows read in full.

Fixes are compaction, clustering or Z-ordering on the common filter columns, selecting fewer columns, and moving partitioning off high-cardinality columns.

Deletion vectors mark deleted rows in a small sidecar file, so a DELETE touches only metadata, and the rewrite happens later during compaction. Without them, Delta rewrites every file containing a deleted row (copy-on-write).

Data skipping uses per-file min/max statistics in the transaction log to skip whole files. Unlike a B-tree index it does not locate individual rows; it only works when values are clustered across files, which is why it pairs with Z-ordering.

Parquet and ORC are columnar and suit analytics. Avro is row-based with an embedded schema and suits streaming and record exchange. The format choice is about the workload, not a ranking. The open table formats comparison goes further.

• COPY INTO is an idempotent SQL load that skips files it already loaded. It suits thousands of files arriving in batches.
• Auto Loader (cloudFiles) is a streaming source that discovers new files incrementally, with schema inference and evolution. It suits continuous arrival and millions of files.
• Medallion: Bronze keeps raw data for replay, Silver holds cleaned and deduplicated data, Gold holds business aggregates. The point is recoverability: a Silver bug is fixed by reprocessing from Bronze, not by re-pulling the source.
• Unity Catalog: one governance layer for tables, files and models across workspaces, with grants, lineage and audit.

Type 1 overwrites, Type 2 keeps history with a new row per change, Type 3 adds a “previous value” column.

Type 2 in one MERGE needs the staged-union trick: the change rows appear twice, once with the real key (to match and close the current row) and once with a null merge key (so they fall through to the insert branch):

MERGE INTO dim_customer t
USING (
  SELECT s.customer_id AS merge_key, s.* FROM staged s
  UNION ALL
  SELECT NULL AS merge_key, s.* FROM staged s
  JOIN dim_customer d ON s.customer_id = d.customer_id
  WHERE d.is_current AND s.address <> d.address) u
ON t.customer_id = u.merge_key AND t.is_current
WHEN MATCHED AND t.address <> u.address THEN
  UPDATE SET is_current = false, end_date = u.effective_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, address, effective_date, end_date, is_current)
  VALUES (u.customer_id, u.address, u.effective_date, NULL, true);

Follow-ups: compare a hash of tracked columns rather than each column, handle nulls in the comparison (use <=>), and decide what happens to late changes that arrive out of order.

A star schema has denormalised dimensions joined directly to the fact, which means fewer joins and simple BI queries. A snowflake normalises dimensions into sub-dimensions, which saves storage and keeps hierarchies consistent but costs joins. Lakehouse BI generally favours star.

Many-to-many (an account with several holders) goes through a bridge table between fact and dimension, with a weighting factor if measures must not be double-counted. The data modeling guide covers grain and fact types.

A lake stores raw files of any shape cheaply. A warehouse stores modelled, governed tables for SQL. A mart is a subject-specific slice of the warehouse. A lakehouse puts table formats on lake storage to get warehouse guarantees (ACID, schema, time travel) without copying data into a separate system.

Choose an RDBMS for transactional consistency and point lookups, NoSQL (DynamoDB, Cassandra) for key-value access at high write rates, and a lake or warehouse for scans and aggregation.

Start with the grain: one row per order line, or per ride. The fact holds measures (quantity, revenue, duration), and dimensions are product, vendor, store, date and customer. Name additive versus non-additive measures. Ratios like average ride duration are computed, not stored.

Then answer the follow-ups the interviewer will ask: which dimensions change (SCD), how to partition the fact (by date), and what changes at a billion rows (clustering on the common filter keys, pre-aggregated rollups).

Do not scan 180 full snapshots. Build an incremental table of first-seen and last-seen dates per phone number, updated daily from the latest snapshot only. A 90-day count is then a filter on last_seen >= current_date - 90.

If approximate is acceptable, keep a daily HyperLogLog sketch per day and merge 90 of them. Either way, the insight is to change the storage design so the query becomes cheap.

Running a step twice must give the same result as running it once:

• Overwrite by partition, not append: INSERT OVERWRITE the day you are loading, or Delta’s replaceWhere.
• MERGE on a business key for upserts.
• Commit atomically. Write to a staging location and swap, or rely on the table format’s commit, so a half-finished run leaves nothing visible.
• Track what was processed: a watermark or batch ID table updated in the same transaction as the data.

For “the same financial transaction arrives twice” (Amazon), the key is a natural or producer-assigned transaction ID with a uniqueness check at write.

With a reliable updated_at, store the high-water mark and pull rows above it, with a small overlap to catch clock skew. With log-based CDC (Debezium, ADF CDC, database change feeds), read the change stream with operation types and apply it with MERGE.

With no key and no timestamp (EPAM), compute a hash of each full row, compare the current extract with the previous one, and treat new hashes as inserts and missing hashes as deletes. It costs a full extract each time, so say that the real fix is asking the source owner for a change column.

Store data by event date, not load date. Each run re-aggregates every event date touched by newly arrived records, not just “yesterday”, and overwrites those Gold partitions.

Keep a lookback window (for example, recompute the last 7 days every run) when late arrival is routine, and a targeted backfill for rare very late data. For dimensions that arrive after their facts, insert a placeholder dimension row and update it when the real one lands.

Separate tolerant ingestion from strict consumption. Bronze accepts new columns (schema evolution on write, or keep the raw payload in a string or VARIANT column). Silver and Gold have explicit contracts.

Additive changes (a new nullable column) can flow through automatically. Breaking changes (rename, type narrowing, dropped column) should fail loudly at a validation step and alert, rather than silently produce nulls downstream. Mention a schema registry for streaming sources and data contracts with producers.

Reconcile stage by stage: counts and key sums at extract, Bronze, Silver and Gold. The stage where counts diverge locates the problem. Then check that stage’s filters, join types (an inner join to a dimension with missing keys is the classic loss), deduplication rules, rejected-record outputs and watermark logic.

Identify the missing keys with an anti-join of source against target, and group them by an attribute to find the pattern. For ongoing checks: row-count, null-rate, uniqueness and freshness assertions that fail the pipeline, not just a dashboard.

Treat it as a correctness incident. Pin down which metric, since when, and by how much. Diff the output of the previous code version and the new one on the same input. Most such bugs are join fan-out (duplicates multiplying a sum), a filter change, timezone handling at day boundaries, or a changed upstream definition.

Then prevent recurrence with a regression test that compares key metrics on a fixed sample before deploy.

Capture lineage automatically rather than documenting it: OpenLineage events from Spark and Airflow, or a catalog such as Unity Catalog that records table- and column-level lineage. Add run metadata (job ID, code version, input snapshot IDs) as columns or in an audit table, so any output row can be traced to the run and inputs that produced it.

1. Bulk-copy history from a consistent snapshot, partitioned by date, and validate each partition with counts and checksums.
2. Start CDC from the snapshot point so the target stays current.
3. Run both systems in parallel and reconcile key reports daily.
4. Cut consumers over in waves, starting with read-only analytics, and keep the old system as a rollback until sign-off.

Mention transfer limits (500 TB over the network may need a physical transfer appliance), type mapping differences, and who signs off on reconciliation.

Shape the answer around the two consumers. Producers write to Kafka with an idempotent producer, keyed by account so per-account ordering holds. A streaming job scores transactions against features held in a low-latency store, and emits alerts. A separate path lands every event in a lakehouse table for reporting and model training, with MERGE on the transaction ID.

Cover: partitioning by account, out-of-order events, exactly-once writes, replay from Kafka retention, PII handling, and reconciliation with the core banking ledger.

Use a single event log (Kafka or Kinesis) with two consumers. A streaming job maintains current status per order in a key-value store for customer lookups. A second streaming or micro-batch job appends events to a lakehouse table partitioned by event hour, with hourly aggregates built on top.

At about 6,000 orders per second on average, size for peak, not average. Discuss late events, idempotent upserts into the status store, and replaying from the log to rebuild either side.

Make the pipeline metadata-driven: one engine, with per-client configuration for source, schema mapping, rules and schedule stored in tables, not code. Isolate tenants by catalog or schema with separate permissions, and tag compute for cost attribution.

The trade-off to name: a shared framework cuts duplicated code but makes one client’s special case everyone’s risk. Version the framework, and allow plug-in transforms for exceptions.

The problem is repeated computation, not storage. Find the common transformation those 50 jobs perform, materialise it once as a curated table (or materialised view) partitioned and clustered for their access patterns, and point the jobs at it. Then tier cold history to cheaper storage classes.

Back it with numbers: bytes scanned per day before and after.

Production-ready means: correct output validated against a source, idempotent reruns, alerts on failure and on lateness, data-quality checks, a documented backfill procedure, least-privilege access, and known cost per run.

For the 2 AM failure: read the error and the failing step, check whether upstream data arrived, check recent changes, rerun safely if the cause was transient, and communicate the impact on downstream SLAs. Write up the root cause afterwards.

A linked service is connection information (where and how to connect). An integration runtime is the compute that runs the activity. The Azure IR is managed and reaches public or managed-private endpoints. A self-hosted IR is installed on your own machines to reach on-premise or private-network sources. An Azure-SSIS IR runs SSIS packages.

Copy Activity moves data and is billed by data integration units and time. Mapping Data Flows run transformations on a managed Spark cluster, with cluster start-up time and per-core billing, so they cost more for simple copies.

Queued usually means it is waiting for capacity: pipeline or activity concurrency limits reached, a self-hosted IR node offline or saturated, or waiting on a dependency.

Monday-only failures point at what is different on Mondays: a larger weekend backlog, a source system maintenance window, a credential or certificate rotated over the weekend, or another job scheduled at the same time competing for the same IR. Compare the Monday run’s inputs and timing with a good Tuesday run.

Keep secrets in Key Vault and reference them from linked services and Databricks secret scopes, accessed with managed identities so no credential is in code. Dynamic data masking hides values at query time for unprivileged users. Row-level security uses a predicate function bound to a security policy to filter rows per user or client. Define all of it with Bicep or Terraform so environments are reproducible and reviewed.

Databricks suits Spark engineering, streaming and ML on Delta. Synapse dedicated SQL pools suit a warehouse-first team with T-SQL skills. Many architectures use Databricks to transform and a SQL serving layer for BI.

Failure points by stage: file arrival and partial files (Blob), IR capacity and credentials (ADF), cluster start-up, skew and schema changes (Databricks), load concurrency and statistics (Synapse), and refresh timeouts (Power BI). Each needs its own alert.

Partitioning (by date, ingestion time or integer range) prunes whole partitions and makes cost predictable. Clustering sorts data within partitions by up to four columns, so filters on them read fewer blocks. Cost is driven by bytes scanned: select only needed columns, require partition filters, and use materialised views for repeated aggregates. MERGE in BigQuery follows standard SQL with WHEN MATCHED and WHEN NOT MATCHED clauses.

Operators are the task types: they wrap an action (run a query, submit a Dataproc job, call an API) so a DAG is configuration, not custom code. For missing upstream data, use a sensor (in deferrable mode, so it does not hold a worker slot) with a timeout, alert when it times out, and keep downstream tasks from running on partial data. Rerunning must be idempotent.

S3 offers Standard, Intelligent-Tiering, Standard-IA, One Zone-IA and the Glacier classes (Instant Retrieval, Flexible Retrieval, Deep Archive). Lifecycle rules move objects by age automatically. Watch the minimum storage durations and retrieval charges on the infrequent and archive tiers.

DynamoDB is a managed key-value store for predictable single-digit-millisecond access at any scale. RDS is a managed relational database for joins and transactions.