Source Salesforce - Incremental dedupe + history mode fails when cursor field is SUPER

[olivermeyer]

Environment

• Airbyte version: 0.39.36-alpha
• OS Version / Instance: Debian
• Deployment: Kubernetes
• Source Connector and version: Salesforce (0.2.5)
• Destination Connector and version: Redshift (0.3.46)
• Step where error happened: Sync job

Current Behavior

We recently upgraded Airbyte to 0.39.36 and had a few issues after some fields were converted to SUPER. Most issues were fixed by a full refresh, but we're left with one issue which we cannot solve: the normalization fails when using the incremental dedupe + history mode on a stream which uses a SUPER cursor field.

Specifically, the AccountHistory stream in the Salesforce uses createddate as cursor, but this field seems to be cast to SUPER somewhere along the way and I'm getting this error:

2022-09-12 07:04:08 normalization > 07:04:06.464534 [error] [MainThread]: Database Error in model salesforce__accounthistory_scd (models/generated/airbyte_incremental/scd/tgtg_raw/salesforce__accounthistory_scd.sql)
2022-09-12 07:04:08 normalization > 07:04:06.464882 [error] [MainThread]:   function lag(super) does not exist
2022-09-12 07:04:08 normalization > 07:04:06.465214 [error] [MainThread]:   HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Expected Behavior

The sync should succeed.

Steps to Reproduce

1. Set up a Salesforce source and a Redshift destination
2. Set up a connection between the two, selecting the AccountHistory stream in incremental dedupe + history mode
3. Run the connection

Are you willing to submit a PR?

With support to understand the normalization code, maybe.

[salima-airbyte]

Connector team is appropriately tagged. Removing team/oss