DEV Community: Tim Collinson

Your ERP extract is 31% wrong. Nothing errored.

Tim Collinson — Mon, 26 Oct 2026 22:51:34 +0000

Update, Oct 28: The day after this went up, two readers found a hole in the pipeline, and it’s patched. A sweep that ended early with a clean 200 would have been treated as complete, and the merge would have deleted everything it missed. Now every full read has to prove it’s complete before it can delete anything, and every merge writes down exactly what it deleted. Every number below still stands. The story is in “The night my pipeline deleted itself,” and the fix is tag post-02.1. Thanks, Karan Pulyala (@karanpulyala) and Alex Shev (@alexshev).

What you'll get out of this

In about 12 minutes, you'll walk away with:

Why the obvious way to pull data from an ERP gets it quietly wrong. Our first extractor got 30.8% of rows wrong and never threw an error.
The six defenses that took it to zero, and which mess each one fixes.
A serverless pattern for ERP extraction on AWS: Amazon EventBridge Scheduler, AWS Step Functions, AWS Lambda, raw pages in Amazon Simple Storage Service (Amazon S3), and curated Apache Iceberg tables in the AWS Glue Data Catalog, built with Amazon Athena MERGE. About $0 when idle.
The gotchas that cost us time: Lambda function URLs that rewrite your $, a Date header that isn't the server's, why we dropped Amazon S3 Tables, and the night our pipeline deleted half its own data and reported success.
An honest comparison of API polling, change data capture (CDC), and scheduled exports, so you can pick the right one for your plant.
A repo you can deploy and break on purpose.

Plant leaders: read the opening, the headline numbers, and the closing questions. Builders: jump to "The robust extractor."

Part 1: The problem

I cheated last time

Confession time. In the last post, I proved that Crestline's ERP was hiding a money-losing press brake. Grumpy PB-02, second shift. It made a nice story.

But I cheated. The ERP data in that post came out as one clean bulk export, perfectly consistent, taken at a single instant. Real ERPs don't hand you that. Real ERPs make you ask nicely, through an API, a page at a time, while the plant keeps running and the data keeps changing underneath you.

Why does that matter? Great question. Because every number in post 1 was only as good as the copy of the ERP it ran on. And nobody checks the copy.

So this time I built an ERP that fights back, wrote the extractor most of us write first, and measured how wrong it was.

It was 30.8% wrong. It never threw an error. Not one.

TL;DR

Crestline's fake ERP now changes while you read it, and has nine realistic messes: expiring tokens, throttling, drifting pages, a LastModified that lies, and more.

A typical incremental extractor got 5,762 of 18,707 rows wrong (30.8%) and kept 55 rows the ERP had deleted. Against a slow ERP, 93.3% wrong.

A robust extractor got zero wrong in every scenario. The key defense: a nightly full sweep, because some changes never move the timestamp.

On AWS it's serverless and costs about $0 when idle. The curated Iceberg tables match the ERP exactly, and post 1's margin query runs on them unchanged.

Repo: https://github.com/trcollinson/crestline-supply (branch tag post-02.1)

An ERP that changes while you read it

The fake ERP is a REST API in the style of mid-market vendors: an OAuth token, OData-style $filter, $top, $skip, and nextLink paging. Its data comes from the plant simulation, so there's always a known right answer.

The important part is time. The simulator emits the ERP's change log: orders entered, jobs released, and a nightly 22:00 labor batch. The API serves the ERP as of a replay clock. So the first page of your read and the fiftieth can come from slightly different versions of the ERP, the way they do against a live system.

Then I added nine messes, each one a switch in mess.yaml.

Things the API does to you:

Tokens that expire mid-extract (401)
Throttling (429 with Retry-After)
Random 500s
$skip paging that drifts when data changes between pages
Numbers served as "250.00000000"
Legacy /Date(1759420800000)/ dates

Things the ERP itself gets wrong:

LastModified doesn't move when labor posts against a job operation and changes its quantities
Duplicate orders, cancelled the next morning: the order line is soft-deleted and its job is hard-deleted
Jobs split into J and J-1 after release

If you've integrated with a mid-market ERP, you've probably met most of these. (If you've met all nine, I'm sorry. Let's get coffee.)

The extractor we all write first

Here's the naive extractor. Be honest, you've written this one. I have. Several times.

Sign in once.
Ask for everything with LastModified newer than the newest stamp you already have.
Follow nextLink until it runs out.
If a request fails, retry immediately, up to three times.

It's simple, it's fast, and it passes every test you'd think to write. Here's what it did over 90 days against an ERP at mess level 2:

Scenario	Rows wrong (of 18,707)	Share wrong
Clean ERP (control)	0	0%
Realistic ERP	5,762, plus 55 deleted rows kept	30.8%
Client polling hard (10 requests a second)	5,991, plus 51 deleted rows kept	32.0%
Slow ERP (15 minutes a page)	17,457	93.3%

The control row matters. On a clean ERP, the naive extractor is perfect, so every error below it is owed to a mess, not a bug in my code.

And almost all of the 30.8% is one mess. When the nightly labor batch posts against a job operation, it updates that operation's quantities but doesn't move LastModified. So the naive extractor never sees the update. Every operation that labor touched stays frozen at the values it had when the job was released.

One job operation, start to finish: labor posts at 22:00 and changes the quantities, but the stamp stays at 08:00. The next morning, the naive read asks for anything newer than 08:00 and gets nothing back. Only a read that looks at every row catches it.

Think about what that means for post 1. The margin analysis needed actual labor against every operation. A copy built this way would have shown standard numbers everywhere, and the PB-02 problem would have disappeared. Not because the plant fixed it. Because the extract hid it.

The slow-ERP number is a different failure. At 15 minutes a page, the token expires partway through, and the naive client never signs in again. It just stops, quietly, with whatever it had.

If you run the eval, you'll see a fourth scenario in between: an ERP that takes five minutes per request. The naive extractor lands at 31.1% wrong. Most of that is the same frozen operations, but on 23 nights the token expired partway through and it quietly skipped the job cost table, leaving about 50 jobs with stale actual costs. Those are exactly the numbers post 1's margin analysis runs on.

No errors. In any of it.

Part 2: The fix

The robust extractor

The robust extractor got zero rows wrong in every scenario: 0 missed, 0 lost, 0 stale, 0 deleted rows kept, 0 pulled twice. Six defenses got it there, and each one answers a specific mess. A seventh, from a reader, keeps the delete rule honest:

Defense	Mess it fixes
Sign in again on `401`	Tokens that expire mid-extract
Wait out `429`s per `Retry-After`; back off with jitter on `5xx`	Throttling, random errors
Keyset paging: order by key, ask for keys past the last one seen	`$skip` drift when data changes between pages
Watermarks with a one-hour overlap	Changes stamped just before your last read
Read labor by sequence number, not timestamp	Labor posted in a late nightly batch
A nightly full sweep of the four job tables	Changes whose `LastModified` never moved, and hard deletes
Count before and after every full read (added in `post-02.1`)	A read that stops early with no error, then deletes what it missed

The sweep is the one that matters most, and the one people skip. Incremental reads can only see changes that move the timestamp. When the ERP doesn't move it, no amount of clever filtering helps. You have to look at everything, compare, and correct. Over the 90 days, the sweeps corrected 5,878 operation versions whose stamps never moved.

It's also the only way to notice a hard delete. A row that vanished doesn't show up in "what changed since yesterday." It only shows up as missing in a full read.

One more rule: every page gets written to S3 exactly as the API served it, with its metadata (entity, page, request time, server time). Nothing gets cleaned on the way in. When you find a bug later, and you will, you can reprocess from the original.

On AWS: serverless, and about $0 when idle

Every hour, EventBridge Scheduler starts a Step Functions run. For each ERP entity, three at a time, a Lambda function extracts to S3 raw, then a curate step merges the new raw files into a typed Iceberg table. The fake ERP itself runs in Lambda behind a function URL, so nothing in this design runs when nothing is happening.

From raw pages to tables you can trust

Raw pages are the record of what the ERP said. They aren't something you want to query. So after each poll, a curate step runs one Athena MERGE per entity into a typed Iceberg table:

Newest version per key wins. Unchanged rows aren't rewritten, so the table's history shows only real changes.
A sweep is a complete picture. Any key it no longer contains was deleted from the ERP, so the merge deletes it too. Cancelled order lines keep their row with deleted = true, the way the ERP shows them.
Types are fixed on the way in. "250.00000000" becomes a number, /Date(ms)/ becomes a date, and every timestamp lands in UTC.
One commit per merge. Updates, inserts, and deletes land together, so nobody ever reads a half-applied change.

The proof is the copy, not the counts. At the end of the window, the curated tables match the ERP's final state exactly: 18,707 rows, 0 missing, 0 extra, 0 different.

Then the real test. Post 1's margin query, unchanged, now reads its ERP data from the curated tables through the same views. It matches the original in every cell, on all 1,252 finished jobs. PB-02 second shift still shows setups at 2.84 times standard, and an ERP margin of 20.5% against a rebuilt 14.8%.

(Sharp-eyed readers: post 1 said 20.4%. The tenth of a point comes from the job-split mess added for this post, which shrinks the parent job's material cost. Messy source data moves your numbers even when your pipeline is perfect. That's sort of the whole point.)

One honest caveat: a run reads each entity at a slightly different moment, so for a few minutes the tables can disagree with each other. The next run closes the gap. If you need a single consistent instant across every table, that's an argument for CDC or a snapshot export.

Part 3: Lessons and choices

What building it on AWS taught us

A Lambda function URL isn't a transparent pipe. It rejected a bare $ in the query string, which is a problem when your API is OData and every parameter starts with $top or $filter. The extractor sends %24top instead. It also replaces your server's Date header with its own clock. The watermarks depended on the ERP's clock, not Lambda's, so the API now sends an X-Server-Time header. Without that, every watermark would have been quietly wrong.

EventBridge Scheduler fires the first run the moment you create the schedule. Not at the next hour. Plan for a deploy that immediately starts working.

S3 Tables was the plan, and we dropped it. Athena ran MERGE against it fine, but CREATE VIEW over an S3 Tables table failed with "not yet supported for Cross-Account Glue DataCatalogs" (October 2026). It isn't a permissions gap: Athena's documentation lists CREATE VIEW as unsupported for S3 Tables. Every query in this project reads its sources through views, so the curated tables are plain Iceberg in the Glue catalog instead. The cost difference is a few cents a month.

Pick the merge window from S3 file names, not inside SQL. The curate step lists the new raw files and puts their exact dates into the MERGE. When we computed the window inside the SQL, Athena couldn't skip old partitions, so every run rescanned the entity's entire raw history, which grows about 2 MB an hour. Now a Jobs merge reads one sweep file: 0.2 MB.

Small tables need upkeep. Hourly merges leave lots of small files behind, so once a day each table gets OPTIMIZE and VACUUM. (Also: those two commands reject double-quoted table names. Ask me how I know.)

Retries are safe by design. Step Functions retries a failed merge, re-merging the same files changes nothing, and the "last merged" marker only moves after a merge succeeds. The marker lives on the poller's existing per-entity state record, so there's no extra state to manage.

The night my pipeline deleted itself

Remember the thesis? A wrong copy never throws an error. I'd love to tell you I learned that from other people's pipelines.

Storytime. To make the AWS run finish in days instead of months, the fake ERP replays its history on a fast clock. I left it running. It was running faster than I'd written down (240 times real time, not 24), and it had no end. So it ran straight past October, through 2027, and into 2028.

The raw table in Athena used partition projection with a date range of 2026 through 2027. New pages kept landing safely in S3, under dates Athena couldn't see. So the next nightly sweep looked empty to the merge. And a sweep is a complete picture, remember? Any key it doesn't contain has been deleted from the ERP. So the MERGE did exactly what I told it to do. It deleted 9,962 of 18,707 rows.

Then it reported success.

The same week, a Lambda function deployed before a fix made 69 of 95 hourly runs fail over three days. Nothing told anyone.

Before you laugh too hard: the replay clock only got there early. On a real ERP running on real time, that date range runs out on January 1, 2028. Same deletion. Same success message.

Here's what changed:

The date range now runs from 2020 to 2099. The merge always names its dates, so a wide range costs nothing.
A check runs before every MERGE. If Athena doesn't see exactly the rows the raw files hold, the run fails before anything changes.
A merge can't delete more than 5% of a table unless someone explicitly allows it, after checking.
An email alarm fires on any failed run, through an EventBridge rule and Amazon Simple Notification Service (Amazon SNS).
An AWS step isn't done until cdk diff shows nothing for every deployed stack, after the last commit. No more Lambdas older than the code.

Because the raw pages were never touched, the fix was a rebuild from raw alone: 18,707 rows, 0 wrong, and post 1's margin query unchanged. That's the other reason to keep raw.

The lesson isn't "add more guards." It's that "delete whatever the sweep doesn't see" is the most powerful rule in this pipeline. It's the rule that makes the copy exact, and it's the rule that emptied half of it. Rules that powerful need a check that fails loudly.

Update: the hole a reader found the next day

I published this post on a Monday. By Tuesday, Karan Pulyala (@karanpulyala) had found a hole in it.

Remember the most powerful rule in the pipeline? A sweep is a complete picture, so anything it doesn't contain gets deleted. Karan's question: who checks that the sweep is complete?

Nobody did. A dying token was already safe (the extractor throws the whole read away). But if the ERP sent back a page with no nextLink halfway through, or my paging code had a bug, the extractor would stop quietly and call the sweep complete. Then the merge would delete every row after the cut, and report success. Again.

Here's what post-02.1 adds:

Every full read counts the ERP's rows at the start and the end ($count=true). If the rows collected don't land in that range, the read is filed as short. A short read can update rows but can't delete any. The range gets ten rows of room for an ERP that's busy mid-read (Karan's second catch), which is far less than a 500-row page, so a lost page can't hide in it.
A read that ends on a full 500-row page asks one more time whether anything comes after its last key. Three real sweeps in the eval ended on exactly 500 rows. Without that extra question, all three would have failed.
Every merge that can delete writes down exactly what it deleted, diffed against the table's Iceberg snapshot from before the merge. A refused merge emails the count, the snapshot, and where the list lives.

Then Alex Shev (@alexshev) asked the right follow-up: have you actually seen it fire? So I planted a broken sweep in the raw bucket, one that skipped the extractor's count check entirely. The merge refused it before touching anything. It would have deleted 65 of 565 jobs. The table's snapshot didn't move, and the email named the count, the snapshot, and the file. An hour later the next real sweep took over and ran clean. Its audit listed the six rows, across four tables, of the one cancelled duplicate it was supposed to delete.

One honest caveat: none of my runs read while the ERP was mid-write, so the ten rows of room comes from reasoning and a unit test, not a measurement. I'll measure it in the next milestone.

This is the best part of writing in public. Two readers made this pipeline safer in a day.

API polling, CDC, or a scheduled export?

There are three common ways to get data out of an ERP. I built the first one. Here's how they compare, because the right answer depends on your plant.

	API polling (built here)	CDC from the database	Scheduled export
Freshness	Hourly, or whatever you schedule	Near real time	Daily, usually
Sees deletes	Only with a full sweep	Yes, from the log	Only if you compare full files
Survives a lying `LastModified`	Only with a sweep	Yes, it reads the log, not stamps	Yes, if each export is complete
Access you need	API credentials	Database-level access, often not allowed by the vendor or your hosting	A job someone sets up in the ERP
What breaks it	API changes, throttling, tokens	Schema changes, vendor upgrades	Someone changes the report
Running cost	Near zero when idle	An always-on replication instance, and often a read replica: tens to hundreds of dollars a month, depending on size	Near zero
Effort to do well	Medium (this post)	High	Low, but limited

For most mid-market plants, API polling with a nightly sweep is the right default. You usually can't get database access to a hosted ERP anyway, and "hourly and exactly right" beats "real time and expensive" for nearly every question finance and operations actually ask. CDC earns its cost when you truly need minutes, not hours, and you control the database.

Part 4: Your turn

Run it yourself

Everything deploys into your own AWS account and runs serverless, so it costs about nothing when it's idle.

You'll need: uv, Node.js with the AWS Cloud Development Kit (AWS CDK) CLI at version 2.1143 or later, the AWS Command Line Interface (AWS CLI), and an AWS profile with a region configured.

git clone --tag post-02.1 https://github.com/trcollinson/crestline-supply.git
cd crestline-supply
uv sync

# On your laptop: simulate 90 days, then score both extractors against the ERP
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42 --out ./out
uv run python eval/extraction.py

# On AWS (set AWS_PROFILE first; cdk bootstrap is once per account and region)
(cd infra && cdk bootstrap && cdk deploy CrestlineFoundation)
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42 --upload
uv run crestline erp-api secrets
(cd infra && cdk deploy CrestlineErpApi -c erpApiMess=../mess.yaml -c erpApiClockStart=2026-08-03T05:00:00Z -c erpApiClockSpeed=24)
(cd infra && cdk deploy CrestlineCurated)
# Optional: add -c erpIngestAlarmEmail=you@example.com to get an email on any failed run
(cd infra && cdk deploy CrestlineErpIngest)   # the first poll starts right away, then hourly

# About two and a half days later, once the ERP's history has played out (one ERP day per hour):
uv run crestline athena setup
uv run python eval/curated.py --margin
uv run crestline query margin-gap --engine athena

The experiment that makes the point: give the ERP honest timestamps. Add erp: lastmodified_skips: [] to mess.yaml, then rerun the backfill and the eval (the simulator reads that setting, so the eval alone isn't enough). The naive extractor drops to 0 rows wrong. But it still keeps the 55 deleted rows, because only a full read can see a delete. The eval's slow-ERP scenario shows the other failure: a token that dies mid-read, and 93.3% wrong.

Cost: a poll run is about a tenth of a cent in Step Functions plus a few seconds of Lambda. Billed, with every layer tagged, the whole pipeline comes to about $1 a month at hourly polling. Almost all of that is Athena (each query bills at least 10 MB, and every run now does a check plus a merge per entity) and S3 requests. Lambda and Step Functions fit inside the free tier.

Teardown: destroy the poller's stack first. It holds the hourly schedule, so polling stops as soon as it's gone. Then the rest, and the two secrets:

cd infra
cdk destroy CrestlineErpIngest
cdk destroy CrestlineErpApi CrestlineCurated CrestlineFoundation
cd ..
uv run crestline erp-api secrets --delete

The buckets are emptied for you. As in post 1, the small Lambda functions that empty them leave their log groups behind (/aws/lambda/CrestlineFoundation-* and /aws/lambda/CrestlineCurated-*); delete those with aws logs delete-log-group.

What this means for your plant

Every report, dashboard, and AI agent you build on ERP data inherits the quality of the copy underneath it. And a bad copy doesn't announce itself. It just makes your numbers quietly look like the plan.

Three questions worth asking your team this week:

When did we last compare our copy of the ERP to the ERP itself? Not the row counts. The rows.
Does our extract trust LastModified? If so, find out which ERP operations don't update it. There's almost always at least one.
Would we notice a hard delete? If your process only asks "what changed," the answer is no.

If you don't know the answers, you're in good company. Go find out. Do it this week.

The takeaway

If you remember one thing from this post: your data is only as right as your copy of it, and a wrong copy never throws an error.

The obvious extractor got 30.8% of rows wrong, almost all from one ERP habit: changes that don't move LastModified.
Six defenses took it to zero, and the one that mattered most was the least clever: look at everything, every night, and compare.
On AWS, the whole thing is serverless, costs about $0 when idle, and produces a copy we can prove is exact.

Post 1 found a money-losing press brake. This post makes sure the data you'd find it with is actually true.

What's next

So far, every number in this series has come from systems people type into: an ERP and an MES. Next up is the data nobody types. The machines.

In the next post, Crestline's shop floor starts talking: a modern laser speaking Sparkplug B, an old press brake with a dumb 16-bit counter, and a powder coat line that knows its oven temperatures but has no idea which parts are inside. Most discrete shops have no historian at all, so we'll build the one they don't have, on AWS, and see what it costs per machine.

P.S. If you run it and your naive extractor does better than mine, open an issue. I want to know what you did. (And if it does worse, I really want to know.)

Tim Collinson has spent nearly 30 years building software, much of it on AWS, across manufacturing, oil and gas, and enterprise IT. Reach him at trcollinson@gmail.com.

Athena read my timestamps as UTC. It didn’t tell me.

Tim Collinson — Tue, 13 Oct 2026 21:19:36 +0000

Nobody gets an error. Somebody gets blamed.

I built a fake metal fab plant to find this kind of bug before a real customer does. Last week it caught one.

The setup

The MES in the fake plant writes timestamps the way real MES systems do: local time, no time zone. 14:30 means 2:30 PM at the plant. The plant is in Mountain Time.

I wrote the obvious SQL to convert plant-local timestamps to UTC:

scan_on AT TIME ZONE 'America/Denver'

DuckDB, running locally, read that as: “this value is in Denver time; give me the UTC moment that corresponds to it.” Correct for what I meant.

Athena read that as: “this value is UTC; give me the Denver moment that corresponds to it.” Also correct, but for a different interpretation.

Same line of SQL. Two engines. Two answers. Six hours apart. No warning on either side.

Why

Trino’s behavior is the SQL standard: AT TIME ZONE says what zone to convert to. If the value doesn’t already carry a time zone, it’s assumed to be UTC. DuckDB is more lenient. In this case, DuckDB’s leniency is what let me write sloppy SQL on my laptop and have it quietly change meaning in production.

Trino isn’t the one being weird. DuckDB is.

The fix

Say explicitly what time zone the value is IN, then convert:

with_timezone(scan_on, 'America/Denver') AT TIME ZONE 'UTC'

Two gotchas on the Athena side:

with_timezone returns a timestamp with time zone. Athena views can’t store zoned timestamps, so cast back to plain timestamp at the view boundary.
If you want the same SQL to run in both engines, define a DuckDB macro that aliases with_timezone(ts, zone) to DuckDB’s native timezone(zone, ts). Then one line of SQL works everywhere.

Why it matters

In a plant, six hours is a whole shift. Get the conversion wrong and second shift’s downtime lands on first shift’s report. Nobody gets an error. Somebody gets blamed.

Bonus: it’s six hours in summer and seven in winter. Daylight saving time ends in Mountain Time on November 1. I wrote a test specifically for that boundary, and it caught a sneaky off-by-one on fall-back. If you aren’t testing against DST transitions, you have a bug and don’t know it yet.

Code

The SQL, both the broken version in the comment and the fix: margin_gap.sql
The DuckDB macro that makes the SQL portable: sources.sql
The DST transition test: test_shifts.py

Your ERP says the job was profitable. Your machines disagree.

Tim Collinson — Thu, 01 Oct 2026 17:29:58 +0000

What you'll get out of this

In about 12 minutes, you'll walk away with:

Why your ERP can hide a money-losing machine, in plain terms. Standard costing charges machine time at standard hours, so long setups vanish.
A working pattern for joining ERP, MES, and machine data on AWS: raw files in S3, cataloged in Glue, queried with Athena, deployed with one CDK stack.
The gotchas that cost us time, so they don't cost you any: Athena time zones, JSON SerDe key casing, 16-bit counters, and a cdk diff that isn't read-only.
Three questions to take to your own plant this week.
A repo you can deploy in your own AWS account for a fraction of a cent, and tear down cleanly.

Plant leaders: read the story, "Why the ERP can't see it," and the three questions at the end. Builders: jump straight to "The rebuild."

The margin report says everything is fine

Let's talk about the monthly margin review. Why would I write about a finance meeting on a technical blog? Great question. Stick with me; there's SQL at the end of this rainbow.

Picture it. Finance puts up the job cost report, and the bracket and enclosure work is holding its numbers. Everyone nods.

The plant manager doesn't nod. She knows second shift on the older press brake is a mess. Setups drag, jobs finish late, and the operators on that shift are always fighting the machine. But the report says those jobs made money, and she has no numbers to argue with. Nothing she can put on a slide will beat the ERP.

If you've worked in a mid-market plant, you've probably sat in that meeting. I have sat in versions of it for years, from pushing plant data into a PI historian at BP to building APIs for manufacturing software. (If you don't know what a PI historian is, count yourself lucky.) The plant manager is usually right. The problem is that proving it means joining three systems that were never designed to talk to each other.

So we built a plant, gave it that exact problem, and set out to prove it.

TL;DR

We simulated 90 days at Crestline Supply, a fictional metal fabricator: 1,252 finished jobs across ERP, MES, and press brake data, each realistically imperfect.

The ERP overstates margin on one brake's second-shift jobs by 4.4 points against the true figure, about $5,800 a quarter from a single machine on a single shift. Setups there run 2.84 times standard, and the ERP can't see it.

The ERP is wrong everywhere else too, but in the pessimistic direction. It's only flattering where the real problem lives.

We rebuilt job cost from all three sources with one SQL file running on Amazon Athena. It lands within 1% of the true cost for 91.9% of jobs.

Everything is open source and runs in your own AWS account. The Athena spend for the whole project was about two-tenths of a cent. https://github.com/trcollinson/crestline-supply

Meet Crestline Supply

Crestline Supply doesn't exist. I made it up. That's the point. It's a simulated metal fabrication plant that makes electrical enclosures, mounting brackets, and custom panels for HVAC and industrial customers. It runs two shifts. Parts go through a fiber laser, deburring, two press brakes, hardware insertion, powder coat, and assembly.

Why simulate instead of using real data? Because real plants never know the truth. When your rebuilt numbers disagree with the ERP, you can't tell who's right. In a simulation, you can.

The simulator works in two layers:

Ground truth. A discrete-event model records what actually happened: every setup, every stroke, every scrapped part, every operator's real time. This is the answer key, and it stays hidden.
Three imperfect views. From that truth, the simulator produces what each business system would actually record, complete with the gaps and distortions real systems have.

The analysis only ever sees the imperfect views. A test in the repo enforces that the query code can't read the answer key. The answer key is used for one thing: grading our work at the end.

The data shapes follow patterns common across mid-market ERPs and PLCs. If you've worked in these systems, they'll look familiar.

Three systems, three partial truths

Every system at Crestline is telling the truth about something. None of them is telling the whole truth.

System	What it knows	What it doesn't know	How it reports
ERP	The job, quantity, price, and standard cost	How long setup and run actually took	Labor rounded to 0.1 hr, posted the next day; machine burden at standard hours
MES	Who scanned onto which job, and when	Setup vs. run; whether the operator was really working	Nightly CSV files in local time with no time zone; about 3% of scans never closed
Press brakes	When each stroke happened	Which job it belonged to	PB-01: JSON on every change. PB-02: a 16-bit counter polled every 30 seconds that wraps

The answer to the plant manager's question lives in the gaps between these three. The machines know when setup ended. The MES knows who was on the job and for how long. The ERP knows what it was worth. Nobody has joined them.

Why the ERP can't see it

The ERP isn't broken. It's doing exactly what standard costing is designed to do, and that design hides long setups.

A job's cost has three main parts: material, labor, and burden. Burden is the cost of running the machine itself: depreciation, power, maintenance, floor space. At Crestline, the ERP's own work center rates put an hour on a press brake at $85 of burden and $38 of labor. The burden is the bigger number.

Here's the catch. Crestline's ERP, like most, takes actual labor from the MES but applies burden at standard hours. If a setup is supposed to take 20 minutes, the ERP charges 20 minutes of machine time, no matter how long the machine actually sat in setup.

Walk through one setup on PB-02, second shift, running 36 minutes over standard:

The operator stays scanned in the whole time, so the extra 36 minutes of labor (about $23) eventually reaches the ERP.
The extra 36 minutes of machine time (about $51 of burden) never does.

More than two-thirds of the cost of that overrun is invisible to finance. Finance isn't hiding it from you. The ERP is hiding it from finance. Now multiply. Over the quarter, PB-02 ran 179 setups on second shift, averaging 43 minutes over standard. That's about 128 machine-hours and roughly $10,900 of burden the ERP never charged. Even elsewhere in the plant, the ERP only sees about 90% of true burden, from ordinary setup variation and downtime. On PB-02 second shift, it sees 72%. A real problem turns into a healthy-looking margin.

There's a second effect pulling the other way. Operators stay scanned in through breaks, forgotten scans are only trimmed back to the end of the shift, and nobody scans off during downtime. So everywhere in the plant, the ERP charges about a third more labor than anyone actually worked. Labor is the smaller cost, but over-reporting it still charges each job more than it should, so margins look 2 to 4 points pessimistic. Keep that in mind; it matters for the results.

The rebuild

The rebuild is one SQL file with six commented views and a final select. It runs on Amazon Athena, directly over the raw files in S3.

Each system hands the rebuild a different piece of the story. Four steps in one Athena query join them, and the join surfaces the one place the ERP flatters itself.

The raw files land in S3 exactly as each system would have produced them, untouched. That matters: when you find a mistake later, you can always reprocess from the original.

Step 1: Put everything on one clock

The MES writes local wall-clock time with no offset. The ERP and machines use UTC. Before anything can be joined, every timestamp needs to mean the same instant.

This is where the first surprise showed up. scan_on AT TIME ZONE 'America/Denver' looks like it says "this is plant time." In Athena, it doesn't. Athena reads a bare timestamp as UTC and converts it, so 2:30 PM comes back six hours early (seven in winter). No error, just a wrong answer. We only caught it because our local test engine read the same line the opposite way.

The fix is with_timezone(), which states which zone the value is in. Athena views also can't store a zoned timestamp, so each view casts back to plain UTC:

-- The MES writes local time with no offset, so the conversion has to say
-- which zone it is in: with_timezone(). Not `at time zone`, which the two
-- engines read differently: DuckDB takes a bare 14:30 as plant time
-- (20:30 UTC), but Athena takes it as UTC and converts, six hours early.
select record_id,
       employee_id,
       job_num,
       op_seq,
       work_center,
       cast(with_timezone(scan_on,  'America/Denver') at time zone 'UTC' as timestamp) as scan_on,
       cast(with_timezone(scan_off, 'America/Denver') at time zone 'UTC' as timestamp) as scan_off
from fixed;

Step 2: Figure out which brake did the work, and when setup ended

The brakes don't know what job they're running. But a job's brake operation has an MES scan window, and a brake produces strokes. The first stroke after an operator scans on marks the end of setup.

PB-02 makes this harder. PB-02 is old. PB-02 is grumpy. PB-02 gives you a 16-bit stroke counter, polled every 30 seconds, and nothing else. The counter rolls over at 65,535 (ours wrapped once, on August 26 at 2:14 PM), so the strokes since the last poll are the difference mod 65,536:

-- PB-02's counter is 16 bits wide and rolls over at 65,535, so the
-- strokes since the last poll are the difference mod 65,536.
select 'PB-02' as brake,
       gw_ts   as counted_at,
       (cnt - lag(cnt) over (order by gw_ts) + 65536) % 65536 as strokes
from pb02_counts

That handles rollover, not a counter that resets to zero when someone power-cycles the machine. A reset looks like a wrap and would count phantom strokes. That mess shows up in a later post.

The 30-second poll caused a subtler bug. PB-02 reports a stroke up to 30 seconds after it happens, so the previous job's last stroke often landed just after the next operator scanned on. Setups looked instant. The fix is to look one poll late at both ends of the scan window.

Then there's the question in this step's title: which brake? The query counts each brake's strokes during the scan window and picks the brake whose count comes closest to the pieces the operator reported. Ties go to the brake whose last stroke came nearest the operator's scan-off:

-- PB-02 reports strokes up to 30 s late: look 30 s late at both ends.
join brake_strokes as b
  on b.counted_at >  o.started_at + interval '30' second
 and b.counted_at <= o.ended_at   + interval '30' second

-- Closest stroke count to the reported pieces wins.
-- On a tie, the brake whose last stroke was nearest scan-off.
row_number() over (
    partition by o.job_num, o.op_seq
    order by abs(s.strokes - o.pieces),
             abs(date_diff('second', s.last_stroke_at, o.ended_at))
) as rank

The full version is in queries/margin_gap.sql.

Against the answer key, all 935 brake operations were placed, 933 on the right brake and all 935 on the right setup shift. The two misses are two separate exact ties: a pair of 16-piece jobs and a pair of 46-piece jobs, each pair running at the same time, one job on each brake, so the counts matched either way.

Here's one real second-shift job from the run. Tasha scanned onto J100795 at 7:36 PM, and PB-02 didn't make its first stroke until 8:32. The ERP charged 20 minutes of setup; the shaded stretch is the machine time it never sees.

Step 3: Clean up the labor the way the plant should

About 3% of MES scans are never closed. Someone forgets to scan off, and a supervisor closes the scan at 06:00 the next morning. Posted as-is, a 20-minute operation becomes a 14-hour one. (Nobody bends brackets for 14 hours straight. Nobody.)

Crestline handles this the way many plants do: a supervisor's labor exception report trims forgotten scans back to the end of the shift. That's better than nothing, but it still charges hours the operator spent on other work.

The query does one thing the supervisor can't do by hand across thousands of rows: it caps each scan at the same operator's next scan-on. If Tasha scanned onto another job at 3:10, she wasn't still on the first one at 11:00. That single rule is most of the difference between the ERP's labor and the rebuilt labor. And notice who was right all along: the supervisor already knew this data was garbage, and the exception report is a workaround someone built because the systems failed them. With the right join, their daily forensics becomes something the query does continuously, on every job.

Step 4: Recompute cost the way it actually happened

With clean hours and real setup times, cost is straightforward: actual labor hours times the labor rate, actual machine hours times the burden rate, plus material. Then compare the ERP's margin to the rebuilt margin, grouped by brake and by the shift the setup happened on.

What the rebuild found

The ERP is wrong about every group of jobs in the plant. It's only flattering in one place, and that's exactly where the problem is.

Margin by brake and setup shift, 90 days, 1,252 finished jobs:

Brake and shift	ERP margin	Rebuilt margin	True margin	Setups vs. standard
PB-01, 1st shift	24.3%	25.5%	27.1%	1.06×
PB-01, 2nd shift	25.9%	27.8%	28.6%	1.07×
PB-02, 1st shift	23.0%	23.7%	25.0%	1.14×
PB-02, 2nd shift	20.4%	14.8%	16.0%	2.84×
No brake operation	23.6%	26.7%	27.7%	—

"True margin" comes from the hidden answer key. In a real plant you'd have the other three columns and not that one. If you run the query yourself, its gap column shows 5.7 points for PB-02 second shift. That's the ERP against the rebuild, the gap a plant manager would actually see; the 4.4 points in this post is the ERP against the truth.

Three things to notice:

Everywhere else, the ERP is pessimistic. It understates margin by 2 to 4 points against the truth, because time nobody worked gets charged as job labor. A finance team that learns to discount the ERP a little would be right almost everywhere.
On PB-02 second shift, it flips. The ERP overstates margin by 4.4 points against the truth. Measured against the plant's usual pessimistic bias, the real gap is bigger than it looks. A finance team that corrected the ERP up by its usual 3 points would put this group at 23.4% against a true 16.0%, a 7.4-point miss. On that group's roughly $131,000 of quarterly revenue, the overstatement is worth about $5,800 a quarter, or roughly $23,000 a year, from one machine on one shift.
The rebuild errs conservative. It lands 1.3 points below the truth on PB-02 second shift, and below the truth in every group by 0.8 to 1.6 points, because the few scans it can't fully clean err high, not low. That's the direction you want a management number to miss in.

Grumpy PB-02, second shift, exactly where the plant manager knew to look. That's the conversation she needed. Not "second shift feels slow," but "setups on PB-02 second shift run 2.84 times standard, and the ERP can't see it because burden is charged at standard hours."

How we know it's right

The rebuild lands within 1% of the true job cost for 91.9% of jobs. The ERP's own "actual" cost does that for 14.9%.

Because we have the answer key, we can grade the rebuild the way you never can in a real plant. An evaluation script compares every job's rebuilt cost to its true cost:

Measure	Rebuilt cost	ERP "actual" cost
Jobs within 1% of true cost	91.9%	14.9%
Jobs within 5% of true cost	94.6%	53.0%
Median error	0.03%	4.52%

The more useful part is the misses. All 101 jobs outside 1% are explained: 91 are forgotten scan-offs the query can't fully clean, 9 are operators who stepped away during downtime while still scanned in, and 1 is a machine sitting idle while scanned. None are unexplained.

One honest note: the first version of the simulator was too clean. The rebuild matched the truth almost perfectly, which told us the plant wasn't messy enough to be believable. Adding forgotten scan-offs made it realistic, and that's also when we had to model the supervisor's exception report, because posting those scans as-is wrecked the ERP's numbers in a way no real plant would tolerate.

What building it on AWS taught us

The AWS side is deliberately small: an S3 raw bucket, an Athena results bucket, a Glue database, and an Athena workgroup with a 1 GB scan cap, all deployed by one CDK stack. Small didn't mean free of surprises. It never does.

Athena reads a bare timestamp as UTC. Covered above, and the most likely thing to bite anyone working with plant data. Use with_timezone() before AT TIME ZONE.
The OpenX JSON SerDe lowercases keys, even inside nested objects it returns as text. JSON paths are case-sensitive, so fields silently came back null. The fix was reading each PB-01 message as a single text line and using json_extract_scalar. PLC tag names like N7:12 weren't going to be valid Glue column names anyway.
Partition projection didn't fit. The ingest_date partition is dated October 1, the extract date, which was still in the future when we built this. A projection range ending at NOW skipped it, so setup runs MSCK REPAIR TABLE instead.
Plain cdk diff isn't read-only. It uploads the template and creates a change set. (Yes, really. I was surprised too.) Use cdk diff --method=template when you want a purely local comparison.
Stack tags changed. With the @aws-cdk/core:explicitStackTags feature flag, Tags.of() no longer sets stack tags, so tags are applied both ways on purpose. A Glue database can't be tagged through CloudFormation at all.
Idempotent uploads came almost free. For a single-part upload with SSE-S3, the ETag is the content's MD5, so rerunning the upload skips unchanged files. That wouldn't hold with KMS encryption or multipart uploads.

Every one of these is small. Together they're the difference between a demo that works on my machine and one that works in yours.

Run it yourself

The whole thing deploys into your own AWS account in a few minutes, costs well under a dollar, and tears down cleanly. The simulation is deterministic, so you'll get exactly the numbers in this post.

You'll need: uv, Node.js with the CDK CLI at version 2.1143 or later, the AWS CLI, and an AWS profile with a region configured.

Deploy and run:

git clone --branch post-01-margin-gap https://github.com/trcollinson/crestline-supply.git && cd crestline-supply
export AWS_PROFILE=<profile>
uv sync
(cd infra && cdk bootstrap && cdk deploy)
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42 --upload
uv run crestline athena setup
uv run crestline query margin-gap --engine athena

cdk deploy stops once to ask you to approve the IAM role it creates. Type y and it carries on.

Then try the experiment that makes the point. Set enabled: false under pb02_second_shift_setups in scenarios.yaml, rerun the backfill with --upload, and rerun the query. PB-02 second shift comes back at 25.2% in the ERP and 25.5% rebuilt, with setups at 1.07× standard, right in line with everything else. Bonus detail: with the scenario off, PB-02 second shift handles 219 jobs instead of 174. The slow brake was also doing less work, because each job goes to whichever brake frees up first.

No AWS account handy? The README has a local option.

Cost: the upload is 99 files and 46.6 MB, so storage rounds to zero. Each query run scans about 100 MB, around a twentieth of a cent, so rerun as much as you like. Athena spend for the entire project, including every test query, was about $0.002.

Teardown:

(cd infra && cdk destroy)
aws logs describe-log-groups --log-group-name-prefix /aws/lambda/CrestlineFoundation --query 'logGroups[].logGroupName'
aws logs delete-log-group --log-group-name <name from above>

cdk destroy removes the stack and empties the buckets. The two aws logs commands clean up the log group CDK's auto-delete Lambda leaves behind. If you bootstrapped CDK just for this, also delete the CDKToolkit CloudFormation stack, then empty and delete the staging bucket it keeps.

What this means for your plant

You don't need a perfect data platform to find this kind of problem. You need the join, and you need to trust it.

Three questions worth asking your team this week:

Do we know actual setup time by machine and shift, or only standard? If the answer is "standard," your long setups are hiding in your margins right now.
Can we connect a machine's output to the job it was running? Even a dumb stroke counter and an MES scan window are enough to answer "when did setup actually end?"
Does finance know burden is applied at standard hours? Most people outside cost accounting have never thought about it, and it's the reason the ERP looked healthy here.

If the answers are "no," you're in good company. That's most mid-market plants I've worked with. The good news is that the data to answer them is usually already sitting in systems you own. Go find it. Do it this week.

What's next

This post cheated a little. The ERP data arrived as a clean bulk export. In real life, getting data out of a mid-market ERP means tokens that expire mid-extract, paging that shifts under you, and a LastModified field that doesn't always get updated. That's the next post in the Crestline series, in two weeks.

Tim Collinson has spent nearly 30 years building software, much of it on AWS, across manufacturing, oil and gas, and enterprise IT. Reach him at trcollinson@gmail.com.

P.S. If you deploy it and your numbers don't match mine, open an issue. I want to know. Deterministic simulations are only fun until they aren't.