DBT (Data Build Tool) from a Beginner to Expert

Explore dbt, a framework for transforming data within a single engine, focusing on transformations and excluding extraction or loading, with dbt cloud and the open-source dbt core option.

Compare ETL and ELT architectures, showing how ETL uses tools like SSIS or Informatica to transform data before loading to data warehouses, while DBT supports ELT workflows.

Explore the difference between declarative and imperative programming and see how DBT turns imperative workflows into declarative pipelines, empowering analysts and accelerating data development.

Explore the five SQL statement categories—DDL, DCL, DML, TCL, and DQL—and see how dbt enables DQL querying for data engineering.

Explore the evolution of database engines from sql-based and non-sql roots to distributed data warehouses and data lakes, highlighting column-store analytics, cloud warehouses, and scalable architectures.

DBT is a transformation tool in the data stack that requires a data warehouse and an ETL process to load data; it supports various adapters and focuses on post-load transformations.

Explain how dbt prevents data swamps by establishing a data catalogue, metadata descriptions, and lineage, while enabling analysts to scale with sql, collaborate, and leverage cloud warehouses for fast analytics.

Explore the six technologies essential for dbt development: sql for modeling, yaml for configuration, python, jinja templating, markdown documentation, and git for collaboration.

Install git and set up an Anaconda Python environment with Anaconda Navigator, then install dbt-sqlserver via pip inside a conda environment, and verify with dbt --version.

Kick off a dbt project by configuring profiles.yml for sql server connectivity, installing unixodbc drivers, and validating with dbt parse and dbt debug to ensure secure, ready-to-run development.

Explore the dbt project skeleton, including dbt_project.yml, model paths and folders such as analysis, models, seeds, and tests, and the target directory. Learn how profiles.yml secures credentials for multiple projects.

Explore dbt models—sql files in the models directory. See how dbt run compiles to t-sql, materializes as tables or views, and uses ref to link models.

Explore dbt main configurations, including table, view, ephemeral, and incremental models, and see how dbt run handles them with general and adapter specific options like alias, tag, docs, and schema.

Define sources in dbt with a sources.yml file by configuring the database, schema, and tables, then connect source tables to models and generate docs to validate the setup.

Learn the typical dbt development workflow, creating base views from sources with sources reference and ref to build dim_customers, dim_packs, and the fact_transactions, then run dbt to materialize star schema.

Configure models_in_target.yml in the targets folder to document and catalogue models like dim customers and fact transactions, using descriptions to populate the catalogue and support lineage.

learn how dbt analysis lets you test models without materializing to the warehouse by running dbt compile in the analysis folder and reviewing target/analysis results before submission.

DBT seeds create a dimension from data and load it into the warehouse, showing how to create dim_files.csv with id and file name, run DBT seed, and join to transactions.

dbt uses seeds, sources, and models linked by a lineage diagram; you define tests in models.yml and run dbt test to validate unique, not-null, and accepted values, refining transactions data.

Explore singular tests in dbt with case-specific sql files that fail when they return results, referencing fact_transactions and values like 3, and note that dbt test scans the tests directory.

dbt executes all tests by scanning the project, generating sql for tests, and running them against the database, while extra test configurations control where, limit, severity, and store failures.

Explore how dbt snapshots implement slowly-changing dimensions by tracking historical employee data through valid_from, valid_to, and updated_at, using the dbt seed command.

Explore configuring dbt snapshots to track slowly changing dimensions, select between timestamp and check strategies, set the target database and schema, and specify the unique id and check columns.

Explore the dbt folder structure with descriptive sources, staging, targets, and data marts. Learn to organize base views, use the catalogue, and consolidate yaml files with naming conventions.

Explore how the data catalogue in dbt exposes sources, models, and databases via dbt docs generate and serve, and learn naming conventions and multi-environment deployment (development, UAT, production).

Customize dbt lineage visuals by applying tags and colors to distinguish sources, staging, and target models, and organize the project with consistent folder structures and configurations.

Learn how dbt macros use jinja templating to create reusable user-defined and system macros, like extract_year_month for date formatting and generate_schema_name to customize schema names.

Learn how dbt incremental models load data in batches, performing an initial full load, then inserts and updates using a delete+insert strategy guided by is_incremental logic.

Learn to document data elements with docs blocks, using markdown in models.yml to describe tables, views, and columns. Use jinja tags to call reusable descriptions across the data catalogue.

Explore the dbt cli through diagnostic commands like dbt parse and dbt debug, then manage the target folder and dbt_packages with dbt clean and dbt deps before running models.

Explore how dbt docs generates the manifest and catalog metadata from your project, serves a local web server, and lets you view the catalogue and lineage.

Explore the dbt development commands, including dbt run, dbt seed, dbt test, dbt compile, dbt snapshot, and dbt build, and learn when and how to apply them in production.

Master the dbt selection criterion using select and exclude to run only needed models like dim_packs; explore graph and set operators, unions, intersections, and the @ operator for dbt runs.

Learn to manage multiple dbt environments by replicating dev to prod, configuring profiles, and running dbt build with full refresh to deploy to production.

Explore dbt's other commands, such as retry, list, and show, and learn how to manage core versus plugin versions, inspect project resources, and troubleshoot without materializing.

Discover open-source dbt packages, install with dbt deps from the dbt hub, and use the codegen macro to generate sources.yml by scanning your database or via dbt run with args.

Explore dbt plugins, focusing on dbt-sugar, a Python package that auto-generates descriptions, column details, and tests for models. Learn its setup and compatibility with Python 3.6+ and PostgreSQL, Snowflake, Redshift.

Explore external dbt tools like piperider and data-diff by Datafold, and learn how they extend dbt workflows via CLI, support multiple cloud warehouses, and use profiles.yml credentials.