Apply macro to all models in dbt by default

I want to add a LIMIT 1000 statement to all my models for testing something. I could do it by either adding manually the LIMIT 1000 to all queries (on the first layer) or creating a macro that just makes the limit and add it to all the models (although this is very pointless).

However, I was wondering if there is a way to apply a custom macro for all dbt models by default, in the way generate_schema_name or get_custom_alias macros work

Solution

One way to accomplish so would be to override dbt's builtin ref() macro to return a select with a subquery where you add the limit 1000.

So, you would first create a macro in your macros/ folder:

-- macros/ref_override.sql

{% macro ref(model_name) %}

  {% set original_ref = builtins.ref(model_name) %}

  (
    select *
    from {{ original_ref }}
    limit 1000
  )

{% endmacro %}

And now, if you compile any of your models, you will see that the following happens:

-- compiled SQL using the ref() override above

with cte_name as (
  select * from 

  (
    select *
    from <your_db>.<your_schema>.<your_model>
    limit 1000
  )

),

...

Hope it helps!