Getting Started with Snowflake

This getting started gives some clues to start working with Snowflake

Connect to your Data

The database structure can be entirely reversed in Metadata and then used in Mappings and Processes to design and adapt the business rules to meet the user’s requirements.

You can refer to Connect to your Data page which explains the procedure.

Below, an example of reversed Snowflake Metadata.

getting started snowflake metadata overview

Temporary Storage

Overview

To optimizing data loading into Snowflake, Semarchy xDM Data Integration is using a temporary storage location to store temporary files before loading them into Snowflake.

When necessary, source data is first extracted to temporary files which are sent into this temporary storage location, before being loaded into Snowflake using the dedicated Snowflake loaders.

Currently, Semarchy xDM Data Integration supports the following storage locations:

  • Internal Snowflake Stage

  • External Storage

    • Microsoft Azure Storage

    • Amazon S3

Storage definition

The storage information are defined in the Snowflake Metadata, which allows to specify the default storage location to be used.

On the Snowflake Metadata server node:

  1. Go into 'Storage' tab

  2. Define the 'Storage Method'

  3. Define 'External Storage' property if you are using External Storage.

  4. Modify the Compression Behavior if necessary.

getting started snowflake metadata storage overview

The following properties are available:

Property Description

Storage Method

Storage Method used as default in Mappings loading data into Snowflake.

  • internalStage: temporary files will be stored inside a Snowflake internal stage.

  • externalStorage: temporary files will be stored in an external storage system which must be defined using 'External Storage' attribute.

This will be default method for all schemas and tables of this Metadata.

External Storage

Metadata link of the external storage container to be used when using external storage method. Choose in the list the external storage, or drag and drop it inside the property.

This property supports: * A Microsoft Azure Storage Container * A Amazon S3 Folder.

Note that this will be the default for all the schemas and tables of this Metadata.

Compression Type

Defines the compression type used when loading files (source files or temporarly generated files) into this database.

Compression Strategy

Defines how compression behavior when loading files (source files or temporarly generated files) into this database.

  • compress at execution: use this option when you want Semarchy xDM Data Integration to compress files which are sent to Snowflake during flows automatically during execution. Template will use correct Snowflake options to specify compression type, compress the files using a Gzip Process Action, and then send compressed file to Snowflake.

  • delegate compression to driver: use this option when you want the compression of files which are sent to Snowflake during flows to be performed automatically by the Snowflake driver, using the 'AUTO_COMPRESS' option supported on the 'PUT' statement. Note that this is supported only when using Snowflake internal stage (see 'Storage Method' parameter')

  • assume already compressed: use this option when source files have already been compressed before the execution of Mappings. Templates will use the correct Snowflake options to specify compression type and simply send them without compressing them as they are already compressed.

Those attributes can be overridden per schema and per table: for this, go on the desired schema or table node, then go inside the 'Storage' tab where you’ll find the same attributes, which will override the value set on the parent nodes.

Those attributes can also be overridden directly in Mappings through parameters on Load Templates, allowing to manually define a different behavior than the one specified in Metadata.

'External Storage' is not linked to the 'Storage Method'. You can decide to use 'internalStage' storage method but provide an External Storage link for specific cases where you’ll override the default storage method.

Definition of an external storage

As indicated in above properties documentation, you can define the external storage location by selecting the related location, or by drag and dropping it directly on the property.

To define an external storage, the prerequisite is to have the corresponding Metadata exisiting in your workspace.

Example through a drag and drop:

getting started snowflake metadata storage external

Refer to Getting Started with Microsoft Azure Blob Storage and Amazon S3 to learn how to create these Metadata.

Override of storage configuration

The storage configuration that is defined on the server node can be overriden on schema, on table, and directly in Mappings.

This offers the ability to have a common default behavior and override it when it is required for some specific developments or use cases.

Example of override on a schema node:

getting started snowflake metadata storage override

Example of override in a Mapping:

getting started snowflake mapping storage override

Create your first Mappings

Your Metadata being ready and your tables reversed, you can now start creating your first Mappings.

The Snowflake technology can be used like any other database in Semarchy xDM Data Integration.

Drag and drop your sources and targets, map the columns as usual, and configure the templates accordingly to your requirements.

Loading data from a database into Snowflake

getting started snowflake mapping example 1

Loading data from a Delimited File into Snowflake

getting started snowflake mapping example 2

Loading data from Snowflake into another database

getting started snowflake mapping example 3

Performing Reject detection while loading a Snowflake Table

getting started snowflake mapping example 4

Replicating a source database into Snowflake

getting started snowflake process example 1

Sample Project

The Snowflake Component ships sample project(s) that contain various examples and use cases.

You can have a look at these projects to find samples and examples describing how to use it.

Refer to Install Components to learn how to import sample projects.