BigQuery Connector: Ability to use a different field for partitioning

[timorkal]

Currently the table which is created is using "_airbyte_extracted_at" as the field for partitioning, but I would like to use a different field.
This is very imporant for table with historical data, where there is already another field which they need to partitioned by.
The default "_airbyte_extracted_at" is not a good choice because as soon as the syncing happens all historical data are getting into the same partition of the day of the sync.

[NAjustin]

@timorkal Could you describe a little more about why partitioning specifically needs to be on another field in the Airbyte destination table itself?

Generally when you're dealing with incremental syncs, you want the destination table to be partitioned by the ingestion time because it means only the current partition is affected, and minimizes the partitions that need to be scanned when merging the data from the temporary table in airbyte_internal and the destination table. Not using this could likely increase the amount of data scanned significantly during a sync, increasing the cost of your sync.

Typically I don't recommend using the Airbyte destination tables directly in a lot of cases, where it makes more sense to use something like dbt or Dataform (or even scheduled or triggered queries) to materialize a read-optimized version of the data that is partitioned and clustered based on the needs of the use-case (and pre-aggregated to the appropriate granularity by default. Doesn't mean you can't use them, just that they're generally optimized for write-performance and not so much read performance. At least that's my experience.

Clustering has a similar problem. While you might be able to add additional fields, the key field (id in this case) really should be listed first along with _airbyte_extracted_at, because again for incremental syncs it's needed for efficient deduplication (and removing it could astronomically increase costs of the merge queries). And since clusters have to be used in order, clustering by anything beyond ID doesn't make nearly as much sense.

Which again, takes us back to the fact that most people would use a modeling tool to build reporting models that optimize for read.

Curious if you're already doing that with this or other sources, and why or why not.

(With that said, if the source is non-incremental, I do think it would be nice to be able to set partitioning and clustering fields, as now these aren't needed to be optimized for deduplication.)

[timorkal]

Hey @NAjustin , thank you for the quick and deep response.
As I am new to Airbyte and I am trying to use it as an alertnative to Google's DataStream to help me with my scenario.
My scenario contains a source Postgres (Cloud SQL) which has a big table of transactions with "id" as PK and "created_at" as a timestamp field.
I am trying to replicate/sync it into BigQuery so I can improve my read performance (I am using Metabase as the BI tool).
I saw that when the table is imported into BigQuery manually and the table is partitioned by DAY on "created_at" and also clustered by other fields (like: "account_name", etc...) I am getting good read performance, as partitioning is being used.
When I try to acheive the syncing using AirByte, because the output table is partitioned by ingestion time (aka "_airbyte_extracted_at"), all the records are getting the same ingestion day and are stored in the same partition, I assume that for the next day the ingestion time will be different but it doesn't help me too much as all the previous data was ingested in a single day.
As my Metabase queries are using the "created_at" field, my read performance is not improving via partitioning, and that's why I thought maybe there is already an option in AirByte to choose a different field for table partitioning.
I would be happy to know if you have other ideas, how to acheive my goal.