Data Quality Rules Template: SQL, dbt & Great Expectations

Overview

A dashboard shows revenue dropping 40% overnight. The business panics until someone checks the source: a null currency column that broke the currency conversion. Nothing in the pipeline failed because nothing was checking. Data quality rules are the explicit definition of what “good data” means for each dataset and column, checked automatically on every run.

This template covers the six quality dimensions (completeness, consistency, accuracy, timeliness, uniqueness, validity) as a table of rules you can adapt to your own datasets, plus working configurations for dbt tests and Great Expectations. For the broader discipline of profiling and monitoring data, see the Data Quality Guide. If your rules live inside dbt models, the recipe on transforming data in the warehouse with dbt covers the model side.

When to Use

• Defining quality checks for a new data pipeline before it reaches production.
• Establishing data contracts between producers and consumers, where each side needs a written, testable definition of acceptable data.
• Setting up dbt tests or Great Expectations suites on existing tables.
• Meeting compliance requirements that demand documented validation (SOC 2, GDPR accuracy obligations, financial reconciliation).
• Onboarding new datasets to a data platform so ownership and expectations are written down.
• Debugging a data incident and formalizing the checks that would have caught it earlier.

Don’t use this template for one-off validation in exploratory notebooks or ad-hoc scripts; a couple of inline assertions is enough there. It’s also overkill for datasets with no downstream consumers, where a failed check blocks nothing.

Solution

The template documents rules for a fct_orders fact table. Copy it and replace the dataset, columns, and thresholds with your own.

# Data Quality Rules — `<Dataset Name>`

## Rules Overview

| Field | Value |
|-------|-------|
| Dataset | fct_orders |
| Schema | marts |
| Database | analytics |
| Owner | Data Platform Team |
| Last Updated | 2026-07-05 |
| Total Rules | 28 |
| Critical Rules | 12 |
| Warning Rules | 16 |
| Framework | dbt tests + Great Expectations |
| Execution | Post-load, pre-consumer access |

## 1. Quality Dimensions

| Dimension | Description | Example |
|-----------|-------------|---------|
| Completeness | Required fields are not null | order_id must never be null |
| Consistency | Data conforms to expected format and relationships | currency must be valid ISO 4217 code |
| Accuracy | Values fall within expected ranges | total_amount must be positive |
| Timeliness | Data is fresh enough for its purpose | Data must be < 24 hours old |
| Uniqueness | No duplicate records | order_id must be unique |
| Validity | Values match expected patterns | email must match regex pattern |

## 2. Column-Level Rules

### order_id

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-001 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE order_id IS NULL` | Fail pipeline |
| DQ-002 | Unique | Uniqueness | Critical | `SELECT order_id, COUNT(*) FROM fct_orders GROUP BY order_id HAVING COUNT(*) > 1` | Fail pipeline |
| DQ-003 | Format: starts with "ord_" | Validity | Critical | `SELECT COUNT(*) FROM fct_orders WHERE order_id NOT LIKE 'ord_%'` | Fail pipeline |
| DQ-004 | Length between 8 and 64 chars | Validity | Warning | `SELECT COUNT(*) FROM fct_orders WHERE LENGTH(order_id) < 8 OR LENGTH(order_id) > 64` | Log + proceed |

### customer_id

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-005 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE customer_id IS NULL` | Fail pipeline |
| DQ-006 | Exists in dim_customers | Consistency | Critical | `SELECT COUNT(*) FROM fct_orders o LEFT JOIN dim_customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL` | Fail pipeline |
| DQ-007 | Format: starts with "cus_" | Validity | Warning | `SELECT COUNT(*) FROM fct_orders WHERE customer_id NOT LIKE 'cus_%'` | Log + proceed |

### order_date

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-008 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE order_date IS NULL` | Fail pipeline |
| DQ-009 | Not in future | Accuracy | Critical | `SELECT COUNT(*) FROM fct_orders WHERE order_date > CURRENT_TIMESTAMP` | Fail pipeline |
| DQ-010 | Not older than 5 years | Accuracy | Warning | `SELECT COUNT(*) FROM fct_orders WHERE order_date < DATEADD(year, -5, CURRENT_DATE)` | Log + proceed |
| DQ-011 | Date matches partition | Consistency | Warning | `SELECT COUNT(*) FROM fct_orders WHERE DATE(order_date) != partition_date` | Log + proceed |

### status

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-012 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE status IS NULL` | Fail pipeline |
| DQ-013 | Valid enum value | Validity | Critical | `SELECT COUNT(*) FROM fct_orders WHERE status NOT IN ('pending', 'processing', 'completed', 'cancelled', 'refunded')` | Fail pipeline |

### total_amount

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-014 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE total_amount IS NULL` | Fail pipeline |
| DQ-015 | Greater than 0 | Accuracy | Critical | `SELECT COUNT(*) FROM fct_orders WHERE total_amount <= 0` | Fail pipeline |
| DQ-016 | Less than 100000 | Accuracy | Warning | `SELECT COUNT(*) FROM fct_orders WHERE total_amount >= 100000` | Log + proceed |
| DQ-017 | Matches sum of line items | Consistency | Critical | `SELECT COUNT(*) FROM fct_orders o JOIN fct_order_items i ON o.order_id = i.order_id GROUP BY o.order_id HAVING o.total_amount != SUM(i.line_total)` | Fail pipeline |

### currency

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-018 | Not null | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE currency IS NULL` | Fail pipeline |
| DQ-019 | Valid ISO 4217 code | Validity | Critical | `SELECT COUNT(*) FROM fct_orders WHERE currency NOT IN ('USD', 'EUR', 'GBP', 'JPY', 'CAD', 'AUD', 'CHF', 'CNY', 'BRL', 'MXN')` | Fail pipeline |
| DQ-020 | Length is 3 | Validity | Warning | `SELECT COUNT(*) FROM fct_orders WHERE LENGTH(currency) != 3` | Log + proceed |

### payment_method

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-021 | Not null when status = completed | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders WHERE status = 'completed' AND payment_method IS NULL` | Fail pipeline |
| DQ-022 | Valid enum value | Validity | Warning | `SELECT COUNT(*) FROM fct_orders WHERE payment_method NOT IN ('credit_card', 'debit_card', 'paypal', 'bank_transfer', 'crypto', 'apple_pay', 'google_pay')` | Log + proceed |

## 3. Dataset-Level Rules

| Rule ID | Rule | Type | Severity | SQL | Action on Failure |
|---------|------|------|----------|-----|-------------------|
| DQ-023 | Row count > 0 | Completeness | Critical | `SELECT COUNT(*) FROM fct_orders` (must be > 0) | Fail pipeline |
| DQ-024 | Row count within expected range | Accuracy | Warning | `SELECT COUNT(*) FROM fct_orders` (must be between 100k and 10M) | Log + proceed |
| DQ-025 | Data is fresh (< 24h) | Timeliness | Critical | `SELECT MAX(_ingested_at) FROM fct_orders` (must be < 24h ago) | Fail pipeline |
| DQ-026 | No orphaned records | Consistency | Critical | `SELECT COUNT(*) FROM fct_orders o LEFT JOIN dim_customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL` | Fail pipeline |
| DQ-027 | All completed orders have payment events | Consistency | Warning | `SELECT COUNT(*) FROM fct_orders o WHERE o.status = 'completed' AND NOT EXISTS (SELECT 1 FROM fct_payment_events p WHERE p.order_id = o.order_id)` | Log + proceed |
| DQ-028 | Refund amounts don't exceed original | Accuracy | Critical | `SELECT COUNT(*) FROM fct_orders WHERE status = 'refunded' AND refund_amount > total_amount` | Fail pipeline |

## 4. dbt Test Configuration

### YAML Schema File

```yaml
version: 2

models:
  - name: fct_orders
    description: "Fact table containing all customer orders"
    columns:
      - name: order_id
        description: "Unique order identifier"
        tests:
          - not_null
          - unique
          - accepted_values:
              values: ["ord_%"]
              quote: false

      - name: customer_id
        description: "Customer reference"
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

      - name: order_date
        description: "Order placement timestamp"
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "order_date <= CURRENT_TIMESTAMP"

      - name: status
        description: "Order status"
        tests:
          - not_null
          - accepted_values:
              values: ['pending', 'processing', 'completed', 'cancelled', 'refunded']

      - name: total_amount
        description: "Order total in original currency"
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "total_amount > 0"
          - dbt_utils.expression_is_true:
              expression: "total_amount < 100000"

      - name: currency
        description: "ISO 4217 currency code"
        tests:
          - not_null
          - accepted_values:
              values: ['USD', 'EUR', 'GBP', 'JPY', 'CAD', 'AUD', 'CHF', 'CNY', 'BRL', 'MXN']

    tests:
      - dbt_utils.expression_is_true:
          expression: "status != 'completed' OR payment_method IS NOT NULL"
          name: completed_orders_have_payment_method

      - dbt_utils.expression_is_true:
          expression: "status != 'refunded' OR refund_amount <= total_amount"
          name: refund_does_not_exceed_original
```

### Custom Test: Row Count Range

```sql
-- tests/assert_row_count_range.sql
{{ config(severity='WARN') }}

SELECT
    CASE
        WHEN COUNT(*) < 100000 THEN 'FAIL: row count below minimum'
        WHEN COUNT(*) > 10000000 THEN 'FAIL: row count above maximum'
        ELSE 'OK'
    END AS result
FROM {{ ref('fct_orders') }}
HAVING result != 'OK'
```

### Custom Test: Data Freshness

```sql
-- tests/assert_data_freshness.sql
SELECT
    EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(_ingested_at))) / 3600 AS hours_since_last_ingest
FROM {{ ref('fct_orders') }}
HAVING hours_since_last_ingest > 24
```

## 5. Great Expectations Suite

```python
import great_expectations as gx

suite = gx.ExpectationSuite("fct_orders_suite")

## Completeness
suite.add_expectation(gx.expect_column_values_to_not_be_null(column="order_id"))
suite.add_expectation(gx.expect_column_values_to_not_be_null(column="customer_id"))
suite.add_expectation(gx.expect_column_values_to_not_be_null(column="order_date"))
suite.add_expectation(gx.expect_column_values_to_not_be_null(column="total_amount"))

## Uniqueness
suite.add_expectation(gx.expect_column_values_to_be_unique(column="order_id"))

## Validity
suite.add_expectation(gx.expect_column_values_to_match_regex(
    column="order_id",
    regex=r"^ord_[a-zA-Z0-9]{5,60}$"
))
suite.add_expectation(gx.expect_column_values_to_be_in_set(
    column="status",
    value_set=["pending", "processing", "completed", "cancelled", "refunded"]
))
suite.add_expectation(gx.expect_column_values_to_be_in_set(
    column="currency",
    value_set=["USD", "EUR", "GBP", "JPY", "CAD", "AUD", "CHF", "CNY", "BRL", "MXN"]
))

## Accuracy
suite.add_expectation(gx.expect_column_values_to_be_between(
    column="total_amount",
    min_value=0.01,
    max_value=100000
))
suite.add_expectation(gx.expect_column_values_to_be_between(
    column="order_date",
    min_value="2021-01-01",
    max_value="2026-12-31"
))

## Consistency
suite.add_expectation(gx.expect_column_pair_values_A_to_be_greater_than_B(
    column_A="total_amount",
    column_B="refund_amount",
    or_equal=True,
    row_condition="status == 'refunded'",
    condition_parser="pandas"
))

## Dataset-level
suite.add_expectation(gx.expect_table_row_count_to_be_between(min_value=100000, max_value=10000000))
```

## 6. Rule Execution and Reporting

### Execution Schedule

| When | Rules | Framework | Action |
|------|-------|-----------|--------|
| Post-staging load | DQ-001 to DQ-022 (column-level) | dbt tests | Block transform on critical failure |
| Post-transform | DQ-023 to DQ-028 (dataset-level) | dbt tests + Great Expectations | Block consumer access on critical failure |
| Hourly (continuous) | DQ-025 (freshness) | Custom SQL | Alert on failure |
| Weekly (audit) | All rules | Great Expectations | Generate quality report |

### Quality Report Template

| Metric | Value | Status |
|--------|-------|--------|
| Total rules executed | 28 | — |
| Rules passed | 26 | ✅ |
| Rules failed (critical) | 0 | ✅ |
| Rules failed (warning) | 2 | ⚠️ |
| Row count | 2,543,210 | ✅ |
| Null rate (order_id) | 0% | ✅ |
| Null rate (payment_method) | 12.3% | ⚠️ (expected for non-completed) |
| Duplicate rate | 0% | ✅ |
| Freshness (hours) | 2.5 | ✅ |
| Referential integrity | 100% | ✅ |

Explanation

The rules sit at two levels. Column-level rules (DQ-001 to DQ-022) check individual fields and catch the frequent failures: nulls, duplicates, bad formats, out-of-range values. Dataset-level rules (DQ-023 to DQ-028) check the table as a whole: row count, freshness, referential integrity, cross-table consistency. A pipeline can pass every column rule and still ship stale or empty data, which is why the dataset level exists.

Severity decides what happens on failure. A critical failure blocks the pipeline so bad data never reaches consumers. A warning logs the issue and lets the run continue, which covers violations that are unusual but legitimate, like an order amount above the normal range. The split matters more than it looks: teams that mark everything critical end up muting alerts after the third false positive, and at that point the rules exist on paper only.

The execution schedule below shows where each group runs inside a typical batch pipeline:

Column checks run right after the staging load, before any transform spends compute on bad input. Dataset checks run after the transform, against the final shape of the data. Freshness runs on its own hourly schedule because staleness can happen between pipeline runs, not just during them.

On tooling: dbt tests are SQL-based, live next to the models, and run as part of dbt build, so they are the default for standard checks. Great Expectations is Python-based and better for conditional rules (like “payment_method required only when status is completed”) and for generating audit reports a non-engineer can read. Running both is normal; the overlap costs little and each covers what the other does awkwardly.

Deriving rules from a data contract is the practical starting point. Each “must” in the contract becomes a rule: “order_id is required” becomes DQ-001, “amounts reconcile with line items” becomes DQ-017. When a rule has no contract behind it, write down the business reason anyway. Rules without a stated reason are the ones nobody can defend when they start failing, and they get deleted instead of fixed.

Keep the rules versioned with the schema they protect. A renamed column or a new enum value will break rules silently if they live outside version control, and the failure mode is the worst kind: the check stops running and nobody notices until a consumer complains. In dbt this is free because tests live in the schema YAML next to the model. With standalone SQL or Great Expectations, store the suites in the same repository as the pipeline code and review changes together.

Variants

Context	Approach	Notes
Streaming pipeline	Use sliding window checks	Replace batch checks with windowed aggregates over event time
ML feature store	Add feature distribution checks	Detect data drift and schema changes before retraining
Compliance data	Add PII detection and masking checks	GDPR, CCPA requirements
Financial data	Stricter accuracy rules	Every cent must reconcile; treat warnings as failures
User-generated content	Add content quality checks	Profanity, spam, encoding issues

For DataFrame-level validation outside the warehouse, the recipe on validating DataFrame schemas with Pandera applies the same rule idea inside Python code instead of SQL.

What Works

Start with the small set of rules that catch catastrophic failures: not null on primary keys, unique on identifiers, referential integrity on foreign keys, and a non-zero row count. Those five or six rules prevent most disasters, and everything else is refinement.

Use warnings for edge cases instead of demanding 100% strictness. A rule that blocks the pipeline because three rows out of two million have an unexpected payment method will be disabled within a month. Log those, review them weekly, and promote them to critical only when a violation is genuinely impossible.

Run the rules inside the pipeline rather than as a separate audit. A post-hoc report tells you bad data already reached the dashboard; an in-pipeline check stops it from getting there.

Track quality metrics over time, not just pass/fail. A null rate creeping from 0.1% to 3% across two weeks is an early warning that a source system changed something, long before any single rule trips its threshold.

Write down the business reason for each rule (“why does this matter?”) and review the suite quarterly. Schemas and business logic drift, and rules that protected last year’s schema protect nothing today. Finally, alert on failures properly: a rule that fails silently in a log file nobody reads gives false confidence, which is worse than no rule at all.

Common Mistakes

Having no quality rules at all, with the intention of “checking it manually”. Manual checks never happen at 3 AM when the upstream feed breaks.

Marking every rule as critical. When a hundred rules can all block the pipeline, the team learns to ignore failures and the critical label loses its meaning.

Writing rules that log failures but trigger no action. If nobody is paged and no run is blocked, the rule is decoration.

Leaving rules unversioned. The schema changes, the rule keeps querying the old column, it silently stops matching rows, and the team believes the data is still being checked.

Skipping the freshness check. Stale data passes every other rule and looks perfectly healthy; only an explicit timeliness check catches a feed that stopped arriving two days ago.

Setting rules too strict for real data. Requiring 100% completeness on genuinely optional fields blocks valid rows and trains everyone to override the checks.

Undocumented rules. When a rule fails and nobody remembers why it exists, it gets removed instead of investigated, and the protection it provided disappears with it.

See Also

• Data Pipeline Design Document Template for documenting the pipeline these rules guard.
• ETL Job Runbook Template for the operational side of running the pipeline.
• Data Governance Policy Template for the policy layer above individual rules.
• Data Quality Guide for profiling, monitoring, and incident handling around these checks.
• dbt data tests documentation for the full test syntax used in the YAML section.
• Great Expectations documentation for checkpoints and extending the example suite.

Companion code: Data quality rules resources: the blank template, the dbt schema and custom tests, and the Great Expectations suite from this page.