See it as a diagram
Everything below, as a diagram you can edit. Describe yours and see it in seconds.
No account needed · Editable canvas, not a picture
Name the layer in the container, the entity in the table
The layer belongs in the schema name: raw, staging, int, marts. The environment belongs one level higher, in the database or the catalog. Repeating either of them in the table name produces names like prod_marts_dim_customer_prod that are long, wrong after the first clone, and impossible to grant on cleanly.
Table prefixes then carry the role rather than the location. stg_ for one-to-one cleaned source models, int_ for intermediate models that exist only to make a later model readable, dim_ and fct_ for the dimensional core, and a stable name with no prefix or a mart_ prefix for published wide tables. A reader who sees fct_order_lines knows the grain question applies and that dimensions attach to it.
Source-derived names should keep the source system in the staging layer and lose it afterwards. stg_salesforce__accounts is useful because it says where the data came from and what still needs integrating. dim_customer is correct precisely because by then the customer is no longer a Salesforce customer, it is the conformed one.
A convention that other tools can read is worth more than a private one. Datadef infers the medallion layer of a table node from its name alone, and the vocabulary it looks for is short: bronze, raw, or landing means bronze; silver, conformed, cleansed, staging, or stg means silver; gold, curated, mart, certified, or business means gold. Those are the words the rest of the ecosystem uses too, which is the argument for picking them over house terms like refined or trusted that nothing else recognises.
The rules that survive a second team
Pick singular or plural once and never revisit it. dim_customer or dim_customers are both defensible; a warehouse containing both is not. The same applies to whether the layer prefix uses one underscore or two before the entity.
Ban abbreviations except from a short published list. cust, ord, and amt each save four characters and cost a lookup every time somebody new reads a query. The exceptions worth keeping are the ones the business itself says out loud, such as mrr or sku.
Encode types in column suffixes: _id for natural keys, _key or _sk for surrogate keys, _at for timestamps, _date for dates, is_ or has_ for booleans, _amount for money with the currency stated in the description. This is the part of a convention that pays back the most, because it removes the most common class of query bug, joining a natural key to a surrogate key.
State the timezone rule once, globally. Timestamps stored in UTC with an _at suffix and any local variant named explicitly, such as created_at_local, removes an entire category of quiet reporting errors.
Deprecation needs a name too. Renaming a table that consumers read is a breaking change, so the pattern that works is to create the new name, keep the old one as a view marked with a removal date in its description, announce the date, and delete on it.
analytics_prod <- environment lives here
raw/ <- landed, untouched
salesforce_accounts
staging/
stg_salesforce__accounts <- source named, one to one, cleaned
stg_billing__invoices
int/
int_customers_joined <- exists to keep marts readable
marts/
dim_customer <- conformed, source no longer named
dim_date
fct_order_lines <- grain: one row per order line
obt_sales_daily <- projection, single consumerA convention nobody enforces is a preference
Enforcement has to be automatic or it becomes the most tiring review comment in the team. A CI check that fails when a model in the marts directory does not start with dim_, fct_, or obt_ takes an hour to write and removes the conversation permanently. SQL linting handles the rest, including keyword case and column ordering, which are conventions people argue about far past their value.
Grants get easier as a side effect. When every published table in a schema follows one prefix rule, permissions can be granted at schema level with future grants, and a table that ends up in the wrong place is visible because its name does not match its neighbours.
Write the convention as a page of rules with one example each, not as prose. The document that gets followed is the one somebody can scan in ninety seconds while naming a table.
Naming shows up in the diagram, which is where mistakes get noticed
A diagram generated from the repository uses the real names, which makes a violation visible in a way a file tree does not. A node labelled marts.customer_stuff sitting next to dim_customer and fct_order_lines is obvious on a canvas and invisible in a directory listing.
Connect the repository that holds the dbt project or the DDL, pick a branch or tag, and Datadef regenerates the diagram and an architecture.md daily from the source, with zones per schema so the layer structure is part of the picture. Because node identity derives from the object name, a rename shows up as a change in the diagram rather than as a silent reshuffle, and nodes you moved by hand keep their positions.
The parser has an opinion about names too, and it is one worth stealing. When a node id is built from a Terraform address, the resource name is included only when it says something: this, main, default, and existing are dropped, because a box labelled storage account this tells a reader nothing that storage account did not already tell them. The same test works on tables. If removing the name from the label loses no information, the name was never doing any work.
For the same reason, put the convention page and the diagram in the same place. Reviewers who can see both are the ones who catch a name before it reaches production and becomes a breaking change to rename.
FAQ
Should the environment be part of the schema name?
What table prefixes are worth enforcing?
Should the source system appear in a table name?
What column suffixes prevent the most bugs?
How do you rename a table that consumers already read?
How do you stop a naming convention from being ignored?