dbt-testing

name: dbt build

on:
  pull_request:
  push:
    branches: [main]

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    env:
      DBT_PROFILES_DIR: .ci/dbt
    steps:
      - uses: actions/checkout@v5
        with:
          fetch-depth: 0          # needed for state:modified+ slim CI

      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - name: Install dbt + adapter
        run: pip install 'dbt-core>=1.8' 'dbt-postgres'

      - name: dbt deps
        run: dbt deps

      # Slim CI: only build/test changed nodes + their descendants.
      # Requires manifest.json from a previous run (e.g. main) at .dbt-state/.
      - name: dbt build (slim)
        run: |
          if [ -f .dbt-state/manifest.json ]; then
            dbt build --select state:modified+ --state .dbt-state/
          else
            dbt build
          fi

      - name: Upload run_results.json
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: dbt-run-results
          path: |
            target/run_results.json
            target/manifest.json
          retention-days: 14

dbt-build:
  image: python:3.12
  variables:
    DBT_PROFILES_DIR: $CI_PROJECT_DIR/.ci/dbt
  before_script:
    - pip install 'dbt-core>=1.8' 'dbt-postgres'
    - dbt deps
  script:
    - dbt build
  artifacts:
    when: always
    paths:
      - target/run_results.json
      - target/manifest.json
    expire_in: 14 days

pipeline {
  agent any
  environment {
    DBT_PROFILES_DIR = "${WORKSPACE}/.ci/dbt"
  }
  stages {
    stage('Install') {
      steps {
        sh 'pip install --user "dbt-core>=1.8" "dbt-postgres"'
        sh 'dbt deps'
      }
    }
    stage('Build') {
      steps {
        sh 'dbt build'
      }
    }
  }
  post {
    always {
      archiveArtifacts artifacts: 'target/run_results.json,target/manifest.json',
                       allowEmptyArchive: true
    }
  }
}

# 1. On main: produce the baseline manifest and store it (e.g. S3, artifact)
dbt build
mkdir -p .dbt-state
cp target/manifest.json .dbt-state/

# 2. On PR: download .dbt-state/manifest.json, then:
dbt build --select state:modified+ --state .dbt-state/

dbt build --empty

dbt build --store-failures

jq -r '
  .results[]
  | select(.status == "fail")
  | "FAIL: \(.unique_id) - \(.failures) failing rows - \(.message)"
' target/run_results.json

{⁠% test column_value_in_range(model, column_name, min_value, max_value) %}
select *
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}
{⁠% endtest %}

- name: discount_percent
  data_tests:
    - column_value_in_range:
        arguments:
          min_value: 0
          max_value: 100

{⁠% test conditional_unique(model, column_name, where_clause) %}
select {{ column_name }}, count(*) as n
from {{ model }}
where {{ where_clause }}
group by {{ column_name }}
having count(*) > 1
{⁠% endtest %}

- name: email
  data_tests:
    - conditional_unique:
        arguments:
          where_clause: "status = 'active'"

{⁠% test row_recency(model, column_name, max_age_hours) %}
{# fails if max(column_name) is older than max_age_hours #}
select max({{ column_name }}) as latest
from {{ model }}
having max({{ column_name }}) < dateadd(hour, -{{ max_age_hours }}, current_timestamp)
{⁠% endtest %}

{⁠% test external_relationship(model, column_name, to_relation, to_column) %}
select {{ column_name }} as orphan
from {{ model }}
where {{ column_name }} is not null
  and {{ column_name }} not in (select {{ to_column }} from {{ to_relation }})
{⁠% endtest %}

- name: external_account_id
  data_tests:
    - external_relationship:
        arguments:
          to_relation: 'analytics.dim_account_external'
          to_column: 'account_id'

- name: order_id
  data_tests:
    - unique:
        config:
          severity: warn          # warn instead of error
          warn_if: ">10"          # warn if more than 10 dupes
          error_if: ">100"        # hard fail if more than 100 dupes

dbt test --select state:modified+ --store-failures