fivetran-destination: Write data into a temporary table with `COPY` then insert with Read-Write queries

MaterializeInc / materialize

The Cloud Operational Data Store: use SQL to transform, deliver, and act on fast-changing data.

https://materialize.com

Other

5.71k stars 466 forks source link

fivetran-destination: Write data into a temporary table with `COPY` then insert with Read-Write queries #24665

Open ParkMyCar opened 7 months ago

ParkMyCar commented 7 months ago

Feature request

Today when handling a WriteBatchRequest we do a bunch of individual INSERT, DELETE, and UPDATE statements. What would be better is if we use the COPY FROM protocol to copy into a temporary table and then use Read-Write queries, e.g. INSERT INTO ... SELECT ..., to merge the temporary table into the final destination.

Note: this is separate from supporting a higher level MERGE

ParkMyCar commented 5 months ago

Note: this was already implemented for the replace_files(...) and delete_files(...) sub operations, but still needs to be done for update_files(...)