Your question is Validate Power BI Revenue Before Publish. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |