/
(SM-2502) Snowflake Storage Setup

(SM-2502) Snowflake Storage Setup

This page provides general guidelines on how to set up Snowflake Storage to work with Storage Management.

With the introduction of Snowpipe Streaming, the connection setup procedure differs slightly from the previous Batch Load data replication method.
The crucial differentiator is whether Snowflake Ingest SDK is to be used or not. Because this SDK contains an internal driver manager conflicting with the default one used by the SNP Java Connector, the Snowpipe Streaming needs to be fully separated from any other connections.
This is further explained in the instructions below.

1. General prerequisites

1.1 Network Connectivity

To enable SAP systems to communicate with the Snowflake environment, a set of hosts/ports related to your Snowflake account must be reachable from the SAP system.

You can execute SELECT SYSTEM$ALLOWLIST(); or SELECT SYSTEM$ALLOWLIST_PRIVATELINK(); to generate a CSV file with a list of hosts and ports that are involved in the communication. All communication is outbound from the SAP perspective.

For more information, see the official Snowflake Documentation.

1.2 Warehouse and Database

The Snowflake warehouse and database must already exist.

1.3 Snowflake user

You must create Snowflake technical users with full privileges on the chosen Snowflake database schema.
The recommended privilege for Snowflake technical users is OWNERSHIP of the Snowflake database.
If you cannot assign ownership of the database to a technical user, the minimum requirements are as follows:

GRANT ALL ON DATABASE <DATABASE> TO ROLE <ROLE> GRANT USAGE ON WAREHOUSE <WAREHOUSE> TO ROLE <ROLE> GRANT ALL ON SCHEMA <SCHEMA> TO ROLE <ROLE>

We recommend using a one-to-one relationship between Snowflake schemas and every SAP system connected. This ensures that the replicated data is isolated.

2. OS prerequisites (On SAP host)

This group of requirements relates to the operating systems underlying the SAP system, with all its application servers.
SNP products such as SNP Glue and SNP Outboard Datatiering have been developed and tested in the SUSE Linux environment and using Windows Server 2012.
However, by design, these products are not limited by the choice of operating system if the requirements listed in this user guide are met.

2.1 OS Directory with JDBC Driver and/or Snowflake Ingest SDK

The JDBC protocol is used to connect to Snowflake. You must copy the Snowflake JDBC driver to the operating system manually, and it must be accessible to the connector. To do so, download the Snowflake JDBC driver.

We recommend using the latest version of the Snowflake JDBC driver (snowflake-jdbc-3.20.0.jar) for the Snowpipe Streaming storage connection in combination with snowflake-ingest-sdk-3.0.0.jar.
We recommend storing the drivers in a file system that is shared between the application servers, organized into subdirectories to avoid possible conflicts. This is because some customers connect to multiple different platforms and services. Important information is that while the path to the driver directory is defined in the storage connection (SAP GUI), the drivers themselves are loaded by the SNP Java Connector.

Sample directory with the JDBC driver for the standard Batch Load method:

$ ls -l /sapmnt/<SID>/drivers/snowflake -rwxr-xr-x 1 <sid>adm root 78671124 Jan 15 09:45 snowflake-jdbc-3.20.0.jar

Sample directory with both the JDBC driver and the Snowflake Ingest SDK for Snowpipe Streaming:

$ ls -l /sapmnt/<SID>/drivers/snowflake_streaming -rw-r--r-- 1 <sid>adm sapsys 57903156 Dec 1 16:31 snowflake-ingest-sdk-3.0.0.jar -rw-r--r-- 1 <sid>adm sapsys 78671124 Jan 31 11:53 snowflake-jdbc-3.20.0.jar

Set its ownership and permissions appropriately to <sid>adm[:sapsys].

For compatibility with the SNP Java Connector, the Snowflake JDBC driver file name must begin with the prefix snowflake-jdbc. If multiple versions of the Snowflake driver exist, the latest version is used. We recommend keeping only one version of the JDBC driver within the Snowflake driver directory.

The Snowflake Ingest SDK JAR file name must begin with the prefix snowflake-ingest. Similarly, if multiple versions of the Snowflake Ingest SDK exist, the latest version is used. We recommend keeping only one version of both the JDBC driver and the Snowflake Ingest SDK within the Snowflake Streaming driver directory.

Note that there was an issue with the SSL handshake in older versions of the driver (3.13.x). This issue was fixed in version snow-jdbc-3.13.21.

3. SAP Configuration

3.1 JAVA Connector

The SNP Java Connector is a critical middleware component. Follow the steps in the Setting up the SNP Java Connector chapter before continuing. 

Snowflake JDBC driver requirements restrict the Java runtime environment. The driver must be installed in a 64-bit environment and requires Java 1.8 or higher. For more information, see the official Snowflake documentation.

If you are running SNP Java Connector on JDK 16, you must add a custom argument. To do so, proceed as follows:

  1. Start transaction /DVD/JCO_MNG.

  2. Go to the Advanced tab.

  3. In the field Additional Java starting arguments, enter -Djdk.module.illegalAccess=permit

JDK 16 introduced strong encapsulation of JDK internals that caused problems in combination with the Snowflake JDBC driver. For more information, see the official Oracle documentation.
In JDK 17, the entry -Djdk.module.illegalAccess=permit no longer works. It is therefore unusable for a connection using the current Snowflake JDBC driver. For more information, see the official Oracle documentation.

In cases where the user wants to utilize both the Snowflake Batch Load connection and Snowpipe Streaming connections simultaneously, it is required to set up separate Java Connector instances for each replication method.

3.2 Storage Management Setup

The final step in setting up SAP and Snowflake connectivity is the creation of appropriate storage connections in transaction /DVD/SM_SETUP

For the Batch Load replication method, SNOW_STAGE and SNOWFLAKE type connections need to be configured (the first one for data file upload, the second for SQL communication with Snowflake API):

3.2.1 Snowflake Stage (SNOW_STAGE)

Binary storage points to the Internal Stage of the Snowflake user. It is used for data transfer, leveraging the extended functionality of the Snowflake JDBC driver.

Proceed as follows to create a Snowflake stage storage:

  1. Start transaction /DVD/SM_SETUP.

  2. Click image-20250512-115518.png Create to create a new storage.

  3. Enter a Storage ID.

  4. In the Storage type field, enter SNOW_STAGE.

  5. Click Confirm.

  6. Specify the required parameters:

Storage ID: Logical name of the storage connection.
Storage type: Type of storage (SNOW_STAGE – Snowflake internal stage).
Java connector RFC: TCP/IP RFC destination used for communication with the SNP Java Connector.
Account name: Name of the Snowflake account without .snowflakecomputing.com.com.com domain, in the format: <account name>.<region>.<platform> (For more information, see Snowflake account name doc).
User role: User role used for data insertion.
JAVA Call Repeat: Number of times that failed JDBC calls are retried.
Repeat delay (seconds): Delay in seconds between retried calls.
Custom location: If enabled, the domain .snowflakecomputing.cn is used. If disabled, the default domain .snowflakecomputing.com is used.
Driver path: Absolute path to the directory containing the Snowflake JDBC driver.
NOTE: In previous versions of Storage Management, the SAP logical file of type DIR (directory) was used. This is now automatically converted to a physical path.
Connection pool size: Number of connections that can be kept open in the pool, reducing resource-expensive establishment of JDBC connections.
Username: Snowflake user.
Password: User password.

  1. Click image-20250512-115641.png Save.

The new storage has been created.

If key pair authentication is established according to the Snowflake documentation, you can configure it with or without a password. Example:

3.2.2 Snowflake Storage (SNOWFLAKE)

This is transparent storage pointing to the Snowflake database. To send SQL command data, use the Snowflake JDBC driver.

Proceed as follows to create a Snowflake transparent storage:

  1. Start transaction /DVD/SM_SETUP.

  2. Click image-20250512-115518.png Create to create a new storage.

  3. Enter a Storage ID.

  4. In the Storage type field, enter SNOWFLAKE.

  5. Click Confirm.

  6. Specify the required parameters:

Storage ID: Logical name of the storage connection.
Storage type: Type of storage (SNOWFLAKE – Snowflake transparent storage).
Referenced storage: Storage ID of Snowflake stage area for temporary file upload or download.
Java connector RFC: RFC destination used for communication with the SNP Java Connector.
Account name: Name of the account in the pattern <account name>.<region>.<platform>. For more information, see the official Snowflake documentation.
Warehouse: Optional parameter. Allows you to select the existing warehouse in the Snowflake account to be used.
Database: Name of an existing database in Snowflake.
Database schema: Name of an existing database schema in Snowflake.
User role: User role used for data insertion.
Custom location: If enabled, the domain .snowflakecomputing.cn is used. If disabled, the default domain .snowflakecomputing.com is used.
Driver path: Snowflake driver directory.
Hints: Additional string added to the connection string when the JDBC driver establishes a connection.
Connection pool size: Number of connections that can be kept open in the pool, reducing resource-expensive establishment of JDBC connections.
Table name prefix: Optional parameter. Allows you to specify a text value for the naming prefix of all Glue tables created within this storage.
Use Transient tables: If selected, only transient tables are created on the storage.
Use Snowflake App for data merge: If selected, the Snowflake Native App is used for the data merge.
Snowflake App name: Name of the existing Snowflake Native App to be used.
Wrap values in staged CSV files: If selected, values are wrapped (encapsulated in quotation marks). Depending on the table content, it might be necessary to encapsulate field values to avoid errors during an INSERT into the Snowflake table from staging CSV files.
Data delivery guarantee: Data transfer behavior. For more information, see the chapterhttps://datavard.atlassian.net/wiki/spaces/ReuseLib/pages/4119724050.

  1. Click image-20250512-115641.png Save.

The new storage has been created.

For the Snowpipe Streaming replication method, only one SNOWSTREAM type connection is sufficient:

3.2.3 Snowpipe Streaming Storage (SNOWSTREAM)

Transparent storage pointing to the Snowflake database. To send SQL commands, the Snowflake JDBC driver is used. To insert data into the Snowflake database, the Snowflake Ingest SDK is used. For more information about Snowpipe Streaming, requirements, limitations, and configuration, see the official Snowpipe Streaming documentation.

Snowpipe Streaming connection supports only the Key Pair authentication method. Please see Snowflake documentation for more information.

Proceed as follows to create a Snowflake transparent storage:

  1. Start transaction /DVD/SM_SETUP.

  2. Click image-20250512-115518.png Create to create a new storage.

  3. Enter a Storage ID.

  4. In the Storage type field, enter SNOWSTREAM.

  5. Click Confirm.

  6. Specify the required parameters:

Storage ID: Logical name of the storage connection.
Storage type: Type of storage (SNOWSTREAM – Snowflake Snowpipe Streaming).
Java connector RFC: RFC destination used for communication with the SNP Java Connector.
Account name: Name of the account in the pattern <account name>.<region>.<platform>. For more information, see the official Snowflake documentation.
Warehouse: Optional parameter. Allows you to select the existing warehouse in the Snowflake account to be used.
Database: Name of an existing database in Snowflake.
Database schema: Name of an existing database schema in Snowflake.
User role: User role used for data insertion.
Custom location: If enabled, the domain .snowflakecomputing.cn is used. If disabled, the default domain .snowflakecomputing.com is used.
Driver path: Snowflake driver directory. It is mandatory to include the Snowflake Ingest SDK JAR file in this directory. The SDK is available for download from the Snowflake Maven Central Repository.
Hints: Additional string added to the connection string when the JDBC driver establishes a connection.
Connection pool size: Number of connections that can be kept open in the pool, reducing resource-expensive establishment of JDBC connections.
Conversion Type: Type of data conversion that is used when sending data to the JCO. For most scenarios, we recommend using CSV conversion due to its stability and reliability:

CSV Conversion (default):

Performance: Slower than JCO table conversion.
Stability: This option is highly stable, and no data inconsistencies are expected.

JCO Table Conversion:

Performance: Better performance than CSV conversion.
Stability: This option is unstable and can lead to inconsistencies between SAP and the JCO.

Username: Snowflake user.
Private Key file location: Path to the private key file.
Private Key Encrypted: If selected, a password input field is displayed.
Password: Password for the private key.
Use Snowflake App for data merge: If selected, the Snowflake Native App is used.
Snowflake App name: Name of the existing Snowflake Native App to be used.
Table name prefix: Optional parameter. Allows you to specify a text value for the naming prefix of all Glue tables created within this storage.
Data delivery guarantee: Data transfer behavior. For more information, see the chapter(SM-2502) Supported Delivery Guarantees .

  1. Click image-20250512-115641.png Save.

The new storage has been created.

If the data contains invalid date values, the values are checked during the extraction. The inconsistent dates will be set to null so that the replication can continue.