[Feature] dbt-bigquery: option to skip listing every dataset at the start of a run #2209

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

At the start of every dbt run / dbt build, dbt-core calls create_schemas, which calls adapter.list_schemas() once per database. On BigQuery that ends up in connections.list_dataset, which pages through client.list_datasets() for the whole project.

For us that's the slowest part of starting a run, and I don't think it's needed. Our project has 10,842 datasets and any given run only touches a handful of them. I timed the exact call the adapter makes (list_datasets(max_results=10000)) and it took 13.7s and 18.7s on two runs, before dbt does anything useful. We've been running a fork for about a year and a half where list_schemas just returns [], and it's been fine. I'd love to stop maintaining the fork though.

Here's why I think it's safe on BigQuery. When a required schema isn't in the list, dbt-core calls create_schema for it, which runs create schema if not exists, so an existing dataset is left alone. The adapter also already caps the listing at max_results=10000, so on a big enough project the list is already incomplete today and dbt copes with it. That's us, actually. We're past 10,000, so the stock adapter never sees all of our datasets anyway.

The proposal is an opt-in profile setting, off by default:

my_profile:
  outputs:
    prod:
      type: bigquery
      skip_list_datasets: true

When it's on:

1. list_schemas returns [] without calling the API, so create_schemas ends up calling create_schema for each schema the run needs. create_schema checks whether the dataset exists first (one cheap list_tables call) and only runs the create schema query when it's missing.
2. _get_catalog_schemas checks each candidate schema with the existing check_schema_exists instead of building a set from list_schemas. Without this, dbt docs generate would skip every schema and produce an empty catalog, which is the one real bug in our fork.

Nothing changes when the setting is off.

Describe alternatives you've considered

• Fixing it in dbt-core, so create_schemas checks only the schemas it needs instead of listing everything. I think that's the cleaner fix long term, but it spans two repos and I figured a small opt-in in the adapter was easier to land first. Happy to go that way instead if you'd prefer.
• Querying INFORMATION_SCHEMA.SCHEMATA instead of the list API. That's region-scoped and still scans everything, so it doesn't really fix it.
• Keeping our fork. That's what we do now, and it means re-applying the patch every time we upgrade.

Who will this benefit?

Anyone on BigQuery whose dbt project shares a GCP project with a lot of other datasets. Per-PR or per-developer datasets pile up fast and would hit the same thing.

Are you interested in contributing this feature?

Yes. I have a PR ready with unit tests for both code paths and will link it here.

Anything else?

There is a tradeoff. With the setting on, every schema the run needs costs one existence check, and adapter.list_schemas() returns [], so any macro that relies on it would see nothing. That's why I'd keep it opt-in rather than the default. Not sure if there's a better name than skip_list_datasets, open to suggestions.