Tuning AWS Glue PySpark Jobs for Speed and Cost

AWS Glue is easy to start with: write a PySpark script, choose a worker type, click run. It's also easy to overpay for. Most Glue jobs I've reviewed were either over-provisioned "just in case" or spent most of their time on problems that more workers can't fix: small files, reading partitions they didn't need, or skewed joins.

Delegated authority data hits all three. The bordereaux themselves are processed by the bordereaux platform. Downstream, the data team receives the platform's output (its reporting layer, or a regular feed of risk, premium and claims transactions) and builds data products on top. Those feeds arrive as many small incremental files. Getting the latest position of each risk tempts jobs to re-read years of history. And a handful of large binders account for most of the premium. This is the checklist I use when a Glue job is slow, expensive, or both.

Understand the bill first

Glue Spark jobs are billed per DPU-hour, per second, with a 1-minute minimum on current Glue versions. Worker types map to DPUs:

Worker	DPU	vCPU / memory	Typical use
G.1X	1	4 vCPU / 16 GB	Most ETL; the sensible default
G.2X	2	8 vCPU / 32 GB	Memory-heavy joins, wide aggregations
G.4X / G.8X	4 / 8	16-32 vCPU / 64-128 GB	Very large shuffles; rarely needed

So 10 × G.2X for one hour is 20 DPU-hours, the same as 20 × G.1X. The question is never "how big?" on its own. It's "how big, for how long?"

1. Use the latest Glue version

Glue 5.0 runs Spark 3.5 with Python 3.11, and each new version has brought real performance improvements from newer Spark (Adaptive Query Execution improvements, faster Parquet reading) and faster start-up. Moving older jobs from Glue 3.0 to a current version often makes them noticeably cheaper, with no code changes beyond fixing deprecated APIs.

2. Turn on auto scaling

1: --enable-auto-scaling  true

With auto scaling on, Number of workers becomes a maximum, and Glue adds or removes executors as the job's stages need them. A typical job on the bordereaux feed has one heavy stage (joining the latest transactions to contract sections and converting currencies) and then a long tail of light work, and auto scaling stops you paying for idle executors during that tail. This is the single easiest saving.

3. Use Flex for anything that isn't urgent

The Flex execution class runs jobs on spare capacity at a significantly lower DPU rate (roughly a third cheaper). The trade-off is that start-up can be delayed and runs aren't guaranteed to start immediately.

1: aws glue start-job-run \
2:   --job-name claims_history_rebuild \
3:   --execution-class FLEX

Historical rebuilds, reloading full history after the platform redelivers a feed, and test runs are good candidates. The month-end premium and claims loads that underwriters are waiting for aren't. A simple rule is to split jobs into "must finish for the month-end pack" (Standard) and "anytime overnight" (Flex), and often more than half can go on Flex.

4. Read less data: pushdown predicates

The most common waste I find: a job reads an entire Glue Catalog table, every feed delivery since the platform went live, and then filters to the latest delivery in Spark. Push the filter into the read so Glue only lists and reads the partitions you need:

1: feed_date = args["feed_date"]
2: 
3: premium = glueContext.create_dynamic_frame.from_catalog(
4:     database="raw",
5:     table_name="feed_premium",
6:     push_down_predicate=f"feed_date = '{feed_date}'",
7:     additional_options={"catalogPartitionPredicate": f"feed_date = '{feed_date}'"},
8: )

catalogPartitionPredicate filters on the Glue Catalog side using partition indexes. On feed tables partitioned by delivery date, the partition count grows quickly, and this can cut minutes from the job's start before Spark does any real work.

If you use the Spark DataFrame API directly (spark.read.parquet(...) or spark.table(...)), a WHERE on partition columns gives you partition pruning. Just make sure it's applied before any operation that stops Spark pushing it down.

5. Fix small files on the way in

Incremental feeds from the bordereaux platform tend to arrive as many small files: one per entity, per delivery, sometimes per batch. Over a few years that's a lot of small files to list and open. Glue can group them into larger read tasks:

 1: risk_bdx = glueContext.create_dynamic_frame.from_options(
 2:     connection_type="s3",
 3:     connection_options={
 4:         "paths": ["s3://my-data-bucket/feeds/risks/"],
 5:         "recurse": True,
 6:         "groupFiles": "inPartition",
 7:         "groupSize": "134217728",
 8:     },
 9:     format="json",
10: )

6. ...and on the way out

Don't create the next job's small-file problem. Control output file counts:

1: (risk_df.repartition("year_of_account", "bordereau_month")
2:     .write.mode("overwrite")
3:     .partitionBy("year_of_account", "bordereau_month")
4:     .option("maxRecordsPerFile", 5_000_000)
5:     .parquet("s3://my-data-bucket/curated/risks/"))

Better still, write to an Iceberg table, which Glue supports natively (--datalake-formats iceberg), and let compaction (Glue Data Catalog's automatic optimisation or a scheduled rewrite_data_files) handle file sizes over time. Iceberg's MERGE also makes corrected feed deliveries far easier to apply than overwriting partitions.

7. Job bookmarks for incremental processing

For jobs that process "new feed files since last run", Glue job bookmarks track what has been processed:

1: --job-bookmark-option  job-bookmark-enable

1: src = glueContext.create_dynamic_frame.from_catalog(
2:     database="raw", table_name="feed_claims",
3:     transformation_ctx="src_feed_claims",
4: )
5: job.commit()

Two gotchas: forget transformation_ctx and the bookmark silently does nothing; forget job.commit() and every run reprocesses everything. And if the platform redelivers a corrected file under the same name, a bookmark may skip it, so agree versioned file names with the feed owner.

8. Look at the metrics before adding workers

Turn on the Spark UI and continuous logging (--enable-spark-ui true, --spark-event-logs-path s3://...) and Glue job observability metrics. I tune Glue jobs from their CloudWatch metrics first, then the Spark UI. Before scaling up, look for:

• One task taking much longer than the rest in a stage usually means data skew, often one large binder or facility. More workers won't help. Check AQE skew join handling (spark.sql.adaptive.skewJoin.enabled, on by default), broadcast the smaller side, or salt the hot key.
• Lots of disk spill points to memory pressure. Moving from G.1X to G.2X with fewer workers can be cheaper than adding more G.1X.
• Executors idle most of the time means the job is over-provisioned. Lower the max workers or rely on auto scaling.
• The driver busy while executors wait usually means something is calling collect(), toPandas() or a Python loop over binders on the driver.

9. Broadcast small dimensions

Contract sections, coverholders, brokers and month-end rates of exchange are small next to the transaction facts. Broadcast them:

1: from pyspark.sql.functions import broadcast
2: 
3: premium_enriched = (premium_df
4:     .join(broadcast(dim_section), ["umr", "section_no"], "left")
5:     .join(broadcast(dim_broker), "broker_code", "left")
6:     .join(broadcast(fx_rates), ["original_ccy", "bordereau_month"], "left"))

AQE often does this automatically when it can see the size, but an explicit hint is reliable when the dimension comes from a source Spark can't size well, such as a JDBC read from an in-house SQL Server.

Results

Applying this list (Glue version upgrade, auto scaling, Flex for non-urgent jobs, pushdown predicates, and output compaction) usually saves a lot, and the month-end jobs often finish earlier too. Most of it is just checking the basics, one job at a time.

Do the cheap things first, measure with the Spark UI, and only then pay for more workers.

CI/CD for dbt with GitHub Actions: Slim CI and Safe Deployments

Once a dbt project has more than a handful of models and more than one developer, "run it locally and push to main" stops being safe. A change to a staging model can break a mart three levels down, and nobody finds out until the morning run fails or, worse, succeeds with wrong numbers.

This post shows the CI/CD setup I use for dbt with GitHub Actions: build and test only what changed on every pull request, in an isolated schema, and deploy to production on merge. The examples use Databricks, but the workflow is the same for any adapter.

The goals

• Every pull request is built and tested before it can be merged.
• CI only builds what changed and what depends on it, not the whole project.
• CI runs in its own schema, so it never touches production tables.
• Production deploys happen from main only, never from a laptop.

Slim CI: build only what changed

dbt can compare the current project with the manifest from production (manifest.json, written by every run) and select only modified models:

1: dbt build --select state:modified+ --defer --state ./prod-artifacts

• state:modified+: models whose SQL or config changed, plus everything downstream of them (the +).
• --defer: when a selected model refers to a model that wasn't rebuilt, read the production version instead. So CI doesn't need to build the whole upstream graph.
• --state: where to find the production manifest.

A change to fct_claims builds that model and its downstream marts, reading unchanged dimensions from production. A pull request that touches two models finishes in minutes instead of rebuilding years of transactions.

Isolated schemas for each pull request

Give CI its own target in profiles.yml, with the schema taken from an environment variable:

 1: analytics:
 2:   outputs:
 3:     ci:
 4:       type: databricks
 5:       host: "{{ env_var('DATABRICKS_HOST') }}"
 6:       http_path: "{{ env_var('DATABRICKS_HTTP_PATH') }}"
 7:       token: "{{ env_var('DATABRICKS_TOKEN') }}"
 8:       catalog: ci
 9:       schema: "{{ env_var('DBT_CI_SCHEMA') }}"
10:       threads: 8
11:     prod:
12:       type: databricks
13:       host: "{{ env_var('DATABRICKS_HOST') }}"
14:       http_path: "{{ env_var('DATABRICKS_HTTP_PATH') }}"
15:       token: "{{ env_var('DATABRICKS_TOKEN') }}"
16:       catalog: prod
17:       schema: reporting
18:       threads: 8

Each pull request gets a schema like pr_142 in a separate CI catalog, so two developers' pull requests can't overwrite each other, and none of them can touch production.

Keeping the production manifest

Slim CI needs the latest production manifest.json. The simplest approach is to have the production deploy upload it as a build artifact, or copy it to cloud storage, and have CI download it. I prefer storage, because artifacts expire:

1: aws s3 cp target/manifest.json s3://my-dbt-artifacts/prod/manifest.json

The pull request workflow

 1: # .github/workflows/dbt-ci.yml
 2: name: dbt-ci
 3: on:
 4:   pull_request:
 5:     branches: [main]
 6: 
 7: jobs:
 8:   build-and-test:
 9:     runs-on: ubuntu-latest
10:     env:
11:       DATABRICKS_HOST:      ${{ secrets.DATABRICKS_HOST }}
12:       DATABRICKS_HTTP_PATH: ${{ secrets.DATABRICKS_HTTP_PATH }}
13:       DATABRICKS_TOKEN:     ${{ secrets.DATABRICKS_CI_TOKEN }}
14:       DBT_CI_SCHEMA:        pr_${{ github.event.pull_request.number }}
15:       DBT_PROFILES_DIR:     ./ci
16:     steps:
17:       - uses: actions/checkout@v4
18:       - uses: actions/setup-python@v5
19:         with: { python-version: '3.11' }
20:       - run: pip install dbt-databricks==1.10.*
21:       - run: dbt deps
22: 
23:       - name: Get production manifest
24:         run: |
25:           mkdir -p prod-artifacts
26:           aws s3 cp s3://my-dbt-artifacts/prod/manifest.json prod-artifacts/ || echo "no manifest, full build"
27: 
28:       - name: Lint SQL
29:         run: pip install sqlfluff sqlfluff-templater-dbt && sqlfluff lint models
30: 
31:       - name: Build and test changed models
32:         run: |
33:           if [ -f prod-artifacts/manifest.json ]; then
34:             dbt build --target ci --select state:modified+ --defer --state prod-artifacts
35:           else
36:             dbt build --target ci
37:           fi

(The AWS step needs credentials too. With GitHub's OIDC integration you can assume an IAM role without storing access keys.)

Make the workflow a required status check on the main branch, so a pull request can't be merged until its models build and its tests pass.

The deploy workflow

 1: # .github/workflows/dbt-deploy.yml
 2: name: dbt-deploy
 3: on:
 4:   push:
 5:     branches: [main]
 6: 
 7: jobs:
 8:   deploy:
 9:     runs-on: ubuntu-latest
10:     environment: production
11:     env:
12:       DATABRICKS_HOST:      ${{ secrets.DATABRICKS_HOST }}
13:       DATABRICKS_HTTP_PATH: ${{ secrets.DATABRICKS_HTTP_PATH }}
14:       DATABRICKS_TOKEN:     ${{ secrets.DATABRICKS_PROD_TOKEN }}
15:       DBT_PROFILES_DIR:     ./ci
16:     steps:
17:       - uses: actions/checkout@v4
18:       - uses: actions/setup-python@v5
19:         with: { python-version: '3.11' }
20:       - run: pip install dbt-databricks==1.10.*
21:       - run: dbt deps
22:       - run: dbt build --target prod --exclude test_type:unit
23:       - run: aws s3 cp target/manifest.json s3://my-dbt-artifacts/prod/manifest.json

Unit tests already ran in CI, so production skips them. The GitHub environment adds an approval step, which also gives you an audit trail of who released what and when.

Many teams don't run the whole project on every merge. Instead, the scheduled production job (in Databricks Workflows, Airflow or similar) runs dbt build on its normal timetable, and the merge simply makes the new code available to it. Either works. What matters is that production only ever runs code from main.

Cleaning up

CI schemas pile up. A small workflow on pull_request: closed can drop the schema for that pull request:

1:       - run: dbt run-operation drop_schema

Here drop_schema is a short macro in the project that runs DROP SCHEMA ... CASCADE.

Small habits that help

• Pin dbt and package versions so CI and production behave the same.
• Use conventional commits (feat:, fix:) and you can generate release notes and version tags automatically.
• Use separate tokens or service principals for CI and production, with CI unable to write to production catalogs.
• Keep CI fast. If a pull request takes more than about ten minutes, people start bypassing it. Slim CI and deferral are what keep it quick.

None of this is specific to insurance, but it matters more when the output feeds Lloyd's returns and finance reconciliations. A tested, reviewed, repeatable release process is much easier to explain to an auditor than "it worked on my machine".

Testing dbt Models: From not_null to Premium Reconciliation

The most expensive bugs in insurance reporting aren't the ones that crash a pipeline. They're the ones that quietly drop a few contract sections from an inner join, double-count a resubmitted transaction, or apply last month's exchange rate. The numbers look plausible, and nobody notices until the premium total doesn't match finance at quarter end.

dbt makes testing part of every run instead of something done by hand before go-live. This post covers the kinds of tests I find most useful on insurance data, from the built-in ones to reconciliation checks and dbt's unit tests.

1. Generic tests: the basics on every model

 1: # models/marts/_marts.yml
 2: version: 2
 3: 
 4: models:
 5:   - name: fct_premium
 6:     columns:
 7:       - name: premium_id
 8:         tests: [unique, not_null]
 9:       - name: contract_section_key
10:         tests:
11:           - not_null
12:           - relationships:
13:               to: ref('dim_section')
14:               field: contract_section_key
15:       - name: broker_key
16:         tests:
17:           - relationships:
18:               to: ref('dim_broker')
19:               field: broker_key
20:       - name: transaction_type
21:         tests:
22:           - accepted_values:
23:               values: ['NEW', 'RENEWAL', 'MTA', 'CANCELLATION']
24:       - name: original_ccy
25:         tests:
26:           - not_null
27:           - accepted_values:
28:               values: ['GBP', 'USD', 'EUR', 'CAD', 'AUD']
29:               config:
30:                 severity: warn

The relationships tests are the most valuable here. They catch orphaned transactions, rows whose contract section or broker doesn't exist in the dimension, before an inner join drops them from a report.

The currency test is set to warn. A new currency isn't necessarily wrong, but someone should look at it.

2. Singular tests: business rules in SQL

A singular test is a SQL file in the tests/ folder that returns the rows that break a rule. No rows means a pass.

1: -- tests/assert_expiry_after_inception.sql
2: select certificate_ref, umr, section_no, inception_date, expiry_date
3: from {{ ref('fct_risks') }}
4: where expiry_date < inception_date

1: -- tests/assert_cancellations_are_negative.sql
2: select premium_id, transaction_type, gross_premium
3: from {{ ref('fct_premium') }}
4: where transaction_type = 'CANCELLATION'
5:   and gross_premium > 0

1: -- tests/assert_risk_within_contract_period.sql
2: select r.certificate_ref, r.risk_inception_date, cs.inception_date, cs.expiry_date
3: from {{ ref('fct_risks') }} r
4: join {{ ref('dim_section') }} cs
5:   on cs.contract_section_key = r.contract_section_key
6: where r.risk_inception_date < cs.inception_date
7:    or r.risk_inception_date >  cs.expiry_date

That last one is a real data quality rule for delegated authority: a risk bound outside the binding authority's period is something the oversight team needs to know about.

3. Reconciliation tests: do the totals match the source?

Row-level tests don't catch a join that duplicates rows or a filter that drops a whole month. For that, compare totals between the source and the final model. Most of the migration and reporting work I've done ends with this kind of reconciliation, and it's worth automating rather than doing in a spreadsheet.

 1: -- tests/reconcile_premium_to_source.sql
 2: with source_totals as (
 3:     select year_of_account, bordereau_month,
 4:            count(*)            as row_count,
 5:            sum(gross_premium)  as gross_premium
 6:     from {{ ref('stg_premium') }}
 7:     group by 1, 2
 8: ),
 9: model_totals as (
10:     select cs.year_of_account, f.bordereau_month,
11:            count(*)              as row_count,
12:            sum(f.gross_premium)  as gross_premium
13:     from {{ ref('fct_premium') }} f
14:     join {{ ref('dim_section') }} cs
15:       on cs.contract_section_key = f.contract_section_key
16:     group by 1, 2
17: )
18: select
19:     coalesce(s.year_of_account, m.year_of_account) as year_of_account,
20:     coalesce(s.bordereau_month, m.bordereau_month) as bordereau_month,
21:     s.row_count     as source_rows,   m.row_count     as model_rows,
22:     s.gross_premium as source_gwp,    m.gross_premium as model_gwp
23: from source_totals s
24: full outer join model_totals m
25:   on  m.year_of_account = s.year_of_account
26:   and m.bordereau_month = s.bordereau_month
27: where coalesce(s.row_count, 0) <> coalesce(m.row_count, 0)
28:    or abs(coalesce(s.gross_premium, 0) - coalesce(m.gross_premium, 0)) > 0.01

Any row returned shows exactly which year of account and month is out, by how many rows, and by how much premium. Compare in the original currency, so currency conversion problems don't hide counting problems.

4. Unit tests: logic with fixed inputs (dbt 1.8+)

The tests above check real data. Unit tests check logic with small, fixed inputs, before the model ever runs against production data. They're ideal for calculations like signing cancellations or netting off commission and brokerage:

 1: # models/marts/_unit_tests.yml
 2: unit_tests:
 3:   - name: net_premium_signs_cancellations_and_deducts_deductions
 4:     model: fct_premium
 5:     given:
 6:       - input: ref('stg_premium')
 7:         rows:
 8:           - {certificate_ref: C1, umr: B1, section_no: S1, transaction_type: NEW,          gross_premium: 1000, commission_pct: 25, brokerage_pct: 5, original_ccy: GBP, bordereau_month: 2025-09-01}
 9:           - {certificate_ref: C2, umr: B1, section_no: S1, transaction_type: CANCELLATION, gross_premium: 400,  commission_pct: 25, brokerage_pct: 5, original_ccy: GBP, bordereau_month: 2025-09-01}
10:       - input: ref('dim_section')
11:         rows:
12:           - {contract_section_key: 10, umr: B1, section_no: S1, coverholder_key: 1, broker_key: 1}
13:       - input: ref('stg_fx_rates')
14:         rows:
15:           - {currency: GBP, roe_month: 2025-09-01, rate_to_gbp: 1}
16:     expect:
17:       rows:
18:         - {certificate_ref: C1, gross_premium: 1000, commission: 250,  brokerage: 50,  net_premium: 700}
19:         - {certificate_ref: C2, gross_premium: -400, commission: -100, brokerage: -20, net_premium: -280}

You only list the columns the test cares about. dbt fills the rest. Run unit tests in development and CI. They don't need to run in production, because the inputs never change.

5. Source freshness

A pipeline that runs perfectly on yesterday's data is still wrong. Freshness checks on sources catch a feed that has stopped arriving:

1: sources:
2:   - name: feed
3:     tables:
4:       - name: claims
5:         loaded_at_field: loaded_at
6:         freshness:
7:           warn_after:  {count: 1, period: day}
8:           error_after: {count: 3, period: day}

Making failures useful

• store_failures: true writes the failing rows to a table, so you can query them, or put them in a report for whoever needs to fix the data.
• Severity and thresholds: warn_if: ">0" and error_if: ">100" let a test warn on a handful of bad rows and fail only when something is badly wrong.
• dbt build runs tests straight after each model and skips downstream models when a test errors, so bad data doesn't reach reporting.

Where to start

If a project has no tests at all, I'd add them in this order: unique and not_null on every key, relationships from facts to dimensions, one reconciliation test per fact table, and source freshness. That's a small amount of YAML and SQL, and it catches most of the problems that would otherwise surface in a finance meeting.

dbt Snapshots: Tracking Coverholder History with SCD Type 2

Some questions can only be answered with history. Was this coverholder approved when the risk incepted? Which broker was on the contract section when the claim was notified? What was the commission rate before it was renegotiated? If your dimension tables only hold current values, those answers are gone.

In SSIS we built Slowly Changing Dimension Type 2 logic by hand, and later as MERGE statements. In dbt, the same job is done by snapshots. This post shows how to use them for insurance reference data, including the YAML configuration introduced in dbt 1.9, and the traps to avoid.

How a snapshot works

A snapshot looks at a source table each time it runs, compares each row with what it stored last time, and:

• inserts new rows with dbt_valid_from set and dbt_valid_to null,
• for changed rows, closes the old version by setting dbt_valid_to and inserts a new version,
• leaves unchanged rows alone.

The result is a full Type 2 history table, maintained by dbt.

Defining a snapshot in YAML (dbt 1.9+)

From dbt 1.9, snapshots can be defined entirely in YAML, next to your models:

 1: # snapshots/coverholder_snapshot.yml
 2: snapshots:
 3:   - name: coverholder_snapshot
 4:     relation: source('feed', 'coverholder')
 5:     config:
 6:       schema: snapshots
 7:       unique_key: coverholder_id
 8:       strategy: check
 9:       check_cols: ['coverholder_name', 'country', 'approval_status', 'oversight_rating']
10:       hard_deletes: invalidate

In older versions, the same thing is a {% snapshot %} block in a .sql file. It still works, but the YAML version is shorter and easier to review.

Choosing a strategy

timestamp uses an "updated at" column. If the source row's timestamp is newer than the stored version, it's a change:

1:     config:
2:       unique_key: [umr, section_no]
3:       strategy: timestamp
4:       updated_at: last_modified_at

It's efficient and reliable, but only if the source really updates that column every time something changes. Plenty of systems don't.

check compares the listed columns directly. It's slower on very wide or large tables, but it doesn't depend on the source system behaving. For reference data from a feed, where I can't be sure an update timestamp is maintained, I use check with an explicit column list.

Avoid check_cols: all unless you really mean it. If the source has a load timestamp or a batch ID that changes on every delivery, every row will look changed and you'll get a new version of every coverholder every day.

Deletes

By default, a row that disappears from the source stays "current" in the snapshot forever. hard_deletes: invalidate (dbt 1.9+) closes it instead, and new_record goes further and adds a row marking the deletion. For a coverholder that's been removed from the register, invalidate is usually what you want.

Only do this if the source is a complete extract. If the source table only holds changes, every coverholder missing from today's delivery would be closed.

Snapshot raw data, not models

Snapshot the source as close to raw as you can, before business logic. If you snapshot the output of a model and later change the model's logic, the snapshot will record a "change" for every row, and you can't recreate the true history. Apply cleaning in a model that reads from the snapshot instead.

Building the dimension

The snapshot itself isn't the dimension. A model on top of it adds a surrogate key and friendlier column names:

 1: -- models/marts/dim_coverholder.sql
 2: select
 3:     {{ dbt_utils.generate_surrogate_key(['coverholder_id', 'dbt_valid_from']) }} as coverholder_key,
 4:     coverholder_id,
 5:     coverholder_name,
 6:     country,
 7:     approval_status,
 8:     oversight_rating,
 9:     dbt_valid_from                                   as valid_from,
10:     coalesce(dbt_valid_to, cast('9999-12-31' as timestamp)) as valid_to,
11:     dbt_valid_to is null                             as is_current
12: from {{ ref('coverholder_snapshot') }}

Replacing a null valid_to with a far-future date makes range joins from the facts simpler.

Joining facts to the right version

1: select
2:     r.*,
3:     c.coverholder_key
4: from {{ ref('stg_risks') }} r
5: join {{ ref('dim_coverholder') }} c
6:   on  c.coverholder_id = r.coverholder_id
7:   and r.risk_inception_date >= c.valid_from
8:   and r.risk_inception_date <  c.valid_to

Now "premium written while the coverholder was suspended" is a simple filter on approval_status, using the version that applied at the time.

Things to watch

• Run snapshots on a schedule. A snapshot only sees the state of the source when it runs. Changes made and reverted between two runs are never captured. For reference data that changes rarely, daily is usually enough.
• History starts on day one. A snapshot can't recreate the past. If you need history before that, load it separately, for example from archived extracts, into a table with the same columns.
• Back it up. Snapshot tables hold history that can't be rebuilt from the source. Don't drop them casually in a full refresh, and make sure your platform's retention or time travel settings cover them.
• Mind the time zone. dbt_valid_from comes from the timestamp column or the run time. Keep it consistent with the dates in your facts, or range joins will miss on boundary days.
• Test it. A simple test that every coverholder_id has exactly one current row catches most problems early.

1: # models/marts/_marts.yml
2: models:
3:   - name: dim_coverholder
4:     tests:
5:       - dbt_utils.unique_combination_of_columns:
6:           combination_of_columns: ['coverholder_id', 'valid_from']
7:     columns:
8:       - name: coverholder_key
9:         tests: [unique, not_null]

Snapshots take away almost all of the hand-written SCD code I used to maintain. The design questions don't go away, though: which columns to track, what counts as a delete, and how facts pick the right version.

Deploying AWS Glue Jobs with the AWS CDK in Python

Creating an AWS Glue job in the console takes a couple of minutes. Creating the same job consistently in dev, staging and production, with the right IAM role, script version, worker settings, job parameters and schedule, and keeping all three in step a year later, is a different problem. Console-built jobs drift: someone changes the worker count in prod to fix an incident, and nobody remembers to change it anywhere else.

For the last few years I've defined Glue jobs and everything around them with the AWS CDK in Python. The CDK generates CloudFormation, so every environment is built from the same code, every change goes through a pull request, and the console becomes read-only in practice. This post shows the patterns I use, with an insurance data pipeline as the example.

Project layout

 1: glue-platform/
 2:   app.py
 3:   cdk.json
 4:   stacks/
 5:     glue_jobs_stack.py
 6:   glue_scripts/
 7:     load_risks.py
 8:     load_claims.py
 9:   tests/
10:     test_glue_jobs_stack.py

Environment settings in context

Settings that differ by environment live in cdk.json, not in the code:

 1: {
 2:   "app": "python app.py",
 3:   "context": {
 4:     "environments": {
 5:       "dev":     { "account": "111111111111", "region": "eu-west-2", "max_workers": 4,  "schedule": null },
 6:       "staging": { "account": "222222222222", "region": "eu-west-2", "max_workers": 10, "schedule": null },
 7:       "prod":    { "account": "333333333333", "region": "eu-west-2", "max_workers": 20, "schedule": "cron(0 5 * * ? *)" }
 8:     }
 9:   }
10: }

 1: # app.py
 2: import aws_cdk as cdk
 3: from stacks.glue_jobs_stack import GlueJobsStack
 4: 
 5: app = cdk.App()
 6: env_name = app.node.try_get_context("env") or "dev"
 7: cfg = app.node.try_get_context("environments")[env_name]
 8: 
 9: GlueJobsStack(
10:     app, f"glue-jobs-{env_name}",
11:     env_name=env_name, cfg=cfg,
12:     env=cdk.Environment(account=cfg["account"], region=cfg["region"]),
13: )
14: app.synth()

Deploying an environment is then cdk deploy -c env=staging.

The stack: buckets, role, scripts and jobs

 1: # stacks/glue_jobs_stack.py
 2: from aws_cdk import Stack, RemovalPolicy, aws_s3 as s3, aws_iam as iam, aws_glue as glue
 3: from aws_cdk.aws_s3_assets import Asset
 4: from constructs import Construct
 5: 
 6: class GlueJobsStack(Stack):
 7:     def __init__(self, scope: Construct, cid: str, *, env_name: str, cfg: dict, **kwargs):
 8:         super().__init__(scope, cid, **kwargs)
 9: 
10:         data_bucket = s3.Bucket(
11:             self, "DataBucket",
12:             bucket_name=f"data-{env_name}-{self.account}",
13:             encryption=s3.BucketEncryption.S3_MANAGED,
14:             block_public_access=s3.BlockPublicAccess.BLOCK_ALL,
15:             enforce_ssl=True,
16:             removal_policy=RemovalPolicy.RETAIN,
17:         )
18: 
19:         role = iam.Role(
20:             self, "GlueJobRole",
21:             assumed_by=iam.ServicePrincipal("glue.amazonaws.com"),
22:             managed_policies=[iam.ManagedPolicy.from_aws_managed_policy_name(
23:                 "service-role/AWSGlueServiceRole")],
24:         )
25:         data_bucket.grant_read_write(role)
26: 
27:         for job_name in ["load_risks", "load_claims"]:
28:             self._glue_job(job_name, role, data_bucket, env_name, cfg)

Two details matter here. RemovalPolicy.RETAIN stops a stack deletion from deleting the data, and grant_read_write creates a least-privilege policy for that one bucket instead of a hand-written wildcard.

A reusable job definition

The scripts are uploaded as CDK assets, so every deployment ships the script version that's in the same commit as the job definition. That rules out the classic mismatch where the job definition changed but someone forgot to upload the new script, or the other way round.

 1:     def _glue_job(self, job_name, role, data_bucket, env_name, cfg):
 2:         script = Asset(self, f"{job_name}Script", path=f"glue_scripts/{job_name}.py")
 3:         script.grant_read(role)
 4: 
 5:         glue.CfnJob(
 6:             self, f"{job_name}Job",
 7:             name=f"{job_name}_{env_name}",
 8:             role=role.role_arn,
 9:             glue_version="5.0",
10:             worker_type="G.1X",
11:             number_of_workers=cfg["max_workers"],
12:             timeout=120,
13:             max_retries=1,
14:             command=glue.CfnJob.JobCommandProperty(
15:                 name="glueetl",
16:                 python_version="3",
17:                 script_location=script.s3_object_url,
18:             ),
19:             default_arguments={
20:                 "--enable-auto-scaling": "true",
21:                 "--enable-metrics": "true",
22:                 "--enable-continuous-cloudwatch-log": "true",
23:                 "--job-bookmark-option": "job-bookmark-disable",
24:                 "--datalake-formats": "iceberg",
25:                 "--data_bucket": data_bucket.bucket_name,
26:                 "--env_name": env_name,
27:             },
28:             tags={"project": "data-platform", "env": env_name},
29:         )

I use the L1 CfnJob construct because it maps one-to-one to CloudFormation and is stable. There's a higher-level Glue construct library too, which is worth a look if you want friendlier defaults.

Job parameters such as --data_bucket and --env_name mean the script never hard-codes an environment. Inside the script they're read with getResolvedOptions.

Orchestration with Step Functions

Risk transactions must load before claims, because claims reference risks. A small Step Functions state machine runs the jobs in order and waits for each to finish:

 1: from aws_cdk import aws_stepfunctions as sfn, aws_stepfunctions_tasks as tasks
 2: from aws_cdk import aws_events as events, aws_events_targets as targets
 3: 
 4: risk = tasks.GlueStartJobRun(
 5:     self, "LoadRisk", glue_job_name=f"load_risk_transactions_{env_name}",
 6:     integration_pattern=sfn.IntegrationPattern.RUN_JOB)
 7: 
 8: claims = tasks.GlueStartJobRun(
 9:     self, "LoadClaims", glue_job_name=f"load_claim_transactions_{env_name}",
10:     integration_pattern=sfn.IntegrationPattern.RUN_JOB)
11: 
12: machine = sfn.StateMachine(
13:     self, "DailyLoad",
14:     definition_body=sfn.DefinitionBody.from_chainable(risk.next(claims)))
15: 
16: if cfg["schedule"]:
17:     events.Rule(self, "DailySchedule",
18:                 schedule=events.Schedule.expression(cfg["schedule"]),
19:                 targets=[targets.SfnStateMachine(machine)])

The schedule only exists in production, because the context sets it to null elsewhere. Dev and staging runs are started by hand or from CI. Add a failure branch that publishes to an SNS topic and you have alerting too.

Testing the stack

CDK's assertions module lets you test the generated template, which catches mistakes before they reach AWS:

 1: # tests/test_glue_jobs_stack.py
 2: import aws_cdk as cdk
 3: from aws_cdk.assertions import Template, Match
 4: from stacks.glue_jobs_stack import GlueJobsStack
 5: 
 6: def test_jobs_use_glue_5_and_auto_scaling():
 7:     app = cdk.App()
 8:     cfg = {"account": "111111111111", "region": "eu-west-2", "max_workers": 4, "schedule": None}
 9:     stack = GlueJobsStack(app, "test", env_name="dev", cfg=cfg,
10:                           env=cdk.Environment(account=cfg["account"], region=cfg["region"]))
11:     template = Template.from_stack(stack)
12: 
13:     template.resource_count_is("AWS::Glue::Job", 2)
14:     template.has_resource_properties("AWS::Glue::Job", {
15:         "GlueVersion": "5.0",
16:         "DefaultArguments": Match.object_like({"--enable-auto-scaling": "true"}),
17:     })

Deploying from GitHub Actions

The pipeline runs the tests, shows a cdk diff on pull requests, and deploys on merge. It authenticates to AWS with OpenID Connect, so there are no long-lived access keys stored in GitHub:

 1: name: deploy
 2: on:
 3:   push:
 4:     branches: [main]
 5: permissions:
 6:   id-token: write
 7:   contents: read
 8: jobs:
 9:   deploy-staging:
10:     runs-on: ubuntu-latest
11:     steps:
12:       - uses: actions/checkout@v4
13:       - uses: actions/setup-python@v5
14:         with: { python-version: '3.11' }
15:       - uses: actions/setup-node@v4
16:         with: { node-version: '20' }
17:       - run: npm install -g aws-cdk && pip install -r requirements.txt
18:       - run: pytest -q
19:       - uses: aws-actions/configure-aws-credentials@v4
20:         with:
21:           role-to-assume: arn:aws:iam::222222222222:role/github-deploy
22:           aws-region: eu-west-2
23:       - run: cdk deploy -c env=staging --require-approval never

Production uses the same workflow behind a GitHub environment with required reviewers, so a person approves each production release.

What I've learned

• Treat the console as read-only. Any manual change will be overwritten by the next deployment, which is exactly the point.
• Keep environment differences in context, not in if env == "prod" branches scattered through the code.
• Ship scripts as assets so the job definition and the script can never get out of step.
• Retain data buckets and anything else you can't afford to lose if a stack is deleted.
• Run cdk diff in every pull request. Reviewers see exactly what will change in AWS, not just the code.