dbtCloud - model management architecture option 1

dbt Cloud Option A

This article describes a production architecture for Git/CI-first Python code generation for dbt Cloud. It covers repository design, generation workflow, Azure DevOps examples, Snowflake metadata discovery, validation, pull requests, deployment, security, testing, rollback, observability, and implementation patterns.

1. Executive Summary

Option A treats Python-based dbt generation as a software engineering build step owned by the Git/CI/CD workflow. The generator does not need dbt Cloud to create files. Instead, CI checks out the repository, runs Python, generates deterministic dbt source files, validates them, creates a Pull Request, and merges approved changes. dbt Cloud then consumes the resulting Git commit and performs the normal dbt deployment.

Developer / Scheduled CI / Metadata Change
                    |
                    v
             Git CI/CD Pipeline
             |                  |
             v                  v
   Snowflake Metadata     Python Generator
             |                  |
             v                  v
       Generated dbt Code
                    |
                    v
           Validation / Testing
                    |
                    v
                 Git Diff
                    |
                    v
           Pull Request / Review
                    |
                    v
                  MAIN
                    |
                    v
              dbt Cloud
                    |
                    v
                dbt build
                    |
                    v
                Snowflake

This is generally the cleanest production model when generated SQL/YAML should be versioned, reviewed, auditable, reproducible, and deployable through the same controls as manually written dbt code.

2. Option A vs. Option B

3. Design Principles

• Generated dbt files are source code and should be version controlled.
• The generator should be deterministic: identical metadata/configuration should produce identical files.
• Generated and manually maintained code should be separated.
• CI should fail before merge if generated dbt code cannot parse or compile.
• Production deployments should use an immutable Git commit or merge SHA.
• Credentials should not be embedded in Python, YAML, or generated files.
• The generator should use least-privilege read access to metadata.
• Direct pushes to protected production branches should normally be avoided.
• Generation, validation, approval, and deployment should leave an auditable trail.

4. Recommended Repository Layout

my-dbt-project/
|+-- dbt_project.yml
+-- packages.yml
+-- selectors.yml
+-- README.md
+-- scripts/
|   +-- generate_models.py
|   +-- metadata.py
|   +-- validation.py
|   +-- cleanup_generated.py
+-- generator/
|   +-- config/
|   |   +-- domains.yml
|   |   +-- naming.yml
|   |   +-- tests.yml
|   +-- templates/
|       +-- staging.sql.j2
|       +-- source.yml.j2
|       +-- schema.yml.j2
+-- models/
|   +-- manual/
|   |   +-- marts/
|   +-- generated/
|       +-- sales/
|       +-- finance/
|       +-- crm/
+-- tests/
|   +-- generator/
+-- .github/ or azure-pipelines/
+-- azure-pipelines.yml

A dedicated generated directory establishes a clear ownership boundary. A useful rule is: files below models/generated are overwritten by automation; files below models/manual are owned by developers.

5. Generation Lifecycle

1. Checkout repository
2. Resolve generator configuration
3. Authenticate to Snowflake, if metadata-driven
4. Read databases, schemas, tables, and columns
5. Normalize metadata
6. Apply naming and modeling rules
7. Render SQL/YAML from templates
8. Remove obsolete generated files
9. Run Python unit tests
10. Run dbt deps
11. Run dbt parse
12. Run dbt compile
13. Optionally run dbt build in the CI environment
14. Inspect Git diff
15. Enforce generation policies
16. Create branch
17. Create Pull Request
18. Review and approve
19. Merge to main
20. dbt Cloud deploys the merge commit

6. Minimal Python Generator

A small deterministic generator can start with a static table definition:

from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
OUTPUT = ROOT / "models" / "generated" / "sales"
TABLES = ["CUSTOMERS", "ORDERS", "PRODUCTS"]

OUTPUT.mkdir(parents=True, exist_ok=True)
for table in sorted(TABLES):
    model = f"stg_{table.lower()}"
    path = OUTPUT / f"{model}.sql"
    sql = f"""-- AUTO-GENERATED. DO NOT EDIT.
select *
from {{{{ source('raw_sales', '{table.lower()}') }}}}
"""
    path.write_text(sql, encoding="utf-8")
    print(f"Generated {path}")

Expected output:

models/generated/sales/
+-- stg_customers.sql
+-- stg_orders.sql
+-- stg_products.sql

7. Generating schema.yml

The generator can create dbt documentation and tests together with SQL models.

from pathlib import Path
import yaml

models = []
for table in ["customers", "orders"]:
    models.append({
        "name": f"stg_{table}",
        "description": f"Generated staging model for {table}.",
        "columns": [
            {
                "name": f"{table[:-1]}_id" if table.endswith("s") else f"{table}_id",
                "tests": ["not_null", "unique"]
            }
        ]
    })

document = {"version": 2, "models": models}
Path("models/generated/sales/schema.yml").write_text(
    yaml.safe_dump(document, sort_keys=False), encoding="utf-8"
)

In a mature generator, tests should come from metadata/configuration rather than guessing primary keys. For example, a YAML policy file can explicitly define uniqueness, accepted values, relationships, owners, PII classification, freshness expectations, and tags.

8. Template-Based Generation with Jinja

Keep SQL patterns outside Python by using templates.

Template: generator/templates/staging.sql.j2

-- AUTO-GENERATED. DO NOT EDIT.
select
{% for column in columns %}
    {{ column }}{% if not loop.last %},{% endif %}
{% endfor %}
from {{ "{{" }} source('{{ source_name }}', '{{ table_name }}') {{ "}}" }}

Python rendering

from jinja2 import Environment, FileSystemLoader
from pathlib import Path

env = Environment(
    loader=FileSystemLoader("generator/templates"),
    trim_blocks=True,
    lstrip_blocks=True,
)
template = env.get_template("staging.sql.j2")
sql = template.render(
    source_name="raw_sales",
    table_name="customers",
    columns=["customer_id", "name", "email", "created_at"],
)
Path("models/generated/sales/stg_customers.sql").write_text(
    sql + "\n", encoding="utf-8"
)

9. Snowflake Metadata-Driven Generation

For enterprise use, the generator can discover objects from Snowflake INFORMATION_SCHEMA or approved metadata views. The Snowflake identity should normally be read-only.

import snowflake.connector

conn = snowflake.connector.connect(
    account="my_account",
    user="DBT_GENERATOR_SVC",
    authenticator="externalbrowser",  # example only; CI should use non-interactive auth
)
cur = conn.cursor()
cur.execute("""
select
    table_schema,
    table_name,
    column_name,
    data_type,
    ordinal_position
from RAW.INFORMATION_SCHEMA.COLUMNS
where table_schema = 'SALES'
order by table_name, ordinal_position
""")
rows = cur.fetchall()

In CI, use a non-interactive authentication mechanism appropriate to your environment, such as key-pair authentication or an approved OAuth/workload identity design. Do not use interactive browser authentication.

10. Metadata Normalization

Do not render directly from raw query rows. First normalize metadata into an internal model. This keeps Snowflake-specific discovery separate from dbt-specific generation.

metadata = {
    "schema": "SALES",
    "tables": {
        "CUSTOMERS": {
            "columns": [
                {"name": "CUSTOMER_ID", "type": "NUMBER"},
                {"name": "EMAIL", "type": "VARCHAR"},
                {"name": "CREATED_AT", "type": "TIMESTAMP_NTZ"},
            ]
        }
    }
}

This normalized representation can later be populated from Snowflake, a data catalog, YAML configuration, or another SaaS platform without rewriting the rendering layer.

11. Naming Rules

Centralize naming behavior rather than scattering it throughout templates.

RAW.SALES.CUSTOMERS
        |
        v source name: raw_sales
        |
        v model name: stg_sales__customers
        |
        v models/generated/sales/stg_sales__customers.sql

Example policy file:

version: 1
staging:
  prefix: "stg"
  include_schema: true
  lower_case: true
domains:
  SALES:
    folder: "sales"
    source_name: "raw_sales"

12. Cleaning Obsolete Generated Files

A generator must handle deleted or renamed source objects. Otherwise stale dbt models remain in Git.

from pathlib import Path

output = Path("models/generated/sales")
expected = {
    "stg_customers.sql",
    "stg_orders.sql",
}
for path in output.glob("*.sql"):
    if path.name not in expected:
        print(f"Removing obsolete generated file: {path}")
        path.unlink()

Restrict cleanup strictly to the generated directory. Never recursively delete from the entire models tree.

13. Deterministic Output

• Sort tables and columns before rendering.
• Use stable YAML key ordering.
• Do not embed generation timestamps inside every generated file unless required.
• Normalize newline and encoding behavior.
• Pin generator dependencies.
• Keep templates versioned in the same repository or a versioned generator package.
• Run the generator twice in CI and optionally verify that the second run creates no diff.

A useful idempotency check is:

python scripts/generate_models.py
git diff --exit-code || true
python scripts/generate_models.py
git diff --exit-code

The second generation should not introduce additional changes when the inputs have not changed.

14. Azure DevOps Pipeline - Basic Example

trigger: none

pool:
  vmImage: ubuntu-latest

steps:
- checkout: self
  persistCredentials: true
  fetchDepth: 0
- task: UsePythonVersion@0
  inputs:
    versionSpec: '3.12'
- script: |
    python -m pip install --upgrade pip
    pip install -r requirements.txt
  displayName: Install Python dependencies
- script: |
    python scripts/generate_models.py
  displayName: Generate dbt source code
- script: |
    dbt deps
    dbt parse
    dbt compile
  displayName: Validate generated dbt project
- script: |
    git status --short
    git diff -- models/generated
  displayName: Show generated changes

15. Azure DevOps - Recommended Multi-Stage Pipeline

stages:
- stage: Generate
  jobs:
  - job: GenerateCode
    steps:
    - checkout: self
    - script: python scripts/generate_models.py
    - publish: $(Build.SourcesDirectory)
      artifact: generated-repo
- stage: Validate
  dependsOn: Generate
  jobs:
  - job: ValidateDBT
    steps:
    - download: current
      artifact: generated-repo
    - script: dbt deps
    - script: dbt parse
    - script: dbt compile
- stage: PullRequest
  dependsOn: Validate
  condition: succeeded()
  jobs:
  - job: CreatePR
    steps:
    - script: |
        echo "Create branch, commit generated changes and open PR"

For a real implementation, the PR stage should use the CI platform's supported Git/API authentication rather than embedding a personal access token in source code.

16. Pull Request Design

A generated Pull Request should summarize exactly what changed.

Title: chore(dbt): regenerate SALES models
Generator: dbt-generator 2.3.0
Input: Snowflake RAW.SALES
Summary:
+ 4 models created
~ 2 models modified
- 1 obsolete model removed
Validation:
PASS python unit tests
PASS dbt deps
PASS dbt parse
PASS dbt compile
Generated paths: models/generated/sales/

For high-governance environments, require CODEOWNERS approval for generator templates/configuration and separate approval for changes to generated production models.

17. CI Validation Layers

18. State-Based dbt Validation

For larger projects, avoid rebuilding everything for every generated PR. dbt state comparison can be used to focus CI on modified models and downstream dependencies when an appropriate previous manifest is available.

dbt build \
  --select state:modified+ \
  --state ./previous-artifacts

Keep full scheduled validation as an additional control; state-based CI is an optimization, not necessarily a replacement for periodic full builds.

19. dbt Cloud Deployment

After merge, dbt Cloud should deploy the exact approved Git revision. A typical production job may execute:

dbt deps
dbt build --target prod

The deployment job should not regenerate source files. Its responsibility is to execute the already approved dbt project.

20. Example End-to-End Scenario

Assume a new Snowflake table RAW.SALES.PAYMENTS appears and the SALES domain is configured for automatic staging generation.

Snowflake RAW.SALES.PAYMENTS
        |
        v
Scheduled Azure DevOps generation pipeline
        |
        v
Metadata discovery detects PAYMENTS
        |
        v
Generator creates:
  models/generated/sales/stg_sales__payments.sql
  models/generated/sales/schema.yml changes
        |
        v
dbt parse / compile
        |
        v
Pull Request
        |
        v
Reviewer approves
        |
        v
Merge to main
        |
        v
dbt Cloud production job
        |
        v
Snowflake staging model is created

21. Example Generated SQL

-- AUTO-GENERATED. DO NOT EDIT.
select
    payment_id,
    customer_id,
    order_id,
    amount,
    currency,
    created_at
from {{ source('raw_sales', 'payments') }}

22. Example Generated sources.yml

version: 2
sources:
  - name: raw_sales
    database: RAW
    schema: SALES
    tables:
      - name: customers
      - name: orders
      - name: payments

23. Example Generated schema.yml

version: 2
models:
  - name: stg_sales__payments
    description: Generated staging model for RAW.SALES.PAYMENTS.
    columns:
      - name: payment_id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null

24. Security Architecture

Azure DevOps Workload Identity / Service Identity
                 |                         |
                 v                         v
             Git Repo                 Snowflake
                 |                 DBT_CODE_GENERATOR
                 |                    permissions
                 |                 read-only metadata
                 v                         |
              branch + PR                  |
                 |                         v
                 v                    dbt Cloud
              dbt Cloud             deployment identity
                 |                         |
                 v                         v
          Snowflake PROD

• Separate the generator identity from the dbt production execution identity.
• Give the generator only metadata/data access required for generation.
• Use protected branches and mandatory PR checks.
• Do not let generated code contain credentials, tokens, private keys, or connection strings.
• Prefer short-lived/federated credentials where supported.
• Store unavoidable secrets in an approved secret manager.
• Restrict who can modify templates and generator policy configuration.
• Log generation runs and preserve CI artifacts needed for audit.

25. Snowflake Role Example

The exact grants depend on the metadata required, but conceptually use a dedicated role instead of a broad administrative role.

Role: DBT_CODE_GENERATOR

• USAGE on approved databases/schemas
• Metadata visibility required by the generator
• SELECT only on approved metadata/configuration views when necessary
• No CREATE/DROP/ALTER production objects
• No ACCOUNTADMIN/SECURITYADMIN ownership

26. Configuration-Driven Governance

A central policy file can control which schemas are generated and how.

version: 1
domains:
  sales:
    database: RAW
    schema: SALES
    enabled: true
    owner: data-sales
    tags:
      - generated
      - sales
    include:
      - CUSTOMERS
      - ORDERS
      - PAYMENTS
    exclude:
      - TEMP_*
      - BACKUP_*
    tests:
      primary_key:
        not_null: true
        unique: true

27. Manual Overrides

Avoid editing generated SQL directly. If exceptions are needed, model them explicitly as configuration or extension points.

generator/config/overrides.yml

tables:
  RAW.SALES.CUSTOMERS:
    rename_columns:
      CUST_ID: customer_id
    exclude_columns:
      - INTERNAL_LOAD_ID
    tests:
      customer_id:
        - not_null
        - unique

28. Generated File Header

Every generated file can include a warning and generator metadata:

-- AUTO-GENERATED FILE - DO NOT EDIT DIRECTLY.
-- Generator: dbt-codegen 2.3.0
-- Source object: RAW.SALES.CUSTOMERS
-- Configuration: generator/config/domains.yml
--
-- Change the generator/template/configuration and regenerate.

Avoid volatile timestamps in the header if they would cause every file to change on every generation.

29. Testing the Generator

Treat the generator itself as production software.

def test_model_name():
    assert model_name("SALES", "CUSTOMERS") == "stg_sales__customers"


def test_excluded_table():
    assert should_generate("TEMP_CUSTOMERS") is False


def test_columns_are_stable():
    cols = ["B", "A"]
    assert normalize_columns(cols) == ["A", "B"]

Golden-file tests are especially useful: provide fixed metadata input and compare generated SQL/YAML against known-good files committed under tests/fixtures.

30. Failure Handling

• If Snowflake metadata retrieval fails, fail the generation pipeline; do not generate partial output silently.
• If dbt parse or compile fails, do not create or merge the production PR.
• If the generator proposes unexpectedly large deletion counts, require manual approval or stop the pipeline.
• If credentials fail, surface the authentication error without printing secrets.
• If a PR already exists for the same generation branch, update it or use a deterministic branch strategy.
• Preserve logs describing input scope, generator version, object counts, validation results, and changes.

31. Destructive Change Guardrail

One important control is to stop automatic generation when the change set is suspiciously destructive.

MAX_DELETE_PERCENT = 20

if existing_count:
    delete_percent = deleted_count * 100 / existing_count
    if delete_percent > MAX_DELETE_PERCENT:
        raise RuntimeError(
            f"Refusing generation: {delete_percent:.1f}% of generated models would be deleted"
        )

32. Rollback

Because generated code is committed to Git, rollback is straightforward at the source-code level.

Bad generation merged
        |
        v
Revert Git commit / PR
        |
        v
main returns to previous generated state
        |
        v
dbt Cloud deploys reverted commit

Database rollback semantics depend on what dbt materializations changed. Source-code rollback and physical data/object rollback should be considered separately.

33. Observability and Audit

Record at least the following for every generation run:

• Pipeline/run ID.
• Git source commit used by the generator.
• Generator version.
• Configuration version or commit.
• Snowflake account/database/schema scope queried.
• Objects discovered.
• Files created, modified, and deleted.
• dbt parse/compile/build results.
• PR number and merge commit.
• Identity that initiated/approved the change.

34. Scheduling Patterns

Option A supports several triggers without coupling generation to dbt Cloud:

Manual:       Developer -> Run pipeline
Scheduled:    Every night -> Metadata scan -> PR if changes exist
Repository:   Change generator/config -> CI regeneration
Event driven: Metadata/catalog event -> CI pipeline

For many teams, scheduled generation plus PR creation is the simplest way to detect newly created source tables.

35. No-Change Behavior

Do not create empty Pull Requests.

python scripts/generate_models.py
if git diff --quiet:
    echo "No generated changes."
    exit 0
else
    create_branch_and_pull_request
fi

36. Branch Strategy

main
+-- feature/manual-dbt-change
+-- generated/sales/<run-id>
+-- generated/finance/<run-id>

Domain-specific generation branches reduce collisions if multiple generation processes can run concurrently.

37. Monorepo Considerations

If one repository contains several dbt domains, scope generation and CI validation to the affected domain where practical, while preserving periodic full-project validation.

models/
+-- generated/
|   +-- sales/
|   +-- finance/
|   +-- risk/
+-- manual/
    +-- sales/
    +-- finance/
    +-- risk/

38. Package the Generator

When multiple dbt repositories need the same generator, move reusable logic into a versioned internal Python package.

company-dbt-generator
|  +-- metadata discovery
|  +-- naming rules
|  +-- templates
|  +-- validation
|  +-- CLI
|         |
|         v
python -m company_dbt_generator generate --domain sales

Each repository can pin a generator version, which makes generation reproducible and allows controlled upgrades.

39. Example requirements.txt

dbt-snowflake==<approved-version>
snowflake-connector-python==<approved-version>
Jinja2==<approved-version>
PyYAML==<approved-version>
pytest==<approved-version>

Use versions approved by your organization and update them through dependency management rather than leaving production generator dependencies unpinned.

40. Production Reference Architecture

+-----------------------+
| Git Repository        |
| dbt + generator code  |
+-----------+-----------+
            |
            | checkout
            v
+-----------------------+
| Azure DevOps CI       |
+-----------+-----------+
            |
   +--------+--------+----------------+
   |                 |                |
   v                 v                v
Generator       Config /         Jinja Templates
Config          Python Package
   |                 |                |
   +-----------------+----------------+
                     v
          +-----------------------+
          | Metadata Discovery    |
          +-----------+-----------+
                      |
                      v
          +-----------------------+
          | Snowflake             |
          | read-only metadata    |
          +-----------+-----------+
                      |
                      v
          +-----------------------+
          | Render generated dbt |
          | source code           |
          +-----------+-----------+
                      |
       +--------------+--------------+
       |              |              |
       v              v              v
    pytest        dbt parse      dbt compile
       |              |              |
       +--------------+--------------+
                      v
               Policy checks
                      |
                      v
                   Git diff
                      |
                      v
                Pull Request
                      |
                      v
                  Approval
                      |
                      v
                    main
                      |
                      v
                 dbt Cloud
                      |
                      v
                  dbt build
                      |
                      v
                 Snowflake

41. Recommended Implementation Phases

1. Foundation: Create generated/manual folder separation, a basic generator, local tests, and dbt parse validation.
2. Metadata: Read approved Snowflake metadata and normalize it into an internal Python model.
3. Templates: Introduce Jinja templates, YAML generation, naming rules, and override configuration.
4. CI: Automate generation and validation in Azure DevOps; produce diffs and artifacts.
5. PR Automation: Create branches and Pull Requests only when changes exist; protect main.
6. Governance: Add deletion guardrails, CODEOWNERS, policy checks, secret scanning, and audit metadata.
7. dbt Cloud Deployment: Deploy only approved merge commits and separate generator credentials from runtime credentials.
8. Scale: Package reusable generator logic, add domain concurrency, state-based CI, observability, and scheduled discovery.

42. Final Recommended Flow

SOURCE OF TRUTH
================
Git Repository
       |
       v
Azure DevOps Pipeline
       |
       +-------------------+
       |                   |
       v                   v
Python Generator    Snowflake Metadata
       |                   |
       +---------+---------+
                 v
          Generated Code
                 |
                 v
     Tests + dbt Validation
                 |
                 v
           Pull Request
                 |
                 v
        Review / Approval
                 |
                 v
                MAIN
                 |
                 v
             dbt Cloud
                 |
                 v
              dbt build
                 |
                 v
             Snowflake

The central architectural decision is that the generated files become normal, reviewed, immutable source code before production execution. dbt Cloud is therefore not responsible for persisting dynamically generated files; it deploys an already validated Git revision.