A missing list, an empty list and a list containing null are three different records.
One pupil has no recorded score list. Another has a recorded list with no scores. A third has a list containing one missing score. Those records can look identical after a careless export, but they do not mean the same thing. Parquet can preserve the distinctions by storing structural information alongside the present values.
This guide works through a small optional list of optional integers. You will reconstruct five rows from definition and repetition levels, count the different units correctly, and build a round-trip test that catches a flattened or shifted result.
The numbers below are original teaching examples. The exact level values belong to the explicitly stated schema; change the schema and you must recalculate the levels.
Information subject guide · Learning that transfers into life · How X Works hub
Choose your reading route
Route 1 · Chapters 1–3: Build the structure
Name the three kinds of absence
Name the list states and work the level sequence.
Route 2 · Chapters 4–6: Reconstruct the records
Keep row, element and present-value counts separate.
Route 3 · Chapters 7–9: Check and transfer
Build a round-trip test that can notice lost structure
Use round trips, query fixtures and a different nested record.
Full chapter index · Independent worked case · Questions and practical checks
Chapter index
Build the structure
Name the three kinds of absence
Declare the schema before assigning levels
Work the five-row sequence
CHAPTER 1 OF 9 · Build the structure
1. Name the three kinds of absence
Use an optional field called scores whose value, when present, is a list of optional integers. In plain language, the whole list may be missing, a present list may have no elements, and an existing element may be null. Our four example states are null, [], [null] and [7, null, 9]. Brackets denote a list, and the word null denotes absence at the relevant level.
An absent list does not state that no scores exist in the world. It states that this record has no list value under the schema and application definition. A present empty list records zero elements. A list containing null records an element position without an integer value. What those states mean operationally still needs the dataset’s documentation.
Imagine an application defines null as “not submitted yet”, an empty list as “submitted with no attempts”, and a null element as “one attempt with no numeric mark”. Flattening all three to blank destroys distinctions that can change a denominator or follow-up action. Another application can use different meanings; the storage format preserves structure without defining the business policy.
Before reading encoded bytes, write expected records in an unambiguous form. A spreadsheet display might hide the difference between an empty cell and the text “[]”. A JSON-like display is helpful for this exercise, but a real interchange must also preserve types and the difference between an omitted property and an explicit null if those are distinct in that interface.
The first practical check is therefore semantic, not binary. Ask a data owner which states matter and how they should survive the pipeline. Then use those states as test cases. No amount of correct decompression can restore a distinction that the source system deliberately discarded.
CHAPTER 2 OF 9 · Build the structure
2. Declare the schema before assigning levels
Our teaching schema follows the standard three-level LIST form: an optional outer group named scores with a LIST annotation; inside it, a repeated group named list; inside that, an optional INT32 field named element. The outer optional group permits a missing list. The repeated middle group permits zero or more elements. The optional leaf permits an element with no integer.
Along the path to the leaf, there are three definition steps in this particular schema: the outer optional list, an occurrence of the repeated element group, and the optional integer value. Maximum definition level is therefore 3. There is one repeated level on the path, so maximum repetition level is 1. These are counts of schema structure, not counts of nesting brackets in a displayed row.
Definition level 0 in this example means the outer list is absent. Level 1 means the outer list is present but there is no repeated element occurrence: an empty list. Level 2 means an element occurrence exists but its optional integer is absent. Level 3 means the integer is present, so the decoder consumes one integer from the encoded value stream.
This mapping is not universal. If the list itself were required, the maximum and intermediate definition levels would change. If its element were required, a null element would no longer be a valid state. A reader must derive the mapping from the actual schema, including recognised list conventions, rather than memorise that 1 always means empty.
Legacy LIST shapes require careful interpretation under the compatibility rules in the Parquet logical-type specification. Do not rename an arbitrary repeated group into a list and assume the same levels now apply. Validate the writer’s actual schema and the reader’s interpretation before comparing reconstructed records.
CHAPTER 3 OF 9 · Build the structure
3. Work the five-row sequence
Here are five logical rows, numbered 0 through 4: null; []; [null]; [7, null, 9]; [5]. For the first row, emit one structural entry with definition 0 and repetition 0. There is no physical integer. For the second, emit definition 1 and repetition 0. Again, there is no physical integer. The different definition level preserves the different state.
For row 2, [null], emit definition 2 and repetition 0. There is one element occurrence, but no integer value to consume. For row 3, [7, null, 9], emit three entries: (repetition 0, definition 3) for 7; (1,2) for the null element; and (1,3) for 9. The repetition 1 entries continue the same list.
For row 4, [5], emit (0,3) and consume the integer 5. The complete definition sequence is 0, 1, 2, 3, 2, 3, 3. The matching repetition sequence is 0, 0, 0, 0, 1, 1, 0. The physical integer stream contains only 7, 9 and 5, in that order.
Keep these three sequences aligned entry by entry. At each definition 3, take the next physical integer. At definitions 0, 1 or 2, reconstruct the appropriate structural state without consuming an integer. Treating a null element as a zero-valued integer would both invent a mark and misalign the remainder of the stream.
Check the counts independently: five logical rows, seven leaf-level structural entries, five list elements across present lists, three present integers, and two null elements. The null list and empty list contribute structural entries but are not list elements. A single count called “values” can conceal all of these different units.
CHAPTER 4 OF 9 · Reconstruct the records
4. Reconstruct one row at a time
Start the decoder with no current row. Read repetition 0 and definition 0: start row 0 and record a null list. The next entry also has repetition 0, so start row 1; definition 1 creates an empty list. The next repetition 0 starts row 2; definition 2 creates a one-element list whose element is null.
The next entry, (0,3), starts row 3 and consumes 7. The following (1,2) does not start a new row; append a null to row 3. The following (1,3) appends 9 after consuming it from the physical stream. The final (0,3) begins row 4 and consumes 5. Only the repetition changes tell you where the repeated list continues versus where a new top-level row starts in this simple path.
Now deliberately make a wrong decoder. Let it start a new row for every structural entry. It produces seven rows rather than five, spreading the last list across multiple rows. A second wrong decoder starts a new row for each present integer and produces only three rows. Both errors can look plausible if a test checks only the final integer values rather than the complete records.
A third wrong decoder preserves five rows but changes [null] into []. Its count of present integers remains correct, and some aggregates may be unchanged. The output is still not a faithful reconstruction. A round-trip comparison needs to inspect list presence, list length and element positions, not merely sums of numeric values.
Use stable row IDs in another flat column to expose alignment errors. The ID belonging to row 3 must remain associated with the entire [7, null, 9] list. Matching the first integer page to the first ID page by ordinal is not enough. Physical pages in different columns need not have the same boundaries.
CHAPTER 5 OF 9 · Reconstruct the records
5. Count the unit your question actually asks for
Suppose you want the number of records that have a submitted list. Under the fictional application definition, rows 1 through 4 have lists, so the answer is four. Suppose you want the number of records with at least one element. Rows 2, 3 and 4 qualify, giving three. Suppose you want the number with at least one numeric score. Only rows 3 and 4 qualify, giving two.
Now count elements rather than records. The present lists have lengths 0, 1, 3 and 1, giving five elements total. Three elements contain integers and two are null. The seven structural entries do not equal this element count. The extra entries preserve the missing and empty top-level list states.
An average over present integers in the fixture is (7 + 9 + 5) divided by 3, or 7. An average that replaces null elements with zero would divide 21 by 5 and give 4.2. That may be a deliberate application rule, but it is not a neutral decoding step. State the transformation before comparing the two results.
There is no numeric score corresponding to the absent list or the empty list. Adding one zero per such row would be yet another transformation with another denominator. The format has not told you which mean the reader should publish. It has preserved enough structure for the application to choose explicitly.
When a metadata field reports a value count, inspect its definition for the relevant format structure. Do not substitute it mechanically for the number of top-level rows, list elements or non-null integers. Nested storage makes unit discipline essential: several legitimate counts can coexist without disagreeing.
CHAPTER 6 OF 9 · Reconstruct the records
6. Change one schema condition and rebuild the example
Make scores a required list while leaving its elements optional. The state null for the entire list is now invalid. The remaining rows must be represented under the new path, whose maximum definition level is lower because the outer optional step has gone. Reusing the old level sequence without changing the schema would misinterpret the states.
Instead keep the outer list optional but require its elements. A present list may still be empty, and the whole list may still be absent. The states [null] and [7, null, 9] are no longer valid under that schema. A migration must decide how to handle them explicitly: reject, preserve under another field, or apply an authorised transformation.
Do not let a writer’s ability to emit a file stand in for a lossless migration test. A conversion can succeed by dropping invalid elements or substituting a value. The right question is whether it preserved the intended logical records or applied a documented change. Compare the records before and after under their respective schemas.
Add a second repeated level, such as a list of lists, and one-bit repetition reasoning is no longer sufficient. The level must identify which repeated boundary is being continued. A new inner list within the same outer record is not the same as a new top-level row. Derive the path again rather than extending the one-level example by guesswork.
For teaching, change only one condition at a time. First make the list required. Then make the element required. Only after both cases are clear should you add another repeated level. This sequence reveals which structural distinction each schema feature makes possible.
CHAPTER 7 OF 9 · Check and transfer
7. Build a round-trip test that can notice lost structure
Begin with the five exact logical records and a stable ID for each. Write them with the intended schema, then inspect the stored schema rather than trusting a filename or configuration label. Read them back with the intended reader and compare each complete record, including null list, empty list, null element, order and duplicate elements.
Add an ordinary integer zero and a duplicate list such as [7, 7]. A decoder or export that turns null into zero can now be distinguished from one that preserves the value. A transformation that converts lists to sets will fail on the duplicate. Neither mistake is caught by a test using only distinct positive integers.
Change page boundaries without changing records. Arrange a long list and enough surrounding data to exercise the writer’s page policy. Do not assume how the writer divides it; inspect the result. A reader must reconstruct the same logical rows for every valid layout it supports, including the required handling of row boundaries and level streams.
Use a second supported reader where useful. Agreement can strengthen evidence about interoperability, but two readers can share a mistaken assumption. Keep the independently written expected records as the primary fixture contract. A full scan through a different path should not redefine the expected result after a discrepancy appears.
Record writer and reader versions, schema, input fixture, actual stored layout and output. State exactly which transformations occurred between source and file. If an intermediate table flattened the lists, a later Parquet read cannot be blamed for failing to restore the original nested structure.
Try a query fixture that asks for rows with an empty list, then a separate query asking for rows containing a null element. Under the stated application definitions, row 1 answers the first query while rows 2 and 3 answer the second. A pipeline that collapsed [null] into [] can therefore pass a numeric-sum test and fail both structural queries. Use your engine’s documented operators rather than assuming a particular SQL spelling is portable.
Add a query asking whether any element equals 9. It should identify row 3 once under an existence-style question, even though that row has three elements. An unnest-and-join query may have a different multiplicity contract. Make the intended result explicit before comparing counts. The format preserves the list; the query algebra decides whether the output unit is a parent record or an expanded element.
For a repeated-value check, change row 3 to [9,9,null]. An existence query still identifies one parent row. A query returning each matching element can return two matches. Neither is inherently wrong. Comparing them as though they promised the same result confuses a change in the query with a change in decoding. Preserve the question beside the expected result.
CHAPTER 8 OF 9 · Check and transfer
8. Diagnose a shifted list without guessing at the source
Suppose the integer values read back as 7, 9 and 5, but row 3 is reported as [7,9] rather than [7,null,9]. The physical integers are intact. Inspect definition levels and reconstruction before investigating compression. A missing null element is a structural loss even when every present integer survived.
Suppose row 4 receives 9 rather than 5. Trace whether a null consumed a physical value or whether repetition handling ended the previous row too early. Use a small fixture and stop at the first divergence. A count of correct values somewhere in the output cannot establish that each value belongs to the correct record.
If null lists and empty lists merge only after export to CSV, compare the reader’s in-memory result with the export representation. The first failing stage may be the text conversion, not Parquet. Name the boundary honestly and decide whether the export needs a documented encoding for nested values rather than an empty-cell convention.
If a query engine unnests a list, inspect that operator’s semantics separately. Some operations omit empty and null lists or expand null elements differently depending on the operation. Such a query result is not automatically a literal round-trip of the source records. Check the actual engine documentation for the chosen operation before calling a smaller row count corruption.
Retain the original file and the smallest failing query or conversion. Do not “repair” the source lists by inserting zeros to make totals agree. That changes the data and can hide the original defect. A correction should explain which structural states were lost, which outputs depend on them and what can be recovered.
CHAPTER 9 OF 9 · Check and transfer
9. Transfer: reconstruct envelopes and answer slips
A teacher keeps one optional envelope per pupil, with zero or more answer slips inside. An envelope can be absent, present but empty, or contain a blank slip. Those states correspond to a missing list, an empty list and a null element under a suitable definition. A slip bearing 7 is a present integer.
Imagine a clerk records only the written numbers and throws away the envelopes and blank slips. They retain 7, 9 and 5 but cannot reconstruct the five pupils’ submissions. Structural evidence was not decoration. It carried the boundaries and absence states needed to put the numbers back into records.
Now keep two kinds of marks in a transcript: one says how far the envelope-and-slip structure exists, and one says whether the next entry continues the same pupil’s envelope. With a clear convention and the written numbers, the clerk can reconstruct the original records. The marks do not give a business interpretation to a blank slip; they preserve its position.
Change the rules so every pupil must submit an envelope and every slip must bear a number. Some earlier records are now invalid. You cannot preserve them merely by changing the label on the filing system. Decide whether to reject, transform or keep an explicit exception, and record the decision.
Finish by explaining why five pupils, seven transcript entries and three written numbers can all be correct counts. Then ask which count belongs in an average, a submission rate and an audit of missing marks. If you can answer without calling every absence zero, you have understood the transferable structure.
Independent worked case and evidence checks
Use a new fictional list field called stops with the same optional-list, optional-element structure. Its rows are []; [2,null]; null; [null,4]. The definition sequence is 1,3,2,0,2,3. The repetition sequence is 0,0,1,0,0,1. The physical integer stream is 2,4. Reconstruct all four rows without looking back at the input.
There are four top-level rows, six structural entries, four actual element occurrences and two present integers. Two rows have null elements, one list is empty and one list is absent. If a report gives six stops, it has counted the structural transcript instead of the list elements. If it gives two stops, it has counted only present integers.
Now replace the null in the last list with the integer zero. The final definition level becomes 3 rather than 2, and zero is added to the physical stream before 4. This shows why zero is not a spelling of null: it changes both the structural presence evidence and the value stream.
As a transfer check, make the leaf required. Which source rows are invalid, and which remain valid? The two rows containing null elements are invalid under that revised rule, while the absent list and the empty list remain possible if the outer list stays optional. Do not change those records without a stated migration policy.
Ask a second reader to explain one reconstruction error using only the schema and the three streams. If they need to know the expected total in order to decide where a value belongs, the trace is not explicit enough. A sound decoder follows structural evidence rather than adjusting rows until an aggregate looks right.
To check the reconstruction mechanically on paper, cross out one physical integer each time you encounter maximum definition level. At the end, both the structural transcript and physical stream should be exhausted consistently. Too many integers left over or an attempt to consume beyond the stream is evidence of an inconsistent fixture or decoder. It is not permission to append zeros or silently truncate the last list.
Now exchange the last two logical records while preserving each record internally. The physical integer order and repetition sequence can change. A round-trip comparison must follow stable row identity and list order under the declared record ordering. A checksum of the concatenated present integers would answer only a byte-sequence question, not whether the parent-to-list associations survived.
Worked comparison
| Logical state in the declared schema | Definition level | Physical integer consumed |
|---|---|---|
| Missing list | 0 | None |
| Present empty list | 1 | None |
| Present null element | 2 | None |
| Present integer element | 3 | One integer |
Questions and practical checks
Are definition levels just null flags?
Not in a nested path. They preserve how far the optional and repeated structure is defined.
Does repetition level 1 start a new row?
Not in this one-repeated-level example. It continues the same list; level 0 begins a new top-level record.
Are nulls stored as zero integers?
No. The level information represents absence; no physical integer is consumed for the null.
Can the same levels mean something else in another schema?
Yes. Derive maximum levels and intermediate meanings from the actual schema.
Does equal numeric sum prove a lossless round trip?
No. List presence, element count, order, duplicates and null positions can change while the sum remains the same.
What is the smallest useful fixture?
Include a missing list, an empty list, a null element and multiple present elements, each tied to a stable row ID.
Continue with the connected subjects
Preserve the field and its interpretation
Parquet field IDs: keep nested field identity separate from a renamed label
Schema evolution: state what changes when optionality or structure changes
Null, blank and zero: check the meaning of absence before calculating
Follow rows into pages and queries
Page indexes: carry row positions across columns without zipping page numbers
Row-group statistics: use null evidence only for the unit it describes
Parquet terminology: distinguish schema, logical types and encoding
Sources and scope
Apache Parquet: definition and repetition levels
Apache Parquet logical types: standard LIST structure and compatibility rules
Apache Parquet: nullity and present values
Apache Parquet format: value counts, page headers and schema elements
The numerical cases in this guide are original fictional teaching examples, not measurements from a real site. This is an educational explanation, not a field safety instruction, engineering approval or an official certification. For a real project, use qualified practitioners, current product documentation and site-specific evidence. Sources were checked on 5 November 2026.