APPLYDJINN
← ETL and Data Engineering: reliable pipelinesProgress & review ↗

Typorithm · medium

Parquet schema, nulls and predicate boundaries

pythonetldata-engineeringindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

Parquet schema, nulls and predicate boundaries

0%

Concept and contract

Write and read an actual columnar file with DuckDB, checking schema as well as returned rows.

Parquet stores typed columns and is useful when analytical readers need only part of a dataset. A file extension does not establish a schema or prove an optimization occurred. This lesson creates a real Parquet file through DuckDB and reads it back before claiming a successful round trip.

The owned fixture defines id as VARCHAR and amount_cents as BIGINT. Missing amounts remain SQL NULL rather than becoming zero. The query filters at a supplied lower bound and orders the returned identities, separating data meaning from whatever physical row order the writer happened to use.

Mechanism and worked example

The program creates an in-memory staging table, inserts parameterized values and copies the table to batch.parquet in a fresh directory.

The program creates an in-memory staging table, inserts parameterized values and copies the table to batch.parquet in a fresh directory. DESCRIBE then observes the file schema, and read_parquet executes a real predicate against that file. The report returns measured schema names and filtered rows.

A record exactly on the minimum must be included because the baseline predicate is greater than or equal. NULL amounts do not satisfy that numeric comparison. The tests include empty input and a zero boundary, so the demonstration cannot substitute a hand-written expected list for actual file creation and query execution.

Complete worked example

python

import duckdb

def inspect_example(rows, minimum):
    connection = duckdb.connect(config={'threads': 1, 'memory_limit': '64MB',
                                         'autoinstall_known_extensions': 'false', 'autoload_known_extensions': 'false'})
    try:
        connection.execute('CREATE TABLE stage(id VARCHAR, amount_cents BIGINT)')
        if rows:
            connection.executemany('INSERT INTO stage VALUES (?, ?)', rows)
        connection.execute("COPY stage TO 'batch.parquet' (FORMAT PARQUET)")
        schema = [[r[0], r[1]] for r in connection.execute("DESCRIBE SELECT * FROM read_parquet('batch.parquet')").fetchall()]
        selected = connection.execute("SELECT id, amount_cents FROM read_parquet('batch.parquet') WHERE amount_cents >= ? ORDER BY id", [minimum]).fetchall()
        return {'schema': schema, 'rows': [list(row) for row in selected]}
    finally:
        connection.close()
Reasoning
The program creates an in-memory staging table, inserts parameterized values and copies the table to batch.parquet in a fresh directory. DESCRIBE then observes the file schema, and read_parquet executes a real predicate against that file. The report returns measured schema names and filtered rows.
Download the ETL and Data Engineering computer lab ↗

Focused decision: use the complete example for context

python

selected = connection.execute(
    "SELECT id FROM read_parquet('batch.parquet') WHERE amount_cents >= ?",
    [minimum],
).fetchall()
Decision
Read actual Parquet metadata and values, then count stored NULL amounts separately from the filtered rows.
Download the ETL and Data Engineering computer lab ↗

Counterexamples and common mistakes

The controlled bug changes the inclusive predicate to a strict greater-than comparison.

The controlled bug changes the inclusive predicate to a strict greater-than comparison. The boundary record disappears even though the file is valid and its schema has not changed. Successful serialization is therefore weaker evidence than an end-to-end contract covering both storage and the consuming query.

Do not infer predicate pushdown, row-group pruning or performance gains from the returned values alone. Those require query-plan and workload evidence. This bounded fixture disables automatic extension installation and uses built-in Parquet support; it does not contact remote object storage or establish a production partitioning strategy from a few rows.

Controlled bug: observe the rejected data contract

python

import duckdb

def inspect_example(rows, minimum):
    connection = duckdb.connect(config={'threads': 1, 'memory_limit': '64MB',
                                         'autoinstall_known_extensions': 'false', 'autoload_known_extensions': 'false'})
    try:
        connection.execute('CREATE TABLE stage(id VARCHAR, amount_cents BIGINT)')
        if rows:
            connection.executemany('INSERT INTO stage VALUES (?, ?)', rows)
        connection.execute("COPY stage TO 'batch.parquet' (FORMAT PARQUET)")
        schema = [[r[0], r[1]] for r in connection.execute("DESCRIBE SELECT * FROM read_parquet('batch.parquet')").fetchall()]
        selected = connection.execute("SELECT id, amount_cents FROM read_parquet('batch.parquet') WHERE amount_cents > ? ORDER BY id", [minimum]).fetchall()
        return {'schema': schema, 'rows': [list(row) for row in selected]}
    finally:
        connection.close()
Reasoning
The controlled bug changes the inclusive predicate to a strict greater-than comparison. The boundary record disappears even though the file is valid and its schema has not changed. Successful serialization is therefore weaker evidence than an end-to-end contract covering both storage and the consuming query.
Download the ETL and Data Engineering computer lab ↗

Independent practice

Add nulls to the report by querying the saved file for rows whose amount_cents IS NULL.

Add nulls to the report by querying the saved file for rows whose amount_cents IS NULL. Preserve the original schema and inclusive filtered output. The task deliberately asks for a separate null observation because NULL is not the same as an amount below the numeric threshold.

Run the count against read_parquet rather than the original Python rows. Use SQL IS NULL, not equality to NULL, because SQL comparisons with NULL do not produce ordinary true equality. Retain the empty-file case and the zero-boundary case so both the count and predicate keep their distinct meanings.

Step 1: Read actual Parquet metadata and values, then count stored NULL amounts separately from the filtered rows. Step 2: Edit the owned exercise file and run verify.py --practice etl-parquet. Preserve caller inputs and the independently declared failure cases.

Practice starter: solve before reading the solution

python

import duckdb

def inspect_example(rows, minimum):
    connection = duckdb.connect(config={'threads': 1, 'memory_limit': '64MB',
                                         'autoinstall_known_extensions': 'false', 'autoload_known_extensions': 'false'})
    try:
        connection.execute('CREATE TABLE stage(id VARCHAR, amount_cents BIGINT)')
        if rows:
            connection.executemany('INSERT INTO stage VALUES (?, ?)', rows)
        connection.execute("COPY stage TO 'batch.parquet' (FORMAT PARQUET)")
        schema = [[r[0], r[1]] for r in connection.execute("DESCRIBE SELECT * FROM read_parquet('batch.parquet')").fetchall()]
        selected = connection.execute("SELECT id, amount_cents FROM read_parquet('batch.parquet') WHERE amount_cents >= ? ORDER BY id", [minimum]).fetchall()
        return {'schema': schema, 'rows': [list(row) for row in selected]}
    finally:
        connection.close()

# Independent task: Read actual Parquet metadata and values, then count stored NULL amounts separately from the filtered rows.
Reasoning
Read actual Parquet metadata and values, then count stored NULL amounts separately from the filtered rows.
Download the ETL and Data Engineering computer lab ↗
  • Hint 1: Add nulls to the report by querying the saved file for rows whose amount_cents IS NULL. Preserve the original schema and inclusive filtered output. The task deliberately asks for a separate null observation because NULL is not the same as an amount below the numeric threshold.
  • Hint 2: Run the count against read_parquet rather than the original Python rows. Use SQL IS NULL, not equality to NULL, because SQL comparisons with NULL do not produce ordinary true equality. Retain the empty-file case and the zero-boundary case so both the count and predicate keep their distinct meanings.

Solution, tests and trade-offs

The solution performs an additional aggregate query against the actual file and converts the scalar into the returned report.

The solution performs an additional aggregate query against the actual file and converts the scalar into the returned report. Acceptance checks compare exact column types, ordered values and null count. This provides stronger evidence than merely checking that batch.parquet exists or has nonzero bytes.

Real analytical storage also needs compatible schema evolution, compression choices, file sizing and data-quality checks. Many tiny files can make planning expensive, while enormous files can complicate replacement and recovery. Choose these settings from observed workloads; this lesson establishes a deterministic local round trip rather than recommending one universal file size.

Practice solution and acceptance checks

python

import duckdb

def inspect_example(rows, minimum):
    connection = duckdb.connect(config={'threads': 1, 'memory_limit': '64MB',
                                         'autoinstall_known_extensions': 'false', 'autoload_known_extensions': 'false'})
    try:
        connection.execute('CREATE TABLE stage(id VARCHAR, amount_cents BIGINT)')
        if rows:
            connection.executemany('INSERT INTO stage VALUES (?, ?)', rows)
        connection.execute("COPY stage TO 'batch.parquet' (FORMAT PARQUET)")
        schema = [[r[0], r[1]] for r in connection.execute("DESCRIBE SELECT * FROM read_parquet('batch.parquet')").fetchall()]
        selected = connection.execute("SELECT id, amount_cents FROM read_parquet('batch.parquet') WHERE amount_cents >= ? ORDER BY id", [minimum]).fetchall()
        return {'schema': schema, 'rows': [list(row) for row in selected], 'nulls': connection.execute("SELECT count(*) FROM read_parquet('batch.parquet') WHERE amount_cents IS NULL").fetchone()[0]}
    finally:
        connection.close()
Reasoning
The solution performs an additional aggregate query against the actual file and converts the scalar into the returned report. Acceptance checks compare exact column types, ordered values and null count. This provides stronger evidence than merely checking that batch.parquet exists or has nonzero bytes.
Download the ETL and Data Engineering computer lab ↗

Interview reasoning and next step

Explain why NULL amounts are excluded from the numeric filter while zero can be included.

Explain why NULL amounts are excluded from the numeric filter while zero can be included. Then distinguish schema validation from value validation and performance measurement. A reader can successfully parse a file with the wrong logical meaning, so typed storage still needs business contracts and reconciliation.

For a stretch, inspect a query plan and compare projected columns on a larger owned fixture, recording engine version and data size. Keep correctness assertions unchanged. Do not describe a faster toy query as proof of general production performance or introduce live credentials into the teaching lab.

Optional stretch — after the core task works: Inspect a real query plan on larger owned data without changing the correctness contract.

Computer practice: https://applydjinn.com/typorithm/etl-data-engineering-lab-20261107.zip. Use an isolated Python 3.13 environment, install requirements.txt and run verify.py. Edit exercises/etl-parquet.py and run --practice etl-parquet. Each case owns its files and database; Parquet uses the pinned DuckDB engine. The recovery probe terminates a separate interpreter and reopens the SQLite file. The app retains static examples, prepared results and questions without an editor or code execution. Use the course Download button once to save every lesson and illustration; opening an unsaved course does not automatically download it.

Write and read an actual columnar file with DuckDB, checking schema as well as returned rows.