How to Define a Metric Filter Using Variables

Help
,
1

The problem I’m having

I’m trying to write a .yml file to define some metrics. I want to filter out certain records by a specific column (or dimension) using dynamic values before dbt computes the metrics. However, these values are not predefined, so I’d like to pass them as variables when I run the metrics (e.g., dbt run --vars {“ids”: “‘a’, ‘b’, ‘c’”}).

The values can be a list of strings concatenated into a single string, such as “‘a’, ‘b’, ‘c’”. Then, when dbt executes the metrics, I want the generated SQL query to look like this:

SELECT ...

FROM ...

WHERE id IN ('a', 'b', 'c')

Is there a way to define a filter using the IN operator and dynamic values from variables? Or is there a recommended workaround?

The context of why I’m trying to do this

The filter values are not predefined.

What I’ve already tried


metrics:
  - name: positive_ratio
    description: "the positive rate"
    label: "the positive rate"
    type: simple
    type_params:
      measure:
        name: positive_ratio

  - name: count_fully_agreed
    description: "Count the number of records unanimously labeled as normal"
    label: "Count fully agreed"
    type: derived
    type_params:
      metrics:
        - name: positive_ratio
      expr: "sum(case when positive_ratio = 0 then 1 else 0 end)"
    filter: |
      - field: id
        operator: 'in'
        value: ({{ var('ids') }})

Some example code or error messages

Filter:
    - field: project_id
      operator: 'in'
      value: ({{ var('ids') }})
Error Message:
    Error while parsing Jinja template:
    - field: id
      operator: 'in'
      value: ({{ var('ids') }})
Traceback:
    File "/venv/dbt-sha-5957145fa911a118d4f40567e65b5dbc6cebb4ca/lib/python3.11/site-packages/dbt_semantic_interfaces/implementations/filters/where_filter.py", line 130, in filter_expression_parameter_sets
      where_filter.call_parameter_sets(custom_granularity_names=custom_granularity_names),
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/venv/dbt-sha-5957145fa911a118d4f40567e65b5dbc6cebb4ca/lib/python3.11/site-packages/dbt_semantic_interfaces/implementations/filters/where_filter.py", line 53, in call_parameter_sets
      return WhereFilterParser.parse_call_parameter_sets(
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/venv/dbt-sha-5957145fa911a118d4f40567e65b5dbc6cebb4ca/lib/python3.11/site-packages/dbt_semantic_interfaces/parsing/where_filter/where_filter_parser.py", line 46, in parse_call_parameter_sets
      descriptions = WhereFilterParser.parse_item_descriptions(where_sql_template)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/venv/dbt-sha-5957145fa911a118d4f40567e65b5dbc6cebb4ca/lib/python3.11/site-packages/dbt_semantic_interfaces/parsing/where_filter/where_filter_parser.py", line 39, in parse_item_descriptions
      raise ParseWhereFilterException(f"Error while parsing Jinja template:\n{where_sql_template}") from e

06:51:35 Encountered an error:
Parsing Error
  Semantic Manifest validation failed.