Gen AI Data Guy

So recently parkrun removed all their stats and as a keen running who is trying to work their way up the top 100 of their local parkrun I wanted to get some of these stats back and have a bit of "fun" at the same time. So here is a little "ETL" process that I developed with the help of Gen AI.  The steps of my ETL:  Copy and paste data into Google Sheets template where an AI produced formula extracts URLS from the text and puts them into a new field. This effectively allows me to extract the parkrun athlete id, the primary key, and use it in my analysis. I also have a column to autofill the data I am processing.  Use an Gen AI generated Google Apps script to process it into a processed sheet, this allows me to build up a backlog of events (I had over 500 to process).  This is then queried using a Gen AI Google sheets query to extract key information and columns / format times etc. I then ingest the fully processed sheet into Keboola directly from Google Sheets. ...

Over the last few days I have been playing with Streamlit. This is a really useful library that effectively lets you build an interactive dashboard using python. In theory, and somewhat in practice, I can now / have handled extracting data from APIs and DBs, transformed the data using pandas, loaded it into my final database and built a dashboard all using python. From what I have read it can be setup as a form, if I could make it private it might allow me to have direct data entry into my source database as well, else I can still use Google Sheets but might implement it for fun.  The dashboard below is embedded so it should be updated as I progress with my code. The code below however is from a one of gist, you don't seem to be able to embed from the github repo, but you can find the latest version of the code here . Apparently the embedding of the code failed so just check out the dashboard for now and I will try and get that working.

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 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...

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. 

One of the tools I am hoping to get to grips with is DBT. It appears to be a very popular tool at the moment. I think with the trend of moving to ELT having a good tool to perform your transformations is important and from what I hear DBT is good.  I have signed up for the free DBT cloud developer account and connected it to my Snowflake instance but after that I am not quite sure what I am meant to be doing. DBT has its own training so I am starting with the dbt fundamentals course. The training is supposed to take several hours with a few more hours implementing the hands on project and gives up a badge for LinkedIn or something. I am more interested in trying out the tool and seeing what it can do, for free, for this project. I have looked into quite a few training courses over the last few months, looking at all the tools I am using for this and things like AWS and when it comes to actually being useful the dbt training is at the top so far. I skipped some as it was basic for s...

Really finding Keboola was the thing that kickstarted this project otherwise I would be trying to build custom code on a python cloud server and building everything from scratch.  In Keboola you build you data sources and destinations using connection details which is fairly simple and something I will likely cover in another post, same goes for transformations etc. Here though I am going to discuss Flows, this is where you bring everything together. On my free account there are some limitations.  My easiest flow is very basic:  Pull parkrun results e-mail from Gmail to Google Sheets (actually done by Zap not Keboola).  Keboola will, as often as I like, in this case once a week, pull the data from the sheet into its storage.  It will then transfer this to the target database. Currently I have this setup to be MySQL database but I can and might expand that to the Snowflake instance within Keboola.  I then, outside of Keboola, connect to the MySQL database f...

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. 

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: 

For this project I seem to have ended up with:  Oracle Database on my local Machine Snowflake DB through Keboola, cloud hosted on Snowflake MySQL DB through free MySQL database website hosted in the clod.  PostgrelSQL through Elephant SQL.  Plus ended up with a test DB on SQLLite.  Whilst this is great fun logging into lots of different sites and browsers and tools to access all the different databases was a pain, especially as I am sure to working purely in SQL developer. After a quick Google I found DBeaver .    As you can see from the above screenshot I have successfully connected to all the above databases from one tool. The tool even handled the downloading and installation of the drivers, I just had to work out the basic stuff and I was in.  Now I just need to work out what I want to build in each database and how it fits into the overall project I am working on. 

Keboola is my new favourite toy and I have lost many hours playing with it and so far all for free, they even have free training which I have mostly been able to follow with the free account, though the tool is clearly evolving quickly and the training is now over a year old.  However finding Keboola and the free MySQL databases are what inspired me to start this project.  What I like about Keboola (Free):  So far (over a week) completely free and no credit card required. I am not anywhere near looking like I am going to use my allocation for this month.  Easy to connect to Snowflake, Google Drive, Google Sheets, MySQL and much more.  Free use of Snowflake, with Snowflake being so hot at the moment this is great.  Lots of logging.  Decent training that is mostly useful,  The dashboard: What I don't like:  I couldn't get the gmail connector to work due to Google Security policies.  Product is evolving so quickly that training is already d...