Unlocking dbt : Design and Deploy Transformations in Your Cloud Data Warehouse

This book shows how dbt is used to build data transformation pipelines that enable dependency management and allow for version control and automated testing. It explains how dbt is revolutionizing data transformation and the advantages that a command-line tool like dbt provides over and above the use of database stored procedures and other ETL and ELT tools that handle data transformations. You’ll see how to create custom-written transformations through simple SQL SELECT statements, eliminating the need for boilerplate code and making it easy to incorporate dbt as the transformation layer in your data warehouse pipelines. Additionally, you will learn how dbt enables data teams to incorporate software engineering best practices such as code reusability, version control, and automated testing into the data transformation process. At its simplest definition, dbt (or data build tool) is an open source data transformation tool commonly used for modeling data in data warehouses. It enables data professionals comfortable writing SQL to transform data in their warehouses more effectively. At a more granular level, it is a tool that has a development framework that combines modular SQL with software engineering best practices to create a better, faster, and more reliable data transformation experience. Modular SQL refers to the practice of breaking down SQL code into smaller, reusable chunks so that it can be more easily maintained, tested, and reused. Other best practices that dbt lends itself well to include version control, documentation, DevOps, and so on. It is very important to understand though that dbt is just a transformation tool and does not handle data movement outside the data store it connects to. So when you consider extract-transform-load (ETL) and extract-load-transform (ELT) designs, this tool is only the transform component of these patterns. You will need to combine dbt with other tools to create your full ETL/ELT pipelines. Several market tools such as Fivetran, Airflow, and Meltano allow you to run dbt as part of their framework, or you could utilize completely separate tools entirely. How you utilize it is up to you and what works best for your use case. Unlocking dbt walks you through using dbt to establish a project, build and modularize SQL models, and execute jobs in a way that is easy to maintain and scale as your data ecosystem matures. You’ll begin by establishing and configuring a project, a process covered using both dbt Cloud and dbt Core, so that you can confidently stand up a project using either platform. From there, you’ll move into building transformations with peace of mind that your project will scale appropriately as you continue to develop it. After learning the basics needed to get started, you’ll continue to build on that foundation by looking at the unique ways in which dbt combines SQL with Jinja to take your code beyond what is capable in normal SQL. You will learn about advanced materializations, building lineage in your data flows, the unlimited potential of macros, and so much more. This book also explores supported file types and the building of Python models. Rounding things out, you will learn features of dbt that will assist you in making your transformation layer production ready. These include how to implement automated testing, using dbt to generate documentation, and running CI/CD pipelines. What You Will Learn: Understand what dbt is and how it is used in the modern data stack Set up a project using both dbt Cloud and dbt Core Connect a dbt project to a cloud data warehouse Build SQL and Python models that are scalable and maintainable Configure development, testing, and production environments Capture reusable logic in the form of Jinja macros Incorporate version control with your data transformation code Who This Book Is For: Current and aspiring data professionals, including architects, developers, analysts, engineers, data scientists, and consultants who are beginning the journey of using dbt as part of their data pipeline’s transformation layer. Readers should have a foundational knowledge of writing basic SQL statements, development best practices, and working with data in an analytical context such as a data warehouse.