ERP 增量抽取有 30.8% 資料列錯誤，卻沒有報錯

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 post-02-erp-extraction)

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.

1. Sign in once.
2. Ask for everything with LastModified newer than the newest stamp you already have.
3. Follow nextLink until it runs out.
4. 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:

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:

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.

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.

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 --branch post-02-erp-extraction 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:

1. When did we last compare our copy of the ERP to the ERP itself? Not the row counts. The rows.
2. Does our extract trust LastModified? If so, find out which ERP operations don't update it. There's almost always at least one.
3. 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.