0:00Hello everybody and welcome to another Show and Talk Day. We're going to talk about how to build robust data pipeline with AI. I'm super excited about this topic because I get that question a lot. In case you haven't seen me online, if you're not interested yet in DuckDB,
0:22I'm Mehdi, also known as MehdiO online, and I'm a DevRel and do everything data and AI at MotherDuck. And so today, basically the idea is that we're going to take a specific use case,
0:39see what is like the most lazy prompts that we can do to build data pipelines, and then how does it look like to actually build, I would say, a better data pipeline. And spoiler alert, it's mostly about prompting and how you organize your workflow.
1:01But it's really interesting, actually, what sticks in them are, you know, fundamentals, I would say, of data engineering, what's new and how you package this together to basically make the best out of the AI tool that you are using today.
1:17And speaking of AI tool, I'm just curious, how much have you been using, you know, AI coding agents on your day to day basis? 20 percent, 50 percent, 100 percent. And also, what's your favorite, you know, provider?
1:35Do you do Anthropic, more OpenAI, Codex, or really just going full local AI? Let me know in the comments. I'm mostly on the Anthropic bandwagon, but often also switch to Codex just because of various reasons.
1:56And just it's also in terms of performance. I think sometimes one performs otherwise. I see John Kimi. I need to get my hands on Kimi and Olama because I'm poor. Yes, I do use Olama as soon as I'm traveling.
2:14It's funny. Whenever I take the plane, I, you know, download an image to Olama to work to that flight. Yeah, so let's let's go and dive in.
2:29So let me share my screen here and hopefully you're going to see everything fine. Let me double check. Oh, there.
2:46Right. So. So the first important thing to understand is that green is different than correct.
3:04And it's always has been the case in and I would say the AI words in the data engineering world. Sorry that you can have some silent failures, as we call them, where your data pipeline runs.
3:21There is data injected, but it's wrong at the end because it's not the right business rules, because there was missing data for various things. With the advance of AI coding, this is even more right because we are looking less at the code.
3:41Right. And we'll go into that later. We're mostly looking at the results. And so if the result is green again, it's not because it's correct. And there is two reasons why your AI pipelines will often breaks. The first one is that AI is non-deterministic.
4:01So when you ask data pipelines to be implemented, you get two different implementation. It's basically attempt different choice. And again, as I said, it runs doesn't mean doesn't tell you actually which one is correct.
4:20And the problem also is that you can you can always not review all the codes. So especially on complex pipeline, you should review most of the code.
4:32But I think as models get better and we are mostly in PR review, it's really important about, you know, what do you give as an instruction first and what's the contract to actually review. And the second thing, why often I see data pipeline failed in the past.
4:53No, it's mostly solved is that I cannot see your data. And when I say mostly solved, it's because if you look six months ago, there was no clear framework popping up actually more than six months ago. We're already in July, but I'm referring to MCP framework or skills that are also useful.
5:15But there wasn't any standards to how do you give AI access to your data? And I think there is still a ton of work to do there around sandboxing what AI can see, you know, we've just recently maybe a replica of your database, all the stuff.
5:35So the problem is that if you build your data pipeline and you just have mock data locally or it doesn't actually see your data, it's the same than when you were building data pipeline manually. You need to see production, you know, data, how it looks like.
5:52And and basically just just give that to your AI so that it doesn't guess your schema. It doesn't guess anything. And you don't get those silent backs, as I was mentioning, which are like plausible numbers, but actually wrong numbers.
6:10So this is a use case today. So we're going to go to NOAA data, which is actually let me show you. It's the global history called Climatology Network Daily. And it's a source basically of data of meteor climatology data.
6:33So we get you see information like temperature, where it was taken, the station, the latitude, longitude and others. Those data are available as parquet. And basically, this is a public history packet.
6:52And this is the prompt that you can use. And actually, let me ping that in the chat right now, if you want to try it.
7:05So this is the prompt that you can try and see a bit the result by yourself, especially if you are using various models, you're going to get various various results.
7:22I did this one with Opus 4. 8. And I'll show you the results of the various pipelines that I get from Opus 4. 8 with just this model, this prompt. You shouldn't have anything connected yet. We'll come into that. But yeah, basically, that's the prompts. That's that's the idea to to go towards.
7:46So let's go back to where it's what it's going to get. So let me dive quickly, actually. Into what it has been generated.
8:06So so this is the Python pipeline that he has generated. And what it does is that it uses DuckDB probably because of, you know, I think I mentioned.
8:26Yeah, I mentioned Motorduck. Right. So in the prompt, of course, just to be sure. And so it is DuckDB, of course, to connect to Motorduck. So how do you connect to Motorduck if you're not familiar with Motorduck?
8:40You can install any DuckDB client here. I'm using Python, but you can use Java, JavaScript, Rust, anything that is your vibe. And then it's just Motorduck is just an extension. And then you connect to the cloud like this.
8:56So here I'm connecting to a database and then basically I'm doing a one shot, I would say, query to load the data. That's basically the parquet data which is there. I'm using the wildcard here, you see, to basically load multiple parquet file.
9:16OK, and there is a filter here based on US. So a couple of things there that is wrong and we're going to see that. First, you see that it's printing me the average temperature. And so if I run this pipeline, I'm going to get 179 degree.
9:38Like what units? I think that's the that's it seems weird. And you see that the oldest reading temperature gets about eight thousand and eight hundred degree Celsius. So there is already we already see in the data there is something wrong.
9:58Aside from that, from the business rules, we see that he's been loading the data directly from a specific year. I was vague in the prompt. Again, that's the exercise to be as vague as possible in the prompt. I'm betting you and see what we can prove from there.
10:16OK, so I see that we have our coded value on the year, basically, because I never mentioned that I want only this year. Well, maybe all the data I see that we have a create replace.
10:31So which means that we're going to do a full snapshot every time we load this data for a new fresh data, which is not what I want. It's OK for small, medium sized data, but it's going to be really expensive if we do that every time.
10:50So the good thing is just incremental basically loads. We see that it reads the data value and it is. And the trick here, I know that because I know the data sets is that it's not the temperature is in tenths of degree. So which means that you need to divide those value by 10.
11:12So that's why the temperature here seems pretty high and close to hell. And also another information is that there is a QA flag in the data sets that basically said when there is a reading that is bad. So the source already give you a hint in the metadata when a reading of a temperature was wrong.
11:36OK, so just going back to the slide deck here. So this is the temperature we show. There is basically three silent bugs to recap. We have hard coded here, create a replace. So we are doing we are basically wipe the table every run.
11:57We store the temperature in tenths of degree. Nobody divided by 10. And the source flag is not getting it. So and the pipeline status, we agree. So that's a really good example that this pipeline runs. You get the data, but there is a couple of things that is that is wrong.
12:18There is the business logic and I would say the data engineering aspect on how you make that efficient, how you make that pipeline parameterized so that you can run any various year. So we'll get into that. And that's the first point of principle when writing pipeline with AI is that foundation still matters.
12:39Right. You can ask a big prompt, but you need specific data engineering foundation. Some of them I'm just giving you some of the I would say hot topics that I always use when I'm building pipeline these days with AI. But you can be much more specific depending on your technical knowledge.
12:59That's not an exhaustive list. But the first thing I would say is parameters. Often you want a data pipelines to be parameterized to run on a specifically time window. The reason is that if you have a failure on a specific day, then you can rerun that pipeline.
13:17If you want to backfill when you initiate your first pipeline and say, I want to run over the past two years of data, you can do that. The code stay the same, but you inject various different parameters. It can be a single date. It can be a year depending on your data shape for our data.
13:37If you remember, we have a partition by year, which means that we have a year partitioning key that we can use basically to kind of act as a window time for loading. The second point is independence, which means that if you run the pipeline thrice, you get the same results.
13:57You get no duplicates typically. So there is various strategy around that. But typically, in a nutshell, the classic one is delete and insert, upsert. You have also merge strategy. But the simple one is that you're going to delete the data on the existing window that you want to try to load and then basically insert it.
14:20So every day, if you load the data every day, you make sure that there is no specific data within that time range. Specifically, if you have an hour overlap and so on, that's where it's important to not have duplicates. And then you reinsert data. Being incremental loads. Sorry, that's like boring things that you already know if you're data engineers.
14:42But I mentioned that doing incremental load will be much cheaper on the long run than a full snapshot every time, unless it's a really small set of data. And inspect first. Inspect the data to avoid the gotcha on the business rules that I mentioned, like the temperature thing.
15:02So that's the second point. Important is that you need to give eyes to your AI, to your data. So exactly as I said before, and it is a big challenge in, I would say, the data ecosystem compared to software engineers. Software engineers can mock their application based on fake data. If you build a web application, you can mock users and so on.
15:26If you build a data pipeline, you need real production data. You cannot just rely on staging data. It's really hard. I've never seen, throughout my whole career, decent, I would say, staging data. You can mock the data and fake the data for testing, for like various testing we'll get into that. But like real just inspection on how the data look like in production.
15:49You need to have read access and you need to give your AI read access on the data. So how does that work? Really simple. You use an MCP. I'm curious who is been using MCP in your workflow. And which MCP are you been using to connect your data?
16:11Or if you are not using an MCP, but just the CLI and give your agent basically access to your data, to your CLI. I'm also just curious which CLI you're using. Let me know in the comments.
16:27But basically, the thing is today, any database providers, would it be monadoc or others, provide an MCP. So provide a way for your AI to connect basically to your data. OK, so you just need to go through your step of configuration, depending on if you use, I would say, Claude or OpenAI.
16:51You have a very small gut shot, but it's a bit of standards. I see people commenting. I see Ryan Dolly is in the chat. Hey Ryan, how's it going? I use MCP every day, but mostly to do stuff in monadoc. I'm pulling data source over API. Monadoc MCP. Monadoc MCP through Devin or Kimi CLI.
17:11Ah, that's interesting. I've never used a Kimi CLI yet, too. And I've been using Py personally and githubcopilot. org. So, yes, that's the models. But I'm curious which kind of MCP, which kind of database. Basically, if you have a database, you mostly use the online MCP that is available for you.
17:35But, yes, so you can connect an MCP for monadoc. We are officially part of, I would say, the integration. So if using Cloud or OpenAI, that's the same thing.
17:52I mean, more or less, you go to settings and then you go to connector and you can search to various connectors here.
18:08And basically you can type monadoc and you get the official monadoc list for connecting the monadoc MCP to Claude. That's the same process for OpenAI. If you're using any other CLI, we have documentation on our docs.
18:26If you go to monadoc. docs and you go to query VVI, you have various instructions directly in the dive bar over here to see how you need to set up for your coding agent.
18:46So there was also a question in the audience. Yes, there will be artifact shared. So slides and blogs for this presentation right after the session, if you need to jump out. So here, basically, we give our data coming back to our use case of climatology data.
19:08We give eyes on data for my coding agent here, which is Claude. And it sees that, oh, value are 10 of degrees. So it's going to see in the logic that it's going to see that the partition is layout because it's going to basically try multiple queries against three and realize that there is two partition actually, not just by year and by element.
19:31And it found also the Q flag because typically it's going to do a describe on the parquet file to get the schema and look at the data. So when it's done, basically, it knows that we can partition by we can put years as a parameters.
19:49We have to do the data value divided by 10 to get to get the correct units of temperature and we can drop where the QA flag is set. that when source told that the data is already bad. And of course we didn't pick the gates on station plus state. So we delete the year of those row
20:13and then insert to have the pipeline dependence. So it's basically the AI with some just intent of like, hey, pick the right parameter for the pipeline, check their business logic over there and make the pipeline independent and with incremental loads.
20:35You're already telling a lot to the AI agents that it will not do by default as we saw with the pipeline, if you remember here, that's the basic pipeline that it gave me, a one-shot SQL query, Python flights. All right, next thing is testing.
20:57So there is multiple kind of tests. If you, again, foundation matters, there is unit test and that's where you shouldn't rely on production data. You can mock your data. And then there is end-to-end test. We're going to talk about end-to-end test later, but local tests are really important and DougDB works really well for that
21:21because it works locally. So it works in process in my Python, basically process. So here I have a test pipeline and as you see is basically create some mock data. So that data looks really look like the production data because he's been, again, highs on the data.
21:43So he can mock the data pretty easily. So I have some rows. I create a table to have the data locally. And then basically I do some assertion. So we need to drop basically some data that's contained, not containing the QA flag.
22:06We need to be able to have unique, not duplicate rows. So there is various tests. This one, for example, shows that if we forget to divide it by 10, the audit fails loudly. So to basically do those tests, which are really simple.
22:29So I mock the data and I have various tests and then I do an assertion each time. You use the best ways to use basically a local DougDB. And so if I do, let me go in the repo. So if I do uvrun test pipeline,
22:51it's gonna just run the test using DougDB. And you see that was really, really super fast, less than a second because everything is in process. It's within my Python process. I'm not connecting to any remote databases and I'm just doing three tests. So those are useful.
23:14They're not purely safeguards, but at least if you change some business logic in the pipeline, typically unit tests will help you raise that if you run on every step on the CI. So now we are arriving on the third part, which is the contract. And that's the most difficult part.
23:38People also talk today is about creating the loop. Typically, let's say we are not giving instruction, we are giving loops to agents so that it basically iterate by itself until the results. I don't like really creating loop because like a loop, when does a loop end? That's why I like more contracts or a goal.
24:01I mean, I said a goal because this is building, for example, if you are using Claude Code and you have the goal, basically shortcuts that said the gold Claude checks before stopping and iterating. And so for our data pipelines and coming back to the climatology use case
24:24that we are covering, it means for example, that you need to judge the output against a goal you set up in front. Not the code. And I think that's the biggest, I would say a point when building data pipeline is that there is a business logic to it, right? And that should be your goal.
24:48Your goal at the end of the day, you can review and you should review the codes, but it's mostly at the architectural level, how you want to organize the codes. But the code itself, I feel it's less and less important as the model gets better. But it's more like, what is the goal, the business goal that you want to reach?
25:10And so here, for example, we say that we have some KPI that we want to measure trends and we want to keep the same station. And I want to explain to you why. So there is multiple station that gets added over time to take new temperature measure. And if you want, basically my pipeline
25:35is that I wanted to see how the temperature is rising over the time. Is climate change happening? And the problem is that if you take all the new station, there is a lot of new station in cold area, then basically your trends get biased because those area were always cold, right?
25:57And they basically shift the total average of the temperature. So the point here is that just take the same station that were originally there for the data and ignore all the new station. So that's one of the contracts and rules that we'll pass. But the way to do that, basically,
26:22is again, to use the AI and iterate. And how do you do that? Simple. I'm gonna show you the contracts here I wrote for the raw business data pipeline. So basically here, I mentioned already the value that is divided by 10, but actually I just have,
26:47I would say, a first discovery discussion with the AI tool. Say, hey, here's the data set. I want to build the pipeline. Let me write a contract so that you write a robust data pipeline that makes sense for my intent here that is having a view of the temperature grow over time. Okay, so that was roughly the prompt.
27:11And so you see I put, there is the Q flag. I want exactly one row per station independent per year. And then KPI system station mean T max per year and other things, business rules. So that's how I put the contracts.
27:33But now, how do I let the agent iterate? Because he's gonna make a mistake. Maybe he's gonna find things that I'm missing here to respect this contract. Maybe there is, I don't know, other kind of temperature like Fahrenheit and Celsius. So for that, it's not only giving my AI eyes to the data
27:57because it can read, I need to do the response formation. So I need him to be able to write the pipeline and iterate on this pipeline and this contract. So the really important thing here, K takeaway, is that if you built, you need AI to get access to the data, but then you need AI to be able to deploy
28:19the end result pipeline. Here, it's a Python pipeline. And see the logs of this pipeline deployed and iterate multiple times. So Motorlack has flights for that. It's basically a Python runtime and that you can use a serverless Python runtime.
28:41So towards our MCP, you can use, basically ask the agents to deploy Python runtime. And then basically is gonna do the pipeline. And if there is an error, it's gonna fail loudly. You're gonna get the logs from, I would say the flights
29:02and basically is gonna iterate on the final transformation. To give you an idea, so this is the Motorlack UI and on the flights of the Python runtime, you see, I have various pipeline here. And if I click on any of those here, of course I need to go really, because this one is running daily, but you're gonna see, you could see some failure states
29:27and I have the logs over here. And so that's what the AI needs to see, right? I don't want to copy paste those logs when the pipeline fail and it can iterate over the pipeline. All right. So the contracts is important because as I said,
29:49like the biggest trap here in my use case is that if you take all the station, including the new station, then it looks like the climate is getting cooler, which is obviously not true based on multiple trusted research paper. There is an increased global temperature
30:10and that's why you should take the same station over there. And so what I also ask people often is in the exploration phase is that look at the data. And so I think I have the dive here. So in the MCP of Motorlack,
30:30you can also create an easy visualization as you want. So the AI can create it for you. So I haven't coded anything that you see here and it's used the data set, the same data set we show. And we see the temperature increase over time, right? I had another graph, but basically that's the graph.
30:55Sorry. This is the graph that I wanted to show you is that you can also ask the AI to draft at exploration phase. Hey, show me a line on average temperature if you take all the station and if you take a fixed set of station
31:17that is available from the start to now. And you see that by looking at this kind of graph, I already know that something is wrong with my assumption because there is new station and they might be in the colder area. So the key takeaway here is that, yes, you can build a data visualization at the end results, but here we were talking about building robust data pipeline,
31:39not about building data visualization. But I want to tell you that actually building data visualization when you're trying to build the best data pipeline is useful to understand the shape of your data, the traps to avoid and how to put your logic back in your pipeline. All right, we are arriving on the final phase, which is package it.
32:05So, by the way, if you have question because I'm going through, and I realized I haven't shared my screens for the rest. It is happening, it is live. Don't worry, we'll share all the slides.
32:30So, yes, and I saw the comments. I'm really unfortunate. So let me quickly recap. Actually, I have disabled, I had a shortcut, which was wrong. It happens, guys, it happens. I'm the alone running there. So let me quickly recap on the people that has in TeamDigs. So,
33:00the most important thing just to recap, getting back eyes on your data, having the contract set up. And I was showing the contract here. This is a contract I've been writing down by the use of the AI. And so now let's look at basically
33:23how we package this basically to not do it again. And so we use skills. So the reason for that is that skills is a pretty much a standards nowadays to have a specific pipeline that your AI can redo.
33:45So we're gonna package everything that we just learned to build a robust data pipeline in a skills. So the first one is get eyes on the data before writing any transform, but parameters, independency, and incremental loads. I would say that's the three key thing for building robust data pipeline. Had unit tests here on realistic fixtures and write the contracts first and update before publish.
34:09So that's really at the high level of the skills. I think I have. . . You can extend basically all this section as you want for the skills, meaning that if you need to give your eyes on data, here I'm using the MotorDAC MCP,
34:32but whatever MCP that you are using, you can basically specify this into the skills. And speaking of skills, we have a agency that we use to build the skills. So we have agent skills here from MotorDAC that are open-ended agent skills for building application with MotorDAC,
34:57but it's also some of them are really good inspiration for just generate data work. So I would really advise you to get a look at it. If you need to build a pipeline or whatsoever, we put like things like it up and then and other stuff that I just said into those skills. So even if you're not using MotorDAC, they are worth to look at and get inspiration.
35:20Skills are just marked down if you don't know. So it's really easy to just inspect them. So you see, for example, building data pipelines. And if I look at them, you see it says specifying, use the remote MCP or local MotorDAC, explore the live data. And then after it's like batch over streaming,
35:43role ending, explicitly role staging analytics boundary in the pipeline. So everything we just like, mostly we talked at the high level, but more explicitly into a skill file. So this is also something I would recommend you to see.
36:06So I just put the links over there. And as we are closing in, I want also to share you a blog that basically recap what we just covered today. So this is basically all the information recap
36:30on just one blog. So wrapping up, we talked about packaging in a skill that so that you can invoke it. And basically when you build the pipeline, you just do slash Rob is basically pipeline or Rob is flight, because here I'm using a MotorDAC flight
36:54and it's happening every time. So the prompts that we, I would say a Rob is prompt look like this, sorry, which is basically build a MotorDAC flight that curates the daily max temperature, put it into a specific database before writing and transform,
37:16use MotorDAC MCP to inspect the source units, null rate, duplicate key, judge the output against the contract, not the code, yada, yada. And then we'd say a parameter, take the year as a parameter. And then finally a test factor, the transform into pure SQL function and write golden tests that run on the local DB and others.
37:40So yeah, one single prompt, one paragraph that may sound like heavy and scary, but basically, honestly, it's just a matter of those four disciplines that I often applied on those things. So we have a couple of time for question against my apology for not showing my screen for a while. This is a wrong shortcut that I will disable.
38:04First time it happened to me, but yeah, I'm going to the various thing. It's on Spotify. Yeah, that's true. Yeah, so them being criminal, you imagine is reality. So really fun comments. All right, so you're on the first victim on corpus of your shortcuts.
38:30Database skills. Yeah, so we have some skills around data visualization for the side I've been doing. It's often reusing the same thing, but we have also the dive gallery I can mention. So which is, let me show you.
38:53Here, and let me share it quickly. So the dive gallery is an inspiration, a good inspiration if you want to build a fun data visualization. This is all set on MotorDyke dives, but to be honest, they're a good inspiration no matter where you're putting your data visualization.
39:15We had also some hackathon and with two winners that I really recommend to look, but yeah, a lot of them, and you can filter by kind of like what you're into. If it's map, just the slides deck. So I have put some slide decks over there also that you can look.
39:45Doug Doom was awesome. Yes, indeed. Doug Doom was really someone who created a dive with Doom. For prompts, it's generally more specific and long the better. So I would say prompt shouldn't be too long, because for me, prompt is a way for humans to inspect and see what's going on.
40:09And I would package things into skills if it's really long. I don't think it's it makes really sense. You have long prompt. What I do use is a plan mode. So in every, I believe, codex and code, you have the plan mode, which enable you for like complex tasks to see, OK, let's let's look at a plan and the AI will write a
40:33long plan, which is kind of a prompt and then execute that plan. But that's more like a one shot thing. Right. So I would say the shorter is easier to kind of like as long as you have skills to inspect and debug. We have a bit of love from Dave. Thank you. Thank you for that.
40:57I'll share with the team. All right. So this is it for today. All the assets will be available and sent to you blog, the slide deck that at least you see everything and the blog if you want to review it. Hope you had a great time. Sorry again for these things. It happens to everybody.
41:19I've done, you know, more than that I can count live stream and it's still, you know, it's still happening. You still have the things around the corner to mess with you. But hopefully you had some good insights Intel to take away. Have a great time, everybody, and see you next.
