Amazon S3 to Snowflake ETL: 2 Easy Methods

Does your organization have data integration requirements, like migrating data from Amazon S3 to Snowflake? You might have found your way to the right place. This article talks about a specific Data Engineering scenario where data gets moved from the popular Amazon S3 to Snowflake, a well-known cloud Data Warehousing Software. However, before we dive deeper into understanding the steps, let us first understand these individual systems for more depth and clarity.

Overview of Amazon S3

Amazon S3 is a highly scalable object storage provided by AWS, used for storing and accessing any quantity of data anytime from the web. It allows you to store data in objects inside buckets. These buckets are the containers storing these objects. All stored objects’ sizes range from 0 bytes up to 5 terabytes.

Use Cases

Let us discuss some of its key use cases.

• Data Backup and Recovery: You can store backups of critical data in S3. This provides a durable store for data and quick recovery in case failures or disasters happen.
• Static Website Hosting: It can also host static websites by serving directly from buckets in S3; that is, HTML, CSS, JavaScript, and media files will be served since it’s inherently qualified to scale and provide low latency in its access.

Overview of Snowflake

Snowflake is a cloud-based data warehousing platform designed to handle large-scale data analytics and processing. Snowflake uses a multi-cluster, shared data architecture, separating storage and compute. This enables independent scaling of both resources, optimizing performance and cost-efficiency.

Use Cases

I have listed some of the key use cases of Snowflake.

• Data Warehousing: Snowflake is used mainly for Data Warehousing. It stores huge volume of structured and semi-structured data for business intelligence and reporting analysis.
• Real-time Analytics: Snowflake supports real-time data ingestion and processing, and thus it shall find a perfect place in applications requiring up-to-date insights and operational analytics.

How to Set Up Amazon S3 to Snowflake Integration

This article delves into both the manual and using Hevo methods in depth. You will also see some of the pros and cons of these approaches and would be able to pick the best method based on your use case. 

Prerequisites

You will have a much easier time understanding the ways for setting up the Amazon S3 to Snowflake Integration if you have gone through the following aspects:

• An active account on Amazon Web Services.
• An active account on Snowflake.
• Working knowledge of Databases and Data Warehouses.
• Clear idea regarding the type of data to be transferred.

Let us now go through the two methods:

Method 1: Manual ETL Process to Set up Amazon S3 to Snowflake Integration

You can follow the below-mentioned steps to manually set up Amazon S3 to Snowflake Integration:

• Step 1: Configuring an S3 Bucket for Access
• Step 2: Data Preparation
• Step 3: Copying Data from S3 Buckets to the Appropriate Snowflake Tables
• Step 4: Set up automatic data loading using Snowpipe
• Step 5: Manage data transformations during the data load from S3 to Snowflake

Step 1: Configuring an S3 Bucket for Access

To authenticate access control to an S3 bucket during a data load/unload operation, Amazon Web Services provides an option to create Identity Access Management (IAM) users with the necessary permissions.

An IAM user creation is a one-time process that creates a set of credentials enabling a user to access the S3 bucket(s).

In case there are a larger number of users, another option is to create an IAM role and assign this role to a set of users. The IAM role will be created with the necessary access permissions to an S3 bucket, and any user having this role can run data load/unload operations without providing any set of credentials.

Step 2: Data Preparation

There are a couple of things to be kept in mind in terms of preparing the data. They are:

• Compression: Compression of files stored in the S3 bucket is highly recommended, especially for bigger data sets, to help with the smooth and faster transfer of data. Any of the following compression methods can be used:
  • gzip
  • bzip2
  • brotli
  • zstandard
  • deflate
  • raw deflate
• File Format: Ensure the file format of the data files to be loaded matches with the file format from the table below – 

Structured	Semi-structured
Delimited (CSV, TSV, etc.)	JSON
	Avro
	ORC
	Parquet
	XML

Step 3: Copying Data from S3 Buckets to the Appropriate Snowflake Tables

Data copy from S3 is done using a ‘COPY INTO’ command that looks similar to a copy command used in a command prompt or any scripting language. It has a ‘source’, a ‘destination’, and a set of parameters to further define the specific copy operation.

The two common ways to copy data from S3 to Snowflake are using the file format option and the pattern matching option: File format -Here’s an example :

copy into abc_table   
from s3://snowflakebucket/data/abc_files 
credentials=(aws_key_id='$KEY_ID' aws_secret_key='$SECRET_KEY')   file_format = (type = csv field_delimiter = ',');

Pattern Matching –

copy into abc_table   
from s3://snowflakebucket/data/abc_files 
credentials=(aws_key_id='$KEY_ID' aws_secret_key='$SECRET_KEY')   pattern='*test*.csv';

Step 4: Set up Automatic Data Loading using Snowpipe

As running COPY commands every time a data set needs to be loaded into a table is infeasible, Snowflake provides an option to automatically detect and ingest staged files when they become available in the S3 buckets. This feature is called automatic data loading using Snowpipe.

Here are the main features of a Snowpipe –

1. Snowpipe can be set up in a few different ways to look for newly staged files and load them based on a pre-defined COPY command. An example here is to create a Simple-Queue-Service notification that can trigger the Snowpipe data load.
2. In the case of multiple files, Snowpipe appends these files into a loading queue. Generally, the older files are loaded first, however, this is not guaranteed to happen.
3. Snowpipe keeps a log of all the S3 files that have already been loaded – this helps it identify a duplicate data load and ignore such a load when it is attempted.

Step 5: Managing Data Transformations During the Data Load from S3 to Snowflake

One of the cool features available on Snowflake is its ability to transform the data during the data load. In traditional ETL, data is extracted from one source and loaded into a stage table in a one-to-one fashion. Later on, transformations are done during the data load process between the stage and the destination table. However, with Snowflake, the intermediate stage table can be ignored, and the following data transformations can be performed during the data load –

1. Reordering of columns: The order of columns in the data file doesn’t need to match the order of the columns in the destination table.
2. Column omissions: The data file can have fewer columns than the destination table, and the data load will still go through successfully.
3. Circumvent column length discrepancy: Snowflake provides for options to truncate the string length of data sets in the data file to align with the field lengths in the destination table. The above transformations are done through the use of select statements while performing the COPY command. These select statements are similar to how select SQL queries are written to query database tables, the only difference here is, the select statements pull certain data from a staged data file (instead of a database table) in an S3 bucket. Here is an example of a COPY command using a select statement to reorder the columns of a data file before going ahead with the actual data load –

copy into abc_table(ID, name, category, price)    
from (select x.$1, x.$3, x.$4, x.$2  from @s3snowflakestage x)    file_format = (format_name = csvtest);

In the above example, the order of columns in the data file is different from that of the abc_table, hence, the select statement calls out specific columns using the $*number* syntax to match with the order of the abc_table.

Limitations of Manual ETL Process to Set up Amazon S3 to Snowflake Integration

After reading this blog, it may appear as if writing custom ETL scripts to achieve the above steps to move data from S3 to Snowflake is not that complicated. However, in reality, there is a lot that goes into building these tiny things in a coherent, robust way to ensure that your data pipelines are going to function reliably and efficiently. Some specific challenges to that end are – 

• Other than one-of-use of the COPY command for specific, ad-hoc tasks, as far as data engineering and ETL goes, this whole chain of events will have to be automated so that real-time data is available as soon as possible for analysis. Setting up Snowpipe or any similar solution to achieve that reliably is no trivial task.
• On top of setting up and automating these tasks, the next thing a growing data infrastructure is going to face is scaling. Depending on the growth, things can scale up really quickly and if you don’t have a dependable, data engineering backbone that can handle this scale, it can become a problem. 
• With functionalities to perform data transformations, a lot can be done in the data load phase, however, again with scale, there are going to be so many data files and so many database tables, at which point, you’ll need to have a solution already deployed that can keep track of these updates and stay on top of it.

Method 2: Using Hevo Data to Set up Amazon S3 to Snowflake Integration

Loading data into Snowflake using Hevo is easier, more reliable, and fast. For any information on Amazon S3 Logs, you can visit the former link.

You can move data from Amazon S3 to Snowflake by following 2 simple steps without writing any piece of code. 

Step 1: Configure Amazon S3 as the Source

• Connect to Amazon S3 source by providing connection settings.

• Select the file format (JSON/CSV/AVRO) and create schema folders.

Step 2: Configure Snowflake as the Destination

• Configure Snowflake Warehouse.

Hevo will take all groundwork of moving data from Amazon S3 to Snowflake in a Secure, Consistent, and Reliable fashion. 

Future Trends

Integrating Snowflake with deep analytics and AI/ML tools helps you to draw deeper insights and value out of the data in S3 to continue innovating and enhancing their competitive advantage. Increased adoption of the hybrid and multi-cloud strategies that Snowflake will be able to enable across multiple cloud providers while allowing for data consistency and access across S3 and other cloud storage solutions. You can make the most of Snowflake’s auto-scaling properties and its pay-as-you-go cost model, which is in line with changing needs for business and cost strategies.

Conclusion

Amazon S3 to Snowflake is a very common data engineering use case in the tech industry. As mentioned in the custom ETL method section, you can set things up on your own by following a sequence of steps, however, as mentioned in the challenges section, things can get quite complicated and a good number of resources may need to be allocated to these tasks to ensure consistent, day-to-day operations. 

FAQs to sync data between S3 to Snowflake