Loading Data in to Versioned, Archiving tables on daily basis

[vijaybadugu]

Current Data Loading Approach:

Currently, I use the Append tool to load data. Initially, I truncate the entire table and then load the data using the Append tool. However, this approach causes the data to be inserted into delta tables, leading to their continuous growth, which may negatively impact overall database performance.

Why Did I Enable Versioning and Archiving?
We have a few feature classes that are part of a topology and are used in Field Maps with sync-enabled feature services. Additionally, we created several relational tables in the spatial database that are updated daily from a non-spatial database.

To publish these tables with sync-enabled services and display this relational data in Field Maps, we need to enable versioning and archiving on the tables.

How Should the Data Be Loaded into These Tables?

What are the best practices for updating this data daily?

Could you suggest a more efficient approach?

[VinceAngelo · Accepted solution]

Truncate and Append is pretty much worst case for a versioned geodatabase, particularly a TRUNCATE, since it will bypass the Delete event.

Best practice is also the trickiest -- Identify the changes (INSERT/UPDATE/DELETE) that need to occur to bring the data into sync, and issue commands against a versioned view to make it happen.

- V

[vijaybadugu]

In Append Tool, There is an option to match fields to update. However, If the records are not in source, those will be deleted them manually/ write a script method to delete them from target. so I thought of truncate and Append would be right option. Before that, I unregistered the feature class.  

[VinceAngelo]

I have batch scripts running in client sites several times a day, doing updates and inserts of existing and new rows. I use SQL to manage the change detection, using INSERT ... SELECT ... FROM ({virtual_table_query}) vt and UPDATE ... FROM ({virtual_table_query}) WHERE u.keycol = vt.keycol, editing both unversioned and versioned data. TRUNCATE/APPEND is too blunt an instrument, and unregistering the feature class defeats the purpose of versioning and archiving.

The key here is to populate a temporary table (which is truncated and appended, or sometimes only appended), then use SQL for the gentler bits.

- V

[vijaybadugu]

Insert in to Versioned View ( it inserts in to delta tables) or just table through SQL ? The problem Inserting in to table was encountered issues while  generating GLOBAL ID and OBJECT ID.  is there way to auto increment them using procedure? 

[VinceAngelo]

If the table is non-versioned, you're responsible for a serial or SEQUENCE to keep it clean. UUID generation is trivial.

- V

[vijaybadugu]

The table is versioned and archived. can we update this table through sql without inserting in to delta tables and _H tables ? I am using next_rowid and next_globalid to  insert data in to OBJECTID and GLOBALID. 

How to reset OBJECTID to 1 when using next_rowid ?

[RobertKrisher]

Inserting, updating, and deleting data directly using SQL is possible but is not recommended due to its complexity and risk especially when there is a controller dataset involved (topology, parcel fabric, utility network etc). The recommended, safe way to perform these updates is to make use of one an API (Python, .NET, REST, etc) that will manage the edits to the archive as well as ensure we maintain integrity with the controller dataset.

As an example, if you insert a new feature into a topology using SQL the row will be created in the database, but no dirty area will be created. This means that the topology will not be able to validate the feature.

For controller datasets that perform tracing the problem is significantly worse, because this means the feature will not be incorporated into the network index. Also, if you are making updates to features that affect the state of the network you will end up in a similar situation, with edits being made to features with no means to get them reflected in the network index.

If you make the same edit using our API, we will ensure that in addition to performing the requested edits we are also properly managing any system fields. If you find out later that you have made a mistake that affects your topology, and try to log a case with support, they will be unable to help you because your use of SQL instead of the use of the APIs.

[vijaybadugu]

Thanks for response. Updating a versioned, archived table on a weekly/daily basis using SQL instead of geoprocessing tools like truncate → append. Each update contains 70,000 records, which are stored in history (_H) and delta tables, causing growth until a compress operation is performed. This may impact overall geodatabase performance. I am considering updating the table through SQL without modifying the delta or archive tables. These tables are relational to versioned feature classes. Since they are versioned, I have enforced both versioning and archiving; otherwise, I would have only enabled archiving. 

These tables are independent not participating in any of network or topology. I was pulling the data from non spatial database , these tables are used in field maps to show the data .it has just relational class to map actual versioned feature class to this table

[RobertKrisher]

@vijaybadugu In your original post you said that some of the feature classes participated in a topology, so that's why I raised concerns about direct updates.

If you make direct edits to features using SQL then there will be no history for these edits captured in the GIS. This keeps table sizes down, but also means that if someone takes the data offline and tries to sync it, the server won't see that you've made any changes. This would require all your clients to do full downloads.

Doing transactional edits using our APIs will ensure that clients and systems downstream can use that history to perform incremental updates. The important thing is to be selective in your updates and only push inserts/updates/deletes as necessary. This will slow down the growth of the tables/history and will keep the size of the deltas for your field clients down.