Flexible multiplexed sources

[jtcohen6]

See also: #6067, #6104 (comment), #5103

Proposal

Adapted from #6067 (comment):

Is there merit in the idea of a single "dynamic" source? Normally, dbt will validate that the source() you're asking for is actually one you've defined in your yaml files. (It actually doesn't validate that the table really exists in the database—again, because DAG nodes are defined independent on database state—a very good thing.) What if you could tell dbt, "Don't worry about this one?"

version: 2
sources:
  - name: my_dynamic_source
    database: raw
    schema: big_schema
    tables: *  # just imagining, could also be the word 'dynamic'

-- models/my_model.sql
select * from {{ source('my_dynamic_source', 'specific_table') }}

dbt would accept the second argument as the name of a table, and trust you that it exists:

-- compiled
select * from raw.big_schema.specific_table

Say you have another model, too:

-- models/my_other_model.sql
select * from {{ source('my_dynamic_source', 'another_specific_table') }}

In the DAG viz, you'd just see that both models depend on my_dynamic_source.*:

graph LR;
    my_dynamic_source.*-->my_model;
    my_dynamic_source.*-->my_other_model;
    style my_dynamic_source.* fill:#5fb825,color:white
    style my_model fill:#0094b3,color:white;
    style my_other_model fill:#0094b3,color:white;

Loading

Questions

Are we opening ourselves up for bad habits, whereby it's so easy to specify source.* that users never specify more-specific tables?

I don't think that's guaranteed to happen. By specifying specific source tables, you still get the abilities to:

• define and test columns
• add descriptions
• test source freshness

Really, this capability would solve for cases where the alternative is not using a source at all. And that's no worse.

What the heck will docs generate do, to generate a catalog for this table? Should we just try fetching metadata on the schema instead? This would require some lightweight development work in dbt-docs to feel copacetic.

Should it be possible to define specific source tables and flexibly reference additional ones? Should * be the default catch-all whenever there's a {{ source(..., ...) }} call to a source table not-yet-defined? (If we move in this direction, it's another good reason to maintain the clear distinction between source() and ref(), a la the proposal in #5910.)

[yang6n]

This will benefit the use cases where we want to use the tables for modeling without adding them into the source.yml manually (or via codegen), enabling more self-service possibilities.

[morsapaes]

For our particular case (Materialize), it isn't possible to know what tables will be created without pinging the upstream database, or waiting for the CREATE SOURCE statement to run and looking into the system catalog, so your proposal would work great! I totally get the concern about creating bad habits, though.

It's unfortunate that specifying a wildcard for tables also means that it's impossible to define properties for individual tables in that source altogether. For dbt test, is it correct to assume that this would block generic tests, but not singular tests that reference these trust-me-it-exists sources? Not breaking dbt docs sounds like the bigger problem!

[Fleid]

FYI, a potential follow-up is discussed at #7145