How to run a pre-build dbt model on Snowflake with DLH.io

Updated: 4 hours ago

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/


Important

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.

Installation


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:



Configuration


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:



Important

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:



Conclusion


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 contact@brfconsulting.com



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.

54 views0 comments