Access a Snowflake dynamic table in ArcGIS Pro

Summary

Snowflake is a cloud-based data warehouse platform that runs on supported cloud providers. Snowflake dynamic tables are physical tables stored within the Snowflake data warehouse and automatically refreshed based on a defined SQL query. Snowflake dynamic tables can be accessed by creating a Snowflake database connection and defining a query layer referencing the dynamic table.

Procedure

Prerequisites to successfully access a Snowflake dynamic table:
• Install and download the latest version of Snowflake ODBC Driver and Visual C++ Redistributable (x64) for Windows.
• Create a Snowflake Standard Edition account and select Amazon Web Services (AWS) as the cloud platform for the Snowflake account.
• Create a Snowflake dynamic table. Refer to Snowflake Documentation: CREATE DYNAMIC TABLE for more information.

1. Create a Snowflake database connection in ArcGIS Pro. On the Insert ribbon, in the Project group, click Connections > Database > New Database Connection.
2. Fill in the required information in the Database Connection window.
   1. On the Connection Properties tab, select Snowflake for Database Platform.
   2. In the Server field, enter the Account/Server URL value derived from the Snowflake Account Details page.
   3. For the Authentication Type field, select User. Enter the Snowflake username in the User Name field and the password in the Password field.
   4. Ensure Save User/Password is checked.
   5. For the Role field, enter the Snowflake role with the required privileges to access the target database and schema.
   6. For the Database field, select the database of the Snowflake dynamic table.
   7. For Schema, input the Snowflake dynamic table schema.
   8. For Warehouse, enter the Snowflake warehouse with the required permission.
   9. For Connection File Name, click the empty field to automatically populate the value.
3. Click Validate. The message 'Success' with a green check mark is returned to indicate the validation of the database connection properties is successful.
   
4. Click OK.
5. On the View ribbon, click Catalog Pane in the Windows group.
6. In the Catalog Pane, click the Project tab and expand the Databases folder. The Snowflake database is displayed in this folder, as shown in the example below.
   
7. On the Insert ribbon, in the Project group, click New Map to create a new map.
   
8. On the Map ribbon, in the Layer group, click Add Data and select Query Layer to open New Query Layer.
   
   1. For the Datasource field, click the drop-down menu and select the connected Snowflake database.
      
   2. For the Name field, enter a name for the layer.
   3. Paste the following SQL query in the Query field:

      SELECT
        CAST(ROW_NUMBER() OVER (ORDER BY CATEGORY) AS BIGINT) AS OBJECTID, 
        CATEGORY, -- <column name>
        TOTAL_AMOUNT  -- <column name>
        FROM SNOWFLAKE_LEARNING_DB.PUBLIC.DYNAMIC_SUMMARY 
        /*Replace SNOWFLAKE_LEARNING_DB = <database name>, PUBLIC = <schema name>, and DYNAMIC_SUMMARY = <table name>*/
      

      Note:
      The SQL code above is an example based on the workflow provided. Results may vary according to user input.

   4. Ensure the Let ArcGIS Pro discover spatial properties of the layer option is selected.
   5. Click Validate > Next > Finish.
9. On the View ribbon, in the Windows group, click Contents. Right-click the dynamic table in Standalone Tables and click Open.
   
10. Verify that OBJECTID, CATEGORY, and TOTAL_AMOUNT created in the Snowflake dynamic table are displayed in the attribute table, as demonstrated below.