The failure is not from SDODS. It is from the auth provider, in the middle of the login step:
text
INVALID_EMAIL: the email address "${TEST_MEMBER_EMAIL}" is badly formatted.
So the pool leased an account whose username is the placeholder, as a string, and handed it to the login.
Both variables are in projects/mybotbox/.env.staging. That file is definitely being read — env.vars in the environment yaml references the same two variables and sdods config show -p mybotbox -e staging resolves them fine, values and all. So the dotenv layer exists and works, and then somewhere between there and the CSV it stops applying. Where?
Cause first: the dotenv layer that resolved your env.vars is not the scope the dataset cell is interpolated against, and an unresolved ${VAR} in a cell is kept verbatim without a warning. Two behaviours, and neither one is visibly wrong on its own.
.env files are parsed into a layer and merged into the resolved configuration — they are never injected into process.env. That is deliberate: it is what makes a shell variable beat a file, and it is why the precedence table has them as separate rows. Config interpolation uses the merged layer, which is why env.vars resolves.
The data provider does not see that layer, so a cell reference can only resolve against the real environment. Locally that is empty, so nothing resolves.
The second half is why it takes a day to find. An unresolved reference in a data cell is kept as written rather than throwing. That is the right default for a data file — a body of test text containing a literal ${ should not blow up a suite — but it means the value travels intact through the loader, the pool, the lease and the auth cache, and the first thing that objects is whatever finally consumes it. Here that is the auth provider's email validator, about a hundred frames from the cause, with an error that names their product and not ours.
CI is green because CI passes the variables as real env: entries in the job. They land in process.env, the provider finds them, the cell resolves.
The workaround that works today is to put the file in your shell before the run.
bash
set -a; . projects/mybotbox/.env.staging; set +a
bash
sdods run -p mybotbox -e staging -l ui -t @smoke
Status, because it matters for what you do next: the cause was that the merged dotenv layer was built for interpolating the yaml and then never handed to the data provider, so the provider fell back to bare process.env — which the config loader deliberately never writes to. Current data/provider.ts resolves through config.vars before that fallback, so if you are still seeing it you are on a build from before that change and set -a is your fix until you move.
Two things that make it survivable in the meantime:
Use the ${VAR:-default} form where a default is meaningful. A reference with a default always resolves, so it can never leak the literal — that is why the sample projects write passwords that way, and it is also why nobody hits this on the demo projects.
Where a default is not meaningful, fail early on purpose. A first step that asserts the username does not contain ${ gives you the error next to the cause instead of two layers down.
Adding the boring version of the same fix: an .envrc with the same exports, so the shell has them whenever you are in the directory and you never have to remember the set -a dance. Same mechanism, it just gets the values into the environment for you.
Does not help CI, but CI was never the broken one.
We hit this with a password rather than an email, which was worse — the auth provider returned a plain 401 and we spent two days convinced the account was locked. There is no signal at all when the literal is a password, because a wrong password and a placebo-shaped password fail identically.
The tell we go by now is any error containing ${. And a warning: sdods data preview is no help here. It shows the file, not the resolved row, so the output is identical whether the variables are set or not — which is correct behaviour for a file viewer and exactly the wrong tool for confirming this.
For anyone finding this from the CI direction rather than the local one: the same class of bug can point the other way. If a variable is set in your shell and only in your shell, local passes and CI leases a literal. Worth having one scenario that asserts the leased username matches an email shape, tagged @sanity, so whichever side is misconfigured says so in ten seconds.