DEV Community: Rui Wang

MCP or CLI? Why I Use Both for AI Data Analysis

Rui Wang — Mon, 19 Oct 2026 08:11:02 +0000

When I explore data with an AI assistant, I want to ask follow-up questions, check a calculation, and try another angle. Once I have a useful query, I want to save it and run it again on the next export. MCP and the CLI fit these parts of my workflow well.

I build BigdataSight, a native Mac data workspace with both interfaces. Here is an example of how they work together. The sample execution records below were created for demonstration.

A worked example

Suppose we have execution records from several services and want to know which service had the most failures yesterday. Save these records as runs.csv:

run_id,service,status,finished_at
r1,billing,failed,2026-10-18 09:00:00
r2,billing,succeeded,2026-10-18 10:00:00
r3,billing,failed,2026-10-18 11:00:00
r4,search,succeeded,2026-10-18 12:00:00
r5,search,failed,2026-10-18 13:00:00
r6,ingest,succeeded,2026-10-18 14:00:00
r7,billing,cancelled,2026-10-18 15:00:00
r8,billing,failed,2026-10-19 00:00:00

For this example, yesterday is October 18, 2026. We count only succeeded and failed attempts, treat both the reporting window and timestamps as UTC, and return the failure count and rate, ordered by failure count. Each row is one attempt, including retries.

Explore through MCP

MCP lets an AI client discover tools and contextual resources. A connected assistant can inspect the file's columns and preview a few rows before writing a query. I would start with this prompt:

Use BigdataSight to inspect the runs file I added. Show its schema and a short preview.

For October 18, 2026 in UTC, use finished_at to count succeeded and failed attempts by service. Return finished attempts, failed attempts, and failure percentage, ordered by failure count.

Show the SQL before running it. Ask if the timestamp type or status values need clarification.

Checking the schema first helps catch details such as a timestamp stored as text or a status field that uses error for failures. The assistant can then adjust the query to the actual data.

With finished_at parsed as a UTC timestamp, the query is:

SELECT
    service,
    COUNT(*) AS finished_runs,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_runs,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
        / COUNT(*),
        2
    ) AS failure_pct
FROM runs
WHERE finished_at >= TIMESTAMP '2026-10-18 00:00:00'
  AND finished_at < TIMESTAMP '2026-10-19 00:00:00'
  AND status IN ('succeeded', 'failed')
GROUP BY service
ORDER BY failed_runs DESC, service;

Use the actual table name from your SQL workbench in place of runs, and adjust timestamp handling to match the column's type. The example returns:

service	finished_runs	failed_runs	failure_pct
billing	3	2	66.67
search	2	1	50.00
ingest	1	0	0.00

Billing has two failures out of three completed attempts. Its cancelled attempt and the failure at midnight on October 19 fall outside our definition. The SQL makes the date range and denominator easy to check.

Save the query as a command

In BigdataSight 1.2, open the source and put the reviewed SQL in the editor. Select the source under Settings → External Access and choose Structure and data access, then use Run → Copy as Command…. The copy window lets you select a shell, result-row limit, and timeout.

Install bds through Command Line → Set Up bds…, select sh in the copy window, and save the copied command as daily-failures.sh:

sh ./daily-failures.sh > daily-failures-result.json

The generated command includes the source selection and sends the query to bds query --request -. The output file contains the JSON query response, subject to the chosen result-row limit. Check the command's exit status before using the result. To keep earlier results, choose a new output filename each time: > overwrites an existing file.

Each saved command uses a specific date range and data source. For a reusable script, ask the AI assistant to extract those values into parameters. Review the script and try it on a known date before adding it to your daily workflow.

Choose the interface that fits the work

I like MCP when I am exploring data in a connected AI client: source discovery, query results, and follow-up questions stay in one conversation. A saved CLI command is convenient when I want to repeat a calculation or include it in a script.

An AI coding assistant with terminal access can also inspect CLI help and run queries directly. And a connected MCP client can repeat a known query. Both support exploration and execution; the surrounding workflow guides my choice. MCP requires client support for the server's tools, while scripts need to handle shell quoting, exit statuses, and JSON output.

Start a task in one place, retrieve it in another

For a longer query or export, BigdataSight 1.2 Pro supports independent tasks. Ask the AI client to submit a supported operation as a task and keep the returned task ID. You can close that MCP connection and check progress later from the terminal.

Replace TASK_ID_FROM_RECEIPT with the returned ID:

printf '%s\n' '{"version":1,"jobID":"TASK_ID_FROM_RECEIPT"}' |
  bds jobs get --request -

Once the task completes, retrieve its result:

printf '%s\n' '{"version":1,"jobID":"TASK_ID_FROM_RECEIPT"}' |
  bds jobs result --request -

To check this handoff, I supplied the same eight records directly in SQL, submitted the query through MCP, and closed the connection. CLI and a new MCP session both retrieved the same three summary rows.

Running the saved query calculates a fresh result from the selected source. Retrieving a completed task gives you the result captured by that run. If the source changes, you can keep the earlier result for comparison.

Keep the Mac awake while a task runs. Tasks expire 24 hours after submission, and retrieval requires the relevant source access and Pro entitlement. Save results you want to keep before they expire.

Using this workflow

For a calculation I plan to reuse, I keep the SQL, source selection, dates, timezone, status definitions, and result-row limit alongside the output. That gives me what I need to review or adjust the next run.

Queries run on the Mac. Schemas, previews, and results returned to an AI client may be sent to its model provider; check the client's data settings when choosing what to share.

You can try local CSV and Parquet queries with BigdataSight's free Viewer. Pro adds more formats, remote sources, comparisons, broader exports, and independent tasks. The steps here use BigdataSight 1.2. See the setup guide to get started.

Where would a saved command help most in your AI data analysis workflow?

Disclosure: We build BigdataSight, a native Mac data workstation.

AI-assistance disclosure: I used an AI coding assistant to help research and edit this article. I reviewed the final argument and the linked specifications before publishing.

How AI can investigate a 12 GB export without reading it into a prompt

Rui Wang — Mon, 05 Oct 2026 04:27:12 +0000

Imagine asking an AI assistant for the average order value in a 12 GB sales export. It finds an amount column and suggests AVG(line_amount). The query runs. The number looks reasonable. It answers the wrong business question.

The file has one row per order line, not one row per order. The difficult part was deciding what a row meant before choosing the calculation.

This is a useful place to start when connecting AI to local data. A model can help formulate questions and write queries. A data engine can execute those queries over millions of rows. The engineer supplies the business definition that neither the column names nor the SQL syntax can settle.

The example below uses four invented order lines to explain that division of work. The 12 GB file is a hypothetical setting, not a performance benchmark.

Find the grain before choosing an aggregate

Suppose the export contains these lines:

order_id	line_amount
A	100.00
B	30.00
B	40.00
B	30.00

Both orders total 100.00. Average order value is therefore 100.00. But the average line amount is (100 + 30 + 40 + 30) / 4 = 50.00.

Giving the assistant more rows would not fix that calculation. It needs to know the grain: what entity one row represents. A column named amount might hold a line amount, a repeated order total, or a payment amount. Each requires a different query.

For this example, the engineer defines the metric as follows: sum the sales lines belonging to each order, then average those order totals. Use USD orders placed in October 2026, with timestamps interpreted in UTC. Assume each order has one currency and the export contains sales lines; refunds are recorded separately. These choices make the calculation concrete.

Schema inspection supplies the available fields and types. A short preview helps distinguish a line amount from a repeated order total. The next step is to express the chosen grain in SQL:

WITH per_order AS (
    SELECT
        order_id,
        COUNT(*) AS line_count,
        SUM(line_amount) AS order_amount
    FROM orders
    WHERE ordered_at >= TIMESTAMP '2026-10-01 00:00:00'
      AND ordered_at <  TIMESTAMP '2026-11-01 00:00:00'
      AND currency = 'USD'
    GROUP BY order_id
)
SELECT
    COUNT(*) AS order_count,
    SUM(line_count) AS line_count,
    SUM(order_amount) AS sales_amount,
    AVG(order_amount) AS average_order_value
FROM per_order;

Here, orders stands for a relation opened in a SQL workbench. The query assumes ordered_at is a UTC timestamp without a timezone and line_amount is a non-null decimal sales amount. With a timezone-aware timestamp, use literals or parameters appropriate to the engine and the business timezone.

The inner aggregation changes the grain from line to order. The outer one computes the business metric at that new grain. On the four-line example, the result is two orders, four lines, 200.00 in sales, and an average order value of 100.00.

Keeping both counts in the result makes the calculation easier to discuss. If the assistant suggests averaging sales_amount / line_count, the engineer can point to the denominator immediately. The useful conversation is about orders and lines, rather than a long pasted table.

One result row can still require a large scan

That query returns one row. Computing it may require processing every qualifying sales line. Result size and execution cost are different dimensions.

Adding LIMIT 1 to an aggregate does not generally reduce the rows fed into it. The aggregate already produces one row. Moving a limit into an input subquery reduces the input, but also changes the average being calculated.

For large local files, the better question is which columns and file regions the engine must read. The SQL above references four fields: order_id, line_amount, ordered_at, and currency. An export might contain 180 fields, including descriptions, addresses, and serialized attributes that this query never uses.

Parquet stores data in column chunks within row groups. Its footer locates those chunks, so a reader can find the requested columns without decoding all the others. The Parquet file layout explains how readers start from file metadata to locate column data.

This is projection pushdown: carrying the query's column selection down to the file reader. Four fields instead of 180 can materially reduce the work, although the saving depends on their encoded sizes. A large text field and a small integer field do not cost the same amount to read.

The date filter can reduce the work further. Suppose the file has these row-group statistics for ordered_at:

Row group	Minimum date	Maximum date	October query
0	September 1	September 15	Skip
1	September 16	October 8	Read relevant columns and filter rows
2	November 1	November 14	Skip

With usable min/max statistics, groups 0 and 2 cannot contain an October order. Group 1 overlaps the requested interval, so its relevant data still needs to be examined. Readers such as DuckDB use this form of filter pushdown.

Now shuffle the same dates across every row group. Each group's minimum may be September 1 and maximum November 14. The filter has not changed, but the reader loses those opportunities to skip groups.

That is why file layout matters even when the assistant writes good SQL. Clustering rows by a frequently filtered field can make its statistics more selective. Smaller row groups offer finer skipping, while very small groups add metadata and scheduling overhead. DuckDB's file-format performance guidance discusses that tradeoff. There is no single row-group size that makes every workload fast.

CSV has a different cost model: readers commonly infer its types from text, and a plain CSV file has no Parquet-style row-group statistics for a date filter to skip unrelated regions. An interface that looks identical can therefore do quite different amounts of reading underneath.

A query result is a useful compression of the question

A raw preview preserves individual values. An aggregation preserves a relationship chosen by the question: sales per order, errors per service, bytes per partition, or missing values per column.

Consider what happens after the first average is computed. The engineer wants to know whether orders with many lines have a different average value. Reuse the per_order calculation and replace its final SELECT with:

SELECT
    line_count,
    COUNT(*) AS order_count,
    AVG(order_amount) AS average_order_value
FROM per_order
GROUP BY line_count
ORDER BY line_count;

On the teaching example, the output has just two groups:

Lines per order	Orders	Average order value
1	1	100.00
3	1	100.00

The assistant can now discuss a specific relationship without reading all four lines, let alone the full export. On real data, the engineer might next ask for a comparison by sales channel or a breakdown of unusually large orders. The result suggests the next calculation.

This compression is task-dependent. Grouping by a low-cardinality status usually produces a short result. Grouping by a nearly unique order_id may produce millions of rows. GROUP BY alone does not make an answer compact; the chosen dimension does.

It also matters how partial results are combined. Suppose two channels report average order values of 100 and 20, based on 10 and 90 orders respectively. The overall average is (100 × 10 + 20 × 90) / 100 = 28, not 60. Passing the counts alongside the averages lets the assistant combine them correctly. Passing only two averages throws away the weights.

For exact recombination, prefer carrying the underlying sum and count instead of rounded averages. The same idea appears in distributed aggregation:
SUM and COUNT can be combined across partitions, while AVG needs both.
Choosing what to return is part of the computation, not just formatting a response for the model.

Give the assistant the definition it cannot infer

“Analyze this file” leaves both the metric and the reading strategy open.
A more productive starting request is concrete:

This export has one row per sales line. I need average order value
for USD orders placed in October, using UTC dates. Sum line_amount
per order_id before averaging. Start by inspecting the schema and
checking that these fields have the expected meaning. Suggest the
SQL, including order and line counts, for me to review and run.

The assistant can map that request to the actual column names, draft the query, explain the grouping, and suggest a useful next breakdown. The engineer decides what counts as a sale and whether the proposed calculation matches how the business measures it. The local engine does the arithmetic.

In BigdataSight Direct 1.1, the Agent connection lets an AI client inspect local file structure and, when enabled, short value previews. We supports Parquet, CSV, TSV, JSON/NDJSON/JSONL, SQLite, Excel, and Arrow/Feather, including table, view, or worksheet selection where needed.

For the Parquet/CSV example above, inspect the file through Agent, review and run the proposed SQL in the app's SQL workbench, then bring the small query result back into the conversation. The Agent inspection step and the SQL workbench step are separate parts of that workflow. Connection instructions are in the Agent setup guide; channel availability is recorded in the release notes.

The next time an assistant proposes a number, ask it to show the grouping and the denominator. That short exchange often contributes more than another thousand rows of context.

Disclosure: We build BigdataSight, a native Mac data workstation.

AI-assistance disclosure: I used an AI coding assistant to help research and edit this article. I reviewed the final argument and the linked specifications before publishing.

The AI agent checked the table. Did every result describe the same state?

Rui Wang — Tue, 25 Aug 2026 07:29:28 +0000

Why agent data tools need exact table identity, bounded coverage, and honest incomplete results beyond a successful tool call.

An AI agent reports:

Table state: Delta version 1183
Schema check: Pass
Active files: 247 present
Release rule: Pass

The tool calls completed without an error. Can another agent rely on this result?

Suppose the table advanced from version 1182 to 1183 while the checks were running. The timeline call observed version 1183. The schema call used metadata cached from version 1182. The file check resolved the current table but did not return a version at all.

Every line may be accurate on its own. Together, they describe no real table state.

The values in this example are fictional. The failure shape is not. A mutable dataset can change between calls, retries, cache reads, and handoffs between agents. Once several valid observations are combined, the result can become a claim that none of the tools actually proved.

Transport is not evidence semantics

The July 2026 release of the Model Context Protocol
made its core stateless and each request self-describing. MCP gives clients and servers a common way to discover and call tools. The Agent2Agent protocol
defines communication between independent agent systems.

These protocols solve real connection problems. They do not define the domain meaning of every tool result.

An MCP tool can return a valid JSON object called schema_check. An A2A agent can send that object to another agent. Neither fact tells the receiver whether the schema and file checks observed the same table state, whether a read limit stopped the inspection, or whether an unsupported delete feature was ignored.

Protocol success answers, "Did the message arrive and satisfy the wire contract?" Data evidence answers, "What can this result prove?"

Data formats already give us stable states

The data layer has useful identities that an evidence contract can reuse.

The Delta Transaction Log Protocol defines a serial history of atomic table versions. A snapshot at a version contains the table metadata and the set of files that belong to that state.

The Apache Iceberg specification
defines snapshots, manifest lists, manifests, data files, and delete files. A snapshot identifies the table state used to plan a scan.

Those identities are stronger than latest, a wall-clock time, or an object storage prefix. They let an agent say which state it observed even after the table changes again.

Other data sources have equivalent anchors: an object version, a content digest, a database transaction identifier, or a catalog reference resolved to an immutable state. A useful agent contract should preserve the native identity instead of inventing a weaker one.

The surrounding standards cover different parts

OpenLineage models jobs, runs, and datasets. Its dataset facets can carry a dataset version, while its data quality assertions facet records test outcomes and optional expected and observed values.

W3C PROV provides a general model for entities, activities, agents, and derivations. It is a foundation for describing where an artifact came from and which activity produced it.

The OpenTelemetry semantic conventions for GenAI agents are developing shared names for agent invocations, plans, workflows, and tool execution spans. That work helps operators observe how an agent ran.

Newer proposals are also exploring cryptographically verifiable agent
evidence. For example,
Proof of Insight describes a content-addressed, signed graph for observation, deterministic computation, model reasoning, and attestation. Its current specification labels itself a working draft rather than a published standard.

These specifications overlap, but they do not require the same data-evidence boundary.

In the specifications above, I have not found a widely adopted profile that requires a data-check result to bind all of the following:

Part	Question it answers
Subject and state	Which exact dataset state was observed?
Question or rule	What was the check trying to establish?
Scope and coverage	Which metadata, files, partitions, samples, or rows were read?
Capability	Which table features and delete semantics did the reader understand?
Outcome	Did the rule pass, warn, fail, or remain incomplete?
Limitations and provenance	What stopped the check, and how can another system reproduce or challenge it?

OpenLineage can represent several of these facts. PROV can express their provenance. MCP or A2A can carry them. The missing piece is a small semantic profile that says which facts are required for an agent-facing data verification result and how they remain bound to one observation.

A profile is a better starting point than a new transport

Creating another RPC or messaging protocol would duplicate work. The same evidence should be able to travel through MCP, A2A, a command-line process, an event stream, or a saved file.

The standardizable part is the meaning of the result.

An illustrative envelope might express this shape:

subject:
  format: delta
  state: 1183
question: schema_compatibility
coverage:
  metadata: complete
  rows: not_read
outcome: incomplete
limitations:
  - active_file_check_observed_a_different_state

This is not a proposed wire schema. It only shows the separation between table identity, the question, observed coverage, outcome, and limitation.

A practical profile would need stronger rules:

A mutable source must resolve to an exact state, or the result remains incomplete.
Observations from incompatible states cannot be merged under one verdict.
Transport completion and evidence outcome use separate status fields.
Successful sections remain available when another section is incomplete.
Unsupported table features and exhausted read limits appear in the result.
The shareable form excludes credentials, signed URLs, private locations, raw rows, and hidden model reasoning.
A policy decision remains separate from the observations it evaluates.

The seventh rule matters when an agent participates in a release workflow. A schema comparison can prove that one field changed. It cannot decide whether the change is allowed until it receives an explicit compatibility or release rule.

Bounded reads need an honest result

Agents make repeated work cheap to request. They do not make remote metadata, object listings, or full scans free.

A data tool needs budgets for time, bytes, objects, metadata entries, rows, and retries. When a budget ends the operation, the result should preserve what was observed and mark the unsupported conclusion as Incomplete.

Retrying silently can make the evidence worse. The next request may observe a new table state and fill the missing section with facts from a different snapshot. A clean-looking report then hides the boundary that mattered most.

An incomplete result can still be useful:

State identity: Verified at Iceberg snapshot 711
Schema check: Pass over snapshot metadata
Delete check: Incomplete after the manifest-read limit
Row impact: Not checked

Another agent can reuse the state and schema evidence, request a larger delete budget with approval, or send the result to a person. It does not need to discard the whole operation or pretend the missing premise passed.

A standard should earn its name

Publishing a JSON Schema is easy. Establishing a useful standard requires independent use.

A credible path would start with one narrow profile for read-only observations of versioned analytical data. It would include mappings to MCP tool results, A2A artifacts, OpenLineage facets, and PROV records. A public conformance set would cover state changes during inspection, unsupported delete semantics, bounded reads, redaction, and incompatible evidence binding.

The profile would become more convincing when two unrelated implementations can exchange the same result and reject the same invalid combinations. Open governance, versioning rules, and a compatibility policy should follow before anyone calls it an industry standard.

That suggests a modest first step: publish a data-evidence profile draft, its mappings, and failure cases. Ask data tool builders, lineage projects, and agent framework authors where it conflicts with their existing models. Change the draft before giving it a brand.

A BigdataSight development note

In our post-1.0 research, we are exploring how local agents can request bounded, read-only checks of Delta and Iceberg tables and receive compact evidence without source locations, credentials, schema bodies, or row values.

The recurring constraint is that observations must stay attached to the state that produced them. Speed matters only when the observations describe the same state. Interoperability matters only when the next agent can see their coverage and limitations.

We are treating MCP and other transports as adapters around stable data semantics. Product truth stays in the data contract. The broader idea of an open data-evidence profile is research. It is not a shipping BigdataSight 1.0 capability, a published specification, or a delivery commitment.

One question for data and agent engineers

If an AI agent returns a data verification result, which missing fact makes the result hardest to trust?

The exact table version, snapshot, or object identity
The metadata, files, partitions, samples, or rows it covered
Unsupported features or delete semantics
The expectation or release rule used for the verdict
A reproducible provenance or evidence reference
Something else

A one-number comment is enough, and important to us.😊

Disclosure: We build BigdataSight, a native Mac data workstation. This article describes a post-1.0 research direction, not a shipping 1.0 capability, public protocol, or release commitment.

AI-assistance disclosure: I used an AI coding assistant to help research and edit this article. I reviewed the final argument and the linked specifications before publishing.

The report says verified. Is the change safe to release?

Rui Wang — Wed, 12 Aug 2026 11:30:26 +0000

How to keep table identity, evidence coverage, and release intent separate in a data investigation report.

A schema-change report contains four lines:

Previous state: Delta version 41
Current state: Delta version 42
Schema changes: Verified
Fields added: 1

The feature request expected one new nullable double field named score.
Can version 42 go to production?

Not from those four lines.

The report established an observed change between two table states. It did not
establish that the added field is score, that its type is double, or that
the change matches the feature request. Verified describes the evidence
check. It is not a synonym for safe to release.

The numbers in this article are fictional, but this gap is common whenever a
data report mixes observation and approval.

Two questions, two different answers

A data investigation usually starts with a factual question:

What changed between the two states?

A release review asks a second question:

Did the observed change match what we intended and what consumers allow?

The first answer comes from evidence. The second also needs an expectation or
rule.

Suppose a metadata comparison confirms that version 42 contains one more field
than version 41. That is useful. It narrows the investigation. It still cannot
approve the release because several different changes produce the same count:

score was added as nullable double;
score was added as nullable string;
an unrelated field was added;
score was added, but an existing field also changed type.

All four cases can display Fields added: 1. Only the first matches the stated
request.

What a useful report must preserve

The report needs enough context to stop a reader from silently strengthening
its conclusion.

The exact before and after

“Previous” and “current” are unstable labels. Record Delta versions, Iceberg
snapshot IDs, object versions, or another durable identity.

If the table advances to version 43 five minutes later, another engineer should
still be able to inspect the 41-to-42 edge. Otherwise, the rerun answers a new
question while looking like a reproduction of the old one.

What was actually read

Metadata can support schema, snapshot, manifest, and recorded file-count
claims. It cannot prove a claim about every row.

A bounded sample can reveal a suspicious pattern. It cannot prove that the
pattern is absent outside the sample. A full scan supports stronger row-level
claims only when it covered every relevant partition and applied the table's
delete semantics.

Write the coverage beside the result. Do not hide it in a log or tooltip.

Independent results

One missing check should not erase evidence that was successfully collected.
It should also not disappear behind one green report status.

For example:

Check	Result	Meaning
State identity	Verified	Versions 41 and 42 were resolved.
Schema summary	Verified	One field was added.
Field detail	Incomplete	The shareable report does not include the field path or type.
Row impact	Not checked	No table rows were read.

This report is more useful than either “passed” or “failed.” It tells the next
reviewer what can be reused and what still needs work.

The limitation and the next check

Incomplete is not an error message. It is a result with a missing premise.

In this example, the next check is small: compare the added field's path, type,
and nullability with the feature request. If a local detailed review observes
score as nullable string, the release rule fails. If the detail is not
available, the decision remains incomplete. Neither case should become a pass.

Why this matters to automation

A person may notice that Verified and Fields added: 1 do not answer the
release question. An automated step can make a stronger assumption because the
word looks conclusive.

Giving the same report to an AI agent does not repair missing evidence. The
machine-readable version must preserve the same state identities, coverage,
independent results, and limitations that a person sees. It should not turn an
absent field-level check into approval.

This does not require a giant document. It requires stable meaning. A compact
structured report can say:

which two states were compared;
which observations completed;
what each observation covered;
what remains incomplete;
which rule, if any, produced a decision.

The agent can then combine the report with project context it already has. If
the report contains evidence but no release rule, the agent should describe
the evidence and leave the release decision open.

Five questions before trusting a report

Before accepting a green status, ask:

Which exact before-and-after states were compared?
What metadata, files, samples, or rows were actually read?
Does Verified describe an observation or a decision against an explicit rule?
What missing evidence would make the result incomplete?
Can another person repeat the same check after the table changes again?

If the report cannot answer one of them, narrow the claim. “One field was added
between versions 41 and 42” may be fully supported. “Version 42 is safe to
release” is a different claim.

A BigdataSight development note

In a post-1.0 development build, BigdataSight can generate a read-only
Investigation Report for an opened remote Delta or Iceberg table. The report
brings current-to-previous state checks into one view, keeps section results
independent, and can copy a bounded report without source locations,
credentials, schema bodies, or row values.

The user starts the report explicitly. A completed report does not certify that
the table is correct, compatible, or safe to release. When a bounded check
cannot establish a result, the section remains Incomplete.

This work has passed automated and hands-on development acceptance. It is not
part of the current App Store 1.0 release, and this note does not promise a
release date.

Our broader direction is evidence-backed, local-first data investigation and
reproducible verification, read-only by default. The useful outcome is not a
larger green badge. It is a report whose conclusion cannot outrun its evidence.

One question for data engineers

When you review a data change, which detail is most often missing from the
report?

The exact before-and-after states
What data or metadata was checked
The expected change or release rule
The reason a result is incomplete
Reproduction steps
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 one-question prompt on the
BigdataSight research page.

Disclosure: We build BigdataSight, a native Mac data workstation. This article describes a post-1.0 development artifact and its design boundary, not a shipping 1.0 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 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.