22 Data Quality Checks Every Data Engineer Should Know
A practical reference for checks that catch bad data before it reaches reporting, automation, or customers.
These 22 data quality checks cover the problems data engineers see most often: missing values, duplicates, broken relationships, schema drift, late feeds, and totals that have gone wandering without a map. Advanced ETL Processor runs these checks inside a visual ETL workflow, routes failed rows, and keeps the original source data unchanged.
Data quality checks protect the target before loading
Extraction reads the source. Transformation prepares the values. Data quality checks decide whether the result is safe enough to load, publish, export, or pass to another application.
Not every workflow needs all 22 checks. A one-off file may need only required fields and data types. A nightly finance feed should probably check keys, totals, volume, freshness, duplicate files, and audit columns before anyone trusts the output.
Rule of thumb: if a bad value can reach a customer, a finance report, or an automated decision, validate it before loading. If it only annoys a temporary spreadsheet, make tea and keep the rule simple. Not every CSV deserves a cathedral.
For the broader concept, start with What is Data Validation?. For related rule types, use the Data Validation hub.
22 data quality checks compared
| Check | What it is | Why it matters | Typical action | Guide |
|---|---|---|---|---|
| Allowed values or domain validation | Checks that a value belongs to an approved list, such as status, currency, country, department, or product category. | Unexpected values split reporting groups and break downstream rules. `Completed`, `Complete`, and `COMP` are three arguments waiting to happen. | Use reference tables, map known aliases, reject unknown values, and report new values for approval. | Read the guide |
| Audit column consistency checks | Checks audit fields such as created date, updated date, created by, updated by, batch ID, and source system. | Audit fields explain where data came from and when it changed. Without them, troubleshooting becomes archaeology. | Require audit columns on critical feeds, validate timestamps, and ensure batch identifiers match the current run. | No dedicated guide yet |
| Business rule consistency for aggregates and totals | Checks that business-specific totals and calculated values agree with source values or expected formulas. | Header totals, line totals, tax totals, and balances must agree. If they do not, the data may be technically valid but financially useless. | Compare calculated totals, allow agreed rounding tolerance, and quarantine records that fail. | Read the guide |
| Completeness check for expected partitions or files | Verifies that all expected files, partitions, regions, dates, or batches are present before processing. | Processing 11 monthly files out of 12 produces neat-looking but wrong results. That is the worst kind of wrong. | Check the expected manifest, file count, partition list, or control table before loading. | No dedicated guide yet |
| Cross-column consistency checks | Compares values across fields in the same row to confirm they make sense together. | A shipped date before an order date, a closed case with no close date, or a UK postcode with a US country code usually means the row needs attention. | Define field-pair rules, log violations with both values, and fix the source or transformation rule. | Read the guide |
| Data type validation against schema | Compares field values with expected schema types such as date, integer, decimal, boolean, and text. | One text value in a numeric field can stop an import or force bad type conversion. Excel is particularly talented at this form of mischief. | Reject invalid values, convert safe values explicitly, or update the schema if the source contract has genuinely changed. | Read the guide |
| Distribution checks for mean, median, and standard deviation | Compares statistical shape against historical behaviour or expected data profiles. | Row counts can look normal while values have shifted. Distribution checks catch silent changes in pricing, quantities, durations, and measurements. | Profile key numeric fields and alert when the distribution moves beyond an agreed tolerance. | No dedicated guide yet |
| Duplicate file ingestion across systems | Checks whether the same file, batch, or extract has already been processed. | Duplicate ingestion creates repeated transactions and inflated totals, especially when multiple systems share folders or feeds. | Track file names, checksums, batch IDs, and processed timestamps before accepting a file. | Read the guide |
| Duplicate record detection | Finds repeated records using exact matches or business keys such as customer, date, amount, and reference number. | Duplicates inflate totals, create false activity, and make reconciliation feel like a hobby nobody asked for. | Deduplicate by rule, keep the newest record, quarantine both copies, or flag them for manual review. | Read the guide |
| Hierarchy validation for region, country, and state | Verifies that hierarchical values belong together, such as state within country or department within division. | Bad hierarchy values distort rollups and regional reporting. They also make drill-down dashboards deeply irritating. | Validate against hierarchy reference tables and reject or map invalid combinations. | Read the guide |
| Negative value checks where not allowed | Confirms that values such as quantity, stock count, payment amount, or hours are not negative unless the business permits it. | Negative values may be legitimate credits or returns, but they may also be source errors. The rule depends on the field. | Define where negatives are allowed, reject the rest, and document exceptions such as refunds and adjustments. | Read the guide |
| Null or missing values check | Confirms that required fields are populated and that blanks, nulls, empty strings, and placeholder values are handled consistently. | Missing values can break joins, calculations, exports, and reports. They also hide source-system problems until someone asks why half a dashboard is empty. | Reject the row, apply an agreed default, route it to an exception table, or ask the source owner to fix the feed. | Read the guide |
| Numeric range checks, such as 0-100% | Confirms that numeric values sit inside agreed limits, such as percentages between 0 and 100 or quantities above zero. | A value can be numeric and still be wrong. Range checks catch values that pass type validation but fail common sense. | Set lower and upper limits, log failures, and review thresholds regularly with the business owner. | Read the guide |
| Outlier detection for abnormal values | Identifies values far outside normal behaviour, even when they pass basic type and range checks. | Outliers can be valid, but they can also reveal unit errors, decimal-point mistakes, and source-system defects. | Flag outliers for review, route them to exceptions, or require approval before publishing. | No dedicated guide yet |
| Percentage or total consistency checks | Checks that percentages add to expected totals and that component values match summary values. | Percentages that add to 103% make reports look exciting for all the wrong reasons. | Use tolerance rules for rounding, compare components with totals, and report mismatches. | Read the guide |
| Primary key uniqueness check | Verifies that each primary key appears once in the dataset or target load batch. | Primary keys identify records. If they are duplicated, updates become unpredictable and downstream systems may overwrite the wrong row. | Stop the load, isolate duplicate keys, and decide whether the source should merge, reject, or reissue the records. | Read the guide |
| Referential integrity validation | Checks that child records have valid parent records, such as orders linked to customers or invoice lines linked to invoice headers. | Broken relationships create orphan records and unreliable reporting. Databases can reject them, but ETL-side checks usually give cleaner diagnostics. | Load parents first, reject unmatched children, or send unmatched records to an exception workflow. | Read the guide |
| Regex pattern validation for email, phone, and IDs | Uses a regular expression to verify that values match a required format. | Pattern checks catch malformed email addresses, phone numbers, IDs, postcodes, and codes before they enter operational systems. | Apply a documented pattern, reject invalid values, and keep patterns maintainable rather than heroic. | Read the guide |
| Schema drift detection for new or removed columns | Detects changes in column names, order, count, data types, or nested structure. | Schema drift breaks mappings and transformations. It usually arrives quietly, like a cat, but with more support tickets. | Compare incoming schema with the expected schema and fail fast when required fields change. | No dedicated guide yet |
| String length validation | Checks that text values fit target field sizes before loading. | Oversized strings can fail inserts or get truncated. Truncated account codes are a small disaster wearing a short coat. | Reject, trim only when approved, expand the target field, or route long values for review. | Read the guide |
| Timeliness or freshness check against SLA | Confirms that files, records, or extracts arrive within the expected time window. | Old data can be worse than no data because people trust it. A stale dashboard is just a confident liar with charts. | Compare extract timestamps with the SLA, alert on late feeds, and stop dependent workflows when freshness is critical. | No dedicated guide yet |
| Volume checks against historical thresholds | Compares current row counts or file sizes with previous runs or expected ranges. | A sudden drop or spike often indicates an upstream failure, duplicate extraction, or changed filter. | Set warning and failure thresholds, compare against history, and review exceptions before loading. | No dedicated guide yet |
What it checks
Checks that a value belongs to an approved list, such as status, currency, country, department, or product category.
Why ETL teams use it
Unexpected values split reporting groups and break downstream rules. `Completed`, `Complete`, and `COMP` are three arguments waiting to happen.
Typical failure handling
Use reference tables, map known aliases, reject unknown values, and report new values for approval.
Common use cases
- Currencies
- Countries
- Statuses
- Departments
Audit column consistency checks
What it checks
Checks audit fields such as created date, updated date, created by, updated by, batch ID, and source system.
Why ETL teams use it
Audit fields explain where data came from and when it changed. Without them, troubleshooting becomes archaeology.
Typical failure handling
Require audit columns on critical feeds, validate timestamps, and ensure batch identifiers match the current run.
Common use cases
- Batch tracking
- Migration evidence
- Compliance feeds
- Operational debugging
What it checks
Checks that business-specific totals and calculated values agree with source values or expected formulas.
Why ETL teams use it
Header totals, line totals, tax totals, and balances must agree. If they do not, the data may be technically valid but financially useless.
Typical failure handling
Compare calculated totals, allow agreed rounding tolerance, and quarantine records that fail.
Common use cases
- Invoice totals
- Tax calculations
- Stock balances
- Financial summaries
Completeness check for expected partitions or files
What it checks
Verifies that all expected files, partitions, regions, dates, or batches are present before processing.
Why ETL teams use it
Processing 11 monthly files out of 12 produces neat-looking but wrong results. That is the worst kind of wrong.
Typical failure handling
Check the expected manifest, file count, partition list, or control table before loading.
Common use cases
- Monthly batches
- Regional files
- Partitioned loads
- Multi-file imports
What it checks
Compares values across fields in the same row to confirm they make sense together.
Why ETL teams use it
A shipped date before an order date, a closed case with no close date, or a UK postcode with a US country code usually means the row needs attention.
Typical failure handling
Define field-pair rules, log violations with both values, and fix the source or transformation rule.
Common use cases
- Date pairs
- Status combinations
- Country and region rules
- Amount comparisons
What it checks
Compares field values with expected schema types such as date, integer, decimal, boolean, and text.
Why ETL teams use it
One text value in a numeric field can stop an import or force bad type conversion. Excel is particularly talented at this form of mischief.
Typical failure handling
Reject invalid values, convert safe values explicitly, or update the schema if the source contract has genuinely changed.
Common use cases
- Excel imports
- CSV loads
- API payloads
- Staging-table checks
Distribution checks for mean, median, and standard deviation
What it checks
Compares statistical shape against historical behaviour or expected data profiles.
Why ETL teams use it
Row counts can look normal while values have shifted. Distribution checks catch silent changes in pricing, quantities, durations, and measurements.
Typical failure handling
Profile key numeric fields and alert when the distribution moves beyond an agreed tolerance.
Common use cases
- Pricing analysis
- Sensor feeds
- Duration metrics
- Quantity monitoring
What it checks
Checks whether the same file, batch, or extract has already been processed.
Why ETL teams use it
Duplicate ingestion creates repeated transactions and inflated totals, especially when multiple systems share folders or feeds.
Typical failure handling
Track file names, checksums, batch IDs, and processed timestamps before accepting a file.
Common use cases
- Shared folders
- SFTP feeds
- Batch IDs
- Multi-system handoffs
What it checks
Finds repeated records using exact matches or business keys such as customer, date, amount, and reference number.
Why ETL teams use it
Duplicates inflate totals, create false activity, and make reconciliation feel like a hobby nobody asked for.
Typical failure handling
Deduplicate by rule, keep the newest record, quarantine both copies, or flag them for manual review.
Common use cases
- CRM imports
- Supplier files
- Payment feeds
- Merged datasets
What it checks
Verifies that hierarchical values belong together, such as state within country or department within division.
Why ETL teams use it
Bad hierarchy values distort rollups and regional reporting. They also make drill-down dashboards deeply irritating.
Typical failure handling
Validate against hierarchy reference tables and reject or map invalid combinations.
Common use cases
- Sales territories
- Geographic reporting
- Department rollups
- Product categories
What it checks
Confirms that values such as quantity, stock count, payment amount, or hours are not negative unless the business permits it.
Why ETL teams use it
Negative values may be legitimate credits or returns, but they may also be source errors. The rule depends on the field.
Typical failure handling
Define where negatives are allowed, reject the rest, and document exceptions such as refunds and adjustments.
Common use cases
- Stock counts
- Payment amounts
- Hours worked
- Order quantities
What it checks
Confirms that required fields are populated and that blanks, nulls, empty strings, and placeholder values are handled consistently.
Why ETL teams use it
Missing values can break joins, calculations, exports, and reports. They also hide source-system problems until someone asks why half a dashboard is empty.
Typical failure handling
Reject the row, apply an agreed default, route it to an exception table, or ask the source owner to fix the feed.
Common use cases
- Required database fields
- Customer identifiers
- Invoice dates
- Mandatory status values
What it checks
Confirms that numeric values sit inside agreed limits, such as percentages between 0 and 100 or quantities above zero.
Why ETL teams use it
A value can be numeric and still be wrong. Range checks catch values that pass type validation but fail common sense.
Typical failure handling
Set lower and upper limits, log failures, and review thresholds regularly with the business owner.
Common use cases
- Discounts
- Quantities
- Measurements
- Financial limits
Outlier detection for abnormal values
What it checks
Identifies values far outside normal behaviour, even when they pass basic type and range checks.
Why ETL teams use it
Outliers can be valid, but they can also reveal unit errors, decimal-point mistakes, and source-system defects.
Typical failure handling
Flag outliers for review, route them to exceptions, or require approval before publishing.
Common use cases
- Fraud review
- Unit mistakes
- Decimal-point errors
- Operational exceptions
What it checks
Checks that percentages add to expected totals and that component values match summary values.
Why ETL teams use it
Percentages that add to 103% make reports look exciting for all the wrong reasons.
Typical failure handling
Use tolerance rules for rounding, compare components with totals, and report mismatches.
Common use cases
- Allocation files
- Forecasts
- Budget splits
- Summary reports
What it checks
Verifies that each primary key appears once in the dataset or target load batch.
Why ETL teams use it
Primary keys identify records. If they are duplicated, updates become unpredictable and downstream systems may overwrite the wrong row.
Typical failure handling
Stop the load, isolate duplicate keys, and decide whether the source should merge, reject, or reissue the records.
Common use cases
- Database loads
- Incremental updates
- Customer masters
- Transaction tables
What it checks
Checks that child records have valid parent records, such as orders linked to customers or invoice lines linked to invoice headers.
Why ETL teams use it
Broken relationships create orphan records and unreliable reporting. Databases can reject them, but ETL-side checks usually give cleaner diagnostics.
Typical failure handling
Load parents first, reject unmatched children, or send unmatched records to an exception workflow.
Common use cases
- Order and order-line loads
- Customer transactions
- Product catalogues
- Relational database imports
What it checks
Uses a regular expression to verify that values match a required format.
Why ETL teams use it
Pattern checks catch malformed email addresses, phone numbers, IDs, postcodes, and codes before they enter operational systems.
Typical failure handling
Apply a documented pattern, reject invalid values, and keep patterns maintainable rather than heroic.
Common use cases
- Postcodes
- Order references
- Phone numbers
- Structured identifiers
Schema drift detection for new or removed columns
What it checks
Detects changes in column names, order, count, data types, or nested structure.
Why ETL teams use it
Schema drift breaks mappings and transformations. It usually arrives quietly, like a cat, but with more support tickets.
Typical failure handling
Compare incoming schema with the expected schema and fail fast when required fields change.
Common use cases
- CSV feeds
- API responses
- Supplier exports
- Spreadsheet templates
What it checks
Checks that text values fit target field sizes before loading.
Why ETL teams use it
Oversized strings can fail inserts or get truncated. Truncated account codes are a small disaster wearing a short coat.
Typical failure handling
Reject, trim only when approved, expand the target field, or route long values for review.
Common use cases
- Database column limits
- Fixed-width files
- Product codes
- External API fields
Timeliness or freshness check against SLA
What it checks
Confirms that files, records, or extracts arrive within the expected time window.
Why ETL teams use it
Old data can be worse than no data because people trust it. A stale dashboard is just a confident liar with charts.
Typical failure handling
Compare extract timestamps with the SLA, alert on late feeds, and stop dependent workflows when freshness is critical.
Common use cases
- Daily extracts
- Dashboard feeds
- Supplier drops
- Operational alerts
Volume checks against historical thresholds
What it checks
Compares current row counts or file sizes with previous runs or expected ranges.
Why ETL teams use it
A sudden drop or spike often indicates an upstream failure, duplicate extraction, or changed filter.
Typical failure handling
Set warning and failure thresholds, compare against history, and review exceptions before loading.
Common use cases
- Scheduled imports
- Warehouse loads
- API extracts
- Event streams
Where Advanced ETL Processor fits
Advanced ETL Processor Enterprise runs these checks inside a visual ETL workflow, logs failures, routes rejected rows, and keeps the original files unchanged. That matters when the process is scheduled and nobody is watching the screen at 2:00 a.m.
The Validator object is the main place to configure these rules. The validation rules documentation lists the available checks before you start wiring them into a workflow.
Useful next pages include required field validation, range validation, lookup validation, and referential integrity validation.
Data quality checks FAQ
Which data quality checks should an ETL workflow run first?
Start with required fields, data types, schema drift, primary keys, and duplicate file checks. These checks confirm that the source is usable before the workflow spends time on transformations, lookups, and database writes.
Are data quality checks the same as data validation rules?
They overlap. Data validation rules are the specific checks applied to records, fields, files, and relationships. Data quality checks use those rules to protect completeness, accuracy, consistency, uniqueness, and timeliness.
Should every workflow use all 22 checks?
No. Use the checks that match the source, target, and business risk. A small one-off import may need only basic checks, while finance and customer workflows usually need several checks working together.
Where should failed data quality checks go?
Failed rows should go to a controlled exception file, table, or review queue with the failed rule, field name, source reference, and run identifier. A vague failed-import message is not enough for repeatable operations.
Can Advanced ETL Processor automate these checks?
Yes. Advanced ETL Processor can run validation checks inside visual ETL workflows using validators, SQL, lookup tables, expressions, regular expressions, routing, logging, scheduling, and scripts where needed.
Next steps
If this validation rule belongs in a repeatable ETL process, configure it in the Validator object, test it on a sample file, then decide whether failed rows should be rejected, logged, or routed for review.
Keep the original data unchanged. Raw files are still the witness statement.