Semantic Layer Implementation using DBT

This project demonstrates the end-to-end implementation of ELT data pipeline using dbt and semantic layer using Cube. Moreover, this project separated OLTP as data source and OLAP as data warehouse to simulate the real-world data pipeline.

The tech stack includes:

• PostgreSQL for OLTP database,
• Adminer for PostgreSQL UI (You can also use Dbeaver if you like),
• ClickHouse for OLAP database,
• Tabix for ClickHouse UI,
• Zookeeper + Kafka + Debezium for real-time data streaming using CDC,
• Kowl for Kafka UI,
• Airflow + dbt to transform data inside OLAP database,
• Cube as semantic layer,
• Metabase as BI visualization tool.

All of the components used are containerized in Docker for ease of setup.

The datasets used are Olist e-commerce and marketing datasets obtained from Kaggle.

Design

Prerequisites

All you need to do is installing Docker. After that, clone this repository by running:

git clone https://github.com/b1llywitant0/dbt-semantic-layer-implementation.git

Getting Started

1. Important: All scripts using Shebang should be in LF format, not CRLF. Please run if you use Windows:

git config --global core.autocrlf false

2. Create .env file. Please run:

cp .env.example .env

3. Creating network for containers and installing images. Please run:

make docker-build

Note: I separated the docker compose file for better understanding of each service.

4. Creating PostgreSQL (also Adminer) as OLTP data source and ingesting data into it. Please run:

make postgres

In postgres folder, there are config and script folders but they are not used. To make it simple, we directly get the Postgres image from Debezium, which stated inside the docker-compose file.

5. Creating ClickHouse (also Tabix) as OLAP for data warehouse and creating CDC tables. Please run:

make clickhouse

6. Creating CDC pipeline between OLTP and OLAP using Kafka and Debezium. Please run:

make cdc

Data inside Write-Ahead Logging of PostgreSQL will be decoded by Debezium and will be stored inside Kafka as message queue, then will be consumed by OLAP into CDC tables created before. Read more:

• ClickHouse PostgreSQL CDC Part 1
• ClickHouse PostgreSQL CDC Part 2

7. Creating Airflow and dbt. Please run:

make airflow

Data Load & Transformation

Crosschecking

Before we begin, it is important to check whether the data exists or not inside the ClickHouse.

1. Open Kowl to see whether the streaming data from PostgreSQL are successfully received by Kafka. Topics should appear, i.e. cdc_closed_deals, cdc_customers, etc. In consumer groups, connect-clickhouse-sinker should appear as well.

When creating cdc container for the first time, please wait all of the data consumed (lag=0 in connect-clickhouse-sinker) before proceeding to the next step.

2. Open Airflow and run reference_tables_postgres_to_clickhouse DAG manually to load data from PostgreSQL to ClickHouse.

This DAG had been set to run with dbt transformation using TriggerDagRunOperator, this step only to crosscheck the DAG and the data. Moreover, the first time you run this DAG, it will be triggered twice and resulting in duplicate data inside datawarehouse (full refresh group). Please run again once after that to remove duplicates.

3. Open Tabix and login using:
   • Name: <anything_you_like>
   • http://host:port: http://localhost:8123
   • Login: clickhouse
   • Password: root
4. Check the presence of data in ClickHouse inside 'raw' schema/database using SELECT statement.

Running dbt

Note: dbt is installed inside Airflow.

1. Open Airflow and run dbt_transformation DAG.

Generating Data Catalog

1. Accessing the Airflow container:

make airflow-bash

2. Change directory to dbt project folder inside the container:

cd dbt

3. Generating dbt docs:

dbt docs generate

4. Serving dbt docs:

dbt docs serve --port 8001 --host 0.0.0.0

You can see data lineage by clicking button on bottom right of the screen. The preview:

Semantic Layer and Data Visualization

Setup

1. Creating Cube container. Please run:

make cube

2. Creating metabase container. Please run:

make metabase

Accessing Cube

1. Open Cube Playground and see Cubes/Views created.

When querying, since we are using ClickHouse, there's still integration issue (at least, when I finished this project), such as: Cube Issue #9383

Accessing Metabase

1. Open Metabase and login using email in .env. To access admin, please use billywitanto@gmail.com. To see the access control created for dashboards, you can either use marketing@example.com or ecommerce@example.com.

To edit, you need Admin privilege. When accessing Cube in Metabase, there may be error to display the data. But, we still can query most of the needed data in that state. So, just go create or edit the questions and see whether the query works or not. I assume that the problem lies with the ClickHouse integration, such as issue stated above.

Dashboard Previews

Marketing Dashboard

Ecommerce Dashboard

Other References

About ClickHouse

• ReplacingMergeTree Table Engine
• Materialized View
• Using dbt-ClickHouse with examples
• Datetime functions
• Connecting ClickHouse with Airflow for Batch Job
• User Defined Functions

About dbt

• Best Practice of Structuring dbt Project
• Configuration of ClickHouse in dbt
• Snapshot Model for Generating SCD2 Tables
• Incremental Model
• dbt utils Package

About Cube

• Using Cube with dbt
• Cohort Analysis
• Many-to-many joins
• Using Cube with Metabase
• Preaggregation for data caching (Not used in this project, but interesting to read)

About Strategies

• Strategies for Change Data Capture in dbt
• Data Warehouse Guideline: SCD2
• Data Modeling Techniques for More Modularity
• Decimal Types
• Dead Letter Queue