DATA ENGINEERING

Structuring a Self-Service BI Environment with DBT — Part 2

How we’re structuring it here at Afya

If you haven’t read part 1 yet, I recommend starting there before coming back here =)

In this text, we’ll clarify why we chose to use DBT as the primary tool for data transformation for Self-Service BI here at Afya. Additionally, we’ll cover how we’re organizing our development and production environment.

DBT

For those who aren’t familiar with DBT, I’ll try to briefly explain how this tool works. On its website, it’s described as a data transformation tool that enables analysts and data engineers to transform, test, and document their datasets more efficiently, along with providing native integration with leading cloud providers.

You can see from the architecture of a Data Lake, shown in the figure below, that DBT operates mainly in the Modeled layer where data transformations occur. What sets it apart from other transformation tools is its ability to scale the model development process through continuous deployment, along with an intuitive interface and the use of two very accessible languages like Jinja and SQL. It has also recently started incorporating the Python language.

In addition to the ease of creating models, DBT addresses Data Governance concerns, as mentioned in the previous article, which is a prerequisite for a Self-Service BI environment. The tool offers excellent features related to data lineage and cataloging, simplifying the entire analysis process for developers. For those looking to customize metadata, there’s also the option to do so through packages available on the DBT Hub.

Currently, there are two versions of DBT:

• DBT Core: An open-source platform that allows development and transformations to be done locally on a computer.
• DBT Cloud: A cloud-hosted environment by Dbt Labs, responsible for orchestrating DBT jobs and accessing documentation.

If you’re interested in delving deeper into this excellent tool, I recommend accessing their courses page, which is free. The training is in English with subtitles in the same language.

After this context-setting about DBT, we’re diving into the more technical (and fun!) part of the text. I’ll explain how we’ve structured our development and production environments here at Afya.

As mentioned, DBT has two versions, Core and Cloud. At Afya, we use the Core version for development, which is installed in a container to maintain a consistent development environment for everyone. Although we’re moving towards a pre-built Docker image, I’ll show the step-by-step process of how we set up our development environment.

1. Software Installation

• Download and install Visual Studio Code.
• Download and install Docker Desktop (Windows computers require WSL installed).

2. Environment Preparation

• Create a folder named ‘dbt-config’ and copy a directory containing the pre-built Dockerfile and the hidden .dbt directory with the pre-filled profiles.yml file.

Dockerfile

FROM ubuntu:20.04

COPY ./.dbt /root/.dbt
RUN apt update && apt upgrade -y
RUN apt install telnet -y
RUN apt install vim -y
RUN apt install git -y
RUN apt install python3 -y
RUN apt install python3-pip -y
RUN pip install dbt-core==1.2.2 dbt-redshift==1.2.1

profiles.yml

config:
  partial_parse: false

pebmed: 
 target: dev 
 outputs: 
   dev: 
       type: redshift 
       host: endereço_do_banco
       user: seu_usuario_redshift 
       pass: sua_senha_redshift 
       port: porta 
       dbname: banco_de_dados 
       schema: dbt_eng 
       threads: 4 
       keepalives_idle: 0 # default 0, indicating the system default 
       # search_path: public # optional, not recommended
   prod: 
       type: redshift 
       host: endereço_do_banco
       pass: seu_usuario_redshift 
       port: porta 
       dbname: banco_de_dados 
       schema: dbt_prod 
       threads: 4

We replace the information in the profiles.yml file to include Data Warehouse (DW) details, which in our case reside in AWS Redshift, along with our access credentials.

Get Leandro Carnevali’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This configuration differs from the DBT standard to avoid creating schemas in the DW based on the user; our schemas are based on the profile and environment being used. Currently, we have the following schemas:

• dbt_eng: Development and testing environment for data engineering.
• dbt_analise: Development and testing environment for data analysis.
• dbt_prod: Production environment used by DBT Cloud.

3. Creating and configuring the container

• In the dbt-config directory on the local machine, we run the command below. For Windows machines, we use PowerShell, and for Mac, we use the Terminal.

docker build -t ubuntu:20.04 .

• Once the image is ready, it’s time to start the container.

docker run --name dbt_local -it ubuntu:20.04

Now that we have the container ready, we navigate to the root folder and clone the repository. It’s the same one you registered on DBT Cloud. For more information, access this link.

cd root
git clone https://<seu-repositorio-do-dbt>

4. Final configuration of DBT Core

• Now that DBT Core is installed and the ‘profile.yml’ file is filled, we’ll install the packages contained in the ‘packages.yml’ file. In my case, I’m installing the calogica/dbt_expectations package using the command below:

dbt deps

• To verify if everything is working, let’s run the command dbt seed.

5. Setting up VS Code

• Assuming VS Code is already installed on the machine, we’ll install the necessary extensions to connect directly to the container. The WSL extension is only necessary for Windows machines.

• After the installations, it’s just a matter of connecting to the container through VS Code.

Finally, we open a terminal and run the commands below to install the extensions we use for DBT development through VS Code, remembering that we’re now inside the container.

code --install-extension bastienboutonnet.vscode-dbt && 
code --install-extension donjayamanne.githistory && 
code --install-extension dorzey.vscode-dbt-language && 
code --install-extension eamodio.gitlens 

code --install-extension foldager.dbt-shortcuts &&
code --install-extension innoverio.vscode-dbt-power-user && 
code --install-extension samuelcolvin.jinjahtml && 
code --install-extension mhutchie.git-graph && 
code --install-extension PKief.material-icon-theme

Now our development environment is fully configured and ready for use!

6. Adjusting the environment

• With the VS Code extensions already installed, let’s move to the final stage of preparing our development environment by modifying some user settings. Pressing Ctrl+Shift+P (Command+Shift+P) in VS Code, we’ll select the option Preferences: Open User Settings (JSON). Then proceed with the following steps.

• Once the settings.json file is open, we make the changes as follows.

{ 
    "workbench.iconTheme": "material-icon-theme", 
    "workbench.colorTheme": "Bluloco Dark", 
    "editor.minimap.enabled": false, 
    "launch": { 
        "configurations": [], 
        "compounds": [] 
    }, "files.associations": { 
        "*.sql": "jinja-sql" 
    }, 
    "editor.rulers": [ 
    110 
    ], 
    "editor.tabSize": 2 
}

But what about the production environment???

The production environment is essentially our DBT Cloud. It’s configured to run with the dbt_prod schema, where only the DBT user has permission for modifications, precisely because it needs to be the owner of the tables. Later, we set up a macro in our project that grants access to the tables.

DBT Cloud is capable of running essentially all scripts enabled in the Master branch in our Gitlab repository. Speaking of the repository, after many discussions, we organized it with the following structure.

In summary, the repository is divided into 3 main categories:

• Staging: Tables with a time cut for incremental loading support.
• Sources: Source models where we clean and structure data, managed by Engineering.
• Marts: Analytical models where we transform data into insights, managed by the Analysis/Data Science team.

This way, our Data Warehouse is structured to facilitate data management.

I hope you enjoyed the material, and if you have any suggestions, please reach out to me on LinkedIn. If you want to be part of the country’s largest medical ecosystem, with the most advanced data technologies in an environment that fosters innovation, come to Afya!

I’d also like to announce that, as I write this, we have two available positions for data engineers. If this has piqued your interest, simply access the link and submit your application. =)

I can’t finish without paying tribute to my data engineering team because none of this would have been possible without them, and all the original material was generated by them. Thank you so much, Alice, John, Ana Oliveira (Aninha), Ana Xavier (Anita), and Felipe — this is the result of your hard work!!! Thank you, Master Bruno, for giving me the opportunity to be part of this team!!!

In the next article, I’ll discuss how we track our applications using Snowplow, which alone generates approximately 150 million records per month in our Lake!!!