Data Engineering Interview Questions and Answers, Part 2: The Debugging Round

Part 1 of the data engineering interview questions and answers covers the rounds and a sized design exercise. Many interviews also include a debugging round: "This report says X, finance says Y. Find out why." There is no new technology to learn for it. What is being tested is whether you know the handful of ways pipelines quietly produce wrong numbers, and whether you can prove which one is happening.

Below, five classic bugs are reproduced on real public data in DuckDB: hotel bookings from two Portuguese hotels (Antonio, de Almeida and Nunes, 2019) and hourly bike rentals in Washington, D.C. (UCI: Bike Sharing). The data is real; the bugs are introduced deliberately, the way they appear in production.

import duckdb

con = duckdb.connect()
# Real data: hotel bookings (CC BY 4.0) and Washington, D.C. bike rentals by hour, in local time (CC BY 4.0)
con.execute("""CREATE TABLE bookings AS
               SELECT row_number() OVER () AS booking_id, hotel, agent,
                      adr * (stays_in_weekend_nights + stays_in_week_nights) AS revenue, is_canceled
               FROM read_csv_auto('hotels.csv', nullstr='NULL')""")
con.execute("CREATE TABLE hours AS SELECT * FROM read_csv_auto('hour.csv')")
q = lambda sql: con.execute(sql).fetchone()

# Bug 1: "Revenue by agent is lower than total revenue"
con.execute("CREATE TABLE agents AS SELECT DISTINCT agent, 'agent ' || agent AS agent_name FROM bookings WHERE agent IS NOT NULL")
total = q("SELECT round(sum(revenue)) FROM bookings WHERE is_canceled = 0")[0]
inner = q("SELECT round(sum(revenue)) FROM bookings b JOIN agents a USING (agent) WHERE is_canceled = 0")[0]
left = q("SELECT round(sum(revenue)) FROM bookings b LEFT JOIN agents a USING (agent) WHERE is_canceled = 0")[0]
missing = q("SELECT count(*) FROM bookings WHERE agent IS NULL")[0]
print(f"B1 revenue: table {total:,.0f} | inner join {inner:,.0f} | left join {left:,.0f} | bookings with no agent: {missing:,}")

# Bug 2: "Revenue doubled after we started keeping agent history"
con.execute("""CREATE TABLE agents_hist AS
               SELECT agent, agent_name, DATE '2015-01-01' AS valid_from,
                      CASE WHEN agent % 3 = 0 THEN DATE '2016-12-31' ELSE DATE '9999-12-31' END AS valid_to FROM agents
               UNION ALL                                  -- a third of agents were renamed in 2017: two versions each
               SELECT agent, agent_name || ' (renamed)', DATE '2017-01-01', DATE '9999-12-31' FROM agents WHERE agent % 3 = 0""")
versions = q("SELECT count(*), count(DISTINCT agent) FROM agents_hist")
fanout = q("SELECT round(sum(revenue)) FROM bookings b LEFT JOIN agents_hist a USING (agent) WHERE is_canceled = 0")[0]
current = q("""SELECT round(sum(revenue)) FROM bookings b
               LEFT JOIN agents_hist a ON a.agent = b.agent AND a.valid_to = DATE '9999-12-31' WHERE is_canceled = 0""")[0]
print(f"B2 agent history: {versions[0]} rows for {versions[1]} agents | revenue joined to all versions {fanout:,.0f}"
      f" (+{100 * (fanout / left - 1):.1f}%) | joined to current versions {current:,.0f}")

# Bug 3: "This week's bookings jumped" (one file was loaded twice after a retry)
con.execute("CREATE TABLE loaded AS SELECT * FROM bookings UNION ALL SELECT * FROM bookings WHERE booking_id % 50 = 0")
raw, distinct = q("SELECT count(*), count(DISTINCT booking_id) FROM loaded")
dups = q("SELECT count(*) FROM (SELECT booking_id FROM loaded GROUP BY booking_id HAVING count(*) > 1)")[0]
fixed = q("SELECT count(*) FROM (SELECT * FROM loaded QUALIFY row_number() OVER (PARTITION BY booking_id) = 1)")[0]
print(f"B3 rows {raw:,} vs distinct ids {distinct:,} | duplicated ids {dups:,} | after QUALIFY dedup {fixed:,}")

# Bug 4: "Daily rentals don't match the operator's report" (local times were stored as UTC)
con.execute("""CREATE TABLE hours_utc AS
               SELECT (dteday + hr * INTERVAL 1 HOUR) AS local_ts,
                      timezone('UTC', timezone('America/New_York', dteday + hr * INTERVAL 1 HOUR)) AS utc_ts, cnt FROM hours""")
day = q("""SELECT sum(cnt) FILTER (WHERE CAST(local_ts AS DATE) = DATE '2012-07-04'),
                  sum(cnt) FILTER (WHERE CAST(utc_ts AS DATE) = DATE '2012-07-04') FROM hours_utc""")
print(f"B4 rentals on 4 July 2012: by local date {day[0]:,} | by UTC date {day[1]:,}")

# Bug 5: "Average revenue per booking: dashboard and finance disagree"
avg_of_avgs = q("""SELECT round(avg(a), 2) FROM (SELECT hotel, avg(revenue) AS a FROM bookings
                   WHERE is_canceled = 0 GROUP BY hotel)""")[0]
weighted = q("SELECT round(avg(revenue), 2) FROM bookings WHERE is_canceled = 0")[0]
print(f"B5 average revenue per stay: average of hotel averages {avg_of_avgs} | over all stays {weighted}")

B1 revenue: table 25,996,260 | inner join 23,313,391 | left join 25,996,260 | bookings with no agent: 16,340
B2 agent history: 453 rows for 333 agents | revenue joined to all versions 39,446,778 (+51.7%) | joined to current versions 25,996,260
B3 rows 121,777 vs distinct ids 119,390 | duplicated ids 2,387 | after QUALIFY dedup 119,390
B4 rentals on 4 July 2012: by local date 7,403 | by UTC date 6,503
B5 average revenue per stay: average of hotel averages 356.15 | over all stays 345.85

Bug 1: the inner join that drops rows

Symptom. Revenue by agent added up to 23.3 million, while the bookings table held 26.0 million.

Diagnosis. 16,340 bookings have no agent. An inner join keeps only rows with a match on both sides, and a NULL key never matches anything, so those bookings vanished without an error. The fastest check in an interview: compare row counts or totals before and after every join.

Fix. Use a left join from the fact table to the dimension, and map missing keys to an explicit "no agent" member, so totals reconcile and the gap is visible.

Bug 2: the join that multiplies rows

Symptom. After the team started keeping agent history, revenue jumped by 51.7%.

Diagnosis. The history table had 453 rows for 333 agents: renamed agents have two versions. Joining on the agent key alone matched each of their bookings twice. One renamed agent was also the largest by bookings, which is why the inflation was so big. The check: before joining, confirm the dimension is unique on the join key, for example by comparing count of rows with count of distinct keys.

Fix. Join to the current version, or for point-in-time accuracy, join on the key and the booking date falling between the version's valid-from and valid-to dates. With current versions only, revenue was back to 26.0 million. The fact and dimension design guide covers history tables properly.

Bug 3: the retried load

Symptom. "This week's bookings jumped."

Diagnosis. One file was loaded twice after a retry. There were 121,777 rows but only 119,390 distinct booking IDs, with 2,387 IDs appearing more than once. Grouping by the natural key and counting duplicates proves it in one query.

Fix. Deduplicate on the natural key; DuckDB's QUALIFY clause filters window function results directly (DuckDB: QUALIFY). Then fix the cause: loads should replace a partition or merge by key, so a retry cannot add rows. The ETL interview questions cover idempotent loads.

Bug 4: the day that moved

Symptom. Daily rentals for 4 July 2012 did not match the operator's report: 6,503 against 7,403.

Diagnosis. The rentals are recorded by local hour in Washington. The pipeline correctly converted timestamps to UTC for storage, but the report grouped by the UTC date. Washington is four or five hours behind UTC, so the evening hours of 4 July landed on 5 July in UTC, and the evening of 3 July moved into 4 July. DuckDB's timezone() function makes these conversions explicit (DuckDB: timestamp with time zone functions).

Fix. Store instants in UTC, but group by the business's local date, converting at query time. Ask in the interview which time zone "a day" means for the business: it is a requirement, not a technical detail.

Bug 5: the average of averages

Symptom. The dashboard showed average revenue per stay of 356.15; finance calculated 345.85.

Diagnosis. The dashboard averaged each hotel's average, giving both hotels equal weight, although the city hotel has far more stays. Finance averaged over all stays.

Fix. Re-aggregate from the underlying rows, or weight each average by its count. Never average pre-aggregated averages unless equal weighting is what the business means.

Other bugs worth knowing

Interviewers have a few more favourites, all of which fail silently:

• Keys that look equal but are not: trailing spaces, different letter case, or IDs loaded as numbers that lose their leading zeros, so "00123" never matches "123".
• Integer division: a ratio of two integer columns truncated to zero in some SQL dialects.
• Late-arriving data: yesterday's partition was computed before all of yesterday's events arrived, so totals drift upwards after the report was sent.
• Filters in the wrong place: a condition on the right-hand table placed in WHERE turns a left join back into an inner join.

Naming one or two of these when the first explanation does not fully reconcile the numbers shows breadth.

How to run a debugging round

1. Reproduce both numbers before guessing.
2. Reconcile step by step: row counts and totals after each join, filter and aggregation.
3. Check keys: NULLs, duplicates and uniqueness of dimensions.
4. Check time: time zones, day boundaries and late data.
5. Fix the cause, then add a test so it cannot come back.

For join behaviour in more depth, see SQL joins interview questions.

Pipeline testing and debugging are practised throughout the Data Engineering course.

Sources

• Hotel booking demand datasets (Antonio, de Almeida and Nunes, Data in Brief, 2019) — Data in Brief (Elsevier)
• Bike Sharing Dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository
• QUALIFY clause (DuckDB documentation) — DuckDB Foundation
• Timestamp with time zone functions (DuckDB documentation) — DuckDB Foundation