(SM-2408) SAP - Snowflake Communication

Owned by SNP
Aug 20, 2024
2 min read

Connection to Snowflake is provided by our component - Storage Management (SM). 

Snowflake Internal Stage

Snowflake Internal Stage is a cloud computing storage service offered by Snowflake. A stage in Snowflake is an intermediate space where you can upload the files so that you can use the COPY command to load or unload tables. User type Internal Stage is used for SAP communication and in SM it is covered by referenced binary storage.

During SAP-Snowflake communication, the Snowflake Internal Stage is used as temporary storage when transferring data from SAP to Snowflake (see the description of the DML sequence diagram below).

Communication with Snowflake Internal Stage

Communication with Snowflake Internal Stage is provided via Java connector (extended JDBC API form upload/download streams).

Snowflake Database (DB)

Snowflake is an analytic data warehouse provided as Software-as-a-Service (SaaS). Snowflake provides a data warehouse that is faster, easier to use, and far more flexible than traditional data warehouse offerings. Snowflake is a cloud data platform with a data warehouse that supports the most common standardized version of SQL (ANSI) for powerful relational database queries.

For data transfer to Snowflake DB, also Snowflake Internal Stage storage is used. Firstly, the data is moved in the form of a .csv file to Snowflake Internal Stage, and afterward, the engine loads the transferred data. 

High-level Communication Diagram

Communication with Snowflake

When communicating with the Snowflake DB engine, a Java connector (implemented by SNP) is used. This connector wraps SQL queries using JDBC jars and forwards them to the engine.


Detailed Communication Diagram for Snowflake (batch load)


Detailed Communication Diagram for Snowpipe streaming

Authentication

Java JDBC Service uses dedicated DB user credentials. The Snowflake user by default has also created Snowflake Internal Stage used as binary storage. The user's DB credentials may have special grants on the DB level for accessing dedicated schemes, read/write dedicated objects, and so on. Java JDBC Service is responsible for handling security when communicating with the Snowflake ecosystem.

Prerequisites

  • The customer has signed up for Snowflake Internal Stage and Snowflake DB ecosystem.
  • The customer has the Snowflake DB cluster (including roles) set up (via console).
  • The customer has to provide details needed for connection into Snowflake DB Service including DB's credentials.
  • The customer grants permissions to some system tables in Snowflake for some SAP SM data computation (table size: TABLE_STORAGE_METRICS).
  • The customer makes sure, that DVD users can run select on table INFORMATION_SCHEMA (table exists: INFORMATION_SCHEMA).