End to End Data Engineering Project #3 — Pt 2/4 : Production Level Migration from S3 to Snowflake using Docker, DBT and AWS Services
Have you ever watched the parody of juraasic park from Critics? . There was one scene in particular that really caught my interest . Here’s the link for the video in case you havent watched it . In this video , you see a dinosaur pounce at young kid . But she manages to lock it in a closet . Feeling safe from the doom that imposed her , the girl relaxes against the door . Buuuut! The dinosaur is too smart. It keeps a paper underneath, rattles the door, snatches the key and then proceeds to open the door. It had me thinking , if any of us would’ve thought of such unconventional idea . Think of it :a newspaper as a fishing rod? Or an aparatus that could fetch us an object? Incredible ,dont you think? The dinosaur then proceeds to give a monologue about Venenzuela and revela its larger-than-life plan to mingle with general population under the name “Pilkington”.But that’s beside the point
Lets bring our attention back to the paper , because this is important . Until I watched this video, a paper to me symbolized words or text that could be read. After I watched this video , however ,it materialised as this object that is capable of slipping through narrow spaces and fetching objects.
One object , perceived as two different tools …
Transformation has always been key to deriving insights of many data culprits. I’d like to think of them as real convicts , they hide in plain sight right under your nose snatching you off the opportunity to make a key business decision. But once you get the key basics of transformation right , even a dinosaur can get it done .
Welcome back to follow up tutorial of our AWS production level project. In case you havent come across the first article , I’d highly reccomend visiting this link . Let’s have a quick recap of what we have done in the previous article .
- We have discussed briefly on what Docker and DBT tool is
- We have created first external table and staged data from S3 bucket to Snowflake using IAM role .
In this article we will continue from where we have left off. We will be focussing on the transformation aspect . How we can get mend data to our will and get the right results . I won’t be focusing too much on the transformation itself, but rather on the specific procedures and steps required to get it done.
The architecture so far
For ease of referencing I will be continuing the bullet points from where I have left off . Once again , if you want steps 1–26 . Please visit the following link
27) Now its time to create a working directory Choose your target location in your file explorer
Please note you folder will be an empty directory . You shouldnt have any files inside.
28) And type CMD in the search location and press enter . A comand prompt should open with relevant file locaiton
29) At the comand prompt , type “code .” For this to work , you have to make sure visual code editor is installed
30) In your command prompt (at that same location) type “python” and hit enter .You should see the following output
I would like to stress on how the python version is 3.10 . Please ensure that , the version is intact.
31)Type “quit()” and you should enter back into your normal command prompt.
32)Now type in the following command
pip install dbt-coreYou should get following output .
Notice how Im getting the red , blue and green color highlights. That is a feature of pip with the latest release with python 3.10 . If you are not getting that , then you should probably revisit step 11 . These are all subtle hints or observations that I have made while creating this project, and hey maybe it actually works for you without the color . But if you bump into an error , then its probably a good idea to trace back the tiny nuances that you are observing.
33)Install dbt-snowflake adapter
pip install dbt-snowflake34)Once the installation is finished . Type the following code
dbt init35)You should see your first input request .
Type in the name of your project . Im going to go with “dbt_aws_snowflake”
36)You should get the following prompt
Type 1 , because we want “snowflake” as adapter
37)It should ask you for you account . To get the details of account , go to your snowflake dashboard where you are logged in
In the link you should see a format https://app.snowflake.com/< 7 character string no 1>/< 7 character string no 2>/worksheets
<7 character string no 1> for the sake of this exercise lets take it qwertyqw <7 character string no 2> lets assume its ertyuyt
Once you are done with that , You need to add “-” between <7 character string no 1> and <7 character string no 2> and that would be your account. So the format would be <7 character string no 1>-<7 character string no 1>
So your account will look something like qwertyqw-ertyuyt
[VERY IMPORATNT NOTE ]If you add any other special character in between(like “.”or “,”) then it wont work .So please make sure to keep “-” without spaces.
[VERY IMPORTANT NOTE] qwertyqw and ertyuyt are just random examples . You need to replace it with the 7 character strings that are present IN YOUR link . Make sure to replace them with that .
Type that in your command prompt
38)For username you need to provide the details that you have given here .Please keep them tracked as they are very essential .
39)Once you enter the username .You will get the following prompt on command prompt. Press 1 as you would need to provide your password
40)For password you type the password that you have given for snowflake login .
41) For the dev role provide “ACCOUNTADMIN”(It comes by default .
42)(Optional) If you have a specific role that you would like to provide you can follow these steps on snowflake web app
43) For warehouse , You can provide “COMPUTE _WH” .
44)(Optional) You could give another warehouse depending on your case . You can see all your warehouses by following these steps on snowflake web app
45)For database , you would need to create one. You can go to the worksheet and type in the followin commands . Select the text and hit Ctrl+Enter
CREATE OR REPLACE DATABASE AWS_S3_DB;46)Once the database is created , we need to create a table inside the database. Write the following querry , select the text and hit Ctrl+Enter
CREATE OR REPLACE TABLE FIN_TAB(date varchar(255), total_price varchar(255));In this case Im having two columns the first being the date and the second being total_price. For both of these I have decided to set type as varchar or the string equivalent in SQL . Please note that you can change it according to the data type that you desire. However for the sake of simplicity and I have decided to keep varchar . If your CSV file has other columns, and you dont want to think too much about what datatype each column should be, then keep them all as varchar.
47)Once you are done with steps 27 and 28 , you have a database and a table. Lets provide the database details to the cmd . “AWS_S3_DB” or the database that you have inserted
48)For schema , you can put “PUBLIC” . Every database has a default schema “PUBLIC” . Or you can easily check it in the snowflake web application
Type the schema that you want in the terminal
49)And finally for threads type 1 or 3(Depending on how fast you want the process to run)
50)Once you are done with all the steps, you are not officially done with setting up your first DBT project with connection details .To confirm , go to C:/Users/<User>/.dbt My name is Amos . So I have folder structure like this . Open the profiles.yml file
51)All the details check out! I have all fields appropriately filled
Conclusion
If you were able to follow all the instructions and you understood the purpose of all the tasks, then congratulations 🎉🎉!
You built your first DBT integrated tool with Snowflake . We will be using extensively in future projects as well since transformations are something we constantly have to deal with . As mentioned earlier, without transformation , we cant see our data in a different light .
Great work! In the next part of this tutorail we will focus on the getting data from external table rather than from an internal table(Which is what we achieved here). So stay tuned for Part 3 !
As always , I hope this article was informative. If you have found it helpful, share and follow for more such content .And reach out to me if you are stuck at any point . Until then
… To infinity and beyond.