Cloud Data Quality R41 Professional flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Completeness dimension

Measures the proportion of non-null values in a required field. Compute as COUNT(column)/COUNT(*) or 1 minus null ratio; compare against a threshold to flag incomplete records.

Accuracy dimension

Degree to which a value correctly reflects the real-world entity or authoritative source. Often validated by matching against a golden record or reference dataset and measuring mismatch rate.

Consistency dimension

Agreement of the same data element across systems or tables. Check by joining on key and comparing values; count rows where values differ to quantify inconsistency.

Timeliness dimension

Whether data is available within the required time window. Measure as current time minus latest event or ingest timestamp; compare to SLA and alert on breach.

Uniqueness dimension

Absence of unintended duplicate records. Detect exact duplicates with GROUP BY key HAVING COUNT(*) > 1; compute duplicate rate as duplicate rows divided by total rows.

Validity dimension

Conformance of values to defined formats, domains, or ranges. Implement with regex, enumeration lists, or range checks; count violations and route to quarantine.

Null ratio formula

Null ratio equals COUNT(*) minus COUNT(column), divided by COUNT(*). Because COUNT(column) ignores NULLs, this gives the fraction of missing values in that column.

COUNT(*) versus COUNT(column)

COUNT(*) counts all rows including those with NULLs; COUNT(column) counts only non-null values. The difference is the null count, used in completeness calculations.

COALESCE for imputation

COALESCE(column, default) returns the first non-null argument. Use it to replace NULLs with a default or derived value during transformation, but document imputation rules for auditability.

IS NULL predicate

SQL comparison to NULL using = NULL yields unknown, not true. Always use IS NULL or IS NOT NULL to filter null values in data quality checks.

Exact duplicate detection

Group rows by the business key and filter HAVING COUNT(*) > 1. The resulting groups identify keys with more than one record, which are exact duplicates on that key.

Fuzzy duplicate detection

Normalize values (trim, lowercase, remove punctuation) then compare using edit distance or similarity thresholds. Used when keys are absent and records may differ slightly.

Orphan record check

Left join child table to parent on foreign key and filter WHERE parent.key IS NULL. Rows returned are child records lacking a valid parent, violating referential integrity.

Referential integrity

Every foreign key value in a child table must exist in the parent table. Validate with anti-join queries; failures indicate orphaned or mis-keyed records.

Freshness lag metric

Current timestamp minus MAX(ingest_timestamp) or MAX(event_timestamp). Compare to SLA; a positive lag above threshold signals stale data and should trigger an alert.

Late-arriving data

Records that arrive after the expected processing window. Handle with watermarks, reprocessing windows, or idempotent upserts so aggregates remain correct.

Schema drift detection

Compare current table schema in INFORMATION_SCHEMA.COLUMNS to an expected contract. Flag added, dropped, renamed, or type-changed columns before they break downstream consumers.

Column profiling statistics

Per-column metrics: null count, distinct count, min, max, mean, standard deviation, top-N frequencies, and pattern frequencies. Baselines enable anomaly detection over time.

Partition pruning

Restricting a query to relevant partitions using a filter on the partition column, typically a date. Reduces scanned data and cost in cloud warehouses.

Clustering columns

Physically co-locating rows with similar values on high-cardinality filter columns. Improves scan efficiency for selective queries beyond what partitioning alone provides.

APPROX_COUNT_DISTINCT

Returns an approximate number of distinct values using a probabilistic sketch. Much cheaper than exact COUNT(DISTINCT) on large datasets; acceptable for profiling and monitoring.

Quarantine table

A separate table where records failing data quality rules are written. Preserves bad rows for investigation while allowing clean data to proceed downstream.

Data quality rule assertion

A SQL expression expected to return zero failing rows, such as SELECT COUNT(*) FROM t WHERE key IS NULL. Scheduled execution produces pass/fail scores.

Data quality score

A numeric summary of rule outcomes, often passing rows divided by total rows or rules passed divided by rules run. Tracked over time to show trend and regressions.

Golden record

The authoritative, curated version of an entity used as the reference for accuracy checks. Other records are compared to it to measure mismatch rates.

Data contract

An agreed specification of schema, semantics, and quality expectations between data producers and consumers. Enforced by validation at ingestion and schema comparison.

Pattern frequency analysis

Counting how often each value pattern (for example regex class or format template) occurs in a column. Sudden new patterns often indicate format drift or bad input.

Range check rule

Asserts that numeric or date values fall within expected minimum and maximum bounds. Violations counted as rows outside the range indicate validity failures.

Enumeration domain check

Validates that a column's values belong to an allowed list. Implement with IN or a lookup join; values outside the list are flagged as invalid.

SELECT * avoidance

Listing only needed columns reduces scanned data and cost in columnar cloud warehouses. Especially important in profiling and monitoring queries run frequently.

Idempotent upsert

A write that can be rerun without creating duplicates, typically MERGE or INSERT with key-based deduplication. Essential for reprocessing late or corrected data safely.

Watermark

A threshold marking how far event time has progressed, used to decide when to finalize windows. Data arriving before the watermark is processed; later data triggers reprocessing.