Snowflake resources & RBAC

What Terraform owns inside Snowflake, and the boundary between Terraform and the migrations system.

Terraform vs schemachange

Terraform and schemachange each own a different layer of the same database:

Owns	Terraform	schemachange
Databases, schemas, warehouses	Yes	No
Roles, grants, ownership	Yes	No
Storage integrations, Snowpipes, tasks, alerts, network policies, tags	Yes	No
Table DDL (columns, constraints) inside those schemas	No	Yes

Table structure — adding a column, changing a type, creating a new table — goes through a versioned migration file (src/stock/dbt/migrations/, src/redbook/dbt/migrations/) run by deploy-migrations.sh, never a direct ALTER TABLE and never a Terraform resource. See Migrations and Deploying migrations locally.

Warehouses

Warehouse	Size (var)	Auto-suspend	Purpose
`DRIVE_WH`	`var.warehouse_size` (default `SMALL`)	60s	General-purpose dbt transformations and heavy compute
`STOCK_WH`	`var.stock_warehouse_size` (default `X-SMALL`)	60s	Dedicated to stock dbt transformations

Both start initially_suspended, auto-resume on query, and cap concurrency at 8 — cost control over always-on compute.

Functional roles

Six roles exist, each named DRIVE_{ENV}_ROLE_* and each backing exactly one service or human-user category:

Role	Backs	Scope
`AIRBYTE`	Marketplace/Ads ingestion	Ownership of `RAW_DB` schemas (existing + future)
`DBT`	dbt runs, all three projects	Ownership of `PROCESSED_DB`, `SPECIFICATIONS_DB`, `VEHICLE_STOCK_DB` schemas (existing + future); read-only on `RAW_DB`; `EXECUTE TASK` at the account level
`BI`	Sigma, Tableau	Read-only (`SELECT`) on `RAW_DB` and `PROCESSED_DB`, existing + future; owns one write-back sandbox schema (`RAW_DB.SIGMA_WRITE_SCHEMA`)
`ETL`	Integrate.io	Write access scoped to `RAW_DB`; owns the `IIO_STORAGE_INTEGRATION` Integrate.io manages out-of-band
`N8N`	n8n automation	Write access scoped to a single schema, `RAW_DB.GA4_SCHEMA`
`READONLY`	Read-only console/API access, including the agentic MCP user	`SELECT` on every database, existing + future — the broadest read role in the account

DBT's ownership of PROCESSED_DB is easy to miss if you're only thinking about VEHICLE_STOCK_DB/SPECIFICATIONS_DB — dbt has full schema-creation rights there too.

BI and READONLY grant on existing objects (all_schemas_in_database, all { object_type_plural = "TABLES" }) as well as future ones — a deliberate exception to a future-grants-only approach, made for roles that only ever read.

Service and human users

Each functional role backs one service user (DRIVE_{ENV}_DBT, _SIGMA, _TABLEAU, _AIRBYTE, _N8N, _IIO, _READONLY, _AGENTIC_MCP), plus a separately-managed INTEGRATE_IO user distinct from the _IIO service user.

Two human user roles are created in every environment, including prod:

DRIVE_{ENV}_ENGINEER — default role DBT, also granted AIRBYTE and BI.
DRIVE_{ENV}_PRODUCT — READONLY, for read-only console access.

Human users exist in prod deliberately, so a read-only console user can log into the Snowflake UI there too — this isn't a service-accounts-only environment.

The trust-policy workaround

Snowflake storage integrations (used by both Snowpipes and the S3 export path) generate their storage_aws_iam_user_arn/external ID only after creation. The matching IAM role's trust policy needs that value, so it can't be set purely declaratively at creation time.

Both the Snowpipe and export-S3 Terraform modules work around this with a null_resource local-exec that shells out to aws iam update-assume-role-policy, using var.aws_profile. This is a legitimate limitation of the provider, not a shortcut — but it means terraform plan alone never shows the real state of that trust policy, and applying requires working AWS CLI credentials for that exact profile, separately from the Terraform provider's own Snowflake/AWS credentials.