Creating and Maintaining dbt documentation

Debugging data modelling errors

Developing dbt models

Materialisation

  1. view
  2. incremental
  3. table
  4. ephemeral

If the materialized option is not specified, the default dbt materialisation is view.

Implementing and Maintaining External Dependencies

Implementing dbt tests

Indirect Selection

  1. indirect_selection parameter controls how dbt handles tests on upstream models when a specific model or resource is selected for execution.
  2. the default mode is eager mode.
    1. Tests associated with the selected resource (directly or indirectly upstream) are always executed.
    2. For example, if you run dbt test --select model_a, dbt will run:
      1. All tests associated with model_a directly.
      2. Tests on upstream dependencies of model_a, e.g. model_b if model_a depends on model_b.
  3. buildable mode.
    1. Runs tests only on models that can be materialised, i.e. models that are not ephemeral or seeds.
    2. This mode is slightly more inclusive than cautious by including tests whose references are each within the selected nodes (or their ancestors).
  4. cautious mode.
    1. Ensures that tests are executed and models are built only when all necessary dependencies of selected models are met.
    2. Tests are only executed for the resources that are explicitly selected, not their dependencies.
    3. This is a more conservative mode, ensuring no unexpected tests are run.
  5. empty mode.
    1. Run tests only on explicitly selected models and skips all dependencies.
    2. When you run a test with empty model, dbt will run only the tests associated with the directly selected resource,
    3. and skip all tests for upstream dependencies.

Leveraging the dbt state

Managing data pipelines

Understanding dbt models governance

restrict-access configuration

Restrict-access configuration is used to

  1. restrict access to certain models in a dbt project so they cannot be referenced outside the package in which they are defined.
  2. This is particularly useful for crating private model within a dbt project, ensuring that only models in the same package or group can reference them.

Core Concepts

Common dbt commands

  1. dbt deps.
  2. To download and install dependencies required for a dbt project.
  3. dbt docs generate.
  4. generating your project’s documentation
  5. dbt docs serve.
  6. starts a webserver on port 8080 to serve your documentation locally.
  7. dbt run.
  8. running dbt transformation.
  9. dbt seed.
  10. seeding data into tables.
  11. dbt source freshness.
  12. To check the freshness of data in the sources based on a defined threshold.
  13. dbt test.
  14. running tests on the models.

Hooks

  1. dbt hook are used to execute pre- and post model actions.
  2. It allows you to run SQL statement or commands before or after the main model transformations are performed.
  3. This can be useful for tasks such as data loading, setting up configurations, or performing additional calculation.

Materialisation

Materialisation refers to the way dbt persists the results of a model (a SQL query) in the database. Materialisation determine how and when the data is stored and updated in the database.

dbt provides several built-in materialisation for different use cases:

  1. views.
  2. create a database view.
  3. Use case: lightweight transformation where you want to always query the latest data without storing it physically.
  4. incremental.
  5. create a table and updates it incrementally with new data
  6. Use case: large datasets where only new or changed data needs to be processed and added to the existing table.
  7. tables.
  8. create a physical table in the database.
  9. Use case: transformation that are computationally expensive or when you need to ensure consistent performance by storing the results.
  10. ephemeral.
    1. It allows you to create temporary models that are not persisted in the database.
    2. Instead, ephemeral models are inlined into SQL queries of downstream models that reference them.
    3. This can be useful for intermediate transformations that do not need to be stored as separate tables or views.

Semantic Layer

Snapshot

  1. Snapshot allows you to capture and store the state of your data at specific points-in-time.
  2. This is particularly useful for tracking changes in your data over time, such as slowly changing dimensions (SCDs).

How to create snapshot

  1. You define a snapshot in a .sql file within the snapshot directory of your dbt project.
  2. The definition includes the source table, the unique key, and the columns to track.

Example Snapshot definition:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

{% snapshot my_snapshot %}

  {{
    config(
      target_schema="snapshots",
      unique_key="id",
      strategy="timestamp",
      updated_at="updated_at"
    )
  }}
  select * from {{ source("my_source", "my_table") }}

{% endsnapshot %}
  1. Run Snapshots with dbt snapshot command.
  2. This command will execute the snapshot and store the historical records in the specified target schema.

Valid configurations in dbt_project.yml

  1. Seeds
  2. models
  3. sources
  4. profile

dbt Tests

Why do we need test?

  1. Fell comfortable and confident in the code you are writing.
  2. Ensure your code continues to work as expected.
  3. To help data consumers make data-informed decisions on accurate data.
  4. To increase the likelihood of success.
  5. To build trust in the platform.
  6. Save time as models documented with assertions helps future you (and others) contribute to the codebase.

What makes a good test?

  1. Automated. low effort/repeatable.
  2. Fast. if testing takes too long, no one will do it.
  3. Reliable. believe them when they say something does work.
  4. Informative. leave you clues about what to fix based on the error.
  5. Focused. every test should validate one assumption.

Test on One Database Object

  1. Assert something about the data that you think is true
  2. Contents of the data
  3. Constraints of the table
  4. The grain of the table

Troubleshooting

Table missing primary key constraint during execution

  1. Inspect the model’s compiled SQL code to verify that the primary key constraint is being created correctly.

Optimise SQL models with {{ref()}} function

  1. Replacing SQL queries that reference other models with the {{ref()}} function improves maintainability and ensures proper dependency management.

Best Practices

Structure a dbt project

  1. Organising models into logical folders and subfolders.
  2. Using modularisation and reusable macros.

Reference

  1. dbt: snapshot
  2. dbt: indirect selection
  3. GitHub: dbt-expectations
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

models:
  my_new_project:
    staging:
      +database: prod
    intermediate:
      +materialized: table
    marts:
      +materialized: table
      finance:
        +materialized: incremental
      engineering:
        +materialized: view