Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Configure tables as INSERT ONLY

Planned
Facundo Pezzola User

Context:

- Source: Postgres

- Destination: BigQuery

- Sync every 15 minutes

In our production database we're dealing with tables with more than 500 millon rows that are just item different status changes logs. As they are logs, we don't execute UPDATES, just INSERT.

Every time that Fivetran Synchronises them, it is executing MERGE statements into BigQuery, making our Bill to increase a lot since we're synchronising every 15 minutes.

In other words I want you to execute this:

INSERT INTO `postgres_rds_public`.`items_transaction_history` (
SELECT_fivetran_deleted, _fivetran_synced, extra, from_user_id, id, item_id, price, source, time, to_user_id, type
FROM`fivetran_did_purist_staging`.`postgres_rds_public_items_transaction_history_2024_06_13_1503e558_c62f_4545_8494_4c87581cc10b`
)

Instead of:

MERGE `postgres_rds_public`.`items_transaction_history` AS `#existing` USING `fivetran_did_purist_staging`.`postgres_rds_public_items_transaction_history_2024_06_13_1503e558_c62f_4545_8494_4c87581cc10b` AS `#scratch` ON `#existing`.`id` = `#scratch`.`id` WHEN MATCHED THEN UPDATE SET `#existing`.`id`=`#scratch`.`id`, `#existing`.`_fivetran_deleted`=`#scratch`.`_fivetran_deleted`, `#existing`.`_fivetran_synced`=`#scratch`.`_fivetran_synced`, `#existing`.`from_user_id`=`#scratch`.`from_user_id`, `#existing`.`item_id`=`#scratch`.`item_id`, `#existing`.`price`=`#scratch`.`price`, `#existing`.`source`=`#scratch`.`source`, `#existing`.`time`=`#scratch`.`time`, `#existing`.`to_user_id`=`#scratch`.`to_user_id`, `#existing`.`type`=`#scratch`.`type`, `#existing`.`extra`=`#scratch`.`extra` WHEN NOT MATCHED THEN INSERT ( `_fivetran_deleted`, `_fivetran_synced`, `extra`, `from_user_id`, `id`, `item_id`, `price`, `source`, `time`, `to_user_id`, `type` ) VALUES ( `_fivetran_deleted`, `_fivetran_synced`, `extra`, `from_user_id`, `id`, `item_id`, `price`, `source`, `time`, `to_user_id`, `type` )

 

So my feature request would be: 

1. Access to the connector settings

2. Go to schema

3. On every table there should be an option to switch the update method: APPEND / MERGE

 

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Official comment
    Egidio Terra Fivetranner

    Hi Facundo,

    This is a valuable idea and the ability to configure tables for INSERT ONLY/APPEND mode could help optimize sync costs and time, especially for tables that only receive new log entries with no UPDATE or DELETE operations. I’ve added this request to our feature improvements backlog for further review.

    To better understand your needs, could you share more about the impact MERGE statements have had on your billing and any specific scenarios where APPEND would present challenges or limitations? Are there tables that might switch from INSERT ONLY to also requiring UPDATES, or is this pattern consistent?

    I will keep the community updated on this thread as progress is made.

    Thanks,