dbt¶

About¶

dbt is a tool for transforming data in data warehouses using Python and SQL.

It is an SQL-first transformation workflow platform that lets teams quickly and collaboratively deploy analytics code following software engineering best practices like modularity, portability, CI/CD, and documentation.

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

With dbt, anyone on your data team can safely contribute to production-grade data pipelines.

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-core 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.

CrateDB’s Benefits¶

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.

Setup¶

Install the most recent version of the dbt-cratedb2 Python package.

pip install --upgrade 'dbt-cratedb2'

Configure¶

Because CrateDB is compatible with PostgreSQL, the same connectivity options apply like outlined on the dbt Postgres Setup documentation page.

The dbt connection profile settings for CrateDB stored in profiles.yml are identical with PostgreSQL.

cratedb_analytics:
  target: dev
  outputs:
    dev:
      type: cratedb
      host: [clustername].aks1.westeurope.azure.cratedb.net
      port: 5432
      user: [username]
      pass: [password]
      dbname: crate     # CrateDB's only catalog is `crate`.
      schema: doc       # Define schema. `doc` is the default.
      search_path: doc  # Use the same value like `schema` by default.