Business Context
You’re a data engineer supporting a fintech marketplace (≈2M monthly active users, ~$500M annual GMV). A Power BI dashboard used by Finance shows daily net revenue and is about to be published to executives. In the past, the team has shipped dashboards with subtle issues (double-counted refunds, missing FX conversion, and duplicated order lines), causing incorrect revenue reporting and rework.
The Power BI model is built from a curated table bi_daily_revenue produced by an ELT job. The source-of-truth lives in normalized operational tables: orders, order_items, and payments. Before publishing, you want to validate that the BI table matches the source-of-truth for a given date range.
Task
Write a SQL query that produces a day-level validation report for 2024-01-01 through 2024-01-03 comparing the Power BI table to the source-of-truth calculation.
Requirements
Your query must:
- Compute source-of-truth net revenue in USD per day as:
gross_usd = SUM(item_amount * fx_rate_to_usd) for all order items on that dayrefunds_usd = SUM(refund_amount * fx_rate_to_usd) for payments with status = 'REFUNDED' on that daynet_usd = gross_usd - refunds_usd
- Avoid double counting due to multiple order items by aggregating items at the order level before joining to payments.
- Compare the source-of-truth to
bi_daily_revenue.net_revenue_usd and output:
revenue_date, bi_net_usd, source_net_usd, diff_usd, diff_pct
- Include a data quality signal column
validation_status with values:
OK if ABS(diff_usd) <= 1.00MISMATCH otherwise
- Return one row per date in the range, ordered by
revenue_date.
Table Definitions
orders
| column | type | description |
|---|
| order_id | INT | Primary key |
| user_id | INT | Customer placing the order |
| order_created_at | TIMESTAMP | Order creation timestamp |
| currency | VARCHAR(3) | Order currency (e.g., USD, EUR) |
order_items
| column | type | description |
|---|
| order_item_id | INT | Primary key |
| order_id | INT | FK to orders.order_id |
| item_amount | DECIMAL(10,2) | Line item amount in order currency |
payments
| column | type | description |
|---|
| payment_id | INT | Primary key |
| order_id | INT | FK to orders.order_id |
| status | VARCHAR(20) | CAPTURED or REFUNDED |
| payment_created_at | TIMESTAMP | Payment event timestamp |
| amount | DECIMAL(10,2) | Payment amount in order currency |
| refund_amount | DECIMAL(10,2) | Refund amount in order currency (0 if none) |
| fx_rate_to_usd | DECIMAL(12,6) | FX rate at payment time to convert to USD |
bi_daily_revenue
| column | type | description |
|---|
| revenue_date | DATE | Day (UTC) |
| net_revenue_usd | DECIMAL(12,2) | Curated net revenue used by Power BI |
| refreshed_at | TIMESTAMP | When the BI table was last refreshed |
Sample Data
orders
| order_id | user_id | order_created_at | currency |
|---|
| 101 | 1 | 2024-01-01 10:05:00 | USD |
| 102 | 2 | 2024-01-01 12:20:00 | EUR |
| 103 | 3 | 2024-01-02 09:00:00 | USD |
| 104 | 4 | 2024-01-03 14:10:00 | USD |
order_items
| order_item_id | order_id | item_amount |
|---|
| 1001 | 101 | 60.00 |
| 1002 | 101 | 40.00 |
| 1003 | 102 | 50.00 |
| 1004 | 103 | 120.00 |
| 1005 | 104 | 80.00 |
payments
| payment_id | order_id | status | payment_created_at | amount | refund_amount | fx_rate_to_usd |
|---|
| 5001 | 101 | CAPTURED | 2024-01-01 10:06:00 | 100.00 | 0.00 | 1.000000 |
| 5002 | 102 | CAPTURED | 2024-01-01 12:21:00 | 50.00 | 0.00 | 1.100000 |
| 5003 | 103 | CAPTURED | 2024-01-02 09:01:00 | 120.00 | 0.00 | 1.000000 |
| 5004 | 103 | REFUNDED | 2024-01-02 18:30:00 | 0.00 | 20.00 | 1.000000 |
| 5005 | 104 | CAPTURED | 2024-01-03 14:11:00 | 80.00 | 0.00 | 1.000000 |
bi_daily_revenue
| revenue_date | net_revenue_usd | refreshed_at |
|---|
| 2024-01-01 | 155.00 | 2024-01-04 02:00:00 |
| 2024-01-02 | 90.00 | 2024-01-04 02:00:00 |
| 2024-01-03 | 80.00 | 2024-01-04 02:00:00 |
Expected Output (from sample data)
| revenue_date | bi_net_usd | source_net_usd | diff_usd | diff_pct | validation_status |
|---|
| 2024-01-01 | 155.00 | 155.00 | 0.00 | 0.0000 | OK |
| 2024-01-02 | 90.00 | 100.00 | -10.00 | -0.1000 | MISMATCH |
| 2024-01-03 | 80.00 | 80.00 | 0.00 | 0.0000 | OK |
