BigQuery → Salesforce Sync

Skills used

• CRM & Data Architecture - Designed how customer, revenue and product data should be structured and exposed in Salesforce rather than simply replicating the source tables.

• SQL & Data Transformation - Used SQL to clean, aggregate and reshape BigQuery data before it was sent to Salesforce, including monthly revenue and product-usage calculations.

• Salesforce Integration - Defined external IDs, field mappings and synchronization logic to reliably create and update Salesforce records from warehouse data.

• Revenue Operations - Translated Sales and Account Management requirements into a practical CRM data model, making customer, billing and usage information available where the teams were already working.

• Cost-Conscious Solution Design - Designed the integration around Hightouch's free-tier limitations, avoiding a custom integration and keeping the ongoing solution simple to operate.

The Challenge and the Solution

Salesforce was the main tool used by Sales and Account Management, but a lot of the data they needed was sitting in BigQuery.
Customer information, billing data and product usage were available in the data warehouse, but getting that information into Salesforce required either manual exports or custom integrations.
The goal was to build a reliable way to bring this data into Salesforce without building and maintaining a custom API integration.
There was also a practical constraint: the solution needed to work within Hightouch's free tier.

BigQuery remained the source of truth, while Hightouch handled the synchronization into Salesforce. Instead of sending raw warehouse tables directly to Salesforce, I used SQL models in Hightouch to prepare the data first. This allowed the business logic to stay close to the source data and kept the Salesforce mappings relatively simple.

The solution covered three main areas:

Account master data
Customer and account information was synchronized from BigQuery into Salesforce on a daily basis.

Billing and revenue
Monthly billing information was aggregated in BigQuery and pushed into Salesforce after the monthly financial data was finalized.

License and product usage
Monthly product and license information was also aggregated in BigQuery and synchronized into Salesforce, giving customer-facing teams visibility into what customers were actually using.

Hightouch is a Reverse ETL platform that makes it possible to sync data from a data warehouse into operational tools such as Salesforce.

In this project, BigQuery was the source of truth and Salesforce was the destination, with Hightouch sitting between the two.

The main advantage was that there was no need to build and maintain a custom API integration. Data could be prepared using SQL in BigQuery/Hightouch and then mapped directly to Salesforce objects and fields.

For this particular project, Hightouch's free tier was also an important consideration. The solution was designed around the available number of syncs, which meant combining related data into a small number of well-designed pipelines rather than creating a separate integration for every requirement.

About Hightouch

How it worked

The basic flow was:

BigQuery → SQL model in Hightouch → Hightouch sync → Salesforce

For each data set, the SQL model prepared the data specifically for Salesforce. For example, account data could be cleaned up and enriched before the sync:

• Missing categories could be given a default value.

• Salesforce owner IDs could be resolved before the record was sent.

• Internal or test accounts could be excluded.

• Salesforce-specific fields could be populated from the warehouse.

• The Customer ID could be used as the external ID for matching records.

This meant that Hightouch was doing more than simply copying data from one system to another. It also became the place where the final transformation needed by Salesforce was defined.

Account Synchronization

The account sync was the most straightforward part of the integration. It ran daily and used the Customer ID from BigQuery as the unique identifier. New customers were created in Salesforce and existing customers were updated when their source data changed. One important part of the setup was making sure that Salesforce requirements were handled before the sync. For example, if Salesforce required an account category or owner, the SQL model had to make sure that a valid value was available. This avoided putting that logic into a large number of Salesforce-side automations.

Billing & Revenue

Billing data was handled differently because it represented historical monthly information rather than continuously changing account attributes. The raw billing data in BigQuery contained much more detail than was useful in Salesforce, so it was aggregated before being synchronized. The query grouped the data by:

• Customer

• Month

• Revenue group

• Sales channel
  

The result was a monthly summary that could be stored in a Salesforce custom object. The sync was intentionally run after the monthly financial close rather than automatically changing historical figures while the month was still being finalized.

License & Product Usage

The third data set was product and license usage. The source data contained product-level information that needed to be converted into a structure that worked well in Salesforce. I used SQL to aggregate the data by customer and month and then pivot the relevant product information into Salesforce-friendly fields. This made it possible for customer-facing teams to see things such as product usage and license quantities directly in Salesforce instead of having to look them up in another system. This part of the integration required the most maintenance because changes to the product catalogue could require changes in both BigQuery and Salesforce.

Working Within the Hightouch Free Tier

One of the interesting parts of the project was that the integration had to fit within Hightouch's free-tier limitations. Rather than creating a separate sync for every individual data set, I designed the data model around the number of syncs available. This meant thinking about which data belonged together and where the transformation should happen. The result was a relatively small number of syncs covering the most important CRM data without introducing another paid integration platform or a custom integration service.

What I Learned

The main lesson from this project was that a CRM integration doesn't necessarily need to be a custom software project. With a good data model in BigQuery and a tool such as Hightouch, a large part of the integration can be handled through SQL and configuration. The more important part is getting the boundaries right:

BigQuery handles the data and transformation logic.
Hightouch handles the synchronization.
Salesforce provides the data in a form that Sales and Account Management can actually use.

This approach kept the integration relatively simple to operate while avoiding the cost and maintenance of building a custom middleware solution.

Result

The integration gave Salesforce users access to customer, revenue and product information that previously lived primarily in BigQuery.
It also created a repeatable pattern for bringing additional warehouse data into Salesforce in the future.
Most importantly, the solution was built around the actual business requirements rather than around the capabilities of the integration tool — while still keeping the implementation simple enough to operate within a free-tier setup.

Contact

Connect

daniel.szaloczi@gmail.com

+36705801887

© 2024. All rights reserved.