Hi 👋 Airbyte creates partitioned tables in BigQuery destinations. This is a bad practice as transactional tables most often are not big enough to benefit from partioning. Is it possible to turn this thing off?

I would disagree about this being a bad practice; it actually tends to be ideal. The reason is that the tables are ingestion-time partitioned (on

_airbyte_extracted_at

at daily granularity), which allows for a few important things: • Partition Pruning: When you only need a certain date range of data, by filtering on that column you'll eliminate pruned partitions from needing to be scanned. This saves cost and improves performance, and is especially ideal if you're building downstream incremental models off the tables. • Long Term Storage (LTS): After 90 days of inactivity (meaning no writes or other DML/DDL queries), a table (or partition) goes into LTS. This cuts the cost of storage in half. Having a single table being written would mean it would never go into LTS, and you'd pay more for storage Even with smaller tables, there are almost no real downsides to partitioning in BigQuery regardless of table size since the table metadata is shared across partitions (unlike with table sharding where you have duplicate metadata). I suggest you study up on BigQuery internals—it works very differently than other SQL engines you might be used to. On another note, the tables Airbyte produces are designed for its purpose of moving the data, and are almost never ideal to query directly in BI scenarios. Most of us use tools like Dataform or dbt or other data pipeline tools to handle transformations to optimized reporting tables where we can apply the most relevant optimizations for our workloads.

Hey @Justin Beasley thanks for the reply! BigQuery does provide extensive documentation on partitioning. One of their recommendations is not to create partitions of a total size less than 10 GB. It's a rarity in OLTP to have tables of such size to benefit Airbytes' partitioning, and therefore this indeed is a bad practice. Please referr to this link: https://docs.cloud.google.com/bigquery/docs/partitioned-tables

While it's true that it has to track the partitions, the only matters for the metadata portion (not the actual data query portion). This is a much smaller part of the query lifecycle and in my testing is rarely even detectable. The exceptions are cases where the planners spend a lot of time analyzing partitions before sending an execution plan to the workers. While AI will tell you it's a bottleneck, I've only actually observed this behavior once on an Airbyte table with some other unique characteristics. Since Airbyte doesn't know what the table/partition size will be in advance (or how it will change over time), I suspect that they're optimizing to handle the worst case scenario since the overhead of partition tracking is generally minimal. But this all falls apart if you're trying to query the Airbyte tables directly (e.g. through a BI tool). This won't be efficient, and you'll compound any planner lag exponentially. It really only makes sense to put some type of data modeling downstream from Airbyte in your pipeline (be that Dataform, dbt, triggered/scheduled queries, whatever). While I wouldn't mind an option to be able to set the partition strategy/granularity, I don't know that this lines up well with the way Airbyte's destination configs currently work (which is at the destination connector level). Without a way to override at the connection (or even stream) level, I don't think it would be sane to add that to the destination connector. So I wouldn't expect anyone to bite on a feature request for this until there would be some type of UI to support it.

Thanks again for taking the time. Do you perhaps know how to turn this thing off? I also noticed Airbyte creates cluster on primary key and extracted_at. Which is once more a bad practice as id doesn't need to be sortable and by no circumstances extracted at should be considered as a cursor value for downstream transformations.

it does this because if you set it to be incremental+deduped (whether at the time of creation or later on), it will be doing a lot of targeted overwriting based on the keys. So this speeds up those lookups significantly. Again, a tool like Airbyte has to optimize for flexibility and having to re-write the table if the user changes a setting is an expensive operation. And if you're doing downstream transformations (which you control), does it matter? Is there a performance issue you're actually observing? In our case, our models determine what is appropriate and optimal for the modeling of each system and for the performance and access dynamics of how it's used in our system. That's the purpose of the models, not of the ETL/data movement tool (IMHO). I love being pedantic about system internals as much as the next guy, but at a certain point you have to accept that any tool is going to impose a set of standards—often for good reason—but that won't be bespoke for your exact scenario (and may contain some level of trade-offs). The only way around that is to do custom work for every single ETL connection—which is what a lot of us were doing before, and why Airbyte exists in the first place. If we want full control, we can keep doing it the other way . . . but I'd argue there's a lot more value of spending our time downstream and letting tools handle the data movement piece.

I had a simple question. Now noone will reply.

Simple answer: No, it's not currently possible to disable partitioning or clustering for the BigQuery destination.