The newbie cloud architect diary

Data Build Tool (DBT) is an Open-Source CLI written in Python that is used to manage, document, test, compile, package and run compute queries on a Data Warehouse (DW) or a Data Lake (DL). DBT is developed by a Philadelphia-based startup called Fishtown Analytics, with revenue coming from solid investors, consulting services and a managed service offering called DBT Cloud, that allows for centralizing all operations of the SQL-pipeline life-cycle (ex. management, scheduling and monitoring).

DBT generally relies on a third-party Data Loading tool (like for example Fivetran and StitchData) to bring the data in, although it can also ingest CSV files (so called "Seeds"). Its descriptive language translates into SQL queries including specific features and functions of the underlying supported systems, as well as User Defined Functions (UDF) via Jinja macros.

In my opinion, one of the strengths of DBT is the ability to share modelling assets on the central repository https://hub.getdbt.com. Especially for mid-sized businesses which might not be able to on-board new datasources such as Salesforce and GoogleAds into their DW every month, what could be more convenient than just downloading a re-usable package including the definition of the operational source, a certified analytical target and its corresponding transformation? 

In some extend, DBT could become a reference tool and a One-stop-shop, just like Docker did 6 years ago. Although DBT currently seems to be missing some private repository or marketplace features on the hub, I actually expect this to be added in the near future. Last but not least, DBT reference documentation is of very good quality, and there are a number of shared best practices worth to read, like for example this article written with passion by the creators, or this usage guideline and cheat sheet from GitLab development team.

To start with, let's setup DBT client using Python package manager:

$ python3 -m venv ~/.venv/dbt-env
$ source ~/.venv/dbt-env/bin/activate
$ pip install dbt

You will find additional steps by Operating system here. This is what made installation work on my Ubuntu 20:

$ sudo apt-get install git libpq-dev python-dev python3-pip python3-venv libicu-dev
$ sudo apt-get remove python-cffi
$ pip install --upgrade cffi
$ pip install cryptography==1.7.2

Once this is done, we can check DBT version and out-of-the-box plugins.

Visit this page in case you are missing an adapter plugin, and this one in case you consider creating your own connector.

Let's locally create a new DBT project as part of a cloned GitHub repo.

As we can see, a project skeleton has been created under our repo, as well as a profile.yml under our user home folder. Also note that I've rename the project folder since I don't plan to have multiple projects here.

The profile is a YAML-file which allows you to configure your database connection settings, e.g.

Multiple "profile" sections can be defined as part of the file, the one in use being referenced from the project.yml file (ex. default). Profiles are typically segregated by different source or project.

A profile can define multiple "target" sections, which ones should be used to specify different environments (ex. dev, test, prod), the one in use being referenced by the target property. 

In order to avoid messing-up in production, it is actually recommended to have one different user or one different version of the profile accessed via the DBT_PROFILE_PATH environment variable.

 

The default profile settings are for Redshift. So the first thing you need to do is go the list of supported databases, copy the snippet dedicated to your system like in my case Snowflake, and adjust connection settings. Since credentials need to be defined in clear text inside the profile, you might alternatively prefer to make them load from an environment variable, using the following Jinja macro expression:

Here is how to test the connection:

Now that the connection works, we are good to go with the central piece of DBT which is modeling.

A DBT model is a collection of SQL-files defining the creation and transformations of physical model objects such as views and tables. Each SQL-file should contains a SELECT clause that is injected into a CREATE VIEW AS clause by default, so that a new database view can be created based on its defined columns. Note that each model produces exactly one object inside the schema defined as part of the profile, and named after the SQL-file (without extension).

You can overwrite the default model configuration (of creating views in current schema) from the project.yml settings...

 

...or using another Jinja macro expression as part of the SQL-file.

Note that a WITH clause is recommended for tables, so that even table rows can be created along with the table object. 

Or in case you just wanted to create a table with no data, you would do

As long as you already have some input data for creating models, then the queries will typically return correct column types, so that you don't need to care about them within the model definition. Otherwise, you need to specify them by casting columns, for example through one of the example syntaxes below:

However, I didn't find any way to create integrity constraints. Interestingly in my particular project, I do see the responsibility of provisioning and populating staging tables on the ingestion side (Talend Studio), not on the modelling side (DBT). In that context, DBT would just re-use a pre-defined source schema and its data types for creating the target schema. Still some significant effort is required for creating the models, since DBT does not come with a feature to conveniently auto-import source schema.

Other forms of Materializations such as incremental (models to only insert or update records since the last time that dbt was run) and ephemeral (models that are not persisted in database, but used by dbt as such for transformation).

In addition to the physical model, you can define logical properties inside further YAML-files placed under the models folder. Properties may apply at different levels of the definition:

• name should be matching a SQL-file-name
• description ...
• docs controls the visibility of the model when auto-generating documentation (see further below)
• meta information such as owner, model_maturity, contains_pii
• quote controls whether or not column names should be quoted
• tests integrity validation ex. not_null, unique, accepted_values, relationships, severity 
• tags are user-defined-values which can be used in commands as selector ex. --model tag:pii 

As you can imagine, you just have to create as many files as you have tables. Once you are ready with your model, you can test and run it.

Which basically creates your target schema, then if applicable provisions it with data, then if applicable executes some integrity tests. In case you need to troubleshoot, you can find the generated SQL-statements from with the target folder, and the logs from within the logs folder.
 

           staging: +pre-hook: "create schema if not exists {{ this }}" +schema: staging starschema: +pre-hook: "create schema if not exists {{ this }}" +schema: starschema +post-hook: "grant select on {{ this }} to group reporter"

It looks like I found a bug with the schema configuration, indeed DBT creates the custom schemas but fails at creating the tables:

It works as expected when running the queries by hand, or by skipping the schema configuration:

Now if we want to make this development including descriptions transparent to our stakeholders in the form of a reference documentation, we'll do:

The easiest way to consume the documentation is to start dbt web server locally on your machine.

You can obviously upload your content to an AWS S3 bucket configured to serve static web pages, or deploy dbt server to somewhere else, for example as a container.

A very cool feature of the documentation is that green button in the bottom-right corner of the screen, which visually represents your overall Data lineage (at table-level). Clicking on the tables will highlight paths and even allow you to move them around. Also, the filters from the bottom toolbar can be handy when your schema includes a lot of tables.

Another capability of DBT is the concept of packages. At a high-level, DBT packages are collections of assets in a format that can be shared for re-use. For example, the dbt_utils dependency comes with a number of commonly used macros that you can locally import like follows:

It creates a folder dbt_modules containing your assets and their basic dependency from the central module repository. The same process can be applied to fetch more elaborated dependencies like for example pre-defined models (we'll come to this in the next section).

DBT is fully text-based so that you can obviously commit your changes to the source repository of your choice, for example using a Git client or an IDE. Moreover, you can externalize DBT commands to an automation server agent for Continuous Integration (CI), as demonstrated in this video around the example of Snowflake Cost Usage analysis deployed and operated using GitHub Actions.

In its free and open-source version, DBT CLI is an awesome command line tool which perfectly supports pipeline automation, sharing and re-usability.

In its commercial offering, DBT Cloud is a managed service with a nice Web-UI, as well as additional governance and operations features. However, I must say that I didn't take the time to evaluate it so far.

As compared to the approach of Data Integration tools, DBT lacks some schema fetch and propagate, as well as some interactive syntax check and SQL preview features. As compared to the imperative approach to Database Change Management (DCM) such as Flyway and Snowchange, it lacks support for a metadata store and schema change evolution.