Cloud Data Quality R41 Professional cheat sheet

Cloud Data Quality

• Data quality dimensions: completeness (non-null ratio), accuracy (match to source of truth), consistency (cross-system agreement), timeliness (freshness vs. SLA), uniqueness (duplicate rate), validity (conformance to domain/regex/range).

• Null handling: COUNT() counts rows; COUNT(col) excludes NULLs; AVG ignores NULLs. Use COUNT(col)/COUNT() for completeness. COALESCE(col, default) for imputation; never use = NULL, use IS NULL.

• Duplicate detection: exact duplicates via GROUP BY key HAVING COUNT(*) > 1; fuzzy duplicates via normalized keys (TRIM, LOWER, remove punctuation) or edit distance/Levenshtein threshold.

• Referential integrity check: LEFT JOIN child to parent and filter WHERE parent.key IS NULL to find orphan records; also check parent-to-child for unused parents when required.

• Freshness metric: MAX(ingest_timestamp) vs. current_timestamp; alert when lag exceeds SLA. Late-arriving data handled by watermark or reprocessing window.

• Schema drift: compare INFORMATION_SCHEMA.COLUMNS before/after load; flag added, dropped, or type-changed columns. Enforce contract with expected schema registry.

• Profiling statistics per column: null count, distinct count, min/max, mean/stddev, top-N frequencies, pattern frequency. Baseline these to detect anomalies.

• Cloud cost control: partition pruning on date columns, cluster on high-cardinality filters, avoid SELECT *, and use approximate functions (APPROX_COUNT_DISTINCT) for large-scale profiling.

• Data quality rule lifecycle: define rule (SQL assertion), schedule (batch/stream), score (pass/fail count), route failures to quarantine table, and track trend in a quality dashboard.