Spec the nightly build of an order facts table

Each night your team builds analytics.order_lines, the table other teams use for revenue. It joins 3 sources: orders from the shop's Postgres, refunds from the payment provider's export, and daily currency rates. An agent will write the Python pipeline that builds it in BigQuery. Finance has already found an order counted twice, and refunds that never showed up.

Write the spec you would hand the agent.

What you know:

• orders can be edited for 14 days after purchase, and the export repeats every version of an edited order, each with an updated_at
• refunds arrive up to 30 days after the order
• currency rates come as one row per currency per day
• the table is partitioned by order date, and reports read the last 13 months

Then, in a few sentences: what does "join these sources and load the table" get wrong?