[CT-2700] [Feature] Support "dynamic" configs (i.e. retrieving state from database before parsing node configs)

[jeremyyeo]

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

There's already going to be similar threads around this topic so feel free to close in favour of others. Since I was not able to find one (and after some discussions with Jerco), I've decided to file this as an independent feature request.

Basically dbt should have a way to fetch some state information (think adapter.get_relation(this), some sql to check if a table has some value, etc) prior to determining configuration (enabled, snowflake_warehouse, etc) of a node.

A Snowflake example where we might want to switch up the warehouse based on whether it's the inception of the table or not.

-- snapshots/some_large_snapshot.sql
{% snapshot some_large_snapshot %}

{{ config(..., snowflake_warehouse = '{{ determine_warehouse(this) }}' ) }}

select ...

{% endsnapshot %}

-- macros/dw.sql
{% macro determine_warehouse(this) %}
  {% if adapter.get_relation(this) %}
   {{ return('small') }}
  {% else %}
   {{ return('large') }}
  {% endif %}
{% endmacro %}

Describe alternatives you've considered

Previous exploration in dynamically enabling a model using data from a table:

dbt-labs/docs.getdbt.com#1310

^ This won't work with true config() like snowflake_warhouse.

Who will this benefit?

Use cases above but bound to have plenty that fall into "check the state of a thing before setting configs of things".

Are you interested in contributing this feature?

No response

Anything else?

From jerco:

run_query / get_relation (anything that requires a database query) don’t actually run at parse time, they just return None / [] / etc
Configs are evaluated at parse time using those None values. (Only exception: pre/post hooks wrapped in an extra set of curly braces)

[jtcohen6]

There is a fundamental order of operations to dbt: It must be possible to know the shape of the DAG & configuration of its nodes—what's enabled, what's materialized as what—simply by parsing configuration and project code, before running any database queries (dynamic state). This foundational assumption gets us a lot of mileage.

I totally hear the many (advanced) use cases for this. Rather than dynamically setting configs during parse time, for configurations that are actually applied at runtime, I'd rather we made it possible to override (define custom behavior for) the mechanism for applying those configs at runtime. In the case of snowflake_warehouse (dbt-labs/dbt-snowflake#103), something like:

-- snapshots/some_large_snapshot.sql
{% snapshot some_large_snapshot %}

{{ config(..., snowflake_warehouse = 'dynamic' ) }}

select ...

-- macros/snowflake_warehouse.sql
{% macro snowflake_warehouse(snowflake_warehouse, node) %}
  {% if snowflake_warehouse == 'dynamic' %}
      {% set already_exists = adapter.get_relation(node.database, node.schema, node.identifier) %}
      {% if already_exists %}
       {{ return('small') }}
      {% else %}
       {{ return('large') }}
      {% endif %}
  {% else %}
      {{ return(snowflake_warehouse) }}
{% endmacro %}

[jtcohen6]

I appreciate having this open as a standalone feature request! I'm going to close it as wontfix.

Other approaches for enabling the same use case:

• Making more runtime behavior configurable at runtime, via macros that can be overridden (my preference)
• Making more configs "re-rendered" at runtime, similar to pre/post hooks — but acknowledging that such behavior has proven very tricky for many users

[JulianHammerton]

I appreciate having this open as a standalone feature request! I'm going to close it as wontfix.

Other approaches for enabling the same use case:

• Making more runtime behavior configurable at runtime, via macros that can be overridden (my preference)
• Making more configs "re-rendered" at runtime, similar to pre/post hooks — but acknowledging that such behavior has proven very tricky for many users

Macro does seem the better way to solve this. A call such as this worked for me:
snowflake_warehouse=set_dynamic_table_warehouse()