# models/staging/_sources.yml
version: 2

sources:
  - name: raw
    database: raw_data
    schema: public
    
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    
    tables:
      - name: orders
        columns:
          - name: id
            data_tests:
              - unique
              - not_null
          
          - name: customer_id
            data_tests:
              - not_null
              - relationships:
                  to: ref('stg_customers')
                  field: customer_id
          
          - name: status
            data_tests:
              - accepted_values:
                  values: ['pending', 'shipped', 'delivered', 'cancelled']
          
          - name: amount
            data_tests:
              - not_null

-- tests/test_order_amount_positive.sql
-- Business rule: Order amounts must be positive

select
    order_id,
    amount,
    customer_id,
    order_date
from {{ ref('fct_orders') }}
where amount < 0

-- tests/test_monthly_revenue_anomaly.sql
-- Statistical test for revenue anomalies

with monthly_revenue as (
    select
        date_trunc('month', order_date) as month,
        sum(amount) as revenue
    from {{ ref('fct_orders') }}
    group by 1
),

revenue_stats as (
    select
        avg(revenue) as avg_revenue,
        stddev(revenue) as stddev_revenue
    from monthly_revenue
),

anomalies as (
    select
        mr.month,
        mr.revenue,
        rs.avg_revenue,
        rs.stddev_revenue,
        (mr.revenue - rs.avg_revenue) / nullif(rs.stddev_revenue, 0) as z_score
    from monthly_revenue mr
    cross join revenue_stats rs
)

select
    month,
    revenue,
    avg_revenue,
    z_score
from anomalies
where abs(z_score) > 3  -- Flag anomalies beyond 3 standard deviations

-- macros/schema_tests/test_accepted_range.sql
{% test accepted_range(model, column_name, min_value, max_value) %}

with validation as (
    select
        {{ column_name }} as field_name,
        case
            when {{ column_name }} < {{ min_value }} then 'below_min'
            when {{ column_name }} > {{ max_value }} then 'above_max'
            else 'valid'
        end as validation_status
    from {{ model }}
    where {{ column_name }} is not null
)

select *
from validation
where validation_status != 'valid'

{% endtest %}

# dbt_project.yml
name: 'my_project'
version: '1.0.0'

tests:
  my_project:
    +severity: error
    +store_failures: true
    +schema: test_results
    
    staging:
      +severity: warn
    
    marts:
      +severity: error
      +fail_calc: "count(*)"
      +warn_if: ">10"
      +error_if: ">100"

on-run-end:
  - "{{ log('Test execution completed', info=True) }}"

# models/staging/_sources.yml
version: 2

sources:
  - name: shopify
    database: raw
    schema: shopify
    loader: fivetran
    loaded_at_field: _fivetran_synced
    
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    
    tables:
      - name: orders
        description: "Shopify orders"
        
        loaded_at_field: _fivetran_synced
        
        freshness:
          warn_after: {count: 1, period: hour}
          error_after: {count: 4, period: hour}