book

Data Engineering with dbt

Name: Data Engineering with dbt
Author: Roberto Zagni
ISBN: 9781803246284

by Roberto Zagni

June 2023

Intermediate to advanced

578 pages

15h 37m

English

Packt Publishing

Read now

Unlock full access

Data Engineering with dbt
ContributorsAbout the authorAbout the reviewers
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchShare Your ThoughtsDownload a free PDF copy of this book
Part 1: The Foundations of Data Engineering
Chapter 1: The Basics of SQL to Transform Data
Technical requirementsIntroducing SQLSQL basics – core concepts and commandsSQL core conceptsUnderstanding the categories of SQL commandsSetting up a Snowflake database with users and rolesCreating your Snowflake accountSetting up initial users, roles, and a database in SnowflakeCreating and granting your first roleQuerying data in SQL – syntax and operatorsSnowflake query syntaxSQL operatorsCombining data in SQL – the JOIN clauseCombining orders and customersJOIN typesVisual representation of join typesAdvanced – introducing window functionsWindow definitionWindow frame definitionSummaryFurther reading
Chapter 2: Setting Up Your dbt Cloud Development Environment
Technical requirementsSetting up your GitHub accountIntroducing Version ControlCreating your GitHub accountSetting up your first repository for dbtSetting up your dbt Cloud accountSigning up for a dbt Cloud accountSetting up your first dbt Cloud projectAdding the default project to an empty repositoryComparing dbt Core and dbt Cloud workflowsdbt Core workflowsdbt Cloud workflowsExperimenting with SQL in dbt CloudExploring the dbt Cloud IDEExecuting SQL from the dbt IDEIntroducing the source and ref dbt functionsExploring the dbt default modelUsing ref and source to connect modelsRunning your first modelsTesting your first modelsEditing your first modelSummaryFurther reading
Chapter 3: Data Modeling for Data Engineering
Technical requirementsWhat is and why do we need data modeling?Understanding dataWhat is data modeling?Why we need data modelingComplementing a visual data modelConceptual, logical, and physical data modelsConceptual data modelLogical data modelPhysical data modelTools to draw data modelsEntity-Relationship modelingMain notationCardinalityTime perspectiveAn example of an E-R model at different levels of detailGeneralization and specializationModeling use cases and patternsHeader-detail use caseHierarchical relationshipsForecasts and actualsLibraries of standard data modelsCommon problems in data modelsFan trapChasm trapModeling styles and architecturesKimball method or dimensional modeling or star schemaUnified Star SchemaInmon design styleData VaultData meshOur approach, the Pragmatic Data Platform - PDPSummaryFurther reading
Chapter 4: Analytics Engineering as the New Core of Data Engineering
Technical requirementsThe data life cycle and its evolutionUnderstanding the data flowData creationData movement and storageData transformationBusiness reportingFeeding back to the source systemsUnderstanding the modern data stackThe traditional data stackThe modern data stackDefining analytics engineeringThe roles in the modern data stackThe analytics engineerDataOps – software engineering best practices for dataVersion controlQuality assuranceThe modularity of the code baseDevelopment environmentsDesigning for maintainabilitySummaryFurther reading
Chapter 5: Transforming Data with dbt
Technical requirementsThe dbt Core workflow for ingesting and transforming dataIntroducing our stock tracking projectThe initial data model and glossarySetting up the project in dbt, Snowflake, and GitHubDefining data sources and providing reference dataDefining data sources in dbtLoading the first data for the portfolio projectHow to write and test transformationsWriting the first dbt modelReal-time lineage and project navigationDeploying the first dbt modelCommitting the first dbt modelConfiguring our project and where we store dataRe-deploying our environment to the desired schemaConfiguring the layers for our architectureEnsuring data quality with testsGenerating the documentationSummary
Part 2: Agile Data Engineering with dbt
Chapter 6: Writing Maintainable Code
Technical requirementsWriting code for humansRefactoring our initial model to be human-readableCreating the architectural layersCreating the Staging layerGoals and contents of the staging modelsConnecting the REF model to the STGGoals and contents of the refined layerCreating the first data martSaving history is crucialSaving history with dbtSaving history using snapshotsConnecting the REF layer with the snapshotSummary

Chapter 7: Working with Dimensional Data
Adding dimensional dataCreating clear data models for the refined and data mart layersLoading the data of the first dimensionCreating and loading a CSV as a seedConfiguring the seeds and loading themAdding data types and a load timestamp to your seedBuilding the STG model for the first dimensionDefining the external data source for seedsCreating an STG model for the security dimensionAdding the default record to the STGSaving history for the dimensional dataSaving the history with a snapshotBuilding the REF layer with the dimensional dataAdding the dimensional data to the data martExercise – adding a few more hand-maintained dimensionsSummary
Chapter 8: Delivering Consistency in Your Data
Technical requirementsKeeping consistency by reusing code – macrosRepetition is inherent in data projectsWhy copy and paste kills your future selfHow to write a macroRefactoring the “current” CTE into a macroFixing data loaded from our CSV fileThe basics of macro writingBuilding on the shoulders of giants – dbt packagesCreating dbt packagesHow to import a package in dbtBrowsing through noteworthy packages for dbtAdding the dbt-utils package to our projectSummary
Chapter 9: Delivering Reliability in Your Data
Testing to provide reliabilityTypes of testsSingular testsGeneric testsDefining a generic testTesting the right things in the right placesWhat do we test?Where to test what?Testing our models to ensure good qualitySummary
Chapter 10: Agile Development
Technical requirementsAgile development and collaborationDefining agile developmentApplying agile to data engineeringStarting a project in an agile wayOrganizing work the agile wayManaging the backlog in an agile wayBuilding reports in an agile wayS1 – designing a light data model for the data martS2 – designing a light data model for the REF layerS3.x – developing with dbt models the pipeline for the XYZ tableS4 – an acceptance test of the data produced in the data martS5 – development and verification of the report in the BI applicationSummary
Chapter 11: Team Collaboration
Enabling collaborationCore collaboration practicesCollaboration with dbt CloudWorking with branches and PRsWorking with Git in dbt CloudThe dbt Cloud Git processKeeping your development environment healthySuggested Git branch namingAdopting frequent releasesMaking your first PRSummaryFurther reading
Part 3: Hands-On Best Practices for Simple, Future-Proof Data Platforms
Chapter 12: Deployment, Execution, and Documentation Automation
Technical requirementsDesigning your deployment automationWorking with dbt environmentsCreating our QA and PROD environmentsDeciding where to deployCreating jobsDesigning the architecture of your data platformNotificationsAdvanced automation – hooks and run-operationsHooksRun-operationsTable migrationsDocumentationLineage graphdbt-generated documentationSource freshness reportExposuresMarkdown documentationSummary
Chapter 13: Moving Beyond the Basics
Technical requirementsBuilding for modularityModularity in the storage layerModularity in the refined layerModularity in the delivery layerManaging identityIdentity and semantics – defining your conceptsDifferent types of keysMain uses of keysMaster Data managementData for Master Data managementA light MDM approach with DBTSaving history at scaleUnderstanding the save_history macroUnderstanding the current_from_history macroSummary
Chapter 14: Enhancing Software Quality
Technical requirementsRefactoring and evolving modelsDealing with technical debtImplementing real-world code and business rulesReplacing snapshots with HIST tablesRenaming the REF_ABC_BANK_SECURITY_INFO modelHandling orphans in factsCalculating closed positionsCalculating transactionsPublishing dependable datasetsManaging data marts like APIsWhat shape should you use for your data mart?Self-completing dimensionsHistory in reports – that is, slowly changing dimensions type twoSummaryFurther reading
Chapter 15: Patterns for Frequent Use Cases
Technical requirementsIngestion patternsBasic setup for ingestionLoading data from filesExternal tablesLanding tablesHistory patternsStoring history with deletions – full loadStoring history with deletion – deletion listStoring history with multiple versions in the inputStoring history with PII and GDPR complianceHistory and schema evolutionSummaryFurther reading
Index
Why subscribe?
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Content preview from Data Engineering with dbt

15 Patterns for Frequent Use Cases

In this last chapter, we want to present you with a small library of patterns that are frequently used for ingesting data from external files and storing this ingested data in what we call history tables.

We will start this chapter from the key task of the automation of data ingestion. We will discuss alternative approaches and their pros and cons, and provide you with our opinionated insights and code to ingest data in Snowflake.

Next we will extend the basic pattern for storing the history of changes in an entity that we presented in previous chapters. We will provide you with a set of frequent use cases for storing incoming data and the code and conditions to deal with all these different needs.

In this ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781803246284

Cloud Computing