Database Naming and Structure

In previous chapters, we took a database design through the necessary steps to transform it into a logical model. While a logical model is database-independent, it is close enough to a physical design that it can easily be adapted and deployed to any database. However, before tackling the Snowflake-specific properties of the data model—which will be covered in the following chapter—we should get acquainted with naming conventions and database organization best practices that govern all database designs.

Naming conventions are the guiding principles with which database objects are constructed. Consistent naming standards reduce uncertainty for developers and help business users orient themselves within a database and find the required data assets. Beyond object naming, this chapter will also cover the structure of the database itself, organizing it into logical groupings by schema, which improves usability. Finally, we will look at database...

Naming conventions

Before creating the physical model, naming conventions that govern its design need to be established. Following consistent naming conventions improves understanding, reduces errors, facilitates collaboration, and generally makes it easier to work with your database. While there are many (often conflicting) theories and standards on the right convention to follow, the most important thing is to choose one that is easy to understand and to use it consistently throughout your database.

However, there are some general best practices to keep in mind when naming objects in Snowflake. After all, object names are like the API to your data model and should be regarded as a contract between the modeler and the data consumers. Once an object is created, downstream systems, users, and processes will reference it by name, forming dependencies and increasing the cost of future changes.

This section will cover some of the most crucial considerations in database naming. Instead...

Organizing a Snowflake database

The data objects stored by Snowflake—optimized and compressed in an internal columnar format—are not directly visible nor accessible by customers; they are only accessible through SQL query operations. The customer only manages the logical grouping of database objects into schemas and databases. As described in Chapter 3, Mastering Snowflake’s Architecture, in Snowflake cloud architecture, data is shared virtually without needing to be physically replicated. Therefore, unlike traditional database platforms, the database structure in Snowflake is less concerned with the colocation of physical data and more with the logical grouping of objects—allowing for simple discovery and fine-tuning of access controls.

What does this look like in practice?

Organization of databases and schemas

All Snowflake objects are assigned to a schema upon creation and form a logical hierarchy from object to schema to database. This tiered...

Summary

In this chapter, we saw how naming standards and an organized structure make the database easier to use and facilitate maintenance. But every organization is different and must choose the standard that best suits their needs and aligns with existing conventions.

Internally, Snowflake stores object names as uppercase, and its query compiler converts all unquoted names accordingly. It is recommended to use snake case for naming and stick to an established pattern to maximize the results cache utilization and to avoid enclosing every column and table name in double quotes.

For a clean transition between logical and physical models, singular table names are encouraged. The same applies to columns, which should be named consistently across the entire database. Using descriptive naming patterns for foreign keys allows users to preserve logical relationship names within the physical model.

After object naming, the attention turns to database organization through logical schema...