Debugging a Silent Data Pipeline Failure

A nightly ETL job at DataForge is producing silently wrong output — no crash, just bad numbers downstream. The manager must trace buggy pseudocode by hand, line by line, before proposing a fix.

# Aplly.xyz Case Study Submission

## Title
Debugging a Silent Data Pipeline Failure

## Type
Technology/IT

## Difficulty
Advanced

## Estimated Time
60 minutes

## Overview
A nightly ETL job at DataForge is producing silently wrong output — no crash, just bad numbers downstream. The manager must trace buggy pseudocode by hand, line by line, before proposing a fix.

## Case Details

Function Focus: Manual code/logic tracing, hypothesis-driven debugging

Scenario:
Revenue reports have been off by roughly 8% for the past week, with no errors appearing in the application logs. The suspected culprit is the currency-conversion step of the nightly ETL job, which joins order data against a cached exchange-rate table. The manager has the pseudocode, three sample input rows, and the actual-vs-expected output for those rows.

Dataset Structure:
- Pseudocode block (~25 lines): currency-conversion + join logic against a cached exchange-rate table
- Sample input rows: order_id, currency, amount, order_timestamp
- Output comparison: order_id, expected_usd, actual_usd

Tasks:
1. Trace execution of the pseudocode by hand against all 3 sample input rows — do not paste the code into an LLM for this step
2. Identify the exact line causing the discrepancy
3. Explain why the bug fails silently instead of throwing a visible error
4. Propose a fix and write one regression test case that would have caught this bug before it reached production

Expected Output:
An annotated trace table (one row per input, showing intermediate variable state at each pseudocode line), the identified bug location, a proposed fix, and a regression test case.

Evaluation Criteria:
Correct bug identification arrived at via a genuine manual trace (not a guess), and the quality/coverage of the proposed regression test.

## Data Sources

Pseudocode (currency conversion step):
```
1. function convert_order_to_usd(order, fx_cache):
2. currency = order.currency
3. amount = order.amount
4. timestamp = order.order_timestamp
5.
6. if currency == "USD":
7. return amount
8.
9. rate_entry = fx_cache.lookup(currency)
10. // fx_cache is refreshed daily at 00:00 UTC
11. // BUT: lookup() returns the FIRST matching entry, not the
12. // most recent one, when duplicate currency codes exist
13. rate = rate_entry.rate
14.
15. usd_amount = amount * rate
16.
17. if usd_amount < 0:
18. log_warning("negative amount")
19. usd_amount = 0
20.
21. return round(usd_amount, 2)
22.
23. // fx_cache contains BOTH a stale entry (loaded 3 days ago,
24. // failed silent refresh) and a fresh entry for EUR, in that order
```

Sample Input Rows:

| order_id | currency | amount | order_timestamp |
|---|---|---|---|
| ORD-1001 | EUR | 500.00 | 2026-07-10 09:15 |
| ORD-1002 | USD | 300.00 | 2026-07-10 10:02 |
| ORD-1003 | EUR | 1,200.00 | 2026-07-10 14:47 |

Actual vs. Expected Output:

| order_id | expected_usd | actual_usd |
|---|---|---|
| ORD-1001 | 542.50 | 520.00 |
| ORD-1002 | 300.00 | 300.00 |
| ORD-1003 | 1,302.00 | 1,248.00 |

(Stale EUR rate = 1.04, fresh EUR rate = 1.085 — the pipeline is silently using the stale rate because `lookup()` returns the first match, not the latest.)

## Solution Frameworks
Manual trace tables, hypothesis-elimination debugging

## Solver Guidance & Tutorials
Link to: "Tracing Code by Hand: A Lost Debugging Skill" tutorial

## What You'll Learn
- Systematic debugging without tool-assisted code reading
- Reasoning about silent failures (bugs that don't throw errors)
- Writing regression tests that target root cause, not symptoms

## Tags
debugging, ETL, data pipelines, algorithmic tracing, technology/IT

## Registration Links
- Register as Solver
- Register as Evaluator

Pseudocode (currency conversion step):
```
1. function convert_order_to_usd(order, fx_cache):
2. currency = order.currency
3. amount = order.amount
4. timestamp = order.order_timestamp
5.
6. if currency == "USD":
7. return amount
8.
9. rate_entry = fx_cache.lookup(currency)
10. // fx_cache is refreshed daily at 00:00 UTC
11. // BUT: lookup() returns the FIRST matching entry, not the
12. // most recent one, when duplicate currency codes exist
13. rate = rate_entry.rate
14.
15. usd_amount = amount * rate
16.
17. if usd_amount < 0:
18. log_warning("negative amount")
19. usd_amount = 0
20.
21. return round(usd_amount, 2)
22.
23. // fx_cache contains BOTH a stale entry (loaded 3 days ago,
24. // failed silent refresh) and a fresh entry for EUR, in that order
```

Sample Input Rows:

| order_id | currency | amount | order_timestamp |
|---|---|---|---|
| ORD-1001 | EUR | 500.00 | 2026-07-10 09:15 |
| ORD-1002 | USD | 300.00 | 2026-07-10 10:02 |
| ORD-1003 | EUR | 1,200.00 | 2026-07-10 14:47 |

Actual vs. Expected Output:

| order_id | expected_usd | actual_usd |
|---|---|---|
| ORD-1001 | 542.50 | 520.00 |
| ORD-1002 | 300.00 | 300.00 |
| ORD-1003 | 1,302.00 | 1,248.00 |

(Stale EUR rate = 1.04, fresh EUR rate = 1.085 — the pipeline is silently using the stale rate because `lookup()` returns the first match, not the latest.)

Manual trace tables, hypothesis-elimination debugging