Your question is Validate Mock Customer Load Conversion. Start with the requirements and the two 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 at a fintech payments company that processes ~10M card transactions/day. A legacy customer profile system is being migrated into a new analytics warehouse used for credit risk monitoring and regulatory reporting. Before enabling the production cutover, the team runs a mock load (one-day snapshot) from the legacy system into the new dim_customers table.
Because downstream models and compliance dashboards depend on accurate customer identity and status fields, you must validate that the conversion logic (type casting, normalization, and status mapping) produced correct results. The goal is to produce a single SQL query that can be run after each mock load and reviewed by engineers and QA.
Write a SQL query that compares the legacy snapshot (legacy_customers_snapshot) to the newly loaded warehouse dimension (dim_customers) for a given load_date, and outputs reconciliation metrics and mismatch counts.
For load_date = '2026-02-15', your query must return one row with:
load_datelegacy_row_count and dim_row_countmissing_in_dim_count (present in legacy, absent in dim)extra_in_dim_count (present in dim, absent in legacy)email_mismatch_count (case-insensitive compare after trimming)status_mismatch_count (legacy status_code mapped to dim status)total_credit_limit_legacy and total_credit_limit_dim (sum comparison)Status mapping rules (conversion spec):
status_code = 'A' → dim status = 'active'status_code = 'S' → dim status = 'suspended'status_code = 'C' → dim status = 'closed'| Column | Type | Description |
|---|---|---|
| customer_idPK | BIGINT | Stable customer identifier from the legacy system |
| VARCHAR(255) | Customer email as stored in legacy (may contain whitespace/mixed case) | |
| status_code | CHAR(1) | Legacy status code: A (active), S (suspended), C (closed) |
| credit_limit_cents | BIGINT | Credit limit in cents in the legacy system |
| snapshot_datePK | DATE | Date of the legacy snapshot (partition key for mock load validation) |
| Column | Type | Description |
|---|---|---|
| customer_idPK | BIGINT | Customer identifier in the warehouse (should match legacy) |
| VARCHAR(255) | Normalized customer email stored in the warehouse | |
| status | VARCHAR(20) | Converted status value: active/suspended/closed |
| credit_limit_usd | DECIMAL(12,2) | Credit limit in USD in the warehouse |
| load_datePK | DATE | Date the record was loaded (partition key for the mock load) |