InstantSQL caching on partitioned data sources

[loicalleyne]

Does InstantSQL have a way to avoid starting caching queries without a partition predicate on tables with partitioning? (ie. table in BigQuery using BigQuery extension)

[jraymakers]

No, InstantSQL does not have visibility into whether the tables in the query have partitioning. This is an interesting (and challenging) case to handle, though; we'll add it to the list for future consideration.

[loicalleyne]

In BigQuery when you create a table with partitioning there is the optional parameter to require a partition predicate when querying that table.
IMO having a "require partition predicate" option when creating a partitioned table and a "partitioned by" flag for scan_... would be the most straightforward way to safely use InstantSQL with partitioned data sources.

[jraymakers]

To make sure I understand, is the "require partition predicate" something that can be done (on the BigQuery side)? Is the "partitioned by" flag something that is possible today, or does that need some addition (to the DuckDB BigQuery extension)?

[loicalleyne]

BigQuery API has require_partition_predicate functionality however I'm not certain there's a way to surface that back through the Duckdb APIs, this would be a new generalized feature. I think that any large external data source that supports partitioning could stand to benefit from such a feature for use with InstantSQL - not only BigQuery but also Iceberg where any querying restrictions must be implemented in the query engine.