Gen AI Data Guy

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. 

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

I don't want this blog to become the dbt blog so I have taken my time to post about this but do fully intend to do some more posts on dbt and the cool built in functionality. Equally I am quite happy with where my model is at at the moment so until I find some new tool to use or a new data source I am going to look to expand the section on the free training available.  dbt has the ability to cater for creating scd(2) style tables called snapshots , details of it are included on the advanced materialization training. I set up my first snapshot model by creating an scd2 table for the activity type dim, so that if I get a new exercise type added it will create a new rows, equally if I delete or modify one of the old columns it will end date the old row and insert the new row. The preference is to do this off a date column for change time however I don't have this so do the merge against all columns.  Snapshots sit in their own folder and have a fairly simple modelling structure ...

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. 

 So I enjoyed the first dbt training enough to give the next bunch of training a go. The first part of this training introduces you to jinja and using it to, for example, loop through creating case statements to pivot out rows to columns.  Jinja Code -  I can see there are lots of really useful purposes for this and am hoping that in I will find out later that rather than using a hard coded list you can use a query to generate the list of entries to loop through. This would be a great feature, one of the limitations of a lot of SQL systems is you can't pivot on an unknown list of values. Now onto learning about macros and packages in dbt.  Generates SQL Code -  

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