Order Analytics Model

Problem

The source order tables kept full history as slowly changing dimensions, so a naive row count materially overstated order volume. The BI layer could not express the window-function deduplication needed to correct it, and exact distinct counts at this data volume pushed the query engine into out-of-memory failures.

Approach

1. 01Materialized an enriched table using ROW_NUMBER() deduplication on the documented natural keys for orders and order items.
2. 02Joined reference exchange rates for as-of currency conversion, with a fallback for dates with no published rate, such as weekends and holidays.
3. 03Scheduled hourly refreshes through Airflow and exposed the result as a governed BI dataset.
4. 04Documented the known upstream gaps in refund and returns data rather than letting them silently skew the numbers.

Outcome

Analysts can chart orders, units, and sales directly in the BI tool with counts that match reality, and without the query engine falling over.