hello everyone welcome back to the channel today we're going to talk about dbd macros in details in this video we'll talk about only dbd macros but if you need more info on dbd please check out my other video I'll provide the link of the video in the description of this video so let's start what is dbd macro dbd macro are reusable piece of SQL code that we can Define and use across our dbd projects and in in simple words we can think like these are similar to the functions uh in traditional program language such as python or Java so basically any uh piece of code which we want to use again and again that we can write it in uh macros it's uh macros allow us to like extract that logic into a reusable component which makes our like models in DBT more maintainable and readable so we don't have to write the same piece of code in every model that's why like it's uh make models more readable and maintainable uh if you talk about the benefits of dbd macros uh so the first benefit is code re reusability the same code can be used again and again to solve similar kind of problems so it's pretty much as uh like any programming language where a function can be used like in multiple places and we don't have to write it again and again uh the second is the centralized logic so common logic is centralized in macros uh that improve the maintainability so for example if different teams are working and there are some logic which is like across the board as same that can be centralized uh the other benefit is cross database compatibilities so macros can be used with across different databases so for example in our Cas we we are connecting with bigquery but it could connect with snowflake um uh red shift uh horse Craigs and those kind of and if we write a macro for one uh that can be used with the different databases so that ensures the consistency of our models they don't break if we move from one database to other um and with this capabilities like we even create like Dynamic uh sqls so that basically like automating our job and which like improve the efficiencies of our work and as we already know like models are defined as uh like dot SQL files within the macro uh macros directory of DVD projects so I'm not we're going to see that in the demo but for now this is the theory of dbd macros now let's look into like what's the role of ginger in BD macros so uh before we uh so if if you like talk about Ginger Ginger is nothing is just a like a template engine or it's like a templating language uh which we can combine with SQL in DBT and if you guys are coming from python background so Ginger is like a web template engine even in the Python programming language the same template engine is used here in dbd we can combine Ginger with the SQL to make our code more Dynamic and U to give it more uh capabilities uh which a SQL can't do so basically using Ginger turns our dbd project into more like a programming environment and that give us abilities to do things that like that are not normally possible in SQL uh like for example like if uh statements for loops um those kind of things which we can't do uh in SQL uh and also like uh we can do Rec we can reference variables which we can do in SQL but like referencing envirment variables and variables outside the projects those kind of things and also like we can change the uh like project Behavior based on the target so basically uh we also like that also help us to incorporate uh uh the software development um cycle uh functions too so for example if uh the target is prod we should do something um as per the prod thing if it is dab this so basically it in simple words we can say like it makes our uh uh like our models or our DBT in general much more stronger it's more stronger Dynamic SQL we can say and uh Ginger is is not something which we only used in macros it can be used in models test uh analysis or other places too uh before we start uh writing our first macro I like to share a ginger template cheat sheet which I have prepared so like who don't know Ginger they don't feel overwhelmed like okay now we have to learn Ginger 2 with SQL to understand DBT so it's pretty like straightforward not too complex so I created a cheat seat which uh which specifically I can say tailored for DBT use cases it's not um limited to this like it can be beyond that but I feel like if somebody starting their journey in DVD like it's this is like good enough so let me go to uh that cheat sheet so this is the cheet shade I was talking about uh let's look into the ginger cheet Shades concept uh the first one if you see is the expression and expressions are um are basically like two curly braces with something in it and they are used when we want to like reference something or even call a macro models or variables kind of things so if you recall like um uh like if we want to refer a model it has to go between these curly braces and then this and the same way we can prefer variables and also and in in in ginger pretty much like everything goes in these uh um like in the Expressions uh the second thing is the statements statements are basically uh used to do something or we can say um like it's a syntax where um you want to have some control flow so for example here we are seeing like this is for a macro but this could be like if you let's say if you want to use for Loop if statements everything goes in this kind of uh like everything goes in these statements where like it started with the curly braces uh percentage and close with that and here because it's a macro so we are ending in the same way you will see like we'll see um in in in few seconds like for four and if Loop that goes in the same way third is the comment like so uh if you want to comment out any anything in Jinger we have to basically uh put it in this format uh the next is the variable assignment so if you look at like a variable assignment is uh it's also straightforward like everything between this syntax and then Define your variable and assign the value to it so for example this is a string the same way we can assign a list dictionary or even uh like the basic uh data types like integers and all those kind of things so defining a variable is also straightforward like it's um the syntax is bit different but it's quite intuitive like it's not something uh we haven't seen so far uh the next thing is the uh White uh White space uh so white space basically is to uh is pretty much like trim or is to trim the uh the Extra Spaces on left or right sides kind of thing so it's basically to maintain the spaces or trim the spaces so uh it's come in like we can do it in a three different ways so if you look at this like in this section it have a hyen it means like it will strips uh on the left side here it have a hyen on the right side so it will strip on the right side and when it has on both sides so it will strip um all the spaces from both sides so it's pretty much like trim uh trim left right it's just a different format uh uh the next thing is the um uh control flow and uh I was talking about this when I was talking uh about the statements so control flow is basically give us like or it guide our code like how to transform it uh what to do with it so for example if you look at this this is a sample if Els F else kind of code so if started it's checking like if Target name is dev then we do something I'll say if if it is proda do something else do something like we can have code but basically the idea here is to show like how we can use FS uh in ginger and the same can be incorporated uh in our macros models uh and uh like a test and other things and in control flow if you talk about like looping is another thing which is not like a basic SQL capability here we can have a for Loop which uh like you can see the highlighted section is the for Loop syntax which is very similar to what we have in Java or other places oh sorry in Python but it's just the ginger things like I think like these things like uh the curly braces and percentage will Define like what to do or not uh these are some like uh loop properties which are pretty intuitive like the with the name like Loop last Loop first which is basically like this Loop will run until uh the loop blast is false something like this will run if Loop blast f is so it's pretty intuitive like nothing complex it's just we need to keep in mind of the syntax I think like that that's the uh important part here to keep in mind uh same thing with the index which basically tell us what's the current uh uh like iteration of the Lo like for and it start with Z one not the uh zero like so in some languages like index start from zero here it start from one and if you look at the operators which uh I think available almost in all the languages and they're pretty straightforward to is just this part which I'm talking about like these curly braces and percentage which we need to keep in mind and rest of them are pretty straightforwards I think uh we don't have to go them in that details uh the last thing is about the filters so that's like one another thing we need to keep in mind like in ginger uh the meths or functions are called the filters so like if you see that word don't get confused because in SQL filters uh mean something else but in ginger I prefer to call them methods or functions but uh in ginger the in places they have been used filters for these things and if you look at look at them they are also pretty like intuitive too like uh but here is the format is bit different so for example if we using like the upper fter or method so that will be the pipe in the name of it and the expression is there but like this is uh is the format like a pipe and this and same thing goes with like Tri replace length uh like different kind of thing like for example print and all those things and same thing for the uh like data types if you want to manipulate something like these are the things so as I mentioned like it's nothing crazy it's just a different format but rest of the things are uh pretty same as we have seen in other programming language now let's jump to our first macro so I'll go to my project on uh dbd cloud and in dbd Cloud I'll start with my first micro definition okay so if you see here uh uh in one so this is under our macro folders like you can see this is a macro and in that we have a macro name last business day of the month. SQL and there are two to three things which you will we need to keep in mind here the first thing is uh the macro name need not to be the same with what macro we have uh I know like the naming convention we have used is not very good but what I'm trying the point here is like for example if we talk about this macro like compare dates uh the macroon name is not compare dat. SQL it it could be anything and the second point is within one macro we can Define multiple macros and if you look into the first macro so the like the one thing which we already know like the Syntax for macro is like basically to start with the curly braces percentage macro macro name and if you want to pass parameters this and it ends with this so this is we already aware but in between if you see uh here we are not using a SQL um like we can use a SQL but like I just want to give you the just like okay without SQL how to write a macro or um basically like how to use ginger and make your Macros more Dynamic so this macro is doing nothing basic it's not doing something crazy it's very straightforward so basically if you look at the line number one it's setting uh a variable named current date to the current date in this format and if you come from python world this will be making more sense to you and after that we basically comparing this date uh like our current current date to the parameter which has which going to be passed to this macro so uh we are not doing any um much uh I'll say we don't have much logic here but the idea here is to show okay uh how to write macro that's one thing and second is like uh what's the role of ginger in that M so if you look at this like it's basically if it checks if date is smaller um print like uh have this value if it is uh equal then we say same date logic else uh after date logic and and if so in this one we are closing the our uh F else statement and in the similar way if depending on like what you trying to achieve we can have a for Loop we can even have a like ginger plus SQL it need not to be Ginger or SQL it could be Ginger plus SQL uh but for this case it's purely Ginger uh there is another thing like within the same macro we have defined another macro and this is uh um basically this macro is doing it's setting up a variable named partition key and which basically um like yesterday's dat so what it do it's basically subtract a day from it and we return it as a partition key and uh here's like another thing so far we haven't seen a macro where we are returning something like in our last video our macro was pretty much like a SQL statement and the output of that SQL statement is considered the output of it but we can EX explicitly return things too so here the idea is to show you the return for this okay so once uh like uh we understand a couple of things here the first is like the macro names need not to be the same as the file name or like file name need not to be same as the macroon names second is uh uh we can have multiple macros within one file and uh a macro could become combination of Ginger and uh SQL okay uh the next thing is if we can go to our model so this is uh uh this is our model and it is also very straightforward so in this model if you look at us uh we are setting our date which is uh like 15 January 2023 uh and we are having a CTE and after that we are selecting from so in this one basically uh we are calling compare dates we passing this date and giving this as an like as the output of that as an LSS date compare fail and same thing like we are calling this macro and whatever it returns is stored in the part key okay so uh pretty straightforward but the idea as I'm mentioning is to show you like how to call a macro how to uh like Define a macro so basically here we are defining the macro using gja and SQL and to call the macro we have to just uh give it the name and if we are passing the parameters so here we are passing a parameter but in this one we are not passing any parameters and we like we it can be either ways okay let me run this model so if we click uh uh like we can do this too DBT run s uh so uh now we're going to run this model and there are like different ways to run a model so I'll run this way running it is run successfully now we go to our big query [Music] and Let me refresh this refresh my if we expand it this is our database where we um we are storing our models and all and U uh if you watch our first videos you will understand like it comes from the Json have to Define OKAY model name was dat macro click on this uh we can see we have two Fields they not there should be only one record so we go to preview yeah so in preview we can see we have one record uh the compare fi have the before date logic and partition key is 1998 which is uh not what we want so that basically means like our macro is not working as we expected uh but we can add it but here the idea is to show you uh more about like uh how to like call macros Define macros kind of thing and we might have to do something but don't worry about we'll we'll see that later now let's jump to the last topic um uh that is like uh third party dbd macros so so far what we have seen is like uh we create our own macros and uh uh we can use it but uh in um in dbd like we can like literally install the third party macros uh and that's basically goes back to like we don't want to reinvent the wheel again and again if somebody have already return some DBT macros we can go to them and uh so basically for the process for that like go to DBT Hub search for the desired package if package name ex like if package exists that's a condition too uh and then we just need to uh we need to include that package to our project and and uh we should be good to go and to like how we include that package we basically create package. yl uh file and add the uh like the third party details there and then we should be good okay let me show you that like uh we can do this so if you go to this is the uh DBT Hub and here we can see we have like multiple uh feature package one is DBT utils us the one of the most used ones and they have audit helper uh external expectations code generation project evaluators and it's beyond that like these are not the only ones uh for example there was one for date which is uh I think outside of these so for example if we go to dils and we want to um like check out like uh what DB DB DBT UTS have uh we can go to their leg GitHub page and if you scroll down like you can see uh DBT utils have so many macros and uh if you want to look into the details of them so for example pivot is something which is very common so in pivot like if we we can pivot the table and we going to uh like U look into this example so basically uh like you can see how we call this like in our case like when we were calling the macros it was uh straightforward the macro name but if we are installing from somewhere it has to be that. pivot so before uh like U we look into the example I just want to give you an idea okay so there are like third party packages are available and we can install them to our projects first look into this like how we can install so if you look like uh for example we talk about DB DB _ utils if you want to include this into our package it is very straightforward like we copy this and add this to our package. yml so in my case I already have package. yml but when you initiate a project this is a yl file which does not exist so we can create a new file and add whatever package we want in I already have DBT uh like for example DBT utils but if for example if you want something else like Cen I can just copy this and paste here so in this way I me fix the indentation so like whatever third party package you want we can keep adding to this package. yaml and in your case like you probably don't have it uh so just create a new file and add it so that's how you add uh the add the third party packages but um once we add it it does not mean like it automatically um come into our project so we need to run uh and it's uh it always says here like we need to run DBT uh dependencies to run to install this package if you go and all this and this and that's successful got failed the event so the error says like something it was successful okay so it ran multiple times I I guess that's why if you look at this like uh both uh versions are up to dat we look at this uh DBT Labs or gen and DBT both are installed and they are up to last one happen basically maybe like it R and this is another thing I found like it create a lockl file um but I'm not sure like what's the functionality of this but if you want to keep it keep it otherwise but my guess is this is more uh like whatever package you have installed you want them out okay uh the next thing I was talk about so for example if you want to use the pivot one it has multiple but uh for in our case we want to use the pivot one so for that what we need to do is basically go here uh the first thing was to add the package to your package. HL after that uh install it using DBT deps and after that we are good to use it so if I go to this one the third party see equal if you see here I'm using two uh macros from third parties like from DBT utils I'm using the pit one and I'm also that column value and if we go to the like GitHub again we can get this information okay um yeah here so get column basically one second what it does not the code let me search for that so this this macro basically Returns the unique value of the column we passing there so if we go here we are passing the column color and it will returns its uh unique value here and if we check the pivot one the pivot one need the column whose value want he yeah column and the list of value so we are getting list of values from this and this is the column and it will pivot so we'll go to our piig query and I'll show you so this is our product if you look at the preview it's basically size and color so it have two Fields size and color in color we have red blue green and all different kind of colors and then size we have different kinds of size but we want to Pivot this like we want to make it like okay blue um just think about this like blue how many different size it have red how many different size it have so to do that like we need to Pivot it and if we look at this code this is basically uh like us in this model what we doing it's basically uh we have First Column as a size and after that we are pivoting based on the uh color and then grouping them by size so if we I already ran this uh and I have a output for it so we can go and check out the output directly and look at the preview so if you see size was the first color and after that red blue green and it give us the count of them that's what our model is doing here uh so hope you get the idea of how uh what is macro what the role of ginger in macro and how to install third party uh macros um that's all for this video and uh stay tuned for upcoming video