09 - Environment Management Strategy

15 min read

Environment Management Strategy

Quote

“You need to get everything in version control. Not just the code, but everything required to build the environment.”

— Gene Kim, The DevOps Handbook (2016)

Environment Topology — How Many and Why

ModelEnvironmentsMonthly CostRisk ProfileBest For
Two-tierDev workstation + Prod~$300-500Higher (no pre-prod gate)Solo engineer, small team (1-3), budget-constrained
Three-tierDev + Staging + Prod~$400-600Lower (staging catches issues)Most teams (3-10), standard recommendation
Four-tierDev + Staging + QA/UAT + Prod~$600-1000LowestRegulated industries, large teams (10+)

Two-Tier for Small Teams

For a single data engineer or a team of 2-3 people, two-tier (dev + prod) is usually the right call. Staging adds $65-100/month in infrastructure cost plus operational overhead (maintaining a second environment, keeping schemas in sync, debugging staging-specific issues). The key mitigation: excellent CI/CD testing in GitHub Actions before merge to main. Add staging only when team size, compliance requirements, or incident frequency justify it.

Decision factors:

  • Team size: 1-3 people → two-tier. 4-10 → three-tier. 10+ → four-tier.
  • Data sensitivity: PII or financial data under regulation → staging mandatory.
  • Deployment frequency: Multiple deploys per day → staging catches integration issues.
  • Budget: Staging adds ~$65/month in GCP costs (see cost model below).

What Differs Between Environments

DimensionDevStaging (if used)Prod
GCP Projectdata-platform-devdata-platform-stagingdata-platform-prod
SQL ServerLocal Docker or e2-small VMSmaller replica of proddata-pipeline-sql (production VM)
BigQuery datasetdev_pipelinestaging_pipelinepipeline_data
GCS bucketdata-platform-dev-datadata-platform-staging-datadata-platform-prod-data
Service accountDev SA (broader permissions)Staging SA (prod-like permissions)Pipeline SA (least privilege)
Secrets.env file or dev Secret ManagerStaging Secret ManagerProd Secret Manager
Data volumeSample (1% of prod or synthetic)Full copy or recent subsetFull production data
AirflowLocal Docker ComposeStaging VM (optional)Production Airflow VM
dbt targetdev (profiles.yml)stagingprod
Terraform workspacedevstagingprod
AlertingNone or Slack dev-alertsSlack staging-alertsPagerDuty + Slack prod-alerts
IAMBroader access for debuggingProd-like restrictionsStrict least privilege

Separate GCP Projects Required

Same project for dev and prod means IAM changes affect both environments. Billing is mixed (can’t attribute costs). A dev terraform destroy can hit prod resources. Always use separate GCP projects — the free tier applies per billing account, not per project, so multiple projects don’t increase cost.

Use separate GCP projects per environment

Create data-platform-dev, data-platform-staging, and data-platform-prod as distinct GCP projects under the same billing account. Use Terraform to provision all three from a shared module with environment-specific tfvars. Billing attribution, IAM boundaries, and blast radius are all scoped automatically.

Tool-by-Tool Environment Separation

gcloud CLI — named configurations

One named configuration per environment. Switch with activate. The active configuration determines which GCP project, region, and account every gcloud command targets.

gcloud config configurations create dev
gcloud config set project data-platform-dev
gcloud config configurations activate dev

See gcloud-configurations > gcloud config configurations create — named config per environment for setup and gcloud-configurations > Protecting Production with Visual Cues in Terminal for the shell prompt trick that color-codes your terminal by environment.

Wrong gcloud configuration targets wrong project

Forgetting to switch configurations before running gcloud compute instances delete or bq rm hits the wrong project. Always verify with gcloud config get project before destructive operations. The terminal color-coding pattern in gcloud-configurations makes the active project visible at a glance.

Verify the active project before every destructive operation

Run gcloud config get project as the first line of any script that performs destructive operations. Use the terminal color-coding pattern in gcloud-configurations to keep the active project permanently visible in your shell prompt.

Terraform — workspaces or variable files

Three approaches, each with trade-offs:

ApproachHow It WorksBest For
Workspacesterraform workspace select prod — same code, different state fileSmall teams, identical infra per env
Variable filesterraform apply -var-file=prod.tfvars — same code, different variablesDifferent sizing per env (smaller dev VMs)
Separate directoriesterraform/dev/, terraform/prod/ — different code per envLarge teams, significantly different infra

Recommendation: .tfvars files for small teams (keeps code DRY), separate directories only when environments have fundamentally different architectures.

# Variable file approach
terraform plan -var-file=environments/prod.tfvars
terraform apply -var-file=environments/prod.tfvars

Always verify workspace before apply

terraform apply in the wrong workspace creates or destroys resources in the wrong environment. Always run terraform workspace show before terraform plan. In CI/CD, set the workspace explicitly in the workflow — never rely on the last-used workspace.

Set workspace explicitly in every CI/CD workflow step

Add terraform workspace select prod (or the target environment) as the first step in your GitHub Actions deploy job, immediately before terraform plan. Never assume the workspace state carried over from a previous run.

dbt — profile targets

The profiles.yml file defines connection targets. The target: key sets the default — which environment dbt run without --target hits.

pipeline:
  target: dev
  outputs:
    dev:
      type: sqlserver
      server: localhost
      database: analytics_dev
    prod:
      type: sqlserver
      server: data-pipeline-sql
      database: analytics_db
dbt run                    # hits dev (the default target)
dbt run --target prod      # explicitly hits prod

profiles.yml default target must be dev

If target: prod is the default, every dbt run without --target hits production. A developer running dbt run locally to test a model change modifies production tables. Always set the default to dev. CI/CD pipelines should use --target prod explicitly.

Set target: dev in profiles.yml and use --target prod explicitly in CI/CD

The profiles.yml default must always be dev. All production deploys go through CI/CD with an explicit dbt run --target prod. No engineer should ever need to run --target prod locally — if they do, it is a process problem, not a tooling problem.

Airflow — connections and variables

Each environment has its own Airflow Connections (different SQL Server host, different BigQuery dataset) and Variables (environment name, feature flags).

Copying DAGs between environments

Copying a DAG from prod to dev but forgetting to update the connection ID means the dev DAG runs against the production database. Use connection IDs that include the environment name (sql_server_prod, sql_server_dev) or use Airflow Variables to resolve the connection dynamically.

Embed the environment name in every connection ID

Name connections sql_server_dev, sql_server_staging, sql_server_prod — not just sql_server. Use an Airflow Variable ENV (dev/staging/prod) to build the connection ID dynamically: conn_id = f"sql_server_{Variable.get('ENV')}". A copied DAG that still works correctly in dev is safe by construction.

GitHub Actions — environment secrets and protection rules

GitHub Environments (dev, staging, production) provide per-environment secrets and protection rules (approval gates, branch restrictions).

Production environment protection rules

Configure the production GitHub Environment to: (1) require manual approval before deployment, (2) restrict to the main branch only, and (3) use a separate WIF service account with least-privilege IAM. This prevents accidental prod deployments from feature branches.

SQL Server — separate instances per environment

  • Dev: Local Docker container (docker run -e SA_PASSWORD=... mcr.microsoft.com/mssql/server) or a small GCE e2-small VM
  • Prod: Production GCE VM with proper sizing, backups, and Datadog monitoring
  • Configuration: see server-configuration for memory, recovery model, and RCSI settings

Separate SQL Server Instances

Never share an instance between dev and prod. A dev query with no WHERE clause can lock a prod table. A dev TRUNCATE on the wrong database wipes production data. Separate instances — even if they’re on the same VM — provide process isolation that separate databases on the same instance cannot.

Run dev SQL Server as a local Docker container

Use docker run -e SA_PASSWORD=... mcr.microsoft.com/mssql/server for dev — completely isolated from production with zero cross-contamination risk and no ongoing GCE cost. Production runs on its own dedicated GCE VM. The two instances cannot interact by design.

BigQuery — separate datasets or projects

bq commands use the default project

bq query without --project_id uses the project from the active gcloud configuration. If your gcloud config points to prod, your “dev” query runs against production BigQuery — and you pay for the bytes scanned in prod. Always specify --project_id or verify with gcloud config get project.

Always pass --project_id explicitly to bq commands

Use bq query --project_id=data-platform-dev ... in all scripts. This makes the target project explicit and independent of the active gcloud configuration, eliminating the risk of running dev queries against prod BigQuery.

The Promotion Workflow — Code Path from Dev to Prod

graph TD
    subgraph Dev["Developer Workstation"]
        D1[Write code + tests] --> D2[git push to feature branch]
    end

    subgraph CI["GitHub Actions — PR Checks"]
        D2 --> C1[Lint + type check]
        C1 --> C2[Unit tests]
        C2 --> C3["dbt test --target dev"]
        C3 --> C4[Terraform plan dev]
    end

    subgraph Review["Pull Request"]
        C4 --> R1[Code review]
        R1 --> R2[Merge to main]
    end

    subgraph Deploy["GitHub Actions — Deploy"]
        R2 --> S1["Terraform apply (staging)"]
        S1 --> S2["dbt run --target staging"]
        S2 --> S3[Integration tests]
        S3 --> S4{Manual approval gate}
        S4 -->|Approved| P1["Terraform apply (prod)"]
        P1 --> P2["dbt run --target prod"]
        P2 --> P3[Smoke tests + monitoring]
    end

    style Dev fill:#1a1a2e,stroke:#4285f4,color:#fff
    style CI fill:#1a1a2e,stroke:#e8b84d,color:#fff
    style Review fill:#1a1a2e,stroke:#34a853,color:#fff
    style Deploy fill:#1a1a2e,stroke:#cc4125,color:#fff

Code flows forward, data flows backward

Code changes flow forward only: dev → staging → prod. Never cherry-pick from prod to dev or hotfix directly in production.

Data flows in the opposite direction: production data is sampled or anonymized for dev/staging testing. Dev never generates data that flows to prod — prod is the source of truth.

What blocks promotion at each stage

StageBlockerAction
PR checksLint failure, test failure, dbt test failureFix in feature branch, re-push
Code reviewReviewer requests changesAddress feedback, re-request review
Staging deployTerraform plan shows unexpected changesInvestigate — may indicate drift
Integration testsData quality check fails in stagingFix transform logic, re-deploy staging
Manual approvalReviewer sees risk in prod changesDiscuss, modify, or defer
Post-deployMonitoring shows anomaly after prod deployRollback: git revert → re-deploy

The “Works in Dev, Breaks in Prod” Problem

Top 10 dev-vs-prod failures

#What BreaksWhyPrevention
1Connection stringsHardcoded in dev, different in prodSecret Manager + env-specific connections
2Data volumeDev has 1K rows, prod has 100MTest with production-scale data in staging
3PermissionsDev SA has roles/editor, prod has least privilegeUse prod-like IAM in staging
4Schema differencesDev database schema drifted from prodAutomated schema comparison in CI
5TimingDev DAG runs instantly, prod has dependency waitsTest with realistic schedules in staging
6ConcurrencyDev is single-user, prod has parallel DAGsLoad testing in staging
7Secret availabilityDev uses .env, prod uses Secret ManagerUse Secret Manager in dev too
8NetworkDev has public access, prod is VPC-restrictedTest through IAP tunnel in dev
9Resource limitsDev has no quotas, prod hits BQ slot limitsSet quotas in dev project too
10Feature flagsFeature enabled in dev, disabled in prodExplicit feature flag management

Use staging as a prod mirror to surface these failures before cutover

Staging exists precisely to catch the discrepancies in the table above. Give staging prod-like IAM, prod-like data volumes (recent subset), Secret Manager (not .env), VPC network config, and BQ slot quotas. A failure caught in staging costs a deploy cycle. The same failure in prod costs an incident and SLA breach.

Cost Model by Environment

What each environment actually costs per month. For full per-service pricing detail, see gcp-billing-and-pricing. For complete architecture cost breakdowns at different scales, see gcp-total-cost-of-ownership.

ComponentDevStagingProd
GCE VM (SQL Server)25 (e2-small)$50 (e2-medium)$150 (n2-standard-4)
GCE VM (Airflow)$0 (local Docker Compose)$0 (shared or skipped)$75 (e2-standard-2)
BigQuery$0 (free tier: 1TB/mo queries)$5 (limited queries)$50-200
GCS$1 (small test data)$5 (staging data)$20-50
Secret Manager$0 (free tier)$0$1
Networking$0 (local)$5 (egress)$10-30
Total~$0-25/mo~$65/mo~$300-500/mo

Dev environment cost optimization

Environment Anti-Patterns

Environment management anti-patterns

Anti-PatternRiskFix
No environment separation (dev → prod directly)Every change is a production changeAt minimum, use feature branches + CI tests before merge
profiles.yml default target set to prodEvery dbt run without --target hits productionAlways default to dev — CI uses --target prod explicitly
Same GCP project for dev and prodIAM changes, terraform destroy, billing all mixedSeparate projects — free tier applies per billing account
Hardcoded project IDs in codeCan’t switch environments without code changesUse config files, env vars, or Terraform variables
Dev database with production dataPrivacy risk (PII), unnecessary storage costUse sampled or synthetic data in dev
No approval gate before prod deployBroken code reaches production automaticallyGitHub Environment protection rules on production
Terraform state in same bucket for all envsState corruption, accidental cross-env changesSeparate state buckets per environment
Different schemas between dev and prodQueries that work in dev fail in prodAutomated schema comparison in CI, or use dbt to manage schema

Treat environment config as infrastructure — version-controlled and automated

Store all environment configuration (project IDs, connection strings, feature flags) in version-controlled tfvars files and GitHub Environment secrets. No hardcoding, no manual switches. When every environment property is declared in code, the anti-patterns above become structurally impossible.

Graph View

Backlinks