Re-Engineer a Legacy ETL

Start from brute force

The obvious approach patches each failing test where it fails: add "pending" to the exclude-list, dedupe on the whole row instead of order_id, and strip zero totals at the end. Each patch is one line, the function stays O(n) time and O(n) space, and it passes every hidden test on this page. It is still wrong, because each patch encodes a symptom rather than the spec. A "returned" order still counts ({"acme": 30} instead of {}). Two legs of one order with different statuses count twice (200 instead of 100). And a qualifying order with amount 0 makes its customer vanish.

The approach

Check the inherited function against the spec one rule at a time; each defect maps to one line. Status: if r["status"] == "cancelled": continue is an exclude-list, so pending and any unknown status count. The fix is an include-list, if r["status"] not in ("shipped", "delivered"): continue. Dedupe key: key = r["order_id"] drops a second customer’s order that reuses the reference, so the key becomes (r["customer"], r["order_id"]), checked against seen before adding. Pre-seeding: the first loop, totals.setdefault(r["customer"], 0), puts every customer in the result before any filtering, so it goes, and totals[r["customer"]] = totals.get(r["customer"], 0) + r["amount"] creates a customer only when a qualifying row arrives.

The invariant: a customer is in totals exactly when at least one of their rows passed the status filter, and each surviving (customer, order_id) pair was added once. Fix one defect, rerun the tests, then the next.

Walk through an example

On these six rows the repaired function returns {"acme": 300, "globex": 40}, while the inherited code returns {"acme": 1299, "globex": 0, "initech": 0}, one wrong number per defect.

Complexity

The repaired function is one pass: O(n) time for n rows, with O(1) average set and dict operations, and O(n) space in the worst case for seen, plus O(c) for totals over c customers. The inherited and symptom-patched versions are also O(n) time and space. Nothing here is about speed, only about which rows count.

Edge cases to test

• An unknown status: a lone "returned" row gives {}. Every status outside the include-list is excluded, including ones no fixture shows.
• Two legs of one order with different statuses, "shipped" and "delivered": the order counts once, {"acme": 100}, because the key is (customer, order_id), not the whole row.
• A leg that fails the filter does not block one that passes: a "pending" leg is skipped before it reaches seen, so a later "shipped" leg of the same order still counts, giving {"acme": 100}.
• A qualifying order with amount 0 keeps its customer, {"acme": 0}. Absent means no qualifying orders, not a zero total.
• Status matching is exact: "Shipped" with a capital S is excluded. If upstream casing varies, normalize it on purpose rather than widening the list.
• Duplicate legs with different amounts: the first row wins, 100 rather than 120. The spec says duplicates share an amount, so a mismatch is a data-quality signal worth logging.

Common mistakes

• Rewriting from scratch instead of locating the defects — you lose the behavior that was already correct.
• Deduping on order_id alone: two customers can legitimately carry the same order reference from different upstream systems.
• Excluding only cancelled. The spec names the statuses that count; anything else — pending, returned, a typo — must not.
• Leaving customers with a zero total in the output. Downstream, a zero looks like a real reported value.

Follow-ups an interviewer asks next

The rows no longer fit in memory. What changes?

Push the filter, the dedupe and the sum into the database: filter on status, group by customer and order_id to collapse the legs, then group by customer and sum the amounts. The database streams the rows, and a customer with no qualifying rows never forms a group, so absence comes for free.

How would you show the repaired totals are right before the customer sees them?

Reconcile against an independent source, such as the billing system or an invoice export, and list every customer whose total moved after the fix. Each changed number should trace back to one of the three defects. A change that traces to none of them is a fourth bug or a misread spec, and it should be explained before anything ships.

What if a duplicated order’s legs disagree on the amount?

The spec says they share the amount, so the reference keeps the first and moves on. In production a mismatch means the join is wrong or the data is bad, so count those cases and surface them. Picking the first, the largest or the sum changes reported revenue, which makes it a decision for the customer, not a default buried in code.

How do you stop the next engineer from reintroducing these bugs?

Turn each defect into a named test with the smallest fixture that shows it, as R2 and R3 do here, and write the spec’s three rules in the docstring. Make the include-list a named constant, such as COUNTED_STATUSES, so adding a status becomes a deliberate one-line change.

Where this shows up

This is the shape of a genuine FDE first week: an inherited pipeline whose numbers are subtly wrong, nobody who wrote it is still there, and the customer has been reporting from it for a year. The Palantir re-engineering round and Sierra’s planted-bug debugging round both test this directly. The skill is method — reproduce, narrow, fix one thing, verify — not speed.

Reference solution

R1 = [                                    # provided fixtures
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o2", "amount": 200, "status": "delivered"},
]
R2 = [
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o3", "amount": 999, "status": "pending"},
    {"customer": "globex", "order_id": "o4", "amount": 50, "status": "cancelled"},
]
R3 = [                                        # same order ref, two customers
    {"customer": "acme", "order_id": "o9", "amount": 10, "status": "shipped"},
    {"customer": "globex", "order_id": "o9", "amount": 20, "status": "shipped"},
]


def summarise(rows):
    totals = {}
    seen = set()
    for r in rows:
        if r["status"] not in ("shipped", "delivered"):   # include-list, not exclude-list
            continue
        key = (r["customer"], r["order_id"])              # customer belongs in the key
        if key in seen:
            continue
        seen.add(key)
        totals[r["customer"]] = totals.get(r["customer"], 0) + r["amount"]
    return totals


print(summarise(R1))   # -> {'acme': 300}
print(summarise(R2))   # -> {'acme': 100}

const R1 = [                              // provided fixtures
  { customer: "acme", order_id: "o1", amount: 100, status: "shipped" },
  { customer: "acme", order_id: "o1", amount: 100, status: "shipped" },
  { customer: "acme", order_id: "o2", amount: 200, status: "delivered" },
];
const R2 = [
  { customer: "acme", order_id: "o1", amount: 100, status: "shipped" },
  { customer: "acme", order_id: "o3", amount: 999, status: "pending" },
  { customer: "globex", order_id: "o4", amount: 50, status: "cancelled" },
];
const R3 = [                                  // same order ref, two customers
  { customer: "acme", order_id: "o9", amount: 10, status: "shipped" },
  { customer: "globex", order_id: "o9", amount: 20, status: "shipped" },
];

function summarise(rows: any[]): Record<string, number> {
  const totals: Record<string, number> = {};
  const seen = new Set<string>();
  for (const r of rows) {
    if (r.status !== "shipped" && r.status !== "delivered") continue;   // include-list
    const key = r.customer + "|" + r.order_id;                          // customer in the key
    if (seen.has(key)) continue;
    seen.add(key);
    totals[r.customer] = (totals[r.customer] || 0) + r.amount;
  }
  return totals;
}

console.log(summarise(R1));   // -> { acme: 300 }
console.log(summarise(R2));   // -> { acme: 100 }