Audit Dimension
An audit dimension records the processing context associated with a fact row, such as the ETL code version, source system, and data quality outcomes.
The fact table stores audit keys alongside its other foreign keys. For facts that can be updated, using separate creation and update audit keys preserves both contexts.
Batch Operations
The fact table stores the created_etl_run_id and updated_etl_run_id directly. This lets you identify rows by their creation or most recent update without joining the audit dimension. Both columns are initially set to the first run ID, and any later updates change only the updated_etl_run_id.
These columns make batch selection and reprocessing straightforward. However, because they only track the most recent update, rolling back a specific change requires saved previous values or rebuilding the affected facts from your source history.
Fact Table:
| transaction_id | date_key | amount | created_etl_run_id | updated_etl_run_id | created_audit_key | updated_audit_key |
|---|---|---|---|---|---|---|
| TXN-123 | 20260101 | $100 | RUN-8841 | RUN-8841 | AUD-5 | AUD-5 |
| TXN-124 | 20260101 | $250 | RUN-8841 | RUN-8902 | AUD-5 | AUD-9 |
| TXN-125 | 20260101 | $80 | RUN-8841 | RUN-8841 | AUD-6 | AUD-6 |
Audit Dimension:
| audit_key | etl_run_id | etl_version | load_timestamp | source_system | quality_flag |
|---|---|---|---|---|---|
| AUD-5 | RUN-8841 | v1.4.2 | 2026-01-01 02:00:00 | POS | Clean |
| AUD-6 | RUN-8841 | v1.4.2 | 2026-01-01 02:00:00 | POS | Currency defaulted |
| AUD-9 | RUN-8902 | v1.4.3 | 2026-01-02 02:00:00 | POS | Clean |
Grain
Instead of a separate row for every fact, the audit dimension holds one row for each unique set of audit details. For example, a single process that loads a million clean rows will generate just one audit row shared by all of them. A new dimension row is created only when an attribute changes, such as a different run, source system, or quality outcome.
In the example above, AUD-5 and AUD-6 belong to the same initial run, but AUD-6 exists because the quality flag differs. AUD-9 was created later when a new run updated an existing transaction.
Creating a unique audit row for every fact row would make the dimension table as large as the fact table itself.
What It Enables
Since this acts as a standard dimension table, you can easily group and filter quality metadata. You can break down total sales by the quality_flag to see how much revenue relies on defaulted values. You can also trace a disputed number back to the exact run that produced it or identify exactly which rows were written by a buggy code version.
Keep the flags coarse
Your quality_flag should use a small set of broad categories like Clean or Currency defaulted. Avoid highly specific values like exact percentages. Having too many unique values causes the dimension table to grow out of control. If you need numeric scores, group them into a few distinct buckets.