The 95-Minute Notebook

This article was originally published on LinkedIn and is archived here on texlytics.com.

The symptom

Our Bronze ingestion notebook on Microsoft Fabric had a runtime problem. The notebook pulls incremental changes from CMiC’s REST APIs into a lakehouse on a 15-minute cadence — dozens of endpoints, all driven from a single control table. The kind of thing that should finish in a few minutes. It was taking 95.

Here’s what made it interesting: the Python wasn’t slow. The API calls came back quickly. The filtering and dedupe logic ran in seconds. Every individual piece of work I could measure looked healthy, and yet the wall clock kept reporting an hour and a half. The time was going somewhere the obvious logs didn’t show.

The hunt

Breaking the runtime down per endpoint ended the mystery fast. One name dominated everything else combined: AP_Registered_Invoice_Details — the line items behind every registered AP invoice. About 53,000 rows.

And 53,000 rows is nothing. A bulk fetch of that size is a rounding error in this pipeline. So the question changed shape: why was a small endpoint eating almost the entire run?

The mechanism

In our framework, every CMiC endpoint is classified by the change signals it exposes. The best case gives you a stable UUID plus create and update dates — fetch only what moved since the last watermark, merge by key, done. The worst case gives you none of that. AP_Registered_Invoice_Details is the worst case: no usable date columns, nothing to anchor an incremental fetch on.

So the framework did the only correct thing it could do. Full fetch, every run. All 53,000 rows, every 15 minutes. And then the merge — with no narrower signal to target, the load step read and rewrote the entire Delta table each time. Fetch everything, rewrite everything, 96 times a day.

The notebook wasn’t misbehaving. It was faithfully redoing all of its work, every run, because the source gave it no way to know what changed. That’s the trap with signal-less endpoints: the pipeline isn’t slow because of a bug. It’s slow because full refresh is the only honest answer to “what changed?” when the source refuses to say.

The reframe

I couldn’t add date columns to CMiC’s API. But I didn’t have to, because the signal I needed existed one level up.

Detail rows have a parent. The invoice header endpoint, AP_Registered_Invoices_by_Code, is the good scenario: stable UUID, create date, update date. Headers can run incrementally with no drama. And CMiC’s details endpoint supports a finder — selectByInvVUuid — that filters detail rows by their parent invoice’s UUID.

So the architecture flipped. Instead of asking the details endpoint for everything, the headers became the driver. Pull the invoice headers that changed since the last watermark. Fan out one targeted call per changed parent, fetching only that invoice’s detail lines. Merge those rows by detail UUID. The details endpoint never gets asked an open-ended question again.

We had already proven this pattern elsewhere in the pipeline — CMiC only serves job cost categories per job, so a per-parent fan-out with a worker pool already existed in the codebase. This was the same shape pointed at a new pair of endpoints.

The notebook now averages about six minutes. Same data, same table, same correctness guarantees. The only thing that changed was who drives.

Where it bites

Honest caveat: this design has a known blind spot. It assumes the parent’s update date moves when a detail line changes. Most ERPs cascade child edits up to the parent timestamp, and my intuition says CMiC does too — but intuition isn’t proof, and I never got a definitive answer. If a detail line can change while its header timestamp stays still, the incremental path misses it.

So the design doesn’t rely on trusting that timestamp alone. Two layers cover the gap. First, the pattern self-heals: any future touch to a parent invoice pulls all of its detail lines fresh, so a missed edit gets corrected the next time that invoice moves. Second, a nightly reconciliation scrub — a full compare of detail rows against the source — is the next piece on the roadmap, and the runway for it is already carved out. The incremental notebook runs every 15 minutes, but a schedule governor pauses it from 7 p.m. to 5 a.m., giving the scrub a dedicated window where it can hammer the API without colliding with normal operations and tripping the concurrency limits. If you read my earlier piece on CMiC’s rate limiting, you know why I don’t let two heavy processes share that API at the same time.

If you’re watching the pattern across this series: this is the third time the durable fix wasn’t cleverer code. It was refusing to trust a single signal from the source, and building a reconciliation layer that assumes the source will eventually lie.

The portable lesson

When a child table has no change signal, borrow the parent’s. Almost every signal-less endpoint sits underneath something that does carry a UUID and an update date, and if the API gives you any way to filter children by parent — a finder, a foreign key parameter, anything — you can convert a full-refresh problem into an incremental one without the vendor changing a thing.

Full refresh is a default, not a destiny. When a source won’t tell you what changed, stop interrogating the table that can’t answer and find the one that can.