Seeing Snowflake’s Architecture through Modeling Notation

Throughout this book, relational diagrams have been used to support examples and illustrate ideas in ways that words could not. Although various modeling styles and notations have been introduced, a thorough overview of the visual semantics of modeling and the various elements and their properties has not been covered.

In this chapter, we will run through the complete visual toolkit that will enable users to accelerate the design and understanding of a physical Snowflake database and add functional context through conceptual conventions. Using a simplified and pragmatic approach, users of any background can view and explore a database schema at the level of detail that best suits their needs.

In this chapter, we’re going to cover the following main topics:

• Recall the history of modeling styles and notations
• Understand the connection between the relational model and its visual depictions
• Learn...

A history of relational modeling

The diagrams and examples throughout this book rely on relational modeling to illustrate database concepts and constructs. Learning how to parse and communicate through relational diagrams provides a dual-faceted benefit to database users by allowing them to rapidly visualize and bring to life complex database landscapes and design them from scratch using visual cognitive aids. Yet, despite these benefits, many database users consider modeling an arcane practice without relevance in the modern warehouse and mistake it for a chore instead of the time-saving boon it really is.

The practice of data modeling and its associated notations have passed through many changes and diverging trends without a final standard ever emerging, encumbering universal adoption. The conceptual data model dates back to the 1960s when Charles Bachman first used rectangles to denote record types and arrows to form relationships between them. Toward the end of the decade,...

RM versus entity-relationship diagram

Recall that an RM can be identified from business-relevant concepts and mapped onto a database schema that includes their attributes, identifiers, and relationships. An entity-relationship diagram (ERD) is a visual rendering of the RM using simple, easy-to-understand semantics.

In the following examples, we will revisit a set of relational entities from Chapter 1, Unlocking the Power of Modeling. In the RM, each of the three entities corresponds to a table identified by a primary key and related to other entities through foreign keys. Just observing the table names or even a data preview does not make it easy to determine these relationships, as can be seen in the following RM:

SUPERHERO
---------
SUPERHERO_NAME|BIRTHPLACE   |SUPERHERO_TYPE|HAS_MASK|
--------------+-------------+--------------+--------+
Ratman        |Gotham Gutter|Accident      |true ...

Visual modeling conventions

This chapter will highlight all the semantic elements that will be used throughout the rest of the book. As described earlier, no fixed and universally prescribed standard for drawing modeling diagrams exists. The conventions used here, along with their accompanying visual semantics, are the author’s attempt at simplifying the broad spectrum of modeling concepts and notations into a core set of elements, using the most widely used symbols to represent them. The visual semantics in this book do not conform to any single modeling notation but borrow freely from several leading industry standards. The aim is to provide a modeling language that is both precise enough to accurately reflect technical database details and simple enough for business users to understand the business context that lies beyond the technical specifications.

Unlike those textbooks that separate conceptual, logical, and physical models and force users to convert and adapt one...

The benefit of synchronized modeling

Throughout this chapter, we have seen examples of diagrams describing data models and capturing details that may not have a strict corollary in SQL. By simplifying modeling to its core components—many of which are likely to be understood even by people without a formal background in database design—we ensure that modeling is not confined solely to the database team. As you recall, a model describes not only the database but also the business itself.

Keeping the physical and conceptual models in sync provides another benefit: the model can be viewed at any time at various levels of abstraction because the underlying physical elements remain the same. Users can view the model at a physical level when more detail is needed and flip to a conceptual view if they wish to see it more abstractly.

To illustrate this, the various diagrams in Figure 6.2 are displayed side by side, demonstrating how physical and conceptual elements can be...

Summary

In this chapter, we looked back at the history of relational modeling and the various notation styles that have been used to help visualize the designs. Focusing on the most straightforward and practical semantics of these modeling styles, we could zero in on a pragmatic approach that preserves technical precision while being simple enough to be understood even by people without a formal background in database design. The modeling technique proposed in this book keeps the number of entities synchronized across conceptual, logical, and physical models, thus saving manual effort and ensuring that anyone at any level of detail can view the same model.

Using the consolidated list of modeling conventions, we looked at how to display an entity on a relational diagram in the simplest way possible. Next, we covered relationship notation and the additional context that can be conveyed beyond a simple line using two of the most popular conventions in database modeling: crow’...