How to run a pre-build dbt model on Snowflake with DLH.io
Updated: Oct 6, 2022
DataLakeHouse.io (DLO.io) is a modern analytics platform with several pre-build ELT models for known SaaS applications as initially described in this post.
This article post shows the steps to run DLH.io and the dlh_salesforce package from dbt Labs (Data Build Tool); it has models for better analysis of known Salesforce objects such as accounts or contacts available on Sales Cloud, Service Cloud, Force.com platform and a few other editions. This free package can be found at: https://hub.getdbt.com/datalakehouse/dlh_salesforce/latest/
For our work here, Fivetran was used to ingest and synchronize the raw data from Salesforce into the Snowflake Data Cloud database. The dbt CLI version is 1.1.0 and the dlh_package version is the 0.1.3s.
As all dbt package require, it is necessary to add it to the packages.yml file in your dbt project root folder like below:
Once added it is necessary to run the command dbt deps so the package can be downloaded:
A folder called dlh_salesforce will appear inside the dbt_packages folder within the following folder structure:
Before running any package model, it is necessary to configure the source database and schema and also the target schema where your models will land on Snowflake. This configuration goes into the dbt_package.yml file inside your dbt project. Our data lake is named BRFC_FIVETRAN, and it stores the Salesforce source tables at the SALESFORCE schema. Regarding the target schema, our models are stored in the DEMO_SALESFORCE schema. All of that said, our configuration is the following:
Since Fivetran was used for this proof of concept, it was necessary to add underscores to the columns names. For example the DLH package expects columns such as ISDELETED, but Fivetran ingests the column as is_deleted, that said, columns that consist of more than one word had to be renamed to the same name as Fivetran ingested.
Running the package
Run a model from the package using the following command:
dbt run -m V_SFC_ACCOUNTS_HIERARCHY
Bellow are the results of the populated table from Snowflake Data Cloud:
As shown in this article, the dbt pre-built model from DataLakeHouse.io can be easily deployed and tested.
Besides this, DLH.io has a data synchronization feature which replaces Fivetran and also several analytics models available on the paid version, for more details reach us at firstname.lastname@example.org
About the authors: Leandro Cavalcanti is a Data Engineer of BRF Consulting and
Angelo Buss is a Solutions Architect and the founder of BRF Consulting - an official partner of DatalakeHouse.io, AICG, Snowflake, dbt Labs and Salesforce.