Snowflake's Approach to Data Cleansing and Quality
Instruction: Discuss how you would use Snowflake to ensure high data quality and perform data cleansing.
Updated
Official answer
Define quality at the dataset's grain: required fields, valid ranges, uniqueness, relationships, freshness and reconciled business totals. For order lines, a non-null order_id is insufficient if the same order_id/line_id occurs twice.
Preserve raw input and source identity before cleansing. Parse into typed fields, distinguish a missing value from an invalid value, and record rejected rows with a reason. A tolerant conversion that returns NULL must not silently turn malformed amounts into acceptable missing values.
Make normalization explicit. Standardize units, timezones and identifiers from an agreed contract; do not guess ambiguous dates or erase leading zeros from identifiers. Deduplicate with a deterministic source/version rule and quarantine conflicting duplicates when there is no justified winner.
Run SQL assertions or supported data metric functions with expectations and alerting. A DMF measures an attribute; an expectation defines whether its value is acceptable. Scheduled checks detect issues but do not automatically repair the data. Check feature availability and cost.
Validate transformations on a restricted test target, then compare source/target counts, sums, rejection rates and freshness. Standard-table primary/foreign/unique declarations do not enforce those relationships, so test them separately. Time Travel can support eligible recovery but is not a complete cleansing audit log.
Assign quality failures to owners and retain a replay path. Measure completeness, duplicates, invalid values and reconciliation differences; daily active users is not a direct measure of dataset correctness.