Connecting to Snowflake
Limited availability feature - Bulk exporting
If you're directly connecting your public facing Snowflake instance with the Celonis Platform, you can now take advantage of a bulk export for full extractions. This method offers significant reductions in extraction times without any alterations or new configurations needed on your end.
If you are interested in participating in this limited availability release, contact us at Celopeers.
For more information about direct connections between Snowflake and the Celonis Platform, see: Direct connections to Snowflake.
You can connect your public facing Snowflake instance to the Celonis Platform. This connection can either be direct or using an uplinked on-premise extractor, with you choice dictated by how much access you want to allow the Celonis Platform to have to your database.
If you want to connect to your public facing Snowflake instance using key pair authentication, you need to generate a private key in your Snowflake account. We recommend using an encrypted private key with a passphrase. This passphrase is then encrypted by the Celonis Platform, giving you an extra layer of security.
With access to a private key and passphrase, you can enter this in the Snowflake configuration settings: Step 4.
Or alternatively, this passphrase can be used when configuring an uplink connection between Snowflake and the Celonis Platform.
For more information about key pair authentication, including generating an encrypted private key, see: Snowflake docs: Key pair authentication.
If you are using Snowflake’s built-in identity provider, you can connect to your public-facing Snowflake instance using OAuth authentication.
Important
If your Snowflake instance uses an external identity provider for SSO authentication, OAuth is not supported. We recommend using Key Pair authentication instead.
To use OAuth authentication with Snowflake’s built-in identity provider:
Sign into your Snowflake account.
Note
This account must be able to run commands as both the USERADMIN role for user and role management tasks, and the ACCOUNTADMIN role for account-level operations. Ensure you are using the appropriate role when performing each step.
As the USERADMIN role, create a new user role and grant the required role permissions by running the following commands in the Worksheets window:
# Create a new role for celonis create role celonis_role; # Grant permissions for that role grant select on all tables in schema MY_DB.PUBLIC to role celonis_role;
As the USERADMIN role, create a new user and assign it the role by running the following commands in the Worksheets window:
# Create a new user for celonis create user celonis password='celonis123'; # Grant celonis permissions to celonis user grant role celonis_role to user celonis;
As the ACCOUNTADMIN role, create a security integration group by running the following commands in the Worksheets window:
# Create security integration for the oauth create security integration oauth_kp_int type = oauth enabled = true oauth_client = CUSTOM oauth_client_type = 'CONFIDENTIAL' oauth_redirect_uri = 'https://auth.redirect.celonis.cloud/snowflake_redirect' oauth_issue_refresh_tokens = true oauth_refresh_token_validity = 7776000; # Allow celonis user to use the integration as ACCOUNTADMIN ALTER USER celonis ADD DELEGATED AUTHORIZATION OF ROLE celonis_role TO SECURITY INTEGRATION oauth_kp_int;
As the ACCOUNTADMIN role, retrieve the Client ID and Client secret for the OAuth integration by running the following commands in the Worksheets window:
# Get the client-id & client-secret for the integration; Note that the integration name is case-sensitive and must be uppercase and enclosed in single quotes. select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_KP_INT');
Before creating a connection between your Snowflake instance and the Celonis Platform you must decide which connection type you use.
Direct connections to Snowflake
Use this when you want to allow the Celonis Platform to directly access your Snowflake instance.
Uplinked connections to Snowflake via an on-premise extractor
Use this when you don't want to or can't allow the Celonis Platform to directly access your Snowflake instance. The connection between Snowflake and Celonis is then established using an on-premise extractor that's installed within your network and ideally on a dedicated server.
The role of the extractor is to poll and fetch job requests from the Celonis Platform, before then submitting the execution information the the database via an SQL query. Once the data is retrieved from the database, the extractor fetches it and sends it back to the Celonis Platform. As such, the connection between the database and the Celonis Platform is always made by the extractor, which continuously queries the Celonis Platform for any extractions to execute.
For more information about the on-premise extractor (including system requirements), see: On-premise extractors.
The next step is to modify your network settings to allow the database extractor to communicate with Snowflake and the Celonis Platform.
The settings here are based on the connection type you defined in step 1:
Network settings for direct connections
The following network settings apply for direct connections:
Source system | Target system | Port | Protocol | Description |
---|---|---|---|---|
Celonis Platform | Snowflake | Depending on the database, typical ports are 5432 for Postgres and 30015 for HANA for example | TCP | JDBC connection from the Celonis Platform to the database. The port is the one you normally use to connect to the database. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
Network settings for uplinked connections
The following network settings apply for uplinked connections (via the on-premise extractor):
Source system | Target system | Port | Protocol | Description |
---|---|---|---|---|
On-premise extractor server | Snowflake | Depending on the database, typical ports are 5432 for Postgres and 30015 for HANA for example. | TCP | JDBC connection from on-premise extractor server to the database. The port is the one you normally use to connect to the database. |
On-premise extractor server | Celonis Platform | 443 | TCP | HTTPS connection from on-premise extractor server to Celonis cloud endpoint. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
Celonis Platform IP addresses depending on the cluster
The respective clusters use multiple IPs each, so you need to enable all three of them in your firewall configuration to connect the on-premise extractor server and the cloud endpoint.
For a complete list of inbound and outbound Celonis Platform IP addresses to be allowlisted if needed, see: Allowlisting domain names and IP addresses
Note
This step is only needed if your database is not reachable from the Celonis Platform.
This step is only needed if your database is not reachable from the Celonis Platform.
For more information, see: Setting up
If you would like to use a proxy (optional), see: Proxy settings for on-prem clients.
You can now create the connection between Snowflake and the Celonis Platform from your data pool diagram:
Click Data Connections.
Click Add Data Connection and select Connect to Data Source.
For direct connections, select Cloud - Snowflake.
For uplinked connections, select: On-Premise - Snowflake and then select your uplink (configured as part of step 1).
Configure the following connection details, with the options here depending on your choice of uplinked or direct connection:
Name: An internal reference for the data connection.
Uplink Connections: The name of the Uplink Extractor Server installed on your end.
Configuration type: Select Standard.
Host: The database server name or IP address of the database server.
Port: The port to connect to the database server.
Service Name: If applicable to a specific database type (e.g. Oracle), enter the Service Name (Alias) associated with the database server.
Additional Properties: Additional properties like validateCertificate=false.
Credentials: Select either standard, OAuth, or key pair.
If using OAuth authentication, you must supply the client ID and client secret configured in the prerequisites.
If using key pair authentication, you must supply the username, private key, and if applicable, the passphrase (for encrypted private keys).
When copying and pasting your private key from Snowflake into the Celonis Platform, you only need to include from '-----BEGIN PRIVATE KEY-----' to '-----END PRIVATE KEY-----' .
For example (a shortened version for illustration only):
Maximum number of parallel extractions: This is usually governed by the database type you are using. The default value chosen is 4.
Timeout for database connections: Timeout for all database connections created in this connection (specific to this connection only). This value will overwrite the local timeout (in application.yaml) in case of uplinked connection.
Click Test Connection and correct any highlighted issues.
Click Save.
If using OAuth: The OAuth workflow starts, asking you to sign into your Snowflake account and authorize the connection.