How to Tell Extraction Failure Cause: API vs Schema

The first thing I check at 2 a.m. is not the parser#

A pipeline that fails at 2 a.m. is usually trying to tell you one of three things: the source is having a transient API issue, the contract changed under you, or the data genuinely is not there. If you treat all three the same, you end up retrying the wrong problem, or worse, paging the business for a record that was never supposed to exist yet.

That is why the real question is not “did the extract fail?” It is, how do you tell whether an extraction failure is a transient API issue, a schema drift problem, or a genuine data gap that needs business follow-up? The answer starts before the failure, but when you are already on call, the fastest path is still systematic.

Start with the failure shape, not the error message#

The quickest split I use is this: transport problem, contract problem, or content problem. You can usually classify that in under five minutes if your connector logs are worth anything.

Look at these three things first:

1. HTTP behaviour

   • 429s, 503s, connection resets, read timeouts, TLS handshake failures.
   • If the error rate spikes across multiple endpoints at the same time, that points to API issue territory, not schema drift.

2. Payload shape

   • Did the response still arrive, but fields moved, renamed, changed type, or became nested differently?
   • That is schema drift, even if the API returned a clean 200.

3. Record completeness

   • Did the extract succeed, but the count is off, the date window is empty, or only one customer, site, or ledger is missing?
   • That is where you start checking for a data gap, late-arriving records, timezone mismatch, or backfill logic bug.

If you are running Databricks warehouses feeding Power BI from ERP data, this split matters because the symptom in the report is often the last thing to break. The real failure happened earlier, in the API extractor, and the report just inherited the mess.

Key takeaway: classify the failure by transport, contract, or content first, because retries fix only one of those three.

The 2 a.m. triage sequence that actually saves time#

When a job fails, I want four facts before I touch the code:

• Did the source return a response at all?
• Did the response schema match the last known good run?
• Did the row count, checksum, or high-water mark move as expected?
• Did the failure happen on the first attempt or only after retries?

That sequence answers the practical version of how do you tell whether an extraction failure is a transient API issue, a schema drift problem, or a genuine data gap that needs business follow-up? without sending you down a rabbit hole.

If it failed before a payload arrived#

This is usually a transient API error, rate limiting, auth expiry, DNS, or a network path problem. A single 500 is noise. A run of 500s across multiple retries, especially with the same correlation ID or a source-side incident, is not.

What I look for:

• 429s with Retry-After headers
• 5xx spikes clustered in the same minute
• timeouts that line up with a source-side maintenance window
• auth failures after token expiry or certificate rotation

If the source is an ERP or WMS API, and the same endpoint works again 10 minutes later, I do not call that schema drift. I call it a transient API error and keep the connector logic narrow. That is also why a good extractor design matters, which is the point of How to Design API Extractors for ERP Rate Limits.

If the payload arrived but the parser broke#

That is usually schema drift, but not always. Sometimes the source has not changed shape, only behaviour.

A classic mistake is to see a parser exception and assume the field structure changed. In practice, the source may have started returning:

• an empty string where you expected an object
• a number as a quoted string
• a null in a field your code treated as mandatory
• a partial page where the final record is truncated because the API timed out mid-stream

That last one gets misread all the time. The schema did not change, the response was just incomplete. If your parser is strict, it fails like a schema issue. If your retry succeeds, the job turns green and everyone relaxes too early.

Retries can hide a bad run#

A green retry is not proof the data is clean. It only proves the source answered on the second attempt.

That distinction matters in ETL failure diagnosis. If the first run fetched 8,412 rows and the retry fetched 8,417, you do not have a simple API issue. You have an unstable extract. The run may be technically successful and still be unusable for finance, operations, or customer reporting.

My threshold for escalation is simple:

• One failed attempt, then identical success with identical row counts and hashes: usually transient
• One failed attempt, then success with different row counts or changed checksums: suspicious, keep investigating
• Repeated failures over a short window with the same payload shape: likely source-side or contract issue
• Success with missing slices, missing dates, or missing entities: treat as a data gap, not a connector win

That is the part most teams miss. They optimise for pipeline status, not data integrity.

Schema drift is not just field renames#

A lot of teams think schema drift means someone renamed customer_name to client_name. That is the easy version.

The harder version is when the source API silently changes behaviour without changing the schema. The field names are the same, the JSON validates, and the records look fine at a glance. The numbers are still wrong.

This shows up as:

• totals that no longer reconcile to the source system
• line items that disappear from a status filter
• records whose timestamps shift because the API changed timezone handling
• duplicate suppression that suddenly removes valid rows
• a “closed” flag that now includes records that used to be excluded

If you want to know whether the data is semantically wrong rather than just incomplete, compare against a business invariant, not just the payload shape.

For example:

• invoice totals by day should reconcile within an agreed tolerance to the ERP
• open orders should not drop to zero outside a known shutdown window
• stock movements should never go backwards without a reversal transaction
• a supplier feed should not suddenly lose one warehouse while the others stay stable

That is where lakehouse observability pays for itself. In a Databricks warehouse, you can keep a small set of reconciliation checks beside the raw landing tables, then flag when the shape is valid but the business meaning is off. Power BI is then reporting on trusted data, not just successfully loaded data.

When the data is missing, ask whether it was ever supposed to exist#

A genuine data gap is different from a broken extract. The source may be fine. The extractor may be fine. The issue is that the expected record was not there, or not there yet.

This is the part that needs business follow-up, but only after you rule out timing problems.

Check these before escalating#

• Extraction window mismatch

  • Did the job run in UTC while the source reports in AEST or AEDT?
  • Did the backfill logic cut off at midnight local time instead of source time?
  • Did daylight saving shift the boundary by an hour?

• Late-arriving records

  • Was the source known to post transactions after close of business?
  • Are you extracting on event time but the source only guarantees availability on processing time?

• Incremental watermark drift

  • Did the last successful cursor advance past records that were later corrected upstream?
  • Did a reprocess window fail to include the correction period?

If the missing data sits inside a known late-arrival window, do not page finance or operations yet. Re-run the window and compare the delta. If the record is still absent after the window has fully aged out, then you may have a real business exception.

That is the cleanest answer to how do you tell whether an extraction failure is a transient API issue, a schema drift problem, or a genuine data gap that needs business follow-up? You do not escalate on absence alone. You escalate on absence after timing, watermark, and source behaviour have been checked.

The observability I wish every extractor had#

Most root-cause pain comes from not having the right metadata at the moment of failure. If I could add only a few fields to every extract job, it would be these:

| Metadata | Why it matters | |---|---| | Source endpoint and version | Tells you whether the contract changed | | HTTP status, retries, and backoff timing | Separates transient API issue from persistent failure | | Request window and timezone | Catches window mismatch and DST bugs | | Row count by page and by entity | Shows partial loads and silent omissions | | Schema fingerprint from the last good run | Detects drift faster than eyeballing JSON | | First bad record sample | Makes the failure concrete, not theoretical | | Watermark before and after run | Shows whether the cursor advanced incorrectly | | Correlation ID or request ID | Lets you talk to the source owner with evidence |

If you have those fields, how do you tell whether an extraction failure is a transient API issue, a schema drift problem, or a genuine data gap that needs business follow-up? becomes a lot less philosophical. You can usually prove it.

A practical decision tree for the next failure#

Use this when the alert lands in Slack and everyone wants an answer fast.

1. Check transport health#

• 429, 5xx, timeouts, auth, DNS, TLS?
• If yes, retry once with the same parameters.
• If the second run succeeds with the same counts, mark it transient and monitor.

2. Check payload integrity#

• Did the body parse?
• Did field names, types, nesting, or required keys change?
• If yes, compare against the last known good schema fingerprint.

3. Check data completeness#

• Are counts, totals, or key slices missing?
• Did the watermark move?
• Did the time window align with the source timezone?
• If no, look for late arrival or backfill issues.

4. Check semantic consistency#

• Do totals reconcile?
• Are status distributions plausible?
• Did one entity, site, or channel disappear while others stayed stable?
• If yes, you may have a silent upstream behaviour change.

5. Decide whether to escalate#

• Transient API issue, retry and document.
• Schema drift, patch the connector and add a guardrail.
• Genuine data gap, raise business follow-up with evidence.

That is the part I would rather see in a runbook than a generic “check the logs” note.

What good teams do differently#

The best analytics teams in Australia do not wait for a failure to teach them this. They build extractors that can explain themselves.

That usually means:

• storing raw responses for a short retention window
• versioning schema expectations separately from transformation code
• keeping row-count and checksum checks at landing, not only in the warehouse
• tracking source timestamps, ingest timestamps, and timezone assumptions separately
• alerting on semantic anomalies, not just job failures

If you are already centralising ERP and operational data into a lakehouse, this is where an embedded data and analytics function pays off. A Data Analytics & Lakehouses setup is only useful if it can tell you why a run failed, not just that it failed. Otherwise you are just moving broken data faster.

The business follow-up call should be rare, and specific#

When you do escalate, do not send “the extract is missing data” and walk away. That is not enough for finance, operations, or a product owner to act on.

Send:

• the expected window
• the actual window loaded
• the missing entity or transaction type
• the last known good run
• whether the source API was available
• whether the gap survives a re-run after the late-arrival window

That gives the business something they can answer. For example, “the supplier did not submit this batch,” “the warehouse posted late,” or “the ERP status changed and the record is now legitimately excluded.”

That is the difference between a noisy alert and a real operational issue.

Build the classification into the pipeline, not the incident call#

If you only classify failures during incidents, you will keep rediscovering the same problems. The better move is to bake the decision points into the pipeline itself, so the extractor can label its own failure mode before anyone opens the log file.

That is the kind of work we usually do in Custom Development and Embedded IT Leadership engagements, because this is not a one-off script problem. It is core infrastructure. For businesses in Australia running ERP extracts into reporting layers, the cost of guessing wrong is usually higher than the cost of building the checks properly.

The next time it breaks, do this first#

Before you retry, capture:

• the exact HTTP status or exception
• the request window and timezone
• the last good schema fingerprint
• the row count and checksum from the failed run
• whether the source returned a full payload or a partial one

Then re-run once. If it comes back green, compare the counts and hashes, not just the job status. If they changed, you still have a problem.

If you want help putting that into a pipeline that can tell the difference between API issue, schema drift, and data gap without waking the wrong person, talk to Artigence. We build the extractors, the checks, and the reporting layer together, so the system can show its work instead of making you guess.