dbt: how to implement centralized, global test macros with source-specific configurations in dbt


🧠 Why Use Global Test Macros for Data Governance?

In projects with multiple data sources (CRM, ERP, APIs, etc.), each might have:

  • Different naming conventions
  • Different schemas and nullability rules
  • Different thresholds for acceptable data quality

Rather than writing separate tests for each source, you can:
βœ… Define global test macros once
βœ… Pass source-specific configs as parameters
βœ… Enforce standard governance policies consistently across the project


πŸ“¦ Example: Enforcing “no nulls in business-critical columns”

Let’s say you want a rule:

“In every table, customer identifiers must never be null.”

But:

  • In Source A, the column is customer_id
  • In Source B, it’s cust_id
  • In Source C, the rule only applies if the table is active

βœ… Step 1: Define the Global Test Macro

In macros/tests/test_not_null_dynamic.sql:

{% test not_null_dynamic(model, column_name, condition='1=1') %}
    SELECT *
    FROM {{ model }}
    WHERE {{ column_name }} IS NULL
      AND {{ condition }}
{% endtest %}

This macro dynamically applies a not null test to any column, with optional filtering logic.


βœ… Step 2: Apply It in Source-Specific schema.yml Configs

# models/source_a/schema.yml

models:
  - name: customers_a
    tests:
      - not_null_dynamic:
          column_name: customer_id
# models/source_b/schema.yml

models:
  - name: customers_b
    tests:
      - not_null_dynamic:
          column_name: cust_id
# models/source_c/schema.yml

models:
  - name: customers_c
    tests:
      - not_null_dynamic:
          column_name: customer_key
          condition: "is_active = true"

βœ… Step 3: Run the Test Globally

dbt test --select tag:data_quality

If you’ve tagged your tests or models, you can enforce policies selectively or in CI/CD.


βœ… Benefits of This Approach

FeatureBenefit
🎯 Centralized logicOnly one place to maintain the test logic
βš™οΈ Source-specific configAdapts to differences without duplication
πŸ“ˆ Consistent governanceSame standards across teams and pipelines
πŸ”„ Easy updatesUpdate macro once β†’ affects all sources
🧾 Documented policiesTests are visible in dbt docs and logs
🚨 CI/CD friendlyCan fail pipelines when governance fails

🧼 Summary

  • Global test macros give you a single source of truth for governance rules.
  • Source-specific configs allow flexibility without sacrificing standardization.
  • This pattern scales well across teams, sources, and environments.

    Leave a Reply

    Your email address will not be published. Required fields are marked *