Building an ML Platform from scratch

Key Takeaways

not like what we're live so okay F cool nice okay cool thank thanks I'm ready to just do it happy let's start with I'll walk through so last time we ended and we had some sorry for my voice had some pretty obscure errors from SQL mesh and we couldn't figure them out and so I offline tried to figure them out and I did figure them out with the help of the SQL mesh Folk and so we do have the the three models connect together running in also some some useful learnings from the SQL mesh people so we'll walk through that we'll look at um the state of the models that we have we'll look at the UI we'll look at what plan does and then we'll look at what run does then we'll make a breaking change so just to kind of close the loop on that and then we'll move to the next step which is there's two things we can try to get done one is integrate prefi so we actually have a way to schedule these and we'll start with it locally but the nice thing about prefect is that it's like kind of also running in their Cloud it's it's straightforward to just have it in their Cloud too but we'll stick with it local for now and then we will um and then we will try to get the GitHub actions working um such that when you open a PR excuse me when you open a PR it does the test to see if there's a breaking change change in order to do that we have to push the duck DB file to GitHub so obviously that's like not scalable but also because SQL mesh uses SQL glot the whole kind of point is you can have multiple sources and at any moment in this project it would be with the exception of that HTTP request model it would be instant to switch the switch the underlying engine from Duck DB to some other uh database engine so it doesn't actually matter but we'll just keep pushing with this for now because we want to stick with free and free and open source for now are you gonna give us your hot take on ducky B and Delta no no definitely not definitely not that is a only for me to know stop it stop it this Channel's rated G like oh my God he feels you feel embarrassed just knowing huh yeah why do you why do you do this to me I I'm trying to do is just be a be a good person help help build some mlops can you see my screen no hold on I'll bring it on to the stage there we go now we see it and let me is it big is it big enough no definitely for not for me at least uh maybe for Eric have it now it's all right that yeah that's good what maybe on the next live stream I have to on the next live stream I got to connect to my monitor because I can only see like five lines of code right now okay so uh we don't need to rehash the actual error that we were getting it was really confusing one was around the level of nesting of the schema of like the model names and I learned that nesting means like you can't have one model that has like SQL mesh. hourly scrape and the other one that's like SQL mesh. fu. hourly script they all have to have the same essentially number of per period decimal places in them like the same schema um table uh depth however of that like sorry what are the implications of that isn't that isn't that potentially bad does that mean you couldn't have like sometimes I thought you just wanted to select things from multiple databases to be able to combine them in different ways like if you had like a mark okay you can select for multiple databases this is saying when you're creating your SQL mesh system this is like I think a pretty small implication 90 whatever percent of databases are just schema name. table name like that's that's all we're really talking about here is just that if one of your things has a schema and a table they should all have the structure scheme and table it doesn't impact what you query from it just impacts the final models that could created within SQL mesh but the bigger problem was that one of these was not written like this it was written like this and obviously within SQL um there's a difference between single quotes and double quotes single quotes are string literals double quotes are identifiers what I did not realize is that even though this is a called a SQL file I didn't realize that the model definition was also technically SQL literal for from the purposes from the perspective of SQL mesh and so this right here was not being referenced as the identifier table SQL mesh. hourly scrape this entire thing was being passed in as a SE as a as a string literal to the model definition which is why SQL mesh thought that there was no essentially no decimal places no nesting levels within the database and that's why it complained because this had technically no nesting levels and this had one nesting level I found that super confusing um but we reached out to the SQL M team and they agreed that they should add more uh documentation linting well documentation but also linting like that should just throw an error like you just yeah shouldn't be allowed to use a literal there yeah similarly we are the next error is I also had that um which I assumed is fine because I'm telling them the name of the time column but it's not fine this also caused errors when we were seeing those problems with time stamp could not be formatted we thought it was coming from this right here it was not coming from this right here it was coming from this because we were trying to cast the string literal TS into a timestamp rather than the reference to the column TS and so we talked to them they kind of wore a line that more linting would be useful there and so that's something they're going to do um so let's talk about like what we have I have to zoom out I'm sorry I I can't see the whole screen um this is like in this model is an example of the entertainment of duck TV like how fun and cool it is to work with in reality would this be the way that you do this if you're all in on duct TV maybe um but this obviously can't be transpiled into a different sequel dialect um and so this also makes a lot more sense to be uh to be co uh what am I trying to say to be python code like this makes a lot more sense as a python model just to recap from where we were last time and then just a reminder like what that statement's doing is as part of a sequel query it's making an HTTP request to an API it's getting some Json back it's flattening the Json and turn and then it's turning into a table like all that's done in a select statement which is kind of crazy but also pretty cool what just happened how do I not have python yeah in can you say that again Eric yeah like um there's a RuneScape API that we're hitting so we make an HTTP request to the API to get prices like price data for the last five minutes um or for the last hour I mean so you might think that you have to write a python script to hit that API and get all that information back as Json but we actually aren't we're able to to make that HTTP request directly from a a duck DB SQL query because they have a duck DB SQL happens to have a function for like making an HTTP request and like flattening whenever comes out so anyway H okay well while this is installing maybe maybe uh my partner in crime over there changed this to 310 and I don't have 310 installed so I'm going to change it to 3.11 so that we don't have to sit here and wait for my pm to stall you can use you can use UV to manage python yeah I know uh but I don't know the syntax so I'm just gonna stick with three. 11 for this nice I love how you automatically blamed Eric for that one well seeing as it was working yesterday and I haven't pushed any new PRS from last week I'm definitely gonna blame him you're giving me like I don't I don't know that I actually did any work on this between streams so um okay we're going to do a select star my voice is pain select star from this just so people can see uh they can remember what it was uh close the thing and it was coming in as this single sing painful single Json file um it has it has like 37 100 keys and each of those keys is a number it's the idea of an item so it's basically a hashmap from like items on the marketplace to like metadata about them so the top of the seagull query those Json extract lines there like 18 19 20 21 those are all basically looping over the individual keys and like grabbing grabbing some Fields off of the nested objects this is this is what it looks like each key is an item ID as a string and then we get the four elements for each one and so we essentially want to unest them so we grab it at the start time of that duct that you uh SQL mesh gives us so we can grab you know if it runs multiple times because this is a Time series model that we set the key the kind is incremental by time range so like we were talking about last time the KRON is hourly if we run this every hour it'll just run once but but if we wait 3 hours and we run SQL mesh run because SQL mesh has um State Management built in like they know when it was last run they'll run this model three different times for three different start and end time stamps and so we use that start Tim stamp here to make sure that we get it correctly within the query and then we unnest it so um unnest requires a struct this comes back as Json we cast it into actual like we we push it into Json um from like the literal string and then we can unnest it and then we take the key because these are because this uh uh dictionary is only nested like one level it's the key well it's two levels it's the item ID and then everything else we only need to unnest it by one the key is the item id like we saw that's this and then we just have a simple unnested dictionary which is these four things this syntax can I explain it no um however uh chat GT gave it to us and you know you only need to learn it one time uh and then and then you just cast it to the to the um types and these are all integers and now we have our columns so this takes this out and gives us an actual row by row table of the ID and then the average high price average high volume average low price and average low volume um and that's our first model um maybe this is a good moment too just to give for the people that are just joining the overview of what we're doing like what is the idea here sure um here as in for this Runescape or heres in this live stream live stream live stream the goal of this live stream is that Eric and I have been building mlop systems for a really long time and there are more tools than we have opportunities to play with with um in our jobs and there are a lot of tutorials out there that talk about like one tool talk about demo examples don't really take it end to end and there's a lot of nuance when you actually connect all these systems together and so we're trying to build an extremely RAW live stream where we continue to build this out um session by session we're not really doing that much prep unless it's like debugging offline things that aren't worth seeing like the string literal stuff um and then we're going to essentially build the mlops stack and for the next session Eric we'll put together an architecture diagram and we'll walk through that architecture diagram and we're going to build it in a way that we can swap out tools so like right now we're using SQL mesh and duck DB and we're going to orchestrate it with prefect and do cicd with GitHub but once that's good and going and we have experimentation and tracking and observability to the whole thing we'll pull something out like we'll pull out SQL mesh and we'll replace it with feature form which is another open source tool that is really really cool does a lot results in a lot of the same great functionality but takes a totally different approach to building Data Systems and I'm a huge fan of it so we'll look at that and maybe instead of prefect we'll we'll look at airflow because a lot of people like it instead of one thing we'll look at the next we'll keep pulling out zml and and and such Eric has a list of tools he wants to try too um and by doing that will get to play with a lot of these tools people will learn what is good about these tools what's bad about these tools how strong the open source Community is for all these tools and we continue to iterate like that and so now for this one we're Gathering data from where and what are we doing with the data to then the end goal ideally in an ideal world is to do what right like make predictions yeah right now we're Gathering data from RuneScape um because Eric really likes RuneScape and they have a really it was easy it was data that was easy to get like exactly exactly so RuneScape has for those who don't know what is RuneScape so RuneScape is like it's one of those it's one of those huge online multiplayer games and so it's it's kind of like if you know anything about like World of Warcraft or examples but there's you know the games where you can go do things and level up like gain XP and level up so like so runes if there's like a Marketplace and that's what this data is from and this Marketplace allows players to buy and sell items from each other and they can they can set the price that they would like to buy something at and they can set the price that they would like to sell their items at um and if there is an item being sold by that price then they can they can buy that and if there's not maybe they have to up the amount that they're willing to bid for someone else's pickaxes anyway so like um it's a it's a real enough market that so they they published they published information about the transactions on this Market Marketplace online as this API so we're we can grab information about the price of each item every day for the last like the average price that items were bought at every day for the last like hour the last year um and maybe we could use that to like win the RuneScape Market we could like train a model that helps us Buy Low sell high or something um that isn't much different than doing stuff on stock price data and I don't think Ben and I are actually really interested in pursuing a stock price machine learning project all the way down so right now this data set is sort of what we had so that we could get on the stream and start showing SQL mesh but I think that we're on the hunt for a cooler data set and data problem to solve on the stream starting in the new year so so if anybody knows cooler data sets we've been looking we would love a data set uh recommendation from folks this whole thing we we are such a solution looking for a problem it's like like the definition of wanting to use the shiny tools without a reason so excellent all right well don't let me block you anymore okay so we have uh we opened up the UI and um I have it B a, tapped open it does not matter this is the SQL mesh UI and it lets you see all of your model in one place and also your entire code base which is nice because in SQL mesh you have audits you have logs um macros macros models seeds which we're not going to use it could be like data that's if you have like a CSV file that's on disk that makes sense for you as your seed you could do that um we're not going to do that because that's like not a super legitimate that's not a super common real example and then you have your models which are you know the core of what you're working with here um and then SQL mesh saves all the state so as you query one model to another model it showcases what that model is what it pulls from and what it pushes to and so we have two SQL models in a python model you could see I'm looking at the hourly script SQL model that feeds into this SQL mes train model similarly the RuneScape feeds into the SQL mes train but if we click into the train model that is pulling from both of these how is it pulling from both of these it's very simple and it's really quick actually could you use the column level lineage feature to show H PS like could you hover over one of the what what's happening I think I I don't know if the python models give you that oh they I don't think they do I don't think they do I don't think they do um maybe they do if someone on SQL meshes uh here and can give us an example that would be cool or tell us about it but my understanding is that they do not uh although I mean maybe they should be able to because you know you do have the the things um so okay what are we looking at you with your SQL mesh python model you define kind of all the same things but in Python you define the columns that are coming in and out Define the model kind um you can define a batch size so if you define a batch size you can have it run even if the KRON is hourly so you want this to fill in data at every hour timestamp you can have it fill in in parallel at smaller timestamps let's say one hour's worth of data is actually too much for your organization you can have this by setting batch size and batch concurrency and interval unit you can have it fill in every batch size being SI five interval unit being minutes batch concurrency being let's say two you can in parallel fill in at every five minute intervals until you get to to the hourly cron um that you're supposed to be hitting so in here we Define um with the definition you get some function uh some some arguments that come automatic with SQL mesh the execution context which has all of your variables and things like that because this is an incremental by time model you get the start and the end of that increment you get the execution time and then any other additional keyword arguments which you can Define in your config file but we have not defined any for this use case so with the context you can Define the table and so by defining this table SQL mesh knows that this python model is dependent on this other model this model defined over here um and so it knows that dependency and that line alone is just how you're getting this dependency graph and so we Define a wear Clause we pull from the mapping data frame which is our full model it does not go incremental over time it just pulls everything from the API or from yeah from RuneScape the hourly pulls the hourly scrape of data uh from uh the API as well and then does that Json nesting here we merge them together and then we're going to do something with the data frame and then we're going to return the the data frame um and it will show up in our in our um in our duct TB environment where we can pull from it so we'll do a plan so that we we're not going to do it here uh we're going to do it in the CLI because it's more like real world um and then it will materialize all of these things so just to uh be really fresh with it we're going to remove our database oh in the wrong place uh I am where the config is so if you're going to run SQL mesh the CLI and you're not exactly in the directory where your config is you just pass in- P give it the to your config and then everything works so we're going to run uh you run SQL mesh plan and we're going to plan it in Dev is this repo open for anybody yeah and everything's up to date um Eric it's it's SCE right it it is it is I think uh I'll figure out how to get into the live stream chat and I'll post it yeah yeah perfect so you can see when we run plan we have an a fresh environment nothing's ever happened before it's going to see that we're going to add SQL mesh example that hourly scrape but you'll see that it adds double uncore Dev and that's because we ran plan Dev I could have run plan Dev Ben plan Demetrios plan whatever um and what this is is is it's the name of your virtual environment and we'll see why virtual environments are so cool in a second but just note SQL match example. hourly scrape is like the the schema without a Dunder is is reserved for prod with the dunder is reserved for the environment that you're working in what this allows you to have is that Engineers from your company from your team can all be working on Virtual environments independently with their own data that they never in uh conflict with each other and then SQL M how to reconcile all of that such that uh query is never run twice so it it will it will know that if that query has been run in one of the virtual environments it won't run it another virtual environment it'll do a pointer swap um and it saves a lot of a lot of a lot of query compute which is really cool so it comes in here and it knows that we need some back fills because there's no data in the system at all uh models needing back fills so hourly scrape is or these these are our three models and they're all scraping from December 12 2024 to December 19 2024 why December 12th like where is that coming from it's coming from because I defined the start of this model as December 12th that's all it is you could Define it as something else you could not Define it at all and you can see right here like what is the backfill start date I can do back from the beginning of History I can do anything I want but I set them as start dates of 20 of 1212 and and that'll allow it so should enter enter the backfield end date I'm going to do because my models are hourly um I'm actually going to have them back fill into um yesterday so we don't have to wait the whole hour right now so I'm going to have them back fill so we can see what a run does 12 18 and then we're going to apply the back fills and we got a fail can I Source query from an empty data frame oh yeah okay so this is the this is actually something this is a feature request I opened from them it's it's a nice new error message you can't um return an empty data frame from uh a python model um and that's what we're doing if it's empty you have to yield from nothing why who knows so um we're going to do uh yield DF if Lane DF else y or actually we we can't because it's a from so if l F uh yield DF else yield from this so why are we yielding and not returning it's because well right now because uh we have to yield from here and I don't have the nuances as to why that's a requirement from SQL mesh but a benefit of yielding is that you can run post statements and so perhaps you're connected to an iceberg table and you right here you want to run a compaction or literally anything else they're considered post statement so the model will run and then this stuff will run asynchronously after um the uh the data is yielded from this model so that's a nice thing so one question also is why uh it didn't return any data why was it empty but we can take a look at that in a moment and for right now um we can do the plan 24 12 18 and we'll do the back fil okay so the environment's been updated so we have three uh tables now three models we have our RuneScape mapping train and hourly scrape so how can we like see what's going on we can run UV run SQL mesh fetch DF and all you're really doing is you're writing SQL we can see our hourly script that's empty and that's how that happened and we'll look at our RuneScape mapping he going have all this data and so train will be empty because we did a we did a merge we did a join of those two uh models and so why is it empty I have no idea let's figure out why it's empty um no hourly scrape well let's run you run duct TB and let's do that and instead of uh starts we'll do time stamp uh 2024 128 that is a different timestamp than that any idea Eric I was looking at the the chat for a sec trying to get everyone the repo so you just you just try to plug in the Tim stamp from I just start date yeah the query um yeah if I if I pass in 2018 here in in the RuneScape API I imagine that this happens I bet you that this is I bet you this is time zone mapping yeah time stamp with time zone yeah that would make sense do you want to try oh yeah how do I had a time zone yeah that's so annoying yeah like un Zone it it should be UTC like um yeah it's probably yeah I have no idea time stamp without times I don't want it I want UTC how do I let's just see let's just see something that worked fine um then so so it's oh it's this it's two timestamp as TS um that it's going to be your issue and I wonder what start DS actually is when we run it I I want to say that the same default SQL M would give us as UTC time like um but yeah this two time stamp is giving us [Music] um although it has the time zone in it I feel like SQL mes should have no because I'm no obviously not because uh I'm putting this in two time stamp uh um chat gbt just gave me a funny suggestion of adding at time zone to this like what do you mean um if you look at line 16 it like it's claiming there's this at time zone statement um and here as well let's try it we shouldn't need both yeah um let's try this one there we go see what Moment of Truth no no no I need to uh get rid of the starts 20 24 12 18 that did oh no no because it's not at time zone UTC it's at time zone EST because that's where I am I would think well wait I think it' be relative to the API like whatever whatever the API accepts so I feel like the API is probably going to accept UTC um yeah yeah it's still 1219 maybe it's this maybe it's this no time zone UTC I wonder if that's the thing man time zones they're the worst thing ever is anybody in telling us the answer yeah time z um yeah the output the output column it no longer says time stamp at time zone whatever it now just says time stamp so um well now it does now it says time stamp with time zone but cast wait wait a second I am I am confused gota be honest I'm a little confused um I thought I had this working last night put in 2024 1218 hold on if I put in 2024 1218 this API is returning this this is December 19th at um 2 p.m um so that feels like I put in 2024 1218 how did it get to 2024 oh oh oh today the today that we're the day we're recording is the 19th and I think that the you think this is just this is I think it just needs like Unix time oh we are doing epic though okay there's a0 zero at the end of that maybe that's the it's also weird that epic returned is that working 173 okay then we do this and then now we have he that looks right so maybe it's so time wait so hold on Epic Tim stamp and then maybe have to do integer because it's returning this float like that's what's happening and then um you want to try yeah what happens when run that little statement on uh yeah you got it yeah that's annoying me okay hey we're getting somewhere we're getting somewhere nice look this is just what being this is just this is just what it's all about this is like a vlog a day in the life of Eng yeah literally yeah it's just being unhappy all the time everybody should become a software engineer dealing with time zones it's it's it's actually a nightmare hey the day changed it's 17 now um yeah because this is with the time zone so this this I I want to say makes sense if I do um the at at uh what was it UTC like that uh let me get back to you um see yeah at time at at space time space Zone space UTC yep that's it um and then this should be like start date right like uh like this yeah yeah I just wanted to make okay there we go now we have it this is exactly what we're looking for hard a date back but like I'll put you could put it you could put it back now start no no no it was like uh start DS like that ah nice okay so let's do a UV run SE mesh plan Dev that's good it took some time to run EV run SQL mesh fetch DF we have data we have data so what did everybody learn here 12 is the day yeah because we well this is there are there are many many rows um for but they're all from the 12th like both the starting in and rows starting in oh oh that's interesting can we see like what was our why start right there it seems like so you put the start date in the in the model de definition it seems like it's only using that one like it seems like it's not running this once per hour um or um yeah that's weird why would that be why is the grain Item ID in time stamp on line nine because the that's the primary key here I thought thought time let's see the primary key yeah this uniquely identifies a row Item ID to add oh right okay what's also interesting is I feel like it's also odd to me that TS is a date and not uh a time stamp seeing as um we clearly cast it to a time stamp like and also yeah and also we need it to be a time stamp because our cron is hourly what is TS yeah what if I do SQL mesh you UV run SQL mesh run Dev it finished that doesn't make any sense because I had it run until yeah yeah that's what uh yeah that's what I must I must be using this wrong I'm going to assume that I'm using this wrong and we're going to try without it and see what happens every time you run RM dbdb it's wiping the entire data Lake yes yes yes 100% as we as uh as is the case uh with an example yeah that really doesn't make any sense to me someone's saying in the chat why not add a column like created at it's a good point let's try it um sorry uh start DS as why don't we call it instead of created that we'll do created start uh at end DS as well this is a good example we'll do a we'll do a plan it's going to recognize that there are two new columns here which is really nice um and it will say uh models needing back fill um so like things that are breaking not breaking but things that are um uh needing to be refreshed you can choose to refresh them or not we're going to refresh them and what you'll see is train is indirectly modified but it's not breaking because we didn't remove a column we added a column one question though is does it have the Nuance to know if we're removing a column that train is not using would that be a problem or if we're modifying column that train is using does it it doesn't seem like it knows it can't map the python dependencies to SQL ones and so um it can't M the python dependencies to column lineage um so we'll have to see if that what happens there so we just asked if we wanted to backfill and it said the start date was the 12th and did you leave the end date blank so it's [Music] now yes and the end date is the NS and so this did this to this even though we set the cron to hourly it knows the time color I wonder wait I would have expected okay that's not exactly what I expected however what we can do is we can add the batch size to be so Kon is hourly wait no but the Kon is hourly yeah yeah it's going to run so so okay so when it runs it's not that it's going to run one per hour that's what the the batches for that makes more sense to me this is saying it will run from whenever the last time it ran was up to um um up to the most recently completed window for that cron so like since it's hourly it will run from 12 in a single shot it will run from 1212 through um uh if it was right now 12:19 at 10:00 a.m. because 11 am hasn't completed so it'll run at 10 a.m. if we did it you know for for every 10 minutes it would go to 10:40 a.m. but that's not actually what we want we want we want it to run like multiple times like once per hour and so we want to set batch size is equal to one wait it's batch size I wonder if you want columns commas here we'll find out batch size one in the interval unit is actually interval unit default is the same as whatever Kon is but we'll we'll we'll go explicit let's do a SQL MH plan Dev I think it's the comma expecting a closing parenthesis see oh sorry I did equals my bad so maybe now it's come okay invalid interval unit extra inputs are not permitted is it not in there oh it goes outside of it interesting I would expect that to go inside of it but that's okay the interval unit will be hourly we'll do it in a batch size of one hour should be year month day hour interval unit is hour cool okay so it's just metadata updates so it's just updating the increment how is a question is how do you wipe a virtual environment because your whole point look at you don't want to delete the whole database obviously but I do want to delete my Dev environment like I want to force a back Branch like your SQL mes environment you mean my SQL M virtual environment so SQ m delete a virtual environment one thing that's really cool that SQL mesh if this doesn't give me the answer uh I don't think that's right yeah um what is cool is that if we go to uh the SQL mesh um if we go to the SQL I'm going switch my screen to SQL mesh if we go to the SQL mesh uh ask AI we can just ask it how to do it let's go how do I delete a virtual uh environment in their slack yeah it's it's super nice and they're constantly indexing and and updating their document it's actually a great rag we should have them on to do rag because they're getting all slack messages all GitHub issues all documentation all everything and here we go that I use it constantly uh nice yeah that's with it's with Kappa AI huh they're yeah they're pretty sweet okay so there we go we have okay that's cool that's I just that's a good that's a good thing to know it's obviously useful because you're deleting virtual environments you're not actually losing uh the prod environment mean it's just your personal workspace you probably have to do that constantly apply the virtual update why apply the virtual update let's see seq UV run much fetch DF well it didn't actually work the invalidate didn't do what I wanted let's do that and then oh we have to run the janitor I see so UV run SQL mesh oops okay and then UV run SQL mesh janitor clean up complete now we run uvh SQL mesh FDF hopefully this is cool so we run SQL mesh plan Dev it won't be a virtual update it'll be new models I'm wrong it wasn't there why was this a virtual update all right listen I'm gonna have to delete the database we're just gonna have to figure it out later there are no I just want to keep I just want to keep moving I just want to keep us moving yeah uh squl mesh has two weeks in a row eluded us so here we go um this is oh wow this is it's doing it we're running the query once per hour for the window of time which was 1212 to 1218 now obviously this is slow you can actually set batch concurrency to whatever I didn't know if duck DB I don't know how duck DB works with concurrency because my understanding is that you can only have one writer at a time and I didn't want um to figure that out live and so we're just gonna let this go there's so many hours we should up the uh you want you want to up it into just like from yesterday yeah that'd be good okay plus we're being so mean to RuneScape right now we're like hammering their API that's true that's a good point that's a good point um well no no no because I mean this is I mean you do have to do it like this they don't have an API for everything 2024 let's go 12 um 17 and we'll go 2024 12 18 nice we're back filling from two days ago to yesterday leaving some for yesterday to today so we can show that um exactly I don't know how concurrency Works uh there's 50 hours it's been 50 hours since two days ago I guess so this is actually extremely magical it's just it's just it's just so nice that you don't have to think about any of this the other thing we haven't talked about because we haven't needed to yet is with SQL mesh you have a look back window and so you might get stale data from your customers all the time data came in today but it was four two days ago you can have SQL will look back so at when it's doing today we actually look back and days hours weeks etc for old records and it will pull those in as well which is really nice yeah so now if we run UV run SQL mesh fetch DF and we have the time stamps the only so the issue with this is that we definitely lost data because our grain is ID and start TS but these are dates and not time stamps and so that to me is is extremely confusing yeah can we look at can we can we select the distinct times now well it's just it's these two it's 128 yeah why is it not a date you think it should just natively be a time stamp um um since we're doing the hourly however it's fine we have data we're gonna we're gonna we're g to move on um okay I'm curious like so sagl mesh saves the state of all the hours that we've processed so if we ever do this backfill again it's just going to skip the hours that we've already done the query for which is like very responsible and um and they store that state in the data lake so which in our case is in duct DB so it'd be awesome to like show all the tables in duct Deb and see if we can go find that state in there yeah so for what it's worth SQL mesh recommends not doing it not saving them in duct Deb having a separate database like postgress some transactional system to do it see whatever uh but I mean we're going to keep it like this it's fine for right now but that's just the recommendation so if we run UV run duck DB how do you list the tables in Duck DB is it dasd let's see I think there's usually just like a show tables command and no what on Earth D GT no I don't know um oh I'll GT real quick list show all tables is that what it is chbc just says show tables or select because wait connect tv.tv how do you um here I'll send this to you in slack there's a select star from information schema. classic here we go here are all of our tables so we've got we've got environment which makes sense hey yeah there's our underscore Dev table that was made from plan Dev and there's a whole bunch of seal mesh ones intervals sounds right that oh you did environments yep weird that they're saving it in this very Jony way and not as columns I wonder why um and if we look at for example yeah makes sense those are all the hours we processed that's so cool yeah it's very cool we should like delete one of them and then redo the back fill and see if only that one gets done I imagine uh I don't know they also they also store their plans as well ah plans are a different table I I I I would don't don't mess with their internal tables let them let let them let them do their thing um okay so we have so we have our so we have our train let's let's do something with our train so ff. train we have all of these things what would be something worth doing within polar that isn't worth doing I mean docdb can do everything but like assume you're not using docdb it would be nice to add something uh with polers in here um even if it's something simple like um like what if we just add a standard deviation um I mean that I guess that's also not hard but like what what do you what do you think um like you're wondering about a transform we could do yeah I wonder if uh what if we did like an estimated moving average of the low and the high for a particular item so do like a window function that averages the last 10 days or something like or just a rolling just a rolling uh just an I mean just a rolling estimated move moving average all right cool let's do um let's go and just quickly I mean are these These are in I asked seq watch to also support polers natively instead of pandas because you have to get the table and then um convert it to panders to to to polers um which I guess less than ideal but let's go pandas I like that the sidebar on your chat GPT is all related to this type of stuff I feel like if I show don't don't look at my chat if I yeah it's like very personal what my sidebar of my how can I find a meaning in life [Laughter] [Music] please okay so we're already sorted by time stamp which is great and then we'll add this and we'll add an estimate average em average high price um it's like a pretty standard um column and so we'll add it here telling SQL mesh so yeah SQL mesh like as you can see you have to be a little bit more explicit with the python models many parts of that are understandable but I have talked to them and they are working on a bunch of new support for python like def defining specific dependencies and uh just more native support maybe column level I didn't ask them about that specifically but there's a lot of cool um things to be done there about that so if we run UV run SQL mesh plan Dev it should notice that yeah we have changes just in train we have an EMA you could um now what you do is and you have options in your config to Define these explicitly or uh like how to handle these because there's no input from the user us um that you can Define by default anything that's changing is breaking or non-breaking but we're going to Define this as breaking um and then we're going to do a backfill um of all of them from 2024 12 again 17 to 20 24 12 18 SQL mesh wanted to be able to identify if that was a breaking change but for python code it has no ability to is that what you're saying it has no ability to not right now something that they want to add in the future and so you'll see we did a backfill for everything but like we said it did not rerun the queries for hourly scrape because it's already been run and and they know that nothing has changed so now if we do UV run SE mesh fetch DF select start from train we should have our EMA great um hey okay so wow it's already 11 that's unfortunate um we we ran a query on our mesh data damn that's unfortunate let's um let's do we got the backfill the backfill was actually really cool yeah yeah there was a moment of magic not nearly as much as I was hoping we would get done um let's do a new folder and call it flows Demetrius cut the back fill and set it on YouTube shorts and then it a whole hour and five seconds well yeah we appreciate everybody that's stuck with us for is anybody still on or if on we can there are people on yeah yeah for sure oh wow let us know if you enjoyed this debugging session in the chat yeah I wonder if for the next one we should if people are asking or answering questions in the chat ask if we should um prep everything for prefect in the workflows or if we should actually work through it because obviously working through it is slow like it took us an hour but if we're offline still getting the same value if we're if we're just doing the one thing and then we walk through it in the session so see if people have any thoughts on that at least one person enjoyed veses yeah I app by the way had good comments the whole time yeah yeah it was helping us de let's so in in the spirit of what I just said um I do have a repo with some of the feature with some of the prefix stuff um this is more than we need I'm going to delete a bunch of it don't worry but get rid of this uh run SQL mesh and build features um we're not going to do the um PAR file stuff but we can come back to it in a in a future in a future world we're not going to do the Delta Lake stuff where's my make file okay so now we have prefect installed um the SQL mesh rout is the where are we we're here we are in main so parent parent parent right parent parent parent I think we actually need well I guess and this is going to be the SQL mesot is in here oh no because we're we don't actually don't need this so we just need here because we're in the same place as that so do parent is going to be where it is and so SQL mesh root is here parent resolve and so we're going to default the environment whoa whoa we're going to default the environment to Dev right now uh oh I see run SQL mesh and then okay so this is a very very very very simple uh very very simple prefect flow what are we doing with it we're essentially just doing oh before I do this I apologize we had did the we had done the plan right we did the SQL mesh plan which was the back fill so we only ran it to December 18th we want to run it through December 19th our models are hourly and so um it's going to run it up to 11: a.m. so if we do UV run SQL mesh run Dev it's going to look at what happened it's going to infill the rest of the KRON and so that's where KRON becomes obviously valuable up to the latest of whatever that unit of time is it's not a cron of like being scheduled if you use their Enterprise offering you can actually have it scheduled um which is cool but we're not doing with that at this moment yeah so this is the hourly scrape it's running up to right now because with run I did not give it an end time if I wanted to give it an end time I could have e and I could have given it like up to now of of you know four hours or something like that like now or whenever it lasts r four hours Etc and so it's going to infill up to the the latest hour um and it will and it will fill in so what we just did is what is the thing that you want to have scheduled for you to run every single day right like you want SQL mesh to run every however long and to fill in the Windows of time that you've missed let's say you don't run it on the weekends but your customer sent you some data over the weekend it doesn't matter because whenever SQL mesh runs it knows the last time it ran and it will fill in the missing window so that's really all you might want hourly windows but you could run your SQL mesh thing once a day and when it runs one time it'll just execute 24h hourly windows so like so based on how we just did it like like based off of what we just did here the batch size is one if we didn't do this it would say what were all of the hours that were missed them so it's so it's not like you need to have one prefect dag per seagull mesh query and somehow you need to know the dependency relationship of the queries so you can mirror the the dependency relationship in dags you can just have one dag with all the queries and it goes to the dependency graph and it it knows which ones need to be rerun right and and to to add one thing that's not something they support in their open source but I think that they're adding to their Enterprise which I actually think is a totally reasonable place to to cut off open source but whatever um is when you do this your prefect flow it's not a deep integration with prefect right we just have a task that literally runs the SQL mesh CLI they have a a python SDK but there's some threading problems with it and prefect at the same time so I'm I'm literally just invoking the command that we just did you can add a bunch of other things obviously um telling you where the route is and we're running EnV so um and so that's all it is it's one task you don't get like when you run the UI you saw all of the tables you you still have that but in your prefect flow you don't have a task per model that would obviously be awesome because if and when it fails you can go exactly to the model that failed y whereas now if it fails the whole SQL mesh step fails and then you have to read through logs to debug they have it's worth calling out um airf flow an airf flow like what I'm talking about they have that for air flow so every SQL mesh model becomes an airflow I don't know what airf flow's terminology is but their their task yeah their task everyone becomes um its own and so it becomes easier to debug but we're starting with pref and so they don't have that and that's a limitation to to keep in mind or you can use their Enterprise offering you can get it natively within SQL mesh but the F thing about airf flow is that you do this and then maybe you want to move this into Iceberg tables or maybe you want to do some other thing and obviously with with prefect you have way more uh like you can run literally anything and and you get all of prefect okay so this is the flow this is just building the SQL mesh command our flow is just calling a single task right and there's really nothing else to it and then and then we're going to run it so I don't you're going to remember prefect FL prefect run I want to say it's prefect run SRC no prefect run flows main.py no UV run do you know what it is prefect what flow run I've not used prefect honestly did it change in prefect three because I've only used let's see it should be quick their docs are great so I can just r i can literally just run the file because right now um it's just a python file and they know that it's being run so it will run the flow um yeah so we'll just do that for right now UV run and then we'll talk about deployment so prefect uh sorry UV run python flows main.py and because this is a flow prefect will it'll pull in prefect It'll recognize that and it'll start uh the temporary oh interesting if you haven't oh I must have set the path wrong to much cannot be found point the CLI um um what is this here SQL mesh root should be flows I mean flows oh do parent do parent oops this is always always a thing okay so this is a okay there we go so it started the temporary server it ran prefect run and then you can see the logs SQL mesh ran successfully Run finish an environment Dev why was it so fast because we literally just ran run uh a second ago and so like what did we actually get from this um right now we didn't get anything because we're running it locally but the nice thing about prefect is that you just create a yaml file and then you run prefect deploy and then you add that to your GitHub cicd and that now lives if you're using prefix Cloud hosted option which I recommend starting with it's awesome um your flows all of them but this flow exists in prefect and then you can have them scheduled and so J can we share this link for uh with people um y SL prefect quick start I made this repo a while ago and I was digging into prefect um and this actually is a full flow so you create a yaml like this um here's one deployment um and you give it the path to your deployment and then you just run prefect um deploy and um it deploys your flow to their prefect cloud and then it will start scheduling what it schedules on is like that's kind of your decision as to what what you want to happen but this is like a work example you can deploy you just have to replace the path with your path and maybe what we'll do next time is we can show how to create a worker pool which is really easy you can have it locally so like prefect UV run prefect let's see if I have I don't it's a new computer worker pool uh I don't know the command off the top of my head unfortunately um we got a question Ben um can we have prefect run our hourly tasks concurrently you can have that concurrency happen in prefect or in SQL mesh you can kind of now you you sort o