Add a mechanism for "wrapping" SQL across materializations

[drewbanin]

Feature

Feature description

dbt should make it possible/easy to wrap the model sql that gets executed in a materialization.

Use cases:

• inject a git hash and invocation timestamp into incremental models
• add where _fivetran_deleted is not null to all base models
• add limit 0 for only CI runs
• inject a comment to the top of every model (using vars)

Some questions:

• Where should this live?
• In each materialization?
• Or should it be handled by dbt?
• What's a tenable syntax for defining these wrappers?
• Can there be multiple levels of wrapping?

Implementation

In order for this to be really useful, users should be able to apply a wrapper macro to whole groups of models all at once. As such, I think a reasonable implementation is to specify a wrap_sql config in dbt_project.yml. This wrap_sql parameter can either be a string, or a list of strings, indicating the name of the macro(s) to call. Eg:

models:
  my_project:
    base:
      materialized: view
      wrap_sql: [filter_deleted, add_user]

Example macros:

Option 1: Supply the model as an argument

{% macro filter_deleted(model) %}

select * from (
  {{ model.compiled_sql }} -- or similar
) as sbq

where not sbq.is_deleted

{% endmacro %}

Option 2: Just supply sql as an argument

{% macro add_user(sql) %}

-- Run by: {{ target.user }}
-- example 2: 
{{ sql }}

{% endmacro %}

I'm not certain where the best place to call these macros is, nor do I have super strong opinions on the exact interface for these macros. Whatever we choose, we should be really explicit about what the context is :)

[drewbanin]

closing due to lack of interest

[kconvey]

Bummed to see this one closed! A use case I found for it is having a collection of models to run, which you sometimes want to apply a filter to, but it would be tedious to modify all of them (i.e. SELECT * FROM {{ model.compiled_sql }} as wrapped WHERE wrapped.id > 1000

[jtcohen6]

@kconvey I believe versions of this functionality are possible today through other more generic dbt constructs. The approach that comes to my mind leverages custom ref and source macros (docs).

For instance, if in one of your models you have:

with source_data as (
    select * from {{ source('fivetran_stripe', 'payments') }}
)

Normally this would compile to something like:

with source_data as (
    select * from fivetran_stripe.payments
)

But you could override the source macro to instead return it with a filter, given certain conditions:

{% macro source(source_name, source_table) %}

  {% set src_rel = builtins.source(source_name, source_table) %}
  {% set filtered_src %}
    (
        select * from {{ src_rel }} where _fivetran_deleted is not null
    ) {{ src_rel.identifier }}
  {% endset %}
  {% set final_src = filtered_src if var('filter_out_deleted', False) %}
  {% do return(final_src) %}

{% endmacro %}

Given those conditions:

$ dbt run --vars 'filter_out_deleted: True'`

The compiled SQL changes:

with source_data as (
    select * from (
        select * from fivetran_stripe.payments where _fivetran_deleted is not null
    ) payments
)