Transforming Data with dbt in Snowflake

Overview

dbt is a powerful data transformation tool that brings high flecxibilty to data modeling and transformaiton through SQL.

dbt greatly expands the ability to develop and maintain data lineage within data warehouse and databases by allowing tables to be referenced dynamically and integrating version control amongst other features.

Development

To use dbt proerly several dependcencies need to be setup and integreated so that we can properly leverage all features of dbt within snowflake.

Setup - Environment and CI/CD

GitHub

Create repo for version control
Set up credentials for remote access and integration

Snowflake

Setup virtual warehouse for comoputation reosurces
Create Datawarehouse objects - databases, schemas, tables to load raw data
Create API integration with Github
Pull in Github Repo

dbt

initialize dbt project within github repo
Create necesary Macros
Create model directories that will point to specific databases
Create neceary intial files profile.yml, dbt_project.yml, yml source file

ELT Workflow

Load Raw Data

Source data is loaded into the raw database.
Columns are loaded in varchar format to prevent inferred data types or any unexpected data alteration/transformations when being loaded.
RAW database in snowflake will be used to house all raw data and be the "source" for the dbt models.

Transform with DBT

DBT transforms the data and creates downstream tables in the transform database and downstream views in the model database which are creating using data from the transform database.
From the model database, data is readily available for analytics and a BI/reporting tool can easily be connected to load in views.

DBT Models Interactive Code Snippet

View Full Models

Outcomes

With the dbt models build and ran we can now view the newly created tables and views in the database. As well as review data lineage through an interactive DAG(Directed Acyclic Graph) Which allows us to see upstream and downstream model dependencies clearly.
The DAG provides in depth details include differentiating whether the object is a table view etc.

Click on images to enlarge

GitHub Repo

Future Updates

Projects are pieces of work that can be continuously revisited and developed. Some future updates I would like to explore include:

Automating dbt through an orchestration/pipeline tool such as Boomi, Airflow or Fivetran
Track more metadata for upstream tables
Test incrementally loading data
Develop more views for data insights and expand on data models
Use dbt test features

< Previous

Next >

Setup - Environment and CI/CD

GitHub

Create repo for version control

Set up credentials for remote access and integration

Snowflake​

Setup virtual warehouse for comoputation reosurces

Create Datawarehouse objects - databases, schemas, tables to load raw data

Create API integration with Github

Pull in Github Repo

dbt

initialize dbt project within github repo

Create necesary Macros

Create model directories that will point to specific databases

​Create neceary intial files profile.yml, dbt_project.yml, yml source file

ELT Workflow

Load Raw Data ​

Transform with DBT ​

DBT Models Interactive Code Snippet

Projects are pieces of work that can be continuously revisited and developed. Some future updates I would like to explore include:

Snowflake

Create neceary intial files profile.yml, dbt_project.yml, yml source file

Load Raw Data

Transform with DBT