Creating and Integrating Data Pipeline Using Amazon S3 and Snowflake Data Warehouse. (Using SnowSQL).
Published: 2024-07-11 15:38:57

Data pipeline is widely used in the field of Data Engineering and Analytics to fetch data from external sources for instance AWS Redshift, S3 (Simple Storage Service), GCP (Google Cloud Platform), Oracle, Azure and much more industry –used technologies.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/et05epkp5vyqkijv4pci.png)

Data Pipeline – according to Snowflake is concerned with moving data from one place to a destination (such as a data warehouse or large storage service) while simultaneously optimizing and transforming the data. As a result, the data arrives in a state that can be analyzed and used to develop business insights.

This article aims to cover how integrate AWS S3 with Snowflake which is convenient when dealing and working with large data sources.

**Requirements:**

- Knowledge of SQL for working in Snowflake.
- Snowflake account and AWS
- Data file in preferably CSV (Comma Separated Value), it can be large up to 160 GB of size. Beyond that AWS requires the Cloud developer to use other tools – this is beyond this article’s scope.


**Step 1**
Setup an AWS account as a root user.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mzoxwua3s62q596a1f63.png)

**Step 2**
After a successful login into your account go to **services** as shown. For new users locate **storage** by scrolling downward then **select S3.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l2pa5ixoebwj0oo6byox.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mtmecertyko0zclny36a.png)

**Step 3**
Upon selecting **S3** we expect the display as follows – design might change later.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/noibxrcuy1wg9flwf1qi.png)

Click **Create bucket** then give it a name – in our case **mybootcampbucket**:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2zhqvuyvhqug1uwon4px.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7979ssb0ngwsayv8wkx1.png)

Click **Create bucket** to finish the process.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3m9hhxiijywokv3zyiu1.png)

To upload the file select the newly created bucket (highlighted).


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2re1gshm3fpblcyy10k2.png)

Upload the file now as depicted below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzxy4ixk1ex68ern3lrb.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6goja0sy9d3xckkim1s7.png)


**Step 4**
After uploading the file/dataset. Policy need to be set up. The policy facilitates permission for sharing to external integration when associated with identity and compliance to respective AWS account.
To setup policy click **services** then head to **Security**, **Identity** and **Compliance**. See below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w9caa7t13tqvbxn4jpvk.png)

Select **IAM** (Identity Access Management) then open **Policies**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mp53mu6cbnd6undn83t0.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x6446gcept50puuew41m.png)

After clicking ‘**Create policy**’, allocate the name to the policy to be created. Our case is **Bootcamp2023**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ci0zcdct6k9w0mcth3f.png)


Under policies, click **JSON Policy**, this is because Policies are written in JavaScript Object Notation (JSON) which is a data representation Key – Value pair syntax.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ug53mog0ni9o3ir6g06n.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x2fa79bzaw3bjyv0mk49.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3wtr93ypdgdvkuh5kavw.png)

After setting up the policy, next we create the **Role**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzf7lenk56ivm24q5dw6.png)

Give your role a name, in this scenario – our role is named **Bootcamp_2023**. 


_**Setup the permission**_.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k22161pqjn62jp05igsx.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rlcersegsc04i64u0tj9.png)

I have highlighted to note that we deal with **This account** in particular.
Next select the policy to setup a role for.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3zilbksdo8dczyh75sp2.png)

Finish up with the role creation.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xlawolmmxui5ad212hdy.png)

Now copy the **ARN** (Amazon Resource Names) that will help in identifying resources uniquely.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g6q00svh5ahmiv57jc2c.png)


**Step 5 – Setting Up/Creating a Snowflake Account.**
To create a Snowflake account head to [Snowflake](https://signup.snowflake.com/?utm_cta=website-homepage-hero-button-start-for-free)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wk0ac5jtxsz9h8fz91e4.png)

After creating an account, we need to create our **Warehouse** – as shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q6fm86803gb08etdc7zo.png)

Displaying the pipeline dataset loaded from the AWS S3.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/acwgxwf8ibvjormqtv9c.png)

That is all for this article.