Work with Salesforce Data Cloud data inBigQuery

Data Cloud users can access their Data Cloud datanatively inBigQuery. You can analyze Data Cloud data withBigQuery Omni and perform cross-cloudanalytics with the data in Google Cloud. In this document, we provideinstructions on accessing your Data Cloud data and severalanalytical tasks you can perform with that data in BigQuery.

Data Cloud works with BigQuery based on thefollowing architecture:

Before you begin

Before working with Data Cloud data, you must be aData Cloud user. If you have VPC Service Controls enabled on yourproject, you will need additional permissions.

Required roles

The following roles and permissions are required:

• Analytics Hub Subscriber (roles/analyticshub.subscriber)
• BigQuery Admin (roles/bigquery.admin)

Share Data From Data Cloud

This documentation demonstrates how to share data from Data CloudtoBigQuery -BYOL Data Shares - Zero-ETL Integration with BigQuery.

Link Data Cloud dataset to BigQuery

To access a Data Cloud dataset in BigQuery, youmust first link the dataset to BigQuery with the following steps:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. ClickSalesforce Data Cloud

   Data Cloud datasets are displayed. You can findthe dataset by name using the following naming pattern:

   listing_DATA_SHARE_NAME_TARGET_NAME

   Replace the following:
   • DATA_SHARE_NAME: the name of the data share in the Data Cloud.
   • TARGET_NAME: the name of the BigQuery target in theData Cloud.

3. Click the dataset you want to add to BigQuery.

4. ClickAdd dataset to project.

5. Specify the name of the linked dataset.

Once the linked dataset is created, you can explore the dataset and the tables in it.All of the tables' metadata is retrieved from Data Clouddynamically. All the objects inside the dataset are views that map to theData Cloud objects. BigQuery supports three types ofData Cloud objects:

• Data Lake Objects (DLO)
• Data Model Objects (DMO)
• Calculated Insights Objects (CIO)

All of these objects are represented as views in BigQuery.These views point to hidden tables that are stored in Amazon S3.

Work with Data Cloud data

The following examples use a dataset called Northwest Trail Outfitters (NTO) thatis hosted in Data Cloud. This dataset consists of three tablesthat represent the online sales data of the NTO organization:

• linked_nto_john.nto_customers__dll
• linked_nto_john.nto_products__dll
• linked_nto_john.nto_orders__dll

The other dataset used in these examples is offline Point of Saledata. This covers the offline sales, and consists of three tables:

• nto_pos.customers
• nto_pos.products
• nto_pos.orders

The following datasets store additional objects:

• aws_data
• us_data

Run ad-hoc queries

Using BigQuery Omni, you can run ad-hoc queries to analyze theData Cloud data through the subscribed dataset. The followingexample shows a simple query that queries the customers table fromData Cloud.

SELECTname__c,age__cFROM`listing_nto_john.nto_customers__dll`WHEREage>40LIMIT1000;

Run cross-cloud queries

Cross-cloud queries let you join any of the tables in theBigQuery Omni region and tables in the BigQueryregions. For more information about cross-cloud queries, see thisblogpost.In this example, we retrieve total sales for a customer namedjohn.

-- Get combined sales for a customer from both offline and online salesUSING(SELECTtotal_priceFROM`listing_nto_john.nto_orders__dll`WHEREcustomer_name='john'UNIONALLSELECTtotal_priceFROM`listing_nto_john.nto_orders__dll`WHEREcustomer_name='john')aSELECTSUM(total_price);

Cross Cloud Data Transfer through CTAS

You can use Create Table As Select (CTAS) to move data fromData Cloud tables in the BigQuery Omni region totheUS region.

-- Move all the orders for March to the US regionCREATEORREPLACETABLEus_data.online_orders_marchASSELECT*FROMlisting_nto_john.nto_orders__dllWHEREEXTRACT(MONTHFROMorder_time)=3

The destination table is a BigQuery managed table in theUSregion. This table can be joined with other tables. This operation incursAWS egress costs based on how much data is transferred.

Once the data is moved, you no longer need to pay egress fees for anyqueries that run in theonline_orders_march table.

Cross cloud materialized views

Cross Cloud Materialized Views(CCMV)transfer data from a BigQuery Omni region to anon-BigQuery Omni BigQuery region incrementally.Set up a new CCMV that transfers a summary of total sales from onlinetransactions and replicate that data into theUS region.

You can access CCMVs from Ads Data Hub and join it with otherAds Data Hub data. CCMVs act like regular BigQueryManaged Tables for most part.

Create a local materialized view

To create a local materialized view:

-- Create a local materialized view that keeps track of total sales by dayCREATEMATERIALIZEDVIEW`aws_data.total_sales`OPTIONS(enable_refresh=true,refresh_interval_minutes=60)ASSELECTEXTRACT(DAYFROMorder_time)ASdate,SUM(order_total)assalesFROM`listing_nto_john.nto_orders__dll`GROUPBY1;

Authorize the materialized view

You must authorize materialized views to create a CCMV. Youcan either authorize the view (aws_data.total_sales) or the dataset (aws_data). To authorize the materialized view:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. Open the source datasetlisting_nto_john.

3. ClickSharing, then clickAuthorize Datasets.

4. Enter the dataset name (in this caselisting_nto_john), then clickOk.

Create a replica materialized view

Create a new replica materialized view in theUS region. The materialized viewperiodically replicates whenever there is a source data change to keep thereplica up to date.

-- Create a replica MV in the us region.CREATEMATERIALIZEDVIEW`us_data.total_sales_replica`ASREPLICAOF`aws_data.total_sales`;

Run a query on a replica materialized view

The following example runs a query on a replica materialized view:

-- Find total sales for the current month for the dashboardSELECTEXTRACT(MONTHFROMCURRENT_DATE())asmonth,SUM(sales)FROMus_data.total_sales_replicaWHEREmonth=EXTRACT(MONTHFROMdate)GROUPBY1

Using Data Cloud data withINFORMATION_SCHEMA

Data Cloud datasets support BigQueryINFORMATION_SCHEMA views. The data inINFORMATION_SCHEMA views issynced regularly from Data Cloud and may be stale. TheSYNC_STATUS column in theTABLESandSCHEMATA views showsthe last completed sync time, any errors that preventBigQuery from providing fresh data, and anysteps that are required to fix the error.

INFORMATION_SCHEMA queries don't reflect datasets that have been recentlycreated before the initial sync.

Data Cloud datasets are subject to the samelimitations as otherlinked datasets, such as only being accessible inINFORMATION_SCHEMA indataset-scoped queries.

What's next

• Learn aboutBigQuery Omni.

• Learn aboutcross-cloud joins.

• Learn aboutmaterialized views.