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

At writing I'm tasked with delivering a demo theatre session at the 2026 Esri User Conference titled Cloud-Native Georelational Data Distribution.  If you're coming to UC 2026 you can add it to your schedule.  If you can't make it then the book of the play is what follows below, however to encourage UC attendance there is a chapter missing from the book which I'll show live - namely how fast you can get data into your map or scene from AWS S3 while enforcing a data model for place-of-interest and building features - because you should always be thinking about an information product and not just data. The plot has three threads to its data velocity story; dataset lifecycles I'm categorizing as: Infrequent periodic bulk replacement Base layer data that isn't time enabled Frequent append and upsert Operational layers that grow continuously and have life stages and are time enabled Medium frequency insert, update and delete edits Operational layers that evolve and are time enabled My goal is to show how data might be offered in each of the above velocity scenarios, in a cloud native way, and brought into ArcGIS.  The common thread is that the cloud native format we'll use is GeoParquet in a public S3-API compliant object store such as AWS.  In all cases we'll use ArcPy and DuckDB in notebooks or script tools for data consumption, with the understanding that a data custodian would supply consumers with the tools needed for ArcGIS to consume the data.  The combination of S3, GeoParquet and DuckDB provides performant and functional implementation in ArcGIS. Let's dig in.     Periodic Bulk Replacement   My subject matter data is Overture Maps Foundation Division Area features, a global scale dataset released monthly.  The data model includes polygon geometry with a primary place name and a struct object with alternate names in many languages.  The source data at writing are ten GeoParquet files in AWS S3, with a common schema.  There is no logical partitioning.  The information product I want is a geodatabase feature class, related alternate name table plus a geocoding locator that understands all the names.  Here is what the feature data looks like over Europe: Division Areas To use my information product, for example if I want to find Madrid in Spain using the Bihari language (the popup shows available names for Madrid) I give मैड्रिड as the address: मैड्रिड finds Madrid A notebook is appropriate for the information product, you can find it in the blog download, and really its only trick is using the appropriate glob path to the GeoParquet data, see in this cell: sql = f"""create or replace temp view division_area_view as select id, names.primary as primary_name, class, subtype, region, country, version, is_land, is_territorial, bbox.xmin as xmin, bbox.ymin as ymin, bbox.xmax as xmax, bbox.ymax as ymax, division_id, geometry from read_parquet('s3://overturemaps-us-west-2/release/{release}/theme=divisions/type=division_area/*.parquet',filename=false, hive_partitioning=1) where {whereExp} order by country, ST_Area(geometry) desc;""" view = conn.sql(sql) DuckDB can use glob paths for local or remote data, and make remote queries with the S3 API, and these queries are parallelized across all files in the glob path.  Note that the path includes a release identifier, this is taken from a STAC catalog at run time.  This is the central theme of this post - when and how to use GeoParquet and DuckDB with data that changes at intervals.  In this case we don't know what records changed so cannot query for them easily, so we do a bulk extract. What if we do know which records changed?   Frequent Append and Upsert   My example "busy" data is 311 case data for San Francisco.  The dataset is continuously updated with thousands of cases a day opened, edited or closed, but not pruned, and goes back to 2008.  At writing the bulk download is 8 million features, seen here at 1:10000 scale: 311 Cases in San Francisco Many "event" datasets like this exist, so how might the dataset be efficiently delivered in a cloud native way?  The answer relies on the data having timestamp fields for when cases are opened, updated and closed, with the field updated_datetime being refreshed on any status change.  As the San Francisco open data site (and therefore the system of record behind it) has an API that supports querying then this can be done for changes based on updated_datetime.  Here is the approach taken in the tools shared in the blog download: Do an initial bulk download to a baseline GeoParquet file On a frequent schedule Query the existing GeoParquet file(s) for the maximum updated_datetime value Query the 311 system of record for records more recent than the maximum This is a fast query Write the query result to a new, additional GeoParquet file All GeoParquet files must be at the same glob path On demand, query the set of GeoParquet files to extract data of interest This query makes use of a simple but powerful SQL clause, so read on... Here is an example query where I extract into a memory feature class all 311 cases to-date for 2026 within a polygon - this took 4 seconds.  There are about 8500 features. San Francisco Query The query tool is a script tool, its secret sauce is the QUALIFY clause.  The GeoParquet files made from daily case data will have duplicates due to the case lifecycle (opened one day, edited another day, closed a later day) then we want only the most recent row per service_request_id value across all GeoParquet files - the QUALIFY clause when querying the parquet data does this for us. 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;""")   So now we have a simple way to deliver fast-changing data in a cloud native way with fast queries. What if we have quite big data but no way to query for changes?   Continual Insert, Update and Delete Edits   Data subject to heavy branch versioned editing is high value work for GIS, and while you can share the data state by giving access to its underlying feature services, adding a public mapping workload to the server will not be welcomed by the administrator.   It turns out you can share the state of the data, with support for time travel, using a cloud-native approach.  What enables this is the insert-only data model of branch versioning - the state of a feature at any moment is determined by GDB_FROM_DATE in combination with OBJECTID - the row with latest GDB_FROM_DATE for each unique value of OBJECTID is the current state of a feature, and if you query for earlier GDB_FROM_DATE values you get time travel.  The only trick is making GeoParquet files that represent edit moments, but then the QUALIFY clause comes to the rescue again to deliver the data you want. Here are two views of parcel data over the same extent and using the same source GeoParquet files. Current and earlier moments The left view is the latest moment, the right view is at an earlier moment, you can see a lot of parcel subdivision has taken place.  The data source retains the full data history, edits result in new GeoParquet files being added to a folder or cloud object store.  Here I have a baseline 1.46GB GeoParquet file for the original state of the data with a couple of small GeoParquet files containing edits over two weeks. GeoParquet files containing full data history Here is a manifest of the blog download file CloudNativeDataDistribution.zip: ImportCurrentDivisionAreas.ipynb notebook Downloads Overture Division Area features to your project home geodatabase Creates multi-lingual names for features in a related table Creates or refreshes a locator using Division Area features as reference data GetBaselineSF311 spatial ETL tool Extracts the full 311 dataset for San Francisco to GeoParquet Requires ArcGIS Data Interoperability Requires an account and app token GetUpdatesSF311 spatial ETL tool Extracts 311 case data more recent than any in an existing GeoParquet file Creates a new GeoParquet file Requires ArcGIS Data Interoperability Requires an account and app token Generate311Points script tool Demonstrates using the GeoParquet files to make a memory feature class ExtractCurrentParcels.ipynb notebook Demonstrate extracting the latest data state of GeoParquet files in a branch versioned data model ExtractEarlierParcels.ipynb notebook Demonstrate extracting an earlier data state of GeoParquet files in a branch versioned data model Not included, but available on request, are the tools used to build GeoParquet files for the parcel data.  Remember, where my sample tools are using local file storage for GeoParquet, in production you would use a cloud object store, like AWS S3. Now, while I hope to see you at the 2026 User Conference in San Diego, if you need more encouragement then here is sneak preview of a demo bringing Overture Building theme data into a scene. See if you can find a couple of easter egg script tools in the blog download 😉. Overture Buildings   ... View more

There are many ETL jobs you might want to trigger from your map and the patten we'll explore in this blog will help you get there. Here is one use case, running an ETL web tool that accepts area features from a map as an input.  The web tool happens to build a mobile geodatabase which is returned to my Pro session or Experience Builder analysis widget as a local file, but you might ETL data from anywhere to anywhere, in any format.  The data doesn't have to be in your map, or even in your ArcGIS Pro project. Note the web tool as shown here will not return a mobile geodatabase when run in map viewer, it would need to be zipped first.  This is simple to do with the mobile geodatabase writer, just write with the file name extension .geodatabase.zip and a ZIP file will be automatically created. Running an ETL web tool in Pro The blog download has my sample toolbox, so let's walk through the steps.  There is a model tool and an ETL tool in the blog download's toolbox, the model wraps the ETL tool.  I used ArcGIS Pro 3.6 and of course ArcGIS Data Interoperability for Pro 3.6. ModelBuilder wrapping the ETL Things start with a FeatureSet input parameter, in my case with polygon geometry intended to be the area(s) of interest containing any number of US cities.  At run time this lets you pick from a map layer, browse for data or create your own features from scratch.  The FeatureSet is then written as EsriJSON data to the path %scratchFolder%\esri.json.  If you're not familiar with ModelBuilder inline variable substitution then read about it here.  When the tool runs locally this path will be in the project scratch folder, and when it's a web tool, in the job scratch folder. Now for a little time travel.  Imagine you're building the above model and all you have is the FeatureSet input and the Features to JSON tool in the model.  Save and run it and you'll have a file esri.json in your scratch folder.  Now build your ETL tool (embedded, in the same toolbox as the model) that does what you want given the EsriJSON input.  Here is mine. ETL that reads EsriJSON and does stuff Like the annotation says, an EsriJSON reader (NB: with single path input property) brings in my map input, the features get aggregated (I want one feature, multiparts are OK) then projected however I need in my downstream processing.  In my case the resulting feature is used as an initiator and spatial filter in a FeatureReader, which extracts some data from Living Atlas and writes it to a mobile geodatabase with dynamic schema.  There is a trick though in the pathing of the data. Here I am editing user parameters in the ETL tool and the top one is a scripted parameter SCRATCHFOLDER that gets what ArcPy is using, either locally in Pro or in a job folder in a web tool.  Scripted parameters are evaluated before any data processing happens. SCRATCHFOLDER scripted parameter The next parameter is the source EsriJSON file path, which will be supplied by the model but to make sure I set the default path to use the SCRATCHFOLDER scripted parameter.  Note that you have to save the parameter state after adding SCRATCHFOLDER and reopen the dialog to use the value in other parameters. EsriJSON input parameter Now the trick, the output mobile geodatabase is written with overwrite permission to the scratch folder. Mobile geodatabase output to scratch folder Now back to the model.  The ETL tool is added and the EsriJSON file connected as its input.  The ETL tool output is written to %scratchFolder%\Extract.geodatabase. Back to the future The last step is to add a Calculate Value model tool that copies the output path to a new File type parameter.  This is necessary because the output has to be Derived for a web tool to return data.  Make sure the ETL tool output isn't set to intermediate in ModelBuilder or you'll get no output!  Check out the Calculate Value expression, it is necessary to protect the path from interpretation as having escape characters by making sure its a raw string. Calculate Value expression After some local test runs I had a result I could share as a web tool and then run.  When sharing the web tool I set the message level to Info so I can watch the action.  I used ArcGIS Enterprise 12.0, which is compatible with ArcGIS Pro 3.6. Make sure Data Interoperability is installed on your server and any packages and credentials installed on the server by the arcgis service owner user.   It is good practice to open fmeworkbench.exe as the arcgis service owner user (in the Data Interoperability install) on the server and test readers and writers you will be using. Web tool results view In the message stream above you can see the output written to a job scratch folder and it is also downloaded locally to my Pro session: Data is returned I don't have any file associations for .geodatabase files but I can open the link in Data Inspector: Mobile geodatabase in Data Inspector Now I have an ETL web tool I can share with my colleagues!  Speaking of colleagues I am indebted to @SashaLockamy  for her help putting this together. For clarity, the steps to building my web tool were: Make a model with a FeatureSet input parameter that writes EsriJSON to %scratchFolder%\esri.json Save and run the model to create the esri.json file Make an embedded ETL tool in the model's toolbox that reads EsriJSON and does your ETL If returning data it must be a file data type, and... Script the first parameter to return arcpy.env.scratchFolder Write the desired file output data to that folder Test the ETL tool using your esri.json file Add the ETL tool to the model Connect the EsriJSON output as the ETL tool input Add a Calculate Value tool to cast the ETL tool output to a File, Derived value Make sure the ETL tool output is not intermediate Save and run the model from the toolbox, not the model diagram Share the result as a web tool (or geoprocessing service if using a standalone server) Please do comment in the post with your experiences. ... View more