DEV Community: BigdataSight

The query succeeded. Which table state did it read?

Rui Wang — Thu, 30 Jul 2026 02:31:28 +0000

Why two successful queries can count different rows when they resolve different Delta or Iceberg table states.

Two queries return different counts for the same Iceberg table:

the catalog query returns 12,480,032 rows;
a direct scan of the storage folder returns 12,691,404 rows.

Neither query fails. The difference is 211,372 rows. The numbers are
fictional, but the investigation problem is real.

Before comparing SQL engines, compare the inputs. The catalog query and the
folder scan may not have read the same logical table.

A lakehouse table is a selection of files

A Delta or Iceberg table may store its rows in Parquet files, but the directory
is not the table definition.

For Delta, a table version is reconstructed from its transaction log. The log
records which logical files were added or removed. A logical file can also
carry a deletion vector that marks rows as no longer present. A file removed
from the current version may remain in storage until a later cleanup.

For Iceberg, a catalog or table reference points to table metadata. That
metadata identifies a snapshot. The snapshot points to a manifest list, and
the manifests identify the data files and delete files used for the scan.

This distinction explains why listing every Parquet object can produce a
plausible but incorrect result. The folder may contain:

files used by the selected table state;
files retained for an older state;
files left by a failed or abandoned write;
data whose rows are changed by separate delete metadata.

The object listing answers, "What exists at this location?" The table metadata
answers, "What belongs to this version or snapshot?" An investigation often
needs both answers.

Work from the selected state outward

Suppose an Iceberg snapshot references 118 data files and 2 delete files. The
storage prefix contains 126 Parquet objects.

Scanning all 126 objects does not prove that the snapshot is wrong. Some
objects may belong to an older snapshot or to no committed snapshot. Ignoring
the 2 delete files can also count rows that the table considers deleted.

Start with the reference the user selected:

Record the catalog, branch, tag, version, timestamp, or other table reference used by the query.
Resolve it to an exact Delta version or Iceberg snapshot ID.
Follow the metadata chain and construct the active data-file and delete-file sets.
Check whether each referenced object is accessible. If the metadata records a length or another identity that can be checked safely, compare that too.
Apply the format's delete semantics before comparing row counts.

Do not begin with the newest-looking metadata filename. In Iceberg, the catalog
or table reference chooses the current metadata. In Delta, the selected log
version and its preceding state determine the snapshot.

Missing and extra files mean different things

A referenced file that the reader cannot access makes the check incomplete.
That observation alone does not distinguish a missing object from a temporary
service or authorization failure.

In Apache Iceberg issue 4168, a user reported a data-file upload failure
followed by a manifest that still referenced the object. A later query failed
when it needed that file. This is an example of the failure shape, not proof
that every inaccessible reference has the same cause.

The safe result is incomplete. Treating an unreadable reference as zero rows
would turn missing evidence into a clean-looking count.

An unreferenced object needs a different interpretation. It might be retained
for an older snapshot, left by an unsuccessful write, or waiting for approved
maintenance. Its presence alone does not prove corruption, and deleting it
during an investigation could damage a state that another reader still needs.

Keep the first check read-only. Determine whether any committed state still
references the object before considering cleanup.

A successful query is one piece of evidence

A successful query tells you that an engine returned rows. Record the exact
table state separately before deciding what the result proves. The result does
not, by itself, establish that:

the query selected the state the user intended;
every metadata reference and object was present;
the reader understood every required table feature and delete type;
another engine resolved the same catalog reference and cached metadata.

When two results disagree, save the selected version or snapshot, active-file
counts, delete-file counts, and unsupported features before rerunning either
query. A rerun against a newer state can erase the comparison you needed.

A small evidence record

The record does not need every metadata field. It needs enough information for
another engineer to repeat the decision.

Field	Example
Question	Why do the catalog query and folder scan differ by 211,372 rows?
Selected state	Iceberg snapshot 711.
Metadata check	Snapshot and manifest chain resolved.
File check	118 referenced data files present; 8 additional objects not attributed.
Delete check	2 delete files apply to the selected snapshot.
Result	Incomplete until the folder scan applies the same active-file and delete rules.

Do not include credentials, signed URLs, private paths, SQL, schemas, customer
information, or raw provider errors in the record.

The two inputs are now specific: snapshot 711, interpreted through its data and
delete manifests, and a scan of 126 physical objects. Make the folder scan use
the same active-file and delete rules, then recount. That check can explain the
211,372-row difference instead of running two vague queries again.

References

One question for Delta and Iceberg users

When a table's state looks wrong, which step usually costs you the most time?

Identifying the exact version or snapshot
Following the metadata chain
Matching referenced files to object storage
Understanding delete files or deletion vectors
Reproducing the same result in another tool
Something else

A one-number comment is enough. Please do not post confidential data, file
paths, SQL, schemas, credentials, customer information, or screenshots.

If you prefer email, use the same one-question prompt on the
BigdataSight research page.

Disclosure: We build BigdataSight, a native Mac data workstation. We are
researching evidence-backed, local-first tools for data investigation,
reproducible verification, and workflows that are read-only by default. The
workflow described here is research, not a shipping capability or delivery
commitment.

Writing note: I developed and verified the technical argument in this article. AI tools assisted with English drafting and editing.

The pipeline passed. The data still needs proof.

Rui Wang — Wed, 29 Jul 2026 13:22:55 +0000

A pipeline job finishes without an error. The new table has 18% fewer rows.
Should you rerun the pipeline, stop the release, or trust the new output?

Before rerunning the job, preserve the two states you need to compare. A rerun
can create another state and make the original change harder to explain. The
first useful question is: which two states are we actually comparing?

Pin down the before and after

"Yesterday" and "latest" sound precise until a file is replaced or a table
advances to another snapshot.

Give both states durable identities. That might mean a file checksum, an object
version, a Delta table version, an Iceberg snapshot, a catalog reference, or a
pipeline run ID. Record the local time and timezone too. They help when logs and
object histories use different clocks.

Suppose a Delta table's row-count drop occurred between versions 842 and 849.
Those two numbers turn a vague report into a reproducible question. Without
them, another engineer may inspect version 850 and reach a different conclusion.

Test the smallest useful explanation

An unexpected nullable column and a lower row count point to different first
checks.

If a column became nullable, compare the two schemas before scanning rows.
Check whether the field changed type, moved inside a nested structure, or was
renamed. A CSV inference change and a Parquet schema change may look similar in
a grid, but they are different events.

If the row count changed, group counts by the partition or date most likely to
explain it. A full profile of every column may cost more than the original query
while hiding the useful difference.

Delta and Iceberg add another question: which logical table state did the
reader resolve? The newest objects in storage do not necessarily belong to the
selected state. Check the version or snapshot, the metadata chain, and the data
or delete files involved before blaming an individual file.

The aim is a smaller search area. A larger report can wait until the evidence
calls for one.

State what the check can prove

The same finding means something different when it comes from metadata, a
sample, or every relevant row.

Metadata can establish recorded schema, partition information, and available
statistics. It cannot prove a claim about every row. A sample helps discover a
pattern, but a missing value in the sample does not prove that the value is
absent. A full scan supports stronger row-level claims, provided it covered
every relevant partition and delete.

Record truncation, skipped objects, unsupported semantics, cancellation, and
fallback behavior. If one of them prevents a conclusion, call the result
incomplete. That is more useful than a clean-looking result with a hidden gap.

Leave evidence another engineer can challenge

A short record is enough when another engineer can understand the decision and
repeat the check.

A fictional record might look like this:

Field	Recorded value
Question	Why did the table lose 18% of its rows?
Inputs	Delta table versions 842 and 849.
Checks	Schema comparison and row counts grouped by day.
Scope	Metadata plus a full scan of the affected partition.
Outcome	Warning. The drop is isolated to July 25, 2026.
Limit	Delete semantics were not checked, so the cause remains unproven.

Keep credentials, signed URLs, private paths, secret-bearing SQL, customer data,
and private screenshots out of the record.

Before running the checks, define the finish line:

Pass means the stated rule held over the recorded scope.
Warning means the result needs review.
Failure means the evidence disproved the rule.
Incomplete means the available evidence cannot support a conclusion.

Leave enough evidence for another engineer to reproduce the result, disagree
with it, and run the investigation again.

One question for data engineers

Which part of this work usually costs you the most time?

Understanding an unexpected schema change
Finding why row counts or profiles changed
Understanding the real state of a Delta or Iceberg table
Comparing pipeline output before and after a change
Proving that a delivery or release is safe
Something else

A one-number comment is enough. Please do not post confidential data, file
paths, SQL, schemas, credentials, customer information, or screenshots.

If you prefer email, the same one-question prompt is available on the
BigdataSight research page.

Disclosure: I build BigdataSight, a native Mac data workstation. We are
researching evidence-backed tools for investigating changes in files and
lakehouse tables. The product direction described here remains research and is
not a shipping capability.

Writing note: I developed and verified the technical argument in this article. AI tools assisted with English drafting and editing.