dbt Core Tutorial 2 - Adding Models

Hello. It's Jason from Can here. And in this video, I'd like to discuss how to add models from DBT into the canvas. So in this canvas, we've already got DBT connected to our Postgres database, and we've got some models down here. Now even without DBT connected, you can always just drag a table from the sidebar into the canvas and will execute a query which looks at the results of that table. With dbt, you can do a lot better. So if we just remove this, and instead of dragging, we can click the dbt icon here, which gives us information about the dbt model. Now, if we click the add to canvas button, there are various options here. But if you just choose the default, what we'll do is we'll take the raw code for that model, the raw ginger code, from dbt and add it to a cell and execute it and give it a default title and name. Now, as the raw Jinja code remains intact, we have to compile it before executing it. So if you hover over any block of Jinja, you'll see how counters compile it. So in this case, this ref invocation becomes a database reference. If you want to see everything, we just pull this out. You can always click show compiled SQL down in the sidebar, and we'll show you the full SQL query that is sent to Prosperous or your database. And we compile everything here, so not just refs. And there's also, for example, a macro invocation down here that's been compiled to a string literal over here. Now in the case we need a bit more context, you wanna look at these upstream models, there's actually a built in way of doing that in one click. So if we remove that, go back to add to the canvas. We have these options to include additional upstream and downstream levels. And by levels here we mean, say, one step away in the dbt DAG. Now because this model has three upstream dependencies, then by including one additional level, we're including three additional cells. So in this case, I'll be adding four cells to the canvas stack. So we'll have these. These are all raw ginger code again executed independently. But the really neat thing is, that I think is worth pointing out, is how these are compiled. So if we look again at how this staging customers ref is compiled and hover over it, you'll notice we don't see a database reference. In fact, if I pull this out again, show the compiled SQL, we're actually referring to a cell here, staging customers. And this is because when there's a cell in the canvas with the model name and it's been marked as a model like so, using this option over here, then COUNT will compile all references to that model as if it's a cell. So actually, if I delete this, you'll notice that we've recompiled this reference and re executed this customer cell because there's now no longer a cell with that name. If I undo that deletion, we recompile it again, and we're back referring to this cell. This is a really handy way, of deferring and undefering models. So if I wanted to, say, tweak this model in situ without having to push it back to my database, I can edit it here and know that everything downstream will update reactively. Or if I'm kind of happy with my changes, want to go back to looking how things will be in real life in on the production database, I can delete the cell, and everything goes back to referring to the database table again. Now this is a relatively complex model. You can see by the length of it. And so another way of interrogating models, which you might find helpful, is to explode them. So we've had exploding cells in count for a while now. You can also now explode models. So in this case, what we do is we take every CTE that we've detected in that model, and we explode it out into its own cell. So if I were to just explode this customer's model, it would end up as seven cells because there were seven CTs in there. But we can also go further. Let's explode the customer's model and all subtree models. So this is kind of maximum clarity if you like. This is all the model information, all the CT information in each model split out into their own independent queries. Now when count explodes a model, it will put all those model cells into a frame. So in this case, you can see the frame is called customers with the unique ID. And all of these individual cells inside that frame are CTEs from that customer's model. And the same has happened with a lot of other models here. So our staging customers table has been exploded into three CTEs and so on. If you ever need to see the dependencies between these cells, then you can just click on them, and you'll see where they come from. And so if, yeah, if I want to make a change to this model, I can do that up here, and I know that everything downstream will update. And so there this is a really flexible way we think to layout, to explore, to annotate, and to debug your models. I hope you find it helpful. And if you need any more information, you can read the documentation, or you can contact us using the in app chat.