dbt Cloud Option B
External Python Code Generation Pipeline
Architecture, implementation flow, Azure DevOps example, Snowflake metadata-driven generation, and security considerations.
1. Overview
Option B keeps dbt Cloud as the orchestration entry point while running the Python code generator in a controlled external environment such as Azure DevOps, GitHub Actions, GitLab CI, AWS CodeBuild, or a containerized service.
The key principle is that generated dbt files are source code. They should normally be persisted in Git, reviewed and validated, rather than created only inside an ephemeral dbt execution filesystem.
dbt Cloud
|
| trigger
v
External CI/CD / Generator
|
+-- Clone dbt Git repository
+-- Run Python generator
+-- Generate folders/files
+-- dbt parse / compile
+-- Git diff
+-- Commit / Pull Request
|
v
Git Repository
|
v
dbt Cloud Job
|
v
Snowflake
2. Why use an external generator?
A Python script that creates .sql, .yml, macros, folders, tests, or documentation is performing source-code generation. Running it externally provides a persistent Git workspace, controlled credentials, repeatable validation, auditability, and a clean approval workflow.
Python Generator
|
v
Generated dbt code
|
v
Validation
|
v
Git diff
|
v
Pull Request
|
v
Review / approval
|
v
Merge
|
v
dbt Cloud deployment
3. Main components
- dbt Cloud - orchestration and deployment execution.
- CI/CD platform - Azure DevOps, GitHub Actions, GitLab CI, or equivalent.
- Python generator - reads configuration/metadata and creates dbt source files.
- Git repository - persistent system of record for generated and manually maintained dbt code.
- Snowflake - optional metadata source for automatic model generation and the target data platform.
4. Recommended repository structure
my-dbt-project/ |+-- dbt_project.yml +-- packages.yml +-- scripts/ | +-- generate_models.py +-- templates/ | +-- staging_model.sql.j2 +-- models/ | +-- manual/ | | +-- customer_report.sql | | | +-- generated/ +-- azure-pipelines.yml
Keep manually maintained models separate from generated models. Developers should normally not edit files under models/generated directly; changes should be made to generator logic, templates, or metadata.
5. Small Python generator example
The following script creates three staging models:
from pathlib import Path
PROJECT_ROOT = Path(__file__).resolve().parent.parent
OUTPUT_DIR = PROJECT_ROOT / "models" / "generated"
tables = [
"CUSTOMERS",
"ORDERS",
"PRODUCTS",
]
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
for table in tables:
model_name = f"stg_{table.lower()}.sql"
content = f"""select
*
from {{{{ source('raw', '{table.lower()}') }}}}"""
output_file = OUTPUT_DIR / model_name
output_file.write_text(
content.strip() + "\n",
encoding="utf-8"
)
print(f"Generated: {output_file}")
Running the generator:
python scripts/generate_models.py
produces:
models/generated/ +-- stg_customers.sql +-- stg_orders.sql +-- stg_products.sql
6. Azure DevOps pipeline example
Azure DevOps can provide the external execution environment. The pipeline checks out the repository, runs the generator, installs dbt, validates the project, and shows the resulting Git changes.
trigger: none
pool:
vmImage: ubuntu-latest
steps:
- checkout: self
persistCredentials: true
- task: UsePythonVersion@0
inputs:
versionSpec: '3.12'
- script: |
pip install dbt-snowflake
displayName: Install dependencies
- script: |
python scripts/generate_models.py
displayName: Generate dbt models
- script: |
dbt deps
dbt parse
displayName: Validate dbt project
- script: |
git status
git diff
displayName: Show generated changes
7. Pull Request workflow
For production, avoid allowing the generator to push directly to main. Create a dedicated branch and Pull Request so that generated changes can be reviewed and policy checks can run before deployment.
Python | v Generate files | v dbt parse / dbt compile | v Security and policy checks | v Create generated/<timestamp> branch | v Create Pull Request | v Human or automated approval | v Merge to main
Example Pull Request summary:
Automated dbt model generation Generator version: 1.4.2 Source: Snowflake RAW.SALES Generated models: 12 Modified models: 3 Deleted models: 0 dbt parse: PASS dbt compile: PASS
8. Separation of generation and deployment
Generation flow
dbt Cloud / User
|
v
Trigger external pipeline
|
v
Azure DevOps
|
v
Python generator
|
v
Generate dbt code
|
v
Pull Request
|
v
Merge
Deployment flow
Git main
|
v
dbt Cloud
|
+-- dbt deps
+-- dbt parse
+-- dbt build
+-- dbt test
|
v
Snowflake
This separation is deliberate: generation changes source code, while dbt Cloud deployment executes approved source code.
9. Metadata-driven Snowflake example
Instead of hardcoding tables, the generator can query Snowflake metadata and create dbt models dynamically.
import snowflake.connector
connection = snowflake.connector.connect(
account="...",
user="...",
authenticator="...",
)
cursor = connection.cursor()
cursor.execute("""
SELECT table_name
FROM RAW.INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'SALES'
AND table_type = 'BASE TABLE'
""")
tables = [row[0] for row in cursor.fetchall()]
The resulting process can look like this:
Snowflake RAW.SALES | +-- CUSTOMER +-- ORDER +-- PRODUCT +-- PAYMENT | v Python generator | v models/generated/sales/ +-- stg_customer.sql +-- stg_order.sql +-- stg_product.sql +-- stg_payment.sql +-- schema.yml
10. Security considerations
Do not embed credentials in generate_models.py or the pipeline YAML. Prefer workload identity/OIDC where supported, and use a secret manager such as Azure Key Vault for credentials that cannot be eliminated.
Use a dedicated generator identity with least privilege:
Generator Identity
|
+-- Git
| +-- Read repository
| +-- Create branch
| +-- Create Pull Request
+-- Snowflake
| +-- Metadata read-only access
+-- dbt Cloud
+-- Only permissions required for orchestration
In Snowflake, use a dedicated role such as DBT_CODE_GENERATOR with only the metadata/data privileges required by the generator. Avoid ACCOUNTADMIN, SECURITYADMIN, or SYSADMIN unless there is a specifically justified requirement.
11. Recommended production architecture
+---------------+
| dbt Cloud |
+-------+-------+
|
Trigger
|
v
+---------------+
| Azure DevOps |
| Pipeline |
+---------+-------+
|
+---------+----------+
| |
v v
Python Generator Snowflake
| |
+-------+-------+ |
| | | |
v v v v
Config metadata Jinja YAML
| | | |
+-------+-------+------------+
|
v
models/generated/
|
v
dbt parse
|
v
dbt compile
|
v
policy checks
|
v
Git Diff
|
v
Pull Request
|
v
Approval
|
v
MAIN
|
v
dbt Cloud
|
v
dbt build
|
v
Snowflake
12. Suggested evolution
A mature implementation can generate more than SQL models. The same framework can generate schema.yml, sources.yml, tests, documentation, tags, owners, access configuration, naming conventions, and policy metadata. At that point the Python component becomes a metadata-driven dbt code-generation framework.
13. Minimal end-to-end example
A user or orchestration event starts an Azure DevOps pipeline. The pipeline checks out the dbt repository, runs generate_models.py, validates the generated project with dbt parse, and opens a Pull Request. After approval and merge, the normal dbt Cloud deployment job runs dbt build against Snowflake.
- Trigger generator pipeline
git checkoutpython scripts/generate_models.pydbt depsdbt parsegit diff- Create branch + Pull Request
- Approve + merge
- dbt Cloud deployment starts
dbt build