Check	What it is	Why it matters	Typical action
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.

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.

Use the checks that match the risk

22 data quality checks explained

Where Advanced ETL Processor fits

Video

Next steps