Amazon’s Simple Storage Service (Amazon S3) is an object storage service that allows large amounts of data to be efficiently and cost-effectively stored. S3 is used in a wide range of use cases such as data lakes, websites, mobile applications, backup and restore, archiving data, and big data storage. It is possible and likely that your organization may already have a large amount of data stored in S3.

In order for your organization to extract the maximum value from your data integration strategy, it may be beneficial to copy your data from S3 into a purpose-built big data analytics platform such as Snowflake. Data that is moved to Snowflake can then be combined with data from other systems, which will provide your organization with the following benefits:

• A unified view of data and a single source of truth – achieved by copying data from S3 and other operational systems into Snowflake.
• Improved analytics capabilities – Snowflake is purpose built for running large analytics jobs.
• The ability to transform data in a single location – moving data from S3 and other systems into Snowflake allows you to transform and join data from multiple disparate systems.

One of the challenges with some data integration approaches is that creating custom ETL (extract, transform, and load) or ELT (extract, load, and transform) data pipelines to move your data into Snowflake may require dedicated technical capabilities or resources that your organization may not have readily available. Alternatively, Airbyte can be used to  quickly create ETL pipelines from S3 to Snowflake.

What you will learn in this tutorial

This tutorial will show you how to configure Airbyte to ingest CSV data from Amazon S3 to Snowflake. Furthermore, the approach outlined in this tutorial should also be applicable to ingesting Avro, JSONL, or Parquet data from S3 into Snowflake.
‍

‍

{{COMPONENT_CTA}}
‍

‍

Pre-requisites

You will therefore require the following prerequisites to complete this tutorial:

• An AWS account to set up an S3 host.
• A Snowflake account to create our data warehouse.
• An Airbyte cloud account. You can also use Airbyte locally by following the instructions to set up Airbyte on your system using docker-compose.

Step 1: Set up an AWS S3 Static file storage

The following steps can be used to setup your AWS S3 file storage:

1. Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/.
2. Choose Create bucket.
3. In the Bucket name, enter a DNS-compliant name for your bucket. Make sure to read bucket naming rules.
4. In Region, choose the AWS Region; choose a Region close to you to minimize latency and costs and address regulatory requirements.
5. Choose the recommended option for Object Ownership.
6. Block all public access.
7. Choose Create bucket.
8. To access this bucket from Airbyte, you must set up an appropriate IAM policy. Head over to IAM > Policies > Create Policy. Give the group a name and attach a policy with written access to S3.
9. Once you have created a policy, the next step involves creating a user group & adding a user to that group.
10. In IAM, pick User groups, then create a group.
11. Make sure to attach the S3 Policy we just created.
12. Choose Add a user.
13. For the AWS Access type, choose the access key - Programmatic access to obtain the access key ID and secret access key; we will need both these keys to set up our Airbyte destination.

For the sake of this tutorial, we have uploaded a CSV file to our S3 bucket. In this tutorial, we will later ingest the data in this file into a snowflake database.

‍

‍

Step 2: Set up a database using Snowflake

If you don’t already have a Snowflake account, you’ll need to pick a Snowflake edition and a cloud provider for your warehouse as part of the account creation process.

The Snowflake dashboard will appear after you log in with a username and password. The worksheet area will be where you’ll run scripts and SQL queries for creating, visualizing, and modifying resources.

For Airbyte to successfully sync data from files hosted in S3, you need to create a data warehouse and a database. Luckily, Airbyte provides a script that lets us do it in a few seconds.

Make sure to update the value of sensitive fields such as the password. Take note of the following values, as you will need them later while setting up Snowflake as an Airbyte destination:

-- set variables (these need to be uppercase)
set airbyte_role = 'AIRBYTE_ROLE';
set airbyte_username = 'AIRBYTE_USER';
set airbyte_warehouse = 'AIRBYTE_WAREHOUSE';
set airbyte_database = 'AIRBYTE_DATABASE';
set airbyte_schema = 'AIRBYTE_SCHEMA';

-- set user password
set airbyte_password = 'password';

You can then  visit the warehouse section to confirm that there is a new warehouse with the name AIRBYTE_WAREHOUSE with the configuration that was specified in the script.

‍

Step 3: Set up an Airbyte S3 Source

In this section you will define an Amazon S3 source connection. To set up a new source, go to the Sources menu in your Airbyte dashboard and choose to create a new source by clicking on the New source button and then fill in the following fields:

1. The Output Stream Name is the name of the database table that will be created in Snowflake.
2. A Pattern of files to replicate is a glob pattern of where to look for files. For e.g. to refer to all the files in the bucket use ** as the pattern.  You can learn more about how to construct the correct pattern for complex needs in our Path Patterns docs.
3. AWS User Access Key ID and AWS Secret Access Key are the values that we obtained in Step 1
4. If you have a pre-set location for files inside the bucket, then set the Path Prefix to those folders inside the bucket, this helps Airbyte quickly figure out the location of files in case the bucket contains a large amount of data.
5. For file format, choose CSV from the available options. You can choose to configure various aspects of how Airbyte should read the files like changing the delimiter character to tabs (\t) instead of coma, but we will leave the default settings as is.

‍

6. Once you are done with the configurations, click on set up source button and wait while Airbyte establishes a connection to your bucket.

Step 4: Set up Airbyte Snowflake destination

To set up a snowflake destination, head over to Destinations in your Airbyte dashboard and choose New destination from the list of available destinations, and pick Snowflake.

‍

Step 5: Set up an S3 to Snowflake Connection

To set up a connection between our S3 bucket and the snowflake database, go to Connections and click on the New Connection button at the top right.

In the Select an existing source section, look for the S3 source you just created & click on Use existing source. Similarly, do the same thing for the destination as well. Once you do that, you should see a screen like this.

‍

As you can see, Airbyte is showing the s3csvdata stream that we set up earlier while configuring our source. Enable this stream by clicking on the toggle button if not already done.

Next, you can (optionally) choose the Full refresh Overwrite option for sync mode. This will resend the entire data set on each sync cycle. The S3 source connector also supports several other sync modes. Once satisfied with your changes, click on Save changes and Airbyte will start to sync your data.

Once the sync is complete, you can head to your Snowflake dashboard. Under Databases look for AIRBYTE_DATABASE > AIRBYTE_SCHEMA > Tables, and you should see a new table named s3csvdata.

‍

As you can notice, Airbyte correctly synced column names from our CSV file. Select the Data Preview tab to check what data was synced.

Airbyte provides syncing of several other file formats including: JSONL, Avro, and Parquet. Building an ELT / data ingest pipeline with these alternate file formats is similar to what we have just demonstrated with CSV.

Wrapping up

This tutorial has shown you how to quickly create a connector to replicate data from S3 to Snowflake.  To do this, the following steps were followed:

1. Configure static file storage using AWS S3.
2. Configure a database on Snowflake.
3. Configure an Airbyte S3 source connector.
4. Configure an Airbyte Snowflake destination connector.
5. Create an Airbyte connection that automatically imports files from S3 to a table inside Snowflake.

With Airbyte, the data integration possibilities are endless, and we look forward to seeing you use it! We invite you to join the conversation on our community Slack Channel, participate in discussions on Airbyte’s discourse, or sign up for our newsletter. You should also check out other Airbyte tutorials and Airbyte’s blog!

‍