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.
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.
Your query must:
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_usdbi_daily_revenue.net_revenue_usd and output:
revenue_date, bi_net_usd, source_net_usd, diff_usd, diff_pctvalidation_status with values:
OK if ABS(diff_usd) <= 1.00MISMATCH otherwiserevenue_date.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| user_id | INT | Customer identifier |
| order_created_at | TIMESTAMP | Order creation timestamp (UTC) |
| currency | VARCHAR(3) | Order currency code |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Unique order line identifier |
| order_id | INT | References orders.order_id |
| item_amount | DECIMAL(10,2) | Line item amount in order currency |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment event identifier |
| order_id | INT | References orders.order_id |
| status | VARCHAR(20) | Payment event status (e.g., CAPTURED, REFUNDED) |
| payment_created_at | TIMESTAMP | Timestamp of payment event (UTC) |
| 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 to convert from order currency to USD at event time |
| Column | Type | Description |
|---|---|---|
| revenue_datePK | DATE | Revenue date (UTC) |
| net_revenue_usd | DECIMAL(12,2) | Curated net revenue in USD used by Power BI |
| refreshed_at | TIMESTAMP | Timestamp when the BI aggregate was last refreshed |