How Can I Upsert Data Using a Cloud Function?

Use Case

You want to upsert data with your cloud function but don’t see an object that allows you to achieve this.

Recommendation

Define a primary key in the schema object that is passed as a response to Fivetran. For example:

"schema": {
  "table": {
    "primary_key": ['id']
  }
}

Context

Fivetran requires the following five objects to be passed as a response from a cloud function:

• state
• insert
• delete
• schema
• hasMore

For more information, see our Response format documentation.

There is no upsert object. However, the insert object allows you to both insert and upsert data depending on your use case and how you have built the function.

If you define the primary keys of the response data in the schema object, the rows pushed into the insert object are deleted and re-inserted, mimicking an upsert operation.

NOTE: A deletion in a cloud function remains consistent with Fivetran’s deletion policy: the _fivetran_deleted system column is marked as true.

Considerations

If you don't specify a primary key, then all the rows pushed to the insert object are inserted, with the columns _fivetran_batch and _fivetran_index functioning as primary keys. For more information, see our Schema information documentation.