dbt Architect: Data Platform and Environment Design

This domain tests the environment and platform decisions around a dbt project: how targets, credentials, schemas, warehouses, and platform features such as partitioning, table types, and access policies are configured so development, CI, and production stay isolated but consistent.

profiles.yml and targets

profiles.yml holds outputs keyed by target name; target: selects one, usually from an environment variable. {{ target.name }}, {{ target.schema }}, and {{ target.type }} let one codebase behave differently per environment, and {{ env_var('...') }} keeps secrets out of git. Override generate_schema_name when you need deterministic naming (a clean analytics.prod or analytics.dev_jsmith) instead of dbt's default concatenation of target schema and custom schema.

Environment topology

Three environments minimum: developer targets writing to per-user schemas, a CI target writing to a throwaway schema, and a production target running as a service principal. All read the same read-only sources so tests reflect reality. Production should be the only writer to the published schema; developers read production artifacts for deferral but never write there.

Platform features worth designing for

• Snowflake: separate warehouses for loading and transforming to avoid queue contention; transient tables for rebuildable staging to skip fail-safe storage; masking policies on PII columns; zero-copy clones for CI.
• BigQuery: dataset location is fixed at creation and must match across environments; partition on event or ingestion date, cluster on high-cardinality filter columns; require_partition_filter blocks accidental full scans; policy tags give column-level access control.
• Databricks: Unity Catalog governs grants and lineage; liquid clustering handles skewed filter keys better than static partitions.

Sizing and cost controls

Sizes belong to environments: extra-small warehouses for dev and CI, larger only where production models need it, with short auto-suspend. Set threads to the concurrency the warehouse can actually serve, not to the model count.

Worked example

A team's dev runs are slow and CI occasionally writes to production tables. Fix: give each developer target.schema: dev_<user>, create a ci target bound to a disposable schema on a dedicated extra-small warehouse, and let production run under a service user with write access only to analytics.prod. CI reads the production manifest and uses --defer, so unbuilt upstream models resolve to production relations while new models land in the CI schema.

Traps

• Committing credentials or sharing one developer login.
• Pointing dev at the production schema "temporarily".
• One warehouse serving both loads and transformations, so jobs queue behind each other.
• Raising threads past what the warehouse size supports.
• Assuming BigQuery datasets can be moved between locations after creation.