Latest Contributions by 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 or mapping purposes by ArcGIS Pro, 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 requires 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, as can ArcGIS Enterprise, 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. Not shown:  To prevent interruptions to your schedule if you take days off work, use Windows Task Scheduler to allow the task to run if you are not signed in.  By default tasks will not run if you are not signed in. 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 over 5000 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! ... View more

It's that time of year again, another release! At writing we're in sync with the FME product in FME engine terms - 2026.1.  The post attachment contains details on notable features, new formats and transformers, plus productivity enhancements in the Workbench application. What keeps us motivated is learning what you need in our product, so send in your comments in this post or ArcGIS Ideas! Happy ETLing! ... View more

A recent (at writing) release of the Esri ArcGIS Connector package quietly delivered a significant benefit for people working with ArcGIS Online or Enterprise feature services - namely concurrent read requests. This matters for people doing things like change data capture (aka change detection) for current and revised editions of a dataset ahead of writing the delta transaction - you can read the current state of the data faster. How much faster?  Let me show you - there are two parts to the story, read request size and the new concurrency behavior.  Inspect the workspace annotation and translation log messages in two sessions reading the same service layer: First, the default situation before the recent package upgrade: "Before" read speed And the same data after the package upgrade: "After" read speed Like they say, your mileage may vary, but in my case a hosted point feature layer in ArcGIS Online went from reading the feature service of 1.044M features at  3,295 features per second to 12,533 features per second - over 3 1/2 times faster!  Partly this is the effect of setting the Features Per Request reader parameter to the maxRecordCount value allowed for the service layer (typically 2000), but in addition the new concurrency of underlying Query REST calls. Here is a workspace that shows the round trip, reading a CSV file at a URL with new data, reading the feature service, performing change detection between the two and writing the delta transaction to the feature service, the whole process in 3 minutes 10 seconds with the actual writing the delta transaction 5 seconds. Round trip - read and edit a feature service If your Esri ArcGIS Connector is earlier then 3.24.0 then upgrade now and enjoy the performance! ... View more

In a previous blog I explored the performance of no-code change data capture versus view source swap in a speed test when the goal is maintaining a hosted feature layer - and declared it a tie!  Now I have a new entrant from the coded solutions world - using an ArcGIS Online hosted notebook to calculate and apply the delta transaction for a hosted feature layer where the source data changes daily. My subject matter data is the same as the previous blog, street address points for the city of Los Angeles, updated daily. Los Angeles Address Points There are a little over a million points in the dataset, with a few hundred changes daily: inserts, updates and deletes.  I've been keen to write about an upsert use case (the combination of insert and update in a single transaction) for a while, as it is now supported with the Append geoprocessing tool in Pro and hence in ArcPy in the notebook advanced runtime. I'm getting ahead of myself, so let's first set the scene.  To consider a coded ETL workflow you need to be confident your data is well managed, with little or no need to make fixes or apply transformations during the ETL process - because while you can view data in a notebook it is very difficult to do deep data inspection and discover data problems.  If you can't trust your data then you should be using ArcGIS Data Pipelines or ArcGIS Data Interoperability. In this case the city is delivering well-curated data so I'm happy to recommend a coded lift-and-shift process. Back to the tools used.  In the blog download you'll find an ArcGIS Pro 3.6 toolbox with a model.  The model creates a file geodatabase feature class named  Addresses using CSV data it downloads from the city Open Data site.  The feature class has a tuned schema (see the field map control in the Export Features tool) and also a primary key field House_Number_ID with a not-null constraint and an index, requirements for using upsert transactions. Model Making Addresses Data I published my feature service from ArcGIS Pro after applying some symbology and popup behavior and made sure that House_Number_ID has a unique index in the feature service.  Now for the notebook that maintains the service. I'll let you step through the notebook code (in the blog download), but the processing steps are: Use DuckDB to read the source CSV file from the open data site download URL While reading, enforce a schema and make geometry in a memory relation (table) Using ArcPy, create a memory feature class from data in the DuckDB relation Merge existing and incoming address data into another memory feature class This contains both old and new records Use Find Identical to find identical sequences in the merged data across geometry and all fields The data is in Web Mercator so a tolerance of 1m allows for coordinate precision differences Run Frequency to support finding rows that are unique Edit features do not have identical matches Use set mathematics to determine the upsert and delete records Run Append and Delete Rows functions with the correct records If you inspect the notebook cell messages you'll see the whole job took 9 1/2 minutes (quite big data is read into the notebook and geoprocessed) but the actual write commits were small and took only a few seconds - pretty good in my book. After setting up scheduled processing weekday mornings I now have a continuously maintained information product! Please do comment in this post with any observations or questions. ... View more