book

Data Engineering with dbt

by Roberto Zagni

June 2023

Intermediate to advanced

578 pages

15h 37m

English

Packt Publishing

Read now

Unlock full access

ContributorsAbout the authorAbout the reviewers
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
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
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
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
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
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
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

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
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
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
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
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
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
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
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
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
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 ...