Tests et qualité des données avec dbt

-- tests/assert_positive_order_amount.sql
-- Ce test échoue s'il trouve des montants négatifs

select
    order_id,
    amount
from
    {{ ref('orders') }}
where
    amount < 0

-- tests/assert_delivery_after_order.sql
-- Ce test échoue si delivery_date < order_date

select
    order_id,
    order_date,
    delivery_date
from
    {{ ref('orders') }}
where
    delivery_date < order_date
    and delivery_date is not null

-- tests/assert_order_totals_match.sql
-- Vérifie la cohérence entre orders et order_items

with order_calculated_totals as (
    select
        order_id,
        sum(quantity * unit_price) as calculated_total
    from {{ ref('order_items') }}
    group by order_id
),

order_discrepancies as (
    select
        o.order_id,
        o.total_amount as order_total,
        oct.calculated_total,
        abs(o.total_amount - oct.calculated_total) as difference
    from {{ ref('orders') }} o
    join order_calculated_totals oct
        on o.order_id = oct.order_id
    where abs(o.total_amount - oct.calculated_total) > 0.01
)

select * from order_discrepancies

# models/schema.yml
models:
  - name: orders_transformed
    tests:
      # Vérifier que le nombre de commandes n'a pas changé après transformation
      - dbt_utils.equal_rowcount:
          compare_model: ref('orders_source')

      # Vérifier la fraîcheur des données (max 1 jour)
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1

# models/schema.yml
models:
  - name: customers
    columns:
      - name: age
        tests:
          # Âge entre 0 et 120 ans
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 120

      - name: email
        tests:
          # Format email valide
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"