Hacker News Reader

Are BigQuery's clustered and partitioned supported (both as a source and sink).

Plus how is the deduplication process handled? Fivetran for example creates staging tables and scans the target table. Since it does support BigQuery's integer based partitioning. A table partitioned by Primary key helps in cost optimizations.

View on HN

Yep, the target tables on BQ are automatically clustered on the primary key, and there is an option to further partition them on the peerdb_synced_at column. We've seen this reduce replication costs by 50% and also help reduce costs for off-stream transformations. Sharing a customer case study that you might find interesting: :) https://www.peerdb.io/customers/harmonic-customer-story

Regarding the deduplication process, we stage raw CDC data into a staging table and run periodic MERGE operations. The raw table is also auto-clustered and partitioned similar to final table. This helps save costs.

You can partition your BigQuery table however you like and Fivetran will leave it in place. I don’t think there’s any benefit to partitioning the staging table.

Reply on news.ycombinator.com