How to validate data in Amazon Redshift

A practical guide to validating data quality in Amazon Redshift — the six checks every table needs, how to keep them off the WLM queue, the unenforced-constraint trap, and how to run them as a versioned data contract.

2026-08-31 · 8 min read

To validate data in Amazon Redshift, express each expectation as an aggregate query returning a violation count and run the batch after each load. The six checks worth having are nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. Redshift's Postgres lineage means most of the SQL is familiar — but two things are not: its constraints are declared and never enforced, so the optimiser can produce wrong answers from a duplicate key, and validation queries compete for slots in a WLM queue that your ETL also uses.

Why Redshift needs care

Constraints are hints, not rules. Redshift accepts PRIMARY KEY, UNIQUE and FOREIGN KEY declarations and never enforces any of them. Worse, the planner trusts them: if you declare order_id unique and it is not, a query that relies on that assumption can return incorrect results rather than merely slow ones. Uniqueness validation is not defensive hygiene in Redshift — it is protecting the correctness of every downstream query.

Everything shares a queue. Redshift runs queries through workload management. A validation sweep of full-table scans submitted at the same time as the nightly ETL will queue behind it, or worse, take slots from it. Give validation its own WLM queue with a modest concurrency and a query monitoring rule that aborts anything running past a threshold.

Stale statistics change what "cheap" means. After a large COPY, table statistics are out of date until ANALYZE runs. Plans chosen from stale stats can turn a fast aggregate into a broadcast join. Run validation after ANALYZE, not before.

The six checks every table needs

1. Completeness — nulls in required columns

SELECT COUNT(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

Redshift is columnar, so this reads only the order_id column — cheap even on a wide fact table. Never write SELECT * in a check; it forfeits exactly that advantage.

2. Uniqueness — duplicate business keys

SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM sales.orders
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
) dupes;

This is the check to run first on any Redshift table. Duplicates arrive routinely: a COPY retried after a partial failure, a MERGE implemented as delete-then-insert where the delete filter missed, an upstream extract rerun for the same window. Because the declared primary key does nothing, nothing else will catch them.

If the table has a sort key on the business key, the GROUP BY runs against sorted blocks and is substantially cheaper. It is worth choosing sort keys with that in mind.

3. Conformity — values outside an allowed set

SELECT COUNT(*) AS violations
FROM sales.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

Keep the null guard — NULL NOT IN (...) is NULL, the row drops, and a null-heavy column reports clean.

Redshift VARCHAR lengths are in bytes, not characters. A four-byte emoji in a VARCHAR(10) column truncates on load, and a truncated value fails the allowed-set check for a reason that has nothing to do with the source system. Size text columns generously.

4. Accuracy — numbers outside a plausible range

SELECT COUNT(*) AS violations
FROM sales.orders
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Use DECIMAL/NUMERIC for money. Redshift's DECIMAL arithmetic can also silently overflow into a wider scale during aggregation, so a range check on the raw column is more reliable than one on a computed total.

5. Conformity — malformed identifiers

Redshift keeps the Postgres regex operators:

SELECT COUNT(*) AS violations
FROM sales.orders
WHERE reference IS NOT NULL
  AND reference !~ '^ORD-[0-9]{6}
#39;;

~ is case-sensitive, ~* case-insensitive, !~ negates. SIMILAR TO and REGEXP_COUNT/REGEXP_SUBSTR are also available. No pattern translation is required — unlike SQL Server, the pattern you write is the pattern that runs.

6. Timeliness — freshness

SELECT COUNT(*) AS violations
FROM sales.orders
WHERE created_at < GETDATE() - INTERVAL '24 hours';

Redshift's GETDATE() returns UTC, which is the opposite of SQL Server's behaviour and a genuine source of confusion when porting rules between the two. SYSDATE also returns UTC. Store timestamps as TIMESTAMP (Redshift stores no zone) and keep everything in UTC by convention.

For a load-completion check that touches no table data, SVV_TABLE_INFO carries per-table metadata cheaply.

Referential integrity

SELECT COUNT(*) AS violations
FROM sales.orders o
LEFT JOIN sales.customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL;

Because foreign keys are unenforced, orphaned rows are common after a partial backfill. Distribution style matters for the cost of this check: if both tables are DISTKEY on the join column, the join is local to each slice; otherwise Redshift redistributes one side across the cluster. On large tables that is the difference between seconds and minutes.

A safe read-only user

CREATE USER catalyst_ro PASSWORD '<generated>';
GRANT USAGE ON SCHEMA sales TO catalyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO catalyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO catalyst_ro;

The default-privileges line matters as much here as in Postgres: tables recreated by tomorrow's ETL are otherwise invisible to the validation user, and checks start failing with permission errors that read like data failures.

Then isolate the workload:

-- In the WLM configuration, give the validation user group its own queue with
-- low concurrency, and a query monitoring rule that aborts on long runtime.
CREATE GROUP validators WITH USER catalyst_ro;

From ad-hoc SQL to a data contract

Declaring the expectations makes them reviewable, portable and independent of whichever tool loads the table. Catalyst uses the Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.3.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: varchar(36)
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: customer_id
        logicalType: string
        quality:
          - rule: referentialIntegrity
            dimension: consistency
            severity: error
            mustBe: "customers.id"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: numeric(12,2)
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}
#39;"
      - name: created_at
        logicalType: timestamp
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst imports the columns from information_schema, proposes a baseline contract, compiles each rule to Redshift SQL, and records pass, warn or fail per check with a sample of the failing rows. The YAML round-trips through the visual builder — comments and ordering intact — so the file reviewed in a pull request is the one that executes.

Redshift gotchas worth knowing

Scheduling and alerting

Trigger validation from the completion of the COPY or the transformation job, after ANALYZE, rather than from a fixed clock. On Redshift Serverless the same advice holds with an extra incentive: idle capacity costs nothing, but a validation sweep that wakes the workgroup every fifteen minutes keeps it warm and billing.

Alert on transitions rather than ongoing state, and split severity so that error is reserved for the checks that should genuinely block downstream consumers. The uniqueness rule almost always belongs at error in Redshift, given what an unenforced primary key does to query correctness.

Frequently asked questions

Does Redshift enforce primary keys?

No. PRIMARY KEY, UNIQUE and FOREIGN KEY are accepted and recorded, but never enforced on write. The query planner does trust them, which means a duplicated key can cause a query to return incorrect results, not just extra rows. Validating uniqueness with an explicit check is the only enforcement available.

Can I validate Redshift data without writing SQL?

Yes. Declare the expectation — required, unique, allowed values, numeric range, regex, maximum age, foreign key — and let the tool compile it to Redshift SQL. Catalyst imports the schema from information_schema, suggests a baseline set of rules, and reserves hand-written SQL for genuinely bespoke logic via a customSql rule.

How do I stop validation queries slowing down my ETL?

Put the validation user in its own WLM queue with low concurrency and a query monitoring rule that aborts long-running queries, and schedule checks to follow the load rather than run alongside it. Keep checks column-scoped — Redshift is columnar, so a single-column aggregate is dramatically cheaper than anything touching the whole row.

Does this work with Redshift Serverless and Redshift Spectrum?

Redshift Serverless behaves identically for everything in this guide; the only difference is billing, so avoid schedules that keep the workgroup awake unnecessarily. Spectrum external tables validate fine, but there is no local storage or sort key to exploit, so every check scans the underlying S3 objects — scope them to a partition.

What permissions does a validation tool need?

USAGE on the schema and SELECT on the tables, plus default privileges so future tables inherit the grant. No write access is required. Catalyst connects read-only and stores only metadata and check results.