Automating Snowflake Object Lifecycle Management with Terraform: Provisioning Warehouses, Databases, Roles, and Grants as Code

Introduction

Picture this: your analytics team is growing. You've just hired three new analysts, spun up a second dbt project for the marketing team, and your data engineers are asking for isolated development warehouses so they don't stomp on production query performance. You open the Snowflake console and start clicking through the UI — creating databases, assigning roles, granting privileges, resizing warehouses. Two hours later, you've lost track of what you created, you're not sure if the MARKETING_ANALYST_ROLE has the right grants, and nobody on your team can reproduce the exact setup you just built.

This is the infrastructure-as-code problem for data teams, and Terraform solves it cleanly. Instead of point-and-click provisioning that lives only in someone's memory (or worse, in a Confluence doc that's six months out of date), you define your Snowflake environment in .tf files, store them in Git, and let Terraform handle the create/update/destroy lifecycle. Changes are peer-reviewed, auditable, and reproducible across environments.

By the end of this lesson, you'll be able to manage a production-grade Snowflake environment entirely through Terraform. Specifically:

What you'll learn:

• How to configure the Snowflake Terraform provider and authenticate safely
• How to provision virtual warehouses with right-sized configurations
• How to create databases and schemas with environment-aware naming conventions
• How to build a role hierarchy that mirrors real analytics team structures
• How to manage grants declaratively and avoid the most common drift traps

---

Prerequisites

You should be comfortable with:

• Snowflake fundamentals — warehouses, databases, roles, and privilege grants (if you need a refresher on Snowflake's positioning in the ecosystem, see the Cloud Data Warehouses: Snowflake vs BigQuery vs Redshift comparison)
• Basic Terraform concepts — providers, resources, state, and terraform plan / terraform apply
• Git and command-line comfort
• A Snowflake account with ACCOUNTADMIN access (free trial works fine; see Setting Up Your First Cloud Data Warehouse if you're starting fresh)

You do not need to be a Terraform expert. We'll explain the Snowflake-specific patterns in depth.

---

Why Infrastructure as Code for Snowflake?

Before we write a single line of Terraform, let's be precise about why this matters — because the argument goes deeper than "it's best practice."

Snowflake's permission model is genuinely complex. You have accounts, virtual warehouses, databases, schemas, tables, views, stages, and more — and privileges can be granted at every level. A production analytics environment typically has dozens of roles, hundreds of grants, and multiple warehouses tuned for different workload patterns. Managing this through the UI or even raw SQL scripts creates a few specific problems:

State drift: Someone logs in and runs GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.REPORTING TO ROLE ANALYST_ROLE directly in a worksheet. Now your documentation is wrong, your security audit will fail, and nobody knows when it happened.

Environment inconsistency: Your dev Snowflake account has a slightly different role structure than prod. Your dbt runs work locally but fail in CI because a grant is missing in the dev environment. These bugs are painful to debug.

Onboarding overhead: Every new environment (a new client, a new business unit, a new region) requires manually recreating the same object structure. With Terraform, it's terraform apply -var-file=new-client.tfvars.

Terraform solves all three. The state file is the source of truth, terraform plan surfaces drift before you apply changes, and variables let you stamp out identical environments with different names.

---

Project Structure

Let's establish the file structure before we write any code. Organizing Terraform for Snowflake well from the start saves you significant refactoring pain later.

snowflake-infra/
├── main.tf              # Provider config and backend
├── variables.tf         # Input variable declarations
├── terraform.tfvars     # Variable values (gitignored for secrets)
├── outputs.tf           # Exported values
├── warehouses.tf        # Virtual warehouse resources
├── databases.tf         # Databases and schemas
├── roles.tf             # Role definitions
├── grants.tf            # Privilege grants
└── environments/
    ├── dev.tfvars
    ├── staging.tfvars
    └── prod.tfvars

This separation by concern makes the codebase navigable. When a new engineer asks "where do I add a new role?", the answer is unambiguous: roles.tf. When your security team asks to audit all grants, they look at one file.

---

Configuring the Provider and Authentication

Start with main.tf. The Snowflake Terraform provider needs credentials to authenticate against your account.

terraform {
  required_version = ">= 1.5.0"

  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "~> 0.87"
    }
  }

  backend "s3" {
    bucket         = "my-company-terraform-state"
    key            = "snowflake/production/terraform.tfstate"
    region         = "us-east-1"
    encrypt        = true
    dynamodb_table = "terraform-state-locks"
  }
}

provider "snowflake" {
  account   = var.snowflake_account
  username  = var.snowflake_username
  role      = "SYSADMIN"
  
  # Prefer key-pair auth over password in production
  private_key_path = var.snowflake_private_key_path
}

Notice the S3 backend configuration. Never use local state for anything you care about. Remote state lets your whole team run Terraform safely, prevents concurrent applies from corrupting state, and survives laptop failures. The dynamodb_table argument enables state locking so two engineers can't run terraform apply simultaneously.

Now define the variables in variables.tf:

variable "snowflake_account" {
  description = "Snowflake account identifier (e.g., xy12345.us-east-1)"
  type        = string
}

variable "snowflake_username" {
  description = "Snowflake user for Terraform automation"
  type        = string
  default     = "TERRAFORM_SVC"
}

variable "snowflake_private_key_path" {
  description = "Path to the RSA private key for key-pair authentication"
  type        = string
  sensitive   = true
}

variable "environment" {
  description = "Deployment environment: dev, staging, or prod"
  type        = string
  
  validation {
    condition     = contains(["dev", "staging", "prod"], var.environment)
    error_message = "Environment must be one of: dev, staging, prod."
  }
}

variable "project_name" {
  description = "Project or team name used in resource naming"
  type        = string
  default     = "analytics"
}

Setting Up the Terraform Service User

Before Terraform can do anything, you need a dedicated Snowflake user for automation. Create this user manually — this is the one piece of bootstrapping you'll do in the UI or via SQL. Log in as ACCOUNTADMIN and run:

-- Create the Terraform service user
CREATE USER TERRAFORM_SVC
  DEFAULT_ROLE = SYSADMIN
  DEFAULT_WAREHOUSE = NULL
  COMMENT = 'Service account for Terraform IaC automation';

-- Generate an RSA key pair locally and upload the public key
ALTER USER TERRAFORM_SVC SET RSA_PUBLIC_KEY = '<your-public-key-here>';

-- Grant the roles Terraform will need
GRANT ROLE SYSADMIN TO USER TERRAFORM_SVC;
GRANT ROLE SECURITYADMIN TO USER TERRAFORM_SVC;

Key-pair authentication is strongly preferred over password auth for service accounts. Generate your key pair locally:

openssl genrsa -out snowflake_terraform.p8 2048
openssl rsa -in snowflake_terraform.p8 -pubout -out snowflake_terraform_pub.pem

Copy the public key content (without the -----BEGIN/END----- headers) into the ALTER USER statement above.

---

Provisioning Virtual Warehouses

Virtual warehouses are Snowflake's compute layer. Getting warehouse configuration right has real cost implications — an XL warehouse running 24/7 when you only need it for a few hours a day is expensive. If you want a deeper dive on cost controls, the Cost Management in Cloud Data Platforms lesson covers warehouse resource monitors in detail.

Here's a realistic warehouses.tf for an analytics engineering team:

locals {
  env_prefix = upper(var.environment)
}

# Loading warehouse for ELT ingestion (Fivetran, Airbyte, etc.)
resource "snowflake_warehouse" "loading" {
  name           = "${local.env_prefix}_LOADING_WH"
  comment        = "Warehouse for ELT tool ingestion (Fivetran/Airbyte)"
  warehouse_size = var.environment == "prod" ? "SMALL" : "X-SMALL"

  # Auto-suspend after 60 seconds of inactivity — critical for cost control
  auto_suspend = 60
  auto_resume  = true

  # Multi-cluster for handling concurrent ingestion jobs
  min_cluster_count = 1
  max_cluster_count = var.environment == "prod" ? 3 : 1
  scaling_policy    = "ECONOMY"

  # Prevent accidental deletion
  enable_query_acceleration = false
}

# Transformation warehouse for dbt
resource "snowflake_warehouse" "transforming" {
  name           = "${local.env_prefix}_TRANSFORMING_WH"
  comment        = "Warehouse for dbt transformation workloads"
  warehouse_size = var.environment == "prod" ? "MEDIUM" : "X-SMALL"

  auto_suspend = 120
  auto_resume  = true

  min_cluster_count = 1
  max_cluster_count = var.environment == "prod" ? 2 : 1
  scaling_policy    = "STANDARD"
}

# Reporting warehouse for BI tools and ad-hoc analyst queries
resource "snowflake_warehouse" "reporting" {
  name           = "${local.env_prefix}_REPORTING_WH"
  comment        = "Warehouse for BI tools (Looker, Tableau) and analyst queries"
  warehouse_size = var.environment == "prod" ? "LARGE" : "X-SMALL"

  auto_suspend = 300
  auto_resume  = true

  # Longer auto-suspend for reporting — analysts expect snappy response
  # after the first query warms the cache
  min_cluster_count = 1
  max_cluster_count = var.environment == "prod" ? 4 : 1
  scaling_policy    = "STANDARD"
}

Notice a few deliberate decisions here:

auto_suspend is set differently per warehouse. The loading warehouse suspends aggressively (60 seconds) because ingestion jobs are bursty and infrequent. The reporting warehouse waits longer (300 seconds) because users expect low latency — cold-starting a warehouse on every dashboard load degrades the experience.

Environment-conditional sizing using ternary expressions means the same Terraform code drives dev and prod, just with different variable files. Your dev environment pays for X-SMALL warehouses while prod uses appropriately sized compute.

Multi-cluster warehouses with ECONOMY scaling on the loading warehouse means Snowflake will maximize utilization of a single cluster before spinning up additional clusters — cheaper for batch workloads where some queue time is acceptable.

---

Provisioning Databases and Schemas

In most analytics architectures, databases and schemas map to stages in your data pipeline. The Modern Data Stack architecture typically produces a structure like RAW → STAGING → MARTS, which maps cleanly to Snowflake databases or schemas.

Here's databases.tf for an analytics platform:

# Raw data database — where ingestion tools land data
resource "snowflake_database" "raw" {
  name    = "${local.env_prefix}_RAW"
  comment = "Raw ingested data from ELT sources. Do not query directly in production."

  data_retention_time_in_days = var.environment == "prod" ? 7 : 1
}

# Analytics database — dbt-transformed models
resource "snowflake_database" "analytics" {
  name    = "${local.env_prefix}_ANALYTICS"
  comment = "Transformed analytics models managed by dbt"

  data_retention_time_in_days = var.environment == "prod" ? 14 : 1
}

# Schemas within the analytics database
resource "snowflake_schema" "staging" {
  database = snowflake_database.analytics.name
  name     = "STAGING"
  comment  = "dbt staging models — cleaned source data, 1:1 with source tables"

  data_retention_time_in_days = 1
  is_transient                = false
}

resource "snowflake_schema" "intermediate" {
  database = snowflake_database.analytics.name
  name     = "INTERMEDIATE"
  comment  = "dbt intermediate models — business logic, not for direct consumption"

  data_retention_time_in_days = 1
  is_transient                = false
}

resource "snowflake_schema" "marts" {
  database = snowflake_database.analytics.name
  name     = "MARTS"
  comment  = "dbt mart models — analyst-facing, BI-ready tables and views"

  data_retention_time_in_days = var.environment == "prod" ? 14 : 1
  is_transient                = false
}

# Source-specific schemas within raw database
resource "snowflake_schema" "raw_salesforce" {
  database = snowflake_database.raw.name
  name     = "SALESFORCE"
  comment  = "Raw data from Salesforce CRM via Fivetran"

  data_retention_time_in_days = var.environment == "prod" ? 7 : 1
}

resource "snowflake_schema" "raw_stripe" {
  database = snowflake_database.raw.name
  name     = "STRIPE"
  comment  = "Raw data from Stripe payments via Fivetran"

  data_retention_time_in_days = var.environment == "prod" ? 7 : 1
}

Notice that data_retention_time_in_days is set to 1 day in dev for everything that doesn't need Time Travel history. This avoids paying for storage to retain 14 days of intermediate query results in a dev environment where tables are rebuilt frequently.

The schema names here mirror the multi-layer dbt architecture described in Building a Multi-Layer dbt Project with Staging, Intermediate, and Mart Layers. This alignment between your Terraform structure and your dbt project structure is intentional — it makes it obvious which Snowflake objects correspond to which transformation layer.

---

Building a Role Hierarchy

This is where most Snowflake Terraform projects either shine or fall apart. Roles are Snowflake's access control mechanism, and a badly designed role hierarchy is painful to fix after the fact.

A practical role hierarchy for an analytics team looks like this:

ACCOUNTADMIN
└── SYSADMIN
    ├── TRANSFORMER_ROLE        (dbt service account — write access to analytics DB)
    └── LOADER_ROLE             (Fivetran/Airbyte — write access to raw DB)
└── SECURITYADMIN
    └── ANALYST_ROLE            (read access to marts)
        └── ANALYST_PII_ROLE    (read access to marts + PII columns)

Here's roles.tf:

# Loader role — for ELT tools writing to the raw database
resource "snowflake_role" "loader" {
  name    = "${local.env_prefix}_LOADER_ROLE"
  comment = "Used by ELT tools (Fivetran, Airbyte) to write raw data"
}

# Transformer role — for dbt writing to analytics database
resource "snowflake_role" "transformer" {
  name    = "${local.env_prefix}_TRANSFORMER_ROLE"
  comment = "Used by dbt service account to run transformations"
}

# Analyst role — read-only access to mart models
resource "snowflake_role" "analyst" {
  name    = "${local.env_prefix}_ANALYST_ROLE"
  comment = "Read-only access to analytics marts for data analysts"
}

# Elevated analyst role — includes access to PII fields
resource "snowflake_role" "analyst_pii" {
  name    = "${local.env_prefix}_ANALYST_PII_ROLE"
  comment = "Analyst access plus PII-classified columns. Requires manager approval."
}

# Service account users
resource "snowflake_user" "fivetran_svc" {
  name         = "${local.env_prefix}_FIVETRAN_SVC"
  display_name = "Fivetran Service Account (${var.environment})"
  comment      = "Service account for Fivetran data ingestion"
  
  default_role      = snowflake_role.loader.name
  default_warehouse = snowflake_warehouse.loading.name

  # Key-pair auth configured out-of-band
  must_change_password = false
}

resource "snowflake_user" "dbt_svc" {
  name         = "${local.env_prefix}_DBT_SVC"
  display_name = "dbt Service Account (${var.environment})"
  comment      = "Service account for dbt transformation runs"

  default_role      = snowflake_role.transformer.name
  default_warehouse = snowflake_warehouse.transforming.name

  must_change_password = false
}

# Role grants — assign roles to users
resource "snowflake_role_grants" "loader_to_fivetran" {
  role_name = snowflake_role.loader.name
  users     = [snowflake_user.fivetran_svc.name]
}

resource "snowflake_role_grants" "transformer_to_dbt" {
  role_name = snowflake_role.transformer.name
  users     = [snowflake_user.dbt_svc.name]
}

# Role hierarchy — analyst_pii inherits from analyst
resource "snowflake_role_grants" "analyst_to_pii" {
  role_name = snowflake_role.analyst_pii.name
  roles     = [snowflake_role.analyst.name]
}

---

Managing Grants Declaratively

Grants are the trickiest part of Snowflake Terraform. You need to be precise about what you're granting, at what object level, and to which role — and you need to think carefully about what happens when new objects are created after your Terraform runs.

Here's grants.tf with realistic grant configurations:

# ============================================================
# LOADER ROLE GRANTS
# ============================================================

# Loader gets usage on the loading warehouse
resource "snowflake_warehouse_grant" "loader_usage" {
  warehouse_name = snowflake_warehouse.loading.name
  privilege      = "USAGE"
  roles          = [snowflake_role.loader.name]
}

# Loader gets full access to the raw database
resource "snowflake_database_grant" "loader_raw" {
  database_name = snowflake_database.raw.name
  privilege     = "USAGE"
  roles         = [snowflake_role.loader.name]
}

resource "snowflake_schema_grant" "loader_raw_salesforce" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "USAGE"
  roles         = [snowflake_role.loader.name]
}

resource "snowflake_table_grant" "loader_raw_salesforce_all" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "ALL PRIVILEGES"
  roles         = [snowflake_role.loader.name]

  # This is key: grants privilege on tables that exist NOW
  # Use on_future grants separately for tables created later
  on_all        = true
}

# Future grants — critical for ELT tools that create new tables
resource "snowflake_table_grant" "loader_raw_salesforce_future" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "ALL PRIVILEGES"
  roles         = [snowflake_role.loader.name]

  on_future = true
}

# ============================================================
# TRANSFORMER ROLE GRANTS
# ============================================================

# Transformer reads from raw
resource "snowflake_database_grant" "transformer_raw_usage" {
  database_name = snowflake_database.raw.name
  privilege     = "USAGE"
  roles         = [snowflake_role.transformer.name]
}

resource "snowflake_schema_grant" "transformer_raw_salesforce" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "USAGE"
  roles         = [snowflake_role.transformer.name]
}

resource "snowflake_table_grant" "transformer_raw_select" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "SELECT"
  roles         = [snowflake_role.transformer.name]
  on_all        = true
}

resource "snowflake_table_grant" "transformer_raw_select_future" {
  database_name = snowflake_database.raw.name
  schema_name   = snowflake_schema.raw_salesforce.name
  privilege     = "SELECT"
  roles         = [snowflake_role.transformer.name]
  on_future     = true
}

# Transformer has full ownership of the analytics database
resource "snowflake_database_grant" "transformer_analytics" {
  database_name = snowflake_database.analytics.name
  privilege     = "USAGE"
  roles         = [snowflake_role.transformer.name]
}

resource "snowflake_schema_grant" "transformer_analytics_create" {
  database_name = snowflake_database.analytics.name
  schema_name   = snowflake_schema.marts.name
  privilege     = "CREATE TABLE"
  roles         = [snowflake_role.transformer.name]
}

resource "snowflake_schema_grant" "transformer_analytics_create_view" {
  database_name = snowflake_database.analytics.name
  schema_name   = snowflake_schema.marts.name
  privilege     = "CREATE VIEW"
  roles         = [snowflake_role.transformer.name]
}

# ============================================================
# ANALYST ROLE GRANTS
# ============================================================

resource "snowflake_warehouse_grant" "analyst_usage" {
  warehouse_name = snowflake_warehouse.reporting.name
  privilege      = "USAGE"
  roles          = [snowflake_role.analyst.name]
}

resource "snowflake_database_grant" "analyst_analytics" {
  database_name = snowflake_database.analytics.name
  privilege     = "USAGE"
  roles         = [snowflake_role.analyst.name]
}

resource "snowflake_schema_grant" "analyst_marts_usage" {
  database_name = snowflake_database.analytics.name
  schema_name   = snowflake_schema.marts.name
  privilege     = "USAGE"
  roles         = [snowflake_role.analyst.name]
}

resource "snowflake_table_grant" "analyst_marts_select" {
  database_name = snowflake_database.analytics.name
  schema_name   = snowflake_schema.marts.name
  privilege     = "SELECT"
  roles         = [snowflake_role.analyst.name]
  on_all        = true
}

resource "snowflake_table_grant" "analyst_marts_select_future" {
  database_name = snowflake_database.analytics.name
  schema_name   = snowflake_schema.marts.name
  privilege     = "SELECT"
  roles         = [snowflake_role.analyst.name]
  on_future     = true
}

---

Using Locals and Variables for DRY Configuration

As your grant configuration grows, you'll notice repetition. Applying the same set of grants to a new schema means copy-pasting blocks and changing the schema name. Terraform's for_each and locals help you stay DRY.

Here's how to refactor schema grants using for_each:

# In variables.tf
variable "raw_source_schemas" {
  description = "Source system schemas in the raw database"
  type        = set(string)
  default     = ["SALESFORCE", "STRIPE", "HUBSPOT", "ZENDESK"]
}

# In databases.tf — generate schemas dynamically
resource "snowflake_schema" "raw_sources" {
  for_each = var.raw_source_schemas

  database = snowflake_database.raw.name
  name     = each.value
  comment  = "Raw data for ${each.value} source"

  data_retention_time_in_days = var.environment == "prod" ? 7 : 1
}

# In grants.tf — grant transformer SELECT on all source schemas
resource "snowflake_schema_grant" "transformer_raw_sources_usage" {
  for_each = var.raw_source_schemas

  database_name = snowflake_database.raw.name
  schema_name   = each.value
  privilege     = "USAGE"
  roles         = [snowflake_role.transformer.name]
  
  depends_on = [snowflake_schema.raw_sources]
}

resource "snowflake_table_grant" "transformer_raw_sources_select_future" {
  for_each = var.raw_source_schemas

  database_name = snowflake_database.raw.name
  schema_name   = each.value
  privilege     = "SELECT"
  roles         = [snowflake_role.transformer.name]
  on_future     = true

  depends_on = [snowflake_schema.raw_sources]
}

Now, adding a new source system means adding one string to the raw_source_schemas set in your .tfvars file, and Terraform handles the rest.

---

Outputs

Expose useful values through outputs.tf so other Terraform configurations (or CI/CD pipelines) can reference them:

output "transforming_warehouse_name" {
  description = "Name of the dbt transformation warehouse"
  value       = snowflake_warehouse.transforming.name
}

output "analytics_database_name" {
  description = "Name of the analytics database for dbt profiles"
  value       = snowflake_database.analytics.name
}

output "dbt_service_account_name" {
  description = "Snowflake username for the dbt service account"
  value       = snowflake_user.dbt_svc.name
}

output "transformer_role_name" {
  description = "Role name to set in dbt profiles.yml"
  value       = snowflake_role.transformer.name
}

These outputs are especially useful when you wire Terraform outputs into your CI/CD pipelines for dbt deployments — the pipeline can read the warehouse and database names from Terraform state rather than hardcoding them.

---

Applying Your Configuration

With the files in place, here's your workflow:

# Initialize — downloads the Snowflake provider
terraform init

# Validate syntax
terraform validate

# Preview changes against current Snowflake state
terraform plan -var-file=environments/prod.tfvars

# Apply
terraform apply -var-file=environments/prod.tfvars

The terraform plan output for your first apply will show a long list of resources to create. Read it carefully before applying — particularly the role grants and any on_all table grants, which can be broad.

For subsequent changes, the plan output is your change review. Before merging any PR that touches Terraform, run terraform plan in CI and attach the output to the PR for review. This is the Terraform equivalent of reviewing a SQL migration.

---

Handling Snowflake-Specific Gotchas

Object Names and Case Sensitivity

Snowflake is case-insensitive by default, but stores unquoted identifiers in uppercase. Terraform resources that reference object names need to be consistent. If you create a database named analytics in Terraform, Snowflake stores it as ANALYTICS. Your dbt profiles.yml should reference ANALYTICS (uppercase) or the connection will fail.

The convention throughout this lesson — always uppercase in Terraform configs — avoids this entirely.

Import Existing Resources

If you already have Snowflake objects that were created outside Terraform, import them before managing them:

# Import an existing warehouse
terraform import snowflake_warehouse.reporting PROD_REPORTING_WH

# Import an existing database
terraform import snowflake_database.analytics PROD_ANALYTICS

After importing, run terraform plan to see if there's any drift between the actual object configuration and what your .tf file declares. Fix drift before running apply.

Resource Replacement vs. In-Place Updates

Some Snowflake resource changes require replacement (destroy + recreate) rather than in-place updates. Changing a database's name is an example — Terraform will drop the old database and create a new one. In the plan output, these appear as # resource will be destroyed followed by # resource will be created.

This is dangerous if the database has data. Before applying any Terraform plan that shows replacement, manually verify the impact. If needed, use terraform state mv to rename the resource in state rather than destroying it.

---

Hands-On Exercise

Build a complete Terraform configuration for the following scenario:

Scenario: You're setting up a new analytics environment for a healthcare analytics team. The requirements are:

1. Two databases: CLINICAL_RAW for ingested data, CLINICAL_ANALYTICS for dbt models
2. Three schemas in CLINICAL_RAW: EHR (electronic health records), CLAIMS, LABS
3. Three warehouses: a loading warehouse (auto-suspend at 60s), a transforming warehouse (auto-suspend at 120s), and a reporting warehouse (auto-suspend at 300s)
4. Four roles: CLINICAL_LOADER, CLINICAL_TRANSFORMER, CLINICAL_ANALYST, CLINICAL_ANALYST_PHI (PHI = Protected Health Information, the healthcare equivalent of PII)
5. Grants: Loader gets full access to raw schemas, transformer gets read access to raw + write access to analytics, analyst gets read access to CLINICAL_ANALYTICS, and CLINICAL_ANALYST_PHI inherits from CLINICAL_ANALYST

Extend it: Add a compliance_tier variable that, when set to "hipaa", sets data_retention_time_in_days = 365 on all databases (HIPAA requires audit data retention). When set to "standard", use the environment-based defaults from this lesson.

This exercise reinforces the for_each pattern for schemas, conditional expressions for variable-driven configuration, and the role hierarchy pattern for access control tiers.

---

Common Mistakes & Troubleshooting

"Error: Provider produced inconsistent result"

This usually happens when Snowflake normalizes a value differently than what Terraform expects. For example, setting warehouse_size = "small" (lowercase) may cause Terraform to see drift on every plan because Snowflake returns "SMALL". Fix: always use the exact case Snowflake uses in its API responses (uppercase for most string values).

Grant Drift After dbt Runs

dbt's full-refresh mode drops and recreates tables. If you're using on_all grants without on_future grants, analysts lose access immediately after a full refresh. The fix is always pairing on_all with on_future as shown in the grants section.

"Insufficient privileges to operate on warehouse"

The most common cause: your dbt profiles.yml is pointing to the wrong role, or the transformer role hasn't been granted USAGE on the transforming warehouse. Check by running SHOW GRANTS TO ROLE PROD_TRANSFORMER_ROLE in Snowflake and comparing against your Terraform state.

Terraform State Shows Resources Not Found

If someone deleted a Snowflake object outside Terraform, terraform plan will show it as needing to be created. The plan will succeed, but if you're alarmed by unexpected creates, use terraform state list to see what Terraform thinks exists, and cross-reference with Snowflake's SHOW OBJECTS commands.

Circular Dependencies

Terraform builds a dependency graph automatically from resource references. Occasionally you'll introduce a cycle — Role A depends on Role B which depends on Role A. Terraform will error with a clear message. Fix by restructuring your role hierarchy to be strictly hierarchical, with no circular inheritance.

---

Summary & Next Steps

You've built a production-grade Terraform configuration that manages the full lifecycle of Snowflake objects — warehouses sized and auto-suspended appropriately, databases and schemas mirroring your transformation layer, a role hierarchy that reflects real access control requirements, and grants that cover both existing and future objects.

The key principles to carry forward:

• Always use remote state with locking — local state is a footgun in team environments
• Always pair on_all with on_future grants — one without the other causes access problems
• Use for_each over copy-paste for schema and grant patterns that repeat across sources
• terraform plan in CI is non-negotiable — it's your change review mechanism
• Service accounts with key-pair auth, never passwords, never personal credentials

Where to go next:

The Terraform configuration you've built here is the infrastructure foundation for your analytics environment. The natural next layer is access control depth — row-level security policies and column masking for PII/PHI data, which is covered in detail in Implementing Row-Level Security and Column Masking Policies for Multi-Tenant Analytics.

Once your infrastructure is code-managed, think about how data governance policies attach to this object model. Data Governance: Catalogs, Lineage, and Access Controls covers how to layer a metadata catalog on top of the Snowflake object structure you've just provisioned.

And if you're building the full analytics stack, this Terraform configuration is the foundation that Building a Complete Modern Data Stack from Scratch assumes is in place before you start wiring together ingestion, transformation, and serving layers.