Optional parameters working flow doubt

Get Help
1

Database - redshift

SELECT
    date_trunc({{granularity}}, time_stamp) AS "Time Range", 
    unique_calls,
    unique_callers
    
FROM call_metrics_{{group_by}}
[[WHERE 
    time_stamp >= {{start_date}}
    and granularity = {{granularity}}
    AND time_stamp < {{end_date}}::date + interval '1 day'
    AND 
        {{group_by}} = 1 AND client_name = {{client_name}}  -- Only filter by client_name when group_by = 1
        OR
         {{group_by}} = 2 AND client_name = {{client_name}} AND flow_id = {{flow_id}} ]]-- Filter by both when group_by = 2

For the above query , what i was trying was , even without the {{flow_id}} the query should give me results since if i select {{group_by}} = 1 then no need for {{flow_id}} . But sadly metabase is enforcing parameter validation before the query runs.

Hence i checked out the documentation available and got to know of the optional parameters and used [[ --]] as shown above.

Now the issue is i am getting way different values (and wrong values ) than i am supposed to get , so someone please explain me how the query above is iterating when -

  1. My {{group_by}} is 1 and I do not provide the flow_id
  2. My {{group_by}} is 1 and I do not provide the client_id
  3. My {{group_by}} is 2 and i do not provide the flow_id

Also please review as something is going wrong and i am not able to figure out what is going wrong.

My end goal - even without the flow_id the query must give me the right results.

2

Hello there, so i got to know about this from a fellow analyst, i am updating here so this might be useful for others also.

Here's how they work:

  1. Conditional execution: Content inside [[ ]] will only be included in the query if all variables referenced inside it have values provided.
  2. Skipping validation: Parameters inside these brackets aren't required for the query to run. If a parameter inside the brackets isn't provided, Metabase simply skips the entire clause rather than throwing an error.

This means:

  • If all parameters (start_date, granularity, end_date, group_by, client_name, and flow_id) are provided, the entire WHERE clause will be included.
  • If any parameter is missing (especially flow_id), Metabase won't throw the "missing required parameters" error - it will just omit the entire WHERE clause.

Possible fix -

Add the optional parameters only for statement where flow_id is present.

AND 
        {{group_by}} = 1 AND client_name = {{client_name}}  -- Only filter by client_name when group_by = 1
        [[OR
         {{group_by}} = 2 AND client_name = {{client_name}} AND flow_id = {{flow_id}} ]]