dbt Macro - Include/exclude field in WHERE clause when does/does not exist

Viewed 27

I'm working in dbt, and I'm new to Jinja and defining macros. I have an is_deleted_field that does not exist in every source table that I need to create a snapshot of.

For sources where the IS_DELETED field exists, I want the WHERE clause to output something like...

WHERE 1=1
  AND NOT IS_DELETED
  AND IS_CURRENT

...and for sources where the IS_DELETED field does not exist the WHERE clause should output...

WHERE 1=1
  AND IS_CURRENT

But, when I run my current working snapshot for a base model containing the IS_DELETED field I get this as well:

WHERE 1=1
  AND IS_CURRENT

I'm including the working macro for my dbt snapshots below for reference.

{% macro snapshot(source_model, is_deleted_field='IS_DELETED', current_only=True) %}

{#
  source_model     | snapshot model ref
  is_deleted_field | name of the field that marks whether a record was deleted
  current_only     | Boolean to include current records (True), or include all history (False)
#}

  SELECT
    {{ dbt_utils.star(from=source_model, except=['DBT_VALID_TO']) }},
    COALESCE(dbt_valid_to, {{ var('constants')['OPEN_END_DATE'] }}) AS dbt_valid_to,
    COALESCE(dbt_valid_to, {{ var('constants')['OPEN_END_DATE'] }}) = {{ var('constants')['OPEN_END_DATE'] }} AS is_current
  FROM
    {{ source_model }}
  WHERE 1=1

    {% if is_deleted_field %}

    AND NOT {{ is_deleted_field }}

    {% elif is_deleted_field.none %}

    NONE

    {% endif %}

    {% if current_only %}

    AND is_current

    {%- endif %}

{% endmacro %}
0 Answers
Related