[SPIKE] Experiment with performance of catalog queries

[dbeatty10]

Housekeeping

• I am a maintainer of dbt-core

Short description

For dbt-bigquery and dbt-snowflake, experiment with different settings for relation_count:

• how does it affect the performance of the catalog query over a range of values?
• is there a point at which the query becomes to big to be accepted for execution?
  • can we determine the size of the query to be submitted so as not to exceed the 1 MB limits set by Snowflake and BigQuery

Acceptance criteria

We can see a graph of the performance to run the catalog query (in seconds) on the y-axis vs. the number of selected nodes on the x-axis.

We'd generally expect it to look like one of the curves below (ideally the constant time blue one, but I'm guessing not 😉):

Impact to Adapters

Depending on the results of the experiment, we may choose to use different values for relation_count in dbt-bigquery and/or dbt-snowflake. Alternatively, we may choose to change our implementation in some way.

Context

The work was initially performed in #8521 / #8648.

Then #9394 expressed expectation that we'd get the benefits of #8648 even if more than 100 nodes are selected.

[ChenyuLInx]

@dbeatty10 why only bigquery and snowflake?

[ChenyuLInx]

Another dimension to consider: how many objects are in the schema.

[dbeatty10]

@ChenyuLInx yeah, it makes good sense to do both bigquery and snowflake. And also consider the number objects within the schema. 👍