Practices and Guidelines for SaaS targets

[aaronsteers]

Creating this stub to start collecting notes and practices that work well for people writing targets that publish to SaaS systems.

Related conversations may be found in the #singer-target-development channel on the Meltano Slack.

[pnadolny13]

Some things that came up during the tap-toberfest call related to @dan-ladd's https://github.com/dan-ladd/target-salesforce.

1. How much data type validation should the target do?
2. What happens if a record fails to load to the target API? Should there be a dead letter queue for inspection and retry?
3. Should there be a consistent way to map field names? I know for snowflake all columns are sent as uppercase, should the target do some consistent field transformations or is that the users responsibility? Should we accept a field mapping config so you can define COLUMN_X -> _column_x.
4. Should a SaaS target advertise a schema that the tap needs to conform to?

cc @visch

[pnadolny13]

What I heard others says as answers to these, along with some of my own thoughts are:

1. As much as you can within reason. For example salesforce allows complex type validation so that target mostly does basic type validation and leave the rest up to the user.
2. At a minimum log an error message. @visch is able to do a diff on each run because he has a 2 way sync so he knows which records need to be updated. Others have implemented varying dead letter queue solutions. I've written all error records to an S3 file for manual inspection for a few use cases when errors are rare.
3. Most of this is being pushed down to the tap/source. The developer should have clean tap data that maps properly to target fields. I've also built mapping config options into the target to give you more control to avoid having to make code changes for edge cases.
4. It could be cool to have the target provide a schema that the tap records need to conform to and a test feature that allows you to validate your tap records will be accepted by the target while developing.

[visch]

re # 2 With clean transformation you can do a lot of cool stuff with diffs of the two data sets. Like testing to be sure what you're about to change is right (Not always problem with normal Targets as you normally aren't breaking anything pushing new data into a warehouse). The main mental model is to create a "desired table" which matches 1-1 with the "target table". It's easier with an example

Example: Let's say we want to sync a field in Salesforce accounts called "active_in_last_30_days" to True or False. We pull all salesforce accounts and create a table called current_salesforce_accounts with fields ID, active_in_last_30_days. We also create another table called desired_salesforce_accounts with fields ID, active_in_last_30_days. Then you can do a diff between current and desired, and with a little logic you can come up with which rows need creates, and updates in your target system. You also get a bunch of additional auditing information like your source system (that generated desired_salesforce_accounts) probably has records that doesn't exist in salesforce, what do you do about those? How do you join between your source and your target data set etc :)