Amazon S3 (Simple Storage Service) is a cloud-based object storage service that provides developers and IT teams with secure, durable, and scalable storage for their data. It allows users to store and retrieve any amount of data from anywhere on the web, making it easy to build and scale applications, backup and archive data, and analyze data. S3 is designed to provide high availability and durability, with data automatically replicated across multiple availability zones within a region. It also offers a range of features such as versioning, lifecycle policies, and access control to help users manage their data effectively.
A cloud data platform, Snowflake Data Cloud provides a warehouse-as-a-service built specifically for the cloud. The Snowflake platform is designed to empower many types of data workloads, and offers secure, immediate, governed access to a comprehensive network of data. Snowflake’s innovative technology goes above the capabilities of the ordinary database, supplying users all the functionality of database storage, query processing, and cloud services in one package.
1. Open the Airbyte dashboard and click on "Sources" from the left-hand menu.
2. Click on the "Create Source" button and select "S3" from the list of available connectors.
3. Enter a name for your S3 source and click on "Next".
4. Enter your AWS access key ID and secret access key in the respective fields. You can find these credentials in your AWS account under "Security Credentials".
5. Select the AWS region where your S3 bucket is located from the dropdown menu.
6. Enter the name of your S3 bucket in the "Bucket Name" field.
7. If your S3 bucket is not in the root directory, enter the path to the directory in the "Path Prefix" field.
8. If you want to include only certain files in your data sync, you can enter a file pattern in the "File Pattern" field. For example, "*.csv" will only include CSV files.
9. Click on "Test" to verify your credentials and connection to the S3 bucket.
10. If the test is successful, click on "Create Source" to save your S3 source connector.Once your S3 source connector is set up, you can use it to create a new Airbyte pipeline and sync data from your S3 bucket to your destination of choice.
1. First, navigate to the Airbyte website and log in to your account.
2. Once you are logged in, click on the "Destinations" tab on the left-hand side of the screen.
3. Scroll down until you find the Snowflake Data Cloud destination connector and click on it.
4. You will be prompted to enter your Snowflake account information, including your account name, username, and password.
5. After entering your account information, click on the "Test" button to ensure that the connection is successful.
6. If the test is successful, click on the "Save" button to save your Snowflake Data Cloud destination connector settings.
7. You can now use the Snowflake Data Cloud destination connector to transfer data from your Airbyte sources to your Snowflake account.
8. To set up a data transfer, navigate to the "Sources" tab on the left-hand side of the screen and select the source you want to transfer data from.
9. Click on the "Create New Connection" button and select the Snowflake Data Cloud destination connector as your destination.
10. Follow the prompts to set up your data transfer, including selecting the tables or data sources you want to transfer and setting up any necessary transformations or mappings.
11. Once you have set up your data transfer, click on the "Run" button to start the transfer process.
Should you build or buy your data pipelines?
Download our free guide and discover the best approach for your needs, whether it's building your ELT solution in-house or opting for Airbyte Open Source or Airbyte Cloud.
With Airbyte, creating data pipelines take minutes, and the data integration possibilities are endless. Airbyte supports the largest catalog of API tools, databases, and files, among other sources. Airbyte's connectors are open-source, so you can add any custom objects to the connector, or even build a new connector from scratch without any local dev environment or any data engineer within 10 minutes with the no-code connector builder.
We look forward to seeing you make use of it! We invite you to join the conversation on our community Slack Channel, or sign up for our newsletter. You should also check out other Airbyte tutorials, and Airbyte’s content hub!
What should you do next?
Hope you enjoyed the reading. Here are the 3 ways we can help you in your data journey:
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.
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:
- Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/.
- Choose Create bucket.
- In the Bucket name, enter a DNS-compliant name for your bucket. Make sure to read bucket naming rules.
- In Region, choose the AWS Region; choose a Region close to you to minimize latency and costs and address regulatory requirements.
- Choose the recommended option for Object Ownership.
- Block all public access.
- Choose Create bucket.
- 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.
- Once you have created a policy, the next step involves creating a user group & adding a user to that group.
- In IAM, pick User groups, then create a group.
- Make sure to attach the S3 Policy we just created.
- Choose Add a user.
- 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:
- The Output Stream Name is the name of the database table that will be created in Snowflake.
- 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.
- AWS User Access Key ID and AWS Secret Access Key are the values that we obtained in Step 1
- 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.
- 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.
- 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:
- Configure static file storage using AWS S3.
- Configure a database on Snowflake.
- Configure an Airbyte S3 source connector.
- Configure an Airbyte Snowflake destination connector.
- 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!
What should you do next?
Hope you enjoyed the reading. Here are the 3 ways we can help you in your data journey:
Should you build or buy your data pipelines?
Download our free guide and discover the best approach for your needs, whether it's building your ELT solution in-house or opting for Airbyte Open Source or Airbyte Cloud.
Ready to get started?
Frequently Asked Questions
Amazon S3's API provides access to a wide range of data types, including:
1. Object data: This includes the actual files stored in S3 buckets, such as images, videos, documents, and other types of files.
2. Metadata: S3 stores metadata about each object, including information such as the object's size, creation date, and last modified date.
3. Access control data: S3 provides access control mechanisms to restrict access to objects in a bucket. The API provides access to information about access control policies and permissions.
4. Bucket data: S3 buckets are containers for objects. The API provides access to information about buckets, such as their names, creation dates, and region.
5. Logging data: S3 can log access requests to objects in a bucket. The API provides access to these logs, which can be used for auditing and compliance purposes.
6. Inventory data: S3 can generate inventory reports that provide information about the objects stored in a bucket. The API provides access to these reports.
7. Metrics data: S3 can generate metrics about the usage of a bucket, such as the number of requests and the amount of data transferred. The API provides access to these metrics.