I think more analogous to the examples Kay was giving would be for a single program to quietly change the rules for function application, or to quietly change the behavior of CLOS.
I think of an embedded DSL, based on a macro like it is here, as a bit different than that, because it's not doing anything quietly -- it applies only to the parenthesized extent of the code that begins with your macro name:
(my-dsl-macro my dsl fills the rest of the parentheses)
People who don't already know what `my-dsl-macro` is see the documentation or code that says it's a macro. Even were it a normal function, people reading the code should probably know what the function does, so maybe they have to glance at where the IDE automatically displayed the syntax or documentation for them, anyway.
Ideally, your editor will also color the macro names differently, especially for readers who don't know what's a macro or a function. (Rust adds an exclamation mark to the macro name for a use, which is a good idea when you don't already know what's macros, but maybe a bit annoying to have all those exclamation marks when you do know that, say, `println` is a macro.) But even if you don't know the name is a macro, you'll be clued in if the text within it doesn't look like your top-level language, which it often doesn't (e.g., SQL in s-expressions doesn't look like base CL).
A key is to use DSLs judiciously -- for improved readability (for your base language programmer, or domain experts), maintainability, and/or performance. Maybe not for, say, a convenience for the sake of minor code terseness improvement only.
Of course, SQL is a whopper of a language, and perhaps overkill if you invented it as a DSL for this particular application. (More suspicious would be to invent your own relational query language that seems gratuitously different than SQL, when SQL already existed.)
As for changes more like I think Kay was talking about, in the Racket (Lisp family) universe, outside of a macro, normally you wouldn't do that, but when you have a good reason -- say, you want to prototype a lazy language, or implement a specialized language for a GPU programming backend, or a DSL for your domain experts -- you can. In that case, you'd have a `#lang` line at the very top of the file that tells you what different language this is, instead of `#lang racket`. You might make that produce modules that can interoperate with modules of other `#lang`s, but you're not doing anything sneaky that breaks the language of those other modules.