[CT-3150] [Implementation] allow source freshness to be calculated in batch, when using warehouse metadata tables

[graciegoheen]

Housekeeping

• I am a maintainer of dbt-core

Short description

From #7012

For sources marked for freshness via metadata, dbt should issue a single query instead of one per source. It would massively speed up compiling of source freshness.

It may also be possible to batch queries together to get performance improvements:

• source freshness checks using a loaded_at_field
• unit tests
• etc.

There's some precedent for this — that's the pattern dbt follows right now for docs generate. We know that can lead to significant time/memory consumption at scale, so we'd want this implementation to still respect node selection (--select): If I'm only checking the freshness for a handful of sources, filter down the information schema. Node selection is already supported with dbt source freshness, so we should maintain this behavior. https://docs.getdbt.com/reference/commands/source

Acceptance criteria

• For sources marked for freshness via metadata, dbt should issue a single query instead of one per source.
• We respect node selection (--select) for source freshness command (already supported today)

Impact to Other Teams

None

Will backports be required?

None

Context

No response

[graciegoheen]

Considerations during TR:

• Maybe they have some that are being checked via metadata, and others that are being checked by the loaded_at_field. Will depend on whether loaded_at_field is specified for the source/table.
• Frequent permission issues with querying information schema tables, especially on sources (not owned by dbt)
• If a batch query fails, and per-table queries succeed - we would want to gracefully handle, similar to how we're doing catalog generation during docs generate right now
• We'd want to filter down information schema queries, especially if there are <100 source tables in a schema with many thousands of objects. (That's a fine cutoff to start with.)

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#5276