Data Pipeline Deployment and Infrastructure as Code

Data Engineering & Modern Data Platforms · Lesson 63 · Production Data Engineering and Capstone

Lesson Overview

Production changed at 22:17 and git did not. A bucket policy was clicked in the console so a backfill could run. An Airflow variable was typed into the UI for a one-off. Warehouse grants were copied from a screenshot. Monday's board still needed yesterday's grain by 06:00. Nobody could say which identity published completion-v4, whether the grain check in prod matched main, or which IAM role the merge used when it wrote the marker. The incident timeline became archaeology.

Data Reliability and SLA/SLO Design wrote the contract: freshness, gates, error budget, refusals. This lesson makes that contract deployable. Infrastructure as code (IaC) owns buckets, IAM, orchestration environments, and warehouse objects that should not exist only in a click path. CI owns promotion of DAG code, SQL, and table DDL together. Blue/green or equivalent table cutovers keep readers on a known version. Secrets stay out of repositories and out of chat. Hand-clicking production is not a shortcut. It is an unreviewed deploy with no rollback story.

The teaching platform stays the same. About 40 GB of Parquet progress a day, a completion mart, forty Monday readers, MWAA or Composer or a self-managed Airflow, a lake bucket, a serving warehouse or native lake tables. The goal is not to crown Terraform over CDK or Pulumi. The goal is one reviewable path from merge to prod that lesson 62's freeze policy can actually block.

Learning Objectives

By the end of this lesson you will be able to:

1. Explain why data platform prod changes need the same review bar as application releases.
2. Model buckets, IAM, orchestration environments, and warehouse endowments as IaC modules with state and plans.
3. Compare Terraform, CDK, and Pulumi at the level of state, languages, and team fit without vendor theater.
4. Design environment separation (dev, staging, prod) with promotion rules that match the reliability freeze.
5. Build a CI pipeline that promotes DAG code, transformation SQL, and table DDL as one release unit.
6. Apply blue/green or shadow-table cutovers for mart changes so readers flip atomically.
7. Store and inject secrets without baking them into DAG files or Terraform state careless exports.
8. Refuse console-only prod changes, and define break-glass with expiry and audit.
9. Wire deploy gates to error-budget policy from lesson 62 and cost constraints from lesson 58.
10. Hand Data Engineering System Design a platform you can redraw because the inventory lives in git.

Prerequisites

• Data Reliability and SLA/SLO Design for protected paths, freeze thresholds, and publish markers.
• Data Platform Cost and Performance Optimization for capacity schedules and single-mart rules that IaC must encode.
• Data Platform Security and Governance for identities and audit; this lesson deploys them.
• DataOps and Data Platform Engineering for ownership of the release train.
• Airflow / orchestration lessons for DAG packaging and variables versus code.
• Cloud lessons 54–56 for the shapes of S3/GCS/ADLS, MWAA/Composer/Data Factory, and warehouses you will declare.

Detailed Core Concepts

What "deploy" means on a data platform

An application deploy often means: build artifact, run migrations, replace instances, shift traffic. A data platform deploy is a bundle of unlike parts that must stay coherent:

• Infrastructure: object storage, network paths, IAM roles and policies, KMS keys, orchestration environment sizing, warehouse clusters or reservations, secrets mounts.
• Orchestration code: DAGs, operators, connection ids (not passwords), pool definitions as code where supported.
• Data logic: SQL/dbt/Spark jobs that define the grain.
• Table contracts: DDL or migration files for mart tables, views, row-level policies, publish marker tables.
• Schedules and SLOs as config: resume windows, pause flags, alert thresholds that lesson 62 treats as protected.

Shipping the DAG without the DDL leaves a green import and a failing task. Shipping DDL without the DAG leaves an empty new table and a board still on the old object. Shipping IAM without the job identity leaves a silent permission miss at 05:10. One release unit. One rollback story.

IaC is the inventory you can diff

Infrastructure as code means desired state is declared in version control, applied by a tool that plans the delta, and recorded in state so the next plan is honest. The point for this course is not elegance. The point is that Monday's forensic question—"what is prod?"—has an answer in a commit SHA and a plan log.

Clickops creates drift: the console truth diverges from memory and from staging. Drift breaks promotion ("works in staging") and breaks reliability freezes (someone toggled a gate off in the UI). IaC with enforced apply paths makes drift visible: a plan that wants to undo a click is a signal, not a nuisance.

Terraform, CDK, Pulumi: choose a lane

Terraform uses HCL (or JSON), a large provider ecosystem, and explicit state backends. Plans are first-class. Modules map cleanly to "bucket + lifecycle," "MWAA environment," "IAM role for merge." Many data teams standardize here because reviews read as infrastructure, not as application code.

CDK (AWS CDK, CDK for Terraform, or cloud-specific variants) uses general-purpose languages to synthesize declarative config. It helps when the team already lives in TypeScript or Python and wants abstractions, tests, and loops. The risk is generating sprawling stacks that nobody can reason about in a diff. Discipline still matters: small constructs, clear boundaries.

Pulumi similarly uses general-purpose languages with real engines and state. Fit is strong when platform engineering already ships in Go/Python/TypeScript and wants one language across app and infra. Fit is weak when auditors want least-magic HCL and the data team does not want to own a software build for every bucket change.

This lesson's examples lean Terraform-shaped because the diffs are readable in Markdown. The promotion rules are the same if your stack is CDK or Pulumi: plan in CI, apply from controlled identities, no prod console as the happy path.

Environments are blast-radius boundaries

Dev is for authors: sample data, loose pauses, non-production secrets. Staging mirrors prod topology at reduced scale: same IAM shapes, same DAG package, subset of data or anonymized progress, same gate code. Prod is the SLO surface.

Promotion is not "merge to main equals prod." Prefer: merge → apply staging → run smoke (DAG import, SQL compile, gate dry-run, optional one-day sample) → manual or automated promote to prod with the same artifact versions. Lesson 62's freeze can block the prod promote step without blocking staging experiments.

Blue/green for tables

Web blue/green flips a load balancer. Table blue/green flips a pointer: a view, a synonym, a table swap, an Iceberg/Delta branch or tag, a BI dataset's certified table name. Writers build completion_attempt_v5_build, gates run against the build, marker writes only when green, then the pointer published.completion_attempt moves atomically to the build. Readers never see a half-swapped day. Rollback moves the pointer back. Deleting the old physical table is a delayed, separate job after the error budget says you are clear.

Avoid in-place destructive ALTER as the only path for grain changes. Additive columns can migrate in place with expand/contract. Grain changes and definition cutovers deserve a build table.

Secrets

Secrets are credentials, tokens, and private keys. They are not connection names. Store them in a secrets manager (AWS Secrets Manager, GCP Secret Manager, Azure Key Vault, HashiCorp Vault). Inject at runtime into the orchestrator or job identity. Terraform may create the secret resource and IAM to read it; the secret value is set out of band or via a sealed pipeline, never committed. State files can leak values if providers write them—use careful resource types, encryption of state, and access control on the backend. DAGs reference conn_id or env keys, not passwords in plain Python.

Never hand-click prod

Break-glass exists for outages when the pipeline is down and the board is already burning budget. Break-glass is: time-limited elevated role, ticket link, pair if possible, follow-up PR that captures the change within a stated hours window, and a post-apply plan that shows drift reconciled. A culture that break-glasses weekly does not have IaC. It has cosplay.

Architecture and Design Diagrams

Release train from commit to Monday-safe prod:

mermaidCopy
flowchart LR
  DEV["Dev: author DAG SQL DDL IaC"] --> PR["Pull request + plan"]
  PR --> STG["Apply staging"]
  STG --> SMOKE["Smoke: import compile gates"]
  SMOKE --> PROD["Promote same artifacts to prod"]
  PROD --> MARK["Publish marker path live"]
  PROD --> BUDGET["Blocked if error budget freeze"]

Blue/green table cutover:

mermaidCopy
sequenceDiagram
  participant CI as CI release
  participant Build as Build table
  participant Gate as Gates
  participant Ptr as Pointer view
  participant Board as Board readers
  CI->>Build: Load completion_attempt_v5_build
  CI->>Gate: Run grain and column checks
  Gate-->>CI: Pass
  CI->>Ptr: Swap published.completion_attempt
  Board->>Ptr: Read certified name
  Ptr-->>Board: v5 build

IaC ownership boundaries:

mermaidCopy
flowchart TB
  subgraph iac [IaC modules]
    BKT["Buckets + lifecycle"]
    IAM["Roles and policies"]
    ORCH["MWAA / Composer / Airflow env"]
    WH["Warehouse / reservation"]
    SEC["Secret resources + IAM read"]
  end
  subgraph app [App release unit]
    DAG["DAG package"]
    SQL["Transform SQL"]
    DDL["Mart DDL / pointers"]
  end
  iac --> ORCH
  DAG --> ORCH
  SQL --> ORCH
  DDL --> WH
  IAM --> ORCH
  IAM --> WH

Technical Explanation

Module boundaries that match blast radius

Split state so a bad apply does not require rewriting the world:

1. Network and shared KMS — rare changes, tight approval.
2. Data lake buckets and lifecycle — includes raw and published prefixes; versioning on; public access blocked.
3. Orchestration environment — MWAA/Composer size, startup scripts, DAG bucket sync identity.
4. Serving — warehouse, workgroups, reservations, pause schedules that lesson 58 timed.
5. Job identities — merge role, board read role, CI deploy role; least privilege per prefix and per table.

Do not put DAG Python in the same state as the VPC until you enjoy locked plans. Do put the IAM the DAG needs in a module the DAG release can depend on by name.

Illustrative Terraform shapes (not a full prod root module)

hclCopy
# Illustrative only — patterns, not a drop-in account.
resource "aws_s3_bucket" "lake" {
  bucket = "tlp-progress-lake-${var.env}"
}

resource "aws_s3_bucket_versioning" "lake" {
  bucket = aws_s3_bucket.lake.id
  versioning_configuration { status = "Enabled" }
}

resource "aws_iam_role" "merge" {
  name = "tlp-completion-merge-${var.env}"
  assume_role_policy = data.aws_iam_policy_document.merge_trust.json
}

# Grant merge role write to published prefix and marker table permissions
# via separate aws_iam_role_policy documents reviewed in PR.

CDK and Pulumi express the same resources with constructs or classes. Reviews should still show: bucket name per env, versioning, encryption, role trust, and prefix policies. If the diff cannot answer "can the board role write raw?", the module is too opaque.

Orchestration environments as code

Managed Airflow (MWAA, Cloud Composer) environments have versions, worker counts, and DAG folder locations. Declare them. Sync DAG artifacts with the same CI SHA that passed staging. Avoid editing DAG files directly in the prod DAG bucket. That edit is a prod deploy without CI.

Airflow Variables and Connections: prefer secret-backed connections. Variables that change behavior of certified publishes (definition_version, feature flags for degraded mode) should be code-configured or released as config files in the DAG package, not typed into the UI the night before a cutover.

Warehouse objects and DDL promotion

Treat mart DDL like migrations:

sqlCopy
-- 20261110_completion_attempt_v5_build.sql (illustrative)
CREATE TABLE published.completion_attempt_v5_build (
  learner_id STRING,
  lesson_id STRING,
  event_date DATE,
  definition_version STRING,
  attempted BOOLEAN,
  completed BOOLEAN
)
PARTITION BY event_date;

sqlCopy
-- 20261110_completion_attempt_pointer.sql (illustrative)
CREATE OR REPLACE VIEW published.completion_attempt AS
SELECT * FROM published.completion_attempt_v5_build;

CI applies build DDL in staging, runs a sample load and gates, applies pointer swap in staging, then repeats in prod with the same files. Engines differ (Snowflake swap, BigQuery table copy + view, Redshift late binding views, Iceberg branches). The pattern is stable: build, test, point, retire.

CI pipeline stages

1. Lint / unit — Python tests for grain helpers, SQL lint, IaC fmt and validate.
2. Plan — terraform plan (or cdk diff / pulumi preview) on staging and prod configs; publish plan artifact.
3. Build artifact — versioned DAG wheel or zip, SQL bundle, DDL bundle, tagged with git SHA.
4. Apply staging infra if plan includes infra (separate approval for shared modules).
5. Deploy staging app bundle — upload DAGs, apply DDL to staging, set pointers in staging only after gates.
6. Smoke — DAG bag import, run a non-prod day or dry-run gates against fixtures, confirm marker writer identity.
7. Prod approval — automatic if budget healthy and paths unprotected; required review if protected paths or freeze active.
8. Deploy prod — same SHA; no rebuild from main between staging and prod.
9. Post-deploy — read marker schema, confirm board role SELECT, archive plan apply logs.

yamlCopy
# Illustrative CI sketch — names vary by system
stages: [lint, plan, staging, smoke, prod]
prod:
  when: manual
  rules:
    - if: $ERROR_BUDGET_REMAINING_PCT < 25
      when: manual
      allow_failure: false
  script:
    - deploy_bundle.sh --env prod --sha "$CI_COMMIT_SHA"

Blue/green cutover checklist

• Build table loaded for required event_date range.
• Gates pass on build (grain, columns, volume band, version pin).
• Dual-run compare: aggregates of v4 vs v5 within tolerance for N days if definition changed.
• Pointer swap in a single transaction or atomic metadata operation.
• Board cache / BI extract invalidation on definition_version.
• Old table retained until lesson 62 budget and product sign deletion.
• Rollback runbook: pointer back; do not "fix forward" under freeze unless reliability owner agrees.

Secrets and CI identities

CI needs a deploy role that can upload DAGs and apply DDL. It should not have unbounded data read on vault prefixes. Job runtime roles need data access. Separating deploy identity from merge identity prevents a compromised DAG upload credential from also dumping the lake—defense in depth from lesson 57, encoded in IAM modules.

Never print secrets in plan logs. Mask CI variables. Rotate break-glass credentials on a schedule shorter than your comfort.

Encoding lesson 58 and 62 into modules

Pause schedules, workgroup byte cutoffs, and "one serving mart" are not only wiki entries. Example: a Terraform variable board_pause_enabled defaults false until a resume probe object exists; a required tag serving_mart=primary on exactly one warehouse schedule resource; a policy check in CI that fails if two mart refresh jobs are enabled. Soft documentation becomes hard to violate.

Pulumi and CDK sketches for the same lake bucket

Teams sometimes reject HCL. The resources do not change—only the expression.

typescriptCopy
// Illustrative AWS CDK sketch — not a full stack
const lake = new s3.Bucket(this, "ProgressLake", {
  bucketName: `tlp-progress-lake-${env}`,
  versioned: true,
  blockPublicAccess: s3.BlockPublicAccess.BLOCK_ALL,
  encryption: s3.BucketEncryption.S3_MANAGED,
});

pythonCopy
# Illustrative Pulumi sketch
bucket = s3.Bucket(
    "progress-lake",
    bucket=f"tlp-progress-lake-{env}",
    versioning=s3.BucketVersioningArgs(enabled=True),
    acl=None,
)

Review questions stay identical: is versioning on, is public access blocked, is encryption set, is the name env-scoped, who may write the published prefix? If the language abstraction hides those answers behind three helper methods, unwrap them in the PR description.

Airflow DAG deploy object model

Think in three objects: the environment (IaC), the bundle (CI artifact of DAGs + constraints), and the runtime config (secret references + released YAML). A fourth object—the warehouse DDL—must version with the bundle even though a different engine applies it. Promotion moves bundle+DDL+config together. Environment changes (worker count, Airflow version) move on their own train with stricter windows because they restart workers.

Sync mechanisms differ: MWAA S3 sync, Composer GCS DAG folder, git-sync sidecars on self-hosted. Regardless, the prod folder should be writable only by the deploy role. Humans read logs; they do not upload completion_dag.py from a laptop.

Database migration discipline for warehouses

Number migrations monotonically: 0001_marker.sql, 0002_completion_v4.sql, 0003_completion_v5_build.sql, 0004_pointer_to_v5.sql. Record applied ids in a ops.schema_migrations table from the deploy job. Never edit an applied migration file; add a new one. This is boring on purpose. Clickops CREATE OR REPLACE VIEW in prod without a migration id is how staging and prod pointers diverge for three weeks without anyone noticing.

Some engines support transactions for view swaps; some need carefully ordered statements. Document the engine-specific cutover in the migration comment. The course does not require one vendor's syntax—it requires an ordered, reviewed, SHA-linked history.

Feature flags versus definition pins

Feature flags that change certified grain are definition pins in disguise. Put them in config/reliability.yaml and the board SQL, released with DDL. Ad-hoc Airflow Variables named USE_NEW_COMPLETION recreate lesson 62's silent incorrect publish. Temporary flags for non-certified experiments may exist in non-prod; they still should be code, not UI, if they can be promoted by mistake.

State backends, workspaces, and blast radius

Remote state belongs in encrypted storage with locking (S3 + DynamoDB, GCS, Azure Storage, Terraform Cloud, Pulumi backend). One workspace or stack per environment minimum. Separating iam, lake, and orchestrator state reduces the chance that an orchestrator apply tries to recreate a bucket because someone refreshed the wrong root. Document the terraform_remote_state or stack reference graph. Circular references between roots are a design smell; publish outputs through a thin registry module instead.

Import existing clickops carefully. terraform import / pulumi import should be a reviewed PR that also adds the resource block matching reality. An import that does not add config creates phantom state—destroyed on the next careless apply. After import, run plan and expect empty. If plan wants changes, either accept them in the same PR or fix the config until empty, then merge.

DAG packaging and dependency pins

Pin operator provider versions and Python constraints in the artifact that MWAA/Composer installs. "Whatever the environment had last month" is how staging and prod diverge after an image upgrade. Ship a constraints file with the DAG bundle. Smoke must import the DAG bag with that constraint set. When the orchestrator version bumps in IaC, bump staging first, smoke, then prod—same rule as app code.

Database-like migrations for lake tables

Even when the lake is Iceberg or Delta, treat schema evolution as migrations with ordered ids. Additive columns: expand writers, backfill if needed, expand readers. Destructive changes: new table or new branch, dual-run, pointer flip, retire. Commit the migration id in the publish marker so lineage can answer which DDL SHA produced a day. Lesson 60's observability hooks love that column. Lesson 62's correctness story needs it when two definitions collide.

Multi-cloud and multi-tool honesty

If the org runs Composer in one project and a warehouse in another cloud, IaC still owns both; CI still promotes one SHA; secrets still do not live in either console's "convenient" plaintext. What you cannot do is pretend a single Terraform root must span both without credentials sprawl. Two roots with a contract (output bucket URL, output service account email) is fine. Two click paths is not.

Policy as code beyond fmt

Add lightweight policy checks in CI:

• No IAM policy with Action: "*" on lake buckets for human roles.
• No public ACL or public bucket policy.
• board_pause_enabled requires resume_probe_ms recorded under threshold.
• Exactly one resource tagged serving_mart=primary per env.
• DAG package must contain gates/ if dags/completion/ changes.
• Secret-looking strings fail the build.

Tools vary (OPA/Conftest, Checkov, custom scripts). The course cares that policy runs on the PR, not that you pick a brand.

Rollback is a deploy

Rollback means promoting the previous known-good SHA through the same pipeline, or flipping the table pointer back, or both. It does not mean SSHing into prod and editing files. Keep the last N artifacts immutable in object storage. Under lesson 62 freeze, rollback of reliability-impacting changes is still allowed—freezes block risky forward change, not recovery. State that exception in the sheet so on-call does not wait for a product VP at 05:30.

Human review that is not theater

PR templates for data platform changes should ask: Does this touch publish markers, gates, board SQL, IAM, or schedules? What is the rollback? Did staging smoke run on this SHA? What is remaining error budget? Empty checkboxes are a process smell. Require the reliability owner on protected path diffs. Do not require ten reviewers on a typo in a README.

Practical Implementation or Design Walkthrough

Problem

TechLearningPro must stop console deploys for the completion pipeline. Deliver an IaC + CI plan that releases DAG + SQL + DDL together, supports a definition cutover with blue/green tables, keeps secrets out of git, and honors an error-budget freeze.

Step 1: Inventory what exists only in clicks today

List buckets, prefixes, roles, MWAA/Composer env, warehouse, views, Airflow connections, alert channels. Each line becomes a resource or a documented external dependency. Anything left as "manual" needs an owner and a date to eliminate.

Step 2: Create env-parameterized modules

textCopy
modules/
  lake_bucket/
  merge_iam/
  orchestrator/
  serving/
  publish_marker_ddl/   # or live in sql/ migrations
environments/
  staging.tfvars
  prod.tfvars

env names buckets and roles. Prod tfvars set pause flags from the timed resume study. Staging uses smaller worker counts but the same IAM graph.

Step 3: Define the release unit layout

textCopy
dags/completion/          # DAG package
sql/transforms/           # merge SQL
sql/ddl/                  # ordered migrations
gates/                    # expectations / checks
infra/                    # Terraform/CDK/Pulumi
config/reliability.yaml   # freeze paths and thresholds from lesson 62

One version file or git SHA stamps all of them at promote time.

Step 4: Implement pointer-based cutover for completion-v5

1. Merge PR that only adds completion_attempt_v5_build DDL and transform behind a closed pointer.
2. Deploy to staging; load two sample days; run gates; dual-compare to v4.
3. Deploy build path to prod; load in parallel without moving pointer (shadow).
4. After dual-run window, PR that flips the view and updates definition_version pin in board SQL and reliability config together.
5. Promote under healthy budget; invalidate BI extracts.

Step 5: Secrets path

Create secret resources in IaC. CI policy: no *.pem, no password= in repo (pre-receive or secret scanner). Airflow connection: host and login method in code; password from secrets backend. Document rotation owner.

Step 6: Break-glass runbook (one page)

textCopy
1. Open incident linked to freshness or security.
2. Request time-bound role (max 2 hours).
3. Pair + record commands in ticket.
4. Reconcile with PR same business day or +1.
5. Revoke role; post diff of drift plan.

Step 7: Prove freeze works

Temporarily set budget remaining below threshold in a staging simulation. Open a PR touching gates/. Confirm prod promote is blocked. Clear the simulation. Do this before the first real burn, not during it.

Step 8: Sample CI job responsibilities (illustrative)

bashCopy
# Illustrative packaging — adapt to your CI
set -euo pipefail
SHA="${CI_COMMIT_SHA}"
BUNDLE="dist/completion-${SHA}.tgz"
tar -czf "$BUNDLE" dags/completion sql gates config/reliability.yaml
aws s3 cp "$BUNDLE" "s3://tlp-artifacts-${ENV}/completion/${SHA}.tgz"
# Deploy role unpacks into DAG bucket; does not use merge role credentials

pythonCopy
# Illustrative policy snippet run in CI
from pathlib import Path

PROTECTED = ("dags/completion/", "sql/published/", "gates/", "infra/serving/")

def touches_protected(changed_paths: list[str]) -> bool:
    return any(p.startswith(PROTECTED) or any(p.startswith(x) for x in PROTECTED) for p in changed_paths)

def allow_prod_promote(changed: list[str], budget_remaining_pct: float, approvals: set[str]) -> bool:
    if not touches_protected(changed):
        return True
    if budget_remaining_pct < 25 and "reliability-owner" not in approvals:
        return False
    return "data-platform" in approvals

Step 9: DDL order for the v5 cutover (checklist form)

1. Apply CREATE TABLE ..._v5_build in staging and prod (no pointer change).
2. Deploy transform that can write build table when TARGET=build.
3. Shadow-load N days in prod; gates on build; dual-run compare job.
4. Deploy board SQL branch that still reads pointer (old table).
5. Flip pointer in staging; run BI consumer test.
6. Flip pointer in prod in maintenance window before a low-risk morning if possible; Monday cutovers need extra margin.
7. Update reliability pin to completion-v5 in the same SHA as board SQL.
8. Schedule old table drop after budget-healthy period and product sign-off.

Step 10: What "done" looks like for leadership

• Last prod change to completion has a SHA link in the incident channel bookmark.
• terraform plan (or equivalent) on main is empty for prod workspaces each Monday.
• Secret scanner clean; break-glass count reviewed monthly.
• Freeze drill recorded quarterly.
• No Airflow Variable writes required for certified publishes.

Enterprise Real-World Scenarios

TechLearningPro's Friday night variable

An engineer sets an Airflow Variable SKIP_GRAIN_CHECK=true in prod UI to unblock a backfill. The variable remains. Monday ships without grain enforcement. Lesson 62's correctness SLO is violated while freshness is green. Fix: remove UI write access for certified DAG flags; move flags into the released config bundle; alert on drift between released config and runtime; add a gate that fails if skip flags are present in prod.

Northline's two Terraform roots and one mart

Platform team owns buckets in root A. Analytics owns "warehouse access" in root B with a console-created role. Staging green. Prod merge fails on SELECT. They unify job roles into a shared IAM module, export role ARNs through a remote state data source or a registry, and add a CI smoke that runs SELECT 1 as the merge role on staging and prod after each IAM apply. Cross-root without contracts is clickops with extra steps.

Composer upgrade that skipped staging workers

A patch apply jumps Composer versions in prod because staging was "close enough" on an older image. DAGs import fails at 04:00. Reliability budget burns. Policy: orchestrator version is a protected path; prod version must equal staging version that passed smoke at the same SHA of DAG constraints.

Blue/green without extract invalidation

Pointer flips to v5. Forty readers still see v4 from a BI extract refreshed nightly. Query latency SLI looks fine. Product sees wrong definition. Deploy checklist gains an explicit extract purge step owned by the BI identity, tested in staging by pinning a consumer.

Best Practices

• One release unit: DAG + SQL + DDL (+ config) under one SHA.
• Plan in CI; apply with controlled identities; store plans and apply logs.
• Separate deploy role from runtime data roles.
• Blue/green or pointer swap for grain and definition cutovers.
• Secrets in a manager; scanners on the repo; no passwords in DAGs.
• Staging topological parity for IAM and gates, even at smaller scale.
• Encode pause, single-mart, and freeze rules as checks, not only docs.
• Break-glass with expiry, ticket, and same-day reconcile PR.
• Protect reliability paths in branch rules.
• Prefer additive expand/contract for safe columns; build tables for grain changes.
• Keep artifacts immutable; rebuild from SHA rather than patching prod in place.
• Review terraform plan emptiness on a cadence as a health signal, like test pass rate.
• Document cross-stack contracts (role ARN, bucket name) as versioned outputs.
• Train every on-call on break-glass once before they need it at 05:30.

Environment data ethics

Staging needs realistic shapes without becoming a second production of PII. Prefer sampled or tokenized learner ids, truncated date ranges, and synthetic spikes for load tests. IaC should provision staging buckets with the same encryption and tighter retention. A staging clone of full prod progress "for realism" is a governance incident waiting for lesson 61's vocabulary. Deploy pipelines that can load fixtures for smoke without reading vault data.

Orchestrator config versus business logic

Connection ids, pool names, and package constraints are platform config. Completion definition SQL is business logic. Both ship as code; only business logic should churn weekly. If every feature requires editing MWAA environment variables in the console, your boundaries are wrong. Push definitions into versioned SQL files; keep environment sizing in IaC; keep secret values in the manager.

Common Mistakes and Anti-Patterns

Editing prod DAG files in the bucket. Undocumented deploy. Restore from git immediately and close write access.

Terraform for buckets, clickops for IAM. The failure mode at 05:10 is always IAM. Bring identities into the same standard.

Applying prod with a different commit than staging smoked. "Main moved." Pin SHA.

In-place grain rewrite without pointer. Readers observe partial days. Use build tables.

Secret values in Terraform variables committed to git. Rotate, purge history if needed, move to manager.

Shared admin role for CI and merge. Over-privilege; widen blast radius of CI compromise.

Skipping staging because "it's only SQL." SQL is what the board reads. Especially do not skip under freeze.

UI Variables as feature flags for certified paths. They drift. Release them as code.

Deleting the old green table on cutover day. Keep until budget and product agree.

Break-glass without reconcile. Drift becomes permanent folklore.

One shared state file for all envs. A bad apply variable promotes destruction across prod. Separate backends or workspaces.

Applying DDL from a laptop with admin credentials. Bypasses CI evidence and audit. Use the deploy role through the pipeline.

Treating terraform apply failures as "just retry." Read the plan; partial applies need state attention.

Version pinning only in prod. Staging drifts older; upgrades then explode on Monday. Promote orchestrator versions the same way as DAGs.

No artifact immutability. Rebuilding from a moving branch between staging smoke and prod promote ships untested commits.

IAM changes without a permission smoke probe. Plans look fine; 05:10 fails. Probe after apply.

Pointer flip without board SQL and reliability pin in the same SHA. Version skew by design.

Troubleshooting and Design Review

Plan shows destroy of the lake bucket. Wrong state workspace or name interpolation. Stop. Confirm backend key per env. Never auto-approve destroys on data buckets.

DAG import OK, task AccessDenied. IAM module not applied or wrong prefix. Smoke should include a permission probe.

Pointer flipped, board unchanged. Extract/cache layer. Invalidate and verify definition_version in the result.

Staging passed, prod DDL failed. Engine permission or object already exists from old clickops. Import into state or drop through a reviewed migration, do not click past it.

Freeze did not block deploy. Path not listed in protected config, or promote job bypasses rules. Test the block in staging drills.

Secret rotation broke connections. Deploy role updated secret; workers cached old value. Document refresh mechanism for the orchestrator.

Design review:

• Can you recreate prod IAM from git alone?
• Is there a single SHA for the last prod promote of completion?
• How does rollback of a definition cutover work in one operation?
• Who can write Airflow Variables in prod today?
• Does CI enforce lesson 62 freeze?
• Where do secret values live, and who rotates them?
• What is break-glass TTL?

Interview Questions and Answers

Q1. Why is clickops dangerous for data platforms specifically?

Because the product is the data and the permissions around it. A console change to IAM, a gate flag, or a view definition alters Monday's board without review, test, or rollback. Drift also makes staging a lie. IaC plus CI gives a diff, a plan, and a SHA when the freshness SLO burns.

Q2. How do you promote a DAG change safely?

Package DAG, SQL, and DDL under one git SHA. Apply and smoke in staging with topological IAM parity. Promote the same artifacts to prod. If the change hits protected reliability paths or error budget is low, require explicit approval. Do not edit the prod DAG bucket by hand.

Q3. What is blue/green for warehouse tables?

Build a parallel table, validate it, then atomically repoint the certified name (view, swap, or catalog pointer). Readers keep using the stable name. Rollback repoints to the previous build. It avoids half-visible grain changes during load.

Q4. Terraform versus CDK versus Pulumi for a data team?

All can declare the same resources. Terraform/HCL often wins for readable infra reviews and broad providers. CDK/Pulumi win when the team wants native language abstractions and already has strong software delivery. I care more about state discipline, CI plans, and module boundaries than the brand.

Q5. How do you handle secrets in Airflow-based pipelines?

Store values in a secrets manager. Create secret resources and read permissions in IaC. Reference connection ids in DAGs. Block commits of credentials with scanning. Separate CI deploy credentials from runtime data access.

Q6. How does error budget connect to deployment?

Lesson 62 defines freeze thresholds and protected paths. CI reads remaining budget and path diffs. Below threshold, prod promote of gate/SQL/schedule changes stops or needs reliability approval. That makes the SLO a control, not a chart.

Q7. What belongs in the same release as a definition_version cutover?

Transform SQL, DDL/pointer, board SQL filter, reliability config pin, gate expectations, and BI invalidation steps. Splitting them produces version skew—the failure mode that looks like "random wrong numbers."

Q8. How do you stop a second serving mart from reappearing after a bake-off?

Encode a single primary tag or a CI check that fails when two refresh schedules for the same grain are enabled. Document reopen conditions in a decision record, not in a second cron.

Q9. What is break-glass, correctly done?

Short-lived elevated access during an incident, logged and ticketed, followed by a PR that captures the change and a plan that clears drift. It is not a weekly habit. Metrics on break-glass frequency are a platform health signal.

Q10. Staging is expensive. What parity can you drop?

You can drop data volume and worker count. You should not drop IAM shape, gate code, pointer mechanism, or secret injection path. Those are where prod breaks.

Q11. How do you roll back a bad DDL apply?

Prefer forward fix with pointer rollback to the previous build table. If you must restore, use versioned storage and documented RTO from lesson 62. Do not "click delete" without a rebuild plan. Keep prior builds until signed retirement.

Q12. Where do Airflow Connections fit in IaC?

Connection metadata and secret references can be managed as code; secret values stay in the manager. If the operator only supports UI entry, automate via API from CI using vault-backed values, still not human prod typing.

Hands-On Exercises

Exercise: Deployment plan for a versioned completion pipeline

Problem statement. Create a deployment and IaC plan for TechLearningPro's completion pipeline promoting to completion-v5 without hand-clicking prod. Honor lesson 62 freeze behavior and lesson 58 single-mart / pause rules.

Tasks.

1. Inventory infra and app components into modules vs release unit.
2. Sketch env layout and promotion stages with SHA pinning.
3. Design blue/green pointer cutover and rollback.
4. Define secret handling and CI identity separation.
5. Write break-glass policy with TTL and reconcile.
6. Map protected paths to CI rules using a freeze threshold.
7. List smoke tests required before prod promote.
8. Call out what must match between staging and prod.

Expected deliverables. Architecture diagram, directory layout, CI stage list, cutover runbook, break-glass page, freeze rule sketch.

Evaluation rubric.

Deep dive: promoting a definition cutover without a hero night

Walk a full completion-v4 → completion-v5 release as the interviewers of your future self will ask for.

Week −2: land build-table DDL and a transform flag TARGET=build|pointer defaulting to pointer in prod. Staging writes build only. Gates gain v5 expectations but do not yet block v4 publishes. Reliability pin stays v4.

Week −1: shadow-load seven days into prod build table using the merge role's new permission (granted via IaC PR). Dual-run job compares lesson-level completion counts with tolerance. Differences go to a review doc, not to a Slack shrug. BI receives a staging extract pointed at staging's flipped view for UAT.

Cutover SHA contents (must be one promote):

1. Pointer view → v5 build.
2. Board SQL definition_version = 'completion-v5'.
3. config/reliability.yaml pin update.
4. Gate pack that fails closed on v4-only assumptions removed or updated.
5. Extract invalidation step or ticket with owner checkbox in the promote job log.

Cutover morning: choose a Tuesday if you can. Keep pause disabled. On-call watches marker and query SLIs. Rollback is pointer + pin revert using the previous artifact SHA—not a scramble of console edits.

Week +1: if budget healthy and product signs, schedule old table drop migration. Until then storage pays for safety; lesson 58 accepts that line item when named.

Deep dive: IAM matrix for the completion path

Encode this matrix as policy documents in IaC. CI smoke assumes merge role can INSERT marker and cannot PutObject to the DAG bucket. CI deploy role opposite. Cross-wiring those two is a common incident.

Deep dive: Composer / MWAA specific footguns

• Environment update replaces workers; in-flight tasks may retry—do not schedule env upgrades inside the critical path window.
• Requirements.txt / plugins zip changes need the same SHA promotion as DAGs; an out-of-band plugin upload is clickops.
• DAG folder sync delay: after upload, allow import time before declaring deploy success; smoke should query the orchestrator API for DAG presence and import errors.
• Connection extras often hold non-secret config; still avoid pasting passwords. Hostnames can live in code; credentials cannot.
• Autoscaling that fights lesson 58 budgets: set max workers in IaC so a bad DAG cannot explode cost overnight.

Sample module outputs contract

hclCopy
# Illustrative outputs consumed by the app deploy pipeline
output "merge_role_arn" {
  value = aws_iam_role.merge.arn
}

output "dag_bucket" {
  value = aws_s3_bucket.dags.bucket
}

output "published_prefix" {
  value = "s3://${aws_s3_bucket.lake.bucket}/published/completion/"
}

The promote job reads these outputs for the target env rather than hardcoding ARNs in DAG Python. Hardcoded prod ARNs in code are how staging "works" with prod credentials by accident.

Operational metrics for the deploy system itself

Track change fail rate, lead time from merge to prod, break-glass count, time-to-reconcile drift, and percent of prod applies with empty follow-up plan. These are DataOps metrics from lesson 59 applied to the train that ships completion. A reliability freeze that cannot be enforced will show up as unprotected path promotes in this list.

Appendix: end-to-end promote script commentary

A promote script is not glamorous, but interviews and incidents ask for it. Pseudocode responsibilities:

1. Resolve env (staging|prod) and SHA.
2. Download immutable artifact completion-$SHA.tgz from the artifact bucket.
3. Verify checksum and that config/reliability.yaml parses.
4. If prod and protected paths changed, require approval token and check error-budget remaining via an API or checked-in metric snapshot.
5. Upload DAG payload to the orchestrator DAG bucket under a SHA prefix, then flip a current pointer object only after bag import smoke returns zero import errors.
6. Apply pending DDL migrations not yet in ops.schema_migrations, recording each id.
7. Optionally trigger a staging-only smoke DAG run with a fixture event_date.
8. Emit a chat message with SHA, migrations applied, and who approved.

Failure mid-script must be safe: do not flip current if import failed; do not record migrations that did not apply; do not leave two current pointers. Idempotent re-entry is mandatory so on-call can rerun after fixing a transient S3 error.

Appendix: drift detection weekly job

Once per week, CI or a scheduled task runs plan against prod workspaces with no apply. If plan is non-empty, open a ticket titled "infra drift" with the plan artifact. Common causes: console security group tweak, manual warehouse resize, emergency IAM allow. The ticket owner either reverts via apply of mainline or commits the intentional change. Without this job, IaC slowly becomes lies.

Appendix: secrets rotation playbook

Quarterly or on personnel change: rotate warehouse passwords and API tokens in the secret manager; bounce orchestrator workers or refresh connections per vendor docs; run smoke DAG that reads one secret-backed connection; revoke old versions. IaC does not need to change if it only references secret names. If someone embedded a password in an Airflow Variable during break-glass, the drift detector and secret scanner should both scream—delete the Variable as part of rotation.

Appendix: checklist before first prod cutover under IaC

• Remote state locked and encrypted per env
• Plan empty on main for prod workspaces after import
• Deploy role and merge role separation proven by negative smoke tests
• Secret scanner on and break-glass TTL documented
• Staging topological IAM parity validated
• Freeze drill blocked a protected-path promote
• Blue/green pointer rollback rehearsed once in staging
• Board extract invalidation owner named
• Reliability.yaml readable by CI
• Monday critical path timed after a staging-like apply

Ship only when the list is honest. A half-migrated platform that still clickops IAM is worse than honest clickops, because people trust the wrong inventory.

Lesson Summary

Data platform production is a coupled bundle of infrastructure, orchestration, SQL, DDL, and reliability config. Declare infra in IaC so prod has a diffable inventory. Promote app artifacts as one SHA through staging smokes into prod. Cut over marts with pointers, not live rewrites. Keep secrets in managers. Encode cost and SLO policies into checks. Treat console changes as break-glass incidents to reconcile, not as normal delivery.

Data Engineering System Design asks you to design the full learning-progress platform under interview constraints—with deployability and SLOs as first-class requirements. Data Engineering Interview Questions and Answers will pressure-test how clearly you can tell this story on a whiteboard.

Further Reading

• Terraform documentation for state, modules, and plans.
• AWS CDK, Pulumi docs, or CDK for Terraform for language-native alternatives.
• Amazon MWAA and Cloud Composer environment management and DAG bucket patterns.
• Airflow Security and Secrets Backends.
• Your warehouse's migration and swap documentation (BigQuery, Redshift, Snowflake, Synapse) for pointer cutovers.
• Data Reliability and SLA/SLO Design for freeze inputs.
• Data Platform Security and Governance for identity design you now deploy.