Generally agree, but consider the counter-example of regular expressions, where the purely in-language notation is so much more verbose than putting a string-literal of a minilanguage in the larger language. Usually, we don't need to use the larger language to dynamically generate elements in the minilanguage (most regexes are string literals).
One must consider the cost of developing/maintaining new language parsing infrastructure (main point of the blog) against the readability/conciseness/features of a new syntax (not discussed in the blog).
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.
One must consider the cost of developing/maintaining new language parsing infrastructure (main point of the blog) against the readability/conciseness/features of a new syntax (not discussed in the blog).