Learn how to set up a secure connection to Snowflake and map external interaction tables using the Reltio API.

1. Connect to Snowflake.

   Reltio supports secure integration with Snowflake to enable Zero Copy data access for interaction data. You can establish this connection using one of the following authentication methods, depending on your organization's security requirements:

   • Option 1: Key-pair authentication
   • Option 2: OAuth authentication
   1. Key-pair authentication

      In this step, you can create a Snowflake connection in Reltio by providing the Snowflake account (accountId) along with database, schema, and warehouse details. This information enables Reltio to locate and connect to the correct Snowflake environment.

      1. Step 1: Register the Snowflake connection

         Send a POST request to register a Snowflake connection:

         POST {Url}/reltio/api/tenants/{tenantId}/interactions/database-configs/
         

         Example request body:

         {
             "connection_name": "snowflake_1776064258806",
             "tenant_id": "ZerocopyE2E",
             "database_type": "SNOWFLAKE",
             "snowflake_config": {
                 "databaseName": "DPH_ASHWINI_TEST",
                 "schemaName": "ICEBERGTEST",
                 "role": "ACCOUNTADMIN",
                 "warehouse": "DEMO_WH",
                 "region": "us-east-1",
                 "accountUrl": "https://gweanbw-reltio_partner.snowflakecomputing.com",
                 "connectionProperties": {
                     "queryTimeout": 3000,
                     "maxPoolSize": 5
                 }
             }
         }

      2. Step 2: Configure key-pair authentication

         After registering the connection, configure authentication by associating a Snowflake user with a generated public key.

         Send a POST request to store credentials and receive the public key:

         POST {Url}/reltio/api/{tenantId}/interactions/credentials/snowflake/snowflake_1776064258806
         

         Example request body:

         {
           "username": "reltio_svc"
         }

         The response will include a public key and an SQL statement like:

         ALTER USER reltio_svc SET RSA_PUBLIC_KEY='<public-key>';

         Run this statement in the Snowflake account to complete the authentication setup.

   2. OAuth authentication

   OAuth APIs enable you to configure Snowflake OAuth integration by storing OAuth client credentials and generating the authorization URL required to complete the OAuth authorization flow.

   Before using OAuth APIs, create an OAuth security integration in Snowflake and obtain the client credentials.

   Create a Snowflake OAuth security integration and retrieve the client ID and client secret.

   -- 1. Create the OAuth integration
   CREATE SECURITY INTEGRATION <INTEGRATION_NAME>
     TYPE = OAUTH
     ENABLED = TRUE
     OAUTH_CLIENT = CUSTOM
     OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
     OAUTH_REDIRECT_URI = 'https://{{Env endpoint}}/data-out/snowflake/callback'
     OAUTH_ISSUE_REFRESH_TOKENS = TRUE
     OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
     OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE;
   
   -- 2. Verify that the integration was created
   DESC SECURITY INTEGRATION <INTEGRATION_NAME>;
   
   -- 3. Retrieve the client credentials
   SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<INTEGRATION_NAME>');

   Use the OAuth APIs to:

   • Store the Snowflake OAuth client ID and client secret for the Snowflake connection.
   • Generate the Snowflake authorization URL for the Snowflake account.
2. Grant required permissions in Snowflake.

   Execute the following SQL in Snowflake to assign read-only access:

   
   GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE READER_ROLE;
   GRANT USAGE ON DATABASE customerdb TO ROLE READER_ROLE;
   GRANT USAGE ON SCHEMA public TO ROLE READER_ROLE;
   GRANT SELECT ON TABLE transactions TO ROLE READER_ROLE;
   GRANT ROLE READER_ROLE TO USER reltio_svc;
             

3. Map the external table to an interaction type.

   Use the PUT endpoint to define the mapping between the Snowflake table and Reltio interaction type:

   PUT {Url}/reltio/api/tenants/{tenantId}/zero-copy-interactions/mappings/{connectionId}

   Example request body:

   {
     "interaction_type": "sales",
     "table_name": "transactions",
     "order_by_column": "TRANSACTIONTIME",
     "timestamp_column": "TRANSACTIONTIME",
     "database_name": "MY_DATABASE",
     "schema_name": "MY_SCHEMA",
     "member_mappings": [
       {
         "member_type": "Products",
         "column_name": "ITEMCODE"
       }
     ],
     "attributes": [
       {
         "attribute_display_name": "customerId",
         "column_name": "USERID"
       },
       {
         "attribute_display_name": "country",
         "column_name": "COUNTRY"
       }
     ]
   }
   

   This configuration enables federated query of the interaction data and maps it into Reltio Intelligent 360^TM timelines.

   Note: If you delete an interaction mapping and want to reuse the same definition, creating a new mapping with the same definition returns a deleted object. To understand how to reuse a previously deleted interaction mapping, see Restore a deleted Interaction mapping for Zero Copy Integration.