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.
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
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.
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
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 example_datawarehouse_profile: target: dev outputs: dev: type: postgres host: localhost port: 5432 database: crate schema: doc search_path: doc user: dbt password: pwd1234567A EOF
(please note the values for
search_path in this example)
A dbt project has a specific structure , 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:
- models with view, table, and ephemeral materializations
- dbt source freshness
- dbt test
- dbt seed
- Incremental materializations (with
incremental_strategy='delete+insert'and without involving OBJECT columns)
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.