Storing XML data from thousands of API calls

Question

I am working on a data pipeline that pulls document metadata from an electronic document management system (EDMS). The end goal is to visualize document statuses (overdue, submitted etc.) in a PowerBI dashboard. I use Python to make API calls to the EDMS, which returns an XML file with the metadata for each document. Since I dont want to download the metadata of ALL documents each time the pipeline runs, I first query the EDMS to get the document IDs of any documents which have been modified since the last pipeline run. I can then query the EDMS iterating through the list of modified document IDs.

My question is: how should I store the data returned by the API calls. My options (as i see them):

1. Save each XML file separately. This makes it easy to overwrite files when the document metadata changes in future pipeline runs, but it does result in hundreds of thousands of indiviual XML files, which makes subsequent processing much slower (the infamous "tiny files problem").
2. Store all the document metadata in a single combined XML file. This eliminates the tiny file problem, but does it mean that for each document that is modified, I have to run an XPath search to find the corresponding XML elements in the combined XML file to then update that section of the XML. I am guessing it might also cause memory issues?
3. Store only the XML data of the modified files as a single XML file. I.e. overwrite a single XML file each pipeline run which stores the document metadata of all the modified documents. This is probably the easiest to implement, but it means we do not retain a raw data copy of the metadata of each document in the EDMS.

In a subsequent step the raw data is ingested into a relational database. What would be the best practice solution to this problem?

Answer 1

store the data returned by the API calls ...

I was going to propose: option (4.) Save the data from each XML file in a new row in a DB.

And then, fortunately, I saw:

In a subsequent step the raw data is ingested into a relational database.

Any data pipeline needs to be robust against routine failures. It's not super surprising that a host might reboot, we type ctrl-C, disk space is exhausted, malloc fails, a TCP connection is reset, or a rare data pattern makes your parser core dump. These things happen. On the next run we must pick up the pieces and move on with the job.

Incremental loading of "new" entries is a very good step in that direction. A "whoops" on a previous run just means there's more "new" entries to deal with on the next run, since some or all of the attempted entries were not marked "done".

We have a Source Of Truth available through the EDMS API, plus some scratchpad storage, plus an RDBMS table where the ETL'd bits will end up. You can nuke the scratchpad daily and the pipeline still runs fine, so don't worry too much about its format. Use one tiny file per XML document if that's convenient for debugging. Append them all to a giant XML document if you're OK with discarding the entire run when there's an ETL hiccup. I would create new rows in a table for arriving API data, and here's why.

When a job starts, first thing it has to do is assess "where did we leave off last time?" Usually this corresponds to a timestamp, or perhaps a document serial number ID. Querying a giant XML file for that does not sound super convenient. Querying a filesystem could be as simple as ls -t *.xml, or perhaps a /usr/bin/find invocation, or having your script stat() some files. You can include the timestamp / counter as part of the filename, or just use the FS mtime modification time.

But querying an indexed column of a DB table is even simpler. Plus we enjoy BEGIN .. COMMIT transaction integrity -- no partly written files. And the subsequent processing step, which might be moving the bits from an ETL table into a more permanent table, will likely want to compare such timestamps, again protected by transaction boundaries. So RDBMS is where my vote goes.