ETL Process for SQL Tables

Question

My DB Admins are not familiar with ArcSDE so I am trying to find guidance on proper ways to load data into SQL server tables that are registered with SDE. The data are geocoded points from another non SDE database and have a geometry filed for location data. After I register the table it is adding a new column called [GDB_GEOMATTR_DATA].

So the admins have a nightly process to truncate ,insert all the rows, and finally update to fill geometry column. This is done purely in SQL and is not using ArcGIS to load any data. That will leave the [GDB_GEOMATTR_DATA] column empty. So my question is what is the proper ETL strategy with a registered table and can we perform the ETL without needing ArcGIS or ArcPy?

Mike_Redpath · Answer

The GDB_GEOMATTR_DATA column is used to store Esri-specific higher-order or alternate geometry representations (for example, parametric curves, CAD-derived objects, or annotation) that may not be fully represented in the primary vector geometry column. There are no public APIs or SQL-level functions for directly creating or reading its contents. It is system-managed, is never populated manually, and can safely be left null.

BrianLeroux · Answer

Vince - Thank you for this info. I will pass it along to our DBAs to see if we can build a better process.

We had registered the tables as we thought the performance would be better for our web services.

VinceAngelo · Answer

Brian --

There are potential issues to managing records in a registered table in this manner. The easiest solution is to just leave the feature class unregistered, and access it as a Query Layer.  But make sure you recluster the table, since doing this sort of "load, then update geometry" process thoroughly fragments the table.

Truncate and reload is not a very efficient process, and leaves any service looking at the table during that transition somewhat bereft. I prefer to do change detection, and only INSERT new records, UPDATE changed records, and DELETE removed ones. I'm doing this daily with tens of millions of rows and hundreds of thousands of potential changes, in just minutes over the time it takes to read the new records.

- V