Scheduling Parquet File Maintenance For Transactional Data - The Insert-Only Data Model Edition

BruceHarold

At writing it's the week after the 2026 Esri User Conference where I worked in the Esri Showcase and gave a few demo theatre presentations. It's also time to help with things I heard customers struggling with, and my blog topic combines two that are a good match:

Scheduling ETL tools
Sharing continuously changing external data using GeoParquet file collections

For example data, I am indebted to the City of San Francisco 311 team who kindly make available my subject matter data under this license.

The workflow we're tackling here is sharing 311 event data continuously in a way it can easily be consumed in bulk, on demand, for analytic purposes by ArcGIS geoprocessing or notebook environments. It could be any data in an external system of record, at any scale, and subject to edits on any schedule other than real time.

Reading the dataset update process details we see the dataset updates around 10am daily and the data retention policy is to keep all case history. This is not what I mean by an "Insert-Only Data Model", the concept of insert-only means we only ever add parquet files to the collection of files containing all data states over time at the 10am moment daily, new parquet files may contain insert, update or delete transactions.

How Insert-Only Works

Reading multiple GeoParquet files as a single dataset is supported in ArcGIS as Multifile Connections, by DuckDB in the default Python runtime, using GeoAnalytics Engine, or using the ArcGIS Data Interoperability extension. However, there are two considerations in play in our scenario:

The GeoParquet file collection grows as daily transactions result in new files
Features may exist across multiple GeoParquet files with new, updated and closed status

To read GeoParquet file collections at a glob (wildcard) path with the required view logic requires SQL expression support, available in DuckDB, so that's what we'll use. To support the required view logic requires columns in the data as follows:

An object identifier column, not shared with other events, such as long integer or string
A latest-edited datetime column, set on create, update or delete events
If the data retention policy includes deletes
- A column that flags delete status, such as a short integer, 1 = deleted, 0 = not deleted
None of the above may be null

After a couple of months scheduled ETL of GeoParquet files into a folder (in production I would use an S3-API compliant object store) I have 63 files:

ETL Output

Now we have GeoParquet files arriving daily how do we query them? I'll let you inspect the blog download but in a script tool or notebook the secret sauce is the DuckDB SQL QUALIFY clause used to make a view of the data, see below:

conn.sql(f"""create or replace temp view sf311_view as select
              service_request_id,requested_datetime,closed_date,updated_datetime,
                status_description,status_notes,agency_responsible,service_name,
                service_subtype,service_details,address,street,supervisor_district,
                neighborhoods_sffind_boundaries,police_district,source,media_url,
                bos_2012,data_as_of,data_loaded_at,ST_AsWKB(GEOM) as wkb
                from read_parquet('{pqPath}',filename=false)
                where {where}
                and ST_Intersects(ST_GeomFromText('{wkt}'), GEOM)
                qualify row_number() over (partition by service_request_id order by updated_datetime desc) = 1;""")

Here the pqPath variable is a glob path to the GeoParquet files (local, network, S3...), the where variable is a SQL where expression and the wkt variable is an OGC Well Known Text geometry object supplied from map input. The service_request_id and update_datetime columns are the object identifier and latest-edited datetime columns mentioned above. If the dataset supported feature deletion an extra term would be included in the where clause (assuming a delete status column is_deleted exists), like "and is_deleted = 0". If time travel is desired an extra term would be included like "and updated_datetime < timestamptz '2026-07-20 00:00:00+00:00'".

Scheduling ETL Tools

ArcGIS Pro can schedule any geoprocessing tool, and ETL tools are no different. Once scheduled, you don't have to interrupt your other work just to maintain data, and by using recurrence, you take care of future demand as well. It is also a good way to automate logging your ETL and geoprocessing metadata.

Here is my schedule dialog, starting today (at writing):

Schedule ETL

And here, after one scheduled run, we see from the History pane the latest GeoParquet file (with 2 days' worth of 311 transactions) is created in 28 seconds by the scheduled ETL tool:

Schedule History

So far so good! How does this perform?

Sharing The Data

So I have an original 8.7 million points in a baseline GeoParquet file and another roughly 3000 edits per weekday in their own GeoParquet files, how does querying perform?

Using a script tool (see the blog download) that allows setting a query shape and where expression I extract 5000 311 case features for this year around City Hall in 8 seconds.

Query Example

In the blog download is a zipped ArcGIS Pro 3.7 toolbox with two ETL tools that create GeoParquet files (requires ArcGIS Data Interoperability for Pro 3.7) and a script tool that extracts the data on demand. To implement the tools you will need to obtain an API key from the City of San Francisco's Open Data site, sign up here and your key will be available.

Now picture your transactional data being shared as folders or buckets of GeoParquet files, maintained on a schedule, and easily consumed in analytic workflows!