Summary:

This blog explains how Snowflake table/s can be created with the help of Python models creation using DBT.

Objective:

1. Introduction of DBT models and Snowflake
2. Model creation in DBT using Python
3. Job Automation using DBT

Introduction of DBT models:

DBT is  Data Build Tool. It focuses on the transformation of ELT processes Transformations are done using select statements, which convert into tables and views. It makes the transformation process simple and more effective

It comes in two variations:

• DBT CLI is Command Line Interface that runs by a terminal
• DBT Cloud – It is a web-based application

A Model is a file containing a select statement. They are defined in .sql files. Snowflake table is created with the model filename. DBT will build this model data warehouse by wrapping it in  create view or creating a table as a statement.

Snowflake:

Snowflake is a data warehouse built on Amazon Web Services, Azure Cloud, and GCP.

Python Models creation in DBT

DBT Python Models are the same as DBT SQL models. It is defined as .py files. DBT Python Models are a function named model that returns a data frame, and SQL models are defined using a unique SELECT statement.

General syntax:

In general syntax, DBT is a class compiled by DBT core and session refers to the platform’s connection. We can refer to other models and sources using the dbt.ref() and dbt.source() functions.

Python Models have two materialization options which are table and incremental. So it does not support view creation.

Below are the steps followed for Python Models creation in Snowflake:

1. Create a free trial Snowflake account if you don’t have one. Then create a new database, schema, and warehouse.
2. Create a DBT account and configure snowflake details in it.
3. In the Models folder, create one .py file as shown below.

4. Below is the code snippet for the Python ModelS creation from the sources using source() functions.

The above code snippet is the SQL code translated to Python. We have imported Snowpark library, which is why it creates a Snowpark library by default. In dbt.config, we have to define the materialization of the Python Models.

If we have to define table names directly from Snowflake schemas,  you need to create one source.yml in the sources folder and configure the details, as shown below.

5. Once done, save the code .py file and compile the code. Below is the lineage of the .py file.

6. Now run the Python Models in the command prompt. On successful execution, the table is created in the Snowflake with the table name as month_view as shown below.

Job Automation using DBT:

The scheduler helps automate these queries by using DBT Cloud, simplifying  data. To use DBT Cloud, you must set up DBT linked to a GitHub repository and any data warehouse like  Snowflake.

Steps followed:

1. Set up the GitHub repository and create a new repository
2. Configure the GitHub repository to store DBT files
3. Commit the changes in the DBT folder, as shown below

4. Select the Deploy option in the DBT and create an environment

5. Create a Job in the environment and configure Give the command to run on schedule

General syntax:

6. In ‘Triggers’, you can schedule jobs as per the requirement.

7. On successful execution, it will show the run status as below.

8. Once the steps mentioned are executed, the materialized table will be updated per the schedule.