Issue Using Snowflake Tables in ArcGIS Pro

Question

Our database admins have created a table view in Snowflake that includes a field that should function as the ObjectID field in ArcGIS Pro.  However, whenever I bring the table into ArcGIS Pro, the properties for the table indicate that ObjectID: None.  Additionally, when I view the field properties, the field that should function as the ObjectID field is being interpreted in ArcGIS Pro as a Double instead of an Integer.

From what my database admins told me, it sounds like Snowflake approaches numeric data types slightly differently than other database systems.

I was wondering if anyone else has encountered this issue, and if so, how you were able to overcome it.  Our goal is to be able to bring the tables directly from Snowflake into ArcGIS Pro (and ArcGIS Enterprise), and join the tables to existing spatial datasets.

Thanks for any insights you may have.

MarlonAmaya · Answer

@Anonymous User

I am not sure I understand the need to use "Select by Attributes". Based on our doc, you would not be able to use CAST to convert to an Integer, since Pro does not map any of the supported snowflake datatypes to integer.

Here is the doc used on how we view data in snowflake using query layers.

https://pro.arcgis.com/en/pro-app/latest/help/mapping/layer-properties/create-a-query-layer-cloud-data-warehouse.htm

Marlon

Anonymous User · Answer

Thanks for the resources.  There is not even an ability to use "Select By Attributes" tool, because the table's ObjectID propertie's are "None."  Is there a way to use the CAST function or other method, to force ArcGIS Pro to process the OID field as Integer instead of Double?

Based upon the one link you provided, it appears Snowflake does not natively support the Integer data type for fields.  It's still unclear if we can even use their tables in ArcGIS Pro due to this limitation.

MarlonAmaya · Answer

Hi @Anonymous User ,

Snowflake data types that are supported by ArcGIS Pro.

* https://pro.arcgis.com/en/pro-app/latest/help/data/databases/cloud-data-warehouse-data-types.htm

Depending on the data type in snowflake for the ObjectID, you are seeing expected behavior with the ObjectID being mapped to Double. To overcome this, you can look into using the CAST operation in your SELECT query

* https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

In the following article, you can review the requirements for ArcGIS Pro to consider a unique identifier.

* https://pro.arcgis.com/en/pro-app/latest/help/mapping/layer-properties/choose-unique-identifier.htm* example: CAST(<field> as varchar(50))

Hope this helps.

Marlon

* https://pro.arcgis.com/en/pro-app/latest/help/data/databases/cloud-data-warehouse-data-types.htm