Validating data integrity with SQL (2026)

TL;DR

Validating data integrity with SQL means expressing each business invariant as a query whose result is easy to interpret. Cover structure, nulls, duplicates, references, allowed values, cross-column rules, time, and aggregate reconciliation, then run those checks against a documented snapshot and retain the evidence.

Key Takeaways

• Translate requirements into explicit invariants before writing validation queries.
• Design most integrity checks to return only violating rows, which makes a nonempty result actionable.
• Test nullability, uniqueness, relationships, domains, time rules, and reconciled measures separately.
• Use null-safe comparisons and declare the database dialect, timezone, precision, and snapshot boundary.
• Reconcile counts and money by meaningful business partitions, not only with one grand total.
• Save evidence with a run ID and quarantine bad data instead of silently repairing it.
• Run fast checks near each change and broader reconciliations on controlled snapshots.

Validating data integrity with SQL is the practice of turning data requirements into executable queries that expose missing, duplicated, orphaned, contradictory, or incorrectly transformed records. The most useful checks have an explicit invariant, a defined data boundary, and an unambiguous failure result. A query that returns suspicious rows without explaining the expected rule is only exploration, not a reliable test.

This guide builds a practical validation strategy for transactional databases and analytical stores. The examples use PostgreSQL syntax and run in a transaction with temporary tables, so you can practice safely. Adapt date functions, regular expressions, and null-safe operators when your engine differs. The underlying test design remains the same.

TL;DR

A strong default pattern is SELECT identifying_columns FROM scope WHERE NOT (expected_rule). Add a deterministic boundary, such as a batch ID or closed time window, and make the output identify both the record and the violated rule.

1. Validating data integrity with SQL Starts With Invariants

An invariant is a condition that must remain true for every record or business event in a stated scope. Examples include order_id is unique, quantity is positive, refund amount does not exceed captured amount, and every shipped order has a shipment. Requirements written as vague goals, such as "data should be accurate," cannot produce decisive SQL until you make them specific.

For each rule, record five things: the business meaning, grain, scope, acceptable exception, and failure owner. Grain describes what one row represents. Scope may be a batch, tenant, country, or completed business day. Exceptions include deliberately anonymous users or historical rows created before a constraint existed. The owner is the team that can decide whether a violation is a product defect, migration issue, or valid exception.

Separate database constraints from validation queries. A NOT NULL, UNIQUE, CHECK, or foreign-key constraint prevents some invalid writes, which is excellent. SQL validation still matters because constraints might be missing, deferred, disabled during a load, unable to express cross-table rules, or unable to detect a semantically wrong but well-formed value. A foreign key proves that customer 42 exists, not that the order belongs to the correct tenant.

Use a rule catalog before building a suite:

This catalog prevents random querying and gives each failed result a traceable meaning.

2. Create a Safe, Runnable Validation Fixture

A compact fixture helps you prove that a check fails for the intended reason and passes after correction. The following PostgreSQL script creates temporary source tables containing several deliberate defects. It does not modify permanent objects. Run it in psql or a compatible PostgreSQL client.

BEGIN;

CREATE TEMP TABLE customers (
    customer_id bigint PRIMARY KEY,
    tenant_id integer NOT NULL,
    email text
);

CREATE TEMP TABLE orders (
    order_id bigint PRIMARY KEY,
    external_order_id text,
    customer_id bigint,
    tenant_id integer NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    paid_amount numeric(12, 2)
);

CREATE TEMP TABLE order_items (
    order_item_id bigint PRIMARY KEY,
    order_id bigint NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(12, 2) NOT NULL
);

INSERT INTO customers VALUES
    (1, 10, 'sam@example.test'),
    (2, 20, NULL);

INSERT INTO orders VALUES
    (101, 'WEB-1', 1, 10, 'paid', '2026-07-12T09:00:00Z', 25.00),
    (102, 'WEB-1', 999, 10, 'paid', '2026-07-12T10:00:00Z', 30.00),
    (103, NULL, 2, 10, 'unknown', '2026-07-12T11:00:00Z', NULL);

INSERT INTO order_items VALUES
    (1001, 101, 1, 25.00),
    (1002, 102, 2, 10.00),
    (1003, 102, 1, 15.00),
    (1004, 777, 1, 9.00);

-- Run the validation queries from this guide here.
ROLLBACK;

Good fixtures contain one isolated example of each defect and enough valid rows to catch an overbroad query. In production-like validation, replace the temporary data with a stable snapshot or read replica approved for testing. Never point experimental cleanup statements at a shared database. Start transactions as read-only where your environment supports them.

A fixture is also a mutation test for the validation itself. Remove the duplicate, orphan, or mismatch and confirm that the corresponding result disappears. If a query reports the same rows after the defect is fixed, the query is not proving what its name claims.

3. Validate Completeness, Domains, and Conditional Nulls

A basic null check is useful only when the column is required for the selected population. Customer email may be optional, while external_order_id may be mandatory only for web orders. SQL uses three-valued logic, so comparisons involving NULL produce unknown rather than true or false. Write null handling deliberately.

-- Required identifiers for paid orders.
SELECT order_id, external_order_id, customer_id
FROM orders
WHERE status = 'paid'
  AND (external_order_id IS NULL OR customer_id IS NULL);

-- Values outside the controlled status domain.
SELECT order_id, status
FROM orders
WHERE status NOT IN ('pending', 'paid', 'shipped', 'cancelled');

-- Conditional completeness: paid records need an amount.
SELECT order_id, status, paid_amount
FROM orders
WHERE status IN ('paid', 'shipped')
  AND paid_amount IS NULL;

The NOT IN check needs care if the tested expression or allowed-value subquery can contain null. A static list without null is straightforward. For a reference table, prefer NOT EXISTS, because a null in a NOT IN subquery can make the predicate unknown for every candidate. Also check blank strings if the application treats them as missing: NULLIF(BTRIM(external_order_id), '') IS NULL.

Domains extend beyond enumerations. Test numeric ranges, string length, normalized currency codes, and parseable identifiers. Regular expressions can flag obvious formatting defects, but they rarely prove real-world validity. An email-shaped string is not proof that the mailbox belongs to the customer. Keep syntactic, referential, and business validation as separate rules so a failure has one clear interpretation.

For more test design patterns around missing and boundary values, use database testing interview questions and answers as a practice companion.

4. Find Duplicates at the Correct Business Grain

Duplicate detection starts by defining what must be unique. The physical primary key may be unique while the business key is repeated. In the fixture, order_id is unique, but external_order_id is duplicated. Group by the entire business key and decide whether nulls are excluded, grouped, or tested as a separate completeness failure.

SELECT external_order_id, COUNT(*) AS row_count
FROM orders
WHERE external_order_id IS NOT NULL
GROUP BY external_order_id
HAVING COUNT(*) > 1
ORDER BY row_count DESC, external_order_id;

A group identifies the duplicated value but not the affected rows. Use a window function when investigators need record-level evidence:

WITH ranked AS (
    SELECT
        order_id,
        external_order_id,
        created_at,
        COUNT(*) OVER (PARTITION BY external_order_id) AS key_count,
        ROW_NUMBER() OVER (
            PARTITION BY external_order_id
            ORDER BY created_at, order_id
        ) AS occurrence_number
    FROM orders
    WHERE external_order_id IS NOT NULL
)
SELECT *
FROM ranked
WHERE key_count > 1
ORDER BY external_order_id, occurrence_number;

Do not delete every row with occurrence_number > 1 automatically. The earliest record is not necessarily authoritative, and duplicated events may have different downstream effects. Validation should preserve evidence. Remediation needs a separate, reviewed policy for survivorship, refunds, linked records, audit retention, and replay.

Composite keys are common. A rate may be unique by (property_id, stay_date, currency), while an event may be unique by (producer, event_id). Include tenant or region when uniqueness is scoped. Missing one key column produces false positives and can hide the actual contract. If the requirement is "no overlapping effective periods," simple grouping is insufficient, and a range-overlap or self-join check is needed.

5. Test Referential and Cross-Tenant Integrity

An orphan is a child record whose referenced parent does not exist. Use NOT EXISTS or a left join with a null test. NOT EXISTS states the intent clearly and behaves safely when the child key is nullable, provided you handle allowed null children separately.

-- Orders whose stated customer does not exist.
SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM customers AS c
      WHERE c.customer_id = o.customer_id
  );

-- Items whose order does not exist.
SELECT i.order_item_id, i.order_id
FROM order_items AS i
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.order_id = i.order_id
);

Existence alone is not enough in a multi-tenant system. A parent can exist under a different tenant, which may signal data leakage even if a simple foreign key passes. Validate all ownership dimensions:

SELECT
    o.order_id,
    o.tenant_id AS order_tenant,
    c.tenant_id AS customer_tenant
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.tenant_id <> c.tenant_id;

For optional relationships, define the semantics first. A guest checkout might legitimately have no customer, while a shipped business order may require both a customer and an account. Split missing-required-reference checks from orphan checks. Otherwise, one query mixes two causes and produces an unclear defect count.

Beware fanout when joining multiple one-to-many tables. Joining orders to items and payments can multiply item rows by payment rows, corrupting totals. Aggregate each child table to the parent grain before joining, or validate each relationship independently. The SQL joins for testers guide is useful when practicing join cardinality and orphan patterns.

6. Reconcile Counts and Measures Without Hiding Defects

Reconciliation compares equivalent facts across two representations, commonly source versus target, header versus detail, or ledger versus report. Begin by aligning grain, filters, currency, timezone, cancellation policy, and snapshot. Comparing an open source table with a target that finished loading ten minutes earlier creates noise rather than evidence.

Header-to-line reconciliation should aggregate lines first:

WITH item_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price)::numeric(12, 2) AS calculated_total
    FROM order_items
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.paid_amount,
    i.calculated_total,
    o.paid_amount - i.calculated_total AS difference
FROM orders AS o
JOIN item_totals AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.paid_amount IS DISTINCT FROM i.calculated_total;

PostgreSQL's IS DISTINCT FROM is a null-safe inequality operator. In other engines, use the supported null-safe comparison or expand the predicate explicitly. For floating-point measures, define a tolerance based on the business calculation. For money, prefer fixed-precision decimal types and reconcile in the required currency after the documented rounding step.

Grand totals are smoke signals, not sufficient proof. Two wrong partitions can cancel each other. Compare by business date, tenant, currency, status, or another meaningful dimension. Then drill down to keys within mismatched groups. A useful staged strategy is count, distinct key count, sum, minimum and maximum timestamps, then record-level differences.

Full outer joins expose missing groups on either side. Normalize nulls only for display, not to make absence look like zero unless the business rule equates them. Record whether late events are expected and how long a partition must remain quiet before it is considered closed.

7. Validate Cross-Column Rules, Time, and State Transitions

Many integrity defects are internally inconsistent rows. A cancellation may have a shipment timestamp, an end date may precede a start date, or a percentage may be outside its allowed interval. Express each rule as a focused violation query and retain the columns an investigator needs.

-- Contradictory amount rule in the sample schema.
SELECT order_id, status, paid_amount
FROM orders
WHERE (status IN ('paid', 'shipped') AND paid_amount IS NULL)
   OR (status = 'cancelled' AND paid_amount IS NOT NULL);

-- Generic chronology pattern for a shipment table.
SELECT shipment_id, created_at, shipped_at, delivered_at
FROM shipments
WHERE shipped_at < created_at
   OR delivered_at < shipped_at;

Time checks require a declared clock model. Store instants in timezone-aware types where practical, preserve the business timezone used to define a day, and test daylight-saving transitions if local time drives eligibility or pricing. CURRENT_TIMESTAMP can make a test nondeterministic. Prefer a fixed as-of timestamp supplied to the validation run.

State validation becomes more reliable with an event history. Define allowed transitions such as pending -> paid, paid -> shipped, and pending -> cancelled. Use LAG to compare each event with its predecessor, partitioned by entity and ordered by a deterministic sequence. Timestamps alone may tie, so include an event sequence or immutable event ID.

Do not confuse missing event history with an illegal transition. If history retention begins mid-lifecycle, the first observed state may legitimately be shipped. Scope the rule to entities with complete history or mark the first event as unknown origin. This distinction prevents a large false-positive backlog that teaches teams to ignore data quality alerts.

8. Compare Snapshots and Detect Silent Data Drift

Record-level comparison is valuable after a migration, transformation, or repair. First select the fields that are contractually equivalent. Exclude expected differences such as ingestion timestamps, surrogate keys, or normalized whitespace only when the transformation specification says they may differ.

A full outer join classifies missing and changed rows:

SELECT
    COALESCE(s.order_id, t.order_id) AS order_id,
    CASE
        WHEN s.order_id IS NULL THEN 'missing_in_source'
        WHEN t.order_id IS NULL THEN 'missing_in_target'
        WHEN s.status IS DISTINCT FROM t.status
          OR s.paid_amount IS DISTINCT FROM t.paid_amount
            THEN 'value_mismatch'
        ELSE 'match'
    END AS comparison_result
FROM source_orders AS s
FULL OUTER JOIN target_orders AS t USING (order_id)
WHERE s.order_id IS NULL
   OR t.order_id IS NULL
   OR s.status IS DISTINCT FROM t.status
   OR s.paid_amount IS DISTINCT FROM t.paid_amount;

Checksums can locate mismatched partitions efficiently, but they are not a substitute for record-level evidence. Serialization order, delimiters, null representation, numeric formatting, and hash function must match on both sides. Even with a strong hash, retain counts and drill down before declaring semantic equivalence.

Distribution drift is another signal. Compare null rate, distinct count, quantiles, category frequencies, and date coverage with a known baseline. A distribution change is not automatically a defect. A promotion, new market, or corrected upstream source can create valid drift. Route alerts to someone who can distinguish business change from pipeline failure.

For source-to-target transformation depth, continue with writing SQL to validate ETL. That workflow adds batch control totals, transformation-rule testing, and late-arriving data handling.

9. Operationalize Data Quality Checks in Delivery Pipelines

A SQL file on one laptop is not a maintained control. Give every check an ID, owner, severity, dialect, scope parameter, expected result, and remediation note. Store queries in version control and review them beside schema or transformation changes. Run fast structural and row-level checks on every pipeline change, then schedule heavier partition reconciliations after a stable load boundary.

Design the output as evidence. Useful columns include validation_run_id, rule_id, observed_at, partition, primary key, actual value, expected rule, and source version. Avoid copying sensitive data into reports. Hash or redact personal fields, apply access controls, and define retention. A validation log can become a security problem if it exports unrestricted customer data.

Choose a failure policy by risk. A critical key or tenant violation may block publication. A small timeliness delay may warn and retry. A known source exception may be quarantined with an owner and expiry date. Do not silently convert every failure into a warning, and do not halt an entire platform for a cosmetic issue.

Make checks rerunnable. Pin the input snapshot, use deterministic as-of values, and ensure retries do not duplicate evidence. Separate detection from mutation so a validator cannot accidentally repair the data it is proving. When cleanup is approved, validate before and after with the same rule and retain a reversible audit trail.

Track suite health as well as data health. A query that always returns zero because its filter no longer matches any rows is falsely reassuring. Add scope assertions such as expected partitions, nonzero row counts where appropriate, and freshness of the newest record.

10. Validating data integrity with SQL as an Interview Exercise

Interviewers usually care more about your reasoning than exotic syntax. Start by clarifying the database dialect, schemas, grain, key, null policy, time boundary, and expected output. State whether the query should return failures or a pass summary. Then write the simplest correct query, test it with a tiny counterexample, and discuss scale after correctness.

For duplicate questions, ask whether uniqueness is global or per tenant and how nulls behave. For join questions, state the expected cardinality before joining. For reconciliation, align filters and aggregation level, then mention tolerance and late-arriving records. For date windows, ask which timezone defines a day and whether endpoints are inclusive. These questions demonstrate test design, not hesitation.

Explain SQL semantics that often cause defects: NULL = NULL is not true, WHERE filters can turn an outer join into an inner join, NOT IN is hazardous with nulls, and joining two child tables can multiply rows. If you use a window function or common table expression, explain why it makes the evidence clearer.

A credible answer also covers operational safety. Use a read-only account, query a bounded partition, inspect the execution plan for heavy scans, and avoid exposing personal data. Validation in production must follow access and performance controls. Finally, describe how the check would be versioned, scheduled, alerted, and owned. That turns a one-off query into a quality engineering solution.

Interview Questions and Answers

Q: How would you validate that a target table contains every source record?

I would first align the source and target snapshot, filters, and business key. Then I would use a left anti-join or NOT EXISTS in each direction to detect missing and unexpected keys. After key coverage passes, I would compare mapped values and control totals by meaningful partition.

Q: Why can two matching row counts still hide a data defect?

One source record may be missing while another is duplicated, leaving the same count. Counts also say nothing about field values, relationships, or business rules. I combine row count with distinct key count, anti-joins, value comparison, and aggregate reconciliation.

Q: When would you use IS DISTINCT FROM?

In PostgreSQL, I use it for null-safe comparison. It treats two nulls as equal and one null versus one non-null as different, which is convenient for snapshot comparisons. I would use the equivalent supported syntax or an explicit predicate in another database.

Q: How do you prevent a reconciliation query from double-counting?

I state the grain of every input and expected join cardinality. I aggregate each one-to-many child to the comparison grain before joining it with other children. I also compare pre-join and post-join counts to detect unexpected fanout.

Q: How do you validate eventually consistent data?

I capture the event or batch identity and poll a supported observable state until a defined deadline. I assert allowed intermediate states, the terminal invariant, and timeout behavior. A fixed sleep is weaker because it is both slow and unreliable.

Q: What should a SQL integrity check return?

I prefer one row per violation with the rule ID, business key, relevant actual values, and enough context to investigate safely. A nonempty result should have a documented severity and owner. For dashboards, I can derive counts from that evidence without losing record-level traceability.

Common Mistakes

• Writing queries before agreeing on grain, business key, snapshot, and exception policy.
• Treating a matching grand total as proof that every record is correct.
• Using NOT IN with a nullable subquery and getting an empty or unknown result.
• Applying a right-table filter in WHERE after a left join, which removes missing matches.
• Joining multiple one-to-many tables before aggregation and multiplying measures.
• Comparing floating-point values for exact equality without a justified precision rule.
• Using the server's current time without declaring timezone and closed-window semantics.
• Deleting duplicates based only on row order instead of preserving evidence and applying an approved survivorship rule.
• Running unrestricted scans or exporting sensitive values from a production database.
• Counting failed rows without verifying that the validation scope itself contains the expected data.

Conclusion

Validating data integrity with SQL is most reliable when every query represents a named business invariant over a deterministic scope. Cover completeness, uniqueness, relationships, domains, cross-column consistency, chronology, and reconciled measures with failure-focused queries that preserve actionable evidence.

Start with a small rule catalog and the temporary fixture in this guide. Prove that each query detects its seeded defect, then adapt it to a controlled snapshot of your own system. Version the rules, assign owners, and make the results part of delivery evidence rather than an occasional database inspection.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• AI test data generation with Faker and LLMs (2026)
• SQL for test data setup and teardown (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• AI powered test data masking (2026)