Late data and backfills

Find what arrived late

The source has 1,212 order rows. Five arrive more than 30 days after the order date, and all five are more than 90 days late. Filtering only on ordered_at misses them because the order date is before the last date you checked, even though the system received the rows later.

Filter on _loaded_at to find new rows, then let the update setting replace the corresponding ordered_at date groups. A seven-day lookback is a mitigation, not a complete fix. A limited repair would use bruin run --start-date 2024-10-01 --end-date 2024-12-31 pipeline. bruin backfill can run the repair in smaller date ranges.

Your task

Use queries/ops/late-arrival-audit.sql, queries/ops/timezone-dst-audit.sql, and a monthly comparison to identify every moved 2024 month, its revenue delta, the five rows over 90 days, and the proposed backfill command. Compare each store's local ordered_at with ordered_at_utc around both synthetic seasonal edge windows and explain why UTC is the safe field for ordering events from different stores.

Record the findings in docs/late-data-findings.md without running the proposed backfill. Report stable UTC examples such as order 792 in Berlin and order 796 in New York, using each row's reported timezone rather than claiming one transition date for every region.

Check your understanding

• Why does filtering only on ordered_at >= last_run miss a late row?
• How many source order rows are more than 90 days late?
• Is a seven-day lookback a complete fix?

Do it with your agent

Say next lesson, run the audits, write the findings, then say review my work.

Rubric

• Reports exactly 5 order rows with delay greater than 90 days.
• Reports exactly 5 rows over 30 days and lists the moved months and deltas: 2024-10 = 10,681.87, 2024-11 = 12,493.10, and 2024-12 = 6,030.35.
• Explains using _loaded_at to find new rows, using ordered_at to replace date groups, and gives both date flags for the proposed backfill.
• Reports evidence from both seasonal edge windows, names the store timezone, and explains local-time versus UTC without claiming one transition date for every region.