How to Load Data from AWS S3 to Snowflake

According to a study by Statista, the cloud storage market was valued at $90.17 billion in 2022 and will reach a value of $472.47 billion by 2030. These figures indicate a growing shift toward cloud computing and data storage solutions.

A typical scenario in modern data management involves data transfer from cloud storage to cloud-based computing platforms. Amazon’s Simple Storage Service (S3) is among the go-to options for the former, and businesses trust Snowflake for the latter. 

As a result, S3 to Snowflake data transfers are common for businesses looking to benefit from the synergy of S3’s scalable storage and Snowflake’s top-notch data warehousing features. 

This blog discusses the S3 to Snowflake data loading process and explains two ways of setting it up so you can pick the right one. Let’s get started. 

Amazon S3 Overview 

Amazon S3 is an object storage service by Amazon Web Services (AWS). It’s backed by the scalable storage infrastructure supporting Amazon’s massive global e-commerce network. 

As a high-speed, web-based cloud storage service, Amazon S3 creates online backups of data and applications on AWS. It’s well-suited to meet data storage requirements of any scope from different industries. 

Integration with Other AWS Services: You can link S3 to other AWS security and monitoring services such as Macie, CloudTrail, and CloudWatch. Directly linking vendor services to S3 is also an option. 

Easy and Secure Data Transfer: You can transfer data to S3 over the public internet through S3 APIs. Amazon S3 Transfer Acceleration can help you transfer data more quickly, and AWS Direct Connect creates a private connection between S3 and your data center. 

Use Cases: Amazon S3 is a good storage option for organizations of varying sizes thanks to its scalability, security, availability, and performance capabilities. Typical S3 use cases include the following: 

• Data storage 
• Data archiving 
• Document storage 
• Data backup 
• Log file storage 
• Software delivery 
• Website hosting 
• Data lakes and data warehouses

Snowflake Overview 

Snowflake is a fully managed Software-as-a-Service (SaaS) started in 2012 and launched in 2014. It offers a single platform for tackling users’ data warehousing, data lakes, data science, data application and development, and data engineering requirements. It also supports the secure sharing and consumption of real-time or shared data. 

This cloud-based data warehousing solution simplifies the storage, processing, and analysis of structured and semi-structured data. Snowflake’s separation of computing and storage distinguishes it from conventional data warehouses. This allows you to allocate resources to different functions independently based on your requirements. 

Here are some key features of Snowflake: 

No Hardware and Limited Software: As a true SaaS offering, Snowflake isn’t burdened by virtual or physical hardware you would have to install or configure. There’s also virtually no software that you’ll need to set up. 

Hassle-Free Maintenance: Snowflake undertakes all maintenance, updates, tuning, and management tasks — easing the administrative burden on users. 

Cloud Provider Agnosticism: Snowflake is known for being cloud provider agnostic. It’s available on AWS, Azure, and GCP and retains its functionality and end-user experience on each of them.

How to Load Data from S3 to Snowflake? 

There are two ways you can load data from S3 to Snowflake: manual or automated. 

Below, we’ll discuss manual data transfer using the COPY INTO command and automated data transfer using a third-party integration tool such as Astera. 

Manual Data Transfer from S3 to Snowflake

The following steps are involved in manually setting up a data transfer between S3 and Snowflake: 

1. Setting Up the Amazon S3 Bucket 

• If you don’t already have an Amazon S3 bucket, create one where your data is stored. 
• Verify that Snowflake has the necessary permissions to access this bucket. You may need to set up an AWS Identity and Access Management (IAM) role, grant it the required permissions, and attach it to the Snowflake account.

2. Configuring Snowflake External Stage 

• Log in to your Snowflake account. 
• Create an external stage using the Snowflake web interface or SQL commands. 
• Mention the Amazon S3 bucket path as the location for the external stage. 

3. Create Snowflake Table 

• Define a Snowflake table schema that matches the data structure of your Amazon S3 files. 
• Make a table in Snowflake for the data transferred from S3. 

4. Set Up Snowflake Integration 

• Create a Snowflake integration object to establish a connection between Snowflake and Amazon S3. 
• Specify necessary details, such as the AWS access key, AWS secret key, and the external stage associated with the S3 bucket. 

5. Grant The Required Permissions 

• Ensure that the Snowflake user or role has the necessary permissions to access the external stage and load data into the Snowflake table. 

6. Load Data into Snowflake 

• Use the COPY INTO command in Snowflake to load data from the external stage (Amazon S3) into the Snowflake table. 
• Specify the source file format, the destination table, and other relevant options. 

7. Monitor and Troubleshoot 

• Monitor the data transfer process to ensure smooth, error-free operations. 
• Check Snowflake and Amazon S3 logs for error messages and troubleshoot in case of any errors. 

If you wish to automate the data transfer from S3 to Snowflake, you can set up a schedule using Snowflake’s task scheduler or a third-party scheduler tool. Define a schedule for executing the COPY INTO command to load new data from Amazon S3 into Snowflake at specified intervals. 

The Limitations of Manual Data Transfer from AWS S3 to Snowflake 

Manually loading data from AWS S3 to Snowflake may appear simple but it still has certain limitations. Below are some of the major ones you should know about: 

• Data Transfer Costs: Transferring vast data volumes from S3 to Snowflake can quickly increase data transfer costs. These costs can add up over time and become prohibitively expensive. 
• Network Latency: Network latency can impact data transfer speeds in an S3 – Snowflake transfer. This depends mainly on the geographical distance between your Snowflake instance and the AWS region with your S3 bucket, and your network connectivity. 
• Data Security: Both AWS and Snowflake offer mechanisms to ensure secure data transfer, such as IAM roles and HTTPS encryption. However, misconfigured protocols or gaps in security methods can potentially expose confidential information during transit. 
• Data Format Compatibility: Snowflake supports formats such as JSON, CSV, Avro, and Parquet. However, you can face problems if your S3 data is in a format that Snowflake doesn’t support directly — this will necessitate preprocessing or transforming the data before loading. 
• Scalability and Concurrency: Snowflake is equipped with powerful concurrency and scalability features. However, it’s wiser to optimize your data-loading processes and resource allocation to mitigate performance bottlenecks, slower data loading, and disrupted services. 
• Error Handling and Monitoring: Data-loading processes are prone to errors due to network issues, data format incompatibility, or lack of required permissions. You’ll have to implement comprehensive error-handling mechanisms to identify and mitigate such problems promptly. 
• Regulatory Compliance: Based on the nature of your data and applicable regulatory requirements, ensuring compliance with data governance standards such as the General Data Protection Regulation (GDPR), Health Insurance Portability and Accountability Act (HIPAA), and Payment Card Industry Data Security Standard (PCI DSS) can require substantial effort on your part. 

Automated Data Transfer from AWS S3 to Snowflake Using Astera 

1. Browsing Cloud Files

First, note that you can browse cloud files in an Excel source or any other file source in Astera. 

2. Setting Up Cloud Connections

Selecting the ‘Browse Cloud Files’ option will take you to a browser where you can set up cloud connections. Here, you can set up Amazon S3 by selecting ‘Add New Connection’ and inputting credentials, as seen below. 

3. Choosing The Right File

Next, you’ll choose the file you want to access in AWS S3. 

The selected file will appear in the source file path as follows: 

4. Check the File Layout

When you check the layout of the file in Astera, this is how it’ll show up: 

Once configured, the source object will look like this: 

5. Setting up Snowflake

Next, you’ll set up Snowflake by configuring a Database Destination object with Snowflake credentials: 

Along with the Snowflake connection, you can optionally configure a Staging Area. In the image below, the Amazon S3 bucket serves as the staging area. 

You have now configured Snowflake as the Destination Object and the Dataflow will appear as shown below: 

6. Mapping, Previewing Output, and Running The Dataflow

Next, you’ll create mappings from the Source Object (S3) to the Destination Object (Snowflake): 

‘Preview Output’ for the Destination Object will look like this: 

Lastly, when you run the Dataflow using the ‘Start Dataflow’ button, Astera will write the data to the destination, completing the S3 to Snowflake data transfer. 

Manual S3 to Snowflake Data Transfers vs Automated Transfers in Astera 

As seen above, setting up manual S3 to Snowflake data transfers is a valid option. However, the process can be complicated, especially for non-technical users. To recap, these are the steps involved: 

• S3 bucket setup 
• IAM role creation 
• External stage creation in Snowflake 
• Setting up table schema and making a table in Snowflake 
• Creation of a Snowflake integration object 
• Loading data from S3 into the Snowflake table using the COPY INTO command 
• Monitoring and troubleshooting the data transfer process as needed 

In contrast, Astera simplifies S3 to Snowflake data transfers with its intuitive interface, drag-and-drop functionality, and ease of use. It lets you conveniently create and automate data transfers in a few clicks, regardless of your technical proficiency. 

Conclusion 

Astera streamlines end-to-end data management for a range of industries. Its powerful, enterprise-ready solution allows you to design, deploy, and automate S3 to Snowflake pipelines — with zero coding required. 

Find out how you can use Astera to transform your data transfer processes. Reach out to our team or start your FREE trial today!