[Day 180] From Kaggle to BigQuery dimension tables - an end2end pipeline

 Hello :)
Today is Day 180!


A quick summary of today:
  • finished data modelling in dbt
  • set up PROD in dbt
  • set up automatic dbt job runs in mage
  • created an end to end pipeline


All code from today is on my github repo.


1. Settling down on a data model in dbt

I went over a few different today, but I ended up with the above one. Because all my data is coming from 1 source I felt like, in order to avoid redundancy - I just decided to have dim_loans as the main table, and then have dim_borrower which includes just info about the borrower and dim_date just about the loan issue date.
I also added data description fields, and some tests.

The below pics are taken from the dbt generated documentation:
  • dim_borrower
  • dim_date
  • dim_loans (image is truncated as there are many fields)

2. Setting up a PROD environment for dbt

After I finally settled on a data modelling architecture, I created a PROD env to run all the models in a job.
Not seen in the pic, but there is an 'API trigger' button which shows how we can trigger job runs from some other pipeline orchestrator. This gave me the idea to use this in mage

3. Automatic dbt build trigger in mage

It is just a one block pipeline

But it took me some time to figure out an issue. The issue was that I kept getting error 401 Unauthorized when I used mage's dbt trigger integration. Turns out, the functionality only works when mage is used from their docker image. However, I am running mage, after installing it with pip. I checked whether my API link actually works using postman and then I just switched to using python's request library to send a trigger request to dbt. 

4. An end to end pipeline in mage

At this point, I had 3 pipelines in mage: 

  • data_raw_to_gcs - which takes data from the kaggle api, does small checks and loads it to GCS
  • gcs_to_bq - SQL queries to load data from GCS to BigQuery
  • run_dbt_job - the pipeline described above
So now, I wanted to make an end to end one that executes all 3 of the above pipelines. And mage has a nice way of doing it too. 
I created this end_to_end 1 block pipeline
And it executes all three above - from downloading Kaggle data to uploading the final dim_loans, dim_borrower, dim_date to BigQuery. 


5. A small thing 

I updated the Makefile help options and descriptions again. To make them a bit more user-friendly


Tomorrow, I will start working on a dashboard in Looker. ^^

That is all for today!

See you tomorrow :)

Popular posts from this blog

[Day 198] Transactions Data Streaming Pipeline Porject [v1 completed]

Image
 Hello :) Today is Day 198! A quick summary of today: data streaming pipeline project [v1 done] Here is a link to the project's repo. Well ... I did not know I can do it in a day (~14 hours) after yesterday's issues but here we are. Turns out in order to insert the full (~70 variables with nested/list structure), I need the proper pyspark schema. And yesterday I did not have that and that is why when I was reading data in the kafka producer I was getting NULL in the columns - my schema was wrong. Well today I not only fixed the schema for the 4 variables I had yesterday, but included *all* the variables that come from the Stripe API ~ 70 (for completeness).  When I run docker-compose, the data streams and is input into the postgres db (and is still running). Unfortunately, the free Stripe API for creating realistic transactions has a limit of 25, so every 3 seconds, 25 new transactions are sent to the db. It has been running half the day (since I got that set up) and as I am wr
Read more

[Day 107] Transforming natural language to charts

Image
 Hello :) Today is Day 107! A quick summary of today: created text2chart - transforming natural language to charts [ github ] [ webapp ] I am super sick, but in the around 3 hours of my fever not being high, I managed to study a bit. I was again thinking about creating some nlp project but was stuck on the idea of how to host a model. After looking around I found this article where they create a similar app called chat2vis  and they use codellama. I know codellama is an open source model that I can get from huggingface and I wondered, how did they host it. Turns out - they ask the user to input a huggingface api key, which then allows for querying and generating content. (AWESOME! this is applicable to some of the other PDF chat apps that I have, but I will look to make an app of them later).  My fever eased a bit, and I sat on my chair to do some work ^^ Here is a breakdown of the app. I used streamlit. Also, 2 default datasets during dev: Financial Statements of Major Companies(2009-
Read more

[Day 54] I became a backprop ninja! (woohoo)

Image
 Hello :) Today is Day 54! A quick summary of today: Read through cs231n's neural network lectures [1] [2] [3] Spent 8 hours going over Andrej Karpathy's backprop ninja tutorial After yesterday's confidence boost in doing backprop, I came in today with the intention of becoming of, as Andrej Karpathy calls it, a backprop ninja.  The cs231n NN lectures were the usual about how NN became to be, param initialization, activation functions, loss functions, regularization and optimizers. Nothing out of the ordinary, but still was good to reassure myself of that knowledge. But that was definitely not the main even of today. As I mentioned, I wanted to become (or at least attempt to become) a backprop ninja today. I have watched that particular tutorial twice, the 1st time I had no idea what is going on, the 2nd time I could follow, and now it was the 3rd time - and now, I wanted to participate and actually see the why and the how of the things we have to do, when we do manual back
Read more