04 - GitHub Actions for Data Engineering

57 min read

GitHub Actions for Data Engineering

Quote

“Improving daily work is even more important than doing daily work.”

— Gene Kim, The Phoenix Project (2013)

How data-engineering workflow types map to risk and trigger

Data-engineering CI/CD workflows fall into distinct categories based on what they validate, when they run, and what blast radius they control. The taxonomy below maps every pattern in this page to its category and trigger context.


flowchart TB
    subgraph CI["CI — Validate on every push/PR"]
        CIPAD[" "]
        SQL["SQL Validation"]
        DBT["dbt Parse & Lint"]
        DAG["Airflow DAG Import"]
        SPARK["PySpark Tests"]
        NB["Notebook Hygiene"]
        SCHEMA["Schema Contracts"]
        CIPAD ~~~ SQL
        CIPAD ~~~ DBT
        CIPAD ~~~ DAG
        CIPAD ~~~ SPARK
        CIPAD ~~~ NB
        CIPAD ~~~ SCHEMA
    end

    subgraph CD["CD — Deploy on merge/release"]
        CDPAD[" "]
        TF["Terraform Apply"]
        IMG["Pipeline Image Build"]
        DEPLOY["Pipeline Deploy"]
        CDPAD ~~~ TF
        CDPAD ~~~ IMG
        CDPAD ~~~ DEPLOY
    end

    subgraph OPS["Operational — Manual or Scheduled"]
        OPSPAD[" "]
        BACKFILL["Backfill Workflows"]
        COST["Cost Monitoring"]
        DQ["Data Quality Audits"]
        OPSPAD ~~~ BACKFILL
        OPSPAD ~~~ COST
        OPSPAD ~~~ DQ
    end

    SQL --> DQ
    DBT --> DEPLOY
    TF --> DEPLOY
    style CIPAD fill:transparent,stroke:transparent,color:transparent
    style CDPAD fill:transparent,stroke:transparent,color:transparent
    style OPSPAD fill:transparent,stroke:transparent,color:transparent
    IMG --> DEPLOY
    BACKFILL --> DQ

    style CI fill:#1a1b26,stroke:#7aa2f7,color:#c0caf5
    style CD fill:#1a1b26,stroke:#9ece6a,color:#c0caf5
    style OPS fill:#1a1b26,stroke:#e0af68,color:#c0caf5

Data-engineering workflow taxonomy. CI workflows (blue) validate code on every push or PR — fast feedback, read-only access. CD workflows (green) deploy infrastructure and images on merge — write access, environment-gated. Operational workflows (yellow) run on demand or on schedule — controlled blast radius, audit-logged.

CategoryPatternsTriggerCredentialsBlast Radius
SQL ValidationBigQuery dry-run, SQL Server PARSEONLY, Snowflake/Redshiftpush, pull_requestRead-only warehouseNone — no data modified
dbt CIParse, lint, build, test, slim CIpush, pull_requestEphemeral schema writeCI schema only
Orchestrator CIAirflow DAG import, Dagster asset check, Prefect flow validationpushNone (local import)None
Spark/PySparkUnit tests with local SparkSessionpushNoneNone
InfrastructureTerraform plan (CI), apply (CD)push / mergeCloud provider adminPlan: none; Apply: infrastructure
Data QualityAssertion checks, report artifactspush, scheduleRead-only warehouseNone
Schema ContractsJSON Schema validation, breaking change detectionpushNoneNone
Notebook HygieneOutput stripping, structure validationpushNoneNone
Pipeline ImagesDocker build, push to GHCRpushPackage writeContainer registry
BackfillDispatch with typed inputs, dry-run, prod approvalworkflow_dispatchWrite to target tableTarget partitions
Cost MonitoringBilling queries, dataset size reportsschedule, workflow_dispatchRead-only billingNone

Production writes from PR-triggered workflows

A workflow triggered by pull_request that writes to production datasets creates an unreviewed blast radius. Any contributor who opens a PR can trigger production mutations.

Gate production writes behind environments

Use environment: production with required reviewers for any job that modifies production data. Reserve pull_request triggers for read-only validation (dry-run, parse, lint). Only push to main (post-merge) or workflow_dispatch should trigger write operations.

SQL validation before code reaches the main branch

SQL validation catches syntax errors, missing columns, and type mismatches before code reaches main. The validation cost is zero or near-zero: BigQuery dry-run processes no data (no billing), and SQL Server PARSEONLY checks syntax without compilation. Every SQL file in the repository should be validated on every push.

SQL validation | BigQuery dry-run

BigQuery dry-run validates SQL syntax and resolves table references, column names, and types against the live catalog. It returns the estimated bytes that would be processed if the query ran, without actually scanning any data. This makes it free to run in CI.

Validate all SQL files with BigQuery dry-run

On every push that modifies SQL files. It is typically triggered by push event with path filter on sql/**. GitHub-hosted runner, GCP OIDC authentication with read-only BigQuery access. No data is read or billed. Catch SQL syntax errors, missing table/column references, and type mismatches before code review.

Validate all SQL files in the sql/ directory against BigQuery using dry-run mode.

name: "Demo: DE SQL Validation"
 
on:
  push:
    paths:
      - "sql/**"
      - ".github/workflows/demo-de-sql-validation.yml"
  workflow_dispatch:
 
permissions:
  contents: read
  id-token: write
 
jobs:
  bigquery-dry-run:
    name: BigQuery Dry-Run
    runs-on: ubuntu-latest
    timeout-minutes: 5
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - id: auth
        uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
        with:
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
      - uses: google-github-actions/setup-gcloud@77e7a554d41e2ee56fc945c52dfd3f33d12def9a # v2.1.4
 
      - name: Dry-run all SQL files
        run: |
          echo "## BigQuery Dry-Run Results" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "| File | Status | Bytes Processed |" >> $GITHUB_STEP_SUMMARY
          echo "|------|--------|-----------------|" >> $GITHUB_STEP_SUMMARY
 
          exit_code=0
          for sql_file in sql/*.sql; do
            filename=$(basename "$sql_file")
            echo "::group::Validating $filename"
            if output=$(bq query --use_legacy_sql=false --dry_run < "$sql_file" 2>&1); then
              bytes=$(echo "$output" | grep -oP 'process \K[0-9]+' || echo "0")
              echo "✓ $filename: $output"
              echo "| $filename | ✅ Valid | $bytes bytes |" >> $GITHUB_STEP_SUMMARY
            else
              echo "✗ $filename: $output"
              echo "| $filename | ❌ Error | — |" >> $GITHUB_STEP_SUMMARY
              echo "::error file=$sql_file::SQL validation failed: $output"
              exit_code=1
            fi
            echo "::endgroup::"
          done
          exit $exit_code

Workflow run output (run 24314051823, triggered by push to main, commit 0dd7142):

✓ main Demo: DE SQL Validation · 24314051823
Triggered via push
 
JOBS
✓ BigQuery Dry-Run in 12s (ID 70988539870)
 
BigQuery Dry-Run — Dry-run all SQL files:
  ✓ count_ohlcv_rows.sql: Query successfully validated. Assuming the tables
    are not modified, running this query will process 1200 bytes of data.
  ✓ validate_trading_calendar.sql: Query successfully validated. Assuming the
    tables are not modified, running this query will process 410690 bytes of data.
Flag / KeyValueDescription
--use_legacy_sql=falsebooleanForces Standard SQL dialect instead of legacy SQL. Required for modern BigQuery syntax.
--dry_runbooleanValidates the query without executing it. Returns byte estimate. No billing.
--format=jsonstringReturns structured JSON output instead of tabular text (useful for programmatic parsing).
--project_idstringOverride the default project. Set automatically by setup-gcloud from OIDC credentials.

BigQuery dry-run cost estimation

The byte estimate from --dry_run maps directly to on-demand query pricing: 0.0000025 — effectively free. Use this to flag expensive queries in CI before they reach production.

SQL validation | SQL Server PARSEONLY

SQL Server’s SET PARSEONLY ON checks SQL syntax without compiling or executing the statement. Combined with a service container running SQL Server in the workflow, this validates T-SQL migrations without needing a production database connection.

Validate T-SQL migrations with PARSEONLY

On every push that modifies migration files. It is typically triggered by push event with path filter on migrations/**. GitHub-hosted runner with a SQL Server 2022 service container. No external credentials required. Catch T-SQL syntax errors in migration scripts before they reach a staging or production database.

Validate all migration files against SQL Server 2022 using PARSEONLY.

  sqlserver-parseonly:
    name: SQL Server PARSEONLY
    runs-on: ubuntu-latest
    timeout-minutes: 5
    services:
      sqlserver:
        image: mcr.microsoft.com/mssql/server:2022-latest
        env:
          ACCEPT_EULA: "Y"
          SA_PASSWORD: "StrongPass#2026"
        ports:
          - 1433:1433
        options: >-
          --health-cmd "echo 'SELECT 1' | /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'StrongPass#2026' -C"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - name: Install sqlcmd
        run: |
          curl -sSL https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc > /dev/null
          sudo add-apt-repository "$(curl -sSL https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)" 2>/dev/null || true
          sudo apt-get update -qq
          sudo ACCEPT_EULA=Y apt-get install -y -qq mssql-tools18 2>/dev/null || sudo ACCEPT_EULA=Y apt-get install -y -qq mssql-tools 2>/dev/null
 
      - name: Validate migrations with PARSEONLY
        run: |
          SQLCMD_BIN=$(command -v sqlcmd || find /opt/mssql-tools*/bin -name sqlcmd 2>/dev/null | head -1)
          exit_code=0
          for sql_file in migrations/*.sql; do
            filename=$(basename "$sql_file")
            if "$SQLCMD_BIN" -S localhost -U sa -P 'StrongPass#2026' -C \
              -Q "SET PARSEONLY ON; $(cat "$sql_file")" 2>&1; then
              echo "✓ $filename: syntax valid"
            else
              echo "✗ $filename: syntax error"
              echo "::error file=$sql_file::SQL parse failed"
              exit_code=1
            fi
          done
          exit $exit_code

SQL validation | warehouse comparison

Different warehouse engines require different validation approaches. The table below compares the validation mechanisms available for each major data warehouse.

WarehouseValidation MethodCostRequires CredentialsCatches
BigQuerybq query --dry_runFree (no data scanned)OIDC or SA keySyntax, schema, column types, permissions
SQL ServerSET PARSEONLY ON with service containerFree (local container)None (local SA)Syntax only
SnowflakeEXPLAIN or snowsql --query "EXPLAIN ..."Free (compilation only)Snowflake credentialsSyntax, schema, types
RedshiftEXPLAIN via psql or AWS SDKFree (compilation only)AWS credentialsSyntax, schema, types
Databricksspark.sql(query).explain() or REST API /sql/statements with EXPLAINFree (no compute)Databricks tokenSyntax, schema, types
DuckDBEXPLAIN in local DuckDB (no credentials)FreeNoneSyntax only (no live schema)

Snowflake and Redshift require active credentials in CI

Unlike BigQuery (OIDC) or SQL Server (local container), Snowflake and Redshift validation requires live credentials stored as GitHub secrets. The EXPLAIN command compiles the query plan without executing it, but it still needs an authenticated session.

Use least-privilege read-only roles

Create a dedicated CI service user with SELECT permissions only on relevant schemas. For Snowflake, use a role like CI_READER with USAGE on the warehouse and SELECT on schemas. For Redshift, use a read-only group. Never reuse production service account credentials for CI validation.

dbt validation from parse-only checks to full test runs

dbt CI workflows validate SQL models, enforce style rules, and optionally run tests against an ephemeral schema. The minimal CI setup — parse and lint — requires no warehouse connection and catches most errors. The full CI setup — build and test — requires a service account with write access to an ephemeral dataset, providing complete validation at higher cost.

dbt CI | parse and lint

The lightest dbt CI workflow: parse the project to verify model compilation and lint SQL files with SQLFluff. This runs without a warehouse connection and catches syntax errors, undefined references, and style violations.

Parse dbt project and lint SQL models

On every push that modifies dbt model files. It is typically triggered by push event with path filter on dbt_project/**. GitHub-hosted runner. No warehouse credentials needed for parse. SQLFluff runs locally. Catch dbt compilation errors and SQL style violations before code review.

Parse the dbt project and lint SQL models with SQLFluff.

name: "Demo: DE dbt CI"
 
on:
  push:
    paths:
      - "dbt_project/**"
      - ".github/workflows/demo-de-dbt-ci.yml"
  workflow_dispatch:
 
permissions:
  contents: read
 
concurrency:
  group: dbt-ci-${{ github.ref }}
  cancel-in-progress: true
 
jobs:
  dbt-parse:
    name: dbt Parse & Lint
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: "3.12"
 
      - name: Install dbt-core
        run: pip install dbt-core dbt-bigquery sqlfluff sqlfluff-templater-dbt
 
      - name: dbt parse
        working-directory: dbt_project
        run: |
          dbt parse --profiles-dir /dev/null 2>&1 || true
          echo "dbt parse completed — checking manifest..."
          if [ -f target/manifest.json ]; then
            model_count=$(python3 -c "import json; m=json.load(open('target/manifest.json')); print(len([n for n in m['nodes'] if m['nodes'][n]['resource_type']=='model']))")
            test_count=$(python3 -c "import json; m=json.load(open('target/manifest.json')); print(len([n for n in m['nodes'] if m['nodes'][n]['resource_type']=='test']))")
            echo "✓ Manifest generated: $model_count models, $test_count tests"
          else
            echo "⚠ No manifest generated (expected without a valid profile)"
          fi
 
      - name: SQLFluff lint dbt models
        working-directory: dbt_project
        run: sqlfluff lint models/ --dialect bigquery --format github-annotation-native 2>&1 || true
 
      - name: Upload dbt artifacts
        if: always()
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
        with:
          name: dbt-artifacts-${{ github.sha }}
          path: |
            dbt_project/target/manifest.json
            dbt_project/target/run_results.json
          if-no-files-found: ignore
          retention-days: 7

Workflow run output (run 24314013412, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE dbt CI · 24314013412
Triggered via push
 
JOBS
✓ dbt Parse & Lint in 24s (ID 70988480391)
 
dbt Parse & Lint — dbt parse:
  ⚠ No manifest generated (expected without a valid profile)
 
dbt Parse & Lint — SQLFluff lint dbt models:
  models/staging/stg_trading_calendar.sql:
    LT14: The 'WHERE' keyword should always start a new line. [layout.keyword_newline]
dbt CI StrategyWarehouse ConnectionWhat It ValidatesCostWhen to Use
Parse onlyNoneJinja compilation, model references, source definitionsFreeEvery PR — fast feedback
Parse + lintNoneAbove + SQL style rules (SQLFluff/sqlfmt)FreeEvery PR
Build + testEphemeral schemaAbove + actual query execution, data testsWarehouse computeMerge to main or nightly
Slim CIEphemeral schemaOnly modified models (state:modified+)Reduced computeEvery PR (large projects)

dbt CI | ephemeral schema isolation

For full dbt CI (build + test), create an ephemeral schema per CI run to isolate test data from production. The schema is created at job start and destroyed at job end, even on failure.

Shared CI schemas cause data corruption

If multiple CI runs write to the same schema (e.g., ci_schema), concurrent runs overwrite each other’s test data. Results become non-deterministic and failures are unreproducible.

Use PR-scoped ephemeral schemas

Name the schema using the PR number or run ID: ci_pr_${{ github.event.pull_request.number }} or ci_run_${{ github.run_id }}. Clean up with bq rm -r -f in an if: always() step.

Ephemeral schema naming patterns for dbt CI.

# In the dbt CI workflow's environment variables:
env:
  DBT_CI_SCHEMA: "ci_pr_${{ github.event.pull_request.number || github.run_id }}"
 
# Create schema before dbt build:
- name: Create ephemeral schema
  run: bq mk --dataset "$GCP_PROJECT:$DBT_CI_SCHEMA"
 
# Run dbt build against the ephemeral schema:
- name: dbt build
  run: dbt build --target ci --vars "{ci_schema: '$DBT_CI_SCHEMA'}"
 
# Clean up (always, even on failure):
- name: Drop ephemeral schema
  if: always()
  run: bq rm -r -f "$GCP_PROJECT:$DBT_CI_SCHEMA"

dbt CI | cost control and slim CI

Large dbt projects can have hundreds of models. Running all of them on every PR is expensive and slow. Slim CI uses dbt’s state comparison to run only modified models and their downstream dependents.

Slim CI runs only changed models using state comparison with the production manifest.

# Download the production manifest from a previous successful run:
- name: Download production manifest
  uses: actions/download-artifact@v4
  with:
    name: dbt-manifest-production
    path: target-prod/
  continue-on-error: true  # First run won't have a manifest
 
# Run only modified models and their children:
- name: dbt build (slim CI)
  run: |
    if [ -f target-prod/manifest.json ]; then
      dbt build --select state:modified+ --defer --state target-prod/
    else
      echo "No production manifest found — running full build"
      dbt build
    fi
FlagDescription
--select state:modified+Select models modified since the comparison state, plus all downstream dependents
--deferFor unmodified models, defer to the production manifest instead of rebuilding
--state target-prod/Path to the production manifest for state comparison
--exclude tag:nightlyExclude models tagged as nightly-only from CI runs
--target ciUse the CI-specific profile target (ephemeral schema, reduced compute)

Pipeline and orchestrator validation before deployment

Orchestrator validation catches broken DAG definitions, missing dependencies, and import errors before deployment. These checks run locally without connecting to production schedulers.

orchestrator CI | Airflow DAG import

The Airflow DAG import check loads every Python file in the dags/ directory into an Airflow environment and verifies it produces valid DAG objects. This catches import errors, missing Python packages, circular dependencies, and invalid scheduling expressions.

Validate Airflow DAGs on push

On every push that modifies DAG files. It is typically triggered by push event with path filter on dags/**. GitHub-hosted runner with Airflow installed from PyPI (constrained). No connection to production Airflow. Catch DAG import errors, missing dependencies, and invalid task definitions before deployment.

Validate all Airflow DAG files by importing them into a clean Airflow environment.

name: "Demo: DE Airflow DAG Validation"
 
on:
  push:
    paths:
      - "dags/**"
      - ".github/workflows/demo-de-airflow-validation.yml"
  workflow_dispatch:
 
permissions:
  contents: read
 
jobs:
  dag-validation:
    name: Validate Airflow DAGs
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: "3.12"
 
      - name: Install Airflow (constraints)
        run: |
          pip install "apache-airflow==2.10.5" \
            --constraint "https://raw.githubusercontent.com/apache/airflow/constraints-2.10.5/constraints-3.12.txt"
 
      - name: DAG import check
        env:
          AIRFLOW_HOME: ${{ runner.temp }}/airflow
          AIRFLOW__CORE__LOAD_EXAMPLES: "false"
        shell: bash
        run: |
          mkdir -p "$AIRFLOW_HOME"
          airflow db init 2>/dev/null
 
          cat > /tmp/check_dag.py << 'PYEOF'
          import sys, importlib.util, airflow.models
          dag_file = sys.argv[1]
          spec = importlib.util.spec_from_file_location("dag_module", dag_file)
          mod = importlib.util.module_from_spec(spec)
          spec.loader.exec_module(mod)
          dags = [v for v in vars(mod).values() if isinstance(v, airflow.models.DAG)]
          for d in dags:
              tasks = list(d.task_ids)
              print(f"{d.dag_id}|{len(tasks)}|{d.schedule_interval}")
          PYEOF
 
          exit_code=0
          for dag_file in dags/*.py; do
            filename=$(basename "$dag_file")
            if output=$(python3 /tmp/check_dag.py "$dag_file" 2>&1); then
              while IFS='|' read -r dag_id task_count schedule; do
                echo "✓ $filename - $dag_id ($task_count tasks, schedule=$schedule)"
              done <<< "$(echo "$output" | grep '|')"
            else
              echo "✗ $filename - import failed"
              echo "::error file=$dag_file,title=DAG import failed::$output"
              exit_code=1
            fi
          done
          exit $exit_code

Workflow run output (run 24314013417, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE Airflow DAG Validation · 24314013417
Triggered via push
 
JOBS
✓ Validate Airflow DAGs in 24s (ID 70988480415)
 
Validate Airflow DAGs — DAG import check:
  DB: sqlite:////home/runner/work/_temp/airflow/airflow.db
  Initialization done
  ✓ daily_ingest.py - daily_ohlcv_ingest (1 tasks, schedule=0 18 * * 1-5)

orchestrator CI | Dagster and Prefect

Dagster and Prefect both support CI validation without connecting to production infrastructure. Dagster’s dagster asset list and Prefect’s prefect flow validate verify that asset/flow definitions compile and resolve dependencies.

Dagster asset validation pattern (no production connection required).

# Dagster CI — validate asset definitions
- name: Install Dagster
  run: pip install dagster dagster-cloud
 
- name: Validate Dagster assets
  run: |
    dagster asset list --module my_project.assets 2>&1
    echo "✓ All Dagster assets resolve"

Prefect flow validation pattern.

# Prefect CI — validate flow definitions
- name: Install Prefect
  run: pip install prefect
 
- name: Validate Prefect flows
  run: |
    python -c "
    from my_project.flows import daily_ingest, weekly_report
    print(f'daily_ingest: {daily_ingest.name}, retries={daily_ingest.retries}')
    print(f'weekly_report: {weekly_report.name}')
    print('✓ All flows import and configure correctly')
    "
OrchestratorCI Validation MethodWhat It ChecksProduction Connection
Airflowimportlib DAG importSyntax, imports, task dependencies, scheduleNo
Dagsterdagster asset listAsset definitions, dependencies, I/O managersNo
PrefectPython import + introspectionFlow definitions, task dependencies, retriesNo
dbtdbt parseModel compilation, source references, macrosNo

orchestrator CI | PySpark tests

PySpark tests run with a local SparkSession on the GitHub runner — no cluster required. The local[2] master uses two threads to simulate parallelism and catch concurrency issues in transformations.

Run PySpark tests in a matrix

On every push that modifies analytics code. It is typically triggered by push event with path filter on analytics/**. GitHub-hosted runner with Java 17 (required by Spark) and PySpark installed via pip. No Spark cluster needed. Validate Spark transformations, schema expectations, and business logic with fast local tests.

Run PySpark unit tests against Python 3.11 and 3.12 in parallel.

name: "Demo: DE PySpark Tests"
 
on:
  push:
    paths:
      - "analytics/**"
      - ".github/workflows/demo-de-pyspark-test.yml"
  workflow_dispatch:
 
permissions:
  contents: read
 
jobs:
  pyspark-test:
    name: PySpark Tests (Python ${{ matrix.python-version }})
    runs-on: ubuntu-latest
    timeout-minutes: 15
    strategy:
      fail-fast: false
      matrix:
        python-version: ["3.11", "3.12"]
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: ${{ matrix.python-version }}
 
      - uses: actions/setup-java@c5195efecf7bdfc987ee8bae7a71cb8b11521c00 # v4.7.1
        with:
          distribution: temurin
          java-version: "17"
 
      - name: Install PySpark and test dependencies
        run: pip install pyspark==3.5.4 pytest pandas
 
      - name: Run PySpark tests
        run: python -m pytest tests/ -v --tb=short 2>&1

Workflow run output (run 24314013409, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE PySpark Tests · 24314013409
Triggered via push
 
JOBS
✓ PySpark Tests (Python 3.11) in 30s (ID 70988480410)
✓ PySpark Tests (Python 3.12) in 30s (ID 70988480411)
 
PySpark Tests (Python 3.11) — Run PySpark tests:
  test_spark.py::test_ohlcv_schema PASSED               [ 33%]
  test_spark.py::test_volume_filter PASSED              [ 66%]
  test_spark.py::test_daily_return_calculation PASSED   [100%]
  ============================== 3 passed in 7.19s ===============================
 
PySpark Tests (Python 3.12) — Run PySpark tests:
  test_spark.py::test_ohlcv_schema PASSED               [ 33%]
  test_spark.py::test_volume_filter PASSED              [ 66%]
  test_spark.py::test_daily_return_calculation PASSED   [100%]
  ============================== 3 passed in 6.88s ===============================

Infrastructure changes with plan-first approval gates

Terraform workflows enforce infrastructure-as-code discipline for data platforms. The plan runs on every push (read-only), and apply runs only after manual approval in a protected environment.

Terraform | plan on PR

The Terraform plan workflow runs terraform init, validate, and plan on every push to the infra/ directory. The plan output is written to the job summary and uploaded as an artifact for review.

Run Terraform plan on push

On every push that modifies infrastructure files. It is typically triggered by push event with path filter on infra/**. GitHub-hosted runner with Terraform installed. GCP OIDC authentication for state access. Preview infrastructure changes before they are applied. No resources are created or destroyed.

Run Terraform plan against the infra/ directory and upload the plan artifact.

name: "Demo: DE Terraform Plan"
 
on:
  push:
    paths:
      - "infra/**"
      - ".github/workflows/demo-de-terraform-plan.yml"
  workflow_dispatch:
 
permissions:
  contents: read
  id-token: write
  pull-requests: write
 
concurrency:
  group: terraform-${{ github.ref }}
  cancel-in-progress: true
 
jobs:
  terraform-plan:
    name: Terraform Plan
    runs-on: ubuntu-latest
    timeout-minutes: 15
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: hashicorp/setup-terraform@b9cd54a3c349d3f38e8881555d616ced269862dd # v3.1.2
        with:
          terraform_version: "1.9.0"
 
      - id: auth
        uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
        with:
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
      - name: Terraform init
        working-directory: infra
        run: terraform init -input=false -no-color
 
      - name: Terraform validate
        working-directory: infra
        run: terraform validate -no-color
 
      - name: Terraform plan
        working-directory: infra
        run: terraform plan -input=false -no-color -out=tfplan
 
      - name: Upload plan artifact
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
        with:
          name: tfplan-${{ github.sha }}
          path: infra/tfplan
          retention-days: 7

Workflow run output (run 24314013424, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE Terraform Plan · 24314013424
Triggered via push
 
JOBS
✓ Terraform Plan in 7s (ID 70988480442)
 
Terraform Plan — Terraform validate:
  Success! The configuration is valid.
 
Terraform Plan — Terraform plan:
  + resource "google_compute_network" "main"
  + resource "google_compute_subnetwork" "data"
 
  Plan: 2 to add, 0 to change, 0 to destroy.

Terraform | apply with environment gate

Terraform apply runs only via manual dispatch with explicit confirmation and a production environment approval gate. The inputs.confirm must equal 'apply' to proceed.

Apply Terraform changes with manual confirmation

Only when an operator explicitly triggers the workflow and types “apply” to confirm. It is typically triggered by workflow_dispatch with a confirmation input. GitHub-hosted runner with Terraform. Production environment requires reviewer approval. Apply reviewed infrastructure changes with human-in-the-loop confirmation at two levels: dispatch input and environment gate.

Apply Terraform changes with double confirmation: typed input + environment approval.

name: "Demo: DE Terraform Apply"
 
on:
  workflow_dispatch:
    inputs:
      confirm:
        description: "Type 'apply' to confirm infrastructure changes"
        required: true
        type: string
 
permissions:
  contents: read
  id-token: write
 
jobs:
  terraform-apply:
    name: Terraform Apply
    runs-on: ubuntu-latest
    timeout-minutes: 30
    environment: production
    if: inputs.confirm == 'apply'
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: hashicorp/setup-terraform@b9cd54a3c349d3f38e8881555d616ced269862dd # v3.1.2
        with:
          terraform_version: "1.9.0"
 
      - id: auth
        uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
        with:
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
      - name: Terraform init
        working-directory: infra
        run: terraform init -input=false -no-color
 
      - name: Terraform apply
        working-directory: infra
        run: terraform apply -input=false -no-color -auto-approve

Destructive Terraform apply without environment protections

Running terraform apply -auto-approve without an environment gate means any workflow trigger (including automation) can destroy or modify production infrastructure without human review.

Layer two protections

  1. Input confirmation — the if: inputs.confirm == 'apply' condition prevents accidental triggers.
  2. Environment gate — environment: production with a required reviewer pauses the workflow until a human approves. Both must pass for the apply to proceed.

Terraform | blast radius control

ControlImplementationWhat It Prevents
Plan-only CIterraform plan on every push, no applyAccidental infrastructure changes in CI
Environment gateenvironment: production with required reviewerUnreviewed infrastructure mutations
Typed confirmationinputs.confirm == 'apply'Accidental dispatch triggers
Concurrency groupgroup: terraform-${{ github.ref }}Parallel plan/apply races
State lockingBackend-level state lock (GCS, S3)Concurrent apply from multiple sources
Targeted applyterraform apply -target=resourceLimiting blast radius to specific resources
Sentinel/OPA policiesPolicy-as-code validation before applyEnforcing organizational constraints

Data quality checks that run against live warehouse state

Data quality workflows run assertions against live warehouse data and produce human-readable reports as artifacts. These can run on schedule (nightly audits) or on push (post-deployment validation).

data quality | assertion checks

Data quality assertions are boolean checks on data properties: row counts, null percentages, value ranges, uniqueness constraints. Each check queries the warehouse and evaluates the result against a threshold.

Run data quality assertions against BigQuery

After deployments, on schedule, or on push to configuration files. It is typically triggered by push event or workflow_dispatch. GitHub-hosted runner with GCP OIDC. Read-only BigQuery access. Results written to a JSON report artifact. Validate data integrity across critical tables and produce an auditable report.

Run data quality assertions against BigQuery and upload a report artifact.

name: "Demo: DE Data Quality Check"
 
on:
  push:
    paths:
      - "config/**"
      - ".github/workflows/demo-de-data-quality.yml"
  workflow_dispatch:
 
permissions:
  contents: read
  id-token: write
 
jobs:
  data-quality:
    name: Data Quality Assertions
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - id: auth
        uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
        with:
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
      - uses: google-github-actions/setup-gcloud@77e7a554d41e2ee56fc945c52dfd3f33d12def9a # v2.1.4
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: "3.12"
 
      - name: Run data quality checks
        run: |
          pip install google-cloud-bigquery tabulate
 
          cat > /tmp/dq_check.py << 'DQ_EOF'
          import json, sys
          from datetime import datetime
          from google.cloud import bigquery
 
          client = bigquery.Client(project="bq-wh-nb")
          checks = [
              {"name": "row_count_eurostoxx50",
               "description": "EUROSTOXX50 OHLCV has at least 10 rows",
               "query": "SELECT COUNT(*) AS cnt FROM `bq-wh-nb.stoxx_bronze.eurostoxx50_ohlcv`",
               "assertion": lambda row: row["cnt"] >= 10},
              {"name": "no_null_dates",
               "description": "No NULL dates in trading_calendar",
               "query": "SELECT COUNT(*) AS null_count FROM `bq-wh-nb.stoxx_bronze.trading_calendar` WHERE date IS NULL",
               "assertion": lambda row: row["null_count"] == 0},
              {"name": "positive_volumes",
               "description": "All volumes are non-negative",
               "query": "SELECT COUNT(*) AS neg FROM `bq-wh-nb.stoxx_bronze.eurostoxx50_ohlcv` WHERE volume < 0",
               "assertion": lambda row: row["neg"] == 0},
              {"name": "unique_exchange_dates",
               "description": "No duplicate exchange-date pairs",
               "query": "SELECT COUNT(*) AS dupes FROM (SELECT exchange_code, date, COUNT(*) AS c FROM `bq-wh-nb.stoxx_bronze.trading_calendar` GROUP BY 1, 2 HAVING c > 1)",
               "assertion": lambda row: row["dupes"] == 0},
          ]
          results, passed, failed = [], 0, 0
          for check in checks:
              rows = list(client.query(check["query"]).result())
              row = dict(rows[0]) if rows else {}
              ok = check["assertion"](row)
              passed += ok; failed += not ok
              symbol = "✓" if ok else "✗"
              print(f"  {symbol} {check['name']}: {'PASS' if ok else 'FAIL'} (value={row})")
              results.append({"name": check["name"], "status": "PASS" if ok else "FAIL", "value": str(row)})
          report = {"timestamp": datetime.utcnow().isoformat(), "dataset": "stoxx_bronze",
                    "total_checks": len(checks), "passed": passed, "failed": failed, "results": results}
          with open("dq-report.json", "w") as f:
              json.dump(report, f, indent=2)
          print(f"\nTotal: {len(checks)} | Passed: {passed} | Failed: {failed}")
          if failed: sys.exit(1)
          DQ_EOF
          python /tmp/dq_check.py
 
      - name: Upload DQ report
        if: always()
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
        with:
          name: dq-report-${{ github.sha }}
          path: dq-report.json
          retention-days: 30

Workflow run output (run 24314051827, triggered by push to main, commit 0dd7142):

✓ main Demo: DE Data Quality Check · 24314051827
Triggered via push
 
JOBS
✓ Data Quality Assertions in 18s (ID 70988539869)
 
Data Quality Assertions — Run data quality checks:
  ✓ row_count_eurostoxx50: PASS (value={'cnt': 50})
  ✓ no_null_dates: PASS (value={'null_count': 0})
  ✓ positive_volumes: PASS (value={'neg': 0})
  ✓ unique_exchange_dates: PASS (value={'dupes': 0})
 
  Total: 4 | Passed: 4 | Failed: 0
 
ARTIFACTS
  dq-report-0dd7142 (dq-report.json, 30-day retention)

Great Expectations integration

For larger projects, replace inline assertions with Great Expectations checkpoints. GE generates HTML data docs as artifacts and supports expectation suites defined in YAML. The workflow structure remains the same — run checkpoints in a step and upload the data docs as an artifact.

data quality | schema and contract validation

Event schemas define the contract between producers and consumers in streaming pipelines. CI validates that schemas are syntactically valid, that sample payloads conform, and that changes don’t break consumers.

Validate event schemas and detect breaking changes

On every push that modifies schema files. It is typically triggered by push event with path filter on schemas/**. GitHub-hosted runner. No external services required. Enforce schema contracts for event pipelines. Detect breaking changes (removed fields, new required fields) before merge.

Validate JSON Schema definitions, sample payloads, and detect breaking changes.

name: "Demo: DE Schema Contract Validation"
 
on:
  push:
    paths:
      - "schemas/**"
      - ".github/workflows/demo-de-schema-contract.yml"
  workflow_dispatch:
 
permissions:
  contents: read
 
jobs:
  schema-validation:
    name: Validate Event Schemas
    runs-on: ubuntu-latest
    timeout-minutes: 5
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: "3.12"
 
      - name: Install jsonschema
        run: pip install jsonschema
 
      - name: Validate samples against schemas
        run: python schemas/validate_schema.py
 
      - name: Check for breaking changes
        run: |
          git fetch origin main 2>/dev/null || true
          if git show origin/main:schemas/event_trade.json > /tmp/old_schema.json 2>/dev/null; then
            python3 << 'PYEOF'
          import json
          with open("/tmp/old_schema.json") as f:
              old = json.load(f)
          with open("schemas/event_trade.json") as f:
              new = json.load(f)
          added_req = set(new.get("required",[])) - set(old.get("required",[]))
          removed = set(old.get("properties",{}).keys()) - set(new.get("properties",{}).keys())
          if added_req: print(f"::warning::New required fields (breaking): {added_req}")
          if removed: print(f"::error::Removed properties (breaking): {removed}")
          if not added_req and not removed: print("✓ No breaking changes detected")
          PYEOF
          else
            echo "No previous schema on main — first commit, skipping diff"
          fi

Workflow run output (run 24314013425, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE Schema Contract Validation · 24314013425
Triggered via push
 
JOBS
✓ Validate Event Schemas in 6s (ID 70988480450)
 
Validate Event Schemas — Validate samples against schemas:
  ✓ sample_event_trade.json[0] valid against event_trade.json
  ✓ sample_event_trade.json[1] valid against event_trade.json
 
  Schema validation complete: 0 error(s)
  All samples valid ✓
 
Validate Event Schemas — Check for breaking changes:
  ✓ No breaking changes detected
Change TypeBreaking?CI ActionExample
Remove a propertyYes::error — fail CIRemoving currency from TradeEvent
Add a required fieldYes::warning — warn in CIAdding settlement_date as required
Add an optional fieldNo::notice — informationalAdding metadata as optional
Narrow a typeYesRequires validationChanging price: number to price: integer
Widen a typeNoSafeChanging price: integer to price: number

Notebook and artifact rules that keep repositories clean

Jupyter notebooks committed with outputs create three problems: large binary diffs in version control, accidental data exposure in cell outputs, and non-reproducible analysis. CI should enforce output-free notebooks and validate structural integrity.

notebooks | output stripping and validation

The notebook hygiene workflow inspects every .ipynb file for committed outputs and validates the notebook structure using nbformat.

Check notebooks for committed outputs

On every push that modifies notebook files. It is typically triggered by push event with path filter on notebooks/**. GitHub-hosted runner. No external services required. Prevent committed outputs (data, plots, credentials) from entering version control.

Check all notebooks for committed outputs and validate structure.

name: "Demo: DE Notebook Validation"
 
on:
  push:
    paths:
      - "notebooks/**"
      - ".github/workflows/demo-de-notebook-validation.yml"
  workflow_dispatch:
 
permissions:
  contents: read
 
jobs:
  notebook-hygiene:
    name: Notebook Hygiene Check
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: actions/setup-python@a26af69be951a213d495a4c3e4e4022e16d87065 # v5.6.0
        with:
          python-version: "3.12"
 
      - name: Install tools
        run: pip install nbstripout nbformat
 
      - name: Check for committed outputs
        shell: bash
        run: |
          cat > /tmp/check_nb.py << 'PYEOF'
          import json, sys
          with open(sys.argv[1]) as f:
              nb = json.load(f)
          cells = nb.get("cells", [])
          code_cells = [c for c in cells if c["cell_type"] == "code"]
          output_cells = [c for c in code_cells if c.get("outputs")]
          print(f"{len(cells)}|{len(output_cells)}")
          PYEOF
 
          exit_code=0
          for nb in notebooks/*.ipynb; do
            filename=$(basename "$nb")
            result=$(python3 /tmp/check_nb.py "$nb")
            total=$(echo "$result" | cut -d'|' -f1)
            outputs=$(echo "$result" | cut -d'|' -f2)
            if [ "$outputs" -gt 0 ]; then
              echo "⚠ $filename has $outputs cells with committed outputs"
              echo "::warning file=$nb::$outputs cells with outputs — run nbstripout"
              exit_code=1
            else
              echo "✓ $filename is clean ($total cells, no outputs)"
            fi
          done
          exit $exit_code
 
      - name: Validate notebook structure
        run: |
          for nb in notebooks/*.ipynb; do
            python3 -c "import nbformat; nbformat.read('$nb', as_version=4)"
            echo "✓ $(basename $nb): valid nbformat v4"
          done

Workflow run output (run 24314013428, triggered by push to main, commit 78bcfbc):

✗ main Demo: DE Notebook Validation · 24314013428
Triggered via push
 
JOBS
✗ Notebook Hygiene Check in 7s (ID 70988480418)
 
Notebook Hygiene Check — Check for committed outputs:
  ⚠ analysis_example.ipynb has 2 cells with committed outputs
  ::warning:: 2 cells with committed outputs — run nbstripout
 
Notebook Hygiene Check — Validate notebook structure:
  ✓ analysis_example.ipynb: valid nbformat v4

Notebook outputs can leak sensitive data

Cell outputs may contain API keys, database connection strings, query results with PII, or model weights. When committed to git, these become part of the repository history and are difficult to remove even after deletion.

Set up nbstripout as a pre-commit hook

Install nbstripout as a git filter to automatically strip outputs before every commit:

pip install nbstripout
nbstripout --install

This makes output-free commits the default. The CI check acts as a safety net for contributors who haven’t configured the hook.

artifacts | manifests, reports, and sensitive data

Artifact TypeUpload PatternRetentionSecurity Notes
dbt manifesttarget/manifest.json7 daysSafe — contains model metadata, not data
DQ reportdq-report.json30 daysMay contain row counts and values — review before sharing
Test resultspytest-results.xml7 daysSafe — test names and pass/fail only
Terraform plantfplan (binary)7 daysMay contain resource names and IDs — treat as sensitive
Notebook outputsNever upload—May contain data, credentials, or PII
Query resultsUpload only aggregates7 daysNever upload raw query results with PII

Leaking query results or secrets into artifacts and logs

Workflow steps that print query results to stdout expose them in logs. Steps that upload raw query output as artifacts make them downloadable by anyone with repository read access.

Aggregate and redact before uploading

Print only row counts, pass/fail status, and aggregate metrics to logs. Upload structured reports (JSON/CSV) with predefined columns. Never upload raw SELECT * results.

Controlled backfills and other operator-triggered workflows

Data pipelines often require controlled manual operations: backfilling historical data, repairing corrupted partitions, or re-running failed transformations. These workflows use workflow_dispatch with typed inputs, dry-run validation, and environment-gated approval to prevent accidental production writes.

backfill | dispatch with typed parameters

The backfill workflow uses workflow_dispatch inputs to accept date ranges, target tables, dry-run mode, and an audit reason. Input validation runs before any data operations.

Run a controlled backfill with typed dispatch inputs

Only when an operator explicitly triggers the workflow via the GitHub UI or CLI. It is typically triggered by workflow_dispatch with five typed inputs. GitHub-hosted runner with GCP OIDC. Dry-run mode validates without writing. Production mode requires environment approval. Provide a controlled, auditable mechanism for data backfills with input validation, cost preview, and approval gates.

Backfill workflow with typed inputs, date validation, dry-run mode, and production approval.

name: "Demo: DE Backfill"
 
on:
  workflow_dispatch:
    inputs:
      start_date:
        description: "Backfill start date (YYYY-MM-DD)"
        required: true
        type: string
      end_date:
        description: "Backfill end date (YYYY-MM-DD)"
        required: true
        type: string
      target_table:
        description: "Target table to backfill"
        required: true
        type: choice
        options:
          - stoxx_bronze.eurostoxx50_ohlcv
          - stoxx_bronze.stoxxasia50_ohlcv
          - stoxx_bronze.stoxxusa50_ohlcv
      dry_run:
        description: "Dry-run mode (validate only, no writes)"
        required: true
        type: boolean
        default: true
      reason:
        description: "Reason for backfill (for audit log)"
        required: true
        type: string
 
permissions:
  contents: read
  id-token: write
 
jobs:
  validate:
    name: Validate Parameters
    runs-on: ubuntu-latest
    timeout-minutes: 5
    outputs:
      partition_count: ${{ steps.validate.outputs.partition_count }}
    steps:
      - name: Validate inputs
        id: validate
        run: |
          if ! date -d "${{ inputs.start_date }}" +%Y-%m-%d > /dev/null 2>&1; then
            echo "::error::Invalid start_date format"
            exit 1
          fi
          start_epoch=$(date -d "${{ inputs.start_date }}" +%s)
          end_epoch=$(date -d "${{ inputs.end_date }}" +%s)
          if [ "$start_epoch" -gt "$end_epoch" ]; then
            echo "::error::start_date must be before end_date"
            exit 1
          fi
          days=$(( (end_epoch - start_epoch) / 86400 + 1 ))
          echo "partition_count=$days" >> $GITHUB_OUTPUT
          if [ "$days" -gt 90 ]; then
            echo "::warning::Large backfill: $days days"
          fi
          echo "✓ Validation passed: $days partition(s)"
 
  backfill:
    name: Execute Backfill
    needs: validate
    runs-on: ubuntu-latest
    timeout-minutes: 30
    environment: ${{ inputs.dry_run && 'staging' || 'production' }}
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - id: auth
        uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
        with:
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
      - uses: google-github-actions/setup-gcloud@77e7a554d41e2ee56fc945c52dfd3f33d12def9a # v2.1.4
 
      - name: Run backfill
        run: |
          MODE="${{ inputs.dry_run && 'DRY-RUN' || 'LIVE' }}"
          echo "::notice::Backfill mode: $MODE"
          echo "::notice::Target: ${{ inputs.target_table }}"
          echo "::notice::Reason: ${{ inputs.reason }}"
          echo "::notice::Actor: ${{ github.actor }}"
 
          if [ "${{ inputs.dry_run }}" = "true" ]; then
            bq query --use_legacy_sql=false --dry_run \
              "SELECT COUNT(*) FROM \`bq-wh-nb.${{ inputs.target_table }}\` WHERE date BETWEEN '${{ inputs.start_date }}' AND '${{ inputs.end_date }}'"
            echo "✓ Dry-run complete — query is valid, no data was modified"
          else
            echo "⚠ LIVE backfill executing..."
            echo "✓ Backfill complete"
          fi

Workflow run output (run 24314017251, triggered by workflow_dispatch, actor alp78):

✓ main Demo: DE Backfill · 24314017251
Triggered via workflow_dispatch
 
JOBS
✓ Validate Parameters in 2s (ID 70988492291)
✓ Execute Backfill in 29s (ID 70988496422)
 
ANNOTATIONS
- Backfill mode: DRY-RUN
- Range: 2026-01-01 to 2026-01-31 (31 partitions)
- Target: stoxx_bronze.eurostoxx50_ohlcv
- Reason: Demo backfill for vault documentation
- Actor: alp78
 
Validate Parameters:
  ✓ Validation passed: 31 partition(s)
 
Execute Backfill:
  Dry-run: validating query against stoxx_bronze.eurostoxx50_ohlcv...
  Query successfully validated. 400 bytes of data.
  ✓ Dry-run complete — query is valid, no data was modified
InputTypePurposeExample
start_datestringFirst date of the backfill range2026-01-01
end_datestringLast date of the backfill range2026-01-31
target_tablechoiceTable to backfill (enumerated, no typos)stoxx_bronze.eurostoxx50_ohlcv
dry_runbooleanValidate without writing (default: true)true
reasonstringAudit trail for the backfillMissing data for Jan 2026

backfill | idempotency and rerun safety

Non-idempotent backfills cause data duplication

A backfill that appends rows without checking for existing data will create duplicates on rerun. If the workflow is re-triggered (manually or by automation), the target table accumulates duplicate records.

Design idempotent backfills with MERGE or partition overwrite

Use one of these patterns:

  1. MERGE (upsert) — MERGE INTO target USING source ON key = key WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT
  2. Partition overwrite — delete the target partition first, then insert: DELETE FROM table WHERE date BETWEEN start AND end; INSERT INTO table SELECT ...
  3. Write disposition — in BigQuery, use WRITE_TRUNCATE on the target partition instead of WRITE_APPEND.

Idempotent backfill using partition delete + insert.

-- Step 1: Clear the target partition
DELETE FROM `project.dataset.table`
WHERE date BETWEEN @start_date AND @end_date;
 
-- Step 2: Insert fresh data
INSERT INTO `project.dataset.table`
SELECT * FROM source_pipeline(@start_date, @end_date);

Pipeline images and packages published with traceable tags

Pipeline Docker images are built and pushed to GHCR on every push to source or dependency files. The image is tagged with both the branch name and the commit SHA for traceability.

packaging | pipeline Docker images

Build and push a pipeline image to GHCR

On every push that modifies source code, dependencies, or the Dockerfile. It is typically triggered by push event with path filter on src/**, requirements.txt, Dockerfile. GitHub-hosted runner with Docker Buildx. GHCR authentication uses the built-in GITHUB_TOKEN. Build an immutable, SHA-tagged container image for pipeline deployments.

Build and push the stock-index-pipeline image to GHCR.

name: "Demo: DE Pipeline Image Build"
 
on:
  push:
    paths:
      - "src/**"
      - "requirements.txt"
      - "Dockerfile"
      - ".github/workflows/demo-de-pipeline-image.yml"
  workflow_dispatch:
 
permissions:
  contents: read
  packages: write
 
jobs:
  build:
    name: Build Pipeline Image
    runs-on: ubuntu-latest
    timeout-minutes: 15
    outputs:
      image_tag: ${{ steps.meta.outputs.tags }}
      image_digest: ${{ steps.build.outputs.digest }}
    steps:
      - uses: actions/checkout@692973e3d937129bcbf40652eb9f2f61becf3332 # v4.2.2
 
      - uses: docker/setup-buildx-action@b5ca514318bd6ebac0fb2aedd5d36ec1b5c232a2 # v3.10.0
 
      - uses: docker/login-action@74a5d142397b4f367a81961eba4e8cd7edddf772 # v3.4.0
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ secrets.GITHUB_TOKEN }}
 
      - id: meta
        uses: docker/metadata-action@902fa8ec7d6ecbf8d84d538b9b233a880e428804 # v5.7.0
        with:
          images: ghcr.io/${{ github.repository }}/stock-index-pipeline
          tags: |
            type=sha,prefix=
            type=ref,event=branch
 
      - id: build
        uses: docker/build-push-action@14487ce63c7a62a4a324b0bfb37086795e31c6c1 # v6.16.0
        with:
          context: .
          push: true
          tags: ${{ steps.meta.outputs.tags }}
          cache-from: type=gha
          cache-to: type=gha,mode=max

Workflow run output (run 24314013411, triggered by push to main, commit 78bcfbc):

✓ main Demo: DE Pipeline Image Build · 24314013411
Triggered via push
 
JOBS
✓ Build Pipeline Image in 37s (ID 70988480447)
 
Build Pipeline Image — Extract metadata:
  Tags: ghcr.io/alp78/git-lab/stock-index-pipeline:main
        ghcr.io/alp78/git-lab/stock-index-pipeline:78bcfbc
 
Build Pipeline Image — Build and push:
  Digest: sha256:a94a7b94485d8008e9b1325482f9e51d01d6326c67548f645babc22c0674669e
 
ARTIFACTS
  Docker build metadata

Controls for cost, scale, and environment safety

Data-engineering CI workflows can incur significant costs if warehouse queries run uncontrolled. This section covers cost containment, ephemeral resource cleanup, and concurrency management for expensive jobs.

cost control | warehouse query limits

ControlImplementationScope
Dry-run validationbq query --dry_runZero cost — syntax check only
Byte limit--maximum_bytes_billed=1000000000 (1 GB)Caps individual query cost
Scan limit in CIReject queries estimating >10 GBPrevents runaway test queries
Ephemeral datasetsbq mk/rm per CI runIsolates test data, auto-cleanup
Concurrency groupsconcurrency: group: expensive-${{ github.ref }}One expensive job at a time
Timeouttimeout-minutes: 15Kills stuck queries
Schedule jitterCron with offset minutesPrevents audit query pileup

Expensive warehouse scans from naive test queries

A SELECT * in CI against a multi-TB table will be billed at full on-demand rates. If the workflow runs on every push, costs accumulate quickly.

Use byte limits and dry-run validation

Add --maximum_bytes_billed to all bq query calls in CI. Use dry-run to estimate costs before execution. For Snowflake, use a dedicated CI_XS warehouse with auto-suspend.

cost control | billing monitoring

The cost monitor workflow queries BigQuery dataset sizes and recent query volumes on a schedule, producing a summary report for review.

Cost monitoring workflow output (run 24314017713, triggered by workflow_dispatch):

✓ main Demo: DE Cost Monitor · 24314017713
Triggered via workflow_dispatch
 
JOBS
✓ BigQuery Cost Report in 30s (ID 70988494062)
 
BigQuery Cost Report — Query dataset sizes:
  Dataset: stoxx_bronze
  (Dataset listing requires additional permissions — use bigquery.tables.list)
 
BigQuery Cost Report — Check recent query costs:
  No query cost data available (INFORMATION_SCHEMA may require additional permissions)

INFORMATION_SCHEMA permissions

The INFORMATION_SCHEMA.JOBS_BY_PROJECT view requires the bigquery.jobs.list permission. The Workload Identity Federation service account needs the roles/bigquery.resourceViewer role to access job metadata for cost reporting.

Workload Identity Federation for keyless GCP access

All GCP-interacting workflows in this page use Workload Identity Federation (OIDC) for authentication. This eliminates long-lived service account keys and provides per-workflow, per-branch credential scoping.

WIF | GCP OIDC setup

The OIDC authentication pattern requires three components: a WIF pool and provider in GCP, a service account with appropriate roles, and GitHub secrets pointing to these resources.

OIDC authentication block used across all GCP workflows.

permissions:
  contents: read
  id-token: write
 
steps:
  - id: auth
    uses: google-github-actions/auth@ba79af03959ebeac9769e648f473a284504d9193 # v2.1.10
    with:
      workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
      service_account: ${{ secrets.GCP_SERVICE_ACCOUNT }}
 
  - uses: google-github-actions/setup-gcloud@77e7a554d41e2ee56fc945c52dfd3f33d12def9a # v2.1.4
SecretValuePurpose
GCP_WORKLOAD_IDENTITY_PROVIDERprojects/PROJECT_NUMBER/locations/global/workloadIdentityPools/POOL/providers/PROVIDERWIF provider resource name
GCP_SERVICE_ACCOUNTsa-name@project.iam.gserviceaccount.comService account email for token exchange
GCP_PROJECT_IDbq-wh-nbDefault GCP project

WIF | restricting by branch

WIF providers can restrict which branches or repositories are allowed to authenticate. This prevents feature branches from accessing production resources.

Restrict WIF to main branch only using attribute conditions.

gcloud iam workload-identity-pools providers update-oidc github \
  --location="global" \
  --workload-identity-pool="github-actions" \
  --attribute-condition="assertion.ref == 'refs/heads/main'"

Credential over-scoping across unrelated workflows

A single service account with broad permissions (e.g., roles/bigquery.admin) used by all workflows means any workflow — including untrusted PR-triggered ones — can modify production data.

Use per-workflow service accounts with least privilege

Create separate service accounts for CI (read-only), CD (write to staging), and production (write to production). Bind each to the WIF pool with appropriate attribute conditions (branch, repository, environment).

Key workflow patterns and commands at a glance

WorkflowTriggerKey ActionCredentialsRun Time
SQL Validation (BQ)push on sql/**bq query --dry_runOIDC read-only~12s
SQL Validation (SQL Server)push on migrations/**SET PARSEONLY ONLocal SA~15s
dbt Parse & Lintpush on dbt_project/**dbt parse + SQLFluffNone~24s
dbt Build & Testpush to maindbt build --target ciOIDC write (ephemeral)~5m
Airflow DAG Validationpush on dags/**Python import checkNone~24s
PySpark Testspush on analytics/**pytest with local SparkNone~30s
Terraform Planpush on infra/**terraform planOIDC read-only~7s
Terraform Applyworkflow_dispatchterraform applyOIDC admin~30s
Data Qualitypush, schedulePython assertions + BQOIDC read-only~18s
Schema Contractspush on schemas/**jsonschema validationNone~6s
Notebook Hygienepush on notebooks/**Output cell detectionNone~7s
Pipeline Imagepush on src/**Docker build + pushGHCR token~37s
Backfillworkflow_dispatchTyped inputs + dry-runOIDC write~31s
Cost Monitorschedule, dispatchBQ billing queriesOIDC read-only~30s

Common data-engineering workflow failures and fixes

FailureCauseFix
Unrecognized name: column in dry-runColumn doesn’t exist in the table schemaCheck bq show --schema for actual column names
permission denied on BQ dry-runSA lacks bigquery.jobs.createGrant roles/bigquery.jobUser to the WIF service account
INFORMATION_SCHEMA access deniedSA lacks bigquery.jobs.listGrant roles/bigquery.resourceViewer
dbt parse fails with no manifestMissing profiles.yml or invalid dbt_project.ymlUse --profiles-dir /dev/null for parse-only CI
SQLFluff templating errordbt Jinja not compiled before lintInstall sqlfluff-templater-dbt and configure .sqlfluff
Airflow DAG import errorMissing Python dependency in CIAdd the dependency to the pip install step
PySpark java.lang.NoClassDefFoundErrorJava not installed on runnerAdd actions/setup-java with Java 17
Terraform Error acquiring state lockAnother apply is runningCheck for concurrent runs; use concurrency groups
Terraform plan shows unexpected changesState drift from manual console changesRun terraform refresh or reconcile state
Docker push 403 ForbiddenMissing packages: write permissionAdd permissions.packages: write to the workflow
Notebook hygiene false positive.ipynb_checkpoints/ matched by globExclude checkpoint directories in the glob pattern
Schema validation jsonschema not installedMissing pip install stepAdd pip install jsonschema before validation
Backfill date validation failsDate format doesn’t match YYYY-MM-DDUse date -d validation with explicit format check
WIF auth Unable to generate tokenAttribute condition mismatchCheck branch name matches the WIF provider condition
Cost monitor returns empty resultsSA lacks table listing permissionsGrant roles/bigquery.dataViewer on the dataset

Operating rules for safe data-engineering automation

  1. Validate before mutate — every write operation must be preceded by a read-only validation step (dry-run, plan, parse).
  2. Ephemeral by default — CI resources (schemas, datasets, containers) are created at job start and destroyed at job end, even on failure.
  3. Least privilege everywhere — CI gets read-only access, CD gets write to staging, production requires environment approval.
  4. Idempotent backfills — every backfill uses MERGE or partition overwrite, never blind append.
  5. Audit everything — backfills log the actor, reason, date range, and target table in the job summary.
  6. Cost caps in CI — every warehouse query in CI has a byte limit or uses dry-run mode.
  7. Pin action SHAs — use full commit SHA pins for all third-party actions, never mutable tags.
  8. Artifacts with retention — upload reports and manifests as artifacts with explicit retention periods.
  9. Concurrency for expensive jobs — Terraform, backfills, and warehouse queries use concurrency groups to prevent parallel execution.
  10. Schema contracts — event schemas are validated on every push with breaking change detection before merge.

Graph View

Backlinks