Fail to validate table with GEOGRAPHY type in Databricks

[mahic]

The tool fails after importing and testing a table with GEOGRAPHY type in Databricks

Steps to reproduce

Step 1

CREATE TABLE catalog.schema.geotable (
    id INT,
    geo GEOGRAPHY(4326)
) USING DELTA;

Step 2
datacontract import databricks --table catalog.schema.geotable --output geotable.yaml

Step 3
datacontract test geotable.yaml

Results in following output

Testing geotable.yaml
Server: databricks (type=databricks, schema=schema, catalog=catalog)
╭────────┬────────────────────────────────────────────────────────┬───────┬────────────────────────────────────────────────────╮
│ Result │ Check                                                  │ Field │ Details                                            │
├────────┼────────────────────────────────────────────────────────┼───────┼────────────────────────────────────────────────────┤
│ failed │ Check that field 'geo' is present                      │ geo   │ Could not read model 'geotable':      │
│        │                                                        │       │ '4326'                                             │
│ failed │ Check that field geo has physical type geography(4326) │ geo   │ Could not read model 'geotable':      │
│        │                                                        │       │ '4326'                                             │
│ failed │ Check that field 'id' is present                       │ id    │ Could not read model 'geotable':      │
│        │                                                        │       │ '4326'                                             │
│ failed │ Check that field id has physical type int              │ id    │ Could not read model 'geotable':      │
│        │                                                        │       │ '4326'                                             │
╰────────┴────────────────────────────────────────────────────────┴───────┴────────────────────────────────────────────────────╯
🔴 data contract is invalid, found the following errors:
1) id Check that field 'id' is present: Could not read model 'geotable': '4326'
2) id Check that field id has physical type int: Could not read model 'geotable': '4326'
3) geo Check that field 'geo' is present: Could not read model 'geotable': '4326'
4) geo Check that field geo has physical type geography(4326): Could not read model 'geotable': '4326'

[jochenchrist]

Thanks for the clear reproduction — that made this quick to track down.

The cause is in ibis's Databricks type mapper, not in the import. ibis reads the first GEOGRAPHY type parameter as a geometry subtype (the PostGIS spelling GEOGRAPHY(POINT, 4326)), but Databricks declares only the SRID, GEOGRAPHY(4326), so it fails with KeyError: '4326'. ibis reflects the whole table in one pass, which is why every check of the model failed with that message, including the ones on id.

Fixed in #1484:

• GEOGRAPHY(<srid>) / GEOMETRY(<srid>) are now read correctly, so geo is checked like any other column and its physicalType: geography(4326) is compared against the Unity Catalog type.
• A column whose type ibis still cannot represent now degrades to unknown on its own, with a warning naming it, instead of failing the checks of every other column in the model.

While in there, datacontract import databricks no longer writes the databricksType custom property, which was a duplicate of physicalType. Your geotable.yaml will now come out as:

  properties:
  - name: id
    physicalType: int
    logicalType: integer
  - name: geo
    physicalType: geography(4326)

geo gets no logicalType, since ODCS has no geospatial logical type; the physical type check covers it.

This is on main and will go out with the next release. If you can try it from main against your table, I'd be glad to hear whether it now passes.

[mahic]

Great @jochenchrist

Works as expected in main branch.

Testing geo.yaml
Server: databricks (type=databricks, schema=schema, catalog=catalog)
╭────────┬────────────────────────────────────────────────────────┬───────┬─────────╮
│ Result │ Check                                                  │ Field │ Details │
├────────┼────────────────────────────────────────────────────────┼───────┼─────────┤
│ passed │ Check that field 'geo' is present                      │ geo   │         │
│ passed │ Check that field geo has physical type geography(4326) │ geo   │         │
│ passed │ Check that field 'id' is present                       │ id    │         │
│ passed │ Check that field id has physical type int              │ id    │         │
╰────────┴────────────────────────────────────────────────────────┴───────┴─────────╯

I expect logicalType geo to land from RFC-0048 in upcoming version of ODCS.