Editor’s note: This article has been updated for accuracy and comprehensiveness in November 2023.
Marketing, Sales, Customer Service, and other business functions produce enormous volumes of data that must be processed, stored, and transferred between teams. CRM giants, such as Salesforce, can cover almost all business processes in the company. However, companies may use other tools like database or data warehouse services as additional storage for Salesforce data or a flexible reporting, analysis, and visualization tool. In this case, all the parties must integrate to exchange business data and collaborate efficiently.
This article describes the possible ways to integrate Salesforce CRM and Google BigQuery data warehouse:
- Custom Salesforce BigQuery Integration
- Salesforce and Google BigQuery integration using Skyvia
Table of Contents
- Custom Integration
- Connection via Skyvia
- About Salesforce
- About BigQuery
- Benefits of Salesforce and Google BigQuery connection
- Conclusion
Custom Integration
You can connect Salesforce to Google BigQuery by setting up your custom integration.
Salesforce supports two types of custom integration with BigQuery:
Their connection requirements are pretty similar. Both need the same standard BigQuery Data Viewer role for the service account. In both cases, account creation generates a JSON file of account properties with most of the necessary connection settings located. The permissions lists are the same for both types of connections, etc. But Legacy is the older SQL version that might not be compatible with other SQL solutions, and its syntax differs from the Standard one. These points complicate the integration with other SQL solutions.
In any case, your selection might mostly depend on how you construct queries and interpret the results.
To do that, you have to perform the following steps:
Extract data from Salesforce API
You have to choose the web service relevant to your needs to do that. Salesforce offers various API types for different use cases:
REST API is a simple and lightweight architecture based on RESTful principles. Read, search, or query your data, retrieve object metadata, and access information about limits in your org. REST API supports both XML and JSON.
SOAP API is designed to work with Salesforce objects. It is a standardized and secure web service protocol. SOAP API supports XML only.
Bulk API is a specialized RESTful API for loading and querying vast volumes of data at once. It is helpful to get and process millions of records fast. Bulk API is asynchronous, meaning you can submit a request and return later for the results.
Streaming API is a specialized API for setting up notifications that trigger when changes are made to your data. Streaming API is excellent when you need to get near real-time data updates.
Transform data
You have to consider the Google BigQuery-specific features when loading data from Salesforce.
Google BigQuery supports data import from Avro, CSV, JSON, ORC, or Parquet files. If you get Salesforce data from the API, which returns the result in other formats (for example, XML), you must transform data into the form BigQuery can ingest.
Make sure BigQuery supports the retrieved data types. You can find more information on supported data types here.
Load data
Google BigQuery supports several ways of loading data. You can find the details about each here.
Let’s review the Salesforce to BigQuery CSV manual integration scenario in a few steps.
- First, select the table(s) for export in your Salesforce data explorer panel.
- Then, set the export format as CSV and select Export to Cloud Storage. Here, you have to use the default compression value or choose the compression type as GZIP (GNU Zip) and download it.
- At last, go to the Data Explorer section of your Big Query and select Import > Batch Ingestion.
NOTE: Despite this method’s simplicity, it fits for one-time integration only. You can download the file from Salesforce and manually upload it to BigQuery through the menu. But it doesn’t work if you have multiple data sources with constantly changing data. So, if we’re discussing the “serious” integration tasks, let’s review the more convenient approach.
Connection via Skyvia
Skyvia is a no-code cloud service providing solutions for data integration (ETL, ELT, reverse ETL), automating workflows, cloud-to-cloud backup, data management with SQL, CSV import/export, and connectivity.
It takes several clicks to implement different Salesforce and Google BigQuery integration scenarios. You can replicate Salesforce objects and data to BigQuery, enrich Salesforce data with data from BigQuery, and build complicated flows involving other cloud apps or databases.
Find the examples of Salesforce and BigQuery integration using Skyvia below.
Prerequisites
Before starting your integration journey with Skyvia, you must complete some prerequisites.
You need active Salesforce and Google BigQuery accounts. You must be registered in Skyvia to create connections to Salesforce and Google BigQuery. Not registered yet? Go for it here:
Skyvia Replication
Replication copies the Salesforce data structure and the data itself and then creates the same data structure in BigQuery and keeps it updated if needed. The example below shows how to replicate available Salesforce objects and data to BigQuery.
To create replication integration, perform the following actions:
- Log in to Skyvia, click +NEW in the top menu, and select Replication.
- Select the Salesforce connection as a Source and BigQuery connection as a Target.
- Select the objects and fields to replicate. You can set filters to limit the copied records if needed.
- Enable or disable the available integration options.
The Incremental Updates option is enabled by default. It allows replicating only changes made to the source records.
- Click Schedule to set the integration schedule for the automatic run if needed.
- Run the integration and monitor the results.
Skyvia keeps the results of each integration run and allows checking the number of successful and failed records using the integration run history. Use the Monitor and Log tabs to cap the run results. Click on the specific run to see the details.
As a result, you have a copy of your Salesforce data in the BigQuery data warehouse.
Skyvia Import
With Skyvia Import, you can enrich Salesforce data with BigQuery data. This scenario allows you to load CSV files both manually when needed and automatically by schedule.
To set up the Import integration in Skyvia, do the following:
- Log in to Skyvia, click +NEW in the top menu, and select Import.
- Click on the Data Source database or cloud app Source type and select BigQuery connection.
- Select Salesforce connection as a Target.
- Click Add new to create an integration task. You can add multiple tasks in one integration.
- Select the object to import data from and use filters to limit the number of records if needed.
- Specify the object to import data to and select the action to perform.
- Assign field mapping. Skyvia maps the fields with the same names automatically.
- Run the integration and monitor the results, same as described above.
Other Skyvia Tools
Skyvia allows the implementation of more complicated integration scenarios with its Data Flow and Control Flow tools. With Data Flow, you can build custom complex flows involving other apps or databases, applying conditions using flexible mapping features.
With Control Flow, you can perform independent integration tasks, or data flows inside one Control Flow in a specific order or with special conditions. You can conduct pre- or post-integration actions and assign an automatic error processing logic.
Don’t know whether these tools are relevant to your needs? Try these both for free with a 2-week trial from Skyvia.
About Salesforce
Salesforce is a comprehensive global platform that brings companies and customers worldwide together. It provides various services for sales, marketing, commerce, and IT business functions. Salesforce allows users to create custom apps, offers opportunities for integration with other platforms and services, and provides reporting, analysis, and data visualization services.
About Google BigQuery
Google BigQuery is a cloud serverless enterprise data warehouse from Google. Google BigQuery allows querying petabytes of data in minutes and provides real-time data analysis, data visualization, and machine learning tools.
Benefits of Salesforce and Google BigQuery connection
Salesforce is a CRM giant that provides services for most business processes. Google BigQuery is a manageable and scalable serverless technology set designed to meet modern demand challenges of petabyte-scale data storage, networking, and sub-second query response times.
Google BigQuery and Salesforce integration combines the best Salesforce and BigQuery capabilities:
- You can transfer Salesforce data to BigQuery to keep a copy of your Salesforce data in a secure serverless environment, not worrying about storage space.
- There is no need to build and maintain complicated on-premises infrastructure as BigQuery is a cloud-based serverless technology.
- You can build efficient collaboration between teams by consolidating data from different Salesforce services into BigQuery for custom reporting, analysis, and visualization by BigQuery means.
Conclusion
Salesforce and BigQuery integration can enhance business efficiency and open new data management opportunities. The decision on how to do it is entirely yours.
You can manually build your custom integration and implement any integration use case within API capabilities. But you must consider that the integration must be carefully planned, prepared, and monitored after implementation.
You also need specific skills in software development, web services, and knowledge of Salesforce and BigQuery features. You have to acquire development tools to write your custom codes. Also, you need to have paid subscriptions to access Salesforce APIs, Google services, or other tools.
Custom integration is a flexible and effective way to connect Salesforce and BigQuery, but it’s also complicated, expensive, and time-consuming.
You can also use a more straightforward no-code method of Salesforce BigQuery integration offered by Skyvia. It allows you to set up effective integration in several clicks.
- Replication is helpful when creating a copy of your Salesforce data in the BigQuery data warehouse.
- Import is perfect if the source and target have different data structures. You can use it for integration in both directions. It is helpful to consolidate data from separate Salesforce objects into one BigQuery dataset. You can enrich Salesforce data with BigQuery data.
- Skyvia also offers Data Flow and Control Flow tools for complex scenarios.No specific skills and resources are needed, and no extra apps are required. Skyvia performs all the coding in the back end.
