Using dbt with CrateDB


Dbt [1] is a tool for transforming data in data warehouses using Python and SQL.

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

The idea is that Data Engineers make source data available to an environment where dbt projects run, for example with Debezium or with Airflow. Afterwards, Data Analysts can run their dbt projects against this data to produce models (tables and views) that can be used with a number of BI tools.

dbt’s Features

The data abstraction layer provided by dbt allows the decoupling of the models on which reports and dashboards rely from the source data. When business rules or source systems change, you can still maintain the same models as a stable interface.

Some of the things that dbt can do include:

  • import reference data from csv files
  • track changes in source data with different strategies so that downstream models do not need to be built every time from scratch
  • run tests on data, to confirm assumptions remain valid, and to validate any changes made to the models’ logic

dbt and CrateDB

Due to its unique capabilities, CrateDB is an excellent warehouse choice for data transformation projects. It offers automatic indexing, fast aggregations, easy partitioning, and the ability to scale horizontally.

In this article, I will illustrate how to get the most important functionalities of dbt working by doing the necessary changes in the configuration.

Getting started

For running the following steps, you will need connectivity to a CrateDB cluster, and a Python installation on your workstation. The starting point will be a fresh install of dbt-postgres.

pip install dbt-postgres==1.6.0

Now, create a profiles.yaml file with your connection details.

cd ~
mkdir .dbt
cat << EOF > .dbt/profiles.yml
  target: dev
      type: postgres
      host: localhost
      port: 5432
      database: crate
      schema: doc
      search_path: doc
      user: dbt	  
      password: pwd1234567A

(please note the values for database, schema, and search_path in this example)

A dbt project has a specific structure [2], and contains of a combination of SQL, Jinja, YAML, and Markdown files. In your project folder, alongside the models folder that most projects have, create a folder called macros, and populate it with these macro override files:

Those dbt features have been tested successfully:

We hope you find this useful. CrateDB is continuously adding new features and we will endeavor to come back and update this article if there are any developments and some of these overrides require changes or become obsolete.

  1. GitHub - dbt-labs/dbt-core: dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications. ↩︎

  2. How we structure our dbt projects | dbt Developer Hub ↩︎

1 Like