Alternatives, Secrets, and Multi-Environment dbt
Cosmos versus dbt Cloud jobs and hand-rolled commands, plus target mapping, version pinning, and secrets across dev, CI, and prod.
Ten chapters in, Cosmos has done a lot of quiet work for us: it read the dbt manifest, turned every model and test into an Airflow task, wired the dependencies, mapped the run states back onto the grid, and let us slice the project three ways from one codebase. It’s easy to forget it was ever a choice. So before we close the series, it’s worth stepping back and asking the question a good engineer asks about any dependency: what am I actually buying, what is it costing me, and what would I use instead? Then we’ll spend the rest of this chapter on the three things that separate a demo DAG from one you’d trust in production — running the same project against dev, CI, and prod without editing code; keeping the warehouse credentials out of the repo and out of the process table; and passing runtime parameters into a dbt run on purpose.
The landscape: three other ways to run dbt
Cosmos is not the only way to get a dbt project running under Airflow. There are three alternatives you’ll meet in the wild, and each one moves the complexity somewhere different.
Plain BashOperator. The simplest possible thing works:
from airflow.providers.standard.operators.bash import BashOperator
dbt_build = BashOperator(
task_id="dbt_build",
bash_command=(
"cd /usr/local/airflow/include/dbt/bookshop && "
"dbt build --target prod --profiles-dir ."
),
)One task, one command, the whole project rebuilt as an atom. There’s nothing wrong with this for a small project — it’s honest, it’s transparent, and it fails exactly the way dbt build fails on your laptop. What you don’t get is the entire premise of this series: the dbt graph never becomes the Airflow graph. Fifty models are one green box. When int_order_payments fails you retry all fifty, because Airflow’s unit of retry is the task and the task is the whole build. There’s no per-model grid, no per-model timing, no “retry just the branch that broke,” no lineage in the UI. You’ve orchestrated dbt the way you’d orchestrate a shell script, which is fine right up until the moment you need to know which model died at 3 a.m. and can’t.
A hand-rolled manifest parser. The next step up is to do what Cosmos does, yourself: run dbt ls or read target/manifest.json, walk the nodes, and generate a task per model in a loop. People build this — it’s a reasonable weekend project and it teaches you exactly how Cosmos works under the hood (Chapter 7 walked that internal machinery). The trouble is that the weekend project is the easy 80%. The hard 20% is everything Cosmos has already solved and you’ll rediscover one bug at a time: parsing the manifest across dbt versions when its schema changes, mapping dbt result states back onto Airflow states, handling tests as gates versus nodes, source freshness, the LoadMode question of when to parse (at DAG-parse time or run time — a real scheduler-performance concern), selectors, and multi-target profiles. You will, eventually, have rebuilt Cosmos, minus the part where a community maintains it. The manifest-parser approach is worth understanding and rarely worth shipping.
DbtCloudRunJobOperator. The third alternative isn’t a way to run dbt from Airflow at all — it’s a way to trigger dbt Cloud from Airflow. If your organization already pays for dbt Cloud, dbt runs there: dbt Cloud owns the environments, the credentials, the scheduling, the artifacts, and the run history. In that world Airflow’s job is not to execute dbt but to kick off a dbt Cloud job at the right moment in a larger pipeline and wait for it:
from airflow.providers.dbt.cloud.operators.dbt import DbtCloudRunJobOperator
run_bookshop = DbtCloudRunJobOperator(
task_id="run_bookshop_prod",
dbt_cloud_conn_id="dbt_cloud_default",
job_id=180453,
check_interval=30,
timeout=3600,
)That operator calls the dbt Cloud API, starts job 180453, and polls until it finishes — no dbt Core in your Airflow image, no profile to manage, no adapter to install. This is the right answer when dbt Cloud is already your system of record. What you give up is exactly what Cosmos gives you: the models are opaque to Airflow again, because they’re running in a different system. You get one “did the dbt Cloud job pass” signal, and if you want per-model detail you look in dbt Cloud, not the Airflow grid.
So why Cosmos — and what does it cost?
Line those up and the shape of the decision is clear. Use BashOperator when the project is small and you genuinely don’t need the dbt graph to become the Airflow graph. Use DbtCloudRunJobOperator when dbt Cloud already owns scheduling and you want Airflow to trigger it, not replace it. Use a hand-rolled parser essentially never — understand it, don’t ship it. Use Cosmos when you want Airflow itself to own model-level visibility: the per-model grid, per-model retries, tests as real gates, lineage in the UI, source freshness at the door, and selectable slices of one project. That’s most teams running dbt Core who want Airflow to be the orchestrator, and it’s why this series is built on it.
Cosmos isn’t free, though, and it’s worth naming the costs so nobody’s surprised. It parses your project to build the DAG, which adds work at DAG-parse or run time and makes LoadMode and manifest caching (Chapter 7) something you have to think about on a large project. It couples you to three moving version lines at once — Airflow, Cosmos, and dbt — and cross-tool integrations break at the seams. And it hides machinery: when something goes wrong the stack trace runs through Cosmos’s rendering layer, not just dbt’s, so you need to understand both. Those are real costs. They’re the right ones to pay for the visibility, but pay them with your eyes open.
Pin your versions — the unglamorous prerequisite
Which brings up a footgun buried in the earlier chapters. The requirements.txt we’ve been sketching said, in effect, astronomer-cosmos with no version. That’s fine on the day you write it and a time bomb three months later, because Cosmos, Airflow, and dbt all release independently and the compatibility surface between them is exactly where things break. Cosmos support for Airflow 3.x and its Task SDK (from airflow.sdk import ..., the import surface this whole series has used) arrived in specific Cosmos releases — an unpinned install will one day pull a version that predates or postdates the combination you tested, and the failure won’t say “version mismatch,” it’ll say something inscrutable about a manifest or an import.
Pin everything that participates in the seam:
# requirements.txt — pin the whole cross-tool surface
apache-airflow==3.3.0
astronomer-cosmos==1.11.0
dbt-core==1.11.12
dbt-snowflake==1.11.0
apache-airflow-providers-snowflake==6.6.0
apache-airflow-providers-dbt-cloud==4.4.0The two numbers people forget are the dbt adapter (dbt-snowflake here — it version-locks against dbt-core) and the provider packages, because in Airflow 3.x providers ship on their own cadence and a feature like Snowflake key-pair auth (below) exists only from a specific provider version onward. Pin them, and upgrade them deliberately in a branch where you can actually watch the DAG re-render, rather than discovering the incompatibility when the scheduler quietly stops parsing your file. This is the least interesting paragraph in the series and the one most likely to save you a Saturday.
Multi-environment: one project, three targets
Everything so far ran against a single target. Real pipelines run the same dbt project against at least three: dev (your sandbox schema, cheap warehouse), ci (a throwaway schema built fresh on every pull request, torn down after), and prod (the real schema the business reads, a bigger warehouse, tighter roles). The models are identical across all three. What changes is where they land and who’s allowed to build them — and dbt already has the concept for that. It’s the target.
A dbt profiles.yml is a set of named targets under one profile:
# profiles.yml
bookshop:
target: dev
outputs:
dev:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
role: TRANSFORMER_DEV
warehouse: WH_DEV
database: ANALYTICS_DEV
schema: dbt_dev
threads: 4
ci:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
role: TRANSFORMER_CI
warehouse: WH_CI
database: ANALYTICS_CI
schema: "{{ env_var('DBT_CI_SCHEMA', 'ci_default') }}"
threads: 8
prod:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
role: TRANSFORMER_PROD
warehouse: WH_PROD
database: ANALYTICS
schema: analytics
threads: 12Same models, three destinations, distinct roles and warehouses. The dev target writes to ANALYTICS_DEV.dbt_dev on a small warehouse; prod writes to ANALYTICS.analytics on a big one under a role that dev doesn’t hold. The CI schema is itself an env var so each pull request can get its own (ci_pr_1487) and drop it when the PR closes.
Telling Cosmos which target — two ways
There are two ways to hand this to Cosmos, and the choice is the real decision.
The first is a checked-in profiles.yml that you point Cosmos at with profiles_yml_filepath, letting target_name select the environment:
from cosmos import ProfileConfig
profile_config = ProfileConfig(
profile_name="bookshop",
target_name="prod",
profiles_yml_filepath="/usr/local/airflow/include/dbt/bookshop/profiles.yml",
)target_name is the whole multi-environment mechanism in one string. The dev DAG constructs its ProfileConfig with target_name="dev", the prod DAG with target_name="prod", and each resolves to a different outputs: block — different database, schema, role, warehouse — from the same file. Nothing in the models changes; the target does. A tidy pattern is to compute the target from the deployment environment rather than hardcode it, so one DAG file behaves correctly wherever it’s deployed:
import os
DEPLOY_ENV = os.environ["DEPLOYMENT_ENVIRONMENT"] # "dev" | "ci" | "prod"
profile_config = ProfileConfig(
profile_name="bookshop",
target_name=DEPLOY_ENV,
profiles_yml_filepath="/usr/local/airflow/include/dbt/bookshop/profiles.yml",
)Now target_name → environment mapping is literally an environment variable set per deployment. The dev Astro deployment sets DEPLOYMENT_ENVIRONMENT=dev; prod sets prod; the DAG code is identical in both.
The second way skips the file entirely and builds the profile from an Airflow connection via a profile mapping:
from cosmos import ProfileConfig
from cosmos.profiles import SnowflakeUserPasswordProfileMapping
profile_config = ProfileConfig(
profile_name="bookshop",
target_name="prod",
profile_mapping=SnowflakeUserPasswordProfileMapping(
conn_id="snowflake_prod",
profile_args={
"database": "ANALYTICS",
"schema": "analytics",
"warehouse": "WH_PROD",
"role": "TRANSFORMER_PROD",
},
),
)Here Cosmos reads an Airflow connection named snowflake_prod and generates the profiles.yml at run time from it — account, user, and credentials come from the connection, while the profile-specific bits (schema, warehouse, role) come from profile_args. The dev DAG points its mapping at conn_id="snowflake_dev", prod at snowflake_prod, and per-environment separation is now a matter of which Airflow connection you reference.
Which to choose? The profiles_yml_filepath approach keeps dbt’s config in dbt’s own format — the same file works when you run dbt build by hand on your laptop, and dbt people find it obvious. The profile_mapping approach keeps credentials in Airflow’s connection store instead of leaning on env vars, which matters most for the next section. Many teams land in the middle: a checked-in profiles.yml whose sensitive values are env_var(...) lookups, with those env vars populated from a secrets backend. That gets you dbt-native config and credentials that never touch the repo — which is exactly where we’re headed.
The CI target earns its own paragraph
Dev and prod are the obvious two; CI is the one people bolt on badly. A CI dbt run has a job dev and prod don’t: prove a pull request’s changes build and pass tests without touching the schemas anyone else uses. That’s why the CI target’s schema was itself an env var above — every PR gets an isolated schema like ci_pr_1487, built from empty, tested, and dropped when the PR merges or closes. A CI run that writes into a shared dev schema isn’t testing the PR; it’s leaking one engineer’s half-finished model into everyone else’s sandbox.
This is also where the state comparison from Chapter 5 pays off outside of production. A CI run doesn’t need to rebuild fifty models to test a change to one — it needs to build the models that changed and their downstream, using dbt’s state:modified+ selector against the prod manifest as the deferral baseline. The Cosmos RenderConfig that drives your CI DAG can carry that selector, so the CI environment is not just a different destination but a different, slimmer shape of the same project: same code, same profile mechanism, target_name="ci", and a selector that turns a fifty-model project into the three tasks the PR actually touches. Cheap warehouse, ephemeral schema, minimal graph — that’s a CI target that stays fast as the project grows instead of getting slower with every model anyone adds.
Secrets: resolving credentials at run time
Notice what the profiles.yml above did not contain: a password. Every sensitive field was an env_var(...) call or came from a connection. That’s the non-negotiable rule — credentials never live in the repo, never in the DAG file, never in the rendered bash_command. The question is where they do live, and how they arrive at run time.
The Airflow secrets backend
Airflow can resolve connections and variables from an external secrets manager instead of (or ahead of) its own metadata database. You wire it in with AIRFLOW__SECRETS__BACKEND, and from then on any conn_id your DAG references is looked up in that manager first. For AWS Secrets Manager:
AIRFLOW__SECRETS__BACKEND="airflow.providers.amazon.aws.secrets.secrets_manager.SecretsManagerBackend"
AIRFLOW__SECRETS__BACKEND_KWARGS='{"connections_prefix": "airflow/connections", "variables_prefix": "airflow/variables"}'With that set, when Cosmos’s profile_mapping asks for the connection snowflake_prod, Airflow fetches it from AWS Secrets Manager at the path airflow/connections/snowflake_prod — the credential is never stored in Airflow’s own database, only fetched into memory for the duration of the task. HashiCorp Vault is the same idea with a different class:
AIRFLOW__SECRETS__BACKEND="airflow.providers.hashicorp.secrets.vault.VaultBackend"
AIRFLOW__SECRETS__BACKEND_KWARGS='{"connections_path": "airflow/connections", "variables_path": "airflow/variables", "url": "https://vault.internal:8200"}'The payoff is that rotating a Snowflake password becomes a one-place operation: update the secret in AWS or Vault, and the next task run picks it up. No redeploy, no DAG edit, no restart — the backend is queried per lookup. Pair this with the profile_mapping style above and the credential’s entire lifecycle lives in the secrets manager; Airflow and dbt are just consumers.
Two operational notes save real debugging time here. First, a secrets backend adds a network call to every connection lookup, so scope the prefixes tightly (connections_prefix, variables_prefix) — if you point it at a path with hundreds of unrelated secrets, or leave the prefix too broad, you’ll pay the latency on lookups that were never going to be there. Second, the backend doesn’t replace Airflow’s own connection store, it sits in front of it: Airflow checks the secrets backend first and falls through to its metadata database and environment connections on a miss. That fallthrough is a feature — local dev can define snowflake_dev as an AIRFLOW_CONN_* env var while prod resolves the same conn_id from Vault — but it’s also a trap, because a stale connection lingering in the metadata database can silently shadow the one you think you’re reading from the backend. When a credential change “isn’t taking,” check whether an old copy is winning the fallthrough.
If you’d rather stay with a checked-in profiles.yml full of env_var(...) lookups, the same backend still helps — you store the values as Airflow Variables in the backend and inject them as env_vars on the Cosmos config (we’ll see env_vars in a moment), so SNOWFLAKE_ACCOUNT and friends resolve from Secrets Manager rather than being baked into the image.
Snowflake key-pair auth — better than a password
For Snowflake specifically there’s a stronger production pattern than any password: key-pair authentication. You register an RSA public key on the Snowflake user and authenticate with the private key, so there’s no reusable password to leak in the first place. From apache-airflow-providers-snowflake 6.3.0 onward the Cosmos Snowflake profile mappings support it through private_key_content:
from cosmos import ProfileConfig
from cosmos.profiles import SnowflakeEncryptedPrivateKeyPemProfileMapping
profile_config = ProfileConfig(
profile_name="bookshop",
target_name="prod",
profile_mapping=SnowflakeEncryptedPrivateKeyPemProfileMapping(
conn_id="snowflake_prod",
profile_args={
"database": "ANALYTICS",
"schema": "analytics",
"warehouse": "WH_PROD",
"role": "TRANSFORMER_PROD",
},
),
)The private key itself lives in the snowflake_prod connection — stored in your secrets backend, resolved at run time, never in the repo. Because Snowflake lets a user hold two registered public keys at once (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2), rotation is genuinely zero-downtime: register the new public key as the second key, flip the connection over to the new private key, verify a run, then unset the old key. No moment where the pipeline can’t authenticate. That’s the property you want from a credential the business depends on — it can be rotated on a schedule without anyone holding their breath.
One log-hygiene note that spans all of this: dbt treats environment variables prefixed DBT_ENV_SECRET_ as secrets and scrubs their values out of its own logs. That protects dbt’s output — it does not scrub the process table or an operator’s rendered command, which is the deeper reason to route secrets through connections and backends rather than interpolating them into a bash_command. Keep the credential out of the string, not just out of the log.
Passing vars into a run — on purpose
The last real-world pattern is parameterizing a run. dbt models often read var(...) values, and there are three places Cosmos lets you set them, each with a different scope.
ProjectConfig(dbt_vars=...) sets vars used while Cosmos parses the project to build the DAG — reach for it when a var changes the shape of the graph (a var referenced in a model’s config that decides materialization or selection):
from cosmos import ProjectConfig
project_config = ProjectConfig(
DBT_PROJECT_PATH,
dbt_vars={"stripe_enabled": "true"},
)operator_args={"vars": {...}} sets vars passed to the dbt run — the --vars you’d type on the command line, affecting what the models compute, not how the DAG is built:
dag = DbtDag(
dag_id="bookshop_prod",
project_config=project_config,
profile_config=profile_config,
operator_args={
"vars": {"start_date": "2026-01-01", "reprocess": "false"},
},
)env_vars sets process environment variables for the dbt subprocess — the right home for the SNOWFLAKE_ACCOUNT-style values that your profiles.yml reads through env_var(...), and for DBT_ENV_SECRET_-prefixed secrets:
from cosmos import ProjectConfig
project_config = ProjectConfig(
"/usr/local/airflow/include/dbt/bookshop",
env_vars={"SNOWFLAKE_ACCOUNT": "{{ var.value.snowflake_account }}"},
)Where this gets genuinely useful is wiring the vars to an Airflow Param, so a run can be parameterized from the UI or from conf on a triggered run:
from airflow.sdk import Param
dag = DbtDag(
dag_id="bookshop_reprocess",
project_config=project_config,
profile_config=profile_config,
params={
"start_date": Param("2026-01-01", type="string"),
"full_refresh": Param(False, type="boolean"),
},
operator_args={
"vars": {"start_date": "{{ params.start_date }}"},
"full_refresh": "{{ params.full_refresh }}",
},
render_config=RenderConfig(select=["tag:reprocessable"]),
)Now the same DAG serves double duty: it runs on its schedule with the defaults, and when an analyst needs to reprocess from a specific date they hit Trigger DAG w/ config, set start_date, and the templated {{ params.start_date }} flows through into dbt’s --vars. That’s a manual backfill with a real UI, no code edit, and an audit trail of who triggered what with which parameters — the interval-aware pattern from Chapter 10, exposed as a button.
Examples in this chapter are docs-checked against the series’ stated versions (Airflow 3.3.0, astronomer-cosmos, dbt-core, and the Snowflake provider ≥6.3.0 for key-pair auth), but they were not executed in this repository unless a companion project explicitly says so. Confirm the exact profile-mapping class names and provider versions against the releases you pin.
Final thoughts
That’s the series. We started with the plainest question — why orchestrate dbt at all — and worked outward through the naive bridge, Cosmos’s task-per-model rendering, selection, state and deferral, testing and observability, the load-and-run internals, execution modes, the assets seam, incrementals under backfill, and finally the landscape around the choice we’d been making all along.
If there’s one idea that ties it together, it’s that the interesting problems in this stack live at the seams — between Airflow and dbt, between dev and prod, between the repo and the secrets manager, between a schedule and a triggered backfill. Cosmos is worth its costs because it makes the biggest seam — the dbt graph becoming the Airflow graph — legible instead of opaque. But it doesn’t absolve you of the others. Pin your versions so the tools don’t drift apart underneath you. Map targets to environments so one project runs everywhere without an edit. Push credentials into a secrets backend and prefer key-pair auth so rotation is a scheduled non-event instead of a fire drill. Parameterize the runs that need it so a backfill is a button, not a branch.
Do those, and Airflow stops being a fancier cron in front of dbt build and becomes what it should be: the place where your transformations are visible, retryable, environment-aware, and safe to hand to the next engineer who’s on call at 3 a.m. — which, eventually, is all of us. Thanks for reading the whole way through.
Comments