SQL becomes a plan.
SELECT observation_id, station, temperature_c FROM data WHERE temperature_c >= 18 ORDER BY observation_id
Start with the artefact
Ask the file a question.
1190 bytes hold 18 observations. Begin to run this SQL in the browser, then follow the scan and result.
What DuckDB is
DuckDB is an analytical query engine that runs inside your process, including a browser tab. Give it SQL and a Parquet file: it chooses columns, applies filters, joins inputs and aggregates values into an answer. Its optimizer turns the question into a plan; physical operators carry out that plan over batches of values. Applications in Python, R and JavaScript use DuckDB to read files and return typed results without operating a separate database server.
About this fixture
Recorded plans use DuckDB 1.4.3 on macOS-26.6.2-arm64-arm-64bit. The adjustable diagrams compute row membership from the fixture. EXPLAIN ANALYZE counters belong to the pinned native run. Begin loads the local DuckDB runtime and runs the opening query. Edited SQL also executes in this browser; the operator tree preserves the recorded native plan.
From SQL to a plan.
The parser turns the SQL text into a structure. Binding resolves the named table and columns, checking their types. Optimization then moves projections and filters toward the scan. Physical planning chooses the operators that perform the work.
The opening query needs observation ID, station and temperature. Its scan applies the temperature predicate while projecting those columns. ORDER_BY then orders the survivors. Follow those same records through the adjustable diagrams below.
The actual operator tree.
This is DuckDB's recorded JSON EXPLAIN tree, with every node retained, including internal projections. Read children as inputs to their parent. Expand the details to inspect the original fields; unfamiliar operators remain visible.
DuckDB physical operatorsInspect operator fields
PROJECTION
{
"Projections": [
"__internal_decompress_integral_bigint(#0, 1)",
"#1",
"#2"
],
"Estimated Cardinality": "0"
}ORDER_BY
{
"Order By": "memory.main.\"data\".observation_id ASC"
}PROJECTION
{
"Projections": [
"__internal_compress_integral_utinyint(#0, 1)",
"#1",
"#2"
],
"Estimated Cardinality": "3"
}PARQUET_SCAN
{
"Function": "PARQUET_SCAN",
"Projections": [
"observation_id",
"station",
"temperature_c"
],
"Filters": "temperature_c>=18.0",
"Estimated Cardinality": "3"
}
Compare estimates with executed cardinalities.
The selected query was also executed with native DuckDB’s JSON EXPLAIN ANALYZE. The rows below retain operator counts and discard timing fields. The estimate expresses a planning assumption; actual output cardinality describes what that operator emitted in this run.
PROJECTIONEstimated output: 0
Actual output: 5 rows
Reported rows scanned by this operator: 0
ORDER_BYEstimated output: not reported
Actual output: 5 rows
Reported rows scanned by this operator: 0
PROJECTIONEstimated output: 3
Actual output: 5 rows
Reported rows scanned by this operator: 0
PARQUET_SCAN Estimated output: 3
Actual output: 5 rows
Reported rows scanned by this operator: 18
A projection usually preserves row count; an aggregate can turn eighteen inputs into two groups. A join can multiply rows when keys repeat. Compare these obligations with the selected tree instead of expecting every operator to have the same cardinality.
A parent emits five rows while its source examines eighteen. Read each counter beside its operator: output counts tell you what it emits; scan counts describe the input it examines.
Original EXPLAIN text
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_integ│
│ ral_bigint(#0, 1) │
│ #1 │
│ #2 │
│ │
│ ~0 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ memory.main."data" │
│ .observation_id ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_integra│
│ l_utinyint(#0, 1) │
│ #1 │
│ #2 │
│ │
│ ~3 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PARQUET_SCAN │
│ ──────────────────── │
│ Function: │
│ PARQUET_SCAN │
│ │
│ Projections: │
│ observation_id │
│ station │
│ temperature_c │
│ │
│ Filters: │
│ temperature_c>=18.0 │
│ │
│ ~3 rows │
└───────────────────────────┘
Recorded EXPLAIN ANALYZE — executed query
This command executed the query on macOS-26.6.2-arm64-arm-64bit, recorded at 2026-09-10T18:51:37.531372+00:00. Row counts and timings belong to that run. Compare their cardinalities with the result rows to see where estimates differ from execution.
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT observation_id, station, temperature_c FROM data WHERE temperature_c >= 18 ORDER BY observation_id
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 0.0010s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_integ│
│ ral_bigint(#0, 1) │
│ #1 │
│ #2 │
│ │
│ 5 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ memory.main."data" │
│ .observation_id ASC │
│ │
│ 5 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_integra│
│ l_utinyint(#0, 1) │
│ #1 │
│ #2 │
│ │
│ 5 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ TABLE_SCAN │
│ ──────────────────── │
│ Function: │
│ PARQUET_SCAN │
│ │
│ Projections: │
│ observation_id │
│ station │
│ temperature_c │
│ │
│ Filters: │
│ temperature_c>=18.0 │
│ │
│ Total Files Read: 1 │
│ │
│ 5 rows │
│ (0.00s) │
└───────────────────────────┘
A tilde marks an estimated row count. Estimates can differ from the executed result. EXPLAIN plans work; EXPLAIN ANALYZE performs it and reports what happened.
Follow the values
Eighteen values enter. Which five leave?
The question is temperature_c ≥ 18. Projection chooses columns; filtering chooses rows. The requested answer uses the other three columns. These are the actual values from the downloaded file, ordered by observation ID so that every position stays recognizable.
Inspect the aligned source columns
Selected input positions
[13, 14, 15, 16, 17]A selection can identify surviving positions without immediately copying every column. Follow each selected position back to the source array.
Output observation IDs
5 rows1415161718
Every projected column must follow the same selection. Keeping temperatures but forgetting to select station names would misalign the rows.
At 18 °C, positions 13–17 yield IDs 14–18. Positions start at zero; the observation ID is a data value. Changing the threshold here evaluates the verified source values in JavaScript. The live SQL tool below executes DuckDB itself.
The scan does two different jobs.
In the captured filter plan, PARQUET_SCAN lists three projected columns and the temperature predicate. Metadata can sometimes eliminate a row group before values are decoded; the row predicate determines which decoded values qualify. The scan may inspect many input rows before producing those five outputs.
Why vectors rather than a JavaScript loop per row?
A batch lets an operator apply the same operation to many typed values and pass the surviving positions onward. The complete fixture fits in this diagram. Null handling would additionally require validity information: this fixture has no null temperatures.
A vector is a logical column, not always a flat array.
The eighteen station values repeat just two strings. A flat representation stores a slot for every value. A dictionary representation can retain the child values once and use positions to address them. DuckDB also has constant and sequence vectors: the logical row count need not equal the number of stored payload values.
Station representation
Child values
0: North1: South
Logical slot → child position
0 →01 →12 →03 →14 →05 →16 →07 →18 →09 →110 →011 →112 →013 →114 →015 →116 →017 →1
The eighteen decoded station values stay identical. The dictionary example retains two strings and eighteen positions. Memory use also includes offsets, validity, pointer widths and allocation overhead.
Constant vector
SELECT 18 AS threshold FROM dataOne payload value, 18, can represent eighteen equal logical values. A consumer must respect the representation instead of reading eighteen consecutive payload slots.
Sequence vector
start = 1; increment = 1; count = 18The observation IDs in this fixture can be described by a sequence. The value at logical position i is 1 + i. The sequence view computes those IDs from a start and an increment.
These are execution representations. A Parquet dictionary on disk and a DuckDB dictionary vector in memory serve different layers; a reader may decode one into another. Operators can preserve indirection or flatten when they need contiguous values. The reader and operators determine which execution representation travels through the plan.
A second filter addresses the first filter’s result.
Filter by temperature, then by station. The second selection contains positions within the first selection—not original row positions. Following both levels of indirection is essential to keep columns aligned.
The null variant is shared with the nullable aggregate below. The original-file query and its recorded buffers remain available as the non-null reference.
Source temperature0:51:62:73:84:95:106:117:128:139:1410:1511:1612:1713:1814:1915:2016:2117:22
First selection[13, 14, 15, 16, 17]
Second selection[0, 2, 4]
Composed source positions[13, 15, 17]
Final observation IDs: 14, 16, 18. For each second-stage position j, look up firstSelection[j], then read that source position from every column.
Predict before changing the threshold
At 14 °C and South, the first selection starts at source position 9. The second selection is [0, 2, 4, 6, 8], producing source positions [9, 11, 13, 15, 17] and IDs [10, 12, 14, 16, 18]. Treating those second-stage positions as source positions would silently select different rows.
For a null temperature, temperature ≥ minimum is unknown. WHERE keeps only true, so it excludes that null slot from the first selection. Validity tells the predicate to treat that slot as unknown.
Change the question, keep the filtered observations
What if each observation needs a station description?
The filter-to-Arrow read above is complete. Now join its surviving observations to a lookup. This join can emit a different number of rows. The original filter query’s recorded Arrow answer stays available for comparison.
A join attaches values through a key.
The station name is the link between two inputs. The weather file has observations; a separate two-row relation maps North and South to descriptions. An inner join emits matches. This fixture has exactly one lookup row per station, so each surviving observation emits exactly one result. Duplicate lookup keys could multiply rows; unmatched keys would disappear.
The join query matches the station key against a two-row lookup. Its HASH_JOIN combines warm observations with Northern site or Southern site. Inspect both child inputs in the plan. The hash table helps the operator locate matching keys. Follow the build and probe below, then compare rewrite choices in the query-engine guide.
Turn the chosen order into operators
Two keys unlock five rows.
The query asks for warm observations and station descriptions. The scan and row filter leave IDs 14–18. The lookup supplies the description; it has just two keys. Advance through the build, then watch each weather row probe a key.
0 build keys · 0 emitted rows
Retained lookup
North → not built yet
South → not built yet
Current operation
No keys retained. Read the lookup first.
Rows emitted so far
No output yet.
Step through the fixture’s probe keys and see which lookup values each one reaches. The recorded DuckDB HASH_JOIN builds a lookup over the station descriptions. Each surviving weather row probes that retained state.
An inner join emits nothing for an unmatched key. Duplicate lookup keys can emit multiple rows for one probe; this fixture has unique station keys. Count matches for each probe to determine the output size.
Warm observation
Station lookup contents
Equality matches for the selected observation
ID 16 · South → Southern site
5 filtered input rows produce 5 joined rows. This is the same ≥ 18 °C filter used above.
At the original 18 °C threshold, North has two warm observations; South has three. Duplicating South’s lookup entry contributes six South matches plus two North matches. Removing it leaves only the two North rows. Every matching lookup entry contributes a joined row, including duplicate keys.
Every threshold and lookup variant was executed with pinned native DuckDB. These controls select captured answers. The opening workbench runs editable SQL in the browser.
Change the question
What if we need one average per station?
The same 18 observations now feed a sink that retains state. The consumed prefix is shared by the next two diagrams. This query has no temperature filter; it includes the entire source.
Retain just enough state
An average is a sum and a count in progress.
Now ask for the average temperature per station across all 18 observations. Unlike the filter, a group aggregate must keep information from earlier inputs. Follow ID order as a teaching sequence; DuckDB can process batches in another order.
0 / 18 consumed
No input consumed yet.
Lines route each observation to its station. Consumed inputs update that station’s sum and count; no input needs to be retained as a complete row. The highlighted identity is shared with the earlier selection and join diagrams. North
- Count
- 0
- Temperature sum
- 0
- Average so far
- undefined
South
- Count
- 0
- Temperature sum
- 0
- Average so far
- undefined
These averages are provisional. Later rows can change both numerator and denominator.
For this non-null fixture, count and sum explain the average. In a nullable column, AVG(column) excludes null values while COUNT(*) still counts their rows. Combine partial averages using their counts as weights, or combine their sums and counts directly.
The recorded plan uses HASH_GROUP_BY with count_star() and avg(#1). Follow the sum and count to see which state each input updates. Run the complete aggregate in DuckDB →
All 27 threshold choices and 19 input prefixes are checked against pinned native DuckDB results. Download those expected results.
Batches can finish while an aggregate is still incomplete.
Now ask for the average across the selected fixture variant. A source produces a batch, intermediate operators process it, and an aggregate sink retains state. The aggregate finalizes only after its input ends. Change the batch size: the boundaries move, but the final mathematical answer must stay the same.
123456789101112131415161718
Sink state after 0 rows
COUNT(*) = 0
COUNT(temperature) = 0
SUM = NULL
AVG so far = NULL
Can the final average be emitted?
No. An unseen row can still change the sum and denominator.
The sink can update state per batch. A downstream consumer of the final scalar waits for completion. That dependency creates a pipeline boundary; the sink can retain just the aggregate state.
Combine partial state, not partial averages
Illustrative task A, first four rows:
sum NULL, valid count 0
Task B, remaining consumed rows:
sum NULL, valid count 0
Combined average = (0 + 0) ÷ (0 + 0) = NULL.
With all eighteen non-null rows, the partial averages are 6.5 and 15.5. Their simple average is 11; the correct weighted result is 243 ÷ 18 = 13.5. With IDs 2 and 8 null, the denominator is sixteen and the correct answer is 225 ÷ 16 = 14.0625. Native DuckDB checks every prefix in both variants.
The controls divide fixture rows into small batches so you can inspect each update. DuckDB’s execution format normally uses vectors of up to 2,048 tuples. Parallel execution can maintain local sink state and combine it; floating-point evaluation order can affect rounding on less exact inputs than these small integers.
Change one assumption
When can storage eliminate work before decoding?
The eighteen-row file is too small to demonstrate this. Hold the predicate logic fixed and compare two larger files containing the same values in different physical orders; then return to the recorded engine evidence.
The same values can produce very different pruning opportunities.
Eighteen rows are too small to expose this effect. This second, reproducible fixture contains IDs and temperatures 1–16,384 in eight actual Parquet row groups of 2,048 rows. Both files contain exactly the same records. Only physical order changes.
Physical layout
Temperature ≥
Row group 0: skipmin 1 · max 2048 · 16,459 temperature-column bytes
Row group 1: skipmin 2049 · max 4096 · 16,459 temperature-column bytes
Row group 2: skipmin 4097 · max 6144 · 16,459 temperature-column bytes
Row group 3: skipmin 6145 · max 8192 · 16,459 temperature-column bytes
Row group 4: skipmin 8193 · max 10240 · 16,459 temperature-column bytes
Row group 5: skipmin 10241 · max 12288 · 16,459 temperature-column bytes
Row group 6: candidatemin 12289 · max 14336 · 16,459 temperature-column bytes
Row group 7: candidatemin 14337 · max 16384 · 16,459 temperature-column bytes
2 candidate row groups; 4,096 candidate rows; 4,096 actual matching rows.
At 12,289, ordered data can exclude six groups. Interleaving spreads both low and high values through every group, so all eight remain candidates. Min/max bounds an interval. The predicate still evaluates values inside that interval.
Bounds and encoded column sizes come from actual PyArrow-written files. Native DuckDB verifies the same result counts for both layouts. Candidate counts show which groups these bounds retain. Other required columns, metadata, caching and read coalescing affect actual I/O.
This connects storage to execution: projection chooses columns, statistics exclude impossible groups, decoding produces vectors, selections keep qualifying positions, and a sink computes the answer. A smaller result alone says nothing about how much work those stages performed.
Native results and row-group bounds · DuckDB execution format
Batches carry the result.
DuckDB works on groups of values rather than asking JavaScript to loop over each source row. In the browser, its result crosses the worker boundary as Arrow record batches. Arrow supplies typed columns and validity information for nulls, keeping the batch’s columns aligned.
Our SQL tool converts values to text inside DuckDB before that handoff, keeping BIGINT, decimal and timestamp precision intact. SQL types remain visible above the values. The preview renders up to 1,000 returned rows. The query plan determines the input work.
Here is a native DuckDB Arrow export of the rows selected by the shared ≥ 18 °C filter after casting numbers to VARCHAR, as the explorer does for safe display. The arrays below use Arrow UTF-8 strings: an offset buffer delimits each value inside a byte buffer. This capture follows the interchange layout at the result boundary.
id
Offsets: [0, 2, 4, 6, 8, 10]
Slot 2 reads bytes [4, 6) in this column’s value buffer.
31 36Validity: all valid; bitmap omitted. Decoded value: 16
station
Offsets: [0, 5, 10, 15, 20, 25]
Slot 2 reads bytes [10, 15) in this column’s value buffer.
53 6f 75 74 68Validity: all valid; bitmap omitted. Decoded value: South
temperature
Offsets: [0, 4, 8, 12, 16, 20]
Slot 2 reads bytes [8, 12) in this column’s value buffer.
32 30 2e 30Validity: all valid; bitmap omitted. Decoded value: 20.0
Every column uses the same output slot but its own byte offsets. “14” occupies two UTF-8 bytes; “South” occupies five. Offsets count bytes, not characters. In the null variant, slot 2 of station is invalid. The validity bit distinguishes that slot from a valid empty string.
At the original 18 °C threshold, output slot 2 belongs to source ID 16, whose source position was 15. Filtering changes positions. Converting a number to text changes its representation. Neither operation changes its identity. The fixture combines native chunks into one stable display.
A null has a slot, but no value.
Select a position in the actual Arrow Int64 array [5, null, 7]. Its validity byte is 05: bits are read from the least significant end. Array position and value are separate concepts.
Position 0 · validity bit 0 = 1
Eight bytes at the selected Int64 slot Positions above count bytes.Read eight little-endian bytes → 5
The fixed-width value buffer reserves eight bytes even for a null. The validity buffer gives those bytes meaning. This is why reading only the values buffer silently corrupts nullable data. Strings add offsets to this validity-and-values pattern.
Choose a real query.
Select warm observations
SELECT observation_id, station, temperature_c FROM data WHERE temperature_c >= 18 ORDER BY observation_id
5 recorded result rows. Values below are exact database text.
The explorer opens the matching file and SQL. Press Run SQL to execute it locally. A query ID travels in the link; edits you make to SQL stay out of the URL.
Reproduce the evidence.
Download all SQL, results and raw plans. The synthetic file's SHA-256 is 12e54f3643325c44da99b5508fc79bc83e92ee2a0da78ddf603d6d090064a4b5.
Save reproduce.py, queries.json and weather.parquet in one folder, then run uv run reproduce.py. It uses pinned DuckDB 1.4.3, verifies the file hash and results, and prints fresh plans and analyzed runs.
Native DuckDB and DuckDB-Wasm share an engine implementation; matching them checks the browser integration. Separate explicit expectations check the 18 rows, five warm observations and station averages.
Read the official EXPLAIN guide, Parquet documentation and Wasm query API.
Your turn.
Change the question in the SQL workbench. Run it on your own Parquet file, inspect the result, and ask DuckDB for its plan.