Hi Kirk,
As you yourself already found out, the Mobile Geodatabase uses ESRI's own ST_Geometry implementation (the link and all sublinks you found in the ArcMap Help that hasn't yet been ported to the Pro Help: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-the-st-geometry-...)
I have recently been experimenting with SQLite and ArcGIS Pro too, but using a different path. I use Python and arcpy to write out geometries to a SQLite database created with the Create SQLite Database geoprocessing tool (also see this link). The data involved is coming from OpenStreetMap, and is stored in a PostgreSQL / PostGIS database, that is NOT ESRI Enterprise Geodatabase enabled, just an ordinary spatial (PostGIS) database.
Note that the process described below DOES NOT work for the Mobile Geodatabase, just a plain SQLite database as created with the geoprocessing tool mentioned above, there appear to be additional dependencies that regulate data type validation that I haven't been able to figure out yet.
In order for the SQLite INSERT to work, I need to:
- Create a SQLite database using the Create SQLite Database geoprocessing tool.
- Load the stgeometry_sqlite.dll library that ESRI created and made available as a separate download on the My ESRI website (unfortunately, this library appears not part of the standard Pro installation) in Python using the sqlite3 Python package that is part of the default install of Pro's Python environment (you need to use enable_load_extension and load_extension for that in the sqlite3 Python module).
- Create a table with CREATE TABLE using a sqlite3 connection/cursor
- Add a geometry column to this table using the ESRI ST_Geometry library's AddGeometryColumn function and sqlite3 cursor.
- To use PostGIS ST_AsBinary with a psycopg2 cursor to read the PostGIS database and extract the features as WKB.
- Insert the geometries using a sqlite3 database connection / cursor using ST_GeomFromWKB. Note that ESRI's ST_Geometry library supports other options like ST_MPolyFromWKB as well for dedicated geometry types, but I haven't succeeded in using these properly, and the ST_GeomFromWKB "just works".
- Add a spatial index using the Add Spatial Index geoprocessing tool.
Note that implementing all of this was particular tough, this is certainly not for the faint of heart! It took me the better part of two weeks to get it running.
However, on the bright side, I am now capable of exporting geometries to a SQLite database at a rate of just over 100M records / hour! (yes, my current OpenStreetMap database is that big...). This is probably about 5x the speed or more of using an arcpy.da.SearchCursor and arcpy.da.InsertCursor that could do the same thing. This speed difference was crucial for me. This database can subsequently be read by ArcGIS Pro again.
By the way, as alternative to psycopg2, there is pyodbc, which supports a far wider range of databases besides PostgreSQL (e.g. SQL Server, Oracle), but lacks for example server side cursors. Both Python DB API adapters have their strengths and weaknesses...