Error Event Schemas
Managing data quality in a data warehouse requires a comprehensive system of data quality screens or filters that test the data as it flows from the source systems to the Business Intelligence (BI) platform.
When a data quality screen detects an error (such as a missing customer ID, an invalid date of birth, or an improperly formatted email), the data should not simply be discarded or silently printed to a text log.
Instead, this event is recorded in a special dimensional schema that is available in the ETL back room. This enables the analysis of data quality problems using the exact same dimensional techniques used to analyze business performance.
Error Event Fact Table
This schema consists of an error event fact table whose grain is one screen execution against one source record that failed.
Because a single validation screen might detect multiple issues on a single record, the specific error_type is omitted from this table. Instead, this table identifies the record examined, the screen that flagged it, and the severity of the failure (e.g., Fatal, Warning, Informational).
| error_event_key | date_key | source_system | target_table | source_record_id | screen_key | severity |
|---|---|---|---|---|---|---|
| 1 | Jan 13 | CRM | Customer | CRM-88201 | SCR-VAL-01 | Warning |
| 2 | Jan 13 | CRM | Customer | CRM-88347 | SCR-VAL-01 | Fatal |
Including a severity attribute is critical; it turns a massive log of everything that went wrong into an actionable system that can be triaged, making questions like "Are severe errors increasing?" much more useful.
By modeling errors this way, management can use standard BI tools to ask questions like:
- Which source system generates the most errors?
- Which tables have the most errors?
- Are fatal errors increasing over time?
Error Event Detail Fact Table
To drill down further, an associated error event detail fact table can be created. Its grain is much finer: one row per column that failed within that event.
For example, if Error Event 2 from the table above involved a single source record that had both a missing DOB and an invalid email, the detail table would record two rows hanging off that same event:
| error_event_key | target_table | column_name | error_reason |
|---|---|---|---|
| 2 | Customer | DOB | Missing |
| 2 | Customer | Invalid |
This allows the ETL team to pinpoint exactly which fields are failing data quality screens the most frequently.
Downstream Impact and Audit Dimensions
Recording the error in an event schema is only half the decision; the ETL pipeline must also define what happens to the data itself. The row must either be rejected, halted, or passed through with default values (such as an "Unknown" placeholder key).
If flawed data is permitted to pass through into the data warehouse, this must be explicitly signaled to downstream users. This is typically achieved by attaching an Audit Dimension to the resulting fact row. The audit dimension's data quality flag is what tells a fact-table user that a screen fired on their data, ensuring the error event schema does not become an isolated log that nobody reads.