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