SQL testen: dbt-Tests, Great Expectations

Warum SQL testen?

dbt-Tests: Integriert

# models/orders.yml
version: 2
models:
  - name: orders
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('users')
              field: id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'cancelled']

dbt-Tests ausführen

dbt test
dbt test --select orders
dbt build  # = run + test

Benutzerdefinierte Singular-Tests

-- tests/no_negative_totals.sql
SELECT id, total
FROM {{ ref('orders') }}
WHERE total < 0

Generische Tests

-- tests/generic/test_in_range.sql
{% test in_range(model, column_name, min, max) %}
SELECT * FROM {{ model }}
WHERE {{ column_name }} < {{ min }} OR {{ column_name }} > {{ max }}
{% endtest %}

-- Use it in YAML:
columns:
  - name: age
    tests:
      - in_range:
          min: 0
          max: 120

dbt-utils-Paket

- dbt_utils.expression_is_true:
    expression: "revenue >= 0"
- dbt_utils.equal_rowcount:
    compare_model: ref('staging_orders')

Great Expectations

import great_expectations as ge

suite = context.add_or_update_expectation_suite('orders')
batch = context.get_validator(batch_request, expectation_suite_name='orders')
batch.expect_column_values_to_not_be_null('id')
batch.expect_column_values_to_be_between('total', 0, 10000)

results = context.run_checkpoint('orders_checkpoint')

Wann welches Tool verwenden?

Test-Schweregrad

- accepted_values:
    values: [...]
    config:
      severity: warn
      warn_if: ">100"
      error_if: ">1000"

CI-Integration

# GitHub Actions
- run: dbt deps && dbt build --profiles-dir profiles
- if: failure()
  run: echo "::error::dbt tests failed"

Dashboards zur Datenqualität

Schema-Tests vs. Zeilen-Tests

Zusammenfassung

Schnelltest