Login

  • Sep 2

Hightouch - Reverse ETL with Hands-on Demo

I once worked at a company where my team had created a Data Warehouse that integrated data from several systems like Saleforce, Workday, Customer Ads data from Google and Facebook Ads, Braze and many other source systems with Salesforce being the major source for Financial analytics. However, ~2–5% of financial data used for certain metrics did come from other sources like Ads data. 

Our Data Warehouse had KPI’s that integrated data from all these different sources that was displayed in a Dashboard. But, certain teams were comfortable analyzing the data in the Salesforce interface. This created a misalignment between teams who were looking at the dashboard versus teams looking into Salesforce interface as certain data was missed into Salesforce reporting solution. 

At that time we had to device a solution that involved building custom API scripts and it took a while to develop, implement, align with other teams, increasing operational maintainability and additional cost. 

Hightouch solves this exact problem with minimal configuration.

What is Hightouch?

It is a reverse ETL tool where it moves data out from Data warehouse back into operational tools. So, in example above, I would have just used Hightouch to map my KPI’s say, “ROAS” from my Data Warehouse (Snowflake, Databricks, Redshift, Big Query …) to a custom field on a record in Salesforce.

How does Hightouch Works?

In the diagram below blue box shows regular ETL process with Medallion Architecture and Gold Layer being your Data Warehouse. Hightouch will be configured to have permission to read from your Data Warehouse and push processed data back into the data source like Salesforce. 

Hands-On Project to Configure and Demo HighTouch

We will use a very simple hands-on project that can be setup in zero cost using Google BigQuery Free Tier(comes free with any standard Google Account) as the Source, and Google Sheets as the Destination.

We will divide this project into different Steps.

Step 1: Setting up Free Data Warehouse (Google BigQuery)

Step 2: Create Your Target Destination (Google Sheets)

Step 3: Connect Hightouch

Step 4: Configure the Model & Sync

Step 5: Run and Analyze

Elaborating on Step 1:

  1. Open console.cloud.google.com and log in with your Google Account.

  2. Create a new project as below

3. Open BigQuery from the left navigation menu.

4. Create the dataset by clicking on “Create dataset” and name it say “demo_data”

5. Click on “+” sign and create a query as below

CREATE OR REPLACE TABLE `demo_data.vip_customers` AS
SELECT 101 AS user_id, 'alex@example.com' AS email, 95 AS loyalty_score, 'Gold' AS status
UNION ALL
SELECT 102, 'sam@example.com', 40, 'Silver'
UNION ALL
SELECT 103, 'taylor@example.com', 88, 'Gold';

Now we are done with Step 1. Lets start with Step 2.

Step 2

Here we will be creating our target destination in Google Sheets

  1. Go to Google Drive and create a blank Google Sheet named Salesforce CRM Sync (Demo).

2. Name the first tab/sheet VIP Leads.

Step 3

Here we will be connecting with Hightouch

  1. Go to hightouch.com and sign up for a Free Account.

  2. Add Source:

  • Go to Sources → Add Source → select Google BigQuery.

  • Authenticate with your Google Account to connect your BigQuery dataset.

3. Add Destination:

  • Go to Destinations → Add Destination → select Google Sheets.

  • Authenticate with Google and select the sheet you created in Step 2.

Step 4

In this step we will be creating the Core Hightouch Workflow and configure its Model & Sync

  1. Create a Model (SQL Logic):

  • In Hightouch, go to Models → Add Model.

  • Choose your BigQuery source, select SQL Editor, and paste:

SELECT user_id, email, loyalty_score, status 
FROM `demo_data.vip_customers` 
WHERE loyalty_score > 80

2. Create the Sync:

  • Click Create Sync from your model and select your Google Sheet as the destination.

  • Row Identifier: Select user_id.

  • Column Mapping: Map the SQL outputs (email, loyalty_score, status) to spreadsheet columns.

  • Trigger: Set to Manual.

Step 5

  1. Click Run Sync.

2. Switch over to your Google Sheet — in a few seconds, you will see alex@example.com and taylor@example.com populate automatically.

3. Show how Delta Syncing works:

4. Go back to BigQuery and update Sam’s score so they become a VIP:

UPDATE `demo_data.vip_customers` SET loyalty_score = 90 WHERE user_id = 102;

5. Re-run the sync in Hightouch

6. Check your Google Sheet: Hightouch detects that only Sam changed, adding their row without re-writing the entire sheet

And that’s it. Our workflow is complete!!!