Real Interview Questions for the Data Engineer at Meta
NewInterview Q&A
Q&A-1Tell me about yourself.
Reported in Meta Data Engineer Interview Experiences (2026) - Exponent
The 90-second answer
"I'm a data engineer, seven years in, and four of those have been spent owning the warehouse and batch layer rather than building it once and handing it off. Right now I lead four engineers at [company]. I own the batch layer behind finance and growth reporting: about 400 source tables, 1.2 PB in the lake, 60 production pipelines on Airflow and Spark, and a 06:00 freshness SLA on the 20 tables the exec dashboard reads. Before that I was the second data engineer at a Series B marketplace and built ingestion from nothing: 300 GB a day off Kafka and Postgres CDC into partitioned Hive tables.
| Period | Scope | What I owned | Number I can defend |
|---|---|---|---|
| 2019–2021 | Series B marketplace | ingestion: Kafka + CDC → lake | 300 GB/day, 40 pipelines |
| 2021–2023 | same | warehouse model for finance and growth | 900 tables, nightly run 4 h → 40 min |
| 2023–now | current | team of 4, batch platform and SLAs | 1.2 PB, 60 pipelines, 06:00 SLA |
The piece I'd point at is a backfill from last year. Supplier records were landing hours after the orders that referenced them, and the fact build inner-joined against a dimension snapshot taken at the start of the run. Orders whose dimension row hadn't arrived yet were dropped: about 4% of rows over eleven weeks. Nobody caught it because the dashboard was aggregated and the gap sat inside a rounding error.
Two changes fixed it. The join became a left join, with unmatched dimension keys written to a quarantine table instead of disappearing, so the loss became countable instead of invisible. And I moved the fact table's partition key from event date to load date. That second change is what made the repair affordable: with event-date partitions the affected days were already published, so a re-run meant rewriting eleven weeks of history in one shot. With load-date partitions each day's ingest sits in its own partition, so I replayed the eleven weeks in six hours on 40 executors, overwriting only the load partitions that held bad rows. The reconciliation now compares source extract counts to fact counts per load partition and fails the DAG before it publishes. It has caught three real incidents since.
On the shape of the role, I've chosen to own the model and the SLA rather than go the platform or specialist route, and that's a trade rather than an obvious win. Owning the model puts me on the page at 05:40 when freshness slips, and my depth is in modelling and correctness, not query engine internals or stream processing. A platform path would have given me deeper systems skills and a cleaner rotation. I'd call the owner path wrong when there's no platform underneath you: a small team ends up maintaining bespoke infrastructure badly instead of modelling well, and wrong again when the org's bottleneck is genuinely a specialist problem, because then a generalist owner becomes the queue everything waits in.
The reason I'm talking to Meta: at this scale the constraint I care about isn't throughput, it's keeping derived data correct when the source changes underneath it. A user deletion has to propagate through every downstream table and feature set, and most warehouse models I've seen treat that as something bolted on after the model is frozen."
What that answer is doing
It opens with an identity and a position, not a chronology. "2015 I graduated, 2016 I joined…" spends the only 90 seconds you get on facts the interviewer already has in front of them. Three numbers land early: 400 tables, 1.2 PB, 06:00 SLA, which tells them which thread to pull, and every one of those is a thread you want pulled.
The backfill story is compressed to before, mechanism, after. It is the evidence, not decoration, and it ends on a number that shows the fix held.
Failure modes
- Chronological recital. Reads as nerves and tells the interviewer nothing they can't read off the CV.
- Unfalsifiable adjectives. "Passionate about data", "strong collaborator", "end-to-end ownership": zero information, and the interviewer has to invent their own follow-up.
- Running past two minutes without pausing. The question is an invitation to be steered. Stop at 90 seconds; the follow-up tells you whether they want the 30-second or the 3-minute version, and you should have both ready.
- Numbers you can't defend. They will ask why 40 executors and what the shuffle looked like. Say only figures you can take three questions deep.
- No hook at the end. Finish on the thing you want asked about next, or the next question is whatever the interviewer had queued before you walked in.
Curated: · Written: · Reviewed:
Q&A-2Write a query, given a library-style schema with books, users, and check-in/check-out data, that finds for each user the number of books they checked out and did not return before the due date, and returns the username and count.
Reported in Meta Data Engineer Interview Experiences (2026) - Exponent
The 90-second answer
One clarification before writing SQL: "did not return before the due date" covers two populations. Returned late, and still out with the due date already passed. I'll count both, treat a return on the due date as on time, and count checkout events rather than distinct titles. If you want only currently-overdue books, or distinct books, that's a one-line change.
Assumed schema:
sql users(user_id PK, username) books(book_id PK, title) checkouts(checkout_id PK, book_id FK, user_id FK, checkout_date, due_date, return_date NULL) -- NULL = still out
sql -- Postgres 16; :today passed in so the result is reproducible SELECT u.username, COUNT(*) AS overdue_count FROM checkouts AS c JOIN users AS u USING (user_id) WHERE COALESCE(c.return_date, :today) > c.due_date GROUP BY u.user_id, u.username ORDER BY overdue_count DESC, u.username;
The whole query turns on that predicate. COALESCE(return_date, :today) collapses the two cases into one comparison: an unreturned book is scored as if it came back today, so it counts only once its due date is behind us. Writing it as return_date IS NULL AND due_date < :today OR return_date > due_date also works but needs parentheses: AND binds tighter than OR, and the unparenthesised version silently counts every late return regardless of user filters you add later.
Trace (today = 2026-03-25):
| checkout | user | due_date | return_date | counted |
|---|---|---|---|---|
| 1 | ana | 2026-03-01 | 2026-02-27 | no: early |
| 2 | ana | 2026-03-05 | 2026-03-05 | no: on the due date |
| 3 | ana | 2026-03-10 | 2026-03-14 | yes: 4 days late |
| 4 | ana | 2026-03-20 | NULL | yes: still out |
| 5 | ben | 2026-03-30 | NULL | no, not due yet |
Result: ana | 2. Ben is absent, not zero.
Follow-ups I'd expect
Users with no overdue books. The inner join drops them. If the report needs every user, drive from users and LEFT JOIN checkouts with the predicate in the ON clause: putting it in WHERE after a left join converts it back to an inner join and drops the zeros again:
sql SELECT u.username, COUNT(c.checkout_id) AS overdue_count FROM users AS u LEFT JOIN checkouts AS c ON c.user_id = u.user_id AND COALESCE(c.return_date, :today) > c.due_date GROUP BY u.user_id, u.username;
Distinct titles. COUNT(DISTINCT c.book_id) if a user who re-borrowed the same book twice should count once.
Scale. checkouts is the fact table and grows without bound; the predicate is a range filter on due_date, so partition by month on due_date and the scan touches only the partitions that can contain overdue rows. An index on (user_id, due_date) helps the per-user lookup but not the full scan: the partition prune is what matters at Meta's row counts.
Timezone. CURRENT_DATE in the warehouse is UTC; a branch closing at 21:00 local on the due date is still "today" in UTC. Pass the library's local date in as :today rather than relying on the server clock.
Late fees. Same predicate, but SUM(:today - due_date) for unreturned and SUM(return_date - due_date) for late returns: the COALESCE trick carries over unchanged.
Curated: · Written: · Reviewed:
Q&A-3Design a database schema for a ride-sharing app.
Reported in Meta Data Engineer Interview Experiences (2026) - Exponent
The 90-second answer
I'd scope this to the transactional core first (riders, drivers, vehicles, trips, payments) and treat location pings and analytics as separate stores, because their write volume is two orders of magnitude higher. Three decisions carry the design: a trip is a state machine with an append-only event log, driver-to-vehicle is time-bounded many-to-many, and the database itself enforces "one active trip per driver" rather than trusting application code.
sql CREATE TABLE trips ( trip_id BIGINT PRIMARY KEY, rider_id BIGINT NOT NULL REFERENCES riders(rider_id), driver_id BIGINT REFERENCES drivers(driver_id), vehicle_id BIGINT REFERENCES vehicles(vehicle_id), status TEXT NOT NULL CHECK (status IN ('requested','matched','arrived','in_progress','completed','cancelled')), requested_at TIMESTAMPTZ NOT NULL DEFAULT now(), completed_at TIMESTAMPTZ, pickup_geohash TEXT NOT NULL, -- geohash-6, ~1.2 km x 0.6 km fare_minor BIGINT, -- integer minor units, never float currency CHAR(3) );
-- the constraint that prevents double-assignment CREATE UNIQUE INDEX one_active_trip_per_driver ON trips (driver_id) WHERE status IN ('matched','arrived','in_progress');
CREATE TABLE trip_events ( trip_id BIGINT NOT NULL REFERENCES trips(trip_id), seq INT NOT NULL, event_type TEXT NOT NULL, -- matched, arrived, started, completed, cancelled occurred_at TIMESTAMPTZ NOT NULL, payload JSONB, PRIMARY KEY (trip_id, seq) );
Why the event log. trips.status is the current truth; trip_events is how it got there. Every transition is one insert with seq = previous + 1, so the log is the audit trail, the input to fare disputes, and the source for CDC into the warehouse. Recomputing a trip's state from its events is a cheap reconciliation job that catches any writer that skipped the state machine.
Driver and vehicle are separate tables. A driver keeps the same driver_id across cars; driver_vehicle_assignments(driver_id, vehicle_id, valid_from, valid_to) records which car they drove when. A trip stores both driver_id and vehicle_id so a later vehicle change never rewrites history: the analytics question "which car was on trip 91823" stays answerable without a temporal join.
Location pings do not belong in this database. A million concurrent trips pinging every 4 s is 250k writes/s; at ~100 bytes a row that is 25 MB/s, roughly 2 TB/day before compression (illustrative figures). That goes to a partitioned store, Kafka into a time-partitioned table keyed by (city_id, hour), with only the latest position denormalised onto a small driver_presence table for the matching service to read.
Matching. driver_presence(driver_id, geohash, lat, lng, heading, updated_at) indexed on (geohash, updated_at). The matcher queries the 3×3 block of geohash cells around the pickup, filters updated_at > now() - interval '30 seconds', ranks by ETA, then does a conditional update to claim the driver. The partial unique index above is the backstop: two dispatchers racing on the same driver means one gets a unique-violation and retries with the next candidate.
Money. payments and ledger_entries are separate; every fare produces balanced debit/credit rows keyed by (trip_id, entry_no). Integer minor units throughout: a float fare column is a reconciliation bug waiting for its first refund.
Failure modes I'd name. Clock skew across dispatch nodes: order events by seq, not by client timestamps. Hot partitions when a city's pings all land in one hourly partition: sub-partition by city_id. Stale presence rows from crashed drivers: a TTL sweep, plus the 30-second filter above so a stale row can never be matched.
Analytics side. CDC the OLTP tables into Iceberg/Hive, keep dim_driver and dim_vehicle as SCD2 so trip facts join to the attributes that were true on the trip date, and build the surge/pricing aggregates from trip_events rather than from mutable status columns.
Curated: · Written: · Reviewed:
Q&A-4Write a query against a star schema containing sales transactions and book author dimensions to identify all authors who have published at least five books.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
"Published" is a property of the catalogue, not of sales, so this query never touches the fact table. Count distinct books per author in the dimension and filter with HAVING:
sql SELECT a.author_id, a.author_name, COUNT(DISTINCT b.book_id) AS book_count FROM dim_author a JOIN dim_book b ON b.author_id = a.author_id GROUP BY a.author_id, a.author_name HAVING COUNT(DISTINCT b.book_id) >= 5;
The inner join is deliberate: authors with zero books drop out, which is what "published at least five" implies. COUNT(DISTINCT b.book_id) rather than COUNT(*) is the part interviewers push on: it survives a fan-out from a bridge table or a join to the fact table without inflating the count.
Why not join fact_sales
An author with five books and 900 sales rows produces 900 joined rows. COUNT(*) returns 900; COUNT(DISTINCT b.book_id) returns 5. Worse, going through the fact table silently changes the answer: a book published but never sold has no fact row, so an author with five published books and two that sold would be excluded. If the schema only links authors to books via the fact table, say so and use COUNT(DISTINCT b.book_id) over the fact join, but flag that you are now answering "five books that sold", not "five books published".
Grain traps, in the order they bite
| Trap | Symptom | Fix |
|---|---|---|
dim_book is at ISBN/edition grain | Five editions of one title count as five | Count DISTINCT b.work_id if the dimension carries it |
| Books have multiple authors | Author column on dim_book can't hold them | Join a dim_book_author bridge on book_id; DISTINCT keeps the count right |
Unknown-member row (author_id = -1) | Placeholder author appears with a huge count | WHERE a.author_id > 0 or filter on the unknown flag |
| SCD2 author dimension | Same author counted twice | WHERE a.is_current = TRUE or valid_to IS NULL |
Worked trace
dim_author 12,400 rows, dim_book 310,000 rows at ISBN grain, 96,000 distinct work_id. Author 4471 has 6 ISBNs across 3 works. At ISBN grain the query returns her with book_count = 6 and she passes the >= 5 filter; at work grain she has 3 and correctly fails. That single grain decision changes the result set from 41 authors to 27 on this data: worth stating out loud before writing SQL, because the interviewer is usually testing whether you ask.
Follow-ups to expect
- "Now add total revenue per qualifying author." Aggregate the fact table separately and join, rather than joining fact and dimension in one
GROUP BY: otherwise the book count and the revenue multiply each other. Two CTEs, join onauthor_id. - "Make it incremental." The dimension is small enough to recompute nightly; a full
GROUP BYover 310k rows is seconds. Only reach for incremental logic if the author dimension is genuinely large. - "What if an author is deleted?" Soft-delete flags on the dimension mean the filter belongs in
WHERE, notHAVING:HAVINGis for aggregates only, and mixing the two is the most common syntax-level mistake here.
Curated: · Written: · Reviewed:
Q&A-5Calculate the percentage of total sales completed on the exact same day a customer registered, formatted as a clear aggregate ratio.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Three things decide this query, so I'd state them before writing it. "Sales completed" means orders whose status is completed and whose completed_at is set, not created, not shipped. "Exact same day" means the same calendar date in one fixed timezone (I'll use UTC unless the business reports in another), not a rolling 24-hour window. And the denominator is all completed sales, with guest orders (customer_id IS NULL) excluded from both sides: I'll say so, because including them in the denominator only would drag the ratio down.
sql WITH sales AS ( SELECT o.order_id, o.customer_id, o.amount, CAST(o.completed_at AS DATE) AS completed_date FROM orders o WHERE o.status = 'completed' AND o.completed_at IS NOT NULL AND o.customer_id IS NOT NULL ) SELECT COUNT() AS total_sales, COUNT_IF(s.completed_date = CAST(c.registered_at AS DATE)) AS same_day_sales, ROUND(1.0 * COUNT_IF(s.completed_date = CAST(c.registered_at AS DATE)) / NULLIF(COUNT(), 0), 4) AS same_day_ratio FROM sales s JOIN customers c ON c.customer_id = s.customer_id;
COUNT_IF is Presto/Trino; on Postgres it's COUNT(*) FILTER (WHERE ...). NULLIF on the denominator is the only thing standing between an empty table and a division-by-zero error.
What the output looks like
| total_sales | same_day_sales | same_day_ratio |
|---|---|---|
| 10,000 | 1,200 | 0.1200 |
Multiply by 100 for 12.00% if the consumer wants a percentage rather than a ratio. I return the raw counts alongside the ratio because a ratio alone hides whether it came from 12 of 100 or 12,000 of 100,000.
The follow-ups an interviewer will push on
Calendar day vs 24 hours. These are different questions. A customer who registers at 23:50 and buys at 00:10 the next day is same-day under a 24-hour reading and not under a calendar-day reading. If they meant 24 hours, the predicate becomes s.completed_at < c.registered_at + INTERVAL '24' HOUR and the join has to happen before the filter, since the comparison needs both timestamps.
Timezone. CAST(completed_at AS DATE) on a timestamptz uses the session timezone, which is a silent correctness bug. Pin it: CAST(completed_at AT TIME ZONE 'UTC' AS DATE), and use the same zone on registered_at.
Revenue instead of count. "Percentage of total sales" can mean dollars. Swap COUNT_IF(...) for SUM(CASE WHEN ... THEN amount ELSE 0 END) and COUNT(*) for SUM(amount). The ratio is usually lower, because first-day purchases skew small.
Data quality. Check for completed_at < registered_at: it happens when a customer record is backfilled or merged. I'd run that as a separate assertion rather than silently filtering, so the pipeline fails loudly.
Scale. At Meta's order volume the join is the cost, not the aggregate. Two options: broadcast the customer dimension if it fits, or denormalize registration_date onto the order fact at ETL time so the query is a single-table scan with no shuffle. The second is what I'd push for if this ratio is on a dashboard.
Curated: · Written: · Reviewed:
Q&A-6Given a stream of Uber ride requests, how would you compute the number of ride requests in every 15-minute window?
Reported in Meta Data Engineer Interview Experiences (2026) - Exponent
The 90-second answer
Pin down three things first: a "ride request" is the request_created event keyed by request_id; the timestamp is event time from the gateway, not the time the processor sees it; and the windows are tumbling 15-minute windows aligned to the hour (10:00–10:15, 10:15–10:30), counted per city if the product needs that breakdown. Then it is a stateful event-time aggregation with a watermark.
In Spark Structured Streaming (3.5):
python requests = (spark.readStream.format("kafka") .option("subscribe", "ride_requests").load() .select(F.from_json(F.col("value").cast("string"), schema).alias("e")) .select("e.request_id", "e.city", F.to_timestamp("e.event_ts").alias("event_ts")))
counts = (requests .withWatermark("event_ts", "30 minutes") .dropDuplicates(["request_id"]) # at-least-once delivery .groupBy(F.window("event_ts", "15 minutes"), "city") .agg(F.count("*").alias("requests")))
(counts.writeStream .outputMode("update") .foreachBatch(upsert_to_sink) # idempotent on (window, city) .option("checkpointLocation", "s3://.../chk") .start())
window emits [start, end); a 10:15:00 event belongs to 10:15–10:30, not the previous window. The 30-minute watermark keeps state until the processor has seen an event at least 30 minutes past the window end; after that the window is final and late events for it are dropped. That is the knob: a larger watermark accepts more late data at the cost of more state and longer final-output latency.
Trace with events:
| event_ts | request_id | window | running count |
|---|---|---|---|
| 10:02 | r1 | 10:00–10:15 | 1 |
| 10:14 | r2 | 10:00–10:15 | 2 |
| 10:15:30 | r3 | 10:15–10:30 | 1 |
| 10:16 | r4 | 10:15–10:30 | 2 |
| 10:31 | r5 | 10:30–10:45 | 1 |
If r6 with event_ts=10:09 arrives after the stream has seen 10:31, the watermark is 10:31 - 30m = 10:01, so r6 is accepted and the 10:00–10:15 count becomes 3. With a 10-minute watermark the watermark would be 10:21 and r6 would be dropped. That is the difference between a correct count and a silently low one.
Failure modes to name: processing-time windows misattribute events delayed in Kafka; a too-tight watermark drops late events (watch Spark's numLateRecordsDropped); no watermark means unbounded state per window per city; duplicate request_id from at-least-once producers inflates counts unless deduped; a hot city skews one partition, so salt the group key or pre-aggregate. For exactly-once, checkpoint plus an idempotent sink keyed on (window_start, window_end, city), a Delta MERGE or a compacted Kafka topic, because foreachBatch can replay.
Alternatives: Flink SQL TUMBLE(event_ts, INTERVAL '15' MINUTE) with WATERMARK FOR event_ts AS event_ts - INTERVAL '30' MINUTE gives the same semantics with lower per-event latency; Kafka Streams TimeWindows.ofSizeAndGrace keeps state per key. A micro-batch job recomputing the last 15 minutes every minute is simpler but is not event-time correct and doubles compute.
Curated: · Written: · Reviewed:
Q&A-7Find the IDs of the top five customers, ordered by the average payment per item made by the users they successfully invited to the platform.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Two things decide this query before I write a line of it: what "average payment per item" is averaged over, and what counts as a successful invite. I'll state both, then give the query.
Assumptions I'd say out loud: users(user_id, inviter_id, …) where inviter_id is null for organic signups, so a non-null inviter_id already means the invite converted. orders(order_id, user_id, amount, status) with one row per purchased item, amount in integer cents. Only status = 'completed' counts: refunds and chargebacks are excluded.
sql SELECT u.inviter_id AS customer_id, SUM(o.amount)::numeric / COUNT(*) AS avg_payment_per_item FROM orders o JOIN users u ON u.user_id = o.user_id WHERE u.inviter_id IS NOT NULL AND o.status = 'completed' GROUP BY u.inviter_id ORDER BY avg_payment_per_item DESC, customer_id LIMIT 5;
That's the pooled reading: every item bought by anyone this customer invited goes into one pot, and I divide total revenue by total item count. The ::numeric cast is not decoration: SUM over integer cents divided by COUNT(*) is integer division in Postgres, so $40.50 comes back as 40 and the ranking silently changes.
The ambiguity that actually decides the answer
"Average payment per item made by the users they invited" can also mean: compute each invitee's own average, then average those. The two disagree whenever invitees buy different numbers of items.
| Inviter | Invitee | Items | Amount each | Pooled avg/item | Avg of invitee avgs |
|---|---|---|---|---|---|
| A | B | 2 | $10 | ||
| A | C | 1 | $100 | (10+10+100)/3 = $40.00 | (10+100)/2 = $55.00 |
Same data, different top five. I default to pooled because "payment per item" is a per-item quantity and pooling is what a revenue metric means; the avg-of-avgs version weights a one-item buyer the same as a fifty-item buyer, which is rarely what the business wants. If the interviewer meant the other one, it's a two-level aggregate:
sql WITH per_invitee AS ( SELECT u.inviter_id, u.user_id, AVG(o.amount) AS avg_item FROM orders o JOIN users u ON u.user_id = o.user_id WHERE u.inviter_id IS NOT NULL AND o.status = 'completed' GROUP BY u.inviter_id, u.user_id ) SELECT inviter_id, AVG(avg_item) AS avg_of_avgs FROM per_invitee GROUP BY inviter_id ORDER BY avg_of_avgs DESC LIMIT 5;
Edge cases I'd raise unprompted
Invitees with zero completed orders. The inner join drops them, which is correct: their average is undefined, not zero. If product wants them counted as zero, it's a LEFT JOIN plus COALESCE(SUM(o.amount)::numeric / NULLIF(COUNT(*), 0), 0). NULLIF matters: without it you get a division-by-zero error, not a null.
Ties at fifth place. LIMIT 5 picks arbitrarily among equal averages, so the same query run twice can return different customers. I add customer_id as a deterministic tiebreak, and if the ask is genuinely "everyone tied for the top five," I switch to DENSE_RANK() OVER (ORDER BY avg_payment_per_item DESC) and filter rk <= 5, which can return seven rows, and that's the point to confirm with the interviewer.
Self-invites and cycles. WHERE u.inviter_id <> u.user_id kills the trivial self-referral; a longer cycle (A invited B, B invited A) needs a recursive check or a constraint upstream, and I'd say so rather than pretend the query handles it.
Multi-item orders. If orders is one row per basket and line items live in order_items, COUNT(*) counts baskets, not items. The denominator becomes COUNT(oi.item_id) after joining order_items, and the numerator stays the order amount, or moves to oi.price if items are priced individually. Getting this wrong inflates the average by the average basket size, which at Meta scale is a large error.
At production scale
This scans every completed order and hash-joins to users: fine on a laptop, not on a warehouse with billions of rows. The shape I'd actually ship is a pre-aggregated daily rollup keyed by (inviter_id, ds) holding sum_amount and item_count, then the final query sums those two columns and divides. That makes the top-five query a scan of one small table, and it makes the metric reproducible for a given date instead of drifting as orders settle. Indexes that matter for the raw version: users(inviter_id) and orders(user_id, status) INCLUDE (amount).
One last thing I'd flag: ranking on average alone rewards a customer whose single invitee bought one expensive item. If the interviewer wants a ranking that's stable, I'd offer a floor, HAVING COUNT(*) >= 10, and show how the top five changes.
Curated: · Written: · Reviewed:
Q&A-8Write a query against a transaction log to aggregate total order value and unique customer counts grouped and sorted by payment type in descending order.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
I'd clarify two things before writing SQL: what counts as an order in the log (one captured payment per order, or every status transition), and whether "sorted by payment type descending" means the literal ORDER BY payment_type DESC or the aggregate descending. I'll answer the literal version and note the swap.
Assuming one row per captured payment, with columns transaction_id, order_id, customer_id, payment_type, amount, status, updated_at:
sql SELECT payment_type, SUM(amount) AS total_order_value, COUNT(DISTINCT customer_id) AS unique_customers FROM transaction_log WHERE status = 'captured' AND payment_type IS NOT NULL GROUP BY payment_type ORDER BY payment_type DESC;
SUM(amount) gives total order value; COUNT(DISTINCT customer_id) gives unique customers per payment type. Filtering to captured excludes authorizations, voids and refunds: otherwise a refunded order still contributes its original amount. If the log is append-only with status transitions, dedupe to the latest state per order first:
sql WITH latest AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn FROM transaction_log ) SELECT payment_type, SUM(amount) AS total_order_value, COUNT(DISTINCT customer_id) AS unique_customers FROM latest WHERE rn = 1 AND status = 'captured' AND payment_type IS NOT NULL GROUP BY payment_type ORDER BY payment_type DESC;
Trace on four rows:
| order_id | customer_id | payment_type | amount | status | | 1 | c1 | card | 40 | captured | | 2 | c1 | card | 60 | captured | | 3 | c2 | card | 25 | refunded | | 4 | c3 | wallet | 30 | captured |
Result: card → 100, 1 unique customer; wallet → 30, 1. The refunded row is excluded.
If you meant highest total order value first, change the last line to ORDER BY total_order_value DESC.
What an interviewer will push on
- Currency. Summing
amountacross currencies is meaningless. Either addcurrencyto theGROUP BYor convert to a common currency with a dated FX rate. - NULL payment type.
GROUP BYputs NULLs in their own group; I filter them out unless the business wants an "unknown" bucket. - Duplicates. Retries can insert the same
transaction_idtwice. If there's no unique constraint, dedupe withSELECT DISTINCT transaction_id, ...or theROW_NUMBERpattern above. - Scale.
COUNT(DISTINCT)is exact but memory-hungry at billions of rows. In Presto/Trino,approx_distinct(customer_id)uses HyperLogLog with ~2% error; if exactness is required, pre-aggregate to(payment_type, customer_id)then count, which shrinks the shuffle. - Sort direction.
ORDER BY payment_type DESCsorts strings; if payment types are an enum, the order follows the enum's declared order, not alphabetical.
Detection: compare the sum of all groups to a direct SELECT SUM(amount) ... WHERE status='captured'; a mismatch means the filter or dedupe is wrong.
Curated: · Written: · Reviewed:
Q&A-9Given a raw list of customer comments collected across multiple physical locations, how would you find the single most common comment across all locations while ignoring duplicate comments originating from the same location?
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
I'd clarify the grain first: "most common across all locations" should mean the comment mentioned at the most distinct locations, not the comment with the most raw rows. Otherwise one busy location that collected 500 copies of "slow checkout" decides the answer. So I collapse to one row per (location_id, comment), then count locations per comment.
Assuming exact comment text after trimming whitespace, in PostgreSQL 16 or Spark SQL:
sql WITH deduped AS ( SELECT DISTINCT location_id, comment FROM customer_comments WHERE comment IS NOT NULL ), counts AS ( SELECT comment, COUNT(*) AS location_count FROM deduped GROUP BY comment ) SELECT comment, location_count FROM counts ORDER BY location_count DESC, comment ASC LIMIT 1;
DISTINCT removes duplicates within a location. The GROUP BY then counts how many locations each comment appears at. The secondary comment ASC makes the "single" answer deterministic when two comments tie. If the business wants every tied winner, replace the final select with RANK() OVER (ORDER BY location_count DESC) and filter rank = 1.
A made-up trace shows why the dedupe matters:
| Comment | Raw rows | Distinct locations |
|---|---|---|
| Slow checkout | 4 | 2 |
| App crashed | 4 | 3 |
Raw row count ties at 4–4. After deduping per location, "App crashed" wins 3 locations to 2.
Normalization is a product decision, not a SQL detail. If "same comment" is case- and whitespace-insensitive, dedupe on lower(btrim(comment)) and carry a representative string for display:
sql WITH deduped AS ( SELECT DISTINCT location_id, lower(btrim(comment)) AS comment_key FROM customer_comments WHERE comment IS NOT NULL ) SELECT comment_key, COUNT(*) AS location_count FROM deduped GROUP BY comment_key ORDER BY location_count DESC, comment_key ASC LIMIT 1;
That merges "Great service" and " great service ". It also merges "Great service!" only if you strip punctuation, which I would not do silently: punctuation can change sentiment.
At Meta scale, the cost is the DISTINCT over long text. Two practical moves: pre-aggregate per location per day before the global rollup, or hash the comment to a fixed-width key (xxhash64(comment) in Spark) and group on the hash, keeping one representative row per hash. In Spark:
python
df.dropDuplicates(["location_id", "comment"])
.groupBy("comment")
.count()
.orderBy(F.desc("count"), F.asc("comment"))
.limit(1)
Failure modes I'd name: NULL comments get counted as a comment unless filtered; case and whitespace variants split the count; ties make LIMIT 1 arbitrary unless you add a tie-break; and a coordinated spam comment from many locations still wins on breadth even though no real customer wrote it. The query answers the question as asked, breadth across locations, not "most common customer sentiment."
Curated: · Written: · Reviewed:
Q&A-10Write a function to search for a target element within an unsorted list and analyze its execution time and space complexity.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Unsorted means no ordering to exploit, so there is no sub-linear algorithm: every element is a candidate until you find the target, and if it isn't there you must look at all of them. Linear scan, O(n) time, O(1) extra space. I'd confirm the contract first (return the index of the first occurrence, or -1) because duplicates change what "found" means.
python
Python 3.12
def search(items, target): """Index of first occurrence of target in items, or -1.""" for i, x in enumerate(items): if x == target: return i return -1
Complexity, precisely
| Case | Comparisons | Time |
|---|---|---|
| target at index 0 | 1 | O(1) |
| target at index i | i+1 | O(n) |
| target absent | n | O(n) |
| target uniformly placed | (n+1)/2 expected | O(n) |
Space is O(1): two loop variables, no copy, no recursion. Trace on [4, 9, 1, 7, 3, 8, 2] searching for 3: compares 4, 9, 7, 1, 3. Five comparisons, returns index 4. Searching for 6 runs all seven and returns -1.
What the interviewer is actually probing
Whether you reach for the right structure once the access pattern is known. One search on a list you already hold: scan it. Many searches against a list that doesn't change: pay O(n) once to build a set (or dict value→index if you need positions) and every lookup is O(1) expected. The break-even is a handful of queries, because building the set costs more per element than one == comparison: roughly 3–5× in CPython for small ints, so around 3–5 searches before the set pays for itself. If the list is static and you need ordered results, sort once at O(n log n) and bisect at O(log n) per query.
| Approach | Build | Per query | Space | Use when |
|---|---|---|---|---|
| linear scan | — | O(n) | O(1) | one or two searches |
set / dict index | O(n) | O(1) avg | O(n) | many queries, static data |
sort + bisect | O(n log n) | O(log n) | O(n) copy | many queries, need order |
In Python, target in items and items.index(target) run the same loop in C rather than in the interpreter, so they are typically 2–4× faster than the hand-written version for primitive types: use them unless you need custom comparison logic.
Failure modes I'd name
float('nan') == float('nan')isFalse, so a list containing NaN never matches itself. Check withmath.isnanor identity.- Comparing NumPy arrays elementwise returns an array, and
if x == targetthen raises truth value of an array is ambiguous. Usenp.array_equalor compare scalars. - An object with an expensive
__eq__keeps the O(n) but multiplies the constant; a scan over 10M rows at ~60 ns per comparison is roughly 0.6 s in CPython, and that number is what decides whether the loop is acceptable. - If the "list" is really a Spark or Hive partition, the same O(n) applies but it's a distributed scan,
df.filter(col("x") == target), and the cost is I/O and shuffle, not comparisons. Don't hand-roll a Python loop over a DataFrame column.
Curated: · Written: · Reviewed:
Q&A-11Write a query to calculate the maximum number of training workshops hosted in total across any consecutive years that each contained at least one scheduled event.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Assume one row per workshop in workshops(workshop_id, host_year), and that a year with no workshops is either absent from the table or present with a zero count. The question is a gaps-and-islands problem: collapse each unbroken run of years that all have at least one event into one island, sum the workshops in each island, return the largest sum.
The trick is that host_year - ROW_NUMBER() is constant within a run of consecutive years and jumps whenever a year is missing. Filter zero-count years before numbering, or a gap year will silently stitch two islands together.
sql WITH yearly AS ( SELECT host_year AS yr, COUNT() AS workshops FROM workshops GROUP BY host_year HAVING COUNT() > 0 -- drop years with no events ), islands AS ( SELECT yr, workshops, yr - ROW_NUMBER() OVER (ORDER BY yr) AS grp FROM yearly ) SELECT MAX(total) AS max_workshops FROM ( SELECT grp, SUM(workshops) AS total FROM islands GROUP BY grp ) s;
Checked against PostgreSQL 16; runs unchanged on Trino.
Worked trace. Suppose the table holds 2018:3, 2019:5, 2020:0, 2021:2, 2022:4, 2023:1.
| yr | workshops | rn | yr − rn | island sum |
|---|---|---|---|---|
| 2018 | 3 | 1 | 2017 | |
| 2019 | 5 | 2 | 2017 | 8 |
| 2021 | 2 | 3 | 2018 | |
| 2022 | 4 | 4 | 2018 | |
| 2023 | 1 | 5 | 2018 | 7 |
MAX(total) = 8. Note 2020 is filtered out; had it stayed with workshops = 0, its grp would also be 2017 and the answer would wrongly be 15.
Follow-ups an interviewer will push on
- What if there is a calendar dimension with every year?
LEFT JOINit,COALESCE(COUNT(w.workshop_id), 0), and keep theHAVING COUNT(w.workshop_id) > 0filter: same shape, and it makes the zero-year case explicit rather than implicit. - Does a single year count as a run? Yes. A lone year with events is an island of size one; the query already returns it.
- What does "workshops hosted" count? If the fact table has one row per session rather than per workshop,
COUNT(*)overcounts a multi-session workshop. UseCOUNT(DISTINCT workshop_id)and say which you chose: the interviewer is checking whether you noticed the grain. - Cost. One scan, one sort for the window, one hash aggregate: O(n log n) on the number of distinct years, which is tiny. The self-join alternative (
a.yr = b.yr + 1) is O(n²) and never worth it here. - NULL
host_year.GROUP BYputs NULLs in their own group andROW_NUMBERorders them last; addWHERE host_year IS NOT NULLunless you can defend treating them as a year.
Failure modes to name. The one that bites in practice is filtering after numbering instead of before: the query runs, returns a plausible number, and is wrong only when a gap year exists. Detect it by asserting the island count against a manual LAG(yr) break count on a small sample. The second is grain confusion: COUNT(*) on a session-level table inflates every island proportionally, so the max may still look reasonable while being off by the average sessions-per-workshop factor.
Curated: · Written: · Reviewed:
Q&A-12Design an end-to-end data model for tracking engagement metrics on a video feature like Instagram Reels, defining key entities, attributes, and slowly changing dimension strategies.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Two facts and four dimensions, and the grain decision is the interview. Raw engagement is an immutable event stream, one row per event_id, because every metric anyone will ask for later (views, completions, replays, likes, comments, shares, saves, profile visits, follows attributed to a reel) is a different filter over the same events. On top of it sits a daily aggregate at (reel_id, viewer_id, ds), which is what almost every query actually hits.
The SCD answer is: don't SCD2 what you can snapshot. At Reels volume, a range join against a versioned dimension is the most expensive thing in the pipeline, so attributes that slice the metric (country, device, surface) get copied onto the fact at ingest as as-of values. SCD2 is reserved for dimensions where history must be reconstructable rather than merely sliced: creator and content state.
sql CREATE TABLE fact_reel_event ( event_id STRING, -- uuid; dedupe key event_ts TIMESTAMP, event_type STRING, -- view_start|complete|replay|like|comment|share|save|profile_visit reel_id BIGINT, viewer_id BIGINT, creator_id BIGINT, -- denormalised: creator rollups skip a join surface STRING, -- feed|explore|profile|dm country STRING, -- as-of snapshot, not a join watch_ms BIGINT, ds DATE ) PARTITIONED BY (ds);
CREATE TABLE fact_reel_viewer_day ( reel_id BIGINT, viewer_id BIGINT, ds DATE, views INT, completes INT, replays INT, watch_ms BIGINT, likes INT, comments INT, shares INT, saves INT ) PARTITIONED BY (ds);
Dimensions and their SCD strategy
| Dimension | Strategy | Why |
|---|---|---|
dim_reel | SCD2 on visibility, moderation_state, is_deleted | an impression from March must be explainable by March's eligibility, not today's |
dim_creator | SCD2 on follower_band, verified | "did large creators get more reach" needs the band as of the event |
dim_user | SCD1 + daily snapshot table | range joins over billions of users are unaffordable; snapshot the two or three attributes you slice on |
dim_audio | SCD2 on rights_status | licensing takedowns retroactively change what was servable |
Why the snapshot beats the range join. Illustrative numbers: 500M DAU × 30 events = 15B rows/day. Joining that to a versioned user dimension (billions of users, dozens of versions each) shuffles the whole dimension on every run. Copying country (2 bytes) and surface (1 byte) onto the fact costs 15B rows × 3 bytes ≈ 45 GB/day raw, roughly 10 GB compressed, and the join disappears from every downstream query.
Late events and restatement. A complete event for reel 991 lands 3 days late. The event table takes it: partition ds is the event date, so it's an insert into an old partition. The daily aggregate is then recomputed from the immutable events for the affected (reel_id, ds) pairs, never incremented with +=; that is what makes at-least-once delivery from the stream harmless. Dedupe on event_id with a 24-hour state window in the streaming job; anything older is caught by the recompute.
Metric definitions live in the semantic layer, not the model. Whether a view counts at playback start or after a fixed watch threshold is a product decision that has been revised more than once across video products. Store raw playback events with watch_ms and let the threshold be a versioned metric definition, so a definition change is a config bump rather than a backfill of the fact table.
Failure modes. Overlapping valid_from/valid_to in an SCD2 dimension fans out joins silently: enforce non-overlap with a uniqueness check on (natural_key, valid_from) and a row-count assertion before publish. Deletion requests must propagate into the fact table, not just tombstone the dimension; on Iceberg that's a merge-on-read equality delete keyed on reel_id, and it needs its own SLA because the aggregate will otherwise keep serving deleted content's metrics.
Curated: · Written: · Reviewed:
Q&A-13Define success metrics for a localized peer-to-peer transaction ecosystem like Marketplace, and design the dimensional schema required to measure user retention and conversion funnels.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Two halves: pick metrics that describe liquidity, then build a schema where every funnel step is a fact at its own grain joining to conformed dimensions.
The trap is answering with GMV. GMV is a lagging output that a handful of high-ticket sales can move while the market is dying. For local P2P the north star is weekly transacting buyers per DMA: distinct buyers with at least one completed local transaction in the trailing seven days, counted per metro. Pair it with sell-through rate, because in P2P supply churns faster than demand: when sellers stop relisting, buyer retention falls over about two weeks later and GMV hides it until the drop is unrecoverable.
| Metric | Definition | What it catches |
|---|---|---|
| Weekly transacting buyers / DMA | distinct buyers, ≥1 completed local txn, trailing 7d | north star: liquidity |
| Sell-through rate | listings sold within 30d of creation ÷ listings created | supply health |
| D30 buyer repeat | first-time buyer cohort in week W with a 2nd completed txn in days 8–30 | retention |
| Median time to first offer | hours from listing live → first offer | local demand density |
| Cross-DMA share | completed txns where buyer DMA ≠ listing DMA ÷ all completed | guardrail on "localized" |
| Dispute + cancel rate | cancelled or disputed ÷ initiated | trust guardrail |
Funnel, per DMA and category: impression → listing view → save or message → offer → initiated → completed handoff → rated.
Schema
sql -- SCD2; role-played as buyer and seller CREATE TABLE dim_user ( user_key BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN, signup_date DATE, home_geo_key INT );
-- SCD2 so price/status edits don't rewrite history CREATE TABLE dim_listing ( listing_key BIGINT PRIMARY KEY, listing_id BIGINT NOT NULL, seller_user_key BIGINT REFERENCES dim_user(user_key), category_key INT, geo_key INT, price_cents BIGINT, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN );
-- grain: one row per impression CREATE TABLE fact_impression ( impression_id BIGINT, date_key INT, viewer_key BIGINT REFERENCES dim_user(user_key), listing_key BIGINT REFERENCES dim_listing(listing_key), geo_key INT, surface VARCHAR(16), event_ts TIMESTAMP );
-- grain: one row per transaction attempt CREATE TABLE fact_transaction ( transaction_id BIGINT, date_key INT, buyer_key BIGINT REFERENCES dim_user(user_key), seller_key BIGINT REFERENCES dim_user(user_key), listing_key BIGINT REFERENCES dim_listing(listing_key), buyer_geo_key INT, listing_geo_key INT, gross_cents BIGINT, status VARCHAR(16), first_impression_id BIGINT );
Retention is a cohort on first completed transaction:
sql WITH first_txn AS ( SELECT buyer_key, MIN(date_key) AS cohort_key FROM fact_transaction WHERE status = 'completed' GROUP BY buyer_key ) SELECT f.cohort_key, COUNT(DISTINCT f.buyer_key) AS cohort_size, COUNT(DISTINCT t.buyer_key) AS retained_d30 FROM first_txn f LEFT JOIN fact_transaction t ON t.buyer_key = f.buyer_key AND t.status = 'completed' AND t.date_key BETWEEN f.cohort_key + 8 AND f.cohort_key + 30 GROUP BY 1;
Worked funnel (one DMA-week, illustrative): 10.0M impressions → 1.20M views (12.0%) → 90k saves/messages (7.5% of views) → 30k offers (33%) → 12k initiated (40%) → 9k completed (75%). Impression-to-completed is 0.09%. The 88% loss at impression→view is a ranking problem; the 67% loss at message→offer is where pricing and trust live. Those are different teams, which is why the funnel has to be a fact table and not a dashboard query.
Failure modes
- Joining
dim_listingonis_currentinstead ofevent_ts BETWEEN valid_from AND valid_toretroactively re-prices historical conversions when a seller cuts the price after the sale. - Completions arrive late: a Sunday-initiated transaction completes Wednesday, after the weekly partition published. Write to a mergeable fact and let the mart read the latest snapshot; recomputing history shifts cohorts and makes retention non-reproducible.
fact_transactionhas two FKs intodim_user. An unaliased join silently doubles rows: alias buyer and seller explicitly.- Without the
buyer_geo_key = listing_geo_keyfilter, shipping-enabled categories contaminate the local metric. Report cross-DMA share alongside it. - Duplicate and bot listings inflate impressions and deflate conversion; dedupe on (seller, category, price, photo hash) before the impression fact lands.
Curated: · Written: · Reviewed:
Q&A-14Explain how you would model user invitation trees and referral networks to support downstream reporting on conversion velocity and downstream customer lifetime value.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Three layers: immutable events, a slowly-changing referral edge table, and a daily tree snapshot with depth and path materialized. Conversion velocity is a time delta on the invitee; LTV is descendant revenue rolled up the tree, bounded by an attribution window and a depth cap.
Assumptions I'd state up front: each user has at most one inviter (so the graph is a forest, not a general DAG), attribution is first-touch, and LTV credit stops at 3 levels. Beyond that the signal is noise and the join cost explodes.
Layer 1: raw events (append-only, partitioned by dt):
sql invite_events(invite_id, inviter_id, invitee_id, channel, sent_ts, accepted_ts, status) user_events(user_id, event_type, event_ts) -- signup, first_purchase, churn revenue_events(user_id, order_id, amount_cents, currency, event_ts, is_refund)
Layer 2: referral edge, SCD2:
sql dim_referral_edge(child_id, parent_id, invite_id, valid_from, valid_to, is_current)
SCD2 because attribution gets corrected: fraud reversals, disputed invites. You restate a cohort without rewriting history.
Layer 3: tree snapshot. A recursive CTE per query is a non-starter at billions of edges. Precompute a bounded-depth closure table with an iterative join:
sql WITH l1 AS (SELECT child_id, parent_id AS ancestor_id, 1 AS depth FROM dim_referral_edge WHERE is_current), l2 AS (SELECT l1.child_id, e.parent_id, 2 FROM l1 JOIN dim_referral_edge e ON l1.ancestor_id = e.child_id AND e.is_current), l3 AS (SELECT l2.child_id, e.parent_id, 3 FROM l2 JOIN dim_referral_edge e ON l2.ancestor_id = e.child_id AND e.is_current) SELECT * FROM l1 UNION ALL SELECT * FROM l2 UNION ALL SELECT * FROM l3;
That gives (descendant_id, ancestor_id, depth): roughly 3× the edge count, tractable. Need arbitrary depth? Path enumeration (/a/b/c) with prefix joins, but you cap path length and pay on updates.
Conversion velocity. Two deltas per invitee: accepted_ts - sent_ts (accept latency) and first_purchase_ts - signup_ts (activation latency). Aggregate by inviter signup-week cohort and channel. Report p50/p90: the distribution is heavy-tailed and the mean lies.
Downstream LTV. Roll revenue up the tree:
sql SELECT a.ancestor_id, SUM(r.amount_cents) AS ltv_cents FROM tree_snapshot a JOIN revenue_events r ON a.descendant_id = r.user_id WHERE r.event_ts BETWEEN a.attribution_start AND a.attribution_start + INTERVAL '365' DAY AND r.is_refund = FALSE GROUP BY a.ancestor_id;
Worked example: A invites B and C; B invites D. Year-one spend: D $120, B $40, C $0. A's 3-level LTV = $160. Depth-1 only would give A $40: the $120 from D is the whole point of the tree.
Failure modes.
- Cycles. A invites B, B invites A. The iterative join loops. Cap depth and carry a visited set, or reject at write with a uniqueness constraint on
child_idwhereis_current. - Late-arriving invites. An invite accepted 30 days after sent lands in a later partition. Use event-time watermarks and reprocess the affected snapshot; never mutate the edge table in place.
- Re-attribution. A disputed invite flips parent. SCD2 handles the history; rebuild the snapshot for the affected date range.
- Depth explosion. A viral campaign creates a 20-level chain. The 3-level cap is a product decision: document it and expose
depthso analysts can see truncation.
Curated: · Written: · Reviewed:
Q&A-15Walk through how you handle late-arriving event data in a distributed analytics processing pipeline, outlining trade-offs between backfilling batch schemas and real-time streaming updates.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
Late data is an event-time problem, not a batch-versus-streaming problem. Keep the raw event log as the source of truth. Run a streaming job with event-time windows and a watermark for freshness, and a batch job over the same log for correctness and backfill. Both write to one table through an idempotent MERGE keyed on the window and dimensions, so a correction is an update, not a duplicate.
Two numbers decide the split: p99 lateness, and how much correction the consumer tolerates. If p99 lateness is 4 minutes and the SLA is 15, a 20-minute watermark covers almost everything and the batch job is a safety net. If p99 is 6 hours, streaming is a preview and batch is the number of record.
Event time, processing time, watermark
A click at 10:00:00 that reaches the pipeline at 10:14:00 has event_time 10:00 and processing_time 10:14. Watermark = max event time seen − allowed out-of-orderness. A 15-minute window [10:00, 10:15) fires when the watermark reaches 10:15, i.e. when max seen reaches 10:35 with a 20-minute delay.
| event_time | arrives | max seen → watermark | outcome |
|---|---|---|---|
| 10:00:00 | 10:00:30 | 10:00:30 → 09:40:30 | on time, window open |
| 10:00:00 | 10:14:00 | 10:14:00 → 09:54:00 | 14 min behind max seen, inside 20-min delay → accepted |
| 10:00:00 | 10:40:00 | 10:40:00 → 10:20:00 | event_time < watermark → dropped to side-output; batch corrects |
Trade-offs
| Streaming update | Batch backfill | |
|---|---|---|
| Latency to visible | seconds to minutes | hours (next run) |
| Correctness | final at watermark; later events dropped | exact over full log |
| Cost | continuous, ∝ throughput | ∝ data scanned; 2B events × 1 KB = 2 TB per day-partition |
| Schema change | state migration / savepoint | re-read with new schema, rewrite partition |
| Recovery | checkpoint + Kafka offset replay | rerun partition, idempotent overwrite |
| Ops burden | state size, backpressure | scheduling, skew |
The write path
Spark 3.5 Structured Streaming, watermark plus foreachBatch merge into Iceberg:
python agg = (events .withWatermark("event_time", "20 minutes") .groupBy(F.window("event_time", "15 minutes").alias("w"), "country") .agg(F.count("*").alias("cnt")))
def upsert(batch_df, _): batch_df.createOrReplaceTempView("staged") spark.sql(""" MERGE INTO prod.clicks_15m t USING staged s ON t.window_start = s.w.start AND t.country = s.country WHEN MATCHED THEN UPDATE SET t.cnt = s.cnt, t.updated_at = current_timestamp() WHEN NOT MATCHED THEN INSERT (window_start, country, cnt, updated_at) VALUES (s.w.start, s.country, s.cnt, current_timestamp()) """)
agg.writeStream.option("checkpointLocation", "s3://chk/clicks_15m")
.trigger(processingTime="1 minute").foreachBatch(upsert).start()
The batch job recomputes the day partition from the raw log and INSERT OVERWRITEs it, or merges with source='batch' winning. Either way the key is the same, so replay is safe.
Failure modes and detection
- Watermark too tight → silent undercount. Detect: nightly reconciliation of streaming vs batch counts per partition; alert at >0.1% delta.
- Watermark too loose → state explosion, checkpoint timeouts. Detect: checkpoint size and RocksDB state per operator.
- Backfill racing the stream → double count. Detect: duplicate
(window_start, country)keys; fix by writing batch to a shadow partition and swapping. - Client clock skew: one event with a timestamp 2 hours ahead pushes the watermark forward and drops everything real. Detect:
max(event_time) − now()alert; clamp future timestamps at ingest. - Schema evolution: backfill reads old Parquet without new columns. Detect: schema-registry version check,
mergeSchemaon read.
Skip streaming entirely when the SLA is daily: batch-only is cheaper and simpler. Skip batch only if the consumer can never accept a correction.
Curated: · Written: · Reviewed:
Q&A-16Describe a situation where a major production data pipeline failed or missed its SLA. How did you diagnose the issue, communicate with stakeholders, and prevent reoccurrence?
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
I owned a daily revenue rollup: Spark on Hive, ~1.2 TB per partition: that fed finance dashboards and a downstream ML feature. SLA was 07:00 UTC. One morning it landed at 10:40. I diagnosed two compounding causes: an upstream Kafka partition arrived 2 h late after a producer deploy, and a skewed join key (null account bucket) held 40% of rows, turning a 12-minute stage into 2 h 10 m. I posted scoped impact and an ETA in the on-call channel within 15 minutes, then shipped three fixes: a freshness gate, skew-aware salting, and an idempotent backfill command.
Diagnosis
The DAG was running with no failed tasks, so the scheduler was not the problem. I pulled stage-level metrics and compared them to the previous seven runs.
| Signal | Normal | Incident | What it told me |
|---|---|---|---|
| Join stage p95 | 12 min | 2 h 10 m | compute blowup, not I/O |
| Upstream APAC partition freshness | 06:10 | 08:20 | late-arriving data |
| Max partition size | 1.2 GB median | 41 GB | extreme skew |
| Executor spill | < 5 GB | 380 GB | memory pressure from skew |
The late partition alone would have delayed the run by ~2 h. The skew turned that delay into a 3 h 40 m miss because the join could not parallelise the null bucket. I confirmed by running the join on the suspect partition in isolation: 38 minutes for 41 GB versus 90 seconds for a normal partition.
Communication
I posted in #revenue-oncall at 07:12 with three facts: dashboards are stale, no data is lost, ML feature is delayed. I gave an ETA of 09:30 and updated every 30 minutes. I also pinged the finance lead directly because it was month-end close and they had a 09:00 review. The status page carried the same wording so support could answer without asking me.
Prevention
- Freshness gate. The DAG now waits on an upstream partition sensor with a 30-minute timeout. If the partition is late, it alerts and either runs with a
partial_data=trueflag or holds, depending on the consumer contract. Finance dashboards hold; ML features accept partial with a flag. - Skew-aware join. I salted the null/zero account key into 16 buckets, joined on
concat(key, '_', bucket), then re-aggregated. The join stage returned to 14 minutes on the next run. - Data quality check before publish. A SQL check fails the task if row count is outside 3 sigma of the 7-day median, or if any single join key exceeds 10% of rows. That catches both late data and skew before the SLA window closes.
- Backfill automation. A one-command idempotent partition overwrite. Backfilling one day is 1.2 TB; at the cluster's measured 400 MB/s that is ~50 minutes, which is why the 09:30 ETA was credible rather than optimistic.
The pipeline has not missed the 07:00 SLA since. The trade-off is an extra sensor task and a quality check that occasionally fails on legitimate traffic spikes: we tuned the sigma threshold to 3.5 after two false positives in the first month.
Curated: · Written: · Reviewed:
Q&A-17How do you resolve technical disagreements with Data Scientists or Product Managers regarding metric definitions or data pipeline requirements?
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
I split the disagreement into two questions and never argue them together: what the metric means, and what the pipeline must guarantee. The first is a semantic decision owned by the Data Scientist or PM; the second is an engineering trade-off I own. My job is to make both concrete enough that the decision is cheap.
First I write both definitions as SQL and run them against the same week of data. A recent one: the PM defined daily active user as any user_id with an event; the DS defined it as a session_start. On a 7-day sample the first gave 12.4M, the second 11.1M: a 10.5% gap concentrated in background sync events from one Android build. That number ended the abstract argument. We kept the broader definition for the growth dashboard and added a session_start-based engaged DAU alongside it, with both in the metric spec.
Then the pipeline requirement. The DS wanted hourly refresh for a dashboard read in a weekly review. The source table is partitioned by day at 180 GB; without an hourly partition, each hourly run rescans the full day: 24 × 180 GB = 4.3 TB/day against 180 GB for a daily run. I proposed adding an hourly partition for the current day and running hourly only on that slice: 24 × 7.5 GB ≈ 180 GB/day plus cluster overhead, roughly the same cost as daily, and the dashboard gets its freshness. The weekly review did not need 12 months of hourly history, so we kept daily for history.
The artefact that prevents the next disagreement
Every metric gets a spec row before it gets a pipeline. The spec is the contract; the SQL is an implementation detail.
| Field | Example |
|---|---|
| Metric name | engaged_dau |
| Formula | count(distinct user_id) where event = 'session_start' |
| Grain | user-day, UTC |
| Filters | excludes is_internal = true, dedupes event_id |
| Source | dim_users, fact_events (v3) |
| Owner | DS (semantics), DE (pipeline) |
| SLA | daily by 06:00 UTC, 99.5% |
| Version | 2.1, changed 2026-01-14 |
When a definition changes, the version bumps and both dashboards are reconciled in the same PR. That is how you stop two teams quoting different numbers in the same review.
Failure modes I watch for
- Definition drift by dashboard. Someone edits a Looker or Tableau calculation and the metric silently diverges. Detection: a reconciliation job that compares the dashboard aggregate to the warehouse table daily and alerts on a delta above 0.5%.
- Backfill breaking a downstream join. A new pipeline requirement, such as adding a late-arriving dimension, triggers a backfill that double-counts. Detection: row-count and primary-key uniqueness tests on the partition before swap.
- SLA negotiated without a cost number. "Can it be real-time?" is not a requirement until someone says what it costs. I bring the scan size and the run count to the meeting.
When we still disagree
I write a one-page decision log: the two options, the measured delta, the cost, and who owns the call. The DS owns semantics, I own feasibility and cost, the PM owns priority. If it is still unresolved, it goes to the analytics lead with that page, not with a Slack thread. Then we commit, and the spec records the decision so it is not relitigated next quarter.
Curated: · Written: · Reviewed:
Q&A-18Tell me about a time you identified a critical flaw in an existing data model or pipeline architecture and proactively led the effort to rebuild it.
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The pipeline was the daily GMV dashboard for a marketplace doing about 2M orders a day. Finance closed the books on it and marketing set spend against it. I owned the Airflow DAG and the Spark jobs behind it.
The flaw was a join fanout that had been there for 18 months. The fact table was built with:
sql SELECT o.order_id, o.order_ts, o.amount, p.promo_code FROM orders o LEFT JOIN promo_redemptions p ON o.order_id = p.order_id
Then GMV was SUM(amount) grouped by day. The model assumed one promo per order. In reality, 20% of promo orders had two or more stacked codes. A left join duplicates the order row for each promo, so those orders' amounts were counted multiple times. Orders with no promo were fine. The dashboard overstated GMV by 6-14% depending on the campaign mix.
On a day with 1,000 orders and $100k GMV, 300 orders had promos. 240 had one code, 60 had two. The left join produced 1,060 rows. The 60 double-promo orders had $6k of the $30k promo GMV, so their amount was counted twice: $70k non-promo + $24k single-promo + $12k double-counted = $106k. A 6% overstatement on that day.
I found it because finance's month-end reconciliation was off by $1.2M on a $9M month. I traced it in an hour with a query that compared COUNT(*) to COUNT(DISTINCT order_id) on the fact table. The ratio was 1.08. Then I grouped by promo count and saw the multiplier.
I wrote a one-page memo: root cause, the 6-14% range, which dashboards and downstream models were affected, and three options.
| Option | What it does | Trade-off |
|---|---|---|
| A | dedup promos with ROW_NUMBER | fixes the number, loses promo attribution |
| B | pre-aggregate promos per order | fixes the number, still no promo-level ROI |
| C | fact at order grain + bridge table | preserves attribution, more complex |
I recommended C because marketing needed promo attribution and the bridge made the grain explicit.
I got buy-in from the data platform lead and the finance VP by showing the reconciliation gap and the cost: two engineers, six weeks, a 24-month backfill of about 40TB. We dual-ran the old and new pipelines for two weeks. The new model matched the ledger to within 0.3% daily; the old one was off by 8% on average.
The rebuild: fact_orders has one row per order, with order_amount and a promo_count column. bridge_order_promo has one row per order-promo pair, with allocated_amount split evenly across promos. We added dbt tests: unique and not_null on fact_orders.order_id, and a reconciliation test that sums fact_orders.order_amount and compares to the finance ledger with a 0.5% threshold. The backfill ran as a Spark job over 24 months of partitions, writing to a new table, then we swapped the view.
Failure modes we hit: late-arriving promo redemptions restated two days after the fact. We fixed that by partitioning on event date and running a daily merge that reprocesses the last three days. The backfill itself took 6 hours on 200 executors; we ran it on a Saturday. Detection now is the reconciliation test plus a row-count anomaly alert.
The outcome: GMV variance dropped from 8% to under 0.3%, marketing reallocated about $400k of spend based on the corrected promo ROI, and the bridge model became the template for two other fact tables.
Curated: · Written: · Reviewed:
Q&A-19Describe a time when a critical bug in an upstream source schema corrupted downstream analytical tables. How did you recover the data and update your validation pipelines?
Reported in Meta Platforms Data Engineer Interview Questions 2026 - Dataford
The 90-second answer
On the revenue mart, a payments team migration changed amount_cents BIGINT to amount DOUBLE in dollars but kept the column name and shipped no version bump. Our nightly Spark job still did sum(cast(amount_cents as decimal(18,2))), so every value landed 100× small. Four daily partitions (2026-02-03 → 2026-02-06) and three Unidash dashboards were wrong before a p99 alert on revenue-per-order fired at 03:12 on the 7th.
I ran it as contain → scope → restore → replay → prevent.
Contain and scope. I paused the mart's scheduler entry and its children, then diffed the source table's schema history against the last good run. That pinned the change and the first bad partition. A per-partition count(*) and sum() comparison between source and mart bounded the damage: 4 partitions, 11.2M rows, $4.1M understated revenue.
Recover. We keep 30 days of mart snapshots. I restored the four partitions from the 2026-02-02 snapshot into staging, then replayed with a compatibility view:
sql CREATE VIEW payments_compat AS SELECT CASE WHEN schema_version = 'v2' THEN amount * 100 ELSE amount_cents END AS amount_cents, ... FROM payments;
The schema_version guard matters: a blanket ×100 would have double-counted late-arriving old-schema rows. The backfill took 38 minutes for 11.2M rows on 40 executors. I reconciled source sum vs mart sum to the cent and row counts per partition, then unpaused children.
Update validation. Three layers:
| Check | What it catches | Action |
|---|---|---|
Schema contract on amount type/unit | silent type or unit change | fail job before write |
| AOV within 0.5×–2× trailing 7-day median | unit/scale bugs | fail partition |
| Source-to-mart sum diff = 0 | aggregation drift | block publish |
We also added a quarantine table for rows failing range checks so non-financial pipelines can keep freshness while alerting.
The trade-off: blocking on schema change protects money metrics but stops freshness; quarantining keeps freshness but risks partial data. We block for revenue, quarantine for engagement. Detection went from four days to under an hour.
Curated: · Written: · Reviewed:
Q&A-20Write a function that inverts a dictionary, mapping each value to the list of keys that had that value.
Reported in Meta Data Engineer Interview Guide | DataDriven
The 90-second answer
I'll assume values are hashable and that each output list preserves the input dict's insertion order. Python 3.7+ guarantees dict order, so the result is deterministic.
python def invert_to_lists(d: dict) -> dict: out = {} for key, value in d.items(): out.setdefault(value, []).append(key) return out
setdefault does one lookup and inserts an empty list on first sight. defaultdict(list) is the same idea with a different failure mode: it creates entries on read, so a later out[missing] silently adds a key instead of raising. Trace: {"a": 1, "b": 2, "c": 1} becomes {1: ["a", "c"], 2: ["b"]}. One pass, O(n) expected time and O(n) space; worst case O(n²) only if every value collides in the hash table, which you don't design around.
What breaks it
- Unhashable values.
{"a": [1, 2]}raisesTypeError: unhashable type: 'list'. Freeze to a tuple recursively, or canonicalise to a string, but only if the canonical form is consistent, or two equal lists land in different buckets. - Type collapse.
1,1.0andTruehash equal and compare equal, so they merge into one bucket. In a pipeline where a column mixes ints and bools, that silently combines categories. Key on(type(v), v)if the distinction matters. - Mutation during iteration. Adding or deleting keys while looping raises
RuntimeError: dictionary changed size during iteration. Build a new dict, never edit in place.
Variants and when to use them
| Variant | Code | When |
|---|---|---|
loop + setdefault | out.setdefault(v, []).append(k) | default; safe reads |
defaultdict(list) | out[v].append(k) | you control every read |
| set values | out.setdefault(v, set()).add(k) | dedupe wanted, order irrelevant |
sorted + groupby | groupby(sorted(d.items(), key=itemgetter(1)), itemgetter(1)) | input already sorted; O(n log n) |
| pandas | df.groupby("value")["key"].agg(list) | data already in a frame |
At Meta scale this is a group-by, not a Python loop. If the dict has 50M entries, do it in Spark with groupBy("value").agg(collect_list("key")) and let the shuffle handle it; the Python version is for config maps and small lookup tables. The one thing I'd check before shipping: whether the caller needs a list or a set, because a list preserves order and duplicates, and a set changes the contract.
Curated: · Written: · Reviewed:
Q&A-21Write a query that, given sales transactions data, finds customers who purchased 3 or more books on both the first day of sales recorded in the dataset and the last day of sales recorded.
Reported in Meta Data Engineer Interview Guide | DataDriven
The 90-second answer
Three things are ambiguous, so I'd state them before writing SQL. "First day of sales recorded in the dataset" is the global MIN(sale_date) over the whole table, not the customer's own first purchase. "3 or more books" I read as copies, SUM(quantity), not distinct titles. And a day is a calendar date in the store's timezone, so if sale_ts is stored UTC I truncate after converting, otherwise a 23:00 PT order lands on the next day.
With those fixed, one pass with conditional aggregation does it:
sql WITH bounds AS ( SELECT MIN(sale_date) AS d_min, MAX(sale_date) AS d_max FROM sales WHERE status = 'completed' -- exclude refunds/cancellations AND customer_id IS NOT NULL -- guest checkouts can't be attributed ) SELECT s.customer_id FROM sales s CROSS JOIN bounds b WHERE s.sale_date IN (b.d_min, b.d_max) AND s.status = 'completed' AND s.customer_id IS NOT NULL GROUP BY s.customer_id HAVING SUM(CASE WHEN s.sale_date = b.d_min THEN s.quantity ELSE 0 END) >= 3 AND SUM(CASE WHEN s.sale_date = b.d_max THEN s.quantity ELSE 0 END) >= 3;
Why this shape. The HAVING clause is two independent per-day tests, so a customer with 5 books on day one and 2 on the last day is correctly rejected: a single SUM(quantity) >= 6 would wrongly admit them. The CROSS JOIN is free: bounds is one row. And it degrades correctly when the dataset covers a single day, because both CASE arms then evaluate the same rows and the two conditions collapse into one, which is the right answer for a degenerate input.
Worked trace on five customers, d_min = 2024-01-01, d_max = 2024-03-31:
| customer | books on d_min | books on d_max | result |
|---|---|---|---|
| c_01 | 3 | 2 | out: fails last day |
| c_02 | 4 | 3 | in |
| c_03 | 3 | 3 | in |
| c_04 | 5 | 0 | out: never bought on last day |
| c_05 | 1 | 4 | out: fails first day |
The readable alternative is two filtered sets and INTERSECT:
sql WITH per_day AS ( SELECT customer_id, sale_date, SUM(quantity) AS books FROM sales WHERE status = 'completed' AND customer_id IS NOT NULL GROUP BY customer_id, sale_date HAVING SUM(quantity) >= 3 ), bounds AS (SELECT MIN(sale_date) d_min, MAX(sale_date) d_max FROM sales) SELECT p.customer_id FROM per_day p JOIN bounds b ON p.sale_date = b.d_min INTERSECT SELECT p.customer_id FROM per_day p JOIN bounds b ON p.sale_date = b.d_max;
Same result, easier to review, but it aggregates every day in the table before throwing most away. On a partitioned fact table the first version wins: MIN/MAX on sale_date is answered from partition metadata on Iceberg, Delta and Hive, so the second scan touches two partitions instead of all of them. On a 2-billion-row sales table that's the difference between a 40-second full scan and a couple of seconds.
Follow-ups I'd expect. If the interviewer meant distinct titles, swap SUM(quantity) for COUNT(DISTINCT book_id) in both CASE arms: the structure is unchanged. If refunds arrive as negative-quantity rows rather than a status column, drop the status filter and let the signed sum net out, but then a customer who bought 3 and returned 1 on the same day falls below the threshold, which is a product decision, not a SQL one. And if "first day" means the first day that customer appears, replace bounds with a per-customer MIN(sale_date) window: a different question, and worth confirming out loud rather than guessing.
Curated: · Written: · Reviewed:
Q&A-22Design a data warehouse to combine cellular tower connectivity data with Facebook app logs, including pipeline architecture and dimension/fact schema.
Reported in Meta Data Engineer Interview Guide | DataDriven
The 90-second answer
The two sources don't share a grain, so the real design question is the join key. App logs are per user session and carry user_id and device_id. Carrier connectivity feeds are per device per sampling interval (signal strength, radio access type, cell id) with no user id and often only a pseudonymous device identifier. The only key that survives both is the pseudonymous device id plus a time window. So: land both raw, conform on device and UTC time, keep two facts at their native grain, and materialise a third fact at session grain that carries network context. If the carrier feed is cell-aggregate only (no device id), you cannot join at user level at all: you fall back to a cell × hour fact and compare app QoE aggregated to the same cell. Say which case you're in before drawing anything.
Schema
| Table | Grain | Key | Notes |
|---|---|---|---|
dim_device | device version | device_sk | SCD2 on OS, carrier, plan; device_root_id where stable |
dim_cell | cell × operator version | cell_sk | SCD2 on RAT, band, geo; keyed (operator_id, cell_id, valid_from) |
dim_app_context | app/feature | app_sk | feature surface, app version |
fact_connectivity_sample | device × 5-min bucket | device_sk, bucket_ts | rsrp_dbm, rat, cell_sk, latency_ms, sample_count |
fact_app_session | session | session_id | device_sk, user_sk, duration, bytes, error_code |
fact_session_network | session | session_id | dominant cell_sk, avg signal over the session |
Pipeline
carrier drops (S3, T+1, sometimes T+3) -> Spark ingest, dedupe on (device_id, bucket_ts), UTC normalise -> INSERT OVERWRITE PARTITION (dt) into fact_connectivity_sample app logs (Scribe/Kafka) -> sessionise -> fact_app_session (dt partitioned) -> interval join on device_sk, broadcast dim_cell (a few million rows) -> fact_session_network
Partition both facts on dt and bucket both on device_sk (4096 buckets) so the join is a co-partitioned bucket join with no shuffle of either side. Sizing: 40M devices × 288 five-minute buckets is ~11.5B rows/day, roughly 1 TB compressed per day. The smaller side of the join, but a 30-day window is ~30 TB, so it is nowhere near broadcastable. Only the dimensions are: dim_cell at a few million rows is tens of MB and broadcasts cleanly; the connectivity fact never does.
The join, traced
Session s_9912 runs 14:02:11–14:09:40 UTC. Its connectivity buckets are 14:00 (overlap 2m49s) and 14:05 (overlap 4m40s). Take the largest-overlap bucket as the dominant cell, average rsrp_dbm across both, and write one row. Sessions shorter than a bucket get the bucket containing their start; sessions spanning more than three buckets get flagged, because tower handoff mid-session is a real signal, not noise.
Failure modes
- Timezone drift. Carrier feeds often arrive in local time with no offset. Store UTC plus the raw offset, and alert on per-hour sample counts: a DST shift shows up as a 2× or 0× hour, not an error.
- Device id churn. IDFA resets and reinstalls mint a new
device_sk. Keep SCD2 and track join rate per day; a drop from 92% to 70% means a carrier changed its hashing, not that users left. - Late drops. Nominal T+1 with a T+3 tail. Use a 3-day lookback and idempotent partition overwrite so a re-run is safe.
- Cell id reuse. Cell ids are unique per operator and get recycled across upgrades: hence the versioned
dim_cellkey. - Skew. Stadium and airport cells dominate volume; salt the join or broadcast the cell dimension rather than letting one reducer take the hot partition.
When not to build fact_session_network: if the carrier contract forbids device-level joins, the honest design is a cell × hour fact joined to app metrics aggregated to cell × hour. Coarser, but it doesn't fabricate a user-level link that doesn't exist.
Curated: · Written: · Reviewed:
Q&A-23Write a query: given a library database with `copies` and `checkouts` tables, calculate the total count of books in good condition that have not been returned, and determine the percentage of those books with more than two renewals.
Reported in Meta Data Engineer Interview Questions & Guide 2026 | Dataford
The 90-second answer
Two things decide this query before I write a line of it: what "not returned" is stored as, and whether "books" means copies or distinct titles. I'll assume checkouts.returned_at IS NULL marks an open checkout, and I'll report both counts (copies currently out in good condition, and distinct book_ids) because a title with three copies on loan is one book to a librarian and three rows to the database. The percentage is computed over the same population as the count, so the numerator and denominator can't drift apart.
sql WITH open_good AS ( SELECT c.copy_id, c.book_id, ck.renewals FROM copies c JOIN checkouts ck ON ck.copy_id = c.copy_id WHERE c.condition = 'good' AND ck.returned_at IS NULL ) SELECT COUNT() AS copies_out_good, COUNT(DISTINCT book_id) AS titles_out_good, ROUND(100.0 * COUNT() FILTER (WHERE renewals > 2) / NULLIF(COUNT(*), 0), 2) AS pct_over_two_renewals FROM open_good;
NULLIF(COUNT(*), 0) is the part interviewers look for: with no open good-condition checkouts the denominator is zero and the whole expression returns NULL instead of raising division-by-zero. FILTER is Postgres 9.4+ and standard SQL:2003; on engines without it, SUM(CASE WHEN renewals > 2 THEN 1 ELSE 0 END) is identical.
Worked trace. Five copies, three of them good and currently out with renewals of 0, 1 and 4; the other two good copies are on the shelf (returned_at set). open_good has three rows. copies_out_good = 3, titles_out_good = 3 (all distinct titles here), and one row has renewals > 2, so pct_over_two_renewals = 33.33. If a fourth good copy of an already-counted title were out, the copy count would read 4 and the title count 3: that gap is exactly why I return both.
Where this breaks, and how I'd catch it
- Two open checkouts for one copy. A missing
returned_atwrite or a re-checkout without closing the prior row double-counts. Detect withSELECT copy_id FROM checkouts WHERE returned_at IS NULL GROUP BY 1 HAVING COUNT(*) > 1. If the source can't guarantee one open row per copy, dedupe withDISTINCT ON (copy_id) ... ORDER BY checked_out_at DESCand say so out loud. renewalsmay not be a column. If renewals live in arenewalsevent table, the count isCOUNT(r.*)per checkout, and the join must be aLEFT JOINor checkouts with zero renewals vanish from the denominator. That silently inflates the percentage: the classic version of this bug.- Condition is dirty.
'Good',' good','GOOD'all fail= 'good'. I'd checkSELECT DISTINCT condition FROM copiesfirst and normalise withlower(trim(condition))if the column isn't an enum. - "Not returned" vs "overdue". If the interviewer means overdue, the predicate is
returned_at IS NULL AND due_at < CURRENT_DATE, a different number. I state which one I'm answering.
Performance. The join wants a partial index on the open rows: CREATE INDEX ON checkouts (copy_id) WHERE returned_at IS NULL. At Meta scale that index is a fraction of the table, and the query becomes an index scan plus a hash join against copies filtered on condition: no full scan of the checkout history, which is the table that grows without bound.
Curated: · Written: · Reviewed:
Q&A-24Using application crash logs, how would you compute the daily average crash rate grouped by country and client version?
Reported in Meta Data Engineer Interview Questions & Guide 2026 | Dataford
The 90-second answer
Crash logs give you the numerator only. A crash rate is crashes ÷ sessions, so before writing anything I check that a session (or app-launch) table exists at the same grain. If it doesn't, the question as asked can't be answered: I'd say so and offer crash-free-user rate off DAU instead, which is what most mobile teams actually act on.
Three definitions decide the query:
| Decision | Choice | Why |
|---|---|---|
| denominator | distinct sessions | launches over-count users who background and foreground constantly |
| day boundary | UTC on server received_at | device clocks are wrong on a slice of crash uploads |
| "daily average" | pooled: Σcrashes / Σsessions | mean-of-daily-rates is dominated by low-traffic days |
sql WITH sessions AS ( SELECT CAST(date_trunc('day', session_start) AS date) AS ds, country, version, count(DISTINCT session_id) AS sessions FROM app_sessions WHERE session_start >= TIMESTAMP '2026-01-01 00:00:00' AND session_start < TIMESTAMP '2026-02-01 00:00:00' GROUP BY 1, 2, 3 ), crashes AS ( SELECT CAST(date_trunc('day', received_at) AS date) AS ds, country, version, count(DISTINCT crash_id) AS n_crashes FROM crash_events WHERE received_at >= TIMESTAMP '2026-01-01 00:00:00' AND received_at < TIMESTAMP '2026-02-01 00:00:00' GROUP BY 1, 2, 3 ) SELECT s.country, s.version, sum(coalesce(c.n_crashes, 0)) AS crashes, sum(s.sessions) AS sessions, 1.0 * sum(coalesce(c.n_crashes, 0)) / sum(s.sessions) AS pooled_rate, avg(1.0 * coalesce(c.n_crashes, 0) / s.sessions) AS avg_daily_rate FROM sessions s LEFT JOIN crashes c USING (ds, country, version) GROUP BY 1, 2 HAVING sum(s.sessions) >= 10000;
Sessions drive the join, not crashes. That spine is the whole point, and the arithmetic shows why. Take BR / 12.3: Monday 40 crashes over 20,000 sessions = 0.20%; Tuesday 10 over 2,000 = 0.50%. Pooled is 50/22,000 = 0.227%. The mean of the two daily rates is 0.35%: 54% higher, entirely because Tuesday is small. An inner join fails the other way: it silently drops days that had sessions but no crashes, which are the good days, and inflates the average.
Failure modes I'd name before shipping it:
- Duplicate uploads. Crash reports are queued and retried on next launch, so the same crash lands two or three times.
count(DISTINCT crash_id)handles it; where the id is client-generated and unreliable, dedupe on(device_id, session_id)and cap at one crash per session: a crash loop otherwise reports dozens of crashes for one session and pushes the rate past 100%. - Missing country or version on the crash row. A crash during startup can precede locale resolution. Take country and version from the session row rather than the crash payload, and keep
unknownas its own bucket instead of dropping it. - Version grain.
12.3.1and12.3.1.4821are the same release; normalise to build number through a version dimension, or the rate splits across rows that each look small. - Small denominators. 3 sessions and 1 crash is 33%. The
HAVINGfilter is the blunt fix; Wilson intervals or an empirical-Bayes prior over the parent version is the honest one.
If the real ask is "is this release worse than the last one", I'd compare crash-free-session rate week over week per version. Same data, and it's the number the release owner will act on.
Curated: · Written: · Reviewed:
Q&A-25Write a function that takes two strings, compares them word-by-word, and returns a list of words that do not match, maintaining case sensitivity.
Reported in Meta Data Engineer Interview Questions & Guide 2026 | Dataford
Three things decide this answer, so I'd pin them down out loud before typing: does "word-by-word" mean positional (index i against index i), what separator defines a word, and do mismatches come from both strings or only the first. I'll assume positional, whitespace-delimited, and return both sides of each mismatched pair with the first string's word first: saying that in ten seconds lets the interviewer redirect me before I've written the function rather than after.
python from itertools import zip_longest
def mismatched_words(a: str, b: str) -> list[str]: """Positional, case-sensitive word comparison; returns both sides of each mismatch.""" wa, wb = a.split(), b.split() out: list[str] = [] for x, y in zip_longest(wa, wb, fillvalue=None): if x != y: out.extend(w for w in (x, y) if w is not None) return out
Checked against Python 3.12. str.split() with no argument collapses runs of whitespace and strips the ends, so "a b" and "a b" compare equal: usually what you want, and worth stating because it's a silent decision. zip_longest handles unequal lengths without a separate branch; the fillvalue=None sentinel is safe because split() never yields None.
Trace, a = "The quick Brown fox", b = "The slow Brown fox jumps":
| i | a[i] | b[i] | equal | out after |
|---|---|---|---|---|
| 0 | The | The | yes | [] |
| 1 | quick | slow | no | ['quick', 'slow'] |
| 2 | Brown | Brown | yes | unchanged |
| 3 | fox | fox | yes | unchanged |
| 4 | — | jumps | no | ['quick', 'slow', 'jumps'] |
Time is O(n + m) over the two token counts, output space O(k) for k mismatched words, plus O(n + m) for the two split lists. Case sensitivity is explicit: no .lower() anywhere, so "Brown" vs "brown" is a mismatch, which is exactly what was asked.
If the interviewer meant order-insensitive comparison, "which words appear in one string but not the other", positional code is the wrong tool, and I'd switch to a multiset difference:
python from collections import Counter
def unmatched_multiset(a: str, b: str) -> list[str]: ca, cb = Counter(a.split()), Counter(b.split()) return list((ca - cb).elements()) + list((cb - ca).elements())
Counter.__sub__ drops non-positive counts, so duplicates are handled: "the the cat" vs "the cat" returns ["the"], where a set difference would return [].
The failure mode that actually bites is positional drift. Insert one word at the front of b and every subsequent position shifts, so a 200-word document returns 199 false mismatches from a one-word edit. If the inputs are documents rather than short fixed-format records, I'd align with difflib.SequenceMatcher over the word lists and report only the replace/delete/insert opcodes: worst case O(n·m), near-linear on similar strings. Second failure mode is Unicode: "café" in NFC and NFD are different byte sequences and compare unequal, so if the strings come from different sources I'd unicodedata.normalize("NFC", s) both before splitting. Tests I'd write on the spot: both empty → []; one empty → every word of the other; identical strings → []; a case-only difference → both words returned.
Curated: · Written: · Reviewed:
Curated: · Written: · Reviewed: