{"id":218418,"date":"2022-01-25T09:46:09","date_gmt":"2022-01-25T08:46:09","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=big-data-tools&p=218418"},"modified":"2022-11-30T13:49:20","modified_gmt":"2022-11-30T12:49:20","slug":"how-i-started-out-with-dbt","status":"publish","type":"big-data-tools","link":"https:\/\/blog.jetbrains.com\/zh-hans\/big-data-tools\/2022\/01\/25\/how-i-started-out-with-dbt","title":{"rendered":"How I started out with dbt\u00ae"},"content":{"rendered":"\n

For some time now, I\u2019ve noticed that dbt<\/a>\u00ae is gaining popularity. I\u2019ve seen more questions and more success stories, so a couple of days ago I decided to try it out. But what exactly is dbt anyway?<\/p>\n\n\n\n

Here is the first phrase you can find in its documentation<\/a>: \u201cdbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles turn these select statements into tables and views.\u201d It sounds interesting, but maybe that\u2019s not entirely clear. Here\u2019s my interpretation: dbt is a half-declarative tool for describing transformations inside a warehouse.<\/p>\n\n\n\n

dbt doesn\u2019t perform any extractions or loads (as in ELT); it is only responsible for transformations.<\/p>\n\n\n\n

A remarkable fact about dbt: it uses 2 data engineering lingua franca: SQL and YAML.<\/p>\n\n\n\n

So, let’s get going!<\/p>\n\n\n\n

Installation<\/h1>\n\n\n\n

As dbt Core is written in Python I would usually install it with pipx<\/a>. But here is the catch: there are many different connectors<\/a> from dbt to other databases, so installing dbt in an isolated environment may not be that beneficial in this specific situation.<\/p>\n\n\n\n

An alternative way is to use Python virtualenv and install dbt there, but for the sake of simplicity, I won\u2019t describe it here. Thus, I\u2019ll install dbt for Postgres with:<\/p>\n\n\n\n

pip install \u2013-user dbt-postgres<\/pre>\n\n\n\n
This takes some time because there are many dependencies, but not too many. The next step is to create a project.<\/p>\n\n\n\n
Creating a project<\/h1>\n\n\n\n
Navigate to a directory where you want to create a project and call:<\/p>\n\n\n\n
dbt init<\/pre>\n\n\n\n
During execution, this command asks several questions: the name of the project and id of the connector. I have only one connector \u2013 postgres \u2013 so I will select it by doing the following:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Given that I\u2019ve used demo-project<\/code> as the project name, the following file structure will be generated:<\/p>\n\n\n\n
demo-project\n\u251c\u2500\u2500 analyses\n\u2502   \u2514\u2500\u2500 .gitkeep\n\u251c\u2500\u2500 data\n\u2502   \u2514\u2500\u2500 .gitkeep\n\u251c\u2500\u2500 dbt_project.yml\n\u251c\u2500\u2500 .gitignore\n\u251c\u2500\u2500 macros\n\u2502   \u2514\u2500\u2500 .gitkeep\n\u251c\u2500\u2500 models\n\u2502   \u2514\u2500\u2500 example\n\u2502       \u251c\u2500\u2500 my_first_dbt_model.sql\n\u2502       \u251c\u2500\u2500 my_second_dbt_model.sql\n\u2502       \u2514\u2500\u2500 schema.yml\n\u251c\u2500\u2500 README.md\n\u251c\u2500\u2500 snapshots\n\u2502   \u2514\u2500\u2500 .gitkeep\n\u2514\u2500\u2500 tests\n    \u2514\u2500\u2500 .gitkeep<\/pre>\n\n\n\n
Also, it will create (or update) the file ~\/.dbt\/profiles.yml<\/code>, where database connection settings are stored.<\/p>\n\n\n\n
As we can see, most of the project is just empty directories. This lets us understand what the most important parts are:<\/p>\n\n\n\n
  1. dbt_project.yml<\/code> is the description of the project\u2019s global settings.<\/li>
  2. models<\/code> is the directory with the description of models we will work with.<\/li><\/ol>\n\n\n\n
    Now it\u2019s time to understand what the model is. A model is an atomic entity of dbt describing a model of our domain. It is just a single SQL file containing a single SELECT<\/code> statement. Later on, it will be kept in our storage as a view, materialized view, or other related entity.<\/p>\n\n\n\n
    Besides the SQL model, we should also have a schema.yml file. This file contains a declarative description of models, tests, and types. I\u2019ll elaborate on schema.yml later.<\/p>\n\n\n\n
    Now it\u2019s time to write the first example.<\/p>\n\n\n\n
    Test schema<\/h1>\n\n\n\n
    Consider the following DDL:<\/p>\n\n\n\n
    CREATE TABLE IF NOT EXISTS customer\n(\n   id         BIGSERIAL PRIMARY KEY,\n   email      TEXT                                   NOT NULL,\n   username   TEXT                                   NOT NULL,\n   activated  BOOLEAN                  DEFAULT FALSE,\n   created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS post\n(\n   id         BIGSERIAL PRIMARY KEY,\n   author     BIGINT                                 NOT NULL REFERENCES customer,\n   content    TEXT,\n   created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS comment\n(\n   id         BIGSERIAL PRIMARY KEY,\n   author     BIGINT                                 NOT NULL REFERENCES customer,\n   content    TEXT,\n   created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,\n   post       BIGINT                                 NOT NULL REFERENCES post\n);<\/pre>\n\n\n\n
    It\u2019s a straightforward schema with three tables: customer<\/code>, comment<\/code>, and post<\/code>. Each post has an author (customer), and each comment has an author (customer) and a post. If you prefer a visual representation of tables, here it is:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Just for the sake of realism, I added several not entirely necessary fields like created_at<\/code>.<\/p>\n\n\n\n
    Now I will put some data into my DB just to have something to test my model on.<\/p>\n\n\n\n
    INSERT INTO customer (id, email, username, activated, created_at) VALUES (2, 'asm0dey@jetbrains.com', 'asm0dey', true, '2022-01-13 13:59:38.029016 +00:00');\nINSERT INTO customer (id, email, username, activated, created_at) VALUES (3, 'pavel.finkelshteyn@jetbrains.com', 'asm0dey', true, '2022-01-13 18:05:22.305746 +00:00');\nINSERT INTO comment (id, author, content, created_at, post) VALUES (1, 3, 'Test Comment', '2022-01-13 18:06:08.872014 +00:00', 1);\nINSERT INTO public.post (id, author, content, created_at) VALUES (1, 2, 'Hello, world!', '2022-01-13 13:59:59.911324 +00:00');<\/pre>\n\n\n\n
    Two customers, one post, one comment \u2013 nothing fancy, but already enough to start my experiments.<\/p>\n\n\n\n
    First model<\/h1>\n\n\n\n
    First off, I will define the most simple model possible: a user model. I delete everything from the models\/example directory and create a new file called user.sql<\/code> there. Inside I write:<\/p>\n\n\n\n
    SELECT * FROM customer<\/pre>\n\n\n\n
    That\u2019s all. Of course, it won\u2019t be that easy in real systems, but just imagine that we have a more complex data structure. Now, it\u2019s time to define our view with its constraints in schema.yml and define what schema.yml is. Basically, schema.yml<\/code> is the registry of all of our models in the current directory. Since all of the models are organized into a tree structure, descriptors can define some properties for underlying directories.<\/p>\n\n\n\n
    Here is my description of the user<\/code> model:<\/p>\n\n\n\n
    version: 2\n\nmodels:\n- name: user\n  description: \"Same as customer, but with another name\"\n  columns:\n    - name: id\n      description: \"The primary key for this table\"\n      tests:\n        - unique\n        - not_null\n    - name: email\n      description: email\n      tests:\n        - unique\n        - not_null\n    - name: username\n      description: unique name of customer\n      tests:\n        - not_null\n        - unique<\/pre>\n\n\n\n
    It\u2019s almost self-descriptive, but let\u2019s look at several interesting things:<\/p>\n\n\n\n
    1. Even though I select all fields from the customer<\/code> table, I define only three fields in the schema. This demonstrates that they don\u2019t have to match each other. Of course, not enumerating all these fields leaves us prone to errors and is therefore not recommended.<\/li>
    2. The model itself and its columns have a field named description<\/code>. This description will go into the documentation.<\/li>
    3. Each column may have field tests<\/code>. I will run these tests to see if everything works as expected. Out of the box, dbt ships with four generic tests already defined: unique<\/code>, not_null<\/code>, accepted_values<\/code>, and relationships<\/code>. You can read more about them and custom tests in docs<\/a>.<\/li><\/ol>\n\n\n\n
      Now that I have this very basic model, I can create it in my DB with the dbt run<\/code> command.<\/p>\n\n\n\n
      This is how the output looks on my machine:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      What did this command do? It created a view named user<\/code>.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      And its content precisely corresponds to our SQL query:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Testing model<\/h2>\n\n\n\n
      Now, let\u2019s test if our model complies with the dbt test<\/code> command (spoiler: they should fail).<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      What a surprise \u2013 I have a “nonunique” username! And you know what’s cool? It gives me the location of the script executed to perform the check, so let\u2019s open it. Here\u2019s how it looks:<\/p>\n\n\n\n
      select\n  username as unique_field,\n  count(*) as n_records\n\nfrom \"demo\".\"public\".\"user\"\nwhere username is not null\ngroup by username\nhaving count(*) > 1<\/pre>\n\n\n\n
      To check what\u2019s wrong, I can just hit Ctrl<\/kbd>+Enter<\/kbd> in my IDE and see problematic usernames. Here\u2019s how it looks for me:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Now I know what to do to fix it \u2013 I\u2019ll just update my username. pasha.finkelshteyn<\/code> should do the trick.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Tests passed!<\/p>\n\n\n\n
      But now the question arises: what if I want to build a more complex model? A model on top of other models, for example?<\/p>\n\n\n\n
      Second model<\/h1>\n\n\n\n
      Of course, it\u2019s possible thanks to jinja<\/a> templating in models.<\/p>\n\n\n\n
      Jinja is a modern and powerful templating library used in all kinds of projects. There are such well-known tools as Ansible<\/a> and Django<\/a>. dbt does not usually utilize the full power of Jinja, but only a tiny subset of it (phew!). We need a dbt-specific function, called ref<\/code>. It is almost clear from its name that it can refer to one model from another.<\/p>\n\n\n\n
      Let\u2019s say, for example, that we want to view users with a count of posts they wrote. To perform this task, I\u2019ll create a model.<\/p>\n\n\n\n
      WITH users AS (\n  SELECT u.id user_id, u.username username\n  FROM {{ ref('user') }} u\n)\nSELECT user_id,\n      username,\n      count(post.id) post_number\nFROM users,\n    post\nWHERE post.author = user_id\nGROUP BY user_id, username<\/pre>\n\n\n\n
      Also, I register the model in the schema:<\/p>\n\n\n\n
      - name: user_post_count\n  description: info about users and posts they wrote\n  columns:\n    - name: user_id\n      type: bigint\n      tests:\n        - unique\n        - not_null\n        - relationships:\n            to: ref('user')\n            field: id\n    - name: username\n      type: text\n      tests:\n        - unique\n        - not_null\n        - relationships:\n            to: ref('user')\n            field: username\n    - name: post_number\n      description: number of posts user wrote\n      type: int\n      tests:\n        - not_null<\/pre>\n\n\n\n
      In both schema and model, you can see that I reference the already existing view with help of the ref function, and I hope that its usage is self-explanatory. It may be interesting to look at generated model code:<\/p>\n\n\n\n
      WITH users AS (\n  SELECT u.id user_id, u.username username\n  FROM \"demo\".\"public\".\"user\" u\n)\nSELECT user_id,\n      username,\n      count(post.id) post_number\nFROM users,\n    post\nWHERE post.author = user_id\nGROUP BY user_id, username<\/pre>\n\n\n\n
      The exciting thing here is that dbt itself substituted the reference with the current view location in the database.<\/p>\n\n\n\n
      Updating transform layer<\/h1>\n\n\n\n
      This time dbt build<\/code> performed a humongous amount of work, checking and recreating two views and running 13 tests.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      All the tests passed, so we have a new view (1) with a count of posts (2) for each user:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Generating docs on the transform layer<\/h1>\n\n\n\n
      The final thing I want to do is generate documentation on my current view. To accomplish this, I will run the dbt docs serve<\/code> command.<\/p>\n\n\n\n
      The generated documentation is just beautiful! For example, it contains:<\/p>\n\n\n\n