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

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.