Salesforce Replication Plugins

Introduction 

Salesforce replication plugins can help users automatically replicate their Salesforce objects to specified destinations with only a few clicks.

User Stories

• As a data pipeline user, I would like to be able to create a pipeline to replicate a single Salesforce object to a specified destination, so that I can use the Salesforce object for analytics in my downstream processes.
• As a data pipeline user, I would like to be able to create a pipeline to replicate multiple Salesforce objects to their corresponding destinations, so that I can use the Salesforce objects for analytics in my downstream processes.

Design considerations and constraints

Ideally, this use case would be solved using a separate pipeline template, that knows that there is a dependency between the source and the sink, in the sense that the sinks would only work with certain kinds of sources (that merely provide a list of objects to replicate). Another constraint in such a template would be that such a pipeline does not support any transformations between the source and the sink. This pipeline is basically a single source to many sinks pipeline. It does not require users to specify output schema or anything like that, because all of that is like a black box. Such a pipeline merely reads input objects, and replicates them as-is to destination(s). Perhaps this pipeline template exposes special kinds of plugins that can solve this use case.

However, given that that solution is more long term, this document will focus on a way to achieve this using the current data pipeline template. The proposal involves providing a Salesforce batch source for replication, that allows users to specify source objects to replicate. It then also provides a set of batch sinks (for BigQuery and GCS to start off with), that replicate data from these specific sources into their respective destinations.

Requirements

• Support BigQuery and GCS as destinations
• Select source object(s) and a destination. For each object, create a corresponding object in the destination (if it doesn't exist already), and push data to it. 
• A user should be able to easily identify the destination object based on its name. The name should be unique, and must contain the source object name. For uniqueness purposes, more information may be encoded in the destination object name.
• Support full (get all data since the beginning of time) and incremental (get data since the specified timestamp) modes. 
• Automatically handle schema changes between source and destination. If a destination exists, but with a different schema from the source because the source's schema has been updated, the destination's schema should be updated.
  

Type of plugin

1. Batch Source
2. Batch Sink (s)

User flow

1. User specifies Salesforce credentials (username, password, clientId and clientSecret)
   1. The source plugin determines the Salesforce instance to use based on these credentials
2. User selects the Salesforce objects to replicate
   1. There may be a limitation here that CDAP currently does not have the capability to list all objects for the user to select from. If that's the case, we may require the user to manually enter the objects to begin with, and then enhance this experience later (perhaps when connections and plugins are unified)
3. User specifies whether he wants to run a "full refresh" (pull all data since the beginning of time) or incremental (pull data since the specified timestamp).
4. User specifies the destination. 
   
   1. This is perhaps a different set of sink plugins
   2. There is one such plugin for BigQuery and GCS
   3. For each such plugin, the user only specifies the top level container. e.g. For GCS, user specifies a bucket. For BigQuery, a dataset.
5. For all Salesforce objects selected
• The sink plugins create a corresponding object in the destination- e.g. a directory in the GCS bucket, a table in the BigQuery dataset.
• The sink plugins ensure that the schema of the destination objects is consistent with the schema of the source, at the time of the run, and make an effort to resolve inconsistencies by updating the destination schema
  • Ignore deleted columns, add new columns.

Technical Design

Source Properties

• clientId: Client ID from the connected app 
• cllientSecret: Client Secret from the connected app
• username: Username
• password: Password
• loginUrl (default is https://login.salesforce.com/services/oauth2/token) For Salesforce sandbox runs login url is different. That's why user needs this option.
• objects: list of Salesforce objects to replicate

Sink Properties

BigQuery

• (Optional) Project ID
• (Optional) Service Account File Path
• (Required) Dataset

GCS

• (Optional) Project ID
• (Optional) Service Account File Path
• (Required) Bucket
• (Optional) Partitioning scheme (defaults to YYYYMMDDHHmm)
• (Optional) Format (defaults to JSON)

Deliverables

1. Batch source plugin for Salesforce
2. Batch sink plugin for BigQuery and GCS
3. Template pipelines using these plugins for the Hub