Roadmap › ETL & Data Warehousing Concepts › Module: Building Reliable Pipelines
Lessons in this course (12)
10. Data quality
Intermediate★ Must knowLesson 10 of 12
Dimensions of data quality: accuracy, completeness, consistency, timeliness, validity, uniqueness.
Checks to automate:
- Not-null and allowed-value checks on key columns.
- Referential integrity: every fact has a matching dimension member (use an "Unknown" member, key −1, for late or missing dimensions — late-arriving dimensions).
- Duplicate business keys.
- Range checks (no negative quantities, dates not in the future).
- Row count / total amount reconciliation against the source.
- Freshness (data loaded within SLA).
sql
-- Unknown member pattern
INSERT INTO dw.DimCustomer (CustomerKey, CustomerId, Name, City, ValidFrom, ValidTo, IsCurrent)
VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown', '1900-01-01', '9999-12-31', 1); -- with IDENTITY_INSERT ON