Mirroring a weekly CSV drop into an enterprise geodatabase — truncate-and-reload or a differential edit, and the documented rules that choose for you

Every week another system hands you a set of CSV files. Each file is the complete, current state of one table, and your job is to make the matching table in an enterprise geodatabase look exactly like it: new rows in, changed rows updated, vanished rows gone. An Esri Community thread describes precisely this job: more than 80 CSV files a week, landing in non-spatial tables that nothing else edits, registered with a SQL Server enterprise geodatabase. When we read it on September 17, 2026, none of its answers had been accepted. Having found raw SQL against the registered tables problematic, its author weighs four options: the ArcGIS API for Python, truncate-and-reload, Append with its upsert, and ArcPy cursors. This post is mostly about choosing between the last three, and the short answer is that documented constraints make that choice, not preference.

Everything below was checked against the ArcGIS Pro 3.7 documentation, the service section against the ArcGIS API for Python reference, and the cost note against the ArcGIS Online credits page. Where we say measured, we ran it on ArcGIS Pro 3.7.1 against file and mobile geodatabases, not against SQL Server, and we label each measured result.

There is no mirror tool, only building blocks

We did not find an Esri page that describes one ArcPy pattern for inserting, updating and deleting against a full-state drop in a single pass. What the reference does document is the parts:

• Truncate Table removes all rows “using truncate procedures in the database.” If a selection is applied to the layer or table view you pass it, the selection is ignored and every row goes.
• Append adds rows, and with its match_fields parameter (in the reference since Pro 3.1) it updates the target rows that match and inserts the rest — the tool page calls it the equivalent of an UPSERT. It never deletes a target row that is missing from the input.
• arcpy.da.UpdateCursor changes rows with updateRow() and removes them with deleteRow(). There is no arcpy.da.DeleteCursor; a cursor delete goes through the update cursor.
• arcpy.da.InsertCursor adds rows.
• arcpy.da.Editor wraps edits in an edit session, which the reference describes as the way to “manage database transactions.”

The tools whose names suggest they could compute the delta for you cannot. Table Compare and Feature Compare are only effective at identifying the first addition or deletion they meet. Detect Feature Changes works on line features and requires an Advanced license. The Data Loading toolset migrates data to a new schema; it does not synchronize changes. Working out which rows were added, changed and removed is your script’s job.

The first question: how is the target table versioned?

The thread never says, and no single fact decides more. Ask whoever administers the geodatabase before you write a line of code.

One detail in that table is easy to get backwards. The multiuser_mode argument of arcpy.da.Editor defaults to True, the versioned setting. The reference says to set it to False when editing a nonversioned table, and that with False, editing versioned data raises an error. Pass it explicitly either way.

If the tables are new, Esri’s guidance on the first load is also worth knowing: regardless of the versioning type, it recommends loading all the data before registering the dataset as versioned.

What rules out truncate-and-reload

On a table that is not versioned, both approaches are possible in principle. Then the Truncate Table reference and its neighbors start removing options. Each row below is either a hard refusal or a silent change for something downstream.

The message does not point at the likely cause. For the attachment-enabled table, the attachment-enabled feature class and the composite relationship origin, Pro 3.7.1 returned the same error, ERROR 160592: Truncation not allowed while editing., with no edit session open in any of the three cases. Esri’s page for that error explains more than the message does: it also covers a table that requires an edit session to make edits, such as a table with attachments.

If you see that from a script that never started editing, look for attachments and relationship classes on the target, not for a stray edit session.

There is also a window to think about. The Truncate Table page says truncate commands do not use database transactions, and the Load data page says a load into nonversioned data commits as a single transaction that other users see on their next refresh. Read together, those two sentences mean that between the truncate and the load’s commit, anyone reading the table can find it empty, and a load that fails after the truncate leaves it empty, or part-loaded if the job loads in more than one call, until the job succeeds. We did not measure this with a concurrent reader; it follows from the two documented behaviors. A differential edit, by contrast, commits its changes when the edit session stops.

When truncate-and-reload is the right call

Truncate Table documents its own use case: “workflows in which all rows will be removed from a table or feature class and there is no requirement to back up the transactions, such as the nightly reloading of data.” That description fits a table that is not versioned or archived, has no attachments, is in no relationship class and is not identified anywhere by ObjectID or GlobalID, loaded by a job that connects as the owner. For that table the reload is simpler, and both Truncate Table and Append run at every ArcGIS Pro license level.

Because the truncate cannot be undone, check the drop before it runs: that the file is there, is not empty, and holds about as many rows as last week. That check is our recommendation, not Esri’s documentation. The reload also has to survive the CSV itself, which is the first of the three traps below.

Three Pro 3.7.1 traps a mirror has to design around

1. ArcGIS decides your key is a number

ArcGIS reads a .csv file as a comma-delimited table and honors a schema.ini file for column types. Without one, Pro 3.7.1 read our test drop’s business key and ZIP code columns as Integer: the key 00101 came back as 101, the ZIP 01234 as 1234, and no schema.ini was written.

Quoting the values did not help. The text-file page says that values bracketed with double quotes are interpreted as text fields, but on 3.7.1, with no schema.ini present, a file whose bytes were "00101","01234",10.5 still produced Integer fields holding 101 and 1234. What did work was a schema.ini beside the CSV with a section for that file declaring all of its columns, the keys as text (for example Col1=BIZ_KEY Text). Reading the file with Python’s own csv module also worked: it returns every value as a string and kept 00101 intact.

2. Append’s upsert, fed a CSV, duplicates rows

On Pro 3.7.1, Append with match_fields reading a CSV directly updated the matching rows and inserted them again. It reported appended_row_count 3 and updated_row_count 0, and left duplicate keys behind. We reproduced it with and without a field map, with and without a schema.ini, and with skip_options="NO_SKIP". The same rows staged into a file geodatabase table first gave the documented result: 1 appended, 2 updated, no duplicates. We found no documentation either way and did not test other Pro builds, so treat it as a 3.7.1 observation, and stage the CSV into a geodatabase table before any upsert.

Even working as documented, the upsert has two properties that matter for a mirror. It never deletes, so you still need a delete pass. And it rewrites every matched row: in our 3.7.1 test on file and mobile geodatabases, an upsert of an identical copy of the data updated 3 of 3 rows and moved last_edited_date on all three. That matches the editor tracking rule that new values are recorded when a row is “modified in any way.” If anything downstream uses last_edited_date to find what changed this week, a weekly upsert tells it everything did.

3. Cursor edits outside an edit session

Three measured results, all on Pro 3.7.1 file and mobile geodatabases:

• Opening an update cursor and an insert cursor on the same workspace at the same time, outside an edit session, raised RuntimeError: workspace already in transaction mode. The reference documents that simultaneous insert or update cursors require an edit session.
• An insert cursor on a table or feature class with attachments, and a delete on the origin of a composite relationship class, raised RuntimeError: Objects in this class cannot be updated outside an edit session. The Editor reference lists “some dataset types” that can only be edited within an edit session; neither of these is named in that list, though the ERROR 160592 page does name a table with attachments as one that requires an edit session. The same reference gives another cause for that message: multiuser_mode set wrong for how the data is versioned.
• The silent one: deleting the origin row of a simple relationship class through a cursor outside an edit session did not set the child foreign keys to null. Inside arcpy.da.Editor, and with the Delete Rows tool, it did, which is the documented behavior.

The rule that falls out is short: every cursor edit a mirror makes goes inside arcpy.da.Editor. Truncate Table is the exception: on 3.7.1 it refused to run inside one.

A differential edit that designs around all three traps

This is the script we ran on Pro 3.7.1. It reads the drop with Python’s csv module, compares it with the table key by key, and makes every change inside one edit session.

"""Mirror a full-state CSV drop into a geodatabase table: inserts, updates and deletes in one edit session."""
import csv
import datetime

import arcpy


def _text(value):
    return value if value != "" else None


def read_drop(csv_path, key, fields, converters):
    """Read the drop with Python's csv module, so text keys keep their leading zeros."""
    rows = {}
    with open(csv_path, newline="", encoding="utf-8-sig") as fh:
        for record in csv.DictReader(fh):
            k = record[key]
            if k in rows:
                raise ValueError(f"duplicate key {k!r} in {csv_path}")
            rows[k] = tuple(converters.get(f, _text)(record[f]) for f in fields)
    return rows


def mirror(csv_path, target, key, fields, converters, workspace, multiuser_mode=False):
    incoming = read_drop(csv_path, key, fields, converters)
    inserted = updated = deleted = 0
    with arcpy.da.Editor(workspace, multiuser_mode=multiuser_mode):
        with arcpy.da.UpdateCursor(target, [key, *fields]) as cursor:
            for row in cursor:
                new_values = incoming.pop(row[0], None)
                if new_values is None:
                    cursor.deleteRow()
                    deleted += 1
                elif tuple(row[1:]) != new_values:
                    cursor.updateRow([row[0], *new_values])
                    updated += 1
        with arcpy.da.InsertCursor(target, [key, *fields]) as cursor:
            for k, values in incoming.items():
                cursor.insertRow([k, *values])
                inserted += 1
    return inserted, updated, deleted


CONVERTERS = {
    "AMOUNT": lambda v: float(v) if v != "" else None,
    "ASOF": lambda v: datetime.datetime.fromisoformat(v) if v != "" else None,
}

How it works, in order:

1. Read the drop as text. read_drop() reads every value as a string with Python’s csv module, so 00101 stays 00101, and keys a dictionary on the business key. A key that appears twice in the drop stops the run before anything is edited. Every other column stays text unless you give it a converter; the example converts an amount and a date. The key itself is never converted.
2. Open one edit session. multiuser_mode=False is the documented setting for a nonversioned table. For traditional versioning, pass True. Used as a with block, the Editor saves the session when the block succeeds and stops editing without saving if an exception is raised. A nonversioned edit session has no undo or redo, so that exit is your rollback, and it only rolls back a run that raises.
3. Walk the table once. For each row, pop() the key out of the drop. No match means the row has left the source, so it is deleted. A match with different values is updated. A match with identical values is left alone, which is what keeps last_edited_date honest.
4. Insert what is left. Anything still in the dictionary is new. The insert cursor opens only after the update cursor’s block has closed, so the two are never open at once, and neither is used after its with block. For how cursor lifetime changed in Pro 3.7, see the cursor scope post.

We ran it against a table with GlobalIDs, UTC editor tracking and a simple relationship class keyed on GlobalID. The second-week drop added one row, changed one, left one unchanged and dropped one, and the run returned (1, 1, 1). The changed row kept its ObjectID, its GlobalID and its created_date, and only its last_edited_date moved. The unchanged row kept all four values. The child rows of the two surviving parents still resolved to them; the child of the removed parent was left with a null foreign key, which is the documented simple-relationship delete rule applied inside the edit session. In a composite relationship the same delete removes the child rows. Running the same drop again returned (0, 0, 0).

Know its limits before you schedule it:

• An empty drop deletes every row. A zero-byte or header-only file reads as zero rows without an error, and a file cut off at a line break reads as a shorter drop. The script cannot tell either from a real mass deletion, and because nothing raised, the edit session saves the deletes. Before you schedule it, add a guard: refuse an empty drop when the table is not empty, and raise inside the edit session when deletes pass a limit you set for each table, which discards the session. That guard is our recommendation, not Esri’s documentation, and it is not in the script above.
• The key is compared as text. If the target’s key field is numeric, or the source pads or trims its keys differently from the table, nothing matches: every row is deleted and inserted again each week, with new ObjectIDs and GlobalIDs. Use a text key field, or normalize both sides before comparing.
• Empty cells and field defaults. Both cursors leave their explicit argument at its default of False, and the cursor reference says a field’s default value is applied when the value is None. An empty cell in a field that has a default is therefore written as the default, does not match next week, and is rewritten every run. Pass explicit=True to both cursors, or avoid defaults on mirrored fields. We derived this from the reference; we did not measure it.
• It has not been run against SQL Server or any enterprise geodatabase. It was executed on file and mobile geodatabases on Pro 3.7.1. Run it against a copy of your target first.
• It holds the whole drop in memory, as a dictionary keyed on the business key.
• Duplicate keys already in the target do not survive. The first occurrence takes the drop’s values; every later occurrence finds nothing left to match and is deleted.
• Comparison is exact. Your converters must produce the same types and precision the cursor returns, or every row will look changed. That held for the Double and Date fields we tested on file and mobile geodatabases; an enterprise geodatabase may store them differently, so confirm that a second run returns (0, 0, 0) there too.
• Each call mirrors one table in its own edit session. With 80 files, a failure partway through leaves some tables at this week’s state and the rest at last week’s. Running the whole set again is the recovery: tables already mirrored should return (0, 0, 0), once you have confirmed that on your target. Whoever reads the tables should know a week can land partially.
• It does not apply to branch-versioned tables, which cannot be edited over a database connection.

On speed: Esri documents that edit sessions batch updates, which it describes as offering significant performance advantages when editing enterprise geodatabases. We found no Esri throughput figures comparing cursors with Append, and the timings in the thread’s replies could not be reproduced without an enterprise geodatabase, so we are not repeating them.

Why not go through a feature service?

The first alternative the thread lists is the ArcGIS API for Python. Its edit_features method does add, update and delete in a single call, but against a feature layer or table in a service, not over a database connection, and the reference says that for 250 or more records at once, append should be used instead. The same reference describes append as updating hosted feature layers, available in ArcGIS Online and ArcGIS Enterprise 10.8.1 and later. And the Append tool page is explicit about services that sit on a geodatabase: use a database connection to perform large data loading operations directly to the underlying database. For a registered table you can reach over a database connection, the service is the long way around; for a branch-versioned table, a web feature layer is the only way in.

Running it every week

Scheduling. ArcGIS Pro schedules geoprocessing tools, at every license level, through Windows Task Scheduler, so the mirror has to be wrapped in a script tool to be scheduled from Pro. For enterprise geodatabase data, the connection has to be made with Save User/Password checked. With a Named User license, check Sign in automatically, which Esri says is required for the tool to run outside ArcGIS Pro. That license also brings a token with a maximum expiration set by your administrator, and if that interrupts unattended runs, Esri suggests a single-use license where possible. Running a task whether or not the user is logged on requires the run-as account to be in the local administrators group, and Esri advises considering a generic user account for critical scheduled tasks.

Credentials. A saved password in a connection file is a credential at rest. Our recommendation, not Esri’s documentation: where your DBMS allows operating system authentication, use it for this account instead.

Least privilege. This is where the choice of approach and the security model meet. A differential edit can run as a data editor rather than the owner. On SQL Server, Esri’s minimum for a data editor is SELECT, INSERT, UPDATE and DELETE on the datasets plus EXECUTE on the stored procedures associated with the data, and in a relationship class the privileges go on both the origin and the destination. A truncate-and-reload needs a connection as the data owner, and only the owner can alter the schema. Editor tracking records the name the job connects as: the database user, or the operating system login under operating system authentication. That name is what appears as the last editor on every row the job inserts or changes. The grants are not the whole boundary, though: anyone who can write to the drop location can rewrite or empty every mirrored table through this job, so restrict who can write there. That last point is our recommendation.

After the run. Both Analyze Datasets and Rebuild Indexes say that after data loading, deleting and updating it is important to run them. Analyze Datasets has to connect as the owner of the datasets, so it cannot run under the mirror’s data editor account: schedule it as a separate job that connects as the owner, or leave statistics to the database administrator’s own maintenance. Updating statistics is I/O-intensive, so schedule it for when database traffic is at its lightest.

Licensing. Truncate Table, Append and Delete Rows run at every Pro license level. Analyze Datasets, Rebuild Indexes and Register With Geodatabase need Standard or Advanced, as does creating tables in an enterprise geodatabase if the job bootstraps missing targets, and that step needs an account allowed to create tables, not the mirror’s data editor. On traditional-versioned tables, check the license for the reconcile, post and compress steps too. If your tables participate in relationship classes, check your seat: Pro’s help message for editing data in relationship classes says it requires additional licensing and carries a Standard or Advanced banner.

Cost. ArcGIS Online credits are consumed by specific transactions and types of storage, and this job does not call ArcGIS Online: it edits the database over a connection. What it costs is database I/O and an ArcGIS Pro license for the account that runs it, at Standard or Advanced if the same job runs Analyze Datasets or Rebuild Indexes.

Before the first scheduled run

1. Ask how each target table is versioned, and whether archiving is on. Branch-versioned rules out both approaches over a database connection; traditional rules out the truncate.
2. List what hangs off the table. Attachments, relationship classes (we tested GlobalID and ObjectID keys), and anything else that remembers ObjectIDs or GlobalIDs each rule out the reload.
3. Pick the accounts. Owner for a reload; a data editor is enough for a differential edit, with statistics and index maintenance run separately as the owner.
4. Pin the CSV types. Read with Python’s csv module, or ship a schema.ini that declares keys as text. Quoting the values was not enough on 3.7.1.
5. Never point an Append upsert at the CSV itself on 3.7.1. Stage it into a geodatabase table first.
6. Put every cursor edit inside arcpy.da.Editor, with multiuser_mode set for how the table is versioned. A reload’s truncate is the exception: it runs outside, and it cannot be undone.
7. Guard against a bad drop. Refuse a missing or empty file, and cap deletes for each table.
8. Prove it on a copy. For a differential edit, run a week, run the same drop again and expect (0, 0, 0), then feed it an empty file and expect a refusal. For a reload, check that the row count and keys match the drop and that GlobalIDs changed, and do not use ObjectIDs to tell one week’s rows from the last.
9. Refresh statistics and indexes afterwards, in the quiet window, under the owner connection.

Esri does not hand you a mirror, and the thread asking for one had no accepted answer when we read it. Esri does hand you documented rules, and together with the 3.7.1 behavior we measured, they leave the reload standing only for the plainest tables. Find out which kind of table you have before the first weekly run, design around the three traps and guard against an empty drop. Then the job becomes the boring kind: one edit session per table, three counts you log, and a second run that changes nothing.

References

• ArcGIS Pro: Truncate Table (data owner, versioned data, attachments, archiving, no transactions, and the nightly-reload use case)
• ArcGIS Pro: Append (match_fields as an UPSERT, Preserve GlobalIDs, and large loads through a database connection)
• ArcPy: Editor (multiuser_mode, the with-block save and discard, and edit-session batching)
• ArcPy: UpdateCursor (deleteRow, updateRow, and simultaneous cursors requiring an edit session)
• ArcPy: InsertCursor (the explicit argument and field default values)
• ArcGIS Pro: Tool error 160592 (truncation refused while editing, including tables that require an edit session)
• ArcGIS Pro: Privileges for geodatabases in SQL Server (the data editor minimum, including EXECUTE on stored procedures)
• ArcGIS Pro: Load data into existing tables (single-transaction commits, versioned loads, and the privileges table)
• ArcGIS Pro: Relationship class types (foreign keys set to null in simple relationships, cascade delete in composite ones)
• ArcGIS Pro: The archive process (how deletes and updates retire rows instead of removing them)
• ArcGIS Pro: Grant dataset privileges (branch-versioned data edited only through web feature layers)
• ArcGIS Pro: Registering and unregistering data as versioned (the exclusive lock, edits deleted before they are posted, and unique indexes)
• ArcGIS Pro: Use an ASCII or text file table (comma-delimited reading, schema.ini, and the double-quote rule that 3.7.1 did not follow)
• ArcGIS Pro: Schedule geoprocessing tools (Windows Task Scheduler, saved credentials, and Named User token expiry)
• ArcGIS Pro: Analyze Datasets (statistics after loads, deletes and updates, owner connection, license level)
• ArcGIS Online: Understand credits (what consumes credits, for the cost note)
• ArcGIS API for Python: arcgis.features (edit_features, the 250-record guidance, and FeatureLayer.append)
• Esri Community: mirroring complete weekly CSV drops to a registered enterprise geodatabase table (the thread this post answers; no accepted answer when read on September 17, 2026)