The hand-built string is the common answer and it is not wrong, but wrap it in a macro once rather than repeating it, and be aware of what it costs you.
Why the direct render fails. The templating layer substitutes the representation of the value. A list renders with brackets and the language's own quoting, which is not SQL syntax. The template engine has no idea it is producing SQL — it is string substitution, and that is the whole reason this class of problem exists.
The idiomatic fix: a macro that turns a list into a SQL value list. Something along the lines of taking the list, quoting each element for the target dialect, and joining with commas — then call it wherever you need it. Written once, used everywhere, and it gives you a single place to fix quoting when you meet a dialect that disagrees.
Handle these cases inside the macro or it will surprise you in production:
- An empty list.
IN ()is a syntax error on most engines. Decide what empty means — usually "match nothing", so emit a condition that is always false, or better, make the macro emit a whole predicate rather than just the parenthesised list so it can choose. - Numeric versus string. Quoting numbers works on some engines and not others, and silently changes plan choice on a few. Either branch on the element type or have two macros.
- Quotes inside the values. If any element can contain an apostrophe, naive quoting produces broken SQL at best. Escape it.
That last one is the reason your instinct that it looks fragile is correct: you are building SQL by string concatenation, which is the shape that produces injection bugs. Inside a transformation project the inputs are usually your own configuration rather than user input, so the risk is lower — but if any of these values can ever come from outside, do not do this.
Two alternatives that are frequently better than parameterising at all:
Join against a table instead of filtering with a literal list. If the set of ids is large or changes often, put it in a seed or a small model and write where id in (select id from ...). Now the set is versioned, testable, visible in the lineage, and there is no string building anywhere. This is the right answer more often than people expect, and it is the one I would try first.
Use a filter that reads from configuration rather than a list literal. For the backfill-versus-daily case specifically, what you usually want is a date or batch predicate rather than an id list — the list is often standing in for "the rows from this run", and expressing that directly is simpler and much faster on a large table.
On invocation: passing a list on the command line means passing it as structured data, and quoting it correctly through your shell is its own small nightmare. Putting the variable in the project configuration and overriding it only when necessary is considerably less painful, and it means the default is committed and reviewable rather than living in somebody's shell history.