Jump to content

Data Platform/Systems/dbt

From Wikitech

dbt (data build tool) is a tool to build data models with SQL. You write SQL files, dbt understands dependencies between them, and creates/updates tables/views in the data platform.

This page is for users who want to:

  • Develop dbt models in dbt-jobs
  • Run those models from stat hosts
  • Use those tables in scheduled production jobs (through Airflow)

Prerequisite knowledge

Before starting, you should know:

  • Basic git and GitLab flow (clone, branch, MR)
  • How to use stat machines.
  • Basic Spark SQL (dbt runs through SparkSQL)

dbt vs Airflow DAGs

  • Airflow is a scheduler/orchestrator: it decides when and in what order jobs run in production.
  • dbt is a SQL transformation framework: it helps define SQL models + dependencies.
  • In production, both are needed:
    • dbt defines transformations.
    • Airflow schedules and executes them.

Install and run on stat hosts

First, log in to a stat host. The following commands should be entered in a terminal, one line at a time.

From a Jupyter environment, you can click Terminal to open a window and type the commands below.


Clone and enter the git repository where dbt models are defined: 

git clone https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs.git
cd dbt-jobs

Install environment and dependencies: 

make dbt-install
source conda-analytics-activate dbt-jobs

Note: make dbt-install is only needed the first time the project is setup. In future sessions, only source conda-analytics-activate dbt-jobs is needed to enable dbt commands.

Now you can run dbt commands, for example: 

dbt run -s my_model_name
dbt test -s my_model_name

How dbt-jobs is organized by teams

dbt-jobs is a shared dbt repository used by multiple teams/projects. Each team/project can have its own folder structure, usually under models/.

Most people will mainly work in models/, because that is where model SQL files live. For more information about folders in the repository, check the README.md or dbt documentation.

About folder conventions:

  • dbt recommends using staging, intermediate, and marts folders, but anyone can organize differently if that works better. Folders don't change how dbt works, they are a way of structuring models. It's important to note that multiple models cannot have the same name even if they are in different folders.
  • Models can have configurations, like tags, or destination schemas. They can be defined at model level, or they can be configured automatically for entire folders in dbt_project.yml e.g:
    movement-insights-contributors-metrics:
      +schema: wmf_contributors
      +tags: ["monthly"]

Schema behavior:

  • Production (Airflow): models write to the schema configured in dbt_project.yml
  • Manual runs on stat hosts: models write under username_schemaname

How to iterate and develop a new model

  1. Create (or choose) a folder for your team/project in models/.
  2. Configure that folder in dbt_project.yml with at least:
    • +schema
    • +tags
    • (optionally) materialization and other settings
  3. Add your SQL file inside that folder.
    • The file name becomes the table name.
  4. Write your query.
  5. Run dbt:
dbt run -s my_new_model

Referencing tables correctly: source() vs ref()

In dbt, avoid hardcoding table names in FROM ....

When writing something like: 

SELECT * FROM ...

the object should usually be referenced with either source() or ref():

  • ref('model_name')
    • Use for other dbt models in this same repository
    • dbt tracks dependencies and build order automatically
  • source('source_name', 'table_name')
    • Use for tables created outside this repo/dbt project
    • Source metadata is defined in YAML (models/sources.yml or team-specific source files)

Example: 

SELECT *
FROM {{ ref('int_webrequest_requestctl_daily') }}

SELECT *
FROM {{ source('wmf', 'mediawiki_history') }}

Querying new tables (adding sources)

If you need to query an external table not yet declared as a source:

  1. Add it to a source YAML file (for example models/sources.yml or a team-local source file):
version: 2

sources:
  - name: my_external_schema
    schema: my_external_schema
    tables:
      - name: my_external_table
  1. Use it in SQL:
SELECT *
FROM {{ source('my_external_schema', 'my_external_table') }}
  1. Run dbt:
dbt run -s my_model

This keeps lineage, docs, and dependency behavior consistent.

dbt coding style guidelines (linter)

A linter is a tool that checks code style and common quality issues before merge. In this repo, the linter is SQLFluff (configured for dbt + Spark SQL).

Why this matters:

  • Keeps SQL style consistent across teams
  • Improves readability and long-term maintainability
  • Helps preventing common mistakes

You can sqlfluff manually from the stat hosts or from local, both to detect errors and to fix most of the formatting issues automatically.

Commands: 

sqlfluff lint
sqlfluff fix

Recommended habit:

  • Run sqlfluff lint before opening/updating your MR
  • Use sqlfluff fix for automatic fixes, then review changes

Documentation, dbt-docs

Dbt comes with a built-in mechanism to generate project documentation.

To generate the documentation and serve it, perform the following steps:

  • Ssh into a stats machine (e.g. stat1008)), set up the dbt conda environment and change into the checked out dbt-jobs repository (see above)
  • To generate the documentation locally, run the following command in the dbt-jobs repository root folder: dbt docs generate
  • To serve the documentation via a local webserver run the dbt docs serve command, best with a dedicated non in use port (e.g. 8765): dbt docs serve --port 8765
  • In a new terminal on your local machine create a tunnel to the stats machine with the specified port: ssh -N stat1008.eqiad.wmnet -L 8765:127.0.0.1:8765
  • Open a browser on your local machine pointing to the forwarded address: http://localhost:8765

For additional options for generating and serving dbt documentation consult the documentation page.

FAQ / How-to

What is dbt deps and when should I run it?

dbt deps installs dbt packages declared in packages.yml (for example utility packages like dbt_utils). Run it when:

  • You clone the repo for the first time
  • packages.yml changes
  • dbt asks for missing dependencies

On stat hosts: 

set_proxy
dbt deps
unset_proxy

Without dbt deps, models/macros from external packages will not be available.

How to manually backfill a model

Airflow allows to backfill a model since the configured `start_date` in the DAG, which often is not enough. To backfill a model further than that we need to enter into a `an-launcher` server and run the desired `dbt` command as the `analytics` user.

Steps to do it:

  • ssh an-launcher1003.eqiad.wmnet
  • source /opt/conda-analytics/bin/activate
  • Clone the repository: git clone https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs.git
  • cd dbt-jobs
  • Make sure logs/ and target/ folder exist and the analytics user can write on them.
    • mkdir logs && mkdir target
    • chmod 777 logs && chmod 777 target
  • Make sure you use a screen or similar to avoid issues if the connection is lost.
  • Run the dbt command in a single run or script:

Some examples: 

start_date=2020-07-01
sudo -u analytics /opt/conda-analytics/bin/dbt run --full-refresh --project-dir . --profiles-dir . --target production  --select "+base_moderator_actions" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"

start_date=2020-08-01

while [ "$start_date" != 2026-06-01 ]; do
sudo -u analytics /opt/conda-analytics/bin/dbt run --project-dir . --profiles-dir . --target production  --select "+base_moderator_actions" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"
start_date=$(date -I -d "$start_date + 1 month")
done

Troubleshooting

Errors when changing a model's schema

When adding or removing columns from a model- take the following steps to avoid errors:

  1. Set on_schema_change in the model config at the top of the sql file. This tells dbt what to do when the schema changes. The default value is ignore which causes dbt to either return an error or make it so your newly added column doesn't appear in the table. Choose sync_all_columns, append_new_columns, or fail depending on the desired behavior and how tolerant you are of unexpected schema changes.
  2. update the model's .yml entry to reflect the changes. E.g. if you remove column_a in example_model.sql make sure you also remove the column_a lines from the yml file. If you add column_b to the model's sql file, add it to the yml file too.
  3. include full-refresh to apply schema changes to incremental models. E.g. dbt run --full-refresh -s my_model

If you don't follow these steps, you may find errors like the following in the dbt output:

17:18:27  Failure in model base_moderator_actions_history (models/movement-insights-contributors-metrics/base/base_moderator_actions_history.sql)
17:18:27    Runtime Error in model base_moderator_actions_history (models/movement-insights-contributors-metrics/base/base_moderator_actions_history.sql)
  Runtime Error
    Cannot update missing field user_is_extendedconfirmed in spark_catalog.cmyrick_wmf_contributors.base_moderator_actions_history

or

20:13:22  Failure in model base_moderator_actions_reverts (models/movement-insights-contributors-metrics/base/base_moderator_actions_reverts.sql)
20:13:22    Runtime Error in model base_moderator_actions_reverts (models/movement-insights-contributors-metrics/base/base_moderator_actions_reverts.sql)
  Runtime Error
    cannot resolve '`snapshot`' given input columns:
Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=Data_Platform/Systems/dbt&oldid=2433046"