The query succeeded. Which table state did it read?

#dataengineering #iceberg #lakehouse

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.