Best Practices for Optimizing BigQuery Partitions via Airbyte Sync

[itailulu]

I'm using Airbyte to sync data daily from Klaviyo to BigQuery. The current setup partitions data by the airbyte_extracted_at column. Given that most of our queries are time-specific, this partitioning strategy leads to suboptimal performance and higher costs due to the necessity of reading the entire table.

I've been considering manually creating a table with partitioning configured on event timestamps to better align with our querying needs. However, I'm concerned about the potential impacts this might have on the efficiency and cost of the sync process.

Has anyone in the community dealt with similar challenges? Are there recommended approaches or alternative methods, such as moving data to a correctly partitioned table post-sync?
I’m eager to hear about any best practices or insights you might have.

Thanks in advance!