Learn how to create an Airbyte Cloud connection to replicate your CSV data from S3 to Snowflake.
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:
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 into Snowflake. Furthermore, the approach outlined in this tutorial should also be applicable to ingesting Avro, JSONL, or Parquet data from S3 into Snowflake.
You will therefore require the following prerequisites to complete this tutorial:
The following steps can be used to setup your AWS S3 file storage:
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.
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:
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.
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:
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.
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.
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:
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!
Get all your ELT data pipelines running in minutes with Airbyte.
Build a Slack activity dashboard quickly using the Slack Airbyte connector and Apache Superset.
Using the Airbyte GitHub connector and Metabase, we can create insightful dashboards for GitHub projects.