Building Self-Service Data Infrastructure with DBT: Maximizing Efficiency and Empowering Analysts

Photo by Federico Respini on Unsplash

Intro

In today’s data-driven world, companies struggle with the challenge of managing and analyzing vast amounts of data stored across various technologies and platforms such as data warehouses, RDS, S3, Salesforce, and more. The role of a Data Engineer has become pivotal, often serving as a bottleneck due to constraints in manpower and time within these data-driven organizations.

As a result, the role of analysts has evolved. While there are many Business Intelligence (BI) tools available, they still require robust data infrastructure provided by engineering teams. What if analysts could have a self-service tool that empowers them to construct their own foundational data infrastructure from the raw data? build easy schedulers and create complex ETL’s , This is where DBT comes into play.

In this article, I will dive into the structure and setup of a DBT project, outlining the problem we aim to address (enabling self-service for analysts). We will explore the project’s architecture, covering aspects ranging from development and continuous integration/continuous deployment (CI/CD) to production. Additionally, I will discuss strategies for effectively managing multiple DBT projects and the challenges we encountered and solved during this project.

1. Dbt project structure

In this section, we will explore DBT structure and it’s fundamental components for running our first model, In our article we use DBT core and our data warehouse is Galaxy, in that case we are using dbt-trino adapter that will install dbt-core and more dependencies, the adapters are the most basic level in our project and define how to connect to our data platforms.

After installing the adapter , we will init new dbt project.

pip install dbt-trino

dbt init

The structure appears as follows,

Basic DBT project structure

In this section we will cover the main components in dbt:

1. profiles.yml.
2. dbt_project.yml.
3. Models.

Profiles

In the profiles.yml file we will establish the core for our data warehouse. After conducting an analysis of dbt, I’ve arrived at the following findings:

1. Pay attention to the name property in the dbt_project.yml file, dbt_basic this value pointing the root key in the profiles.yml file, both should be identical.
2. It is possible to configure multiple profiles within a single profiles.yml.
3. During runtime, the selected target profile becomes both the source and destination for our dbt models.
4. It’s important to note that dbt is not designed to support querying data from one data source to another, nor is it intended for data transfer between different sources.
5. To effectively differentiate between development and production environments, we will make use of multiple profiles.

In the following profiles.yml file, we will set up two targets, dev and prd, as dev is the default choice.

When analysts will run dbt localy for tests or development , they will execute the corresponding command.

dbt run

To switch to the ‘prd’ mode, we will indicate the ‘prd’ target by:

dbt run --target prd

Tips

1. The schema property represents the default schema where the models will create objects such as tables and views. I’ve chosen to use a placeholder called ‘no_schema_specified’ as a way to prompt my analysts to specify a schema for each model using a customized macro (which I will explain later).

2. For security reasons, I’m using the function env_var() that reads environment variables that have been injected into the process.

DBT project

For our purpose running basic model, this file is straight forward, and well documented, I will emphasize a few points:

1. name property (line 5), is the name of our project, can be customize as you want.
2. profile property (line 10) indicate what profile to use in our project, it must be the same name we configured in the profiles.yml in the first line, in our example “dbt_basic”.

Models

This section holds our day-to-day work, In the sql files located here, we create our datasets. Let me demonstrate a very basic model and explain how it functions. I will also provide some methods for running and verifying these models.

In this example, you will notice that we have organized our models folder by creating subfolders for Tables and Views. When we run the dbt run command, dbt will execute all the SQL files found under the models folder, without considering the specific hierarchy within this folder.

The purpose of the limited_accounts.sql file is to implement a logic that creates a table named limited accounts within the my_schema schema. The table's contents will be derived from the results of the query select * from accounts limit 1.

Our first dbt model

As our project expands and includes numerous SQL files, we might explore alternative approaches for executing and validating models. Here are a few options:

1. Running all files in the “tables” folder: To execute all files within the “tables” folder, we can use dbt run --models tables.

2. Running a specific file: To execute a specific file such as “limited_accounts.sql”, we can use dbt run --select limited_accounts.sql

3. Viewing query results without creating a table or view: We can obtain a quick glance at query results without creating a table or view by using dbt show --select limited_accounts.sql

4. Running models with a specific tag: If we have models tagged, such as the “daily” tag in our example, we can execute all models with this tag using dbt run --select tag:daily.

These approaches offer flexibility in running and checking models based on our specific needs in a growing project.

2. Enabling Self Service DBT Architecture

In our data-driven company, we have analysts distributed across various teams, including custom success, marketing, finance, HR, R&D, and more. Almost every team has analysts who rely on the raw data prepared by our Data Engineering team. These analysts aspire to become self-sufficient, constructing their own unified and reliable source of insights. This business layer aims to serve customers, managers, and potentially stakeholders, providing them with accurate and meaningful information.

In this architecture I will present how it works and dive into each part.

Self Serve DBT Architecture

For security and isolation purposes, we have opted to separate DBT repositories for each department. This way, each department can manage its own models within their dedicated DBT repository and maintain their specific business layer.

The DBT repository is tracked using GIT and managed in Gitlab, where we make use of the gitlab actions for continuous integration and deployment (CI/CD). As a result, we generate a Docker image as our output, which encapsulates the entire DBT project and is ready for execution through CLI commands.

Our Dockerfile follows a standard structure. We utilize a Python 3.9 base image, set up the system, copy the DBT project into the Docker image, and install the necessary requirements. These requirements include DBT adapters, with dbt-trino being our adapter this instance.

# Please do not upgrade beyond python3.10.7 currently as dbt-spark does not support
# 3.11py and images do not get made properly
FROM python:3.9-bullseye

# System setup
RUN apt-get update \
  && apt-get dist-upgrade -y \
  && apt-get install -y --no-install-recommends \
    git \
    ssh-client \
    software-properties-common \
    make \
    build-essential \
    ca-certificates \
    libpq-dev \
  && apt-get clean \
  && rm -rf \
    /var/lib/apt/lists/* \
    /tmp/* \
    /var/tmp/*

# Env vars
ENV PYTHONIOENCODING=utf-8
ENV LANG=C.UTF-8

# Update python
RUN python -m pip install --upgrade pip setuptools wheel --no-cache-dir

# Set docker basics
WORKDIR /usr/app/dbt/

COPY . /usr/app/dbt/

RUN pip install -r requirements.txt

To utilize GitLab actions, we create a .gitlab-ci.yml file that is responsible for building the image and pushing it to the Docker registry.

stages:
  - containerize
containerize:
  stage: containerize
  script:
    - docker build -t dbt:latest .
    - docker push dbt:latest

Orchestrating it all together

At this stage, each department has its own dedicated Docker image, which is pushed to the registry. We will use Airflow for orchestration of our models. In our setup, we create a separate task for each DBT repository.

To run the Docker image, we utilize the DockerOperator, which allows us to execute CLI commands on our Docker image. In the example below, we use the dbt run — select tag:daily command to run all models that have been tagged as “daily”.

dag = DAG(
      "dbt", 
      default_args=default_args, 
      schedule_interval="0 0 * * *", 
      tags=[DAILY])

task = DockerOperator(
    task_id='dbt_task',
    image='dbt:latest',
    command='dbt run --select tag:daily',
    dag=dag
)

Challenges in Deploying our DBT Infrastructure In high scale

1. Cross-Account Data Integration with DBT and Starburst in Multiple AWS Environments

I have previously outlined our architecture, where each department maintains its own Git DBT repository. In our scenario, certain departments manage their data within separate AWS Accounts for security and ITGC compliance, notably HR and Finance. However, they express the need to access and combine data from the central and shared AWS account into their respective AWS accounts.

In essence, we aim to empower analysts to query and persist data across accounts. However, it’s crucial to note that DBT runs within the same source and destination, in our example the same AWS account.

To address this requirement, we leverage Starburst. With Starburst, we can manage multiple catalogs, each linked to a distinct AWS account. Starburst enables us to execute queries and transfer data effectively from one AWS account to another.

To simplify, let’s consider two AWS accounts: Finance AWS and Business AWS. Finance AWS houses financial data exclusively in its isolated environment, while Business AWS contains general business information. Our finance analysts seek to merge and store data from both AWS accounts into their designated AWS account.

The Finance DBT repository uses a dedicated Finance user configured within Starburst with read/write permissions across both catalogs. This setup allows analysts to construct models that query data from multiple catalogs, essentially moving data between AWS accounts.

DBT repo queries two differnet catalogs using starburst with dedicated user.

When incorporating catalogs in the DBT model, we specify the desired Starburst Catalog in the query. For instance, to access the Account table from the biz_catalog and combine it with the arr table in finance_catalog, we frame the query accordingly. The resulting table will be generated in the specified AWS account defined within the “database” property in the model configuration, as demonstrated in our example finance_catalog.

The result will be saved in the Finance AWS account.

2. Analysts day to day work using DBT, Test schemas and tables.

By empowering analysts and granting them the flexibility to build their ETL processes using DBT, and considering their ability to work locally and conduct tests in their daily routines, the process of creating schemas and tables has been greatly simplified. In our initial month after launching the MVP, we saw the creation of over 100 tables and numerous schemas in GLUE while analysts were training themselves with DBT. With around 80 analysts in the company, these numbers have the potential to increase rapidly.

To manage the creation location of local tests, we have customized the macro generate_schema_name.sql. This customization ensures that when analysts perform local testing, all tables and views are automatically generated within a designated schema named dev_dbt. Additionally, we run a simple Python script monthly to clean GLUE tables in this schema. Furthermore, we enforce a 30-day retention policy for files stored in the dev_dbt bucket.

{% macro generate_schema_name(custom_schema_name, node) -%}

   {%- set default_schema = custom_schema_name -%}
   {%- if target.target_name == 'dev' -%}
       {%- set default_schema = 'dev_dbt' -%}
    {% else -%}
        {%- if custom_schema_name is none -%}
          {{ exceptions.raise_compiler_error("Schema must be provided in " ~ node.unique_id ) }}
        {%- endif -%}
   {%- endif -%}

   {{ default_schema }}

{%- endmacro %}

Summary

In this article, I illustrated how we can use DBT to create a self-service tool for analysts, giving them more flexibility and independence while reducing their reliance on Data Engineering teams.

I discussed setting up a DBT repository from the beginning and covered the basic components. We also explored the overall structure and managing DBT models in production using Airflow. Additionally, I highlighted the obstacles we encountered when implementing our infrastructure at a larger scale with over 80 analysts.

I believe that we will continue to see an increase in such solutions for analysts and Data Engineering teams.

• DBT documentation can be found here

* All images, unless otherwise noted, are by the author*