Who can use this feature?
- All roles with access level "high"
- Available on all plans
A BigQuery integration allows you to bring in additional data into Catalyst to enrich your account, opportunity, contact, and additional objects.
Connect your BigQuery account
Connect your BigQuery and Catalyst accounts using a project id and service account key. Catalyst encrypts authorization credentials with AES-256 at multiple levels (with option to enable SSH).
- In Google Cloud management console, create a BigQuery service account key.
-
Make sure that you have API enabled on your BigQuery API page. If you don’t see API Enabled, choose Enable.
-
On the Service accounts page, choose your BigQuery project, and then choose Create service account.
-
On the "Service account details" page, enter a descriptive value for service account name (e.g., catalyst-bigquery-integration), and click Create.
-
On the "Grant this service account access to the project" page, add the following roles:
-
BigQuery Data Viewer
-
BigQuery Metadata Viewer: Catalyst needs this permission to retrieve table and dataset information
-
BigQuery Job User: Catalyst needs this permission to create jobs to retrieve data.
-
-
Click Continue.
-
Leave the key type set to JSON, and click Create.
- Save the .json file.
-
- In Catalyst, navigate to Settings > Integrations.
- Locate BigQuery from the list of inactive integrations, click + Add.
- Enter the following BigQuery details:
- Project ID
- Service account key (JSON)
- Click Connect.
Once connected, add your data source (see below).
Although it is optional, we recommend adding Catalyst IP addresses to your allowlist: 3.95.50.240 | 34.206.178.38 | 34.192.22.163
Add a BigQuery data source in Catalyst
Once connected, you can add a table or CSV file from BigQuery as a Catalyst data source. You can add multiple data sources (databases, schemas, and tables) from the same data warehouse.
Catalyst records and ingests data on a daily basis. If your source data contains aggregated roll-ups (e.g., last 7 days, weekly, monthly, etc.) or uses data from several tables, we recommend creating a new view or table in BigQuery as one source selection, prior to adding a data source in Catalyst.
- From within the BigQuery integration in Catalyst (see above), click + Add Source.
- Enter the details for the data source (see below).
- Click Save.
You can now add a new Catalyst data warehouse field that syncs to the field in your BigQuery data source.
Data Warehouse integrations update every 6 hours, 4 times a day. You can map Catalyst fields immediately after adding a data source, but fields will not populate with data until after the next sync.
Data source details
- Source nickname: Unique name to identify the source in Catalyst.
- Catalyst object: Choose the object you're mapping to in Catalyst.
-
Data path: Map the data path you're connecting Catalyst to in your data warehouse:
- Database
- Schema
- Table
-
Matching strategy: Choose the identifier to use to match your data to your records:
- Salesforce Field ID: Highly recommended. It's best to use an 18-digit SFDC ID when setting up your tables; however, both 15 and 18-digit IDs work.
- Select a field: Choose any supported field that exists in Catalyst for the selected object (e.g., text, number, Boolean, ISO 8601 formatted dates)
- Matching column name: Choose the column in your table that is your identifier.
-
Table structure: Specify how your table is structured (unique or multiple rows).
-
Current snapshot of Salesforce Object ID: Choose this option if the table you're using already has a unique row per Salesforce Object ID record.
-
Latest entry per Salesforce Object ID using timestamp: Choose this option if the table you're using contains multiple rows for a single unique Salesforce Object ID record. Select a timestamp field from the drop-down to indicate the most recently updated record for each Salesforce Object ID; allows Catalyst to reflect the most accurate value at the end of each day.
Performing this operation in your data warehouse could have cost implications, depending on your pricing structure.
-
Current snapshot of Salesforce Object ID: Choose this option if the table you're using already has a unique row per Salesforce Object ID record.