enterprise-geodatabases
Skill2 starsChanged 4 days agoApache-2.0View on GitHub
Reading and safely working with Esri enterprise geodatabases (SDE) on Oracle, SQL Server and PostgreSQL, covering versions, reconcile and post, branch vs traditional versioning, a protected DEFAULT, domains and subtypes, relationship classes, attribute rules, archiving, ST_Geometry vs SDO_GEOMETRY vs SQL Server geometry, and versioned views vs base tables. Use it when asked to "query the SDE", "edit the enterprise geodatabase", "reconcile and post", "what do these codes mean", "why does SQL show different rows than ArcGIS Pro", or before touching any versioned or registered table.
In this repository15 other files it gives its agents.
- CLAUDE.mdCLAUDE.md
- arcpy-scriptingSkill
- crs-and-datumsSkill
- imagery-and-stacSkill
- lidar-with-pdalSkill
- linear-referencingSkill
- maps-and-deliverablesSkill
- open-data-sourcingSkill
- raster-and-terrainSkill
- sop-conversionSkill
- spatial-data-qaSkill
- spatial-joins-and-keysSkill
- spatial-sqlSkill
- working-with-columbusSkill
- writing-clearly-and-conciselySkill
The file
We split this skill into the rules it gives an agent
7 rules. About 0 are common, 4 are distinctive, and 3 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈4
Distinctive · estimate
This author's own choices. Worth a look.
3
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 18
DEFAULT is the published, trusted state of the data.
- Very specific: line 22
DEFAULT's access is usually protected: everyone can read it, only its owner (often SDE or the data owner) can edit it or post to it.
- Very specific: line 25
Do not edit registered tables with SQL (INSERT, UPDATE, DELETE) or with tools that bypass the geodatabase.
- Distinctive: line 29
If someone asks for a bulk fix, propose it as a reviewed edit in a named version.
- Distinctive: line 57
Delete the version: when done. In traditional versioning, the DBA compresses later.
- Distinctive: line 185
Write proposed edits as a list for a person to make in Pro.
- Very specific: line 222
Use it to label results and to QA values outside the domain.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Enterprise geodatabases
An enterprise geodatabase (EGDB, "SDE") is an ordinary Oracle, SQL Server or PostgreSQL database with Esri's system tables and rules on top. The rules (versions, domains, relationship classes, attribute rules, editor tracking) live in the geodatabase, not in the database, so SQL sees the tables but not the rules. Most mistakes come from forgetting that.
General practice (with or without Columbus)
1. The one rule: never edit DEFAULT directly
DEFAULT is the published, trusted state of the data. Edit in a named child version, check it, then reconcile and post. Even if you have the rights to edit DEFAULT, do not: there is no review step, no undo for other users, and a bad edit is live at once.
- DEFAULT's access is usually protected: everyone can read it, only its owner (often
SDEor the data owner) can edit it or post to it. A "protected" error when posting means you need the owner or a QA role to post. That is the design working, not a bug. - Do not edit registered tables with SQL (
INSERT,UPDATE,DELETE) or with tools that bypass the geodatabase. SQL skips domains, subtypes, attribute rules, relationship class behaviour (composite deletes, messaging), editor tracking and GlobalID generation, and on versioned data it can write rows that ArcGIS never shows. - If someone asks for a bulk fix, propose it as a reviewed edit in a named version.
2. Traditional vs branch versioning
| Traditional | Branch | |
| How edits are stored | Delta tables: adds A<registration_id>, deletes D<registration_id>; base table holds only compressed state | One table; every edit adds a row with GDB_ARCHIVE_OID, GDB_BRANCH_ID, GDB_FROM_DATE, GDB_IS_DELETE, GDB_DELETED_AT, GDB_DELETED_BY |
| Version tree | Any depth (DEFAULT > QA > editor) | One level: named versions are children of DEFAULT |
| Where you edit | Direct database connection in ArcGIS Pro, or services | Through feature services with the Version Management capability |
| Maintenance | Reconcile all, then compress regularly, or the state tree and delta tables grow and everything slows | No compress; history is kept by design |
| Required by | Legacy geometric networks, some older workflows | Utility Network and Parcel Fabric multi-user editing, web editing with versions |
Find which one a dataset uses: in Pro, the dataset's Properties (Versioning), or ask the
data owner. In ArcPy, arcpy.Describe(fc).isVersioned says whether it is versioned at all.
In SQL, branch versioned tables carry the GDB_BRANCH_ID field; traditional ones have
A<id>/D<id> delta tables and usually an _evw view.
3. Reconcile and post
- Create a version from DEFAULT (or from the QA version in a traditional tree):
arcpy.management.CreateVersion(sde_conn, "SDE.DEFAULT", "roads_fix_1104", "PRIVATE"). - Edit in that version, inside an edit session (see the arcpy-scripting skill).
- Reconcile: pull DEFAULT's newer changes into your version. Conflicts appear when the same feature (by object) or the same field (by attribute) changed in both.
- Review conflicts in Pro's Conflicts pane. Choose, per conflict, the edit version's value or the target's. Never resolve conflicts in bulk without reading them.
- Post: push your reconciled version into DEFAULT. Post only after a reconcile with no unresolved conflicts.
- Delete the version when done. In traditional versioning, the DBA compresses later.
arcpy.management.ReconcileVersions(
input_database=sde_conn,
reconcile_mode="ALL_VERSIONS", # or "BLOCKING_VERSIONS"
target_version="SDE.DEFAULT",
edit_versions="ANALYST.roads_fix_1104",
acquire_locks="LOCK_ACQUIRED",
abort_if_conflicts="ABORT_CONFLICTS", # stop and review rather than auto-resolve
conflict_definition="BY_OBJECT", # or "BY_ATTRIBUTE"
conflict_resolution="FAVOR_TARGET_VERSION",
with_post="NO_POST", # post only after a person has reviewed
with_delete="KEEP_VERSION",
out_log=r"C:\work\reconcile.log",
)Scripted reconcile with with_post="POST" and FAVOR_EDIT_VERSION silently overwrites other
people's work. Use it only for a documented, approved batch process.
4. Domains and subtypes
- Coded value domains map stored codes to labels (
1=Interstate). Range domains give a min and max. A domain is assigned to a field, and can differ per subtype. - Subtypes split one table into groups by an integer field (for example
SUBTYPE_CD). Each subtype can have its own default values and domains. - SQL returns codes, not labels. To report labels, join to the domain definition, which is
stored as XML in the geodatabase's
GDB_ITEMStable. Prefer reading domains witharcpy.da.ListDomains(sde_conn)(each hasname,domainTypeCodedValueorRange,codedValuesdict,range), and subtypes witharcpy.da.ListSubtypes(fc). - Domains are enforced only by ArcGIS clients when editing. Old or SQL-loaded rows may hold values outside the domain. When you QA a field, count values not in the domain.
- Code field types matter: a code stored as text
"01"will not match the number1.
5. Relationship classes
- A relationship class links an origin table's primary key to a destination table's
foreign key (often
GlobalIDto aGUIDfield). Cardinality is 1:1, 1:M or M:N; M:N and attributed relationships use an intermediate table. - Composite relationships delete destination rows when the origin is deleted, but only when the delete happens through ArcGIS. A SQL delete leaves orphans.
- In SQL, follow a relationship with an ordinary join on its keys. Find the keys with
arcpy.Describe(rel_class):originClassNames,destinationClassNames,cardinality,isComposite,originClassKeys. - QA check: destination rows whose foreign key matches no origin row (orphans).
6. Attribute rules
Arcade expressions the geodatabase runs on edits:
- Calculation rules fill fields (an ID from a sequence, a district from a spatial intersect). Immediate ones run on insert/update/delete; batch ones run later.
- Constraint rules reject an edit that breaks a condition.
- Validation rules flag existing features as errors for review (batch).
They run only for edits made through ArcGIS (Pro, services, ArcPy edit sessions). SQL edits
skip them, so a calculated field can be stale or empty. Batch calculation and validation
rules have extra requirements in an EGDB (typically branch versioning and services); check
before relying on them. Export the rules to read them:
arcpy.management.ExportAttributeRules(fc, r"C:\work\rules.csv").
7. Archiving
- Traditional versioned + archiving: history lives in an archive class
<name>_HwithGDB_FROM_DATE,GDB_TO_DATEandGDB_ARCHIVE_OID. Current rows haveGDB_TO_DATE = 9999-12-31 23:59:59. - Non-versioned + archiving: the same fields are added to the base table itself. A plain
SELECTreturns old and current rows together; filter onGDB_TO_DATEfor the current state. - Branch versioning keeps history by design (see section 2).
- Counting rows in an archived table without the filter overcounts. It is a common reason a SQL count disagrees with Pro.
8. Geometry storage
| Storage | Database | Read geometry as WKT | Notes |
Esri ST_Geometry | Oracle, PostgreSQL | sde.st_astext(shape) | Its SRID is Esri's own id from ST_SPATIAL_REFERENCES; the EPSG code is in that table's auth_srid. On Oracle, many functions need the st_shapelib external procedure; ORA-28595/ORA-06520 mean it is not configured. |
SDO_GEOMETRY (Oracle Spatial) | Oracle | SDO_UTIL.TO_WKTGEOMETRY(shape) | SRIDs may be Oracle legacy codes (8307 for WGS 84, 8265 for NAD83) rather than EPSG. Spatial operators need a spatial index and a tolerance. |
geometry / geography | SQL Server | shape.STAsText() | Methods are case-sensitive. geography::Point(lat, lon, srid) takes latitude first; WKT is lon lat. geography needs correct ring orientation. |
PostGIS geometry | PostgreSQL | ST_AsText(shape) | Optional storage for EGDBs on PostgreSQL. |
SDEBINARY / LOB | Oracle, legacy | not readable in SQL | Only through ArcGIS. Ask the DBA. |
More functions per type are in references/storage-and-sql.md,
and spatial predicates in the spatial-sql skill (../spatial-sql/SKILL.md).
Find a feature class's storage from the shape column's database type, or ask the DBA.
ArcGIS uses the configuration keyword (GEOMETRY_STORAGE in the DBTUNE settings) chosen
when the class was created.
9. Reading through views vs base tables
What you see depends on what you query:
| You query | You get |
| Base table of a traditional versioned class | The state as of the last compress. Edits since then (in delta tables) are missing, and deleted rows may still be there. Wrong for current data. |
Versioned view <name>_evw (traditional) | DEFAULT, including delta-table edits, unless the session set another version first |
| Base table of a branch versioned class | Every row version ever written, across branches. Esri does not support SQL against these; use the feature service or a view the data owner built. |
| Base table of a non-versioned, archived class | Current and historical rows; filter GDB_TO_DATE |
| Base table of a non-versioned, non-archived class | Current data |
| A database view or Esri-registered view | Whatever its owner defined. Read its definition or its context entry before trusting it. |
Reading a specific version through a versioned view needs a call first: Oracle
EXEC sde.version_util.set_current_version('ANALYST.roads_fix_1104');, SQL Server
EXEC sde.set_current_version 'ANALYST.roads_fix_1104';, PostgreSQL
SELECT sde.sde_set_current_version('ANALYST.roads_fix_1104');. Without it the view shows DEFAULT.
10. Before you answer anything from an EGDB
- Which table or view, and is the class versioned (which kind), archived, or neither?
- Which version does the answer describe? Say "DEFAULT as of
<time>" in the answer. - Are coded fields shown as codes or labels? Say which.
- Are qualified names in play (
ROADS.ROADWAY_INVENTORY, field names likeROADS.ROADWAY_INVENTORY.FUNC_CLASSafter a join)? Use the owner-qualified name. - Is geometry in a type your tool can read? If not, read attributes in SQL and geometry through ArcGIS or the feature service.
If the Columbus tools are available
Columbus enforces its rules in the server. Nothing here is a control; if it refuses something, tell the person what and why, and do not look for a way around it.
What works today and what does not.
whoami,list_connectionsanddescribe_connectionshow each connection's type and effective mode. Every connection is read-only until organization policy turns edits on, whatever the connection file says. There is no editing tool yet:propose_editsandapply_edits(edits in a named version, never DEFAULT) are planned and not available. Write proposed edits as a list for a person to make in Pro.- An EGDB's tables on Oracle (
oracle,sde-oracle) and SQL Server (sqlserver,sde-sqlserver,azure-sql) connections are read like any other table:list_datasets,describe_dataset,sample_rows,query_featuresandsql_selectall work, read-only.SDO_GEOMETRYand SQL Servergeometry/geographycome back as shapes. EsriST_Geometrycolumns cannot be read:describe_datasetraisesgeometry.unsupportedand their values come back as a placeholder (unsupported: SDE.ST_GEOMETRY); thesde.st_*functions are database code and are refused. Say so, and suggest reading that layer in ArcGIS Pro or asking the DBA for an SDO_GEOMETRY view. The same holds for versions:sql_selectcannot callsde.version_util, so a versioned view shows DEFAULT. - Connections of type
sde(through ArcPy) are configured and checked, but that engine is not built yet; the tools answer that reading it is not supported yet. Say so rather than guessing at the data. - An EGDB on PostgreSQL can be read today through a
postgisconnection if the person has added one. Then sections 7 to 9 matter directly: check whether the allowed dataset is a base table or an_evwview.sql_selectcannot set a version (the setter calls are not SELECTs, andsde.*functions such assde.st_astextare not on the function allow list), so a versioned view shows DEFAULT. PostGIS-stored geometry works withST_*functions; EsriST_Geometrycolumns cannot be converted or tested throughsql_select.
Use the context pack for meaning. The organization's pack says what tables, codes and versions mean, which SQL cannot:
describe_dataset {"connection": "<connection id>", "dataset": "<schema.table>"}attaches the dataset entry whose connection and physical match (keys, field aliases, each field's domain id,
gotchas such as "retired segments stay with STATUS = 'R'"). For code labels, read the
domain entry the field names:
context_search {"query": "functional class", "kind": "domain"}
context_get {"id": "functional-class"}A domain entry has type (coded or range) and values (code: label) or min/max.
Use it to label results and to QA values outside the domain. If an entry comes back
stale, say so, and do not present it as current. If the pack has no entry, say the codes
are unlabelled rather than inventing labels. context_search {"kind": "dataset"} also
finds datasets on connections whose engine is not built yet, so you can still explain what
a table holds and how to read it.
Columbus does not yet draft context entries from a geodatabase
(draft_context_from_geodatabase is planned), and it does not enforce domains in verify.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

