Question
How can I upsert data in a cloud function?
Environment
Function connectors
Answer
Define a primary key in the schema object that is passed as a response to Fivetran
Please see the example below:
schema : {
table : {
primary_key : ['id']
}
}
Context
Fivetran requires five objects to be passed as a response from a cloud function:
- state
- insert
- delete
- schema
- hasMore
Whilst there is no upsert object, the insert object provides the ability to both insert and upsert data depending on your use-case and how you have built your function.
If you define the primary keys of the response data in the schema object, any rows pushed into the insert object and returned to Fivetran will be deleted and reinserted mimicking an upsert operation.
Note: A deletion in the sense of a cloud function remains consistent with Fivetran’s deletion policy: the ‘_fivetran_deleted’ system column will be marked as ‘true’
Considerations
If a primary key is not specified, then all rows pushed to the insert object will only be treated as an insert, with the columns ‘_fivetran_batch’ and ‘_fivetran_index’ functioning as primary keys.