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

Feature	Benefit
🎯 Centralized logic	Only one place to maintain the test logic
⚙️ Source-specific config	Adapts to differences without duplication
📈 Consistent governance	Same standards across teams and pipelines
🔄 Easy updates	Update macro once → affects all sources
🧾 Documented policies	Tests are visible in dbt docs and logs
🚨 CI/CD friendly	Can 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.

---