Browse cheat sheets · Data Engineering

Technology: Data EngineeringCategory: Data QualityDifficulty: IntermediateUpdated

Data Quality Checks

Build practical data quality checks for schema, completeness, uniqueness, validity, referential integrity, freshness, reconciliation, and pipeline drift without relying on arbitrary universal thresholds.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Validate required columns
  5. 2. Detect unexpected columns
  6. 3. Validate Spark data types
  7. 4. Check required non-null fields
  8. 5. Check null percentage
  9. 6. Avoid scanning the table repeatedly
  10. 7. Check business-key uniqueness
  11. 8. Validate composite-key uniqueness
  12. 9. Do not use dropDuplicates() as a quality check
  13. 10. Inspect duplicate examples
  14. 11. Validate row grain after joins
  15. 12. Validate numeric range
  16. 13. Validate bounded domain values
  17. 14. Validate currency codes
  18. 15. Validate string patterns
  19. 16. Check empty strings separately from nulls
  20. 17. Normalize whitespace before validation when appropriate
  21. 18. Validate date ranges
  22. 19. Validate impossible future dates
  23. 20. Validate timestamp parsing
  24. 21. Preserve raw values during parsing
  25. 22. Validate referential integrity
  26. 23. Be careful with timing of reference data
  27. 24. Quarantine invalid records
  28. 25. Add reason codes
  29. 26. Validate source file presence
  30. 27. Validate source file count
  31. 28. Validate partition completeness
  32. 29. Validate freshness
  33. 30. Separate source freshness from pipeline freshness
  34. 31. Check row counts against history
  35. 32. Use anomaly thresholds based on evidence
  36. 33. Validate zero-row loads carefully
  37. 34. Reconcile financial totals
  38. 35. Reconcile by business segment
  39. 36. Reconcile at the expected grain
  40. 37. Check duplicates after MERGE preparation
  41. 38. Validate before advancing the watermark
  42. 39. Validate after write too
  43. 40. Check idempotency
  44. 41. Persist batch identifiers
  45. 42. Detect repeated source files
  46. 43. Use hashes carefully
  47. 44. Do not hash unstable serialization blindly
  48. 45. Validate null-safe comparison rules
  49. 46. Validate schema drift
  50. 47. Detect type widening/narrowing deliberately
  51. 48. Validate decimal precision/scale
  52. 49. Validate timezone assumptions
  53. 50. Check date-boundary behavior
  54. 51. Validate enumerations against a reference table
  55. 52. Validate one-to-many expectations
  56. 53. Check orphan child rows
  57. 54. Validate aggregate relationships
  58. 55. Validate percentage relationships
  59. 56. Detect impossible combinations
  60. 57. Validate lifecycle transitions
  61. 58. Check monotonic sequence fields
  62. 59. Validate event IDs
  63. 60. Data quality checks can be warning or blocking
  64. 61. Use severity deliberately
  65. 62. Avoid one global quality score
  66. 63. Assign stable rule IDs
  67. 64. Record rule results
  68. 65. Preserve sample failures
  69. 66. Protect sensitive data in quality logs
  70. 67. Check row-count reconciliation before/after filters
  71. 68. Keep rejection accounting explicit
  72. 69. Validate joins for row multiplication
  73. 70. Use validate-style checks conceptually in distributed pipelines
  74. 71. Validate partition-level row counts
  75. 72. Validate partition-level totals
  76. 73. Detect duplicate partitions/batches
  77. 74. Validate incremental boundaries
  78. 75. Use half-open time windows consistently
  79. 76. Check late-arriving records
  80. 77. Detect distribution drift
  81. 78. Check categorical distribution
  82. 79. Check numeric summary
  83. 80. Use percentiles carefully
  84. 81. Compare current vs historical metrics
  85. 82. Detect all-null columns
  86. 83. Check constant columns
  87. 84. Be careful with expensive distinct checks
  88. 85. Sample only for exploration, not final guarantees
  89. 86. Avoid collecting large invalid sets to the driver
  90. 87. Limit examples in alerts
  91. 88. Keep full failure evidence separately
  92. 89. Validate target schema after write
  93. 90. Validate target uniqueness after MERGE
  94. 91. Compare source and target keys
  95. 92. Reconciliation scope must match
  96. 93. Validate counts before destructive overwrite
  97. 94. Add guardrails to rebuilds
  98. 95. Quality rules should be versioned with code
  99. 96. Separate source defects from pipeline defects
  100. 97. Quality checks need ownership
  101. 98. Define remediation path
  102. 99. Data quality belongs at multiple layers
  103. 100. Use a repeatable quality workflow
  104. What the result means
  105. What to look for
  106. Row count suddenly drops
  107. Row count suddenly increases
  108. Duplicate keys appear after join
  109. Null rate spikes
  110. Financial totals mismatch but row counts match
  111. Freshness check fails but pipeline duration is normal
  112. Production notes
  113. Checks consume compute
  114. Blocking rules should be deterministic
  115. Quality state should be auditable
  116. Quarantine is not a trash can
  117. Thresholds must be evidence-based
  118. Quality checks should fail closed only where the business requires it
  119. Common mistakes
  120. 1. Using one generic quality score
  121. 2. Dropping duplicates without investigating them
  122. 3. Treating every null as bad
  123. 4. Treating zero rows as always invalid
  124. 5. Relying only on row counts
  125. 6. Checking quality only before write
  126. 7. Using arbitrary thresholds
  127. 8. Collecting all bad rows to the driver
  128. 9. Advancing watermarks before quality gates pass
  129. 10. Alerting without an owner/remediation path
  130. Next step
  131. Related deep dives

Problem

A pipeline can complete successfully and still produce bad data.

Typical failures include:

  • missing rows
  • duplicate business keys
  • unexpected nulls
  • invalid values
  • schema drift
  • broken foreign keys
  • stale data
  • duplicated loads
  • incorrect aggregates
  • partially loaded partitions
  • silent type changes

The goal is to make data quality checks specific to the dataset and business contract, detect failures early, and route bad data to the right next step instead of relying on one generic “quality score.”

Quick answer

A strong production check set usually covers:

Schema
→ expected columns and types

Completeness
→ required values are present

Uniqueness
→ keys are unique at the expected grain

Validity
→ values satisfy domain rules

Referential integrity
→ foreign/business references exist

Freshness
→ data arrived when expected

Volume
→ row counts/partitions are plausible

Reconciliation
→ business totals match trusted source expectations

Drift
→ distributions or contracts changed unexpectedly

Example PySpark baseline:

from pyspark.sql import functions as F

result = (
    df.agg(
        F.count("*").alias("row_count"),
        F.countDistinct("order_id").alias("distinct_order_id"),
        F.sum(
            F.when(F.col("order_id").isNull(), 1).otherwise(0)
        ).alias("null_order_id"),
        F.sum("amount").alias("amount_total"),
        F.min("order_date").alias("min_order_date"),
        F.max("order_date").alias("max_order_date"),
    )
    .collect()[0]
)

The key rule is:

checks should enforce a known contract
not invent arbitrary thresholds

Code / commands

1. Validate required columns

required_columns = {
    "order_id",
    "customer_id",
    "order_date",
    "amount",
}

actual_columns = set(df.columns)

missing = required_columns - actual_columns

if missing:
    raise ValueError(
        f"Missing required columns: {sorted(missing)}"
    )

Use this before transformations that assume those fields exist.


2. Detect unexpected columns

allowed_columns = {
    "order_id",
    "customer_id",
    "order_date",
    "amount",
    "currency",
}

unexpected = set(df.columns) - allowed_columns

if unexpected:
    print(
        f"Unexpected columns: {sorted(unexpected)}"
    )

Whether to fail or warn depends on your schema-evolution policy.


3. Validate Spark data types

expected_types = {
    "order_id": "string",
    "customer_id": "string",
    "order_date": "date",
    "amount": "decimal(18,2)",
}

actual_types = {
    field.name: field.dataType.simpleString()
    for field in df.schema.fields
}

mismatches = {
    name: {
        "expected": expected,
        "actual": actual_types.get(name),
    }
    for name, expected in expected_types.items()
    if actual_types.get(name) != expected
}

if mismatches:
    raise ValueError(
        f"Schema type mismatches: {mismatches}"
    )

Use explicit types for critical pipelines.


4. Check required non-null fields

from pyspark.sql import functions as F

required_not_null = [
    "order_id",
    "order_date",
]

null_checks = [
    F.sum(
        F.when(F.col(c).isNull(), 1).otherwise(0)
    ).alias(f"{c}_nulls")
    for c in required_not_null
]

result = df.agg(*null_checks)
result.show(truncate=False)

A primary business key often should have zero nulls.

Do not apply zero-null rules to optional fields.


5. Check null percentage

row_count = df.count()

null_stats = df.agg(
    *[
        (
            F.sum(
                F.when(F.col(c).isNull(), 1).otherwise(0)
            ) / F.lit(row_count)
        ).alias(f"{c}_null_pct")
        for c in df.columns
    ]
)

null_stats.show(truncate=False)

Use percentages for monitoring drift.

Do not assume one universal acceptable null percentage.


6. Avoid scanning the table repeatedly

Instead of:

count()
count nulls
count duplicates
sum amount

as separate actions, combine checks where practical into one aggregation.

Distributed scans are expensive.


7. Check business-key uniqueness

duplicates = (
    df.groupBy("order_id")
    .count()
    .filter(F.col("count") > 1)
)

duplicate_count = duplicates.count()

If order_id is expected to define one row, any duplicate is a contract violation.


8. Validate composite-key uniqueness

duplicates = (
    df.groupBy(
        "merchant_id",
        "order_id",
    )
    .count()
    .filter(F.col("count") > 1)
)

Use the real business grain, not an arbitrary single column.


9. Do not use dropDuplicates() as a quality check

Bad:

df = df.dropDuplicates(["order_id"])

This hides evidence.

First detect and inspect duplicates.

Then resolve them using a documented business rule.


10. Inspect duplicate examples

duplicate_keys = (
    df.groupBy("order_id")
    .count()
    .filter(F.col("count") > 1)
    .select("order_id")
)

examples = (
    df.join(
        duplicate_keys,
        on="order_id",
        how="inner",
    )
    .orderBy("order_id")
)

examples.show(100, truncate=False)

Inspect whether duplicates are:

  • true source duplicates
  • retries
  • versioned records
  • join multiplication
  • legitimate multiple events

11. Validate row grain after joins

Suppose output should have one row per order.

Check:

summary = df.agg(
    F.count("*").alias("rows"),
    F.countDistinct("order_id").alias("distinct_orders"),
)

summary.show()

If:

rows > distinct_orders

investigate whether multiple rows per order are expected.


12. Validate numeric range

Example:

invalid = df.filter(
    F.col("amount") < 0
)

Only treat negatives as invalid if the business contract says they are invalid.

Returns/refunds may legitimately be negative.


13. Validate bounded domain values

allowed_status = [
    "pending",
    "completed",
    "cancelled",
]

invalid_status = df.filter(
    ~F.col("status").isin(allowed_status)
    & F.col("status").isNotNull()
)

Use a governed set of values.


14. Validate currency codes

Example:

invalid_currency = df.filter(
    ~F.col("currency").isin(
        "USD",
        "CAD",
        "EUR",
    )
)

In production, use the organization’s supported currency reference rather than hardcoding incomplete global assumptions.


15. Validate string patterns

Example:

invalid_ids = df.filter(
    ~F.col("order_id").rlike(r"^[A-Z0-9\-]+$")
)

Use regex only when the identifier contract is actually defined.

Do not invent formatting constraints from samples alone.


16. Check empty strings separately from nulls

empty_names = df.filter(
    F.trim(F.col("customer_name")) == ""
)

Null and empty string are different states.

Do not combine them unless the business rule says they are equivalent.


17. Normalize whitespace before validation when appropriate

df = df.withColumn(
    "country_code",
    F.trim(F.col("country_code"))
)

Do not silently normalize identifiers where leading/trailing spaces are meaningful.


18. Validate date ranges

Example:

invalid_dates = df.filter(
    F.col("end_date") < F.col("start_date")
)

This is a defensible semantic rule.


19. Validate impossible future dates

If business rules forbid future-dated events:

invalid = df.filter(
    F.col("event_date") > F.current_date()
)

Only use this where future dates are genuinely invalid.


20. Validate timestamp parsing

If source timestamps are strings:

parsed = df.withColumn(
    "event_ts_parsed",
    F.to_timestamp("event_ts")
)

bad = parsed.filter(
    F.col("event_ts").isNotNull()
    & F.col("event_ts_parsed").isNull()
)

Inspect failures before overwriting the original text field.


21. Preserve raw values during parsing

Good pattern:

event_ts_raw
event_ts_parsed

This helps diagnose source-format issues.

Do not destroy the original value before validation succeeds.


22. Validate referential integrity

Example:

missing_customers = (
    orders.alias("o")
    .join(
        customers.select("customer_id").alias("c"),
        on="customer_id",
        how="left_anti",
    )
)

These are order rows whose customer key is not present in the reference dataset.


23. Be careful with timing of reference data

A missing dimension row may mean:

bad source key

or:

late-arriving dimension

Do not classify every anti-join result as corrupt data automatically.


24. Quarantine invalid records

Pattern:

valid = df.filter(
    F.col("order_id").isNotNull()
)

invalid = df.filter(
    F.col("order_id").isNull()
)

Write invalid records with:

validation_reason
batch_id
source_file
ingestion_time

Do not silently discard them if they matter operationally.


25. Add reason codes

Example:

invalid = (
    df.withColumn(
        "validation_reason",
        F.when(
            F.col("order_id").isNull(),
            F.lit("missing_order_id"),
        )
        .when(
            F.col("amount").isNull(),
            F.lit("missing_amount"),
        )
    )
    .filter(F.col("validation_reason").isNotNull())
)

For multiple simultaneous issues, consider an array/list of reasons instead of one overwritten reason.


26. Validate source file presence

For expected daily feeds:

orders_20261004.csv
customers_20261004.csv

a successful job should not run with only one required source.

Check expected vs received files before processing.


27. Validate source file count

Example:

expected:
1 control file
12 hourly files

received:
11 hourly files

Whether to fail or wait depends on the ingestion contract.


28. Validate partition completeness

If one partition per day is expected:

expected_dates = ...
actual_dates = ...

missing_dates = expected_dates - actual_dates

A table can have valid rows and still be missing a whole date.


29. Validate freshness

Example:

latest = df.agg(
    F.max("ingested_at").alias("latest_ingested_at")
)

latest.show()

Compare to the dataset’s actual SLA.

Do not invent a global freshness threshold.


30. Separate source freshness from pipeline freshness

Track:

source event time
ingestion time
processing completion time

Example:

source is 2 hours late
pipeline processes it in 2 minutes

The pipeline is fast but the business data is stale.


31. Check row counts against history

Store run-level metrics:

batch_id
row_count

Then compare current volume with recent baselines.

Do not fail on arbitrary percentage changes without understanding natural seasonality and source behavior.


32. Use anomaly thresholds based on evidence

For some feeds, a 5% change is meaningful.

For others, daily volume legitimately changes by 200%.

Build thresholds from:

  • historical distribution
  • business calendar
  • known seasonality
  • source contract

33. Validate zero-row loads carefully

Zero rows can mean:

legitimate quiet period
source failure
filter bug
missing file
wrong watermark

Do not automatically treat zero as success or failure without context.


34. Reconcile financial totals

Example:

totals = df.agg(
    F.count("*").alias("rows"),
    F.sum("amount").alias("amount_total"),
)

totals.show()

Compare with the trusted source/control total when available.


35. Reconcile by business segment

Example:

(
    df.groupBy("merchant_id")
    .agg(
        F.count("*").alias("rows"),
        F.sum("amount").alias("amount_total"),
    )
    .orderBy("merchant_id")
)

Global totals can hide one segment being over/underloaded.


36. Reconcile at the expected grain

Useful dimensions might include:

date
tenant
merchant
currency
source system

Choose dimensions aligned with how the business validates data.


37. Check duplicates after MERGE preparation

Before a Delta MERGE:

source_dupes = (
    source.groupBy("order_id")
    .count()
    .filter(F.col("count") > 1)
)

if source_dupes.limit(1).count() > 0:
    raise ValueError(
        "MERGE source contains duplicate order_id values"
    )

This prevents ambiguous updates.


38. Validate before advancing the watermark

Correct order:

read source
→ transform
→ validate
→ write
→ reconcile
→ commit/checkpoint watermark

Do not advance state before quality gates pass.


39. Validate after write too

Pre-write checks confirm the candidate dataset.

Post-write checks confirm the stored result.

Useful post-write checks:

target row count
target distinct key count
target business totals
target partition coverage

40. Check idempotency

Rerun the same batch and verify the target does not double.

Example:

same batch_id
same source records
→ same final table state

This is a quality property, not only an orchestration concern.


41. Persist batch identifiers

Useful columns:

batch_id
source_file
ingested_at
pipeline_run_id

This helps trace duplicated or missing loads.


42. Detect repeated source files

Track source manifest:

source_file
file_size
checksum/hash
received_at
processed_batch_id

A repeated filename alone may not prove duplicate content.

Hash or source-provided IDs can help.


43. Use hashes carefully

A record hash can help identify unchanged/duplicate rows.

Example:

from pyspark.sql import functions as F

df = df.withColumn(
    "record_hash",
    F.sha2(
        F.concat_ws(
            "||",
            F.col("customer_id"),
            F.col("status"),
            F.col("amount").cast("string"),
        ),
        256,
    )
)

Define canonical formatting carefully.

Nulls, decimals, timestamps, and whitespace can change the hash unexpectedly.


44. Do not hash unstable serialization blindly

Bad pattern:

hash entire JSON string

if field order or formatting can change while semantics stay the same.

Canonicalize fields first.


45. Validate null-safe comparison rules

When comparing current vs prior records, remember:

NULL = NULL

is not true under normal SQL semantics.

Use null-safe comparison when the engine supports it and the business meaning requires it.


46. Validate schema drift

Track:

added columns
removed columns
type changes
nullability changes

Classify changes:

compatible
warning
breaking

Do not treat every added nullable column as a hard failure if your contract allows compatible evolution.


47. Detect type widening/narrowing deliberately

Examples:

int → long
decimal(18,2) → decimal(18,4)
string → int

These have different compatibility implications.

Do not compare schema only by column names.


48. Validate decimal precision/scale

Financial fields should match the expected precision and scale.

Unexpected floating-point conversion is worth failing early.


49. Validate timezone assumptions

Check whether timestamps are:

UTC
local time
offset-aware
naive

A timezone shift can produce apparently valid but incorrect dates.


50. Check date-boundary behavior

Example:

23:30 UTC

may be a different local business date.

If daily reports are local-time based, validate grouping after timezone conversion.


51. Validate enumerations against a reference table

Instead of hardcoded lists:

invalid = (
    facts.alias("f")
    .join(
        valid_statuses.alias("s"),
        F.col("f.status") == F.col("s.status"),
        "left_anti",
    )
)

Useful when domain values are governed externally.


52. Validate one-to-many expectations

Example:

one order
→ many line items

This is valid.

The quality rule should be:

one order header per order_id

not:

order_id unique across joined order-line dataset

Checks must match the data grain.


53. Check orphan child rows

orphans = (
    order_lines.alias("l")
    .join(
        orders.select("order_id").alias("o"),
        on="order_id",
        how="left_anti",
    )
)

Investigate source ordering/late-arrival behavior before deleting or rejecting.


54. Validate aggregate relationships

Example:

order_total
≈
sum(order_line_amount)

Use exact or tolerance-based comparison according to currency/rounding rules.

Do not invent arbitrary tolerances.


55. Validate percentage relationships

Example:

discount <= subtotal

or:

tax >= 0

only if those rules are true for the domain.


56. Detect impossible combinations

Example:

status = cancelled
AND
shipped_at IS NOT NULL

if that state is impossible in the business lifecycle.

These cross-field checks are often more valuable than generic null checks.


57. Validate lifecycle transitions

For stateful entities:

pending
→ approved
→ completed

A transition like:

completed
→ pending

may be invalid.

This requires historical/state-aware validation.


58. Check monotonic sequence fields

If source provides:

sequence_number

validate:

  • duplicates
  • gaps if gaps are not allowed
  • regressions
  • out-of-order arrival

Do not assume every sequence is gap-free unless documented.


59. Validate event IDs

For event pipelines, a stable event ID supports:

dedupe
replay
idempotency
traceability

Check uniqueness over the appropriate retention window.


60. Data quality checks can be warning or blocking

Classify checks:

BLOCKING
→ pipeline must stop/quarantine

WARNING
→ continue but alert

METRIC
→ record for trend monitoring

Not every anomaly should stop production.


61. Use severity deliberately

Example:

missing primary key
→ blocking

slightly unusual row count
→ warning/metric

new optional column
→ warning

Severity should follow business impact.


62. Avoid one global quality score

A score like:

97% quality

can hide:

0.1% missing primary keys

which may be unacceptable.

Track individual rules and business impact.


63. Assign stable rule IDs

Example:

DQ_ORDER_001
→ order_id must not be null

DQ_ORDER_002
→ order_id unique in Silver orders

DQ_ORDER_003
→ amount currency must be recognized

This helps:

  • alerting
  • dashboards
  • documentation
  • trend tracking

64. Record rule results

Useful schema:

run_id
dataset
rule_id
status
failed_count
checked_count
severity
observed_value
expected_rule
checked_at

This creates an audit trail.


65. Preserve sample failures

For failed rules, store a bounded sample:

rule_id
business key
bad value
source file
batch

Do not store every bad row in monitoring metadata if volume can be huge.

Use quarantine tables/files for full detail.


66. Protect sensitive data in quality logs

Do not expose:

passwords
tokens
full PII
payment data

in validation logs.

Use business keys or masked samples where possible.


67. Check row-count reconciliation before/after filters

Example:

source rows
-
invalid rows
-
deduplicated rows
=
valid target rows

This accounting makes row loss explainable.


68. Keep rejection accounting explicit

Example:

10,000 source rows
9,950 valid
30 duplicate
20 invalid

This is much more useful than:

pipeline succeeded

69. Validate joins for row multiplication

Before join:

100,000 orders

After joining a supposedly one-to-one dimension:

125,000 rows

This indicates duplicate dimension keys or wrong join grain.


70. Use validate-style checks conceptually in distributed pipelines

Even when the framework lacks pandas-style validate="many_to_one", enforce the same expectation explicitly.

Example:

dimension key should be unique before join

Check it.


71. Validate partition-level row counts

Example:

(
    df.groupBy("order_date")
    .agg(F.count("*").alias("rows"))
    .orderBy("order_date")
)

Partition-level checks catch one missing day hidden by a normal global total.


72. Validate partition-level totals

(
    df.groupBy("order_date")
    .agg(
        F.count("*").alias("rows"),
        F.sum("amount").alias("amount_total"),
    )
    .orderBy("order_date")
)

Useful for financial and transactional pipelines.


73. Detect duplicate partitions/batches

Check whether the same:

batch_id
partition_date
source_file

was committed more than once.

This often reveals retry bugs.


74. Validate incremental boundaries

For watermark loads, record:

start watermark
end watermark
source rows
target rows

Then verify no interval was:

skipped
overlapped unintentionally
reprocessed without idempotency

75. Use half-open time windows consistently

Example:

[start, end)

This helps adjacent batches line up without overlapping end timestamps.

But timestamp ties and late arrivals still require explicit design.


76. Check late-arriving records

Measure:

ingestion_time - event_time

Track distribution over time.

Use the real source behavior to set lateness policy.


77. Detect distribution drift

Examples:

country mix
status mix
amount distribution
null rate
distinct customer count

Compare with history.

Do not alert on every natural fluctuation.


78. Check categorical distribution

(
    df.groupBy("status")
    .count()
    .orderBy(F.desc("count"))
    .show()
)

A new/unknown status may indicate source change.


79. Check numeric summary

df.select(
    F.min("amount").alias("min_amount"),
    F.max("amount").alias("max_amount"),
    F.avg("amount").alias("avg_amount"),
).show()

Use trends/context rather than universal “acceptable” values.


80. Use percentiles carefully

For skewed metrics, approximate percentiles can be more informative than average.

Example:

df.selectExpr(
    "percentile_approx(amount, array(0.5, 0.95, 0.99)) AS percentiles"
).show(truncate=False)

Approximate percentile behavior depends on engine/configuration.


81. Compare current vs historical metrics

Store per-run metrics so you can answer:

Did null rate jump?
Did row count collapse?
Did a category disappear?
Did amount distribution shift?

Trend context is more useful than isolated thresholds.


82. Detect all-null columns

row_count = df.count()

all_null_exprs = [
    (
        F.sum(
            F.when(F.col(c).isNull(), 1).otherwise(0)
        ) == row_count
    ).alias(c)
    for c in df.columns
]

An all-null required field often signals parser/schema/source failure.


83. Check constant columns

A field that suddenly has one distinct value may indicate:

  • hardcoded source bug
  • parser issue
  • upstream default
  • legitimate business condition

Track:

df.select(
    *[
        F.countDistinct(c).alias(c)
        for c in df.columns
    ]
)

Use selectively on manageable/high-value columns.


84. Be careful with expensive distinct checks

countDistinct can be shuffle-heavy.

Do not run dozens of full distinct scans on massive tables without prioritizing important keys/columns.

Combine work where possible.


85. Sample only for exploration, not final guarantees

sample = df.sample(
    withReplacement=False,
    fraction=0.01,
)

A sample can reveal issues.

It cannot prove:

zero duplicates
zero invalid keys
complete referential integrity

Use full checks where the contract requires certainty.


86. Avoid collecting large invalid sets to the driver

Bad:

bad_rows = invalid.collect()

For large failures, write to quarantine storage and only collect small summaries/samples.


87. Limit examples in alerts

Alert:

rule failed
failed_count=1,248,321
sample_keys=[...]

Do not attach a million bad rows to logs or email.


88. Keep full failure evidence separately

Pattern:

quality result table
→ summary

quarantine table
→ full failed rows

This separates monitoring from detailed remediation.


89. Validate target schema after write

target = spark.read.table("silver.orders")
target.printSchema()

Useful when schema evolution or overwrite behavior is involved.


90. Validate target uniqueness after MERGE

duplicates = (
    spark.read.table("silver.orders")
    .groupBy("order_id")
    .count()
    .filter(F.col("count") > 1)
)

Do not assume MERGE logic preserved intended grain.


91. Compare source and target keys

Missing target keys:

missing = (
    source.select("order_id").distinct()
    .join(
        target.select("order_id").distinct(),
        on="order_id",
        how="left_anti",
    )
)

Extra target keys:

extra = (
    target.select("order_id").distinct()
    .join(
        source.select("order_id").distinct(),
        on="order_id",
        how="left_anti",
    )
)

Use only where source and target scopes are truly comparable.


92. Reconciliation scope must match

Do not compare:

today's source

against:

entire target history

without applying the same business/date/tenant scope.


93. Validate counts before destructive overwrite

Before replacing a partition/table:

new candidate row count
vs
existing/source expected count

If candidate is unexpectedly empty, stop before overwrite.


94. Add guardrails to rebuilds

Example:

candidate_count = candidate.count()

if candidate_count == 0:
    raise ValueError(
        "Refusing to overwrite target with zero rows"
    )

Only use this if zero rows can never be legitimate for that rebuild.


95. Quality rules should be versioned with code

A rule change is part of pipeline behavior.

Track:

rule version
code commit
deployment

This helps explain why data began failing after a release.


96. Separate source defects from pipeline defects

Classify failures:

source quality
schema contract
pipeline transformation
reference data
target write

This helps route ownership correctly.


97. Quality checks need ownership

Every blocking rule should have:

owner
runbook
expected action
escalation path

A failed check without an owner becomes alert noise.


98. Define remediation path

For each important rule:

fail pipeline?
quarantine bad rows?
continue with warning?
retry source?
backfill later?
open incident?

Decide before production failure.


99. Data quality belongs at multiple layers

Typical pattern:

Bronze
→ ingestion/source checks

Silver
→ schema, dedupe, validity, referential rules

Gold
→ business reconciliation, grain, metric consistency

Do not put every rule in one layer.


100. Use a repeatable quality workflow

1. Validate schema
2. Validate required keys
3. Validate uniqueness/grain
4. Validate domain rules
5. Validate references
6. Validate freshness/volume
7. Reconcile business totals
8. Quarantine failures
9. Persist rule results
10. Advance watermark only after required gates pass

What the result means

Check type What it protects
Schema Contract compatibility
Non-null Required data completeness
Uniqueness Expected business grain
Domain/range Semantic validity
Referential integrity Cross-entity consistency
Freshness Timely data availability
Volume Missing/duplicated loads
Reconciliation Business correctness
Distribution drift Unexpected source/behavior changes
Idempotency Safe retries/reprocessing
Partition coverage Missing time/tenant slices

The main principle is:

data quality = explicit business/data contracts
not generic percentages

What to look for

Row count suddenly drops

Check:

  • missing source files
  • filter/watermark changes
  • source outage
  • schema parse failures
  • partition omissions

Row count suddenly increases

Check:

  • duplicate source load
  • join multiplication
  • retry duplication
  • source behavior change

Duplicate keys appear after join

Inspect right-side key uniqueness and join cardinality.

Null rate spikes

Check source schema, parsing, renamed fields, and upstream defaults.

Financial totals mismatch but row counts match

Check:

  • duplicate amounts
  • currency
  • filters
  • rounding
  • sign conventions
  • dropped/duplicated line items

Freshness check fails but pipeline duration is normal

The source may be late rather than the pipeline slow.

Production notes

Checks consume compute

Full-table scans, distinct counts, and referential joins can be expensive.

Prioritize critical rules and combine aggregations.

Blocking rules should be deterministic

A rule that randomly fails because of unstable sampling is not suitable for production gating.

Quality state should be auditable

Persist:

rule
run
observed value
status
timestamp

not only a pass/fail log line.

Quarantine is not a trash can

Quarantined records need:

  • reason
  • ownership
  • retention
  • remediation path

Thresholds must be evidence-based

Do not copy generic:

5% null
10% row-count deviation

thresholds without business/history justification.

Quality checks should fail closed only where the business requires it

Not every anomaly should stop all data delivery.

Use severity and impact.

Common mistakes

1. Using one generic quality score

Critical failures can be hidden inside a high average score.

2. Dropping duplicates without investigating them

This destroys evidence and may discard the wrong row.

3. Treating every null as bad

Null may be valid for optional fields.

4. Treating zero rows as always invalid

Some feeds legitimately have no activity.

5. Relying only on row counts

Business totals and key-level checks can reveal errors row counts miss.

6. Checking quality only before write

Target write behavior can still introduce issues.

7. Using arbitrary thresholds

Thresholds should come from contracts and historical behavior.

8. Collecting all bad rows to the driver

This can crash large distributed jobs.

9. Advancing watermarks before quality gates pass

This can permanently skip failed data.

10. Alerting without an owner/remediation path

This creates noise instead of reliability.

Next step

Use the failed rule to choose the next action.

  • Missing columns/types → inspect source/schema drift before transformation.
  • Null required keys → quarantine and trace to source/parser.
  • Duplicate business keys → inspect source retries, joins, and dedupe rule.
  • Reference failures → distinguish bad keys from late-arriving dimensions.
  • Volume anomaly → compare source files, watermark, partitions, and recent history.
  • Freshness failure → separate source delay from processing delay.
  • Reconciliation mismatch → compare by date/tenant/merchant and inspect row multiplication/loss.
  • Quality passes before write but target is wrong → add post-write checks.
  • Checks are too expensive → prioritize critical rules, aggregate once, and run deeper checks selectively.
  • Need architectural placement → move to Medallion Architecture Quick Reference.

← All cheat sheets