Snowflake Stage

In today’s data-driven world, organizations are constantly seeking ways to efficiently manage and process vast amounts of data. Snowflake, a cloud-based data warehousing platform, offers a powerful solution for staging and preparing data for processing. This article will describe Snowflake Stage. We will explain its benefits and how it simplifies the process of importing and managing data from various sources.

What is Snowflake Stage?

Snowflake Stage is a fundamental component of the Snowflake data warehousing platform. It temporarily holds data before loading it into Snowflake tables. Stages allow users to efficiently load and manage data from diverse sources. The sources can be files, external databases, or cloud storage services like Amazon S3 or Azure Blob Storage.

Data Staging in Snowflake

Snowflake Stage plays a crucial role in the data staging process. It provides a flexible and scalable way to ingest and prepare data for further processing. With Snowflake Stage, you can:

1. Load data from various sources

Snowflake is a cloud-based data warehousing platform that allows users to load data from a variety of sources. This includes files in formats such as CSV, JSON, and Avro, making it easy to ingest data from different types of files. Additionally, Snowflake can also connect to external databases, allowing users to pull in data from other systems seamlessly.

Snowflake makes it easy for users to connect with cloud storage services like Amazon S3 and Google Cloud Storage. Users can directly load data from these platforms. This flexibility in data loading options makes Snowflake a versatile tool for managing and analyzing data from multiple sources.

2. Perform data transformations

Snowflake provides users with the ability to manipulate and transform data before loading it into tables. This can be done using SQL queries or other tools within the Snowflake platform. Snowflake lets users change data before saving it. This allows them to clean, enrich, and organize the data based on their needs.

This flexibility in data preparation helps to ensure that the data is accurate, consistent, and optimized for reporting. Snowflake supports different data manipulation techniques. This makes it easy for users to handle complex data transformations. Users can format the data correctly for their specific requirements.

3. Handle large volumes of data

Snowflake’s design can handle large amounts of data during the staging process by scaling up and down as needed. This means that Snowflake can adjust its capacity to accommodate varying data loads. This flexibility allows Snowflake to efficiently manage data without being overwhelmed.

Snowflake’s ability to scale makes it a reliable option for handling data processing tasks. Snowflake can easily adjust its resources to process data efficiently and accurately. The system makes this adjustment as it loads the data. This scalability is crucial for organizations dealing with massive amounts of data, as it allows them to easily handle spikes in data volume without experiencing any performance issues.

Also, Snowflake’s design is flexible, so organizations can easily change their resources to fit their needs. This ensures that we can organize and handle information quickly. Also it helps to get insights faster and make better decisions.

Security Aspects of Staging Data in Snowflake

When it comes to staging data in Snowflake, security is a top priority. Snowflake provides several features to ensure the security and integrity of your staged data:

Access control: Snowflake allows you to grant specific permissions to users and roles, ensuring that only authorized individuals can access and manipulate the staged data.
Encryption: All data in Snowflake Stage is automatically encrypted at rest and in transit, protecting sensitive information from unauthorized access.
Data validation: Snowflake provides mechanisms to validate the integrity and consistency of the staged data before loading it into tables.

Example

Here we use a Snowflake default Stage using the Command Line Interface (CLI). The stage is for a testing environment and the fake CSV data was generated randomly at mockaroo.com. To use a Snowflake default Stage for MOCK_DATA_1.csv file, follow these steps:

Connect to your Snowflake account using the Snowflake CLI. The output should be like that:

C:\Users\user>snowsql -a xz13yxx.eu-central-1
User: DAN
Password:*******
* SnowSQL * v1.2.32
Type SQL statements or !help
DAN#COMPUTE_WH@(no database).(no schema)>

Create a new database (if not already created). Use database:

DAN#COMPUTE_WH@(no database).(no schema)>CREATE DATABASE mock_database;
+----------------------------------------------+
| status                                       |
|----------------------------------------------|
| Database MOCK_DATABASE successfully created. |
+----------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.062s

DAN#COMPUTE_WH@MOCK_DATABASE.PUBLIC>use MOCK_DATABASE;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.156s

Load data into the default stage (we do not create named stage here):

DAN#COMPUTE_WH@MOCK_DATABASE.PUBLIC>PUT 'file://C:/TmpDataSnowflake/MOCK_DATA_1.csv' @~ AUTO_COMPRESS=TRUE;
+-----------------+--------------------+ ... +--------------------+----------+---------+
| source          | target             | ... | target_compression | status   | message |
|-----------------+--------------------+ ... +--------------------+----------+---------|
| MOCK_DATA_1.csv | MOCK_DATA_1.csv.gz | ... | GZIP               | UPLOADED |         |
+-----------------+--------------------+ ... +--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 1.922s

Verify the staged data:

DAN#COMPUTE_WH@MOCK_DATABASE.PUBLIC>LS @~;
+--------------------+-------+...+-------------------------------+
| name               |  size |...| last_modified                 |
|--------------------+-------+...+-------------------------------|
| MOCK_DATA_1.csv.gz | 29456 |...| Fri, 22 Mar 2024 10:49:54 GMT |
+--------------------+-------+...+-------------------------------+
1 Row(s) produced. Time Elapsed: 1.071s

The output will display the files successfully staged in Snowflake. Note that you cannot use the internal default stage for an external table. Additionally if you want to create a table for staged data you would use either CLI and command CREATE or REPLACE EXTERNAL TABLE mock_table_1 or just create an external table in Snowflake Worksheet. Using the Web-based Worksheet is more convenient for the table creation due to copy-paste issues in the console client.

USE DATABASE MOCK_DATABASE;
CREATE or REPLACE TABLE mock_table_1 (
	id INT,
	first_name VARCHAR,
	last_name VARCHAR,
	email VARCHAR,
	gender VARCHAR,
	ip_address VARCHAR,
	card_num VARCHAR
	);
CREATE OR REPLACE FILE FORMAT my_csv_format TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1;
COPY INTO mock_table_1 FROM @~/MOCK_DATA_1.csv.gz FILE_FORMAT = (FORMAT_NAME = my_csv_format) ON_ERROR = 'CONTINUE';

Example

Creating a Snowflake Stage using the Snowflake Web Interface:

Log in to your Snowflake account through the web interface.
Navigate to the “Databases” tab and select or create the desired database and schema.
Click on the “Stages” tab and select “Create”.
Provide a name for your stage and configure the necessary settings, such as the storage integration and file format.
Click “Create” to create the stage.
To load data into the stage, navigate to the “Stages” tab, select your stage, and click “Load Data”.
Specify the location and format of the data files and click “Load”.

The staged data will be available for further processing and loading into Snowflake tables.

Conclusion

Snowflake Stage makes it easier to get data ready for analysis in the cloud. Snowflake helps organizations manage and process lots of data from different sources in a safe and flexible way. With its cloud-based architecture and scalability, Snowflake Stage streamlines the data staging process. This empowers businesses to make data-driven decisions faster and more effectively.

DataSunrise offers exceptional and flexible tools for Snowflake, including security, audit rules, masking, and compliance features. To learn more about how DataSunrise can enhance your Snowflake experience, visit our website and schedule an online demo.