Redshift will happily accept a duplicate primary key, a child row with no parent, and a load that silently wrote yesterday's file twice. PRIMARY KEY, UNIQUE and FOREIGN KEY are declarative only: the planner uses them for query rewrites, and nothing enforces them. That single fact is behind most of the "the dashboard numbers changed and nobody knows why" incidents we get called into.
This tutorial builds a practical data-quality layer for an Amazon Redshift warehouse in four parts: making informational constraints honest, freshness and volume monitoring from the SYS views, a test suite that runs in the pipeline, and how to fail a load without wrecking the morning refresh.
1. Informational constraints, and how to keep them true
Declare them anyway. The planner uses PRIMARY KEY and FOREIGN KEY for join elimination and for correct rewrites of materialized views, and they document intent:
CREATE TABLE analytics.dim_customer (
customer_id BIGINT NOT NULL PRIMARY KEY,
email VARCHAR(255),
created_at TIMESTAMP NOT NULL
);
CREATE TABLE analytics.fact_order (
order_id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES analytics.dim_customer(customer_id),
order_ts TIMESTAMP NOT NULL,
amount NUMERIC(18,2)
)
DISTKEY (customer_id)
COMPOUND SORTKEY (order_ts);
Two things are actually enforced and worth using: NOT NULL, which the loader will reject on, and DEFAULT. Everything else you verify yourself.
A uniqueness check is cheap on a sorted key and expensive on everything else, so run it on the keys that matter:
-- Duplicate primary keys
SELECT order_id, COUNT(*) AS n
FROM analytics.fact_order
GROUP BY order_id
HAVING COUNT(*) > 1
LIMIT 10;
-- Orphaned foreign keys
SELECT COUNT(*) AS orphans
FROM analytics.fact_order f
LEFT JOIN analytics.dim_customer d USING (customer_id)
WHERE d.customer_id IS NULL;
Duplicates on Redshift almost always come from one of three places: a re-run COPY of the same S3 prefix, a MERGE whose match condition is not unique on the source side, or an upsert implemented as DELETE + INSERT inside a transaction that was retried. Fix the pipeline; the test just tells you which one broke.
2. Freshness and volume, from the system views
Freshness answers "is this table current?" and volume answers "did we load roughly what we expected?" Both catch the failures that row-level assertions miss, because a table that received nothing at all passes every column test.
Redshift stamps every table with its last insert/update. SYS_LOAD_HISTORY and SYS_QUERY_HISTORY give the pipeline view, and for a quick catalog-wide answer:
-- Staleness by table, from load history
SELECT table_name,
MAX(start_time) AS last_load,
DATEDIFF(minute, MAX(start_time), GETDATE()) AS minutes_stale
FROM SYS_LOAD_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
AND status = 1
GROUP BY table_name
ORDER BY minutes_stale DESC;
Rather than sprinkling thresholds through scripts, keep them in a table and let one query produce the alerts:
CREATE TABLE ops.freshness_sla (
schema_name VARCHAR(128) NOT NULL,
table_name VARCHAR(128) NOT NULL,
max_lag_min INT NOT NULL,
owner_team VARCHAR(64)
);
INSERT INTO ops.freshness_sla VALUES
('analytics', 'fact_order', 90, 'revenue'),
('analytics', 'dim_customer', 1440,'crm'),
('analytics', 'fact_event', 30, 'product');
For volume, record a row count per load and compare against the trailing median rather than a fixed number — fixed thresholds page you every Monday morning and every Black Friday:
CREATE TABLE ops.load_metrics (
run_ts TIMESTAMP DEFAULT GETDATE(),
table_name VARCHAR(256),
row_count BIGINT,
null_rate NUMERIC(6,4)
);
WITH history AS (
SELECT row_count,
ROW_NUMBER() OVER (ORDER BY run_ts DESC) AS rn
FROM ops.load_metrics
WHERE table_name = 'analytics.fact_order'
),
baseline AS (
SELECT MEDIAN(row_count) AS med FROM history WHERE rn BETWEEN 2 AND 15
)
SELECT h.row_count, b.med,
ABS(h.row_count - b.med) / NULLIF(b.med, 0)::NUMERIC AS pct_delta
FROM history h CROSS JOIN baseline b
WHERE h.rn = 1;
Alert when pct_delta exceeds, say, 0.4. Two weeks of history is usually enough to absorb weekly seasonality; for strongly weekly data, compare against the same weekday.
3. A test suite that runs in the pipeline
If you are already using dbt, the generic tests cover the majority of this and cost you nothing to add:
models:
- name: fact_order
columns:
- name: order_id
tests: [unique, not_null]
- name: customer_id
tests:
- not_null
- relationships:
to: ref('dim_customer')
field: customer_id
- name: amount
tests:
- dbt_utils.accepted_range:
min_value: 0
inclusive: false
tests:
- dbt_utils.recency:
datepart: hour
field: order_ts
interval: 2
If you are not on dbt, the same thing is a table of assertions and a runner. Each row is a SQL expression that must return zero:
CREATE TABLE ops.data_tests (
test_name VARCHAR(128) PRIMARY KEY,
target VARCHAR(256),
severity VARCHAR(8), -- 'error' or 'warn'
test_sql VARCHAR(MAX) -- must return one integer column: failing rows
);
INSERT INTO ops.data_tests VALUES
('fact_order_pk_unique', 'analytics.fact_order', 'error',
'SELECT COUNT(*) FROM (SELECT order_id FROM analytics.fact_order
GROUP BY order_id HAVING COUNT(*) > 1)'),
('fact_order_amount_nonneg', 'analytics.fact_order', 'error',
'SELECT COUNT(*) FROM analytics.fact_order WHERE amount < 0'),
('dim_customer_email_shape', 'analytics.dim_customer', 'warn',
'SELECT COUNT(*) FROM analytics.dim_customer
WHERE email IS NOT NULL AND email NOT LIKE ''%%@%%.%%''');
A stored procedure walks the table, executes each statement and records the result, so the whole suite is one call from your orchestrator:
CREATE OR REPLACE PROCEDURE ops.run_data_tests(run_tag VARCHAR)
AS $$
DECLARE
rec RECORD;
failures BIGINT;
BEGIN
FOR rec IN SELECT test_name, severity, test_sql FROM ops.data_tests LOOP
EXECUTE 'SELECT (' || rec.test_sql || ')' INTO failures;
INSERT INTO ops.data_test_results
VALUES (GETDATE(), run_tag, rec.test_name, rec.severity, failures);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Call it from the Data API at the end of each load, then read ops.data_test_results and decide. Step Functions makes the branch explicit: a Choice state on "any error-severity failures" routes to publish or to alarm. The Data API orchestration pattern drops straight in here.
4. Failing safely: gate the swap, not the warehouse
The point of a test suite is not to stop the pipeline; it is to stop bad data reaching users. Load into a staging schema, test there, and only then expose it:
BEGIN;
ALTER TABLE analytics.fact_order RENAME TO fact_order_prev;
ALTER TABLE staging.fact_order_new RENAME TO fact_order;
ALTER TABLE staging.fact_order SET SCHEMA analytics;
COMMIT;
Redshift DDL is transactional, so the swap is atomic: readers see the old table or the new one, never a half-loaded one. Keep fact_order_prev for a day as the rollback. For incremental tables where a full swap is too expensive, gate at the partition level instead — load the day's rows into a staging table, test, then DELETE the target day and INSERT in one transaction.
Two operational rules make this stick:
- Warn-severity tests never block. They accumulate in
ops.data_test_resultsand get reviewed weekly. If a warn test has fired every day for a month, either fix the data or delete the test; a permanently red check trains everyone to ignore the dashboard. - Every error-severity test names an owner. Route the alarm to that team's channel with the failing row count and a link to the query. Unowned alerts are noise.
Where this fits
Data quality on Redshift is unglamorous plumbing that saves a specific, expensive kind of outage: the one nobody notices for three weeks. Start with uniqueness on the keys your MERGE logic relies on, freshness on the tables the executive dashboard reads, and a volume baseline on your two biggest facts. That is an afternoon's work and catches most of it.
If you would rather have the whole layer designed and wired to your orchestrator, that is part of our Redshift Administration and Data Modeling & Architecture engagements — get in touch.