Data Warehouse Naming Conventions: Best Practices and Examples

Quick answer: These data warehouse naming conventions work across warehouses:

• Case and separators: lowercase snake_case, underscores, no quotes, no spaces.
• Length: keep names under 63 bytes if PostgreSQL is in your stack.
• Reserved words: avoid order, group, user and date.
• Layers: raw source schemas, stg_ and int_ models, then fct_/dim_ or plural-entity marts.
• Grain: name tables for what a row represents: orders, order_items.
• Columns: use _id, is_/has_, _at for UTC timestamps, _date, and units like _in_cents or _ms.
• Versions and owners: no ad hoc v2, new, final or personal names in production; use dev schemas, git and dbt model versions.

Enforce all of it with SQLFluff and dbt-project-evaluator in CI.

Sometimes, the only thing standing between an analyst and a catastrophic management decision could be knowing the difference between the tables:

web_user_behavior_v2

and

web_user_behavior_new

Effective naming conventions for your data warehouses allow data analysts to do an exponentially better job, but it’s far from easy.

In this article, you’ll learn the best practices for data warehouse naming conventions and generate sample names to demonstrate them.

Why Data Warehouses Need Their Own Naming Conventions

First, it’s important to define what a data warehouse is. On the surface, an enterprise data warehouse (EDW) looks almost identical to a database: both are accessible via a structured query language (SQL).

But that’s where the similarities end; a data warehouse is fundamentally different from a database.

A database is responsible for managing, storing, retrieving, and updating data in a structured, tabular context. It often supports the automation of software applications’ activities and processes. In this context, a database is most relevant for software engineers.

On the other hand, when data analysts need data, they’re rarely interested in creating new records. Instead, their primary concern is consuming existing data to support the decision-making process.

That’s where the EDW comes in. It’s not suited for quick-write operations, but it’s excellent for reading data at mind-boggling speed.

But speed isn’t the only thing that matters.

For a data professional to be truly productive, they structure the data comprehensively: this is where naming conventions come into play.

When setting up data pipelines, it’s tempting for data engineers to add mental cues to tables and columns. But the truth is that business users don’t care about versions, partitions, or table owners, let alone cryptic internal acronyms.

For them, a table named wub_v2_date_johndoe is useless.

It would be best if you always created a data warehouse’s naming conventions for analysts’ productivity first and foremost.

Apply the rules from containers to columns across Snowflake, BigQuery, Redshift, Databricks and Postgres.

Naming Conventions for All Data Objects

Consistency

The single rule all analytics engineers and database architects should live by is consistency. Set naming conventions, and don’t ever deviate from them. That’s how you make it easy for analysts to find the data assets they need.

Without consistency, everything else is irrelevant.

Beginner-friendly naming

Corporate meetings and reports are bloated with acronyms. While acronyms might save experienced analysts a few keystrokes, it makes a beginner’s job unnecessarily difficult.

Only use approved acronyms that are expected to be known inside your organization.

Kimball’s Design Tip 168 recommends a documented list of dozens, not hundreds, of approved abbreviations, such as Amt and Desc.

Casing

This topic has generated considerable conflict among analysts. For example, Snowflake stores and resolves unquoted identifiers in uppercase, so id becomes ID, while double-quoted identifiers are case-sensitive and must be referenced exactly as created. BigQuery, on the other hand, treats dataset and table names as case-sensitive by default, so mytable and MyTable can coexist in one dataset, while column names are case-insensitive.

Amazon Redshift, PostgreSQL and Databricks Unity Catalog fold names to lowercase. But let’s be honest: SQL keywords are designed with uppercase in mind.

With this in mind, give your data objects a lowercase name if you have the choice. The humans reading the queries afterward will thank you for it.

Write lowercase without quotes; Snowflake’s uppercase display is harmless. If tools quote names, decide on QUOTED_IDENTIFIERS_IGNORE_CASE at account level early: changing it later can strand existing mixed-case objects.

Separation of words

Underscores, hyphens, spaces?

Microsoft’s tabular data ecosystem (SQL Server, MS Access, Excel) supports column names with spaces in them, and they can be referred to by using square brackets.

Snowflake supports it too, and space-separated column names can be referred to using double quotes.

BigQuery is split. Dataset names allow only letters, numbers and underscores, table names allow spaces and dashes, and standard column names allow only letters, numbers and underscores, although flexible column names accept more characters inside backticks. Amazon Redshift rejects spaces in standard identifiers, but delimited identifiers in double quotes can hold spaces, as long as every reference repeats the quotes.

The same goes for hyphens: most EDWs support them, but they have to be encapsulated. That means extra keystrokes, and every keystroke that doesn’t aid query descriptiveness should be avoided. The takeaway: use underscores.

Kimball prefers spaces for report labels. Keep those in the BI or view layer and physical names snake_case.

Reserved words

Snowflake reserves ORDER, GROUP, TABLE, CURRENT_DATE and CURRENT_USER, but not the ambiguous USER, DATE or NAME. Redshift requires quotes around group; BigQuery requires backticks around reserved identifiers. dbt recommends avoiding reserved column names.

• order: use order_id or ordered_at.
• user: use customer_id or user_name.
• date: use ordered_date or created_date.

Prefix generic words with what they describe, matching GitLab’s guidance for id, name and type.

Identifier Rules in Snowflake, BigQuery, Redshift, Databricks and Postgres

Naming conventions cannot override identifier limits. Amazon Redshift and Databricks also differ in case handling and permitted characters.

Warehouse	Max name length	Case handling	Quoting	Character restrictions	Docs
Snowflake	255 characters	Unquoted: uppercase; quoted: case-sensitive by default	Double quotes	Unquoted: letters, digits, _, $; starts with letter or _	Identifiers
BigQuery	Datasets: 1,024 characters; tables: 1,024 UTF-8 bytes; columns: 300 characters	Datasets/tables: case-sensitive by default; columns: insensitive	Backticks	Datasets: letters, numbers, _. Tables also allow spaces and dashes; spaces require quoting. Standard columns start with letter or _	Tables, datasets, columns
Amazon Redshift	127 bytes	Lowercase, including quoted names, unless enable_case_sensitive_identifier is on	Double quotes	Standard: letters, digits, _, $, multibyte UTF-8; starts with letter, _ or multibyte character; no spaces/reserved words	Names
Databricks Unity Catalog	Catalogs, schemas, tables: 255 characters	Objects stored lowercase; column casing preserved; queries case-insensitive	Backticks	Object names exclude periods, spaces, /, control characters and DELETE; hyphens require quoting	Names
PostgreSQL	63 bytes by default; longer names truncated	Unquoted: lowercase; quoted: case-sensitive	Double quotes	Unquoted: letters, digits, _, $; starts with letter or _	Identifiers

The tightest limit wins. With PostgreSQL in your stack, stay under 63 bytes to avoid truncation collisions.

• Kimball’s 30 to 35 characters is a report-readability target, not a platform limit.
• Legacy Databricks hive_metastore schema and table names allow only alphanumeric ASCII and underscores.
• BigQuery columns cannot start with reserved prefixes such as _TABLE_, _FILE_ or _PARTITION.

Naming Conventions for Databases, Schemas, Tables and Views

Compartmentalization

A data warehouse often contains data from many data sources, supporting users of all expertise levels. Compartmentalization can be achieved on the database(s) level or a level below.

• In Snowflake, this is referred to as a schema
• In BigQuery, it’s referred to as a dataset
• In Databricks Unity Catalog, it is a schema inside a catalog, addressed as catalog.schema.table.

There are use cases for both compartmentalization on a database or schema-level, and it often has to do with how a company is organized:

• malwart.sales.transactions_<further specification in table name>
• sales.transactions.<further specification by table name>

Layers: raw, staging, intermediate and marts

In an ELT context, when using a transformation tool such as dbt, the “stage” of the data object is also relevant.

Some tables only act as sinks for an extraction job.

Other tables are only intermediate because multiple output tables rely on them. These tables aren’t relevant for analysts, but they are for analytics engineers because the essential compartmentalization also represents the ordinality of the tables, even when they’re hidden from the final consumers of the data.

The common dbt pattern has four layers; Databricks medallion schemas offer an alternative organization.

Layer	What lives there	Naming pattern	Example
Raw (source)	Loader output, never edited by hand	One schema per source system	raw_stripe.payments
Staging	One cleaned, renamed model per source table, usually a view	stg_<source>__<entity>s	stg_stripe__payments
Intermediate	Reusable joins and reshapes analysts do not query	int_<entity>s_<verb>s	int_payments_pivoted_to_orders
Marts	Business entities at their grain, for analysts and BI	Plain plural entity, or fct_/dim_	orders or fct_orders, customers or dim_customers
Databricks medallion	Raw, validated/cleaned, then business-modeled data	bronze, silver, gold schemas	ops.bronze, ops.silver, ops.gold

In dbt staging names, the double underscore separates a multi-word source from its entity: google_analytics__campaigns. Staging and intermediate prefixes identify stepping stones rather than reporting tables.

Granularity

The granularity of a table refers to what each record represents.

In a time series, it could be an aggregation per second, day, or quarter. In retail, transactions and line items are very relevant, but it could be clicks, heartbeats, tickets, stars, etc.

The table name should reflect that granularity:

• sales.transactions.line_items: This table’s rows contain the individual line items of the transactions. Each row in the table represents a physical row on a receipt.
• sales.transactions.transactions: This table’s rows contain the individual transactions and represent a physical ticket.
• sales.transactions.returns: This table contains returned items (e.g. because they were broken, or because of the return policy).

dbt names marts at their grain; time rollups such as orders_per_day belong in a metrics layer instead.

Prefixes vs. suffixes

Traditionally, OLAP cubes worked with fact tables and dimension tables.

Modern ELT projects have not dropped the idea; they moved it into the name’s prefix, and they disagree about the marts. dbt Labs’ current marts guide names marts as plain plural entities such as orders and customers, while its own dbt-project-evaluator package still expects fct_ or dim_ on mart models by default. Both are fine. The rule that holds everywhere is this: prefixes mark the layer (stg_, int_, and fct_/dim_ if you use them), and suffixes mark the object kind.

On the other hand, views and materialized views often have a _v or _mv suffix.

Within a layer, using suffixes instead of prefixes ensures related tables are grouped when ordered alphabetically: orders, orders_mv and orders_v sort side by side.

Avoid department prefixes: dbt’s marts guide calls finance_orders versus marketing_orders an anti-pattern. Give genuinely different concepts distinct names, such as tax_revenue and revenue.

Singular vs. plural

Of all naming conventions, this is the only one where personal taste is the sole determinant.

While plural naming makes more sense in that a table’s records describe multiple data objects, singular naming results in more intuitive queries when the query engine requires you to specify the table name in some clauses.

For example:

SELECT customer.first_name, customer.city_of_residence
FROM sales.customer
WHERE customer.country_of_residence = 'Denmark'

dbt pluralizes models, while many SQL Server-era guides prefer singular names. Plural tables with singular query aliases, such as FROM customers AS customer, give you both. Apply your choice consistently.

Keep Versions, Owners and Environments Out of Table Names

Your naming convention should not turn web_user_behavior_v2, web_user_behavior_new or wub_v2_date_johndoe into change logs. A Dataiku community user inherited BigQuery processing-step names feeding Tableau: stacked, joined, prepared. The proposed _final suffix was, its author acknowledged, “merely one perspective.”

Keep these out of production table names:

• Versions: ad hoc v2, new and old leave consumers guessing.
• Status words: final, tmp and backup do not describe grain.
• Owners: johndoe belongs in ownership metadata.
• Dates of a one-off copy: date stamps identify copies, not entities.

Use these instead:

• Git: keep model-code history outside table names.
• A dev schema per developer: dev_jdoe identifies a workspace, not a production entity.
• Environment-level separation: separate databases or catalogs, such as sales_dev and sales_prod. This is common practice, not a vendor mandate.
• dbt model versions: reserve _v1 and _v2 for contracted versions, as dbt’s style guide allows, rather than ad hoc copies.

If analysts must see both versions, record the old version’s retirement date in the catalog.

Naming Conventions for Columns

Use customer_id consistently across tables, and distinguish coded categories from true/false flags.

• Relevancy: Only include meaningful columns. There’s no point adding 3 primary keys from various source systems; that will only confuse analysts or reduce trust in the data. If something isn’t relevant for analytical purposes, don’t include it.
• Standardization: Frustration is ensured when a column is called macroregion in one table and region_level_1 in another. That’s why a data object should have only one name; columns in different tables describing the same thing should be named identically. dbt’s style guide puts it the same way: a key to the customers table is customer_id everywhere, not user_id or id.
• Descriptiveness: Columns named weight, name, or city are useless outside the context of the table they’re in. Without knowing the granularity of the table, it’s impossible to know exactly what is weighed or named. Even then, a column named city could describe the city of residence, birth, delivery, etc. For this reason, reduce confusion by adding context: use body_weight, first_name, and city_of_residence.
• Decoded flags: Forcing analysts to use Boolean (1/0 or true/false) or numerical flags is a recipe for disaster. If your company sells 3 plans, is 3 the most expensive plan, or 1? Should 0 be included? What if a plan’s content changes: Is it still the same plan? It’s advisable to rename flags to meaningful values: “free plan,” “standard plan,” and “enterprise plan” can be understood by all.

Decoded flags target coded categories. A boolean is fine when is_ or has_ makes true unambiguous, as in is_refunded.

Column suffixes and prefixes

dbt Labs’ style guide recommends these column naming conventions:

• <object>_id for keys: order_id, customer_id. dbt recommends string-typed primary keys.
• is_ / has_ for booleans: is_refunded, has_coupon.
• <event>_at for UTC timestamps: ordered_at. Add a timezone suffix such as _pt for non-UTC values.
• <event>_date for dates: ordered_date.
• created_at, shipped_date: use past tense for events.
• price_in_cents: dbt recommends decimal currency, such as 19.99, and a suffix otherwise. Extend that habit to currencies and other units: amount_usd, weight_kg, duration_ms.

dbt orders columns as ids, strings, numerics, booleans, dates, then timestamps. The example follows that order; ::date is Snowflake, Redshift and Postgres syntax.

Renaming does not decode plan, convert timezones or change units; perform those transformations before exposing the model.

-- models/staging/shopify/stg_shopify__orders.sql
select
    id as order_id,
    customer as customer_id,
    status as order_status,
    plan as subscription_plan_name,
    subtotal as subtotal_in_cents,
    {{ cents_to_dollars('subtotal') }} as subtotal,
    checkout_time as checkout_duration_ms,
    refunded as is_refunded,
    created_at::date as ordered_date,
    created_at as ordered_at
from {{ source('shopify', 'orders') }}

A Worked Example: One Retail Warehouse, Named End to End

Apply these naming conventions to the malwart retailer’s Shopify orders and Stripe payments using Snowflake-style database.schema.table paths:

malwart.raw_shopify.orders                             -- loader output, untouched
malwart.raw_stripe.payments
malwart.staging.stg_shopify__orders                    -- view, one per source table
malwart.staging.stg_shopify__customers
malwart.staging.stg_stripe__payments
malwart.intermediate.int_payments_pivoted_to_orders    -- stepping stone
malwart.sales.orders                                   -- one row per order
malwart.sales.order_items                              -- one row per line item
malwart.sales.customers                                -- one row per customer
malwart.sales.returns                                  -- one row per returned item

On Databricks, use malwart.bronze, malwart.silver and malwart.gold, following the documented medallion example of ops.bronze, ops.silver and ops.gold.

The mart query uses a singular customer alias and the original Denmark filter:

select
    orders.order_id,
    orders.customer_id,
    orders.order_status,
    orders.order_total_usd,
    orders.is_first_order,
    orders.ordered_date,
    orders.shipped_at,
    customer.city_of_residence
from malwart.sales.orders as orders
join malwart.sales.customers as customer
    on orders.customer_id = customer.customer_id
where customer.country_of_residence = 'Denmark'

• Schema: raw_, staging, intermediate and the sales mart identify the layer.
• Table: orders versus order_items distinguishes an order from its line items.
• Column: _id, is_, _date, _at and _usd indicate meaning, type or unit.
• Production status: no name depends on who built it, which copy it is or when someone created that copy.

How to Enforce a Naming Standard

Check naming conventions in CI: SQLFluff catches identifier-style violations, while dbt-project-evaluator checks model prefixes.

• Write it down: keep casing, layer prefixes, column suffixes and approved abbreviations in the repo as part of your data management practices.
• Lint with SQLFluff: CP02 checks unquoted identifier casing through extended_capitalisation_policy; choose lower or snake. RF04 flags keywords, RF05 special characters, and RF06 unnecessary quoting. CP02 and RF06 can auto-fix. RF05 accepts additional_allowed_characters and allow_space_in_identifier for legacy schemas that need exceptions; leave both unset on a new project.
• Check model names with dbt-project-evaluator: fct_model_naming_conventions expects stg_, int_, fct_/dim_ and rpt_ by default. Its rationale is the same as this article’s: without a convention, anyone querying the warehouse can misread what kind of model a relation is. Override prefix lists through project variables for plain-entity marts. It checks model names, not columns.
• Review what tools cannot see: linters cannot distinguish customer_id from user_id semantically. PR review checks meaning; your catalog or data dictionary records it.

This minimal .sqlfluff uses Snowflake’s dialect. Change the dialect for other warehouses.

# .sqlfluff
[sqlfluff]
dialect = snowflake
rules = CP02, RF04, RF05, RF06

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

Run SQLFluff and dbt-project-evaluator in CI, and fail pull requests on violations.

Conclusion

Let’s summarize the core data warehouse naming conventions you’ve learned:

• Use descriptive naming that’s beginner-friendly
• Keep object names lowercase
• Use underscores as separators
• Compartmentalize domains and transformation stages in databases and schemas
• Object names should reflect their granularity
• Use prefixes for layers and suffixes for object kinds
• Only include relevant columns
• Standardize column names
• Contextualize columns to make them understandable outside their table
• Decode flags to meaningful values
• Avoid reserved words and characters requiring quotes
• Respect your stack’s shortest identifier limit
• Keep ad hoc versions, owners and environments out of production table names
• Enforce naming conventions with linting and review

You should now have a good understanding of best practices for data warehouse naming conventions.

While each naming convention practice we’ve discussed has its place, consistency is the most important. The other components should generally fall into place if you’re consistent with your data warehouse naming conventions.

Common questions

Should table names be singular or plural?

Either works if you apply it everywhere. dbt’s style guide pluralizes models, using names such as customers and orders. Plural table names with singular query aliases let you describe collections while keeping references such as customer.country_of_residence readable. Avoid switching conventions between related tables.

Should names be uppercase or lowercase?

Use lowercase snake_case without quotes. Snowflake folds unquoted identifiers to uppercase; quoted lowercase names become case-sensitive unless QUOTED_IDENTIFIERS_IGNORE_CASE changes that behavior. BigQuery dataset and table names are case-sensitive by default, so consistent lowercase prevents orders and Orders from coexisting accidentally. BigQuery column names are case-insensitive.

Do I still need fct_ and dim_ prefixes?

No, but document your choice. dbt’s marts guide uses plain entity names, while dbt-project-evaluator defaults to fct_ or dim_ for marts. Either approach works when applied consistently. Configure the checker to match your naming convention rather than leaving plain-name marts to generate avoidable violations.

How long can a table or column name be?

Snowflake allows 255 characters; BigQuery allows 1,024 characters for datasets, 1,024 UTF-8 bytes for tables and 300 characters for columns. Redshift allows 127 bytes; PostgreSQL defaults to 63 bytes before truncation. Unity Catalog allows 255 characters for catalog, schema and table names. Kimball’s 30 to 35 characters is a report-readability target, not a platform limit.

Can table or column names contain spaces?

Sometimes, but avoid them in physical names. Snowflake, Redshift and Postgres require double quotes around identifiers containing spaces. BigQuery allows spaces in table names but not dataset names; Unity Catalog forbids them in catalog, schema and table names. Put friendly, spaced display labels in the BI or view layer instead.

Where to next

Use the data warehouse guide to understand how a warehouse is built, then follow data dictionary best practices to record what each name means. Compare the platforms and transformation tooling behind these conventions on the Snowflake, BigQuery and dbt tool pages.