Data Engineering with Dbt : A Practical Guide to Building a Cloud-based, Pragmatic, and Dependable Data Platform with SQL

Use easy-to-apply patterns in SQL and Python to adopt modern analytics engineering to build agile platforms with dbt that are well-tested and simple to extend and run Purchase of the print or Kindle book includes a free PDF eBook Key Features Build a solid dbt base and learn data modeling and the modern data stack to become an analytics engineer Build automated and reliable pipelines to deploy, test, run, and monitor ELTs with dbt Cloud Guided dbt + Snowflake project to build a pattern-based architecture that delivers reliable datasets Book Description dbt Cloud helps professional analytics engineers automate the application of powerful and proven patterns to transform data from ingestion to delivery, enabling real DataOps. This book begins by introducing you to dbt and its role in the data stack, along with how it uses simple SQL to build your data platform, helping you and your team work better together. You'll find out how to leverage data modeling, data quality, master data management, and more to build a simple-to-understand and future-proof solution. As you advance, you'll explore the modern data stack, understand how data-related careers are changing, and see how dbt enables this transition into the emerging role of an analytics engineer. The chapters help you build a sample project using the free version of dbt Cloud, Snowflake, and GitHub to create a professional DevOps setup with continuous integration, automated deployment, ELT run, scheduling, and monitoring, solving practical cases you encounter in your daily work. By the end of this dbt book, you'll be able to build an end-to-end pragmatic data platform by ingesting data exported from your source systems, coding the needed transformations, including master data and the desired business rules, and building well-formed dimensional models or wide tables that'll enable you to build reports with the BI tool of your choice. What you will learn Create a dbt Cloud account and understand the ELT workflow Combine Snowflake and dbt for building modern data engineering pipelines Use SQL to transform raw data into usable data, and test its accuracy Write dbt macros and use Jinja to apply software engineering principles Test data and transformations to ensure reliability and data quality Build a lightweight pragmatic data platform using proven patterns Write easy-to-maintain idempotent code using dbt materialization Who this book is for This book is for data engineers, analytics engineers, BI professionals, and data analysts who want to learn how to build simple, futureproof, and maintainable data platforms in an agile way. Project managers, data team managers, and decision makers looking to understand the importance of building a data platform and foster a culture of high-performing data teams will also find this book useful. Basic knowledge of SQL and data modeling will help you get the most out of the many layers of this book. The book also includes primers on many data-related subjects to help juniors get started. Table of Contents Basics of SQL to transform data Setting up your dbt Cloud development environment Data modelling for data engineering Analytics Engineering as the New Core of Data Engineering Transforming data with dbt Writing Maintainable Code Working with Dimensional Data Delivering Consistency In Your Code Delivering Reliability In Your Data Agile development Collaboration Deployment, Execution and Documentation Automation Moving beyond basics Enhancing Software Quality Patterns for frequent use cases Cover Title Page Copyright and credit Contributors Table of Contents Preface Part 1: The Foundations of Data Engineering Chapter 1: The Basics of SQL to Transform Data Technical requirements Introducing SQL SQL basics – core concepts and commands SQL core concepts Understanding the categories of SQL commands Setting up a Snowflake database with users and roles Creating your Snowflake account Setting up initial users, roles, and a database in Snowflake Creating and granting your first role Querying data in SQL – syntax and operators Snowflake query syntax SQL operators Combining data in SQL – the JOIN clause Combining orders and customers JOIN types Visual representation of join types Advanced – introducing window functions Window definition Window frame definition Summary Further reading Chapter 2: Setting Up Your dbt Cloud Development Environment Technical requirements Setting up your GitHub account Introducing Version Control Creating your GitHub account Setting up your first repository for dbt Setting up your dbt Cloud account Signing up for a dbt Cloud account Setting up your first dbt Cloud project Adding the default project to an empty repository Comparing dbt Core and dbt Cloud workflows dbt Core workflows dbt Cloud workflows Experimenting with SQL in dbt Cloud Exploring the dbt Cloud IDE Executing SQL from the dbt IDE Introducing the source and ref dbt functions Exploring the dbt default model Using ref and source to connect models Running your first models Testing your first models Editing your first model Summary Further reading Chapter 3: Data Modeling for Data Engineering Technical requirements What is and why do we need data modeling? Understanding data What is data modeling? Why we need data modeling Complementing a visual data model Conceptual, logical, and physical data models Conceptual data model Logical data model Physical data model Tools to draw data models Entity-Relationship modeling Main notation Cardinality Time perspective An example of an E-R model at different levels of detail Generalization and specialization Modeling use cases and patterns Header-detail use case Hierarchical relationships Forecasts and actuals Libraries of standard data models Common problems in data models Fan trap Chasm trap Modeling styles and architectures Kimball method or dimensional modeling or star schema Unified Star Schema Inmon design style Data Vault Data mesh Our approach, the Pragmatic Data Platform - PDP Summary Further reading Chapter 4: Analytics Engineering as the New Core of Data Engineering Technical requirements The data life cycle and its evolution Understanding the data flow Data creation Data movement and storage Data transformation Business reporting Feeding back to the source systems Understanding the modern data stack The traditional data stack The modern data stack Defining analytics engineering The roles in the modern data stack The analytics engineer DataOps – software engineering best practices for data Version control Quality assurance The modularity of the code base Development environments Designing for maintainability Summary Further reading Chapter 5: Transforming Data with dbt Technical requirements The dbt Core workflow for ingesting and transforming data Introducing our stock tracking project The initial data model and glossary Setting up the project in dbt, Snowflake, and GitHub Defining data sources and providing reference data Defining data sources in dbt Loading the first data for the portfolio project How to write and test transformations Writing the first dbt model Real-time lineage and project navigation Deploying the first dbt model Committing the first dbt model Configuring our project and where we store data Re-deploying our environment to the desired schema Configuring the layers for our architecture Ensuring data quality with tests Generating the documentation Summary Part 2: Agile Data Engineering with dbt Chapter 6: Writing Maintainable Code Technical requirements Writing code for humans Refactoring our initial model to be human-readable Creating the architectural layers Creating the Staging layer Goals and contents of the staging models Connecting the REF model to the STG Goals and contents of the refined layer Creating the first data mart Saving history is crucial Saving history with dbt Saving history using snapshots Connecting the REF layer with the snapshot Summary Chapter 7: Working with Dimensional Data Adding dimensional data Creating clear data models for the refined and data mart layers Loading the data of the first dimension Creating and loading a CSV as a seed Configuring the seeds and loading them Adding data types and a load timestamp to your seed Building the STG model for the first dimension Defining the external data source for seeds Creating an STG model for the security dimension Adding the default record to the STG Saving history for the dimensional data Saving the history with a snapshot Building the REF layer with the dimensional data Adding the dimensional data to the data mart Exercise – adding a few more hand-maintained dimensions Summary Chapter 8: Delivering Consistency in Your Data Technical requirements Keeping consistency by reusing code – macros Repetition is inherent in data projects Why copy and paste kills your future self How to write a macro Refactoring the “current” CTE into a macro Fixing data loaded from our CSV file The basics of macro writing Building on the shoulders of giants – dbt packages Creating dbt packages How to import a package in dbt Browsing through noteworthy packages for dbt Adding the dbt-utils package to our project Summary Chapter 9: Delivering Reliability in Your Data Testing to provide reliability Types of tests Singular tests Generic tests Defining a generic test Testing the right things in the right places What do we test? Where to test what? Testing our models to ensure good quality Summary Chapter 10: Agile Development Technical requirements Agile development and collaboration Defining agile development Applying agile to data engineering Starting a project in an agile way Organizing work the agile way Managing the backlog in an agile way Building reports in an agile way S1 – designing a light data model for the data mart S2 – designing a light data model for the REF layer S3.x – developing with dbt models the pipeline for the XYZ table S4 – an acceptance test of the data produced in the data mart S5 – development and verification of the report in the BI application Summary Chapter 11: Team Collaboration Enabling collaboration Core collaboration practices Collaboration with dbt Cloud Working with branches and PRs Working with Git in dbt Cloud The dbt Cloud Git process Keeping your development environment healthy Suggested Git branch naming Adopting frequent releases Making your first PR Summary Further reading Part 3: Hands-On Best Practices for Simple, Future-Proof Data Platforms Chapter 12: Deployment, Execution, and Documentation Automation Technical requirements Designing your deployment automation Working with dbt environments Creating our QA and PROD environments Deciding where to deploy Creating jobs Designing the architecture of your data platform Notifications Advanced automation – hooks and run-operations Hooks Run-operations Table migrations Documentation Lineage graph dbt-generated documentation Source freshness report Exposures Markdown documentation Summary Chapter 13: Moving Beyond the Basics Technical requirements Building for modularity Modularity in the storage layer Modularity in the refined layer Modularity in the delivery layer Managing identity Identity and semantics – defining your concepts Different types of keys Main uses of keys Master Data management Data for Master Data management A light MDM approach with DBT Saving history at scale Understanding the save_history macro Understanding the current_from_history macro Summary Chapter 14: Enhancing Software Quality Technical requirements Refactoring and evolving models Dealing with technical debt Implementing real-world code and business rules Replacing snapshots with HIST tables Renaming the REF_ABC_BANK_SECURITY_INFO model Handling orphans in facts Calculating closed positions Calculating transactions Publishing dependable datasets Managing data marts like APIs What shape should you use for your data mart? Self-completing dimensions History in reports – that is, slowly changing dimensions type two Summary Further reading Chapter 15: Patterns for Frequent Use Cases Technical requirements Ingestion patterns Basic setup for ingestion Loading data from files External tables Landing tables History patterns Storing history with deletions – full load Storing history with deletion – deletion list Storing history with multiple versions in the input Storing history with PII and GDPR compliance History and schema evolution Summary Further reading Index Other Books You May Enjoy