Getting dbt Cloud to throw an error if a selection did not include any models #dbt

README.md

Getting a dbt Cloud job to throw an error if a selection did not include any models

dbt 1.4 onwards supports the --warn-error-options flag or DBT_WARN_ERROR_OPTIONS env var where you can now raise an error during NoNodesForSelectionCriteria so this should no longer be required.

1. Add the following three macros to your project:

{% macro pre_run() %}

  {% set query %}
    create or replace table {{ target.database }}.{{ target.schema }}.dbt_meta__selection_checker (
        run_id int,
        models_run int
    );

    insert into {{ target.database }}.{{ target.schema }}.dbt_meta__selection_checker values (
        {{ env_var('DBT_CLOUD_RUN_ID') }},
        0
    );
  {% endset %}

  {% do run_query(query) %}

{% endmacro %}


{% macro post_run() %}

  {% set query %}
    select models_run 
      from {{ target.database }}.{{ target.schema }}.dbt_meta__selection_checker
     where run_id = {{ env_var('DBT_CLOUD_RUN_ID') }};
  {% endset %}

  {% set results = run_query(query) %}
  
  {% set results_list = results.columns[0].values()[0] %}

  {% if results_list == 0 %}
    {{ exceptions.raise_compiler_error("Something went wrong with your selector - no models were included in the run.") }}
  {% else %}
    {{ log(results_list ~ " model(s) ran successfully.", info=True) }}
  {% endif %}
  
{% endmacro %}


{% macro log_results(results) %}

  {% if execute %}

    {% set models_ran = results | length %}

    {% set query %}
      update {{ target.database }}.{{ target.schema }}.dbt_meta__selection_checker
         set models_run = {{ models_ran }}
       where run_id = {{ env_var('DBT_CLOUD_RUN_ID') }};
    {% endset %}

    {% do run_query(query) %}

  {% endif %}

{% endmacro %}

2. Make sure you have an on-run-end hook to update the dbt_meta__selection_checker table (which will not run if no models were in our selection - this is the main reason for the two other run-operation macros):

# dbt_project.yml

...

on-run-end:
  - "{{ log_results(results) }}"

3. Then in your dbt Cloud job, make sure to have a dbt run-operation step before and after your dbt run step that executes the appropriate macro (pre_run and post_run). For example:

dbt run-operation pre_run
dbt run --select my_model
dbt run-operation post_run

If your selector was correct and models were included in the run, your run should have a successful state:

But if your selector had a typo and no models were included in the run, your run should have an error state:

---

If you run locally with dbt CLI, you can simulate this by exporting a fake DBT_CLOUD_RUN_ID environment variable (dbt Cloud jobs on the other hand will generate this environment variable for you):

export DBT_CLOUD_RUN_ID=123
dbt run-operation pre_run
dbt run --select this_doesnt_exist
dbt run-operation post_run