Data Quality Checks That Actually Work

Bad data costs companies millions. Yet most teams waste time checking meaningless things. We implemented data quality at 50+ companies. Here's what actually matters.

Stop Checking the Wrong Things

Teams usually start with basic validation:

• Is this field empty?
• Does it match a pattern?
• Is it within expected ranges?

These catch obvious problems but miss the issues that actually break your business.

What Actually Matters

Business-Critical Validations

Start with checks that affect business outcomes:

Financial Data:

• Balance checks: Do debits equal credits?
• Reconciliation: Do aggregated values match source totals?
• Anomaly detection: Are transaction patterns normal?

Customer Data:

• Uniqueness: Are customer IDs actually unique?
• Completeness: Do we have required fields for key operations?
• Freshness: Is customer status up-to-date?

Product Data:

• Relationships: Do product hierarchies make sense?
• Consistency: Are prices within expected ranges?
• Availability: Can orders be fulfilled with current inventory?

Statistical Anomalies

Instead of hard limits, use statistical methods:

Historical Comparisons:

• Is today's volume within 2 standard deviations of historical average?
• Are value distributions similar to previous periods?
• Do ratios match expected patterns?

Example:

# Bad: Hard limit
if order_count > 10000:
    raise Error("Too many orders")

# Good: Statistical check
mean = historical_orders.mean()
std = historical_orders.std()
if abs(order_count - mean) > 3 * std:
    alert("Unusual order volume detected")

Cross-System Consistency

Data quality issues often show up as inconsistencies across systems:

• Do customer counts match in CRM and billing system?
• Do revenue totals match in transactional DB and analytics warehouse?
• Are product mappings consistent across sources?

Implementation Strategies

Schema Validation

Catch structural issues early:

Tools:

• JSON Schema for API responses
• Great Expectations for comprehensive validation
• Custom validators for domain-specific rules

When to use:

• On data ingestion
• After transformations that change structure
• Before loading to final tables

Business Rule Validation

Enforce domain-specific rules:

Examples:

• "Orders cannot have negative quantities"
• "Users must have at least one contact method"
• "Subscription end dates must be after start dates"

Implementation:

• dbt tests for SQL-based rules
• Python validators for complex logic
• Custom checks in transformation pipelines

Statistical Monitoring

Track data characteristics over time:

Metrics to monitor:

• Record counts (absolute and by dimensions)
• Null percentages
• Value distributions
• Uniqueness rates
• Freshness (time since last update)

Tools:

• Great Expectations for expectations
• Custom dashboards for monitoring
• Anomaly detection algorithms

Cross-System Validation

Verify consistency across sources:

Common checks:

• Reconciliation reports
• Aggregation comparisons
• Join completeness checks

Building a Data Quality Framework

Identify Critical Data

Not all data needs the same level of validation. Prioritize:

• Business-critical: Revenue, customer data, financials
• Decision-support: Analytics datasets, reporting tables
• Reference data: Lookups, configurations

Define Quality Dimensions

For each critical dataset, define:

• Completeness: Are required fields populated?
• Accuracy: Do values reflect reality?
• Consistency: Are values consistent across sources?
• Timeliness: Is data fresh enough?
• Validity: Do values conform to expected formats?
• Uniqueness: Are identifiers actually unique?

Implement Checks Incrementally

Start with critical datasets and expand:

Week 1: Core financial data Week 2: Customer data Week 3: Product data Week 4: Expand to analytics tables

Establish SLAs

Set clear expectations:

• Critical data: 99.9% quality threshold
• Important data: 99% quality threshold
• Supporting data: 95% quality threshold

Automate Responses

Don't just detect issues—fix them automatically when possible:

• Auto-retry: For transient failures
• Auto-deduplicate: For known duplicate patterns
• Auto-enrich: For missing reference data
• Alert: For issues requiring human intervention

Common Pitfalls

Too Many Checks

Every check has a cost:

• Compute resources
• Maintenance overhead
• Alert fatigue

Solution: Focus on checks that matter. Remove checks that never catch issues.

Ignoring Historical Context

A value might seem wrong in isolation but be normal historically.

Solution: Compare against historical patterns, not just absolute thresholds.

Not Acting on Failures

What's the point of detecting issues if you don't fix them?

Solution: Have clear runbooks for each type of quality issue.

Perfect is the Enemy of Good

Don't wait for perfect quality before using data.

Solution: Accept reasonable quality levels, monitor continuously, improve incrementally.

Tools and Technologies

Great Expectations

Comprehensive data quality framework:

Pros:

• Extensive library of expectations
• Good documentation
• Active community

Cons:

• Can be complex for simple use cases
• Requires infrastructure setup

dbt Tests

Simple, SQL-based tests:

Pros:

• Integrated with transformations
• Easy to write and maintain
• Version-controlled with code

Cons:

• Limited to SQL-based checks
• Less statistical analysis

Custom Solutions

Sometimes you need domain-specific validation:

When to build:

• Complex business rules
• Proprietary data formats
• Specific performance requirements

Measuring Success

Track these metrics:

• Quality Score: Percentage of checks passing
• Time to Detection: How quickly issues are caught
• Time to Resolution: How quickly issues are fixed
• False Positive Rate: How often alerts are noise
• Business Impact: Reduction in bad decisions due to data issues

Conclusion

Data quality isn't about perfection—it's about trust. Implement checks that matter, monitor continuously, and improve incrementally. Start simple, expand based on actual issues, and always keep business impact in mind.

A single quality check that catches a $10,000 error is worth more than 100 checks that catch nothing. Focus on what matters.