A practice prompt we wrote. No company or candidate report names it, so it carries no company tag.

How to answer

The set arithmetic is easy; wrong diffs come from how rows are matched. Say first: “I’ll match rows by key, not position, and compare values after normalizing them.”

  1. Ask for the key, then check it. Which columns identify a row? Normalize it before matching: trim, fix case, pad leading zeros, strip a trailing .0. Report duplicate keys in both files separately. With no key, report rows found in one file only, as a multiset; “changed” means nothing.
  2. Diff the schema first. Columns only in one file, and likely renames. Match columns by header name, never position, and compare shared ones only, so a new column doesn’t mark every row changed.
  3. Normalize values per column, out loud. Trim, parse amounts into Decimal (not float), parse dates with the timezone settled, and decide if empty equals null. Exclude volatile columns, like an export timestamp, by name.
  4. Compute three sets and the field changes. Added is new keys minus old, removed the reverse; for shared keys, list each differing column with both values. Beyond memory, sort both by key and walk them together, or full-outer-join in SQLite or DuckDB (before SQLite 3.39.0, a LEFT JOIN each way with UNION ALL). Compare with IS NOT in SQLite or IS DISTINCT FROM in DuckDB: <> is NULL when either side is, hiding every change to or from NULL.
  5. Write the report for a person. Counts first, then changes by column. A column changed on nearly every row is systemic, like a rounding change, and goes at the top. So do removals and additions that pair up once keys are normalized: the key format changed. End with a check on distinct keys: old, minus removed, plus added, equals new, with duplicate-key rows listed beside it.

The trap is a line diff: reorder the export and every row looks changed.

Follow-ups

What the interviewer may ask next, once your first answer is on the table.

  • The table has no primary key. What can you still report, and what can’t you?
  • Both files are larger than memory. How does your approach change?
  • Every row shows updated_at as changed. What do you do with that column?
  • The new export renamed a column and added another. How does your report show that?
  • The same key appears twice in the new file. What does your diff say about it?

Where answers go wrong

  • Comparing line by line, like diff, so a reordered export reports every row as removed and added again.
  • Comparing raw strings, so a price with a trailing zero, or a trailing space, floods the report with changes nobody made.
  • Assuming the key is unique without checking, then silently keeping whichever duplicate was read last.
  • Reporting only a count of changed rows, with no field-level detail, so the reader cannot tell a real edit from a formatting change.

Answer this in two minutes

Write the answer you would say out loud. The clock starts with your first word.

Two minutes

Model answer

“First, the key. You said sku identifies a row in this product table, so I’ll match on that and check it’s unique in both files rather than assume so.

Learn this in Module 5: Practical coding for FDE rounds, part of the curriculum.