Ask Your Question
1

Salesforce CDC to Snowflake overwrites data with nulls

asked 2020-03-09 11:15:53 -0500

updated 2020-03-09 11:35:22 -0500

I have a pipeline with a Salesforce CDC origin. I'm writing to Snowflake using the Snowflake destination with the "CDC Data" option on the data table selected. My problem is that I only have values from Salesforce CDC for the columns where the data has changed. The StreamSets Snowflake destination is passing nulls for all the other fields, which means the merge statement in Snowflake updates all those field values to null inside Snowflake.

Is there a switch somewhere that will prevent this for happening? I have tried deselecting the Ignore Missing Fields option on the Data Advanced tab, but then I get an error about missing fields (because, I assume, they aren't included in the JSON from the Salesforce CDC).

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

answered 2020-03-09 13:18:02 -0500

metadaddy gravatar image

The problem here is that Salesforce CDC only gives you changed fields, but Snowflake wants the entire record. You can use the Salesforce Lookup processor to retrieve the entire record given the record Id. Make sure you specify 'retrieve' mode, rather than supplying a SOQL query, since retrieve sends just one API call per batch, while SOQL query requires an API call per record.

One other thing you'll need to do is to 'expand' CDC notifications that contain multiple changes into multiple records in the pipeline. You can do this with Expression Evaluator and Field Pivoter:

image description

image description

Then your lookup will be something like:

image description

Here's my entire test pipeline:

image description

edit flag offensive delete link more

Comments

1

Thank you, Pat. I had considered doing a lookup back to Salesforce but was concerned I'd hit limits on their APIs because of bulk updates. But if that can be done in batches via Retrieve mode it should significantly reduce that overhead.

asturt gravatar imageasturt ( 2020-03-09 14:13:55 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-09 11:15:53 -0500

Seen: 19 times

Last updated: Mar 09