dbt: Control over cross-domain data access

1. ✅ What custom schema macros are in dbt
2. ✅ How to use them with role-based governance
3. ✅ Why they matter for data access and domain isolation
4. ✅ A full example with role-aware schema resolution
5. ✅ How they help with cross-domain governance and auditing

---

🧠 What Is a Custom Schema Macro in dbt?

A custom schema macro in dbt is a macro that overrides the default behavior for deciding which schema a model should be built into.

By default, dbt builds models into:

<target.schema>.<model_name>

But when you define a custom macro called generate_schema_name(), you take full control over that logic.

✅ This is especially useful when:

• You have multiple business domains (e.g. finance, marketing, sales)
• You want to isolate data per domain
• You need to assign access based on user roles
• You want to audit or adapt schema logic dynamically (e.g. per environment)

---

🧱 Where to define a custom schema macro?

Create a file like this:

macros/generate_schema_name.sql

---

✅ Example: Custom Schema Macro With Role-Based Governance

📁 File: macros/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) %}
    {% set user_role = target.get('role', 'default') %}
    {% set domain = node.fqn[1] %}  {# Assuming models are organized like models/finance/model.sql #}

    {% if user_role == 'finance_analyst' %}
        {% if domain == 'finance' %}
            {{ return('finance_data') }}
        {% else %}
            {{ exceptions.raise_compiler_error("Access denied: finance_analyst cannot write to '" ~ domain ~ "' domain.") }}
        {% endif %}
    
    {% elif user_role == 'marketing_analyst' %}
        {% if domain == 'marketing' %}
            {{ return('marketing_data') }}
        {% else %}
            {{ exceptions.raise_compiler_error("Access denied: marketing_analyst cannot write to '" ~ domain ~ "' domain.") }}
        {% endif %}

    {% elif user_role == 'admin' %}
        {{ return(custom_schema_name or domain ~ '_data') }}

    {% else %}
        {{ exceptions.raise_compiler_error("Unknown or unauthorized role: " ~ user_role) }}
    {% endif %}
{% endmacro %}

---

📁 Structure: models/

models/
  finance/
    dim_revenue.sql
  marketing/
    dim_campaigns.sql

---

🧪 Usage via dbt targets (in profiles.yml):

my_profile:
  target: finance_dev
  outputs:
    finance_dev:
      type: snowflake
      role: finance_analyst
      schema: default_schema  # Will be overridden by macro

---

✅ What Happens?

User Role	Model Path	Resolved Schema	Allowed?
finance_analyst	models/finance/model.sql	finance_data	✅ Yes
finance_analyst	models/marketing/...	❌ Error	❌ No
admin	models/marketing/...	marketing_data	✅ Yes

---

🔐 Why This Is Powerful for Governance

Feature	Benefit
🧑‍💼 Role-based logic	Enforce policies per user type or team
🗂️ Domain-based separation	Keeps finance data separate from marketing data
🧪 Compile-time errors	Blocks unauthorized builds early
🧩 Cross-domain logic possible	Admins can access all, analysts are restricted
📈 Auditable and visible	Easy to inspect schema assignment per model
♻️ Easy to update in one place	All policies live in one macro, version-controlled

---

🧼 Summary

• Custom schema macros let you dynamically set the schema a model builds into.
• Combined with target.role or other metadata, you can implement fine-grained governance rules.
• This enables controlled, auditable, and maintainable access policies across domains.

---