How Can I Upsert Data Using a Cloud Function?

Question

Fivetran's standard response format doesn't include an upsert object. How can I upsert data using a cloud function?

Environment

All Function connectors

Answer

To upsert data using a cloud function, do the following:

1. Define a primary key by including a primary_key field for each table in the schema object. You can use the following structure:

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

2. Send new or updated rows in the insert object, including the primary key for each row.

We use the primary key to identify existing rows. When we find a match, we delete and re-insert the row, effectively performing an upsert. If you don't specify a primary key, then we insert all rows pushed to the insert object, with the columns _fivetran_batch and _fivetran_index functioning as primary keys. For more information, see our Schema information documentation.

Deleting data

By default, when you delete a row using a cloud function, we hard delete the row from your destination.

To soft delete rows, include the softDelete field in your response and set it to true. When you soft delete a row, we mark it as _fivetran_deleted = TRUE in the destination instead of removing it. For more information, see our Response format documentation.