Author: Pratyasha Samal
Introduction:
DBT is a tool that helps to perform transformations with the help of SQL statements. In the recent release of DBT core, DBT has extended the functionality of Python which makes use of Snowpark in the backend. We can directly make .py files in the models directory similar to .sql files and can perform the transformations using Python support.
We have implemented transformations with the help of the Python model. To achieve this, we have used DBT core Version 1.3 beta, which has Python models support.
Prerequisites:
Install Python 3.8 and the snowflake snowpark library by using pip install snowflake-snowpark-python.
A basic level of understanding of DBT is required.
Implementation:
Step 1 :
Install DBT core version 1.3 beta with this command on vs code terminal :
pip install dbt-core==1.3.0b1
pip install dbt-snowflake==1.3.0b1
Run the command: dbt init This will create a directory for models similar to DBT cloud.
Give your project name:
Give Database name:
In our case, its Snowflake, so enter value 1
Give your Snowflake Account Name:
Create account Username and password :
Give Role of snowflake account and warehouse:
Give Database name and Schema name:
After connecting, check your connection using the command: dbt debug
Everything should be in ok status
DBT account is initiated in your directory Change the directory to a new directory, which may be similar to your project name : cd <dirname>
Step 2:
Create SQL model and write the script
Create source yml file and give database and schema and table name
Python Model:
Create Python file with .py extension and write the script as follows:
Refer SQL model in the ref function.
Note: you can use the source function as well
Eg: dbt.source(sourcename,tablename).
This will use Snowpark in the backend, so we have to use Snowpark functions such as select and filter for transformations.
Step 3: Now go to the terminal and run the command dbt compile After compiling, run your DBT model by command: dbt run Check your Snowflake database for transformed views/tables.
Note: We have to enable third-party libraries for snowpark otherwise, you will get an interpreter error. Steps to enable: 1) Go to snowsight 2) Go to admin 3) Go to Billing 4) Go to Terms and billing 5) Enable Anaconda Python packages
Conclusion:
We have transformed a table using the DBT python model which has been released recently by the DBT team with the help of DBT core.