Why Data Testing is Non-Negotiable

A test-free data warehouse is a source of data that could be be correct. Dbt models transform imperfect raw data: one column order_id duplicated in the source, a value NULL unexpected in a join key, a country code out of the expected enum - these issues silently enter the warehouse and produce bad dashboards that no one discovers until an awkward meeting comes along.

dbt integrates a native testing system that makes quality checks a part declarative of the project, not an afterthought. The tests are performed with dbt test, they write themselves in YAML next to the templates, and fail the CI before the corrupted data reaches production.

The 4 Generic Built-in Tests

dbt Core includes four generic tests out of the box, configurable via YAML in the file schema.yml of the model:

# models/staging/schema.yml
version: 2

models:
  - name: stg_orders
    description: "Ordini grezzi dalla sorgente transazionale, normalizzati."
    columns:
      - name: order_id
        description: "Identificatore univoco dell'ordine."
        tests:
          - unique           # nessun duplicato
          - not_null         # nessun NULL

      - name: customer_id
        description: "Chiave esterna verso stg_customers."
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

      - name: status
        description: "Stato corrente dell'ordine."
        tests:
          - not_null
          - accepted_values:
              values: ['pending', 'processing', 'completed', 'cancelled', 'refunded']

      - name: total_amount
        description: "Importo totale lordo in EUR."
        tests:
          - not_null

Detail of the 4 Tests

tests:
  - unique
  # unique su chiave composta (dbt 1.6+)
  - dbt_utils.unique_combination_of_columns:
      combination_of_columns:
        - order_id
        - line_item_id

tests:
  - not_null
  # Con where per escludere casi noti
  - not_null:
      where: "status != 'draft'"
      config:
        severity: warn  # warn invece di error

tests:
  - accepted_values:
      values:
        - 'completed'
        - 'pending'
        - 'cancelled'
      quote: true       # aggiunge virgolette (default true per stringhe)
      config:
        severity: warn

tests:
  - relationships:
      to: ref('stg_customers')
      field: customer_id
      # Gestisce NULL: i NULL non falliscono il test
      config:
        severity: error

Configure Severity and Threshold

Not all tests need to block the pipeline. dbt allows you to configure the severity and an acceptable error threshold:

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: discount_pct
        tests:
          - not_null:
              config:
                severity: warn          # warn: il test fallisce ma dbt non esce con codice errore

      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                severity: error         # error: blocca la pipeline (default)
                error_if: ">10"         # fallisce solo se >10 righe violano il vincolo
                warn_if: ">0"           # warn se anche una sola riga viola il vincolo

      - name: order_id
        tests:
          - unique:
              config:
                severity: error
                store_failures: true    # salva le righe che falliscono in una tabella
                schema: test_failures   # schema dedicato per le failure

Singular SQL Tests: Custom Business Logic

Generic tests cover structural checks. For more complex business logic, dbt supports i singular tests: SQL file in directory tests/ which return error lines. If the query returns 0 rows, the test passes.

-- tests/assert_orders_total_positive.sql
-- Un test singolare: ogni query che restituisce righe causa il fallimento del test.
-- La convenzione è: scrivi la query che trova i casi ANOMALI.

SELECT
    order_id,
    total_amount,
    'total_amount deve essere positivo' AS failure_reason
FROM {{ ref('fct_orders') }}
WHERE total_amount <= 0

-- tests/assert_revenue_matches_line_items.sql
-- Verifica la coerenza tra header ordine e dettaglio righe

WITH order_totals AS (
    SELECT
        order_id,
        SUM(line_amount) AS calculated_total
    FROM {{ ref('fct_order_line_items') }}
    GROUP BY order_id
),
orders AS (
    SELECT order_id, total_amount
    FROM {{ ref('fct_orders') }}
)
SELECT
    o.order_id,
    o.total_amount                             AS header_total,
    ot.calculated_total                        AS calculated_total,
    ABS(o.total_amount - ot.calculated_total)  AS discrepancy
FROM orders o
JOIN order_totals ot ON o.order_id = ot.order_id
WHERE ABS(o.total_amount - ot.calculated_total) > 0.01  -- tolleranza 1 centesimo

-- tests/assert_no_future_orders.sql
-- Nessun ordine può avere data nel futuro (problema comune in migrazioni dati)

SELECT
    order_id,
    created_at,
    CURRENT_TIMESTAMP AS test_run_time
FROM {{ ref('stg_orders') }}
WHERE created_at > CURRENT_TIMESTAMP

Singular Tests with Parameters

-- tests/assert_metric_freshness.sql
-- Verifica che i dati non siano più vecchi di N ore (configurabile)

SELECT
    MAX(event_timestamp)                               AS last_event,
    CURRENT_TIMESTAMP                                  AS now,
    DATEDIFF('hour', MAX(event_timestamp), CURRENT_TIMESTAMP) AS hours_lag
FROM {{ ref('fct_events_hourly') }}
HAVING DATEDIFF('hour', MAX(event_timestamp), CURRENT_TIMESTAMP) > {{ var('max_lag_hours', 4) }}

-- Esecuzione con override della variabile:
-- dbt test --select assert_metric_freshness --vars '{"max_lag_hours": 2}'

Custom Generic Tests

When the same check needs to be applied to multiple models, you can create generic tests customized in the directory tests/generic/. They are Jinja macros that accept the model name and column name as parameters.

-- tests/generic/is_positive.sql
-- Test generico riutilizzabile: verifica che una colonna sia > 0

{% test is_positive(model, column_name) %}

SELECT {{ column_name }}
FROM {{ model }}
WHERE {{ column_name }} IS NOT NULL
  AND {{ column_name }} <= 0

{% endtest %}

-- tests/generic/is_between.sql
-- Test generico con parametri aggiuntivi

{% test is_between(model, column_name, min_value, max_value) %}

SELECT
    {{ column_name }},
    {{ min_value }} AS expected_min,
    {{ max_value }} AS expected_max
FROM {{ model }}
WHERE {{ column_name }} < {{ min_value }}
   OR {{ column_name }} > {{ max_value }}

{% endtest %}

# Utilizzo nel schema.yml
models:
  - name: fct_orders
    columns:
      - name: total_amount
        tests:
          - is_positive          # usa il test generico custom
      - name: discount_pct
        tests:
          - is_between:
              min_value: 0
              max_value: 1

dbt-expectations: Great Expectations Style Assertions

The package dbt-expectations brings the Great Expectations approach to the dbt world: a catalog full of statistical and distribution tests that go beyond simple checks structural.

# packages.yml
packages:
  - package: calogica/dbt_expectations
    version: 0.10.4

# dbt deps per installare

# models/marts/schema.yml - dbt-expectations in azione
models:
  - name: fct_orders
    tests:
      # Numero di righe atteso nel range [1000, 1000000]
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 1000000

      # La colonna non deve avere troppi NULL
      - dbt_expectations.expect_column_values_to_not_be_null:
          column_name: order_id

    columns:
      - name: total_amount
        tests:
          # Tutti i valori devono essere >= 0
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 99999

          # Distribuzione: media attesa tra 50 e 500 EUR
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 50
              max_value: 500

          # Nessun outlier estremo (z-score)
          - dbt_expectations.expect_column_stdev_to_be_between:
              min_value: 0
              max_value: 1000

      - name: order_date
        tests:
          # Le date devono essere nel passato
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: "'2020-01-01'::date"
              max_value: "current_date"
              row_condition: "status = 'completed'"

      - name: email
        tests:
          # Formato email valido con regex
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"

      - name: status
        tests:
          # Distribuzione dei valori: completed deve essere > 60% degli ordini
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.01    # ogni status deve esistere
              max_value: 0.99

Freshness Test on Models

# Verifica freshness dei dati direttamente nel test
models:
  - name: fct_events_hourly
    tests:
      # L'evento più recente deve essere meno di 2 ore fa
      - dbt_expectations.expect_row_values_to_have_recent_data:
          column_name: event_timestamp
          datepart: hour
          interval: 2

Organize Tests in the Project

A well-organized dbt project distributes tests across all layers, with density growing as we get closer to the models exposed to consumers:

# Struttura consigliata dei test per layer
#
# staging/ - test strutturali di base
#   unique + not_null sulle primary key
#   relationships verso le sorgenti
#   accepted_values sulle colonne categoriche
#
# intermediate/ - test di consistenza
#   Nessun test se i modelli sono ephemeral
#   Test critici se sono materializzati
#
# marts/ - test completi + business logic
#   Tutti i test di staging
#   Test singolari per le regole di business
#   dbt-expectations per distribuzione e range
#   store_failures=true per debug in produzione

# Esecuzione selettiva dei test:
# dbt test                              # tutti i test
# dbt test --select stg_orders          # test su un modello
# dbt test --select tag:critical        # test con tag specifico
# dbt test --select source:raw          # test sulle sorgenti
# dbt build                             # run + test insieme (raccomandato in CI)

Document the Tests in the dbt Catalog

Tests defined in YAML automatically appear in the catalog generated by dbt docs generate. Adding descriptions to tests improves understanding for the team:

models:
  - name: stg_orders
    columns:
      - name: status
        description: >
          Stato del ciclo di vita dell'ordine. I valori ammessi riflettono
          il workflow: pending (creato) → processing (pagato) → completed
          (spedito+consegnato), oppure cancelled o refunded in qualsiasi fase.
        tests:
          - not_null:
              config:
                description: "Il campo status non può mai essere NULL."
          - accepted_values:
              values: ['pending', 'processing', 'completed', 'cancelled', 'refunded']
              config:
                description: "Valori al di fuori di questo insieme indicano bug nel sistema sorgente."

Running Tests: Useful Commands and Options

dbt test is flexible: you can run all tests, select a subset of them for model, by type or by tag. Knowing these commands speeds up the development cycle:

-- Esegui tutti i test del progetto
dbt test

-- Test su un modello specifico
dbt test --select stg_orders

-- Test su tutti i modelli che dipendono da stg_orders (downstream)
dbt test --select stg_orders+

-- Test solo dei modelli con un tag specifico
dbt test --select tag:critical

-- Test solo su sorgenti (freshness + test dichiarati in sources.yml)
dbt test --select source:raw

-- Escludere modelli lenti in sviluppo
dbt test --exclude tag:slow

-- Eseguire test specifici per tipo (generic o singular)
dbt test --select test_type:generic
dbt test --select test_type:singular

-- dbt build: run + test insieme (consigliato in CI/CD)
-- Ogni nodo del DAG viene testato prima di procedere al nodo successivo
dbt build --select tag:daily

Debugging Failed Tests with store_failures

# Configurazione globale: salva le righe che falliscono in tabelle dedicate
# dbt_project.yml

tests:
  +store_failures: true           # abilita per tutti i test
  +store_failures_as: table       # 'table' o 'view'
  +schema: test_failures          # schema dedicato per le tabelle di failure

# Oppure per singolo test:
models:
  - name: fct_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                store_failures: true
                schema: test_failures

-- Dopo dbt test, interroga la tabella delle failure:
-- Schema: test_failures (o quello configurato)
-- Tabella: relationships_fct_orders_customer_id__customer_id__ref_dim_customers_ (nome auto-generato)

SELECT
    customer_id,
    COUNT(*) AS occurrences
FROM test_failures.relationships_fct_orders_customer_id__customer_id__ref_dim_customers_
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

-- Questo ti mostra esattamente quali customer_id non esistono in dim_customers --
-- utile per capire se è un problema di dati o di logica del modello.

Integrate Tests into the Development Workflow

Dbt tests are most useful when they are part of the normal development cycle, not just the IC. Here is the recommended workflow when developing or editing a model:

Conclusions and Next Steps

The dbt testing system transforms data quality from a manual process to a guarantee automated. The built-in generic tests cover the fundamental structural checks, tests singulars capture business rules, dbt-expectations brings statistical rigor.

The key is not to treat tests as an optional add-on: write them alongside the models, run them in CI on each PR, and use store_failures=true in production for quick debugging.

The next module completes the data quality picture: the automatic documentation of dbt. Like the YAML description system, the lineage graph and the interactive catalog make the warehouse understandable to the whole team.