[CT-3431] [Feature] Add support for sql_footer

[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

Just like how we have sql_header - we should also support a sql_footer config. In that way, we could do something like:

# dbt_project.yml
...
models:
  my_dbt_project:
    +sql_footer: '{% if target.name != 'prod' %}limit 10{% endif %}'

And have that footer be applied to all models.

Describe alternatives you've considered

In the example above - to apply the same logic you'd need to either:

1. Go model by model and add it (same thing if you had a macro here - still need to copy and paste it into each model you want the logic to have).
2. Override the built in materializations.

Who will this benefit?

Anyone who wants to write less code - I would say the predominant use is to limit the rows just like in the example above.

Are you interested in contributing this feature?

yes

Anything else?

Should be quite straightforward I think - effectively we're going to do (2) on behalf o the users - i.e. add the equivalent of adding the headers here:

dbt-core/plugins/postgres/dbt/include/postgres/macros/adapters.sql

Lines 3 to 5 in c836b75

	{%- set sql_header = config.get('sql_header', none) -%}
	{{ sql_header if sql_header is not none }}

to the bottom - something like:

{% macro postgres__create_table_as(temporary, relation, sql) -%}
  {%- set unlogged = config.get('unlogged', default=false) -%}
  {%- set sql_header = config.get('sql_header', none) -%}
  {%- set sql_footer = config.get('sql_footer', none) -%}

  {{ sql_header if sql_header is not none }}

  create {% if temporary -%}
    temporary
  {%- elif unlogged -%}
    unlogged
  {%- endif %} table {{ relation }}
  {% set contract_config = config.get('contract') %}
  {% if contract_config.enforced %}
    {{ get_assert_columns_equivalent(sql) }}
  {% endif -%}
  {% if contract_config.enforced and (not temporary) -%}
      {{ get_table_columns_and_constraints() }} ;
    insert into {{ relation }} (
      {{ adapter.dispatch('get_column_names', 'dbt')() }}
    )
    {%- set sql = get_select_subquery(sql) %}
  {% else %}
    as
  {% endif %}
  (
    {{ sql }}
  )

 {{ sql_footer if sql_footer is not none }}
;
{%- endmacro %}

Probably other places I'm not considering.

[jtcohen6]

@jeremyyeo Thanks for opening, as always :)

I would say the predominant use is to limit the rows just like in the example above

I think we'd like to do this in a more opinionated way! Check out the conversation in:

• [CT-2967] [Feature] run dbt in --sample mode #8378

Are there any other use cases you could imagine for a more general-purpose (and less-opinionated) sql_footer?

[jomccr]

Thanks @jeremyyeo for opening this issue for me.

Since sql_header is meant for separate statements executed within the same session/connection vs pre_hook and post_hook, I don't think the use case of needing to limit models in CI fits perfectly.

I think sql_footer would need to run after the create or replace statement is complete, but within the same session to align with sql_header's behavior - which kills the possibility to use it for limit since it'd be outside of the model's SQL statement.

Not sure if that feature is really needed though unless you wanted to essentially reference a session parameter set with a sql_header within a post_hook.

https://docs.getdbt.com/reference/resource-configs/sql_header#comparison-to-pre-hooks

However, I like the idea of --sample in #8378.

On a side note - I think column level filters mentioned here might be overkill/better suited to dedicated jinja if-else at the end of the model definition since they're dependent on the structure of the model. It wouldn't violate DRY to add those filters in on a model by model basis.

Your point on partition pruning is spot on though so I'm not sure if I'm considering all the edge cases. I guess adapters could potentially implement custom sampling within partitioned objects/materializations? I'm kind of speculating at this point though, I'll leave it up to you folks 😄

[jtcohen6]

@jomccr Thanks for the response!

Could I ask what your specific use case for sql_footer is? It sounds like you want to run a SQL statement after the model's SQL statement (create/merge/etc), but within the same session/transaction.

That makes sense to me for sql_header: Before dbt compiles/previews/runs a model, it needs to set a session parameter or create a temporary UDF that should be bound to just this model's execution. I'm having a harder time imagining an equivalent need for sql_footer.

[jomccr]

I don't actually think a sql_footer is the right choice either - I asked a question on slack about if there was a way to limit every model to 100 rows, and the answer was that it's not currently supported. I think my request is encompassed by #8378 - this req is good to close.