Google Cloud Cortex Framework

About the Data Foundation for Google Cloud Cortex Framework

The Data Foundation for Google Cloud Cortex Framework is a set of analytical artifacts that can be automatically deployed together with reference architectures.

The current repository contains the analytical views and models that serve as a foundational data layer for the Google Cloud Cortex Framework in BigQuery. Here is the list of entity-relationship diagrams:

• SAP ECC (PDF)
• SAP S/4(PDF)
• Salesforce.com (PDF)
• Google Ads (PDF)
• CM360 enriched with DV360 (PDF)
• TikTok (PDF)

Quick demo setup

If you want to create a demo instance, with automatic generation of BigQuery datasets, automatic permission granting and test data, click this button:

Warning This demo deployment is not suitable for production environments.

Deployment for Development or Production environments

Clone the Data Foundation repository

We recommend using the Cloud Shell.

Note These steps will require the gcloud sdk (already installed in Cloud Shell).

1. Clone this repository with submodules (--recurse-submodules):

   git clone --recurse-submodules https://github.com/GoogleCloudPlatform/cortex-data-foundation

2. Navigate into the previously downloaded folder.

   cd cortex-data-foundation

   If this is not the first time you clone the repository, execute git pull --recurse-submodules to pull the latest changes. If you are already an expert in configuration and requirements, you can skip to the build command in section Execute deployment.

Deployment steps

These are the steps for deployment:

0. Prerequisites
1. Establish project and dataset structure
2. Establish integration mechanism
3. Configure Cloud Platform Components
4. Configure deployment
5. Execute deployment
6. Test, customize, prepare for upgrade
7. Next steps for reporting and add-ons

Prerequisites

Understand the Framework

A successful deployment depends on a good understanding of:

• Your company's business rules and requirements
• Functional understanding of the workload (e.g., SAP, Salesforce)
• Google Cloud fundamentals and products

Before continuing with this guide, make sure you are familiar with:

• Google Cloud Platform fundamentals
• How to navigate the Cloud Console, Cloud Shell and Cloud Shell Editor
• Fundamentals of BigQuery
• Fundamental concepts of Change Data Capture and dataset structures
• General navigation of Cloud Build
• Fundamentals of Identity and Access Management
• Fundamentals of Cloud Composer or Apache Airflow
• Fundamentals of Cloud Dataflow

Establish project and dataset structure

You will require at least one GCP project to host the BigQuery datasets and execute the deployment process.

This is where the deployment process will trigger Cloud Build runs. In the project structure, we refer to this as the Source Project. Each workload should have at least one CDC and one Reporting dataset (i.e., one CDC and one Reporting dataset for SAP, one CDC and one Reporting dataset for SFDC).

Note If you want to have separate sets of projects and datasets for each workload (e.g., one set of source and target projects for SAP and a different set of target and source projects for Salesforce), run separate deployments for each workload.

You will need to identify:

• Deploy SAP, Salesforce.com, marketing?: Decide whether you want to deploy models for all workloads at the same time or only one set of models.
• Source Google Cloud Project: Project where the source data is located, from which the data models will consume. This project is normally accessed by technical practitioners.
• Target Google Cloud Project: Project where the Data Foundation predefined data models will be deployed and accessed by end-users. This may or may not be different from the source project depending on your needs.
• Source BigQuery Dataset (Raw): BigQuery dataset where the source data is replicated to or where the test data will be created. The recommendation is to have separate datasets, one for each data source (e.g., one raw dataset for SAP and one raw dataset for Google Ads).
• CDC BigQuery Dataset: BigQuery dataset where the CDC processed data lands the latest available records. This may or may not be the same as the source dataset if the tool landing the data performs the CDC merge operation. Some workloads allow for field name mapping here. The recommendation is to have two separate CDC datasets, one for each source (i.e., one cdc dataset for SAP and one CDC dataset for Salesforce).
• Target BigQuery reporting dataset: BigQuery dataset where the Data Foundation predefined data models will be deployed. The recommendation is to have two separate reporting datasets, one for each source (i.e., one reporting dataset for SAP and one reporting dataset for Salesforce). This dataset is automatically created by the deployer if it does not exist.
• Target BigQuery machine learning dataset: BigQuery dataset where the BQML predefined models will be deployed. This dataset is automatically created by the deployer if it does not exist.
• Pre-processing K9 dataset: BigQuery dataset where cross-workload, reusable DAG components, such as time dimensions, can be deployed. The workloads will have a dependency on this dataset unless modified. This dataset is automatically created by the deployer if it does not exist.
• Post-processing K9 dataset: BigQuery dataset where cross-workload reporting (e.g., SAP + Google Ads Reporting (CATGAP)) and additional external source DAGs, (e.g., Weather or Google Trends ingestion) can be deployed. This dataset is automatically created by the deployer if it does not exist.

Alternatively, if you do not have a replication tool set up or do not wish to use the replicated data, the deployment process can generate test tables and fake data for you. You will still need to create and identify the CDC and Raw datasets ahead of time.

These parameters will be different for each workload depending on the integration mechanism.

Establish integration mechanism

Each workload has different ways to integrate with BigQuery. This section explains the options for each source.

Note: Using test data? You can skip this section.

Integration options for SAP ECC or SAP S/4HANA

Loading SAP data into BigQuery

Prerequisites for SAP replication

• Cortex Data Foundation expects SAP tables to be replicated with the same field names and types as they are created in SAP.
• As long as the tables are replicated with the same format, names of fields and granularity as in the source, there is no requirement to use a specific replication tool.
• Table names need to be created in BigQuery in lowercase.
• The list of tables used by SAP models are available and configurable in the CDC setting.yaml. If a table is not present during deployment, the models depending on it will fail. Other models will deploy successfully.
• If in doubt about a conversion option, we recommend following the default table mapping.
• DD03L for SAP metadata: If you are not planning on deploying test data, and if you are planning on generating CDC DAG scripts during deployment, make sure table DD03L is replicated from SAP in the source project. This table contains metadata about tables, like the list of keys, and is needed for the CDC generator and dependency resolver to work. This table will also allow you to add tables not currently covered by the model to generate CDC scripts, like custom or Z tables.

Note: What happens if I have minor differences in a table name? Because SAP systems may have minor variations due to versions or add-on and append structures into tables, or because some replication tools may have slightly different handling of special characters, some views may fail not finding a field. We recommend executing the deployment with turboMode : false to spot most failures in one go. Examples of this are:

• Fields starting with _ (e.g., _DATAAGING) have their _ removed
• Fields cannot start with / in BigQuery

In this case, you can adapt the failing view to select the field as it is landed by your replication tool of choice.

Change Data Capture (CDC) processing

There are two main ways for replication tools to load records from SAP:

• Append-always: Insert every change in a record with a timestamp and an operation flag (Insert, Update, Delete), so the last version can be identified.
• Update when landing (merge or upsert): This creates an updated version of a record on landing in the change data capture processed. It performs the CDC operation in BigQuery.

Cortex Data Foundation supports both modes (append-always or update when landing). For append-always, we provide CDC processing templates.

Note Some functionality will need to be commented out for Update on landing. For example, OneTouchOrder.sql and all its dependent queries. The functionality can be replaced with tables like CDPOS.

Return to top of Section

Configure CDC templates for tools replicating in append-always mode

Configure CDC for SAP

Note: We strongly recommend configuring this file according to your needs. Some default frequencies may result in unnecessary cost if the business does not require such level of data freshness.

If using a tool that runs in append-always mode, Cortex Data Foundation provides CDC templates to automate the updates and create a latest version of the truth or digital twin in the CDC processed dataset.

You can use the configuration in the file setting.yaml if you need to generate change-data capture processing scripts. See the Appendix - Setting up CDC Processing for options. For test data, you can leave this file as a default.

Make any changes to the DAG templates as required by your instance of Airflow or Cloud Composer. You will find more information in the Appendix - Gathering Cloud Composer settings.

This module is optional. If you want to add/process tables individually after deployment, you can modify the setting.yaml file to process only the tables you need and re-execute the specific module calling src/SAP_CDC/cloudbuild.cdc.yaml directly.

Performance optimization for CDC Tables

For certain CDC datasets, you may want to take advantages of BigQuery table partitioning, table clustering or both. This choice depends on many factors - the size and data of the table, columns available in the table, and your need for real time data with views vs data materialized as tables. By default, CDC settings do not apply table partitioning or table clustering - the choice is yours to configure it based on what works best for you.

To create tables with partitions and/or clusters, update the CDC setting.yaml file with relevant configurations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

NOTE:

1. This feature only applies when a dataset in setting.yaml is configured for replication as a table (e.g. load_frequency = "@daily") and not defined as a view (load_frequency = "RUNTIME").
2. A table can be both - a partitioned table as well as a clustered table.

Important ⚠️: If you are using a replication tool that allows partitions in the raw dataset, like the BigQuery Connector for SAP, we recommend setting time-based partitions in the raw tables. The type of partition will work better if it matches the frequency for CDC DAGs in the setting.yaml configuration.

You can read more about partitioning and clustering for SAP here.

Return to top of Section

Integration options for Salesforce

Loading Salesforce data into BigQuery

We provide a replication solution based on Python scripts scheduled in Apache Airflow and Salesforce Bulk API 2.0. These Python scripts can be adapted and scheduled in your tool of choice.

There are three sets of processing options for data integration:

• API call and load into Raw datasets, updating existing records if needed
• Source-to-target structure mapping views
• CDC processing scripts

If you have datasets already loaded through a different tool in append-always mode, the CDC processing scripts contain mapping files to map the schema of the tables as generated by your tool into the names and data types of the structure required by the reporting views in Cortex Data Foundation. You can also add custom fields in the schema definition so they are incorporated in the CDC processing.

Note: For CDC scripts to work, the Id for each API (e.g., Account Id) and the SystemModStamp need to be present in the source table. These fields should either have their original name (Id, SystemModstamp) or be mapped respectively to the {object_name} Id and SystemModstamp.

For example, the source table with data of Account object should have original Id and SystemModstamp fields. If these fields have different names, then src/SFDC/src/table_schema/accounts.csv file must be updated with id field's name mapped to AccountId and whatever system modification timestamp field mapped to SystemModstamp.

If you already have the replication and CDC working for Salesforce APIs and only need the mapping, you can edit the mapping files to generate views that translate the structure generated by the integration tool to the structure expected by Cortex Data Foundation reporting views.

Salesforce data requirements

• The structure of the source tables follows snake_case naming in plural, i.e., some_objects. The columns have the same data types as how Salesforce represents them internally. Some fields have been renamed for better readability in the reporting layer.
• Any required tables that did not exist within the raw dataset will be created as empty tables during deployment. This is to ensure the CDC deployment step runs correctly.
• If required, for CDC scripts to work, the Id for each API (e.g., Account Id) and the SystemModStamp need to be present in the source table. The provided Raw processing scripts fetch these fields automatically from the APIs and update the target replication table.
• The provided Raw processing scripts do not require additional change data capture processing. This behavior is set during deployment by default.

Source tables for Currency Conversion in Salesforce

The currency conversion functionality of Salesforce relies on the existence of the objects CurrencyTypes and DatedConversionRates within the source Salesforce system, which are available only if Advanced Currency Management is enabled. If not, you may want to remove relevant entries from src/SFDC/config/ingestion_settings.yaml to avoid running into errors during Salesforce to Raw extraction.

If these tables are not available, we will automatically create empty placeholder tables for them during deployment to avoid break Reporting logic.

Configure Salesforce integration with Cortex provided ingestion templates

Configure API integration and CDC for Salesforce

Following a principle of openness, customers are free to use the provided replication scripts for Salesforce or a data replication tool of their choice as long as data meets the same structure and level of aggregation as provided by the Salesforce APIs. If you are using another tool for replication, this tool can either append updates as new records (append always pattern) or update existing records with changes when landing the data in BigQuery. If the tool does not update the records and replicates any changes as new records into a target (Raw) table, Cortex Data Foundation provides the option to create change-data-capture processing scripts.

To ensure the names of tables, names of fields, and data types are consistent with the structures expected by Cortex regardless of the replication tool, you can modify the mapping configuration to map your replication tool or existing schemata. This will generate mapping views compatible with the structure expected by Cortex Data Foundation.

You can use the configuration in setting.yaml to configure the generation of scripts to call the salesforce APIs and replicate the data into the Raw dataset (section salesforce_to_raw_tables) and the generation of scripts to process changes incoming into the Raw dataset and into the CDC processed dataset (section raw_to_cdc_tables).

By default, the scripts provided to read from APIs update changes into the Raw dataset, so CDC processing scripts are not required, and mapping views to align the source schema to the expected schema are created instead.

The generation of CDC processing scripts is not executed if SFDC.createMappingViews in the config.json file remains true (default behavior). If CDC scripts are required, set SFDC.createMappingViews to false. This second step also allows for mapping between the source schemata into the required schemata as required by Cortex Data Foundation.

The following example of a setting.yaml configuration file illustrates the generation of mapping views when a replication tool updates the data directly into the replicated dataset, as illustrated in option 3 (i.e., no CDC is required, only re-mapping of tables and field names). Since no CDC is required, this option executes as long as the parameter SFDC.createMappingViews in the config.json file remains true.

In this example, removing the configuration for a base table or all of them from the sections will skip the generation of DAGs of that base table or the entire section, as illustrated for salesforce_to_raw_tables. For this scenario, setting the parameter deployCDC : False has the same effect, as no CDC processing scripts need to be generated.

The following example illustrates the mapping of the field unicornId as landed by a replication tool to the name and type expected by Cortex Data Foundation, AccountId as a String.

Make any changes to the DAG templates for CDC or for Raw as required by your instance of Airflow or Cloud Composer. You will find more information in the Appendix - Gathering Cloud Composer settings.

If you do not need any DAGs for Raw data generation from API calls or CDC processing, set parameter deployCDC to false. Alternatively, you can remove the contents of the sections in setting.yaml. If data structures are known to be consistent with those expected by Cortex Data Foundation, you can skip the generation of mapping views with parameter SFDC.createMappingViews set to false.

Return to top of Section

Integration options for Marketing workload

The following data sources are available through the Marketing workload:

• Google Ads
• Campaign Manager 360 (CM360)
• TikTok
• LiveRamp

For all three data sources, we use Dataflow pipelines to obtain data from upstream systems. Cloud Composer is used to schedule and monitor these Dataflow pipelines.

Configure integration for Google Ads

Cortex Data Foundation integrates with Google Ads in the following way:

1. Source to Raw layer: Ingest data from Google Ads to raw dataset using Google Ads APIs. This is achieved by using Dataflow pipelines running in Cloud Composer DAGs.
2. Raw layer to CDC layer: Apply CDC processing on raw dataset and store the output in CDC dataset. This is accomplished by Cloud Composer DAGs running BigQuery sqls.
3. CDC layer to Reporting layer: Create final reporting tables from CDC tables in the Reporting dataset. This is accomplished by either creating runtime views on top of CDC tables or running Cloud Composer DAGs for materialized data in BigQuery tables - depending on how it's configured.

Google Ads API

The ingestion templates for Google Ads use the Google Ads API to retrieve reporting attributes and metrics. The current templates use version 13.

API Limits:

Relevant limits fo the API (as of this release):

• Basic access operations/day: 15000 (paginated requests containing valid next_page_token are not counted).
• Max page size: 10000 rows per page.
• Recommended default parameters: Page size = 10000 rows per page. For more details on these API, please consult documents referred above.

Configurations

Following configs are required to be in place for Cortex to successfully bring data from Ads into the Cortex reporting layer.

Configure Google Ads Account Authentication

1. Go to Google Cloud Console -> API & Services -> Credentials and create a new credential under “OAuth Client ID”.

   Application type: “Web Application”
   Name: Give it a name (e.g. “Cortex Authentication Client”).
   Authorized redirect URIs: http://127.0.0.1
   

   For more information, see Using OAuth 2.0 to Access Google APIs
2. Once the above credential is configured, note the values for Client ID and Client secret - it will be used later.
3. Generate refresh token using "How to refresh token".
4. Now create a secret using Google Cloud Secret Manager (Security -> Secret Manager) with name “cortex-framework-google-ads-yaml” using the following format:

   {"developer_token": "developer_token_value", "refresh_token": "refresh_token_value", "client_id": "client_id_value", "client_secret": "client_secret_value", "use_proto_plus": False, "login_customer_id": "ads_customer_id"}
   

   For the values:
   • developer_token value : Available in Google Ads account
   • refresh_token value: From the step #3 above.
   • login_customer_id value: Available in Google Ads account (Please note that this value should not contain dashes or hyphens)
   • client_id value: From the OAuth setup in step #2 above.
   • client_secret value: From the configurations in step #2 above.

Cloud Composer Connections

Create the following connections in Cloud Composer / Airflow:

Connection Name	Purpose
googleads_raw_dataflow	For Google Ads API -> Bigquery Raw Dataset
googleads_cdc	For Raw dataset -> CDC dataset transfer
googleads_reporting	For CDC dataset -> Reporting dataset transfer

Cloud Composer Service Account permissions

The service account used in Cloud Composer (as configured in the googleads_raw_dataflow connection above) needs Dataflow related permissions. For more, please check Dataflow documentation.

Ingestion settings

File src/GoogleAds/config/ingestion_settings.yaml contains further settings that controls "Source to Raw" and "Raw to CDC" data pipelines.

source_to_raw_tables:

This section has entries that control which entities are fetched by APIs and how. Each entry corresponds with one Google Ads entity. Based on this config, Cortex creates Airflow DAGs that run Dataflow pipelines to fetch data using Google Ads APIs.

Parameters for each entry:

Parameter	Description
load_frequency	How frequently a DAG for this entity will run to fetch data from Google Ads. (See Airflow documentation for details on possible values.)
api_name	API Resource Name (e.g. customer for https://developers.google.com/google-ads/api/fields/v13/customer)
table_name	Table in Raw dataset where the fetched data will be stored (e.g. customer)
schema_file	Schema file in src/table_schema directory that maps API response fields to destination table's column names.
key	Columns (separated by comma) that form a unique record for this table.
is_metrics_table	Indicates if a given entry is for a metric entity (in Google Ads API). System treats such tables a bit differently due to aggregated nature of such tables.
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

raw_to_cdc_tables:

This section has entries that control how data is moved from Raw tables to CDC tables. Each entry corresponds with a raw table (which in turn corresponds with Google Ads API entity as mentioned above.)

Parameters for each entry:

Parameter	Description
table_name	Table in CDC dataset where the raw data after CDC transformation will be stored (e.g. customer)
raw_table	Table on which raw data has been replicated
key	Columns (separated by comma) that form a unique record for this table.
load_frequency	How frequently a DAG for this entity will run to populate CDC table. (See Airflow documentation for details on possible values.)
schema_file	Schema file in src/table_schema directory that maps raw columns to CDC columns and data type of the CDC column. (NOTE: This is the same schema file that's referred to in earlier section.)
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

Reporting settings

You can configure and control how Cortex generates data for the Google Ads final reporting layer using reporting settings file (src/GoogleAds/config/reporting_settings.yaml). This file controls how reporting layer BQ objects (tables, views, functions or stored procs are generated.)

For more details, please see Customizing reporting_settings file configuration section.

Return to top of Section

Configure integration for CM360

Cortex Data Foundation integrates with CM360 in the following way:

1. Source to Raw layer: Ingest data from CM360 to raw dataset using CM360 Data Transfer V2 files. This is achieved by using Dataflow pipelines running in Cloud Composer DAGs.
2. Raw layer to CDC layer: Apply CDC process on raw dataset and store the output in CDC dataset. This is accomplished by Cloud Composer DAGs running BigQuery sqls.
3. CDC layer to Reporting layer: Create final reporting tables from CDC tables in the Reporting dataset. This is accomplished by either creating runtime views on top of CDC tables or running Cloud Composer DAGs for materialized data in BigQuery tables - depending on how it's configured.

CM360 Data Transfer V2

For CM360, we use files generated by Data Transfer V2.0 as source of truth. In order to use CM360 with Cortex, set up Data Transfer process by following the documentation.

Configurations

Following configs are required to be in place for Cortex to successfully bring data from CM360 system into Cortex Reporting layer.

DTv2 Files GCS Bucket

Once the Data Transfer V2 is set up, obtain the GCS Bucket name, and make sure the GCS bucket and files under the bucket are readable by the service account running DAGs in Cloud Composer.

Set up Cloud Composer Connections

Create following connections in Cloud Composer / Airflow:

Connection Name	Purpose
cm360_raw_dataflow	For CM360 DTv2 files -> Bigquery Raw Dataset
cm360_cdc_bq	For Raw dataset -> CDC dataset transfer
cm360_reporting_bq	For CDC dataset -> Reporting dataset transfer

Cloud Composer Service Account permissions

The service account used in Cloud Composer (as configured in the cm360_raw_dataflow connection above) needs Dataflow related permissions. For more, please check Dataflow documentation.

Ingestion settings

File src/CM360/config/ingestion_settings.yaml contains further settings that control "Source to Raw" and "Raw to CDC" data pipelines.

source_to_raw_tables:

This section has entries that control which files from DTv2 are processed and how. Each entry corresponds with files associated with one entity. Based on this config, Cortex creates Airflow DAGs that run Dataflow pipelines to process data from the DTv2 files.

Parameters for each entry:

Parameter	Description
base_table	Table in Raw dataset where the data for an entity (like 'Clicks' data) will be stored.
load_frequency	How frequently a DAG for this entity will run to process data from DTv2 files. (See Airflow documentation for details on possible values.)
file_pattern	Regex based file name patterns that corresponds to an entity.
schema_file	Schema file in src/table_schema directory that maps DTv2 fields to destination table's column names and data types.
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

raw_to_cdc_tables:

This section has entries that control how data is moved from Raw tables to CDC tables. Each entry corresponds with a raw table (which in turn corresponds with DTv2 entity as mentioned above.)

Parameters for each entry:

Parameter	Description
base_table	Table in CDC dataset where the raw data after CDC transformation will be stored (e.g. customer)
load_frequency	How frequently a DAG for this entity will run to populate CDC table. (See Airflow documentation for details on possible values.)
row_identifiers	List of columns (separated by comma) that forms a unique record for this table.
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

Reporting settings

You can configure and control how Cortex generates data for the CM360 final reporting layer using the reporting settings file (src/CM360/config/reporting_settings.yaml). This file controls how reporting layer BQ objects (tables, views, functions or stored procedures are generated.)

For more details, please see Customizing reporting_settings file configuration section.

Return to top of Section

Configure integration for TikTok

Cortex Data Foundation integrates with TikTok in the following way:

1. Source to Raw layer: Ingest data from TikTok to raw dataset using TikTok Reporting APIs. This is achieved by using Dataflow pipelines running in Cloud Composer DAGs.
2. Raw layer to CDC layer: Apply CDC process on raw dataset and store the output in CDC dataset. This is accomplished by Cloud Composer DAGs running BigQuery sqls.
3. CDC layer to Reporting layer: Create final reporting tables from CDC tables in the Reporting dataset. This is accomplished by either creating runtime views on top of CDC tables or running Cloud Composer DAGs for materialized data in BigQuery tables - depending on how it's configured.

TikTok Reporting APIs

For TikTok, Cortex uses TikTok Reporting APIs as source of truth. The current version is v1.3.

Cortex uses Synchronous mode, and calls Basic Reporting APIs to obtain Ad and Ad Group performance data.

Configurations

Following configs are required to for Cortex to successfully bring data from TikTok into Cortex Reporting layer.

Configure TikTok Account and Account Authentication

1. Set up a TikTok Developer Account, if you don't have it already.

2. Create an app for Cortex integration if you need to, as guided here. Make sure you have selected the following two in the scopes for the app:

   • Ad Account Management/Ad Account Information
   • Reporting/All

3. Get app id, secret and long term access token as described in the TikTok guide, and store them respectively in Google Cloud Secret Manager with the following names: cortex_tiktok_app_id cortex_tiktok_app_secret cortex_tiktok_access_token

Set up Cloud Composer Connections

Create following connections in Cloud Composer / Airflow:

Connection Name	Purpose
tiktok_raw_dataflow	For TikTok API -> Bigquery Raw Dataset
tiktok_cdc_bq	For Raw dataset -> CDC dataset transfer
tiktok_reporting_bq	For CDC dataset -> Reporting dataset transfer

Cloud Composer Service Account permissions

The service account used in Cloud Composer (as configured in the tiktok_raw_dataflow connection above) needs Dataflow related permissions. For more, please check Dataflow documentation. Also, the same service account should also have Secret Manager Accessor access.

Ingestion settings

File src/TikTok/config/ingestion_settings.yaml contains further settings that control "Source to Raw" and "Raw to CDC" data pipelines.

source_to_raw_tables:

This section has entries that control how data from TikTok is fetched and where they end up in raw dataset. Each entry corresponds with one raw table that will have data fetched from TikTok API for that entity. Based on this config, Cortex creates Airflow DAGs that run Dataflow pipelines to process data from TikTok APIs.

Parameters for each entry:

Parameter	Description
base_table	Table in Raw dataset where the data for an entity (like 'Ad' data) will be stored.
load_frequency	How frequently a DAG for this entity will run to process data from DTv2 files. (See Airflow documentation for details on possible values.)
schema_file	Schema file in src/table_schema directory that maps API response fields to destination table's column names.
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

raw_to_cdc_tables:

This section has entries that control how data moves from raw tables to CDC tables. Each entry corresponds with a CDC table (which in turn corresponds with an entity mentioned above for the source_to_raw_tables.)

Parameters for each entry:

Parameter	Description
base_table	Table in CDC dataset where the raw data after CDC transformation will be stored (e.g. auction_ad_performance)
load_frequency	How frequently a DAG for this entity will run to populate CDC table. (See Airflow documentation for details on possible values.)
row_identifiers	List of columns (separated by comma) that forms a unique record for this table.
partition_details	(Optional) If you want this table to be partitioned for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
cluster_details	(Optional) If you want this table to be clustered for performance considerations. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

Reporting settings

You can configure and control how Cortex generates data for the TikTok final reporting layer using the reporting settings file (src/TikTok/config/reporting_settings.yaml). This file controls how reporting layer BQ objects (tables, views, functions or stored procedures are generated.)

For more details, please see Customizing reporting_settings file configuration section.

Configure integration for LiveRamp

Cortex Data Foundation enables Identity Resolution by integrating with LiveRamp.

Cortex frameworks can perform RampId lookup for a known audience / customer segment (e.g. ‘high-value customers’, ‘cat lovers’) from CRM (or other) systems.

Using PII information (Email, Phone, Name, Phone number etc), the LiveRamp API returns a RampID, which is an identifier that allows businesses to identify and consolidate records across multiple systems - such as the audience of different campaigns.

LiveRamp APIs

LiveRamp Identity Resolution Retrieval API allows businesses to programmatically resolve PII data to individuals. Cortex uses LiveRamp's Lookup Endpoint by sending hashed PII data over the API call.

Input and Output tables

After deploying Cortex, system will create two BQ tables in relevant dataset provided in config.json:

1. rampid_lookup_input table

   This table is input for the RampId Lookup process.

   Column	Data Type	Description	Example	Is Primary Key?
   id	STRING	Unique Id of this record.	"123"	Yes
   segment_name	STRING	Name of audience/CRM/Customer segment.	"High Value"	No
   source_system_name	STRING	Source system where the record came from.	"Salesforce"	No
   name	STRING	Customer Name	"John Doe"	No
   email	STRING	Customer Email	"[email protected]"	No
   phone_number	STRING	Customer Phone	"1234567890"	No
   postal_code	STRING	Customer postal code	"12345"	No
   is_processed	BOOL	Indicates if a record is already processed. For new records, populate this with FALSE. System will update this to TRUE once it's processed.	FALSE	No
   load_timestamp	TIMESTAMP	Timestamp when the record was inserted in the system. This is purely for audit purpose.	"2020-01-01 00:00:00 UTC"	No
   processed_timestamp	TIMESTAMP	Timestamp when system performed API Lookup for this record. This is always populated by system.	"2020-01-01 00:00:00 UTC"	No

   Note that id needs to be unique.

2. rampid_lookup table

This is output table containing RampIds for each segment in the input record. Please note that LiveRamp by design does not allow to map RampId to an individual record.

Column	Data Type	Description
segment_name	STRING	Segment name from input table.
ramp_id	STRING	LiveRamp RampId
recordstamp	TIMESTAMP	Timestamp when this rampid lookup was performed.

Notes on the lookup tables

1. rampid_lookup_input table needs to be populated on periodic basis (based on your business needs) with your customer's PII details like Name, Email etc. Cortex does not provide an automated way to do so. But Cortex provides a sample script ddls/samples/populate_rampid_lookup_input.sql that shows how you could populate this table using data in your Salesforce system already deployed with Cortex. You can use that as guide too in case your data comes from other system.

2. Also, ensure you do not have duplicates in the rampid_lookup_input table (as in, same individual is present multiple times with same PII information, even if their id may be different). Cortex Lookup DAG will fail if a segment contains too many duplicate entries. This is enforced by LiveRamp APIs.

3. LiveRamp RampIds can change over period of time, for the same individual. This means, you need to perform a fresh lookup for already processed data time to time. Cortex provides a sample script ddls/samples/clean_up_segment_matching.sql that shows how you can do this at a segment level. This way, you can reset a whole segment, and system will perform a lookup for that segment and give you up to date RampIds.

4. rampid_lookup - the output table - may contain slightly less number of records compared to the input table. This is by design, as Cortex tries to dedup the input records using PII details to ensure LiveRamp API lookup does not fail.

Configurations

Configure LiveRamp Authentication

1. Contact LiveRamp to obtain authentication credentials. This should include two fields: Client Id, Client Secret.
2. Create a secret using Google Cloud Secret Manager with name "cortex-framework-liveramp", and use the following as value:

{
  'client_id':'<your client id obtained above>',
  'client_secret':'<your client secret obtained above>',
  'grant_type':'client_credentials'
}

Set up Cloud Composer Connection

Create following connections in Cloud Composer / Airflow:

Connection Name	Purpose
liveramp_cdc_bq	For LiveRamp API -> CDC dataset transfer

config.ini

This config file controls some behavior of the Cloud Composer DAG, as well as how LiveRamp APIs are consumed.

Configure LiveRamp/src/pipelines/config.ini based on your needs. While these parameters are already described in the file, pay attention to liveramp_api_base_url. This parameter by default points to LiveRamp's production API url, for testing purpose, you may need to point it to the staging version, depending on your setup.

Return to top of Section

Return to top of Section

Configure K9 deployments

The K9 deployer is responsible for ingestion, processing and modeling of components that are reusable across different data sources. For example, the time dimension is reusable across all data sources where tables may need to slice and dice analytical results based on a Gregorian calendar.

External data sources that can be combined across different workloads to gain enriched insights and are also reusable are part of the K9 deployer. For example, Weather data or Google Trends ingestion and processing can be combined across SAP, Salesforce and Marketing. The CATGAP is an example of a combination of Google Ads and SAP.

The pre-processing K9 step executes before all workloads start their deployment, so the reusable models are available during their deployment. Conversely, the post-processing K9 executes after all modules have deployed their reporting models so the cross-workload reporting or augmenting models find their dependencies within each reporting dataset.

ℹ️ Fun Fact: The K9 receives its name because it's where the DAGs (/dɑɡz/) live.

The DAGs and models to be generated can be configured in K9 configuration file.

If you are using submodules independently (e.g., only cortex-reporting) or a deployment framework like Dataform or dbt, we recommend porting the pre-k9 DAG execution into your scheduler of choice. The Reporting views that tap into reusable models select from the separate K9 datasets.

Configure Google Cloud Platform components

Enable Required Components

The following Google Cloud components are required:

• Google Cloud Project
• BigQuery instance and datasets
• Service Account with Impersonation rights
• Cloud Storage Buckets
• Cloud Build API
• Cloud Resource Manager API
• Optional components:
  • Cloud Composer for change data capture (CDC) processing (SAP and Salesforce), hierarchy flattening (SAP only), and data replication (Salesforce only) through Directed Acyclic Graphs (DAGs). You can find how to set up an instance of Cloud Composer in the documentation.
  • Looker **(optional, connects to reporting templates. Requires manual setup) **
  • Analytics Hub linked datasets (optional) are currently used for some external sources, such as the Weather DAG. You may choose to fill this structure with any other available source of your choice for advanced scenarios.
  • Dataflow: Integration tool for Google Ads.
  • Dataplex: Used for Data Mesh

From the Cloud Shell, you can enable Google Cloud Services using the gcloud command line interface in your Google Cloud project.

Replace the <SOURCE_PROJECT> placeholder with your source project. Copy and paste the following command into the cloud shell:

gcloud config set project <SOURCE_PROJECT>

gcloud services enable bigquery.googleapis.com \
                       cloudbuild.googleapis.com \
                       composer.googleapis.com \
                       storage-component.googleapis.com \
                       cloudresourcemanager.googleapis.com \
                       dataflow.googleapis.com

You should get a success message:

Grant permissions to the executing user

If an individual is executing the deployment with their own account, they will need, at minimum, the following permissions in the project where Cloud Build will be triggered:

• Service Usage Consumer
• Storage Object Viewer for the Cloud Build default bucket or bucket for logs
• Object Writer to the output buckets
• Cloud Build Editor
• Project Viewer or Storage Object Viewer

These permissions may vary depending on the setup of the project. Consider the following documentation if you run into errors:

• Permissions to run Cloud Build
• Permissions to storage for the Build Account
• Permissions for the Cloud Build service account
• Viewing logs from Builds

Configure the Cloud Build account

In the source project, navigate to the Cloud Build and locate the account that will execute the deployment process.

Locate the build account in IAM (make sure it says cloudbuild):

Grant the following permissions to the Cloud Build service account in both the source and target projects if they are different:

• BigQuery Data Editor
• BigQuery Job User

[Optional] If changing the default values for Data Mesh in config/config.json to implement features beyond descriptions, the executing account (Cloud Build service account) will need to have the following permissions:

• Policy Tag Admin
• Data Catalog TagTemplate Owner
• Dataplex Editor
• BigQuery Data Owner

[Optional] Create a Service Account for deployment with impersonation

The deployment can run through a service account with impersonation rights, by adding the flag --impersonate-service-account. This service account will trigger a Cloud Build job that will in turn run specific steps through the Cloud Build service account. This allows a user to trigger a deployment process without direct access to the resources.

The impersonation rights to the new, triggering service account need to be granted to the person running the command.

Navigate to the Google Cloud Platform Console and follow the steps to create a service account with the following role:

• Cloud Build Service Account

This role can be applied during the creation of the service account:

Authorize the ID of user who will be running the deployment to impersonate the service account that was created in the previous step. Authorize your own ID so you can run an initial check as well.

Once the service account has been created, navigate to the IAM Service Account administration, click on the service account, and into the Permissions tab.

Click Grant Access, type in the ID of the user who will execute the deployment and has impersonation rights, and assign the following role:

• Service Account Token Creator

Alternatively, you can complete this step from the Cloud Shell:

gcloud iam service-accounts create <SERVICE ACCOUNT> \
    --description="Service account for Cortex deployment" \
    --display-name="my-cortex-service-account"

gcloud projects add-iam-policy-binding <SOURCE_PROJECT> \
--member="serviceAccount:<SERVICE ACCOUNT>@<SOURCE_PROJECT>.iam.gserviceaccount.com" \
--role="roles/cloudbuild.builds.editor"

gcloud iam service-accounts add-iam-policy-binding <SERVICE ACCOUNT>\
  --member="user:<EXECUTING USER EMAIL>" \
  --role="roles/iam.serviceAccountTokenCreator"

Create a Storage bucket for storing DAG related files

A storage bucket will be required to store processing DAG scripts and other temporary files generated during deployment. These scripts will have to be manually moved into a Cloud Composer or Apache Airflow instance after deployment.

Navigate to Cloud Storage and create a bucket in the same region as your BigQuery datasets.

Alternatively, you can use the following command to create a bucket from the Cloud Shell:

gsutil mb -l <REGION/MULTI-REGION> gs://<BUCKET NAME>

Navigate to the Permissions tab. Grant Storage Object Creator to the user executing the Build command or to the Service account you created for impersonation.

Create a Storage bucket for logs

You can create a specific bucket for the Cloud Build process to store the logs. This is useful if you want to restrict data that may be stored in logs to a specific region. Create a GCS bucket with uniform access control, in the same region where the deployment will run.

Alternatively, here is the command line to create this bucket:

gsutil mb -l <REGION/MULTI-REGION> gs://<BUCKET NAME>

You will need to grant Object Admin permissions to the Cloud Build service account.

Configure Data Mesh

The default configuration in config/config.json for Data Mesh will add the descriptions for all assets, but will not create any other structures or policies.

Enabling any other options (e.g., deployLakes, deployCatalog, deployACLs) requires the configuration of permissions for the Cloud Build service account and the necessary configuration of the yaml files.

Here is a high-level diagram of the available options:

You will find detailed instructions and examples to configure Data Mesh in the documentation.

Configure Deployment

The behavior of the deployment is controlled by the configuration file config.json.

The file contains global configuration and configuration specific to each workload.

Open the file in config/config.json. From the Cloud Shell:

edit config/config.json

Global Deployment Configuration

Consider your target deployment:

Parameter	Meaning	Default Value	Description
testData	Deploy Test Data	true	Project where the source dataset is and the build will run.
deploySAP	Deploy SAP	true	Execute the deployment for SAP workload (ECC or S/4HANA).
deploySFDC	Deploy Salesforce	true	Execute the deployment for Salesforce workload.
deployMarketing	Deploy Marketing	true	Execute the deployment for Marketing sources (Google Ads, CM360 and/or TikTok).
deployDataMesh	Deploy Data Mesh	true	Execute the deployment for Data Mesh, see the Data Mesh README for more info.
turboMode	Deploy in Turbo mode	true	Execute all views builds as a step in the same Cloud Build process, in parallel for a faster deployment. If set to false, each reporting view is generated in its own sequential build step. We recommend only setting it to true when using test data or after any mismatch between reporting columns and the source data have been resolved.
projectIdSource	Source Project ID	-	Project where the source dataset is and the build will run.
projectIdTarget	Target Project ID	-	Target project for user-facing datasets (reporting and ML datasets).
targetBucket	Target Bucket to storage generated DAG scripts	-	Bucket created previously where DAGs (and Dataflow temp files) will be generated. Avoid using the actual Airflow bucket.
location	Location or Region	"US"	Location where the BigQuery dataset and GCS buckets are. > Note: See restrictions listed under BigQuery dataset locations.
languages	Filtering languages	[ "E", "S" ]	If not using test data, enter a single language (e.g., [ "E" ]) or multiple languages (e.g., [ "E", "S" ]) as relevant to your business. These values are used to replace placeholders in SQL in analytics models where available (SAP only for now - see the ERD).
currencies	Filtering currencies	[ "USD" ]	If not using test data, enter a single currency (e.g., [ "USD" ]) or multiple currencies (e.g., [ "USD", "CAD" ]) as relevant to your business. These values are used to replace placeholders in SQL in analytics models where available (SAP only for now - see the ERD).
testDataProject	Source for test harness	kittycorn-public	Source of the test data for demo deployments. Applies when testData is true. > Note: Unless you have your own test harness, do not change this value.
k9.datasets.processing	K9 datasets - Processing	"K9_PROCESSING"	Execute cross-workload templates (e.g., date dimension) as defined in the K9 configuration file. These templates are normally required by the downstream workloads.
k9.datasets.reporting	K9 datasets - Reporting	"K9_REPORTING"	Execute cross-workload templates and external data sources (e.g., Weather) as defined in the K9 configuration file. Commented out by default.
DataMesh.deployDescriptions	Data Mesh - Asset descriptions	true	Deploy BigQuery asset schema descriptions.
DataMesh.deployLakes	Data Mesh - Lakes & Zones	false	Deploy Dataplex Lakes & Zones that organize tables by processing layer, requires configuration before enabling.
DataMesh.deployCatalog	Data Mesh - Catalog Tags & Templates	false	Deploy Data Catalog Tags that allow custom metadata on BigQuery assets or fields, requires configuration before enabling.
DataMesh.deployACLs	Data Mesh - Access Control	false	Deploy asset, row, or column level access control on BigQuery assets, requires configuration before enabling.

Workload-specific configuration

The following sections are specific to each workload. You do not need to configure them if the deployment parameter (e.g., deploySAP or deployMarketing) for the workload is set to False.

Deployment Configuration for SAP

Parameter	Meaning	Default Value	Description
SAP.deployCDC	Deploy CDC	true	Generate CDC processing scripts to run as DAGs in Cloud Composer.
SAP.datasets.raw	Raw landing dataset	-	Used by the CDC process, this is where the replication tool lands the data from SAP. If using test data, create an empty dataset.
SAP.datasets.cdc	CDC Processed Dataset	-	Dataset that works as a source for the reporting views, and target for the records processed DAGs. If using test data, create an empty dataset.
SAP.datasets.reporting	Reporting Dataset SAP	"REPORTING"	Name of the dataset that is accessible to end users for reporting, where views and user-facing tables are deployed.
SAP.datasets.ml	ML dataset	"ML_MODELS"	Name of the dataset that stages results of Machine Learning algorithms or BQML models.
SAP.SQLFlavor	SQL flavor for source system	"ecc"	s4 or ecc. For test data, keep the default value (ecc). For Demand Sensing, only ecc test data is provided at this time.
SAP.mandt	Mandant or Client	"100"	Default mandant or client for SAP. For test data, keep the default value (100). For Demand Sensing, use 900.

Note: While there is not a minimum version of SAP that is required, the ECC models have been developed on the current earliest supported version of SAP ECC. Differences in fields between our system and other systems are expected, regardless of the version.

Return to top of section

Deployment Configuration for Salesforce

Parameter	Meaning	Default Value	Description
SFDC.deployCDC	Deploy CDC	true	Generate CDC processing scripts to run as DAGs in Cloud Composer. See the documentation for different ingestion options for Salesforce.
SFDC.createMappingViews	Create mapping views	true	The provided DAGs to fetch new records from the Salesforce APIs update records on landing. This value set to true will generate views in the CDC processed dataset to surface tables with the "latest version of the truth" from the Raw dataset. If false and SFDC.deployCDC is true, DAGs will be generated with change data capture processing based on SystemModstamp. See details on CDC processing for Salesforce.
SFDC.createPlaceholders	Create Placeholders	true	Create empty placeholder tables in case they are not generated by the ingestion process to allow the downstream reporting deployment to execute without failure.
SFDC.datasets.raw	Raw landing dataset	-	Used by the CDC process, this is where the replication tool lands the data from SFDC. If using test data, create an empty dataset.
SFDC.datasets.cdc	CDC Processed Dataset	-	Dataset that works as a source for the reporting views, and target for the records processed DAGs. If using test data, create an empty dataset.
SFDC.datasets.reporting	Reporting Dataset SFDC	"REPORTING_SFDC"	Name of the dataset that is accessible to end users for reporting, where views and user-facing tables are deployed.

Return to top of section

Deployment Configuration for Marketing

Parameter	Meaning	Default Value	Description
marketing.deployGoogleAds	Deploy Google Ads	true	Execute the deployment for Google Ads data source.
marketing.deployCM360	Deploy CM360	true	Execute the deployment for CM360 data source.
marketing.deployTikTok	Deploy TikTok	true	Execute the deployment for TikTok data source.
marketing.dataflowRegion	Dataflow region	-	Region for Dataflow pipelines (See available values).
marketing.GoogleAds.deployCDC	Deploy CDC for Google Ads	true	Generate Google Ads CDC processing scripts to run as DAGs in Cloud Composer.
marketing.GoogleAds.lookbackDays	Lookback days for Google Ads	180	Number of days to start fetching data from Google Ads API.
marketing.GoogleAds.datasets.cdc	CDC dataset for Google Ads		CDC dataset for Google Ads.
marketing.GoogleAds.datasets.raw	Raw dataset for Google Ads		Raw dataset for Google Ads.
marketing.GoogleAds.datasets.reporting	Reporting dataset for Google Ads	"REPORTING_GoogleAds"	Reporting dataset for Google Ads.
marketing.CM360.deployCDC	Deploy CDC scripts for CM360	true	Generate CM360 CDC processing scripts to run as DAGs in Cloud Composer.
marketing.CM360.dataTransferBucket	Bucket with Data Transfer Service results	-	Bucket where DTv2 files are stored.
marketing.CM360.datasets.cdc	CDC dataset for CM360		CDC dataset for CM360.
marketing.CM360.datasets.raw	Raw dataset for CM360		Raw dataset for CM360.
marketing.CM360.datasets.reporting	Reporting dataset for CM360	"REPORTING_CM360"	Reporting dataset for CM360.
marketing.TikTok.deployCDC	Deploy CDC scripts for TikTok	true	Generate TikTok CDC processing scripts to run as DAGs in Cloud Composer.
marketing.TikTok.datasets.cdc	CDC dataset for TikTok		CDC dataset for TikTok.
marketing.TikTok.datasets.raw	Raw dataset for TikTok		Raw dataset for TikTok.
marketing.TikTok.datasets.reporting	Reporting dataset for TikTok	"REPORTING_TikTok"	Reporting dataset for TikTok.

Return to top of section

[Optional] Configure K9 external datasets

Configure SAP Hierarchies

You can use the configuration in the file sets.yaml if you need to generate scripts to flatten hierarchies. See the Appendix - Configuring the flattener for options. This step is only executed if the CDC generation flag is set to true.

Configure External Datasets for K9

Some advanced use cases may require external datasets to complement an enterprise system of record such as SAP. In addition to external exchanges consumed from Analytics hub, some datasets may need custom or tailored methods to ingest data and join them with the reporting models.

Note: You will need to configure the DAGs as follows:

1. Holiday Calendar: This DAG retrieves the holiday calendars from PyPi Holidays. You can adjust the list of countries and years to retrieve holidays, as well as parameters of the DAG from the file holiday_calendar.ini. Leave the defaults if using test data.
2. Product Hierarchy Texts: This DAG flattens materials and their product hierarchies. The resulting table can be used to feed the Trends list of terms to retrieve Interest Over Time. You can adjust the parameters of the DAG from the file prod_hierarchy_texts.py. Leave the defaults if using test data. You will need to adjust the levels of the hierarchy and the language under the markers for ## CORTEX-CUSTOMER:. If your product hierarchy contains more levels, you may need to add an additional SELECT statement similar to the CTE h1_h2_h3.
3. Trends: This DAG retrieves Interest Over Time for a specific set of terms from Google Search trends. The terms can be configured in trends.ini. You will need to adjust the time frame to 'today 7-d' in trends.py after an initial run. We recommend getting familiarized with the results coming from the different terms to tune parameters. We also recommend partitioning large lists to multiple copies of this DAG running at different times. For more information about the underlying library being used, see Pytrends.
4. Weather: By default, this DAG uses the publicly available test dataset bigquery-public-data.geo_openstreetmap.planet_layers. The query also relies on an NOAA dataset only available through Analytics Hub, noaa_global_forecast_system. This dataset needs to be created in the same region as the other datasets prior to executing deployment. If the datasets are not available in your region, you can continue with the following instructions and follow additional steps to transfer the data into the desired region.

You can skip this configuration if using test data.

• Navigate to BigQuery > Analytics Hub
• Click Search Listings. Search for "NOAA Global Forecast System"
• Click Add dataset to project. When prompted, keep "noaa_global_forecast_system" as the name of the dataset. If needed, adjust the name of the dataset and table in the FROM clauses in weather_daily.sql.
• Repeat the listing search for Dataset "OpenStreetMap Public Dataset".
• Adjust the FROM clauses containing bigquery-public-data.geo_openstreetmap.planet_layers in postcode.sql.

Analytics hub is currently only supported in EU and US locations and some datasets, such as NOAA Global Forecast, are only offered in a single multilocation. If you are targeting a location different from the one available for the required dataset, we recommend creating a scheduled query to copy the new records from the Analytics hub linked dataset followed by a transfer service to copy those new records into a dataset located in the same location or region as the rest of your deployment. You will then need to adjust the SQL files .

Important Note: Before copying these DAGs to Cloud Composer, you will need to add the required python modules (holidays, pytrends) as dependencies.

Return to top of section

Check for CORTEX_CUSTOMER tags

Many SAP and Salesforce customers will have specific customizations of their systems, such as additional documents in a flow or specific types of a record. These are specific to each customer and configured by functional analysts as the business needs arise. The spots on the SQL code where these specific enhancements could be done are marked with a comment starting with ## CORTEX-CUSTOMER. You can check for these comments after cloning the repository with a command like:

grep -R CORTEX-CUSTOMER

Note: There may be additional customizations depending on the source systems. We recommend getting the business users or analysts involved early in the process to help spot these.

Performance optimization for Reporting views

Reporting artifacts can be created as views or as tables refreshed regularly through DAGs. On the one hand, views will compute the data on each execution of a query, which will keep the results always fresh. On the other hand, the table will run the computations once, and the results can be queried multiple times without incurring higher computing costs and achieving faster runtime. This balance is very specific to each customer, so we encourage each customer to create their own configuration to serve their needs.

Materialized results are updated into a table. These tables can be further fine-tuned by adding Partitioning and Clustering properties to these tables.

The configuration files for each workload are:

Data Source	Settings files
SAP	src/SAP/SAP_REPORTING/reporting_settings_ecc.yaml
Salesforce	src/SFDC/config/reporting_settings.yaml
Marketing - Google Ads	src/marketing/src/GoogleAds/config/reporting_settings.yaml
Marketing - CM360	src/marketing/src/CM360/config/reporting_settings.yaml

Customizing reporting_settings file configuration

This settings file drives how the BQ objects (tables or views) for Reporting datasets are created.

There are two sections:

1. bq_independent_objects: All BigQuery objects that can be created independently, without any other dependencies.

   Note: When Turbo mode is enabled, these BQ objects are created in parallel during the deployment time, speeding up the deployment process.

2. bq_dependent_objects: All BigQuery objects that need to be created in a specific order due to dependencies on other BQ objects.

   Note: Turbo mode does not apply to this section - i.e., each object will be created one after another.

The deployer will first create all the BQ Objects listed in bq_independent_objects first, and then all the objects listed in bq_dependent_objects.

Following properties need to be defined for each object:

1. sql_file: Name of the sql file that will create a given object.

2. type: Type of BQ Object. Possible values:

   • view : If we want the object to be a BQ view.
   • table: If we want the object to be a BQ table.
   • script: This is to create other types of objects (BQ Functions, Stored Procs etc)

3. If type is 'table', then following optional properties can be defined:

   • load_frequency: Frequency at which a Composer DAG will be executed to refresh this table. Mandatory. See Airflow documentation for details on possible values.
   • partition_details: How the table should be partitioned. Optional. See Appendix section Table Partition and Cluster Settings for details on how to configure this.
   • cluster_details: How the table should be clustered. Optional. See Appendix section Table Partition and Cluster Settings for details on how to configure this.

Execute Deployment

This step requires config.json to be configured as described in the section Configure Deployment.

Run the Build command with the target log bucket.

gcloud builds submit --project <execution project, likely the source> \
    --substitutions=_GCS_BUCKET=<Bucket for logs - Cloud Build Service Account needs access to write here>

You can follow the main Build process from the first link of logs:

Alternatively, if you have enough permissions, you can see the progress from Cloud Build.

Each build step will trigger child build steps. These can be tracked from the Cloud Build console:

Or finding the logs for the child build within the log from a step:

And identify any issues with individual builds:

We recommend pasting the generated SQL into BigQuery to identify and correct the errors more easily. Most errors will be related to fields that are selected but not present in the replicated source. The BigQuery UI will help identify and comment those out.

Move the files into the DAG bucket

If you opted to generate integration or CDC files and have an instance of Airflow, you can move them into their final bucket with the following command:

gsutil -m cp -r  gs://<output bucket>/dags/ gs://<composer dag bucket>/
gsutil -m cp -r  gs://<output bucket>/data/ gs://<composer sql bucket>/

Test, customize and prepare for upgrade

In addition to the CORTEX-CUSTOMER tags, you may need to further customize the content to add business rules, add other datasets and join them with existing views or tables, reuse the provided templates to call additional APIs, modify deployment scripts, apply further data mesh concepts, etc. You may also need to slightly adapt some tables or landed APIs to include additional fields not included in our standard. We recommend committing all of these changes with a clear tag in the code to your own fork or clone of our git repositories.

We recommend adopting a CICD pipeline that works for your organization, to keep these enhancements tested and your overall solution in a reliable, robust state. A simple pipeline can reuse our cloudbuild*.yaml scripts to trigger end to end deployment periodically, or based on git operations depending on your repository of choice by automating builds. Using automated testing with your own sample data will help ensure the models always produce what you expect every time someone commits a change. The config.json file plays an important role in defining different sets of projects and datasets for development, staging and production environments.

Tagging your own changes visibly in your fork or clone of a repository together with some deployment and testing automation will be very helpful when performing upgrades. Check out this guide for upgrade instructions.

Next steps

Looker deployment

Instructions for deploying the pre-built Looker blocks can be found here. Please follow the individual block READMEs for detailed instructions on required dependencies to ensure successful deployment.

Optionally, you may also customize the blocks by forking the GitHub repositories into your own Looker project. Instructions can be found here.

Available blocks for the Data Foundation include the following:

• SAP Operational Dashboards for Cortex
• Salesforce Dashboards for Cortex

Demand Sensing

You can deploy the Demand Sensing use case from the Marketplace. Learn more from the documentation.

Application Layer

Deploy a sample micro-services based application through the Google Cloud Marketplace.

Customizations and upgrades

We strongly encourage you to fork this repository and apply your changes to the code in your own fork. You can make use of the delivered deployment scripts in your development cycles and incorporate your own test scripts as needed. When a new release is published, you can compare the new release from our repository with your own changes by merging our code into your own fork in a separate branch. Suggestions for changes or possible customizations in the code are flagged with the comment ## CORTEX-CUSTOMER. We recommend listing these after the initial deployment.

Enable TURBO mode

For your own customizations and a faster deployment in your own development pipelines, you can use the turboMode variable in config/config.json. When set to true, the deployment process will dynamically generate a cloudbuild.views.yaml file with each view in the right dependencies file (e.g., dependencies_ecc.txt or dependencies_s4.txt) as a single step of the build. This allows for a 10x faster deployment. The limitation is that if an error occurs when deploying a view, the build process will stop, and this is a slow way to find out all possible mismatches between the Cortex views and your datasource when compared to the turboMode=false option. If you are still fixing potential structure mismatches between the SELECT clauses in the views and the fields available in your replicated tables, TURBO=false will take longer but will attempt to generate all views even if one fails. This will help identify and fix more errors in a single run.

Support

To file issues and feature requests against these models or deployers, create an issue in this repo. For help gathering the information required to help troubleshoot, execute support.sh from the cloned directory and follow instructions.

Appendix

Understanding Change Data Capture

Replicating raw data from SAP

The goal of the Data Foundation is to expose data and analytics models for reporting and applications. The models consume the data replicated from an SAP ECC or SAP S/4HANA system using a preferred replication tool, like those listed in the Data Integration Guides for SAP.

Data from SAP ECC or S/4HANA is expected to be replicated in raw form, that is, with the same structure as the tables in SAP and without transformations. The names of the tables in BigQuery should be lower case for cortex data model compatibility reasons.

For example, fields in table T001 are replicated using their equivalent data type in BigQuery, without transformations:

Change Data Capture (CDC) Processing

BigQuery is an append preferred database. This means that the data is not updated or merged during replication. For example, an update to an existing record can be replicated as the same record containing the change. To avoid duplicates, a merge operation needs to be applied afterwards. This is referred to as Change Data Capture processing.

The Data Foundation for SAP includes the option to create scripts for Cloud Composer or Apache Airflow to merge or “upsert” the new records resulting from updates and only keep the latest version in a new dataset. For these scripts to work the tables need to have a field with an operation flag named operation_flag **(I = insert, U = update, D = delete) **and a timestamp named recordstamp.

For example, the following image shows the latest records for each partner record, based on the timestamp and latest operation flag:

Dataset structure

Data from SAP or Salesforce is replicated into a BigQuery dataset -the source or replicated dataset- and the updated or merged results are inserted into another dataset- the CDC dataset. The reporting views select data from the CDC dataset, to ensure the reporting tools and applications always have the latest version of a table.

The following flow depicts the CDC processing for SAP, dependent on the operational_flag and recordstamp.

The following flow depicts the integration from APIs into Raw and CDC processing for Salesforce, dependent on the Id and SystemModStamp fields produced by Salesforce APIs.

Some replication tools can merge or upsert the records when inserting them into BigQuery, so the generation of these scripts is optional. In this case, the setup will only have a single dataset. The REPORTING dataset will fetch updated records for reporting from that dataset.

Optional - Using different projects to segregate access

Some customers choose to have different projects for different functions to keep users from having excessive access to some data. The deployment allows for using two projects, one for processing replicated data, where only technical users have access to the raw data, and one for reporting, where business users can query the predefined models or views.

Using two different projects is optional. A single project can be used to deploy all datasets.

Setting up CDC processing

During deployment, you can choose to merge changes in real time using a view in BigQuery or scheduling a merge operation in Cloud Composer (or any other instance of Apache Airflow).

Cloud Composer can schedule the scripts to process the merge operations periodically. Data is updated to its latest version every time the merge operations execute, however, more frequent merge operations translate into higher costs.

The scheduled frequency can be customized to fit the business needs.

You will notice the file uses scheduling supported by Apache Airflow.

The following example shows an extract from the configuration file:

data_to_replicate:
  - base_table: adrc
    load_frequency: "@hourly"
  - base_table: adr6
    target_table: adr6_cdc
    load_frequency: "@daily"

This configuration will:

1. Create a copy from source\_project\_id.REPLICATED\_DATASET.adrc into target\_project\_id.DATASET\_WITH\_LATEST\_RECORDS.adrc if the latter does not exist
2. Create a CDC script in the specified bucket
3. Create a copy from source\_project\_id.REPLICATED\_DATASET.adr6 into target\_project\_id.DATASET\_WITH\_LATEST\_RECORDS.adr6\_cdc if the latter does not exist
4. Create a CDC script in the specified bucket

SAP only: If you want to create DAGs or runtime views to process changes for tables that exist in SAP and are not listed in the file, add them to this file before deployment. For example, the following configuration creates a CDC script for custom table “zztable_customer” and a runtime view to scan changes in real time for another custom table called “zzspecial_table”:

  - base_table: zztable_customer
    load_frequency: "@daily"
  - base_table: zzspecial_table
    load_frequency: "RUNTIME"

This will work as long as the table DD03L is replicated in the source dataset and the schema of the custom table is present in that table.

Sample generated template

The following template generates the processing of changes. Modifications, such as the name of the timestamp field, or additional operations, can be done at this point:

MERGE `${target_table}` T
USING (SELECT * FROM `${base_table}` WHERE recordstamp > (SELECT IF(MAX(recordstamp) IS NOT NULL, MAX(recordstamp),TIMESTAMP("1940-12-25 05:30:00+00")) FROM `${target_table}`)) S
ON ${p_key}
WHEN MATCHED AND S.operation_flag='D' AND S.is_deleted = true THEN
  DELETE
WHEN NOT MATCHED AND S.operation_flag='I' THEN
  INSERT (${fields})
  VALUES
  (${fields})
WHEN MATCHED AND S.operation_flag='U' THEN
UPDATE SET
    ${update_fields}

SAP Only: Alternatively, if your business requires near-real time insights and the replication tool supports it, the deployment tool accepts the option RUNTIME. This means a CDC script will not be generated. Instead, a view will scan and fetch the latest available record at runtime for immediate consistency.

CDC fields required for MERGE operations

The following parameters will be required for the automated generation of change-data-capture batch processes:

• Source project + dataset: Dataset where the SAP data is streamed or replicated. For the CDC scripts to work by default, the tables need to have a timestamp field (called recordstamp) and an operation field with the following values, all set during replication:
  • I: for insert
  • U: for update
  • D: for deletion
• Target project + dataset for the CDC processing: The script generated by default will generate the tables from a copy of the source dataset if they do not exist.
• Replicated tables: Tables for which the scripts need to be generated
• Processing frequency: Following the Cron notation, how frequently the DAGs are expected to run
• Target GCS bucket where the CDC output files will be copied
• The name of the connection used by Cloud Composer
• Optional: If the result of the CDC processing will remain in the same dataset as the target, you can specify the name of the target table.

Gathering Cloud Composer Settings

If Cloud Composer is available, create connection(s) to the Source Project in Cloud Composer.

Please create connections with the following names for DAG execution, based on the types of deployments below.

NOTE: If you are creating tables in the Reporting layer, please make sure to create separate connections for Reporting DAGs.

If you are deploying...	Create for Raw	Create for CDC	Create for Reporting
SAP	N/A	sap_cdc_bq	sap_reporting_bq
SFDC	sfdc_cdc_bq (*)	sfdc_cdc_bq	sfdc_reporting_bq
Google Ads	googleads_raw_dataflow	googleads_cdc_bq	googleads_reporting_bq
CM360	cm360_raw_dataflow	cm360_cdc_bq	cm360_reporting_bq
TikTok	tiktok_raw_dataflow	tiktok_cdc_bq	tiktok_reporting_bq
LiveRamp	N/A	liveramp_cdc_bq	N/A

* SFDC Raw Ingestion module uses the same Airflow connection as SFDC CDC module.

Please refer to sections for each individual data sources for details.

Notes:

• Connection name suffixes indicate their intended usage. _bq are meant for BigQuery access, while _dataflow are meant to run Google Cloud DataFlow jobs.
• You only need to create connections for Raw if you are using the ingestion modules provided by Cortex.
• If you are deploying multiple data sources, for example both SAP and Salesforce, we recommend creating all connections assuming security limitations will be applied to separate service accounts. Alternatively, modify the name of the connection in the template prior to deployment to use the same connection to write into BigQuery as shown below.
• We do not recommend using the default connections and service accounts in Airflow, specially in production environments. This recommendation is to comply with the principle of least privilege
• If you have Secret Manager Backend enabled for Airflow, you can also create these connections within Secret Manager under the same name. Connections in Secret Manager takes precedence over connections created in Airflow.

The GCS bucket structure for some of the template DAG expects the folders to be in /data/bq_data_replication. You can modify this path prior to deployment.

If you do not have an environment of Cloud Composer available yet, you can create one afterwards and move the files into the DAG bucket.

Note: The scripts that process data in Airflow or Cloud Composer are purposefully generated separately from the Airflow-specific scripts. This allows you to port those scripts to another tool of choice.

(SAP Only) Configuring the flattener for SAP hierarchies

The deployment process can optionally flatten hierarchies represented as sets (e.g. transaction GS03) in SAP. The process can also generate the DAGs for these hierarchies to be refreshed periodically and automatically. This process requires configuration prior to the deployment and should be known by a Financial or Controlling consultant or power user.

This video explains how to perform the configuration to flatten hierarchies.

The deployment file takes the following parameters:

• Name of the set
• Class of the set (as listed by SAP in standard table SETCLS)
• Organizational Unit: Controlling Area or additional key for the set
• Client or Mandant
• Reference table for the referenced master data
• Reference key field for master data
• Additional filter conditions (where clause)

The following are examples of configurations for Cost Centers and Profit Centers including the technical information. If unsure about these parameters, consult with a Finance or Controlling SAP consultant.

sets_data:
#Cost Centers:
# table: csks, select_fields (cost center): 'kostl', where clause: Controlling Area (kokrs), Valid to (datbi)
- setname: 'H1'
  setclass: '0101'
  orgunit: '1000'
  mandt:  '800'
  table: 'csks'
  key_field: 'kostl'
  where_clause: [ kokrs = '1000', datbi >= cast('9999-12-31' as date)]
  load_frequency: "@daily"
#Profit Centers:
# setclass: 0106, table: cepc, select_fields (profit center): 'cepc', where clause: Controlling Area (kokrs), Valid to (datbi)
- setname: 'HE'
  setclass: '0106'
  orgunit: '1000'
  mandt:  '800'
  table: 'cepc'
  key_field: 'prctr'
  where_clause: [ kokrs = '1000', datbi >= cast('9999-12-31' as date) ]
  load_frequency: "@monthly"
#G/L Accounts:
# table: ska1, select_fields (GL Account): 'saknr', where clause: Chart of Accounts (KTOPL), set will be manual. May also need to poll Financial Statement versions.

This configuration will generate two separate DAGs. For example, if there were two configurations for Cost Center hierarchies, one for Controlling Area 1000 and one for 2000, the DAGs would be 2 different files and separate processes but the target, flattened table would be the same.

Important: If re-running the process and re-initializing the load, make sure the tables are truncated. The CDC and initial load processes do not clear the contents of the tables which means the flattened data will be inserted again.

Configuring Salesforce to BigQuery extraction module

These are the generic steps to use the Salesforce to BigQuery extraction module provided by Data Foundation. Your requirements and flow may vary depending on your system and existing configuration. You can alternatively use other available tools.

Setting up required credentials & Connected App in Salesforce

You need to login as an administrator to your Salesforce instance to complete the following:

1. Create or identify a profile in Salesforce that
   • Is granted permission for Apex REST Services & API Enabled under System Permissions.
   • Is granted View All permission for all objects that you would like to replicate. For example, Account, Cases, etc. Check for restrictions or issues with your security administrator.
   • Is ideally not granted any permissions related to user interface login like Salesforce Anywhere in Lightning Experience,Salesforce Anywhere on Mobile,Lightning Experience User,Lightning Login User & etc. Check for restrictions or issues with your security administrator.
2. Create or use identify existing user in Salesforce. You need to know the user's user name, password, and security token.
   • This should ideally be a user dedicated to execute this replication.
   • The user should be assigned to the profile you have created or identified in Step 1.
   • You can see User Name and reset Password here.
   • You can reset the security token if you do not have it and it is not used by another process.
3. Create a Connected App. It will be the only communication channel to establish connection to Salesforce from the external world with the help of profile, Salesforce API, standard user credentials and its security token.
   • Follow the instructions to enable OAuth Settings for API Integration.
   • Make sure Require Secret for Web Server Flow and Require Secret for Refresh Token Flow are enabled in API (Enabled OAuth Settings) section.
   • See the documentation on how to get your consumer key (which will be later used as your Client ID). Check with your security administrator for issues or restrictions.
4. Assign your Connected App created in Step 3 to the profile created in Step 1.
   • Select Setup from the top right of the Salesforce home screen.
   • In the Quick Find box, enter profile, then select Profile. Search for the profile created / identified in Step 1.
   • Open the profile.
   • Click the Assigned Connected Apps link.
   • Click Edit.
   • Add the newly created Connected App from Step 3.
   • Click on the Save button.

Note down User Name, Password, Secret Token and Client ID from steps above.

Setting up Google Cloud Secret Manager

The Salesforce-to-BigQuery module relies on Google Cloud Secret Manager to work. This process is thoroughly documented in the documentation for Cloud Composer

Please create a secret as follows:

Secret Name:

airflow-connections-salesforce-conn

Secret Value:

http://<username>:<password>@https%3A%2F%2F<instance-name>.lightning.force.com?client_id=<client_id>&security_token=<secret-token>

Where User Name, Password, Secret Token and Client ID were noted from the steps above.

See the documentation on how to find your Instance Name.

Cloud Composer libraries for Salesforce replication

To execute the Python scripts in the DAGs provided by the Data Foundation, you need to install some dependencies.

For Airflow version 1.10, follow the documentation to install the following packages, in order:

tableauserverclient==0.17
apache-airflow-backport-providers-salesforce==2021.3.3

For Airflow version 2.x, follow the documentation to install apache-airflow-providers-salesforce~=5.2.0.

Here is a command to install each required package:

$ gcloud composer environments update <ENVIRONMENT_NAME> \
    --location <LOCATION> \
     --update-pypi-package <PACKAGE_NAME><EXTRAS_AND_VERSION>

For example,

$ gcloud composer environments update my-composer-instance \
    --location us-central1 \
     --update-pypi-package apache-airflow-backport-providers-salesforce>=2021.3.3

Enable Secret Manager as a backend

Enable Google Secret Manager as the security backend. See details here.

Allow the Composer service account to access secrets

Make sure your Composer service account (default: GCE service account) has Secret Manager Secret Accessor permission. See details in the access control documentation.

BigQuery connection in Airflow

Make sure to create the connection sfdc_cdc_bq according to instructions.

Table Partition and Cluster Settings

For certain settings files (e.g. SAP CDC settings file cdc_settings.yaml or all Reporting settings yaml file reporting_settings.yaml) provide a way to create materialized tables with clusters or partitions of your choice. This is controlled by the following properties in the settings file:

Table Partitioning

Partition can be enabled by specifying partition_details:

Example:

   - base_table: vbap
     load_frequency: "@daily"
     partition_details: {
       column: "erdat", partition_type: "time", time_grain: "day"
     }

Use following parameters to control partitioning details for a given table:

Property	Description	Value
column	Column by which the CDC table will be partitioned	Column name
partition_type	Type of Partition	"time" for time based partition (More details) "integer_range" for integer based partition (More details)
time_grain	Time part to partition with Required when partition_type = "time"	"hour", "day", "month" OR "year"
integer_range_bucket	Bucket range Required when partition_type = "integer_range"	"start" = Start value "end" = End value "interval" = Interval of range

NOTE See BigQuery Table Partition documentation details to understand these options and related limitations.

Table Clustering

Similarly, clustering can be by specifying cluster_details:

Example:

   - base_table: vbak
     load_frequency: "@daily"
     cluster_details: {columns: ["vkorg"]}

Property	Description	Value
columns	Columns by which a table will be clustered	List of column names e.g. ["mjahr", "matnr"]

NOTE: See BigQuery Table Cluster documentation details to understand these options and related limitations.

License

This source code is licensed under Apache 2.0. Full license text is available in LICENSE.