Reconciling Feature Counts Across Staging and Published Layers

Between the batch a source hands you and the layer your consumers query there are usually three or four hops: a landing table, a staging schema, a validity gate, a promotion, sometimes a derived publication. Features are legitimately lost at some of those hops and illegitimately lost at others, and a single count taken at the end cannot tell you which. Reconciliation makes the difference visible: it accounts for every feature at every hop, attributes each loss to the step that caused it, and turns “the published layer is smaller than expected” into “the validity gate rejected 8,412 features from source X, which is 40 times its usual rate”. This guide covers how to build that ledger and how to alert on it. It belongs to automated row-count and attribute sync under spatial data freshness and quality metrics.

Feature counts at each pipeline hop with attributed losses between them A descending bar chart shows feature counts at five stages. The source manifest declares one hundred thousand features. The landing table holds ninety-nine thousand nine hundred and forty, with sixty attributed to transfer errors. Staging holds ninety-nine thousand nine hundred and forty with no loss. The validity gate admits ninety-one thousand five hundred and twenty-eight, with eight thousand four hundred and twelve rejected as invalid geometry, marked as the anomalous step. The published layer holds ninety-one thousand five hundred and twenty-eight with no further loss. Each loss is labelled with its attributed cause. Every loss attributed to the hop that caused it source manifest 100 000 landing 99 940 −60 transfer staging 99 940 −0 validity gate 91 528 −8 412 invalid published 91 528 −0 A single end-to-end count would say “8 472 short” and leave you to guess which hop.

Problem framing: which losses are legitimate

Not every drop is a defect, and a reconciliation that treats them alike produces alerts nobody acts on. Four categories cover the hops in a typical spatial pipeline.

Expected and constant. Deduplication against an existing key, or a filter that excludes a documented subset — features outside the layer’s declared extent, for example. These should have a stable count and a stable ratio, and the ratio is what you monitor.

Expected and variable. Validity rejection is the archetype: some proportion of features from any real source fail geometry validity, the proportion varies a little by batch, and it is only a defect when it moves sharply. Monitor the ratio against its own baseline.

Never expected. Transfer truncation, a failed partition write, a transaction that rolled back half a load. These should be zero, and any non-zero value is an incident.

Silent and invisible. The dangerous category: features that vanish without any step recording their loss. A COPY that stopped early, an upsert that collided on a key and overwrote rather than inserted, a spatial filter applied accidentally by a view definition. These are only detectable by reconciliation, because no component reports them.

The purpose of the ledger is to convert the fourth category into the third: every feature is either present at the next hop or accounted for by a named loss, and the residual is by definition unexplained.

Implementation: a per-hop ledger keyed on the batch

Record counts and attributed losses at each hop as the batch moves, in the same transaction as the movement.

CREATE TABLE IF NOT EXISTS audit.batch_reconciliation (
  layer          text        NOT NULL,
  batch_id       text        NOT NULL,
  hop            text        NOT NULL,     -- manifest|landing|staging|gate|published
  features       bigint      NOT NULL,
  lost           bigint      NOT NULL DEFAULT 0,
  loss_reason    text,                     -- transfer|duplicate|invalid|out_of_extent
  recorded_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (layer, batch_id, hop)
);

-- The reconciliation view: does every hop account for its predecessor?
WITH ordered AS (
  SELECT *,
         LAG(features) OVER (PARTITION BY layer, batch_id
                             ORDER BY array_position(
                               ARRAY['manifest','landing','staging','gate','published'], hop)
                            ) AS prev_features
  FROM audit.batch_reconciliation
  WHERE layer = 'parcels_authoritative' AND batch_id = :batch_id
)
SELECT hop,
       prev_features,
       features,
       lost,
       loss_reason,
       -- Anything the hop did not explain. Must be zero.
       COALESCE(prev_features, features) - features - lost AS unexplained
FROM ordered
ORDER BY array_position(ARRAY['manifest','landing','staging','gate','published'], hop);

The unexplained column is the whole point. A hop that loses features without recording a reason produces a non-zero value, and that value is the signal no other check produces.

Getting the manifest count is the part that requires cooperation from the source. Where the provider supplies a record count in a sidecar file or an API response, use it; where they do not, the count of the delivered file is the best available proxy and should be recorded as such, because it cannot detect a truncated delivery.

# reconcile.py — record each hop as the batch moves.
from opentelemetry import metrics

meter = metrics.get_meter("gis.etl")
hop_features = meter.create_counter("gis.etl.hop_features_total")
hop_lost = meter.create_counter("gis.etl.hop_lost_total")

def record_hop(conn, layer, batch_id, hop, features, lost=0, reason=None):
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO audit.batch_reconciliation
                 (layer, batch_id, hop, features, lost, loss_reason)
               VALUES (%s, %s, %s, %s, %s, %s)
               ON CONFLICT (layer, batch_id, hop) DO UPDATE
                 SET features = EXCLUDED.features,
                     lost = EXCLUDED.lost,
                     loss_reason = EXCLUDED.loss_reason""",
            (layer, batch_id, hop, features, lost, reason))
    attrs = {"layer": layer, "hop": hop}
    hop_features.add(features, attrs)
    if lost:
        hop_lost.add(lost, {**attrs, "reason": reason or "unknown"})

Alerting on the three shapes

groups:
  - name: batch-reconciliation
    rules:
      # 1. Anything unexplained is an incident, at any volume.
      - alert: BatchUnexplainedLoss
        expr: max by (layer, hop) (gis_etl_unexplained_loss) > 0
        for: 5m
        labels: { severity: critical, data_domain: spatial }

      # 2. A never-expected loss reason appeared.
      - alert: BatchTransferLoss
        expr: increase(gis_etl_hop_lost_total{reason="transfer"}[1h]) > 0
        for: 0m
        labels: { severity: critical, data_domain: spatial }

      # 3. A normally-variable loss moved sharply against its own baseline.
      - alert: ValidityRejectionSpike
        expr: |
          (
            increase(gis_etl_hop_lost_total{reason="invalid"}[1h])
              / clamp_min(increase(gis_etl_hop_features_total{hop="staging"}[1h]), 1)
          )
          > 5 *
          avg_over_time(
            (
              increase(gis_etl_hop_lost_total{reason="invalid"}[1h])
                / clamp_min(increase(gis_etl_hop_features_total{hop="staging"}[1h]), 1)
            )[7d:1h] offset 1d
          )
        for: 30m
        labels: { severity: warning, data_domain: spatial }

The multiplier of five on the baseline rather than a fixed threshold is deliberate: validity rejection rates differ enormously between sources, and a fixed percentage either misses a regression on a clean feed or fires constantly on a noisy one.

Four loss categories and the alerting treatment each one warrants Four rows describe loss categories. Expected and constant losses, such as deduplication and out-of-extent filtering, are monitored as a stable ratio and alert only on a change in that ratio. Expected and variable losses, such as validity rejection, are compared against their own rolling baseline with a multiplier. Never-expected losses, such as transfer truncation, alert immediately at any volume. Unexplained losses, where a hop lost features without recording a reason, alert immediately and are described as the category reconciliation exists to expose. Four categories, four treatments expected & constant — dedupe, out-of-extent filter monitor the ratio; alert when the ratio itself changes, not on the count expected & variable — validity rejection compare against the source’s own 7-day baseline with a multiplier, never a fixed percentage never expected — transfer truncation, rolled-back partial load alert at any non-zero volume; there is no acceptable rate unexplained — a hop lost features and recorded no reason the category reconciliation exists to expose; nothing else in the platform can see it Unexplained loss over ninety days, showing it should sit at exactly zero A series of unexplained feature loss per batch is plotted over ninety days. It reads exactly zero for the whole period except for two isolated spikes. The first spike coincides with a database upgrade, the second with a change to the load script. A note states that the value is either zero or an incident, with no meaningful middle ground, which makes it one of the few metrics that can be alerted on at any non-zero value. Unexplained loss is zero or an incident — there is no middle ground database upgrade load script change day 0day 45day 90 Both spikes were introduced by a change nobody expected to affect feature counts.

Verification

Delete a hundred rows from staging outside the pipeline and re-run the reconciliation for that batch. The gate hop must report a hundred unexplained. If it reports nothing, the hop is deriving its predecessor count from itself rather than from the recorded previous hop, which makes the whole ledger self-confirming and useless.

Then run a clean batch end to end and confirm every unexplained value is exactly zero. Persistent small non-zero values usually mean a hop’s count is taken at a slightly different moment than the movement — counting staging after a concurrent delete, for example — and the fix is to count inside the same transaction.

Gotchas

Counting outside the transaction. A count taken after the transaction commits can include concurrent changes and produces small permanent discrepancies that mask real ones.

Trusting the delivered file as the manifest. A truncated file is internally consistent. Where the provider offers a declared count, use it; where they do not, record the limitation explicitly so nobody assumes the check covers truncation.

Reconciling only the final count. The end-to-end difference is the sum of every hop’s loss and tells you nothing about which hop. Per-hop is the whole value.

No loss_reason. A hop that records a loss without a reason produces a ledger that balances and explains nothing. The reason is what routes the investigation.

Alerting on absolute rejection counts. Volume varies; ratios do not. Compare ratios against baselines, as the parent topic’s reconciliation rules do.

FAQ

Should reconciliation block promotion?

Unexplained loss should, always — it means the pipeline cannot account for its own data. A validity-rejection spike should warn rather than block, because rejecting bad geometry is the gate working correctly and blocking would stop a load that is doing the right thing.

How does this interact with incremental loads?

Incremental loads reconcile the same way, with the manifest count being the delta rather than the layer total. The one addition worth making is a periodic full reconciliation — monthly is usually enough — because incremental accounting can drift over many cycles and only a full comparison catches the accumulated difference.

What about features that are legitimately updated rather than inserted?

Count operations rather than rows at hops where upserts occur: inserted, updated and unchanged should sum to the input. An upsert that silently overwrites two source rows into one destination row is exactly the silent loss this check exists to catch, and it is invisible if you only count destination rows.

Does this replace checksum comparison?

No — counts detect missing features, checksums detect altered ones. A batch with the right count and the wrong content passes reconciliation completely, which is why the content-level checks in automated row-count and attribute sync run alongside rather than instead.

Can reconciliation run for streaming feeds?

Yes, over windows rather than batches. Treat a fixed interval — five minutes, say — as the batch identifier, and record the same hops against it. The manifest count becomes the count of messages the source acknowledged sending in that window, which most streaming transports expose as an offset difference. The alerting shapes are unchanged; only the unit of accounting moves from a delivered file to a time window.

How long should the ledger be kept?

Long enough to serve a post-incident review, which means months at minimum. It is one row per hop per batch, so the storage cost is negligible against its value when someone asks where eight thousand parcels went.