Jinja in dbt (Data Build Tool) | Using Jinja functions | Templating | Generating Reusable Code

foreign hello data Pros welcome back to another exciting video in our DDT series in our previous video we learned about DBT Dock and Dot block features today we'll shift our Focus to Jinja a highly valuable feature within DBT in fact we've already started using Jinja in our code the popular ref function that we've been using is one of the Jinja constructs DBT developers mainly use three languages SQL for creating models and tests yaml for configurations and Jinja for making the SQL and yaml code dynamic and reusable Jinja is a powerful templating language while in the DBT it's predominantly used to generate Dynamic SQL code here is an example that showcases how Jinja can be used to generate a dynamic HTML code before I discuss a DDT Ginger real world project use case let me familiarize you with the building blocks of Jinja with a simple example anything that starts and ends with curly braces and percentage is called a control statement examples of control statements include variable declarations if conditions and for loops it's important to note that the contents of control statements are not printed in the final generated code anything that starts and ends with double curly braces is called an expression the contents of expressions are evaluated and the resulting values are printed in the final generated code anything you write without any curly braces is called text s are not evaluated they are simply printed as is in the final generated code lastly anything that starts and ends with curly braces and hash are comments comments are neither printed nor processed they are just for our understanding here is a simple Ginger code that uses all the ginger building blocks that we have just discussed the first line is a comment all these are control statements that basically control the program flow but are not rendered in the generated code all these are texts at runtime they will be rendered as is in the generated code lastly this is an expression at runtime this will be evaluated and the resulting value is rendered in the generated code let's try to compile the code and see what the output is this is awesome isn't it having gained familiarity with Jinja and its ability to generate Dynamic code let's now delve into real world project use cases in one of our projects we successfully passed all our DBT tests in lower environments however when we move to production we encountered several issues upon investigation we discovered that the tests in lower environments were run with insufficient test data consequently our business expressed the need to incorporate this check as the initial test in our future testing Cycles to address this requirement we Define the bare minimum number of rows for each table that would be considered adequate for thorough testing we then developed This Ginger test SQL that can be used to generate Dynamic test code for multiple tables let's put this code in a DOT SQL file in the test folder at the beginning the code defines a dictionary called expected counts this dictionary contains the expected number of records for each table it's worth mentioning that the Jinja allows us to leverage various python data types for this case we've used a dictionary data type next we have a for Loop that iterates through each item in the expected counts dictionary let's assume the first Loop starts with the customers table inside the loop we extract the table name and expected count from the current iteration we also get the actual record count from the data warehouse if the actual record count is less than the expected count the code generates a record as learned in our DDT testing video it's crucial to note that if the test SQL generates a record it will result in a test failure if you need further clarification please feel free to refer to the video link in description the loop continues for the next table in the dictionary until all tables have been processed without Ginger we might need to create separate test files for each table which would result in more than 40 lines of code however by employing Jinja we can consolidate the tests into a single code block spanning just 20 lines we shall now proceed with executing this test upon completion we find that the test has passed confirming that we have sufficient test data in each individual table to further evaluate the effectiveness of the test let's attempt to delete some rows from our customer table returning to DBT we shall rerun the same test this time the test has failed this outcome confirms that one or more tables do not possess enough test data we could now execute this test as the first step in our testing cycle so that future test Cycles will pass only if each table has enough test data Jinja has versatile uses Beyond testing for instance here's an example that demonstrates how Jinja can generate a dynamic SQL for a DDT model additionally here's another example illustrating how Ginger can generate Dynamic yaml configurations overall Jinja offers numerous advantages that enhance code reusability reduce the overall lines of code needed and significantly improve readability and maintainability of your code that's all out for today please stay tuned for our next video where we'll deep dive into DBT macros and packages please do like the video and subscribe to our Channel if you have any questions or thoughts please feel free to leave them in the comments section below thanks for watching

Channel: Sleek Data

Views: 11,327

Keywords: dbt macro, jinja sql, jinja if, jinja for loop, dbt variables, jinja template, dbt macros, dbt data, jinja else if, dbt log, dbt docs, jinja comment, dbt utils, what is jinja, dbt post hook, dbt core, Jinja and macros, Using Jinja

Id: 85El5efB6uw

Length: 6min 42sec (402 seconds)

Published: Tue Jul 18 2023