I've been recently toying around with dynamically switching between DuckDB and BigQuery on query time. So you have a layer of standard SQL in front, which uses sqlglot to translate it to compute-specific SQL. So e.g. you can use DuckDB for development or low to medium-sized datasets, and switch to BQ for big datasets.

In theory, it sounds great, but in practice you indeed lose database-specific operators, you should restrain from using database-specific optimisations, etc...