Choosing Parquet After You Parse XML for Fast Analysis

Parsing XML is a cost you want to pay once. The loop that reads 400,000 orders takes several seconds every time, and every question you ask of the data repeats it. The sensible next step is to land the flattened rows in a format built for questions. For analysis that format is usually Parquet: columnar, compressed, typed, readable by DuckDB, Spark, Pandas, BigQuery, Athena and Snowflake. This post measures what you gain and walks through the writer.

Writing the file

The writer streams records and appends rows to a Parquet file in batches, so memory stays bounded. The schema is declared, with types chosen on purpose:

import datetime, decimal
import pyarrow as pa, pyarrow.parquet as pq
from lxml import etree

schema = pa.schema([("order_id", pa.int32()), ("status", pa.string()), ("customer", pa.string()),
                    ("placed", pa.date32()), ("sku", pa.string()), ("qty", pa.int16()),
                    ("price", pa.decimal128(10, 2))])
w = pq.ParquetWriter("lines.parquet", schema, compression="zstd")
cols = {n: [] for n in schema.names}

def flush():
    if cols["order_id"]:
        w.write_table(pa.table(cols, schema=schema))
        for k in cols: cols[k] = []

n = 0
for _, el in etree.iterparse("o400k.xml", events=("end",), tag="order"):
    oid, st, cu = int(el.get("id")), el.get("status"), el.findtext("customer")
    pl = datetime.date.fromisoformat(el.findtext("placed"))
    for line in el.iter("line"):
        cols["order_id"].append(oid); cols["status"].append(st); cols["customer"].append(cu); cols["placed"].append(pl)
        cols["sku"].append(line.get("sku")); cols["qty"].append(int(line.get("qty")))
        cols["price"].append(decimal.Decimal(line.findtext("price")))
        n += 1
    el.clear()
    while el.getprevious() is not None:
        del el.getparent()[0]
    if n % 200000 < 5:
        flush()
flush(); w.close()

The result:

wrote 1199909 rows in 10.7s, peak_rss=218MB
xml bytes 132461154 parquet bytes 12126975

The file is 12.1 MB, 9 percent of the XML and smaller than the 14.8 MB gzip of the same XML (compressing XML archives and reading them in place). It holds typed columns, which gzip cannot offer: integers as integers, dates as dates, price as decimal(10,2). The write took 10.7 seconds, once. Each denormalized row repeats the order's status, customer and date; Parquet's dictionary and run-length encoding makes that repetition almost free.

The declared schema is the reason to write this yourself rather than let a tool infer. Inference chose double for price in Spark and in DuckDB's extension, and sums of doubles carry rounding noise. Here, the decimal type keeps money exact end to end, and int16 for quantity fails loudly if a value does not fit.

Asking questions of it

DuckDB queries the file in place:

con.sql("SELECT status, count(*) n, sum(qty*price) revenue FROM 'lines.parquet' GROUP BY status ORDER BY status")

[('open', 402389, '1007988756.66'), ('paid', 401103, '1003578628.72'), ('shipped', 396417, '990411449.62')]
duckdb over parquet: 19 ms
[(2438, Decimal('6017269.75'))]
filtered: 6 ms

The grouped aggregate over 1.2 million rows took 19 ms. The three revenues sum to 3,001,978,835.00, the total every other route in this cluster produced from the same file, so the Parquet is a faithful copy. A filter on one customer returned 2,438 lines and a sum in 6 ms. Compare that with streaming the XML to sum the same data, which took about four seconds each time. The speedup is two orders of magnitude, and it applies to every query after the first.

The speed has a mechanical cause. Parquet stores each column separately, so GROUP BY status with sum(qty*price) reads three columns and skips sku, customer and the rest. Row groups carry min and max statistics, so a filter can skip groups that cannot match. XML offers none of that: to read one field of a record you must tokenize all of it.

When to choose it, and when not to

Parquet is a poor fit for a service that fetches single orders by id: the format is built for scans, not point lookups, and each lookup opens row groups. It is also immutable. You do not update a row in place; you write a new file. For a nightly batch that is perfect, and for a live system it is the wrong shape.

Partition the output for larger sets. Write one directory per day (dt=2026-10-27/) as in the raw layer, so engines prune partitions, and keep file sizes between about 100 MB and 1 GB for good scan performance. One 12 MB file is fine for this experiment. A thousand files of 12 KB each is a common mistake and slows every query.

The XML is still the record

Parquet is a derived copy. It drops what the flattening dropped: element order, comments, attributes you did not select, the original text of fields you converted. Keep the XML (storing raw XML in a lake and parsing later describes the raw layer) and record in the Parquet metadata which raw file and parser version produced it. When the rules change, rebuild.

Nested data can stay nested. Parquet supports structs and lists, so an order with its lines as a list column is possible, and engines read it. Flat rows are simpler to query and share, nested columns avoid repeating the order fields. For analysis by line, flat wins. For analysis by order with occasional line detail, consider a pair of tables, orders and lines, joined by key, which mirrors the relational shape the XML was flattened into (flattening repeating XML elements into rows).

Checking the Parquet against the XML

A converted dataset earns trust through reconciliation, not through the absence of errors. Do three checks every time the writer runs. Count: the number of rows in the file, read from the Parquet footer without scanning (pq.ParquetFile(path).metadata.num_rows), must equal the number of <line elements in the source, 1,199,909 here. Sum: one numeric aggregate, the revenue in this case, must match a calculation made a different way, and the three status totals added to 3,001,978,835.00 as every other engine reported. Schema: the types in the file must be the ones you declared, which pq.read_schema prints in one line.

Add a fourth check when the data matters: sample a few hundred random orders from the XML and compare each field to the matching rows in the Parquet. It finds the bugs that counts and sums miss, such as two columns swapped, a date parsed in the wrong order or a field that is always taken from the first line of the order.

Record the result next to the file. A small JSON sidecar with the row count, the checksum of the source, the parser version and the time of the run turns the file into something you can audit later, and costs a few lines of code.

Related reading

This post is part of XML in warehouses and lakes. The reading side is in using DuckDB to read XML through Python, and the wider parsing cost in parsing big XML files fast. The format is specified in the Apache Parquet documentation, and the writer API in PyArrow's ParquetWriter reference. Look at a source record in the XML formatter.

When Parquet is not the right target after XML

The data is small. A few thousand rows fit in a CSV or a SQLite file. Parquet's advantages appear at millions of rows.

Consumers cannot read it. Excel does not open Parquet. Offer a CSV export beside it for those users.

You need updates and deletes. Use a table format that supports them (Delta, Iceberg) or a database, not bare Parquet files.

The structure is irregular. A feed whose records differ widely may flatten into hundreds of mostly empty columns. Keep a semi-structured column (JSON or a variant type) for the irregular part.