Handling XML in Warehouses and Lakes the Practical Way

XML arrives in analytics from the places that predate it: an ERP export, a government dataset, a partner's order feed, a publisher's archive. The people who receive it work in warehouses and lakes that were built for tables and Parquet, and the usual response is a one-off script that works until the feed changes. This guide describes a way of handling XML that survives change. It is built from tests run on the same data in Spark, DuckDB, PostgreSQL and Node, with the platform-specific parts that I could not execute, Snowflake and BigQuery, marked as such.

The shape of the problem

Warehouses store rows with types. XML stores trees with text. Between the two sits a parse, a flattening and a set of decisions, and almost every defect in XML pipelines traces to one of those decisions being made implicitly by a tool.

The parse turns bytes into a tree or a stream. It can fail on bad encoding, on truncation, on an unexpected namespace. It can also succeed and give you the wrong thing, as when a tool reads 3.50 as a floating point number and writes 3.5.

The flattening turns a tree with repeating parts into one or more tables. An order with three lines becomes one order row and three line rows, or three rows each carrying the order's fields. The choice decides what you can join and what you can count without double counting (flattening repeating XML elements into rows).

The typing gives each text value a type. XML has none, so someone decides that this element is an integer, that one a date and that one a decimal with two places. Left to inference, the decision is made by the first rows that happen to be read.

A good design makes each of the three explicit, once, in one place, and puts everything else around them: where the original goes, how you know you did not lose anything and how you redo it when a rule changes.

The architecture in four steps

1. Land. Store the received file unchanged, compressed, in a path that names the source and the arrival date, with a manifest.

2. Parse once. Read the raw file with a declared schema, flatten, and write typed output.

3. Reconcile. Compare counts and one or two sums between the source and the output, and record the result.

4. Serve. Expose the output as tables or Parquet, and let analysts query that, not the XML.

Steps 1 and 3 are the ones skipped most often, and they are the ones that save you. The following sections take the steps in order and then the engines.

Step 1: land the raw file

The raw layer is a record of what was received. A 33,048,205 byte orders file landed as a 3,696,037 byte gzip, 11 percent of the original, under this layout:

raw/source=orders/dt=2026-10-27/_manifest.json
raw/source=orders/dt=2026-10-27/o100k.xml.gz

with a manifest recording the sizes, the SHA-256 of the original bytes, the record count from a cheap streaming pass and the time of receipt. The path uses key=value segments that Spark, Athena, DuckDB and BigQuery external tables understand as partitions. The lander does nothing else: no validation, no fixing, no rejecting. If the file is broken, you want the broken file, so the discussion with the provider can start from evidence. Storing raw XML in a lake and parsing it later has the code and the operating rules: immutability, retention, access control.

The payoff comes later, usually at a bad moment. A rule changes, or a field you ignored turns out to matter, or a corrected file arrives for last Tuesday. With the raw layer you rerun the parse for the affected dates. Without it you ask the provider, wait, and hope their archive has it.

Step 2: parse once, with a declared schema

Here the engines differ in how much they do for you, and they agree on one lesson: do not let them infer types.

Spark 4.0 has a native reader. spark.read.format("xml").option("rowTag", "order") infers a schema in which attributes become underscore-prefixed fields and repeating elements become arrays of structs; explode turns the array into rows. On 100,000 orders it produced 300,251 line rows. Inference typed price as double, and the grouped revenue printed as 2.543324211699986E8. With an explicit schema carrying price decimal(10,2) the same query printed 254332421.17. The three revenues add to 751,472,561.35 and agree with PostgreSQL's total for the file (flattening XML in Spark and Databricks).

DuckDB has no core XML reader. The Python route streams records with lxml, builds Arrow tables in batches and registers them: 1.2 million lines in 7.9 seconds and 62 MB of Arrow, then a grouped sum in 53 ms. The community extension webbed reads XML directly (300,251 lines in 2.9 seconds), and like Spark's inference it typed price as double and qty as text (using DuckDB to read XML through Python).

PostgreSQL, if it is your warehouse or staging store, loads best by streaming records into COPY: 400,000 orders and 1.2 million lines in 6.5 seconds with 44 MB, against 38.5 seconds and 519 MB for a quarter of the data using a tree and row inserts (streaming XML into PostgreSQL without a DOM).

Snowflake reads XML natively into a VARIANT. A file format of TYPE = XML with STRIP_OUTER_ELEMENT = TRUE loads each child of the root as a row, and XMLGET, the "$" content key and LATERAL FLATTEN unpack it into typed columns. These statements follow the documentation and were not executed here (loading XML into Snowflake with native support).

BigQuery cannot load XML, and its SQL library has no XML functions. The options are a JavaScript UDF with a bundled parser, or parsing before the load. I ran the parsing function in Node with fast-xml-parser, and it surfaced a classic trap: by default the parser reads 3.50 as the number 3.5, and 1.00 as 1, so money loses its trailing zeros before you can cast it. With parseTagValue: false the text stays text (parsing XML in BigQuery with a JavaScript UDF).

A thread runs through all of these. Whatever the engine, the schema is a document you write: for each field, the path in the XML, the type, whether it is required and what to do on failure. Keep it in version control. It is the contract between the feed and your tables.

Step 3: reconcile

A pipeline that reports "done" tells you nothing. A pipeline that reports counts and a sum is auditable. Every engine above got the same answers on the same data, and that agreement is what makes any one of them trustworthy:

orders by status (DuckDB / Arrow, 400,000 orders)
open     402389  1007988756.66
paid     401103  1003578628.72
shipped  396417   990411449.62      total 3,001,978,835.00

The same total came from the lxml streaming loop in Python, from the PostgreSQL tables after COPY, and from the Parquet file. Make this a step in the job, not a one-time check:

• Count the records in the raw file at landing (grep -c '<order ' is fine for machine-written files, a streaming count for others) and compare with the rows in the output. For a flattened table, count the repeating element as well: 300,251 <line tags gave 300,251 rows.

• Sum a numeric field both ways, from a second parse path and from the warehouse table. A mismatch in the cents is a type problem; a mismatch in the thousands is a lost record.

• Check for nulls in columns that must not be null. Permissive modes turn a value that does not fit its type into null and keep going. Null counts are where silent failures show.

• Write the result to a log table with the raw file's checksum, the parser version and the time.

Fail the job, not the report, when a check fails. A table that is visibly late is better than a table that is quietly wrong.

Step 4: store for the questions you will ask

The output format should suit the queries. For analysis, a columnar file is the right shape. A 132 MB XML file of 400,000 orders became a 12.1 MB Parquet file of 1,199,909 typed rows, written in 10.7 seconds with zstd compression. DuckDB answered a grouped sum over it in 19 ms and a filtered aggregate in 6 ms, where streaming the XML to compute the same sum takes about four seconds each time (choosing Parquet after you parse XML).

Choose by access pattern. Analytical scans want Parquet or a warehouse table. Fetching one order by id, repeatedly, wants a database with an index. Spreadsheet users want CSV. Updates and deletes want a table format with transactions, or a database. The XML itself stays in the raw layer; nothing in this design edits it.

Partition the parsed output by arrival date or business date, the way raw is, and keep files between roughly 100 MB and 1 GB. Many tiny files slow every engine down, and a single enormous file defeats pruning.

Handling the awkward feeds

Real feeds break the tidy picture in a few predictable ways.

Optional and irregular elements. A field present in half the records becomes a nullable column. A field present in 2 percent might be better kept in a semi-structured column (a VARIANT, a JSON string) than as its own column, until it earns one.

Namespaces. Prefixes differ between providers and between versions. Strip them in the parse, or key on the namespace URI, not the prefix (handling XML namespaces in ElementTree).

Mixed content. Text with inline markup does not flatten. Extract the plain text for search and store the markup as a string if you need it.

Huge single documents. One 2 GB document with a single root and millions of children is fine for a streaming parser and a problem for tools that load a value at a time. Split at record boundaries, or stream.

Changing structure. A provider that renames a field breaks the parse silently if the engine infers. A declared schema fails on the first bad record and tells you the field. Pair it with a shape check on each delivery (detecting structure changes in an XML feed).

Encoding. A file that declares one encoding and uses another fails at an arbitrary offset. The landing manifest, which records the declaration and the first failure, turns that into a ticket.

Late and corrected files. A corrected delivery for an earlier date arrives. Because parsed output paths are functions of the raw path, you rebuild one partition, and the history stays consistent.

One schema file, several engines

The same fields have to be declared in each tool's own language: a Spark schema string, an Arrow schema, a CREATE TABLE, a BigQuery table definition. Maintaining four copies invites drift. A cheaper arrangement is one small description of the feed, kept next to the job, from which the others are generated.

For the orders feed the description needs only a few facts per field: the XML path (@id on order, customer under order, @sku on line), the type (int, string, date, decimal(10,2)), whether it may be missing, and the repeating element that defines the grain of the output table. From that, a short script can emit a Spark schema string, the pyarrow schema used for Parquet and the DDL for PostgreSQL or Snowflake. The script is a few dozen lines, and it pays for itself the first time a provider adds a column: you change one file, regenerate, and the diff shows what moved in every target.

Keep the description honest by testing it against samples. A test that parses a handful of real documents, including the awkward ones, through the generated schema and fails on any field that cannot be typed catches a wrong path or a wrong type on the day it is introduced. It is also the document a new colleague reads to learn what the feed contains, which is more than most feeds have.

Version the description with the parser. When a field's type changes, from int to string because the provider started sending codes with letters, bump the version, rebuild the affected partitions from raw and keep both versions' outputs until consumers have moved.

Incremental feeds and late data

Many feeds are not complete files. A daily delivery may contain only changed records, a weekly one the full state, and corrections for old dates can arrive at any time. The raw-and-parse design handles all three if you decide what a delivery means up front.

For a full snapshot, the parsed table for a date is a replacement: write it to a new partition and swap. For a delta, the parsed output is a set of upserts, and the table is the result of applying deltas in order; keep the order explicit, by received time, and make the apply step idempotent by keying on the record id and the delivery checksum. For corrections, rebuild the partition of the business date they refer to, and record that the partition was rebuilt and why.

The manifest is what keeps this manageable. Because each file has a checksum, a repeated delivery is skipped, and a changed file for the same date is recognized as a correction rather than a duplicate. Because each parsed row names the raw file it came from, a deleted or superseded record can be found in one query. Incremental XML feed imports from the last run covers the bookkeeping for the common "since last time" case.

Where the time goes on each platform

The cost of the XML step differs by platform, and the numbers above give a feel for it. On one machine, a streaming parse of 127 MB took about four seconds, a streaming COPY into PostgreSQL about six and a half, a Parquet write about eleven, and a Spark local job about eleven including startup on a quarter of the data. Those are small amounts of time next to everything around them: queuing a cluster, transferring files, waiting for a warehouse to resume.

That suggests where to optimize. If the XML is under a few hundred megabytes a day, do not spend a day tuning the parser; a script on a small machine finishes before a cluster would start. If it is tens of gigabytes a day, parallelize by file, since the cheapest parallelism is many files and many workers, and split large files at record boundaries (parallel processing of split XML files). If it is hundreds of gigabytes, the XML step is no longer the part to tune, and the question becomes whether the provider can send Parquet or at least smaller, partitioned files.

Warehouse compute is billed by time, so moving parsing out of the warehouse often lowers the bill. A UDF or a VARIANT navigation that runs on every query pays the parsing cost repeatedly, while a Parquet file pays it once. Measure the cost of a representative month both ways before committing.

Security and privacy for raw XML

A raw layer keeps everything the provider sent, including the parts you do not use. Treat it as sensitive by default. Restrict access to the raw prefix to the pipeline and a small group, encrypt at rest, and log reads. If the feed includes personal data, classify the raw files with the same label as the tables built from them, and apply the same retention rules; keeping raw files forever can contradict a promise to delete personal data on request.

Parse defensively. XML parsers can be told to fetch external entities or expand nested ones until memory runs out, and a feed from outside your organization is untrusted input. Configure the parser to refuse external entities and DTDs, set limits on size and nesting, and prefer the safe-by-default libraries (XML external entity attacks and safe XML parsing in Python with defusedxml cover the details). Do not assume a platform's native reader is safe either; read its documentation on entities, and test with a small file that declares one.

Finally, validate what you load. A schema check at landing, run against a sample or the whole file depending on size, produces a report you can send back to the provider. Rejecting a bad file at the door is not the aim; the aim is to know about it before the dashboards are wrong.

A reconciliation session, step by step

Here is the whole check, as I would run it after the first load of a new feed, using the commands that appeared in the tests above.

First the source counts. grep -c '<order ' o400k.xml returned 400,000 in 0.3 seconds, and grep -o '<line ' o400k.xml | wc -l returned the line count, which the 100,000-order file gives as 300,251. Both numbers are cheap and independent of any parser. Second the output counts. The Parquet footer holds the row count without scanning, pq.ParquetFile("lines.parquet").metadata.num_rows, and the same count comes from SELECT count(*) in DuckDB or PostgreSQL. The two sets must match exactly, and a gap in either direction is a defect: fewer rows means lost records, more means a flattening that multiplied rows.

Third a money check from a different path. Sum quantity times price in a plain streaming loop, as the benchmark script does, and compare with the sum in the warehouse table. The loop printed 3,001,978,835.00 and every table agreed once prices were decimals. If a platform had printed 3,001,978,834.99999994, you would see the double precision problem in the last digits and know where to look.

Fourth the distribution. Compare the count by status between the XML and the table: grep -c 'status="paid"' against SELECT count(*) WHERE status = 'paid' on orders. A parser that mishandles one value of an attribute shows up here and nowhere else. Last the nulls: count rows where a required column is null. Zero is the only acceptable answer for fields the feed promises.

Write the five numbers in the job log with the raw file's checksum. If anyone asks in six months why the October revenue changed, the log shows what was loaded, from which file, by which parser version, and whether the checks passed. That record is the real product of the whole design, and it costs about thirty lines of code.

Choosing the engine

The right platform is the one your data already lives in. If you are on Snowflake, native XML loading plus FLATTEN gets you far, with the limit on value size in mind. If you are on BigQuery, parse upstream, because a UDF is a prototype tool. If you run Spark or Databricks, the built-in reader is good, with an explicit schema. If your team works on laptops and a database, DuckDB with an lxml loop is hard to beat for a few gigabytes. If the feed is modest and you have PostgreSQL, a streaming COPY loader is the shortest path to a reliable table.

Do not choose a platform for its XML support alone. The XML step is a small part of the pipeline, and it can almost always be moved into a script that runs before the warehouse. A small, tested parser that writes Parquet works with every engine, and it keeps platform-specific code out of the part of the system that is hardest to test.

A checklist before the first production load

• The raw file is stored unchanged, compressed, with a manifest.

• The schema is written down, with types, and money is decimal.

• Parsing is streaming, with memory limits tested.

• The job reports row counts and at least one sum, and fails when they disagree with the source.

• Output paths are functions of input paths, so reruns replace and do not duplicate.

• The parser has a version, recorded in the output.

• A sample of records was compared field by field.

• Someone knows how to reprocess a bad day.

Related reading

For the memory and speed of the parse itself, read parsing big XML files fast. The tidy-up of the data before analysis is in turning XML feeds into relational tables, and the incremental case in incremental XML feed imports from the last run. Questions you can answer once the data is loaded start with answering questions from XML data.

Vendor and project documentation: Snowflake semi-structured data, BigQuery loading data, the Spark XML data source and the Apache Parquet documentation. Inspect a sample record in the XML formatter.

When this guide's approach does not apply

The XML is small and occasional. A monthly file of a few thousand records does not need a raw layer, a manifest and a reconciliation job. Load it with a script, check the count and move on.

You query the XML in place. If the XML lives in a database column and you query it with XPath, the design is different, and indexing is the issue (indexing XML columns in PostgreSQL without wrecking inserts).

The feed is a stream of tiny messages. Landing every message as a file is wasteful. Batch them into hourly files, and keep the rest of the design.

Regulation dictates handling. Some data may not be stored raw, or must be kept in a particular place. Follow the rule, and document how the design is adapted.

The numbers in this guide differ from yours. They come from one data shape on one machine. Ratios such as the compression factor and the speedup from Parquet will vary with your data, and absolute times with your hardware. Re-run the checks on a sample of your own before you quote them.