DBT Testing And CI/CD

Author: Amulya Nidhi


Overview Of DBT

Data Build Tool(DBT) is known for its good performance at transforming data from warehouses.

It is not responsible for the extract and load process in ELT.


We can perform testing of data as well as CI/CD in DBT.


In our Project, we have performed Testing and CI/CD in DBT.


Use of DBT

A DBT project is a directory which contains .sql and .yml files. Models are the .sql files written in select statements. A project file dbt_project.yml defines our DBT Project. By default, the model is built as a view in the database. We can build the model as a table in the database.


Getting Started with DBT


At first we created a DBT account, connected it with a bitbucket repository to save all the codes.

The data warehouse that we have used for database connection is Snowflake.


The transformed data taken from the Snowflake database is db_dev.


We have built the models of each sector and deployed it in the DBT schema of production_env database.




Creation of Models

The multiple models created in our project are shown below.



In our project, models that we have made refer to the stock names.


The above will show 4 models created for 2 sectors.


One is the Transport_Logistics Sector, in which we have created 3 models of stocks named accuracy, jitfinfra, total, respectively.


Another is the Banking Sector, in which we have created 1 model of stock named aubank.


Code snippet for .sql file (i.e for accuracy model)


select

DATE ,

OPEN ,

HIGH ,

LOW ,

CLOSE ,

ADJCLOSE ,

VOLUME

from {{ source('transport_sector', 'ACCURACY_TRANSFORMATION') }}


Code snippet for .sql file (i.e for jitfinfra model)


select

DATE ,

OPEN ,

HIGH ,

LOW ,

CLOSE ,

ADJCLOSE ,

VOLUME

from {{ source('transport_sector', 'JITFINFRA_TRANSFORMATION') }}


Code snippet for .sql file (i.e for total model)


select

DATE ,

OPEN ,

HIGH ,

LOW ,

CLOSE ,

ADJCLOSE ,

VOLUME

from {{ source('transport_sector', 'TOTAL_TRANSFORMATION') }}


Code snippet for .sql file (i.e for aubank model)


select

DATE ,

OPEN ,

HIGH ,

LOW ,

CLOSE ,

ADJCLOSE ,

VOLUME

from {{ source('banking_sector', 'AUBANK_TRANSFORMATION') }}


The models inside dbt are run with command dbt run.



We can confirm the objects in the Snowflake.


Go to the classic console of Snowflake web UI. Refresh the database objects. We have PRODUCTION_ENV database, Under that in DBT schema, we can see all the tables.



We have created a source.yml file for the models created.


Code snippet for the .yml file

version: 2


sources:

- name: transport_sector

database: db_dev

schema: TRANSPORT_LOGISTICS

tables:

- name: ACCURACY_TRANSFORMATION

- name: JITFINFRA_TRANSFORMATION

- name: TOTAL_TRANSFORMATION

- name: banking_sector

database: db_dev

schema: BANKING

tables:

- name: AUBANK_TRANSFORMATION


Testing

Generic Test


Generic Tests are run on the specific columns in a model. In our project, we have performed Generic Test on the date column.The two built in tests that we performed on the date column are unique and not_null tests.


Unique test shows that the values stored in the date column are distinct. The Not_null test shows that the values stored in the date column are not in null values.


Write dbt test in command line to test all the models.



We can test for one particular model too(lets take for the accuracy model) typing dbt test –select accuracy will show all the testing performed for the model named accuracy.


Code snippet for the test.yml file


version: 2

models:

- name: accuracy

columns:

- name: DATE

tests:

- unique

- not_null

- name: jitfinfra

columns:

- name: DATE

tests:

- unique

- not_null

- name: total

columns:

- name: DATE

tests:

- unique

- not_null

- name: aubank

columns:

- name: DATE

tests:

- unique

- not_null


Specific Test

Specific Tests are run on the entire model. Therefore, we have run the specific test on the entire stocks named accuracy, jitfinfra,total and aubank, etc.We have checked whether the parameters open, close, high, low are not negative for the models.


Run the command dbt test –select open_check in the terminal to test whether the open column in our model is not negative.



Code snippet for the open_check.sql file


with open_value as (

select * from {{ref('accuracy')}}

union

select * from {{ref('jitfinfra')}}

union

select * from {{ref('total')}}

union

select * from {{ref('aubank')}}

union

)

select open

from open_value

where open <0


The same code we have used to check whether the parameters high, close, low are not in negative value.


Generate Docs

Write dbt docs generate on the command line to generate the documentation.

Click on view docs.


In the doc, we have the details of sql code of model, tests performed on the model, DAG graph,etc.



The above is the lineage graph of aubank model.



After the commit is done in dbt, we have created a pull request in the bitbucket repository to merge the code.



Once a request is created it is sent to the reviewer to receive approval and merge the request.

Setting Up The Production Environment

Click on the hamburger menu and click on environments.



We can see two environments, one for development and testing and other for deployment.



We have created a prod environment for the modeled schema refresh.



DBT Refresh Scheldule

We have created a Prod data loading schedule in the prod environment and scheduled it everyday at 6:00 AM UTC.




Setting Up Slack Notifications For The Job Execution:

We have integrated the slack account with dbt.



After integrating, if we go to the notifications sections , we have the option to choose whichever notifications we want to get displayed on slack.



Slack notifications for the job execution are shown below:



Conclusion

We have covered the creation of models for the stocks of 2 sectors, generic and singular tests are performed on the models. We have also seen how to run and test the entire model as well as for the single model, Continuous Integration and Continuous Deployment is also shown. Generation of documentation for the models is also discussed.


54 views0 comments

Recent Posts

See All