How do I pass a list into a model and use it in a SQL IN clause? Rendering the variable directly produces invalid SQL

Strong second on the join-against-a-table option. I have watched a parameterised-list approach grow from three ids to a few hundred, at which point:

• the compiled SQL becomes unreadable in the logs
• some engines have a limit on the number of elements in a value list and you hit it
• the query planner stops using the index it was using at three ids
• nobody can tell what the last run actually filtered on, because the list was on a command line

A seed file with the ids in it solves all four and is version controlled, which means you can answer "what did we backfill in March" from the repository rather than from memory.

Rule I use now: a literal list in a query is fine when it is a handful of values that rarely change. Anything else belongs in a table.

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.

Whatever you build, look at the compiled output before running it. These tools all have a compile step that writes the final SQL to disk without executing it.

With templated SQL the gap between what you wrote and what runs is much larger than usual, and reading the compiled file once is the fastest way to find quoting problems, an empty list producing broken syntax, or a variable that was never actually passed and rendered as nothing.

It is also what you should paste when asking for help, rather than the template — most of the time the answer is visible in the compiled version immediately.

Add a test for the empty case specifically, because it is the one that reaches production.

The list is populated in every environment where anyone tested, and then one day an upstream step produces nothing, the variable is empty, and the model either fails on syntax or — much worse — silently filters nothing and rewrites the table with everything.

Which of those two happens depends on how you wrote the macro, and it is worth deciding deliberately rather than discovering.