The Data Engineer Interview Questions That Reveal Whether You’ve Run a Pipeline in Production

A star schema puts one fact table at the center with denormalized dimension tables hanging directly off it. Queries stay simple because there are fewer joins, at the cost of some duplicated dimension data. A snowflake schema normalizes those dimensions further into sub-tables, trading storage for extra joins.

My default is star schema for most analytics work. On a warehouse that charges per query or per second of compute, every extra join in a snowflake design has a real dollar cost, and the storage saved rarely offsets it. Snowflake schema earns its keep when a dimension has a genuinely large, reusable hierarchy, geography is the textbook case, where the normalized version avoids storing the same country and region strings millions of times over.

The simple version groups and counts. The version you actually want for debugging surfaces the individual rows so you can compare them side by side.

-- quick check: which emails appear more than once
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- debugging version: the actual duplicate rows, ranked
SELECT *
FROM (
 SELECT c.*,
     ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY created_at DESC
     ) AS rn
 FROM customers c
) ranked
WHERE rn > 1;

If the interviewer asks you to delete duplicates and keep only the newest row, you already have the answer sitting in that ranked query: keep rn = 1, delete everything with rn greater than 1. Say that out loud before they have to ask.

OLTP systems (Postgres, MySQL) handle many small, fast reads and writes. They stay normalized to protect data integrity and store rows together, since a typical query fetches one row by primary key. OLAP systems (Snowflake, BigQuery, Redshift) run a smaller number of large analytical scans. They denormalize to cut down on joins and store data by column, since a typical query aggregates a handful of columns across millions of rows and never touches the rest.

Design an OLAP warehouse the way you'd design an OLTP system and you'll spend your first quarter on the job explaining to stakeholders why every dashboard takes eleven seconds to load.

DELETE removes rows one at a time, fully logged, can take a WHERE clause, and can be rolled back inside a transaction. TRUNCATE removes every row with minimal logging, ignores WHERE entirely, and resets identity sequences, making it far faster on a large table. DROP removes the table structure itself, and without a backup there's no getting it back.

In a pipeline, TRUNCATE-and-reload is a common pattern for making a load idempotent. Just be aware that if the pipeline dies partway through, the table sits empty until the reload finishes, which can quietly break a downstream query that ran during that window.

A natural key is a real-world attribute that's already unique, an email address, a national ID, a SKU. A surrogate key is a system-generated identifier, usually an auto-incrementing integer or a UUID, with no business meaning attached.

I default to surrogate keys in a warehouse. Natural keys change more often than teams expect (a customer changes email addresses, a SKU gets renumbered after a product line refresh), and every foreign key referencing that natural value has to change with it. The exception is genuinely immutable identifiers, a country's ISO code is about as safe a natural key as exists, since nobody is renaming France.

COUNT(*) counts every row in the group, full stop. COUNT(column_name) counts only the rows where that specific column is not NULL. Run both on a table where a column has some missing values and the second number will be lower.

It's a small distinction that trips people up specifically when computing something like a completion rate: COUNT(email_verified_at) over COUNT(*) is a legitimate way to compute "percent of users who verified," but only if the person writing it actually means for NULL to represent "not yet verified" rather than "unknown."

ETL transforms data before it lands, so the destination only ever holds clean, structured data. That made sense when destination compute was expensive or limited. ELT loads raw data first and transforms it inside the warehouse, which modern columnar warehouses have more than enough compute to absorb cheaply.

The real reason to prefer ELT is debuggability, not cost. If a transformation has a bug, ELT still has the raw data sitting there to diff against. ETL that transformed on the way in may have already thrown away the original shape, leaving you debugging against a version of the data that's already been mangled once.

Lineage tracks where a piece of data came from, what transformed it, and everything downstream that depends on it. Monitoring tells you a job failed. Lineage tells you what else breaks because it did.

The practical case is impact analysis: a source table changes its schema, and you need to know what breaks without manually tracing through eighty dbt models by hand. The compliance case matters just as much, a GDPR deletion request means proving every table a user's data touched, and without lineage that's a manual grep through years of SQL.

dbt handles the T in ELT. You write SQL SELECT statements, dbt compiles them into CREATE TABLE or CREATE VIEW statements, resolves dependencies between models, runs tests, and generates a lineage-aware documentation site. It sits after ingestion tools (Fivetran, Airbyte, a custom loader) and before BI tools consume the finished marts.

What it doesn't do is scheduling. dbt has no concept of "run this at 3am" or "wait for the ingestion job to finish first." That's Airflow, Dagster, or Prefect's job. Saying "dbt orchestrates our pipeline" in an interview is a fast way to signal you haven't actually operated the stack you're describing.

Validate schema at ingestion, rejecting records missing required fields before they ever reach the warehouse. Add row-count and distribution checks after each transformation layer. Add business-logic assertions where the domain has real invariants, revenue can't be negative, an order can't ship before it was placed.

The check most teams skip is freshness. A table that silently stopped updating six hours ago is a data quality failure even though every row already in it still looks correct. A simple alert on max(updated_at) exceeding an expected staleness window catches an entire category of "everything looks fine but the data is stale" incidents that row-level checks never will.

The driver runs the main program, builds the execution plan, and schedules tasks. There's exactly one per application. Executors are JVM processes on worker nodes that actually run tasks and cache data, with each task occupying one executor thread. The cluster manager, YARN, Kubernetes, or Standalone, allocates resources across applications competing for the cluster.

The follow-up you'll almost always get: what happens if the driver dies? The whole application fails, no partial recovery. That's exactly why long-running Spark streaming jobs checkpoint their state to durable storage (HDFS or S3), so a restart resumes from the checkpoint instead of replaying from the very beginning.

Parquet is columnar, compressed, with schema embedded in the file, and it's the safe default for analytics, readable everywhere from Spark to Athena to BigQuery. ORC is also columnar, with somewhat better ACID support historically for Hive-based stacks, but Parquet's broader ecosystem wins outside a pure Hive shop. Avro is row-based with schema stored alongside the data, and it earns its keep specifically for schema evolution, which is why it's the standard format for Kafka messages, where you're consuming one event record at a time rather than scanning a column across millions of rows.

repartition() can increase or decrease the number of partitions and always triggers a full shuffle, since it redistributes data as evenly as possible across the new partition count. coalesce() can only decrease partitions, and it does so by merging existing partitions on the same node where possible, avoiding a full shuffle in the common case.

Use coalesce() when writing output and you just want fewer files (say, before a final write to cut down on tiny output files), and repartition() when you need genuinely even distribution, before a wide operation on skewed data, for instance. Calling repartition() when coalesce() would have done the job is a quiet, avoidable shuffle that shows up as unexplained job time.

YARN is Hadoop's native resource manager and has been the traditional home for Spark clusters for years, with mature support and a long track record. Kubernetes runs Spark executors as pods, which fits naturally for teams that already run everything else, services, other batch jobs, ML training, on Kubernetes, avoiding a second cluster manager and a second set of ops tooling to maintain.

The trade-off is that Kubernetes support for Spark's dynamic allocation and shuffle service has historically been less mature than YARN's, though that gap has closed a lot in recent Spark releases. The honest interview answer is usually "it depends on what else the company already runs," not a blanket claim that one is strictly better.

Snowflake and BigQuery both bill primarily on compute consumed, credits per second or bytes scanned, not on data stored. A query missing a partition filter that scans an entire multi-year table instead of the one day it needed can burn through in minutes what a well-written equivalent would have cost in seconds, and it does this silently unless someone's watching the bill.

Catch it before it runs with a dry-run cost estimate (BigQuery gives you bytes-scanned before execution) and a query review step in CI for anything hitting production-sized tables. Catch it after the fact with per-query cost attribution tagged by team, so the person who wrote the expensive query is the one who sees the bill, not finance three weeks later asking why compute spend tripled.

A regular view is just a stored query, it re-executes against the underlying tables every time it's read, with zero storage cost beyond the query text itself. A materialized view actually stores the computed result and refreshes on a schedule or incrementally, trading storage and refresh cost for read speed.

Worth it when the underlying query is expensive and read far more often than the source data changes, an hourly rollup queried by a dashboard hit hundreds of times a day, for instance. Not worth it for a query that's already cheap, or for data that changes so frequently the materialized version is stale by the time anyone reads it.

A topic is a named stream of records, split into partitions for parallelism. Brokers are the servers that store and serve partitions. A consumer group is a set of consumers that split a topic's partitions between them, so each partition is read by exactly one consumer within that group at a time, letting multiple consumer instances scale horizontally without duplicating work.

The number of partitions caps your consumer group's real parallelism. Five partitions and eight consumer instances in the same group means three of those instances sit idle, doing nothing, since there's no partition left to assign them.

Ask what "real-time" actually means to them before reaching for Flink or Kafka Streams. Nine times out of ten, it means "not stale by a full day," and a scheduled job running every five to ten minutes into the warehouse satisfies that completely, at a fraction of the operational complexity and on-call burden of a genuine streaming pipeline.

Push back specifically when the stated need doesn't actually require sub-minute latency, and reserve the streaming build for cases where it demonstrably does, fraud scoring at transaction time, a live leaderboard, anything where a five-minute-old number is provably wrong for the use case, not just less impressive-sounding in a roadmap slide.

Type 1 overwrites the old value with no history kept. Fine for correcting a typo in a customer's name, where the old value never mattered. Type 2 inserts a new row on every change, with effective-date columns and an is_current flag, preserving full history. Most teams need Type 2 for anything customer-facing or auditable. Type 3 adds a single previous_value column, capturing exactly one step back.

I've almost never seen Type 3 used outside a textbook. In practice you either want no history (Type 1) or the whole history (Type 2), and Type 3's one-step compromise satisfies neither case well. The real follow-up interviewers ask is what you do when the upstream source has no reliable updated_at column to detect a change in the first place, which is usually where change data capture enters the conversation.

ROW_NUMBER is always unique and sequential, no ties. Use it for deduplication, where you want exactly one row to land on rank 1. RANK gives tied rows the same number but skips the next rank (two rows tied at 2 means the next row is 4). DENSE_RANK ties the same way but doesn't skip (the next row after a tie at 2 is 3).

The question underneath the question is what "rank" is supposed to mean to whoever consumes the output. Dashboards showing top-N categories usually want DENSE_RANK so a tie doesn't accidentally bump a fourth category out of a top-3 view. Engineering tasks like deduplication almost always want ROW_NUMBER.

SELECT
 sale_date,
 revenue,
 SUM(revenue) OVER (ORDER BY sale_date) AS running_total,
 AVG(revenue) OVER (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 ) AS moving_avg_7day
FROM daily_sales
ORDER BY sale_date;

The gotcha: ROWS BETWEEN 6 PRECEDING counts physical rows, not calendar days. If a store was closed on a weekend and there's a gap in the dates, "7 preceding rows" quietly becomes more than 7 calendar days. If the business actually needs a calendar-based window with gaps handled correctly, you need a date spine table joined in first so every day is represented, even the ones with zero sales.

A data contract is an explicit, versioned agreement between the team producing a dataset and the teams consuming it: field names, types, nullability, and what a breaking change actually means. It's enforced with a schema registry or a validation step that rejects a producer's deploy if it silently drops a column or changes a type downstream consumers depend on.

Without one, the first sign of a breaking upstream change is usually a downstream dashboard quietly going wrong for two days before anyone notices. With a contract, the producing team's CI catches the break before it ships. This has become a more common interview topic as more companies treat internal data the way they've long treated external APIs, as something you can't change without warning the people relying on it.

Running the same pipeline twice, same inputs, same time window, produces the same output both times. No duplicate rows, no missing rows, no matter how many times it reruns.

-- naive INSERT: running this twice creates duplicate rows
INSERT INTO fact_orders (order_id, revenue, order_date)
SELECT order_id, revenue, order_date FROM staging_orders;

-- idempotent MERGE: rerunning is safe, matched rows update instead of duplicate
MERGE INTO fact_orders t
USING staging_orders s
ON t.order_id = s.order_id
WHEN MATCHED THEN
 UPDATE SET revenue = s.revenue, order_date = s.order_date
WHEN NOT MATCHED THEN
 INSERT (order_id, revenue, order_date)
 VALUES (s.order_id, s.revenue, s.order_date);

A mistake I've watched more than one candidate make in a mock session: reaching for INSERT... ON CONFLICT DO NOTHING and calling that idempotent. It prevents duplicates, but it also silently drops legitimate updates to an already-loaded row. MERGE, or an explicit UPSERT that updates on conflict, is the safer default.

Change data capture pulls inserts, updates, and deletes out of a source system for incremental downstream processing. Log-based CDC (Debezium is the common tool) reads the database's transaction log directly, catching every change including deletes, with no added query load on the source. It's the most reliable option but needs database-level access and enough log retention to cover your pipeline's lag.

Timestamp-based CDC polls for rows where updated_at is newer than the last run. Simple to stand up, but it misses hard deletes entirely and misses any row where updated_at wasn't set correctly on write. Trigger-based CDC writes changes to a shadow table via database triggers, adding write overhead to the source on every single transaction, which is rarely worth it on a high-write table. My default is log-based whenever I can get transaction log access, timestamp-based only as a fallback.

Route the bad record to a dead letter queue, a separate table or topic rather than a log line, and let the rest of the batch keep processing.

• Capture the raw record and the error message together with a timestamp, never the error alone
• Alert on a failure rate threshold, 0.1% failing silently for three weeks is a real data quality incident, not noise
• Build a reprocessing path so the dead letter queue can be replayed once the upstream cause is fixed
• Back off exponentially on transient failures, a timeout or rate limit, before deciding a record belongs in the dead letter queue at all

If you describe this without mentioning the alerting threshold, expect the follow-up: "how would you know if 20% of records started silently failing?" Bring the answer before they ask.

Airflow is the incumbent, task-centric: you define a DAG of tasks and dependencies, and it has the largest ecosystem of integrations by a wide margin. Its weak spot is data awareness, a task either ran or didn't, but Airflow itself has historically had little native concept of the data an upstream task actually produced.

Dagster is asset-centric: you define the data assets a pipeline produces, and the DAG is derived from what depends on what, which makes lineage and backfilling a named asset far more natural. Prefect leans into dynamic, code-first pipelines that don't require pre-declaring the full DAG shape up front, which fits workflows where the exact task list depends on runtime data. None of the three is strictly better. The honest answer in an interview is naming the actual trade-off for the team's specific pain point, not declaring a winner.

Batch processes a bounded dataset on a schedule. Higher throughput per unit of engineering effort, simpler to debug, simpler to rerun. Good for daily reporting, ML training sets, anything where data that's fifteen minutes old is genuinely fine. Streaming processes continuously with low latency, at the cost of real complexity, exactly-once semantics are genuinely hard, and reprocessing historical data through a streaming pipeline is painful compared to just rerunning a batch job.

My honest take: a fair number of teams running full streaming pipelines would have been fine with micro-batch, five to ten minute intervals, at a fraction of the operational cost. Real sub-second use cases exist, fraud detection and live leaderboards are the honest examples, but "the business wants real-time" often just means "less than ten minutes old," which a scheduled job handles without anyone needing to learn Flink.

Transformations (map, filter, join, groupBy) are lazy. They build a logical plan without executing anything. Actions (collect, count, write, show) trigger real computation. Spark's Catalyst optimizer can only optimize across a chain of transformations before an action fires, so triggering unnecessary actions mid-chain forfeits that optimization entirely.

The common mistake: calling df.count() inside a loop for logging. Each call is a full job on its own. Cache the DataFrame once, count once outside the loop, or read Spark's built-in metrics instead of instrumenting the code yourself.

Narrow transformations (map, filter, union) map each input partition to exactly one output partition, no data crosses the network. Wide transformations (groupBy, join, distinct, sort) require a shuffle, data gets serialized, sent across the network, and deserialized on the other side. This is where most of a slow Spark job's time actually goes.

The practical takeaway is minimizing shuffles, not eliminating them. Filtering data before a join, pushing the filter down instead of joining first and filtering after, reduces the volume that has to move across the network in the first place.

Partitioning by a column, usually a date, creates one directory per value, so a query filtering on that column can skip irrelevant directories entirely (partition pruning). It's the default choice for time-series data. Bucketing hashes rows into a fixed number of files by a column's value, so two tables bucketed on the same key with the same bucket count can join without a shuffle, since matching rows already live on the same node.

They're not mutually exclusive. A common real-world pattern is partitioning by date for freshness queries, then bucketing by user_id inside each partition for fast user-level joins.

These are table formats layered over Parquet, not replacements for it. They add ACID transactions on object storage, time travel to query a table as of a prior version, and schema evolution without rewriting the whole dataset. Plain Parquet files in an object store have none of that, no transactional guarantee that a partial write won't leave readers seeing half-updated data.

Delta Lake is Databricks-native and deeply integrated with Spark specifically. Iceberg is cloud-and-engine-agnostic and has been gaining ground fastest outside the Databricks ecosystem, on AWS and GCP in particular. Hudi leans toward incremental, upsert-heavy workloads. If a candidate can only name one and doesn't know why a team might pick a different one, that's usually a sign of stack-specific memorization rather than understanding what the table format layer is actually solving.

Caching helps when the same DataFrame gets reused across multiple actions, computing it once and reading from memory (or disk, if memory is full) on every subsequent reference instead of recomputing the whole lineage each time.

It hurts when you cache something used exactly once, wasting memory that could have gone to shuffle buffers or another job's executors, or when you cache a huge DataFrame that doesn't actually fit in memory, forcing spills to disk that end up slower than just recomputing would have been. A candidate who reaches for.cache() reflexively on every DataFrame, without checking whether it's reused more than once, usually gets pushed on this specifically.

A data lake (S3, GCS, ADLS) is cheap object storage that accepts any format, any schema, with no governance layer of its own, query performance varies wildly and there's no built-in transactional guarantee. A data warehouse (Snowflake, BigQuery, Redshift) is structured, curated, fast, and governed, at the cost of pricier compute and a schema-on-write model where transformation happens before data lands.

A lakehouse (Delta Lake, Iceberg, or Hudi on top of cheap object storage) takes the lake's storage cost and adds warehouse-like features, ACID transactions, time travel, schema enforcement, on top. For a company starting fresh with both ML workloads (which want raw, flexible access) and BI workloads (which want structured, governed queries), a lakehouse pattern is increasingly the default. A pure analytics team with no ML ambitions is usually still better served by a straightforward warehouse, since it's simpler to operate day to day.

A clustering key tells the warehouse how to physically co-locate rows with similar values into the same micro-partitions, so a query filtering on that column can prune whole micro-partitions instead of scanning them. It's conceptually closer to a table's physical sort order than to a traditional B-tree index, and there's no separate index structure maintained alongside the table.

The practical difference from an OLTP index: clustering helps range-style filters and time-based queries a lot (filtering by order_date on a table clustered by order_date), but it doesn't help point lookups by an arbitrary column the way a dedicated index would, because that's simply not the access pattern warehouses are built to optimize for.

Within a single partition, messages are strictly ordered by the order they were written. Across partitions, there's no ordering guarantee at all, message A written to partition 0 and message B written to partition 1 could be consumed in either order relative to each other.

This surprises candidates who assume "Kafka is ordered" means globally ordered. It doesn't, and it can't while still scaling horizontally across partitions. The fix, when order matters for a given entity (all events for one user, one order), is choosing a partition key that always routes that entity's events to the same partition, typically the entity's ID. Everything for that one order lands in one partition and stays ordered relative to itself, while different orders can still process in parallel across other partitions.

Each partition has one leader broker and some number of follower replicas that copy the leader's data. Producers and consumers talk only to the leader. The in-sync replica set (ISR) is the subset of followers caught up closely enough with the leader to be safely promoted.

If the leader dies, Kafka's controller promotes one of the in-sync replicas to be the new leader, and clients transparently reconnect to it. Data loss is possible only if a message was acknowledged before enough replicas had actually copied it, which is exactly what the producer's acks setting controls, acks=all waits for the full ISR to confirm before considering a write successful, trading a little latency for a much stronger durability guarantee than acks=1.

Standard retention deletes messages after a configured time or once the log hits a size limit, regardless of whether anyone still needs the older messages, appropriate for event streams where only recent activity matters. Log compaction instead keeps only the latest message for each key, discarding older messages with the same key while the topic still grows unbounded in the number of distinct keys.

Compaction fits a "latest state per entity" use case, a topic representing the current value of every user's profile, for instance, where you want a full, current snapshot of every key retrievable at any time without unbounded storage growth from every historical update to the same key. It's a poor fit for an audit-style event log where every individual event matters, since compaction throws away everything except the latest one per key.

Data Vault splits a model into three pieces: hubs hold the raw business keys (customer_id, order_id), links record relationships between those keys, and satellites hold the descriptive attributes and history, each tagged with a load timestamp and a record source. Nothing is ever updated in place, you only insert new satellite rows, so the whole model is append-only and every change is traceable back to the system that produced it.

You reach for it when you're integrating five or six source systems that each have their own idea of what a "customer" is and those source systems change their schemas often. Because hubs and links are dead simple and satellites are isolated per source, adding a new source system or absorbing a schema change rarely touches existing tables, whereas the same change in a star schema often means altering a wide dimension table that twenty reports depend on. It's also the right call when auditors need to see exactly what the data looked like on a given date, since nothing is overwritten.

The tradeoff is real: Data Vault is more tables, more joins, and it's unreadable to an analyst writing ad hoc SQL. Most teams that adopt it still build a Kimball-style dimensional mart on top of the vault for BI consumption, so you're effectively paying for two models. If you have one or two clean source systems and no regulatory pressure to preserve full history at the raw layer, a star schema gets you to a usable warehouse faster with less to maintain.

It usually comes from writing data too eagerly and too often: a streaming job flushing a micro-batch every few seconds, a job over-partitioning the output (partitioning by date and customer_id when you only have a few hundred customers a day), or Spark writing one file per task when the shuffle produced way more tasks than the data actually needed. You end up with a partition holding thousands of files that are each a few KB, when a handful of files at 128MB-256MB would have held the same data.

The immediate cost is metadata pressure and per-file overhead. Every file means a separate open, a separate footer read for Parquet, a separate task scheduled, so a query that should take seconds spends most of its time on file listing and task startup instead of actually scanning data. On object storage like S3 this also shows up as throttling, since listing and opening thousands of small objects hits request-rate limits.

Fixes split into "stop producing them" and "clean up what already exists." For new writes, coalesce or repartition the DataFrame to a sane number of partitions right before the write, or set a target file size (maxRecordsPerFile in Spark, or targetFileSize in Delta/Iceberg) so the writer bin-packs output itself. For data that's already fragmented, run a compaction job: Delta Lake's OPTIMIZE, Iceberg's rewrite_data_files, or a manual read-then-rewrite-with-coalesce pass. Some teams schedule compaction nightly on hot partitions rather than trying to prevent every small file up front, since a slightly delayed compaction is cheaper than tuning every single streaming writer.

A watermark is the engine's answer to the question "how late am I willing to let a record be before I stop waiting for it?" Streaming systems group records into windows by event time, the timestamp on the record itself, not the time the engine happened to process it. But records don't arrive in order: a mobile client can buffer events offline and send them an hour later. Without a rule for when to stop waiting, a window could theoretically never close, since a record for it might still show up.

The watermark tracks the maximum event time seen so far minus some slack, say 10 minutes, and tells the engine it can safely close and emit any window whose end time is before that watermark. Set the slack too short and you drop legitimately late data or emit incomplete aggregates that later get "corrected" in a way downstream consumers may not expect. Set it too long and your results are correct but your latency goes up, since every window sits open waiting for stragglers.

In Spark Structured Streaming this looks like withWatermark("event_time", "10 minutes") before a windowed aggregation. The practical gotcha people hit is assuming a watermark makes late data disappear cleanly, when what actually happens is the record gets silently dropped once its window has already closed, with no error and no alert unless you're explicitly tracking a dropped-records metric.

Start with EXPLAIN ANALYZE, not a guess. You're looking for a sequential scan where an index scan should be happening, and for sort or hash operations that suddenly got expensive as the row count grew.

1. Check for a missing index on the join or filter column that was fast enough to skip on a small table
2. Look for a function wrapped around the indexed column in the WHERE clause, which blocks a standard btree index unless a matching expression index exists
3. Check for an implicit type mismatch between the column and the literal you're comparing it to
4. Confirm table statistics are current, a bulk load without a follow-up ANALYZE leaves the planner working from stale row estimates
5. Only after all of that, consider whether the query is fundamentally joining across more rows than the schema should allow

Interviewers listen for whether "just add an index" is your first move or your fourth. Indexes aren't free. Every one of them slows writes and costs disk, and adding one blind, without confirming the plan first, is how a table quietly ends up with nine indexes nobody remembers the reason for.

Recursive CTEs come up more than most prep guides mention, org charts, bill-of-materials trees, category hierarchies. The pattern is always the same: an anchor member that seeds the top of the tree, then a recursive member that joins back to the CTE itself.

WITH RECURSIVE org_chart AS (
 -- anchor: the top of the tree
 SELECT employee_id, manager_id, name, 1 AS depth
 FROM employees
 WHERE manager_id IS NULL

 UNION ALL

 -- recursive: join back to the CTE, one level deeper each pass
 SELECT e.employee_id, e.manager_id, e.name, oc.depth + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY depth, name;

The trap: without a depth cap or a cycle guard, a bad manager_id pointing back into the tree (a data quality bug, not a hypothetical) sends this into an infinite loop. Postgres will eventually hit its default recursion limit and error out, but on a large enough hierarchy that's a slow, expensive way to find a bad row. Add a WHERE depth < N safeguard for anything running against production data you don't fully trust.

Almost always a fan-out: the order table joined to a child table (order_items, order_notes) with more than one matching row per order, silently multiplying every aggregate downstream. Revenue sums come out 3x too high and nothing in the query itself throws an error.

Fastest way to confirm it: run a COUNT(*) grouped by the presumed one-row-per-order key, before adding the aggregate, and check whether any group has more than one row. Fixes depend on intent, pre-aggregate the child table before joining if you need one value per order, or use a window function partitioned by order_id if you need to keep the child-level detail without the sum being duplicated.

First, scope the blast radius precisely, the exact date range and exact tables the bug actually touched, not an approximate "probably the last month." Backfilling more than necessary risks overwriting correct data with a recomputation that itself has edge cases.

Run the backfill through the same idempotent logic the pipeline normally uses (MERGE or partition-replace), never a bespoke one-off script, so a failure partway through the backfill leaves the data in a consistent state rather than half-old, half-new. Pause the regular scheduled run for that window so it doesn't race the backfill and reintroduce the bug on top of your fix. Validate against a known-good sample, a handful of orders you can check by hand, before trusting the full backfilled range.

Somewhere in the ingestion layer, records failing schema validation are probably being logged and skipped rather than raising an alert, on the reasonable-sounding assumption that a malformed record shouldn't take down the whole batch. That's the right instinct applied without a safety net. Nobody is watching the skip count.

The fix is two layers, not one. Keep records that fail validation quarantined rather than silently discarded, so nothing is actually lost. Then alert the moment the skip rate for a source crosses a threshold that would be unusual on a normal day, so schema drift shows up as a page within the hour instead of a slow, quiet data gap someone notices during next month's reporting.

Skew is uneven data distribution across partitions, one partition holding 80% of the rows because most records share the same join key. Joining on country_code when half your rows are "US" is the textbook trigger.

# salting: spread a hot key across N synthetic buckets before the join
from pyspark.sql import functions as F

N = 10
hot_side = df.withColumn("salt", (F.rand() * N).cast("int"))
dim_side_exploded = dim.withColumn(
  "salt", F.explode(F.array([F.lit(i) for i in range(N)]))
)

result = hot_side.join(
  dim_side_exploded,
  on=["join_key", "salt"],
  how="inner",
)

Other standard fixes: a broadcast join, if the smaller side genuinely fits in memory (roughly 10MB by default, tunable), Spark ships it to every executor and skips the shuffle entirely. Adaptive Query Execution in Spark 3.0+ can detect skew and coalesce or broadcast automatically at runtime. And sometimes the honest fix is just handling a known hot key, "US" specifically, as its own branch and unioning the result back in.

Broadcast joins assume the smaller side actually is small. The usual failure: the "small" dimension table grew past the broadcast threshold (often silently, after a few months of new categories or new customers), and Spark either errors out or, worse, spends minutes serializing and shipping a table that's no longer small to every executor before the join even starts.

Check the actual size of the broadcast side with an explain plan, not an assumption from six months ago. If it's genuinely grown too large, either raise the threshold if it still fits reasonably, or drop the broadcast hint entirely and let Spark's cost-based optimizer or Adaptive Query Execution pick a sort-merge join instead. Hardcoding a broadcast hint on a table that grows over time is a common source of a job that mysteriously gets slower every quarter.

Check the Spark UI's stage view first for a partition size that's dramatically larger than the others, that's almost always data skew, not a general memory shortage, and it explains why the job "used to run fine" (the data distribution changed, the code didn't).

If partitions look reasonably even, check for an unintentional collect() or a broadcast join against a table that's outgrown the broadcast threshold, both of which pull far more data onto a single node than the executor's heap can hold. Only after ruling those out is the actual fix "give it more memory," since throwing memory at a skew problem just delays the failure to a larger dataset instead of solving anything.

Three delivery guarantees exist: at-most-once (a message might be lost, never retried), at-least-once (a message might be duplicated, but never silently dropped), and exactly-once (neither). True exactly-once across both processing and the output sink, in a distributed system that can partition, is a genuinely hard problem, not a marketing checkbox.

In practice it's approximated by combining at-least-once delivery with idempotent writes at the sink (MERGE instead of blind INSERT), transactional producers (Kafka's own transactional API), and checkpointed stream processing state so a restart resumes exactly where it left off rather than reprocessing or skipping. You genuinely need it for financial transactions, where a duplicated charge is a real incident, and for inventory systems, where double-counting a decrement oversells stock. For a dashboard that recalculates on every query, at-least-once with an idempotent load is usually good enough and considerably cheaper to build.

Pick a key with too few distinct values, or one where a huge share of traffic shares a single value, and you get a hot partition: one partition absorbing a disproportionate share of the topic's traffic while the others sit comparatively idle, capping your real throughput at whatever one partition and its consumer can handle, no matter how many partitions or consumers exist on paper.

A concrete failure mode: partitioning e-commerce events by country_code when 70% of your traffic is domestic. One partition ends up carrying most of the load, and adding more partitions or more consumer instances doesn't fix a bottleneck concentrated on a single hot key. Picking a higher-cardinality key, user_id or order_id rather than country_code, spreads load far more evenly, while still preserving per-entity ordering for whichever entity actually needs it.

Plain Parquet files are immutable, there's no row-level delete, so satisfying a real deletion request against raw Parquet means finding every file that could contain the user's rows and rewriting each one without those rows, which is expensive and error-prone across years of partitions. This is one of the strongest practical arguments for running on Delta Lake or Iceberg instead of bare files: both maintain a transaction log on top of Parquet, so a DELETE WHERE user_id = X is a real operation. It still rewrites the affected files (copy-on-write) or marks rows as deleted in a delete file that gets merged in at read time (merge-on-read), but you get it as a single statement instead of a manual rewrite job.

The part people miss is that deleting the current version isn't the end of it. Both formats support time travel, so unless you also expire old snapshots, the "deleted" row is still fully readable by querying an older version. You have to run VACUUM (Delta) or expire_snapshots (Iceberg) after the delete to actually purge the physical files, and you need a retention window short enough to meet your compliance SLA but long enough that you're not accidentally breaking an in-flight query or an ongoing time-travel-based audit.

DELETE FROM user_events WHERE user_id = 'abc123';
VACUUM user_events RETAIN 168 HOURS;

And that's still only the primary table. In practice you need a deletion registry that tracks every table, every derived aggregate, every export to a feature store or a BI extract, and every backup that could contain the user's data, then runs the delete-plus-vacuum pass against each one on a schedule, because a one-off manual fix doesn't scale past the first few requests.

This is what a schema registry is for. Producers and consumers register and fetch Avro or Protobuf schemas by ID instead of embedding the full schema in every message, and the registry can enforce a compatibility mode on every new registration before it's accepted. BACKWARD compatibility means a new schema can still be read by consumers using the previous schema version, which covers the common case of adding an optional field with a default. FORWARD is the reverse, old producers writing data that new consumer code can still read. FULL requires both directions, and NONE turns enforcement off entirely, which is how teams end up in this exact incident.

The gotcha that actually breaks pipelines is a change that looks safe but isn't: removing a field that had no default, renaming a field instead of adding a new one, or narrowing a type like changing an int to a smaller int. Any of those fails backward compatibility because old consumer code deserializing new data will either throw or silently read garbage into a field it expects to exist. If the registry is set to BACKWARD instead of BACKWARD_TRANSITIVE, a change can pass compatibility against the single most recent schema version while still breaking a consumer that's two versions behind and hasn't redeployed yet, which is a real and common failure mode in orgs where consumer teams deploy on different cadences.

The fix is layered: set the registry to BACKWARD_TRANSITIVE (or FULL_TRANSITIVE if you have both old producers and old consumers in play) so every historical version is checked, not just the latest, wire schema compatibility checks into CI so a producer's PR fails before merge rather than at runtime, and run a canary consumer on a copy of the topic that alerts on deserialization errors before the change reaches the real fleet. None of that catches a change that's schema-compatible but semantically different, like a units change from dollars to cents with the same int type, so for anything that sensitive you still need an explicit data contract on top of the schema, not just registry enforcement.