Author here.
You're definitely right that how satisfied I am with leaving a sub-language inside a string literal is significantly a function of size and "staticness". A small sql string like
SELECT * FROM users
WHERE date_part('month', age(users.birthday)) = 0
AND date_part('day', age(users.birthday)) = 0
is probably fine to live in your host source file rather than a different one.
That said, I think it's also worth mentioning that Parser combinators and LR/PEG parser generators typically have very little dynamic data inputs to them, but do benefit significantly from being either a DSL/Library or living in a separate dedicated file. This progression from regex to Parser Combinator/Generator as a function of size and complexity can probably be generalized onto embedded languages writ large as to whether or not they're worth keeping in a string.
That said, I think it's also worth mentioning that Parser combinators and LR/PEG parser generators typically have very little dynamic data inputs to them, but do benefit significantly from being either a DSL/Library or living in a separate dedicated file. This progression from regex to Parser Combinator/Generator as a function of size and complexity can probably be generalized onto embedded languages writ large as to whether or not they're worth keeping in a string.