Gen AI Data Guy

So I have loaded the scripts from every episode of Friends into a Snowflake database and I aim to analyse the words used and create a dashboard that anyone can have a play with by creating a roll up table with various statistics. For now that is a work in progress, so instead I am also going to use it in an episode by episode Friends trivia video series. 

 Wanted to do some rebranding for YouTube if nothing else. Here is what it came up with.

So the first thing I wanted to do on changing this blog was to get a new logo. Images can be fun with AI, they aren't very good at people or text. So the story behind the logo is that I left it to AI. I tried to stick with free or free to try sites to generate these. Using:  https://www.craiyon.com/ https://wepik.com/ai https://picsart.com/ You'll notice that from the selection below the text was always close but garbage. Still I am more hopeful, based off my past experience, that writing code and other data type projects with Gen AI will prove more useful. Must admit I am tempted to use one of these as the logo, even if the wording is wrong etc. 

Having spent a lot of my time playing with Keboola and dbt to load and transform my data I wanted to have a look at just doing stuff in pure python. I have previously built the fill ETL pipeline for a company in python but haven't really had a need to touch it in over 4 years. Most of the work I did before was just using pandas with a few connectors to various databases and producing reports in Excel using xlwings. It wasn't pretty but it was effective and everyone was happy with the job that it did.  Instead I ended up using the prefect library. Well I built it all and then integrated it into prefect once I found it. I found it ok and it has some useful features bit it is not brilliant but that could be through back of use. It does allow you to produce DAGs and and lots of other useful functionality. Script below. 

I have wanted to load my strava data into my data platform since I started loading the strength data. I found some really useful instructions that I used as by base here . I basically use the procedures shown to load my last 200 strava activities. I load this into MySQL, find the new entries which then get loaded into the main MySQL table and then a bulk load into Snowflake. My next step will be to process this into a more meaningful table using either dbt or seeing if I can do something smart with python and a view in Snowflake.

I decided that I liked doing some of my coding in python, even if I have to manually kick it off at the moment I might go with the safe option of a $5 a month python anywhere package to run on a schedule in the cloud or I could put in as an AWS lambda function or in Azure but I don't want to accidentally rack up a bill so might wait until I am further into training on them.  So in this code I have: Used dotenv to store all parameters and passwords as environment variables so I can post my scrips without modification and store them in git. (with the env file set to gitignore).  Retrieved the values above and called the weather API. Flattened the json to get all the columns. Put the new rows into the table in MySQL.  Retrieved the table from MySQL and done a drop and replace into Snowflake.  My Code:  The table: 

I decided that I wanted to ingest some weather data through a different means, this time combining Google API scripts to retrieve the data from the API and another script to connect to the MySQL Database and deposit the data. Both steps are setup on a schedule.  Setting up the API Call:  I followed the instructions from the following medium post to create a function in Google Apps Scripts that would call an API for me. I use it to call the weather API website and retrieve the weather data for where I live.  These 2 things combined import the data into my Google Sheet as per:  I have then set this up to run on a 4 hour schedule within Google Sheets using one of their triggers.  Getting the data into MySQL : So now that I have the data in Google Sheets I want to regularly import this data into a database as it only stores a single row in google sheet, though I could probably get it to persist here as well. Looking at my options with the JDBC drivers available MyS...

A lot of the data that I am working with doesn't lend itself to creating complex star schemas (more on data modelling in a later post) however I want to try and at least want to go to some effort. The one thing that pretty much all my tables have in common is date, so I want to build a date dimension. My strength training happens on a day, my running and walking, parkrun and hopefully the weather data I intend to integrate will have a date on it. This means if I have a date dimension and I aggregate my various facts at date level I can use them together in a single visualisation.  There are various methods I could have used to create the date dimension and I went with using the dbt spine, this was really simple using the utilities:  Used the date functions in Snowflake to extract relevant fields such as year, month, day in words and year month etc. 

As much as I have enjoyed using Keboola there are some connections that it doesn't have or that just haven't worked for one reason or another. I actually came across Zapier as a solution for bringing in e-mails from parkrun to load my results every week. Honestly I have not found it to be as robust as Keboola but that might just be me archiving my e-mails before it completes it 15 minute poll.  The second use case I am working on is the pulling in Strava data, for a fitness dashboard the fact it has a built in connector for Strava is great, though I am worried given the activities I do that I might reach the limit.  I won't go into details on how to set things up but you can setup 5 Zaps that can run for a combined 100 runs during a month for free.  In my data platform / solution I am using Zaps to load harder to get / automate data. It doesn't add much from a technical point of view as it is just signing into a few account to get the data into Google Sheets for downstr...

So the next project for me will be to integrate step counts from Google Fit. Given my improved knowledge and understanding of the tools and infrastructure I am using can working out how I am going to do this and use this data before I start.  The first step was connecting to the Google Fit API and extraction the relevant data. I will admit that I did the standard developer trick and followed an online guide / stackoverflow to get this done, my main source was the link attached.  My next steps will be:  Use Keboola to connect and load the Google Sheet as in In Job As an Out job deposit the data into Snowflake.  Use dbt to transform and load the data into the final star schema.  At very least number of k of steps / day would be good to have on in my exercise fact tables. If I integrate this early enough and in enough places it will test a lot of by dbt understanding if nothing else.  As part of this I am going to also create a proper date dimension and integr...

Say you have a massive table but for ease of use for reporting you need to split numbers out. For example say you are a service that provides numerous different services, each person gets a single form with a mark for each service provided and the transactional system stores this a a row per form per service. For reporting you may well want to pivot these out and have a separate columns for each service with a 1 / 0 and a count for each.  In traditional databases and tools you have to create all your views in advanced and you cannot pivot on a list of values. The joy I am finding with dbt and jinja coding is that you can create the views dynamically. And whilst that may pose some risk if means that in this scenario a new service code is catered for automatically as you can dynamically generate the column by looping through a list of values.  I have applied this logic my strength exercise data, pivoting it from rows to columns. Below is the jinja code and the resulting SQL,...

The more I used dbt the more I like it. I am finding many of its features really useful and I haven't even done the training on macros and packages yet so I feel there is more to come yet. In the meantime I have now start to, just of the fun of it, create some downstream views with dependencies on other steps and a function in SQL. Happy to say it is all working really well and using jinja (and my Snowflake function ) has saved me heap of time coding.  Sources yml:  View using the source function (results in SQL) View that references the output from previous steps, allows them to be linked:  Assuming you create your sources in the yml file and reference previous steps using the reference function rather than calling the resulting table (dbt handles that for you) (as shown above) it will automatically work out the dependencies, run things in the right order and produces a lovely lineage graph like so.  I am hoping to stop playing with what I know of dbt and might make...

So as part of some stuff I have been doing in my day job I have built some useful and decided I should build a basic function in Snowflake before moving onto something more complicated with table functions and the like. So my basic function is one that accepts parameters and uses this to determine if an activity is +- 1 standard deviation from the average over all time for that activity.  I actually call the code above using dbt to create the views and tables and will show that in my next post. For now here is a screen shot of me calling that function using jinja scripting to create the different function calls. 

Have I finally found my BI Tool, one that lets me import data from Snowflake and share it for free? I know, no sooner have I posted about how hard it was to find a tool that could do anything from Snowflake than I come across Zoho. You can check out my dashboard on the following page . Below is a diagram that outlines the processes I have used to obtain this data. In summary my parkrun e-mail is pushed to Google Sheets every week by Zapier and Forms I submit every day are used to track the strength training I do. Keboola is then used to ingest this data into MySQL and or Snowflake where I then use views or the built in transformation processes in Keboola to shift the data into a format for reporting. Google Data Studio then connects to MySQL and Zoho to Snowflake to visualise the data. 

Whilst a lot of the information I am post on here is about how to actually build a free cloud data platform for yourself there is also some good training out there. Whilst you will need to pay to get access a lot of places want to entice you in with certain bits for free. In the data engineering space a popular option for training is places with hands on labs. With the rise of these cloud platforms training providers are able to spin up mini instances with lots of restrictions and allow you to do hands on training without fear of using something outside of the free tier.  As I said most of this training is not free, I did a trial month at Whizlabs and got myself a certification on Snowflake and AWS with hands on experience using their sandbox areas. Honestly despite finishing that course I don't feel like I learnt a huge amount, the labs were too regimented and the trainers were not overly engaging. If money were no object I would give A Cloud Guru a go but I am looking for free ma...

To continue on from my last post I have kept on playing in Snowflake and am working towards building something that resembles ETL Procedures on Snowflake. Now as this is just something I am messing around with at home it is much more about learning that doing things 100% correctly.  For me the below script was the first time in Snowflake SQL procedure I have:  Used Exception handling  That works and inserts into a log table, now to see if I can log the error message Performed a dynamic insert using data retrieved from one of my own tables.  Whilst these are not earth shattering they are little thing I was not confident on how to do this morning and now I have a working example. I was not confident this morning as I am honestly finding it hard to find real world examples written in Snowflake using their SQL language, all examples seem to be in the Javascript one.  As with my previous post below is the code and a little gif showing the code being called. 

I have been working with Google Data Studio for some of my visualisations. Now I say only some because I still have the issue with not being able to get it to connect to PostgreSQL and the Snowflake connector is a paid product. To address this I am looking at other products and am hopeful about Retool but will see what functionality is left once the free trail runs out. I am hoping that I am only using the free stuff.  Building my dashboard in Google Data studio has been pretty easy, there are some things I don't like or can't work out how to do but I am more of a technical data person than a visualisation person, at least for the last 5-6 years that has been the case.  Honestly the best thing to do it stick a data source in Google Data Studio and have a play. You basically pick a theme and drag and drop your dimensions and measures in and watch it build the graphs on the fly. Below are some graphics of the meu options, inserting a graph and manipulating it. 

For part of one of my free ETLs I am using Zapps to transfer e-mails from Google Sheets and then Keboola to transfer the sheets into my Snowflake database. I am familiar with string searches and cleansing in Oracle and using python but have not had the chance to do this in Snowflake. I wanted to give it a go as a proof of concept if nothing else. There were some difference in functions between Oracle and Snowflake, no INSTR and using POSITION instead and some difference in working with dates / timestamps but overall it was very similar.  The code below is what I ended up using:  I think want to use this to create some overview graphics to allow me to track the success or failure of my ETLs. Assuming the aspects of Retool remain free you can see how much ETL is going on this link .  In case things aren't working, here is a table of the output I am producing. 

Not much text to put in this one but a comparison between create table scripts in Oracle (my normal), MySQL (did not like) and Snowflake (Was very easy). I think the newer version of MySQL would have been easier to work with but this is what I have to work with on my free instance. The next step of this project is to see how easy it is to build a little package that runs these controls. who knows if I can get it working in MySQL or Snowflake perhaps it can be worked properly into this project with its own dashboards. From the Googling I have done so far I am not optimistic about MySQL but I always have the backup of creating something in Python and seeing if it can work for all 3, you can even get free python web servers (I think) so that might be an option.  Note that for the Oracle code I have already created a basic version of the package.  Oracle Code:   MySQL Code:  Snowflake Code: 

As part of the project to work on a free data solution I have been looking into data visualisation tools. I have already done a post on Snowflake which has limited capabilities, I have also used Power BI however this has limited sharing options on the free plan. In my day jobs I have used various Oracle tools including OAC utilising the RPD and really badly I have used Excel.  Personally the best free tool, honestly probably the best tool other than it missing the data modelling layer, is Google Data Studio. I have found it fairly intuitive and for the most part easy to get the results I wanted.  Probably the bit I was most impressed with was the data visualisations stuff, see part 2 (on its way), however setting up supported data sources is also very easy. Note the at the moment Snowflake is not supported by Google itself.  To get going: Create a data source, this can use a Google Connector or one of many of their other customer produced connectors.   Once you ...