How should development and production write to different schemas without two copies of the configuration?

Worth adding: whatever convention you land on, make the target visible in the run output and in anything that reports on the project.

The worst version of this is somebody spending an afternoon confused about why their changes are not showing up, because they were building into one schema and querying another. It is invisible unless something tells you.

Most tooling will print the target at the start of a run. Get people in the habit of reading that line, and put the target in the name of any dashboard or notebook that reads from these tables.

The point about production credentials being ones developers do not have is the one I would act on first, ahead of any of the naming work.

Every version of this problem I have seen resolved by convention alone eventually produced somebody running the wrong command with the wrong profile selected. It is not carelessness, it is that the two commands look identical and the difference is invisible until afterwards.

The fix is that the production credential lives only in the scheduler's secret store. A developer physically cannot write to production from a laptop because they do not hold anything that can.

Once that is true, everything else here is about tidy names rather than about safety, and you can take your time over it.

The concatenation is deliberate and it is the safety feature you are currently trying to remove, so it is worth understanding before overriding it.

The default behaviour: the schema a model is built into is the target schema — the one configured for whoever is running — with the model's custom schema appended. So with a target schema of dev_sibel and a custom schema of marts, you get dev_sibel_marts.

That is what makes your development runs safe. Every developer has their own target schema, so every developer's build lands in their own namespace, and the grouping is preserved inside it. Two people can build the same model at the same time without collision, and neither can touch production.

If custom schemas were used literally, everyone building marts would write to the same marts — which is exactly the overwriting problem you started with, just moved.

So the design is:

• Target schema = who and where. Set per environment and per developer, in the connection profile, not in the project.
• Custom schema = logical grouping. Set per model, in the project, committed.

Production is the case that needs the override. In production you want the clean names — marts, not prod_marts — and that is achieved by overriding the macro that generates the schema name. The standard implementation is a conditional: in the production target, use the custom schema alone; everywhere else, use the default concatenated behaviour.

Write it that way round rather than the reverse. If the override defaults to the clean name and only concatenates for known development targets, then a target nobody anticipated writes straight into the production namespace. The safe default is the one that isolates.

Getting the environments separated, which is the more urgent half of your question:

• Every developer has a distinct target schema, typically derived from their name. This is per-person configuration and must never be committed.
• Production uses a separate target, ideally with separate credentials that developers do not have, and ideally in a separate database entirely rather than only a separate schema.

That last point is worth pushing for. Schema separation is a naming convention; database separation with distinct credentials is enforcement. Right now the only thing preventing you from overwriting production is that you have not run the wrong command yet.

A related trap: if two models in different groups have the same name, they can collide once the grouping is stripped in production. Enforce unique model names across the project — most teams do this with a naming convention that includes the layer, which also makes the lineage readable.

If you have the option, consider giving each developer their own database rather than their own schema within a shared one.

It makes the isolation total, it makes cleanup trivial — drop the database — and it removes the whole class of problem where a permission granted for convenience quietly makes the separation notional.

Depends what your warehouse charges for and how it handles cross-database queries, so it is not always available. Worth checking before you commit to the schema-prefix approach, because migrating later is more disruptive than choosing now.