Appendix

Wouldn’t it be fitting if a book on modeling was itself modeled after something? Well, it has been! This book follows a spiral model, starting from a high-level overview of modeling and rising in winding loops, revisiting its core elements in ever-greater depth and complexity. This may seem like an unorthodox approach, but I believe that the difficulty of the technical how is alleviated by the understanding of the conceptual why. As such, I made it a point not to presuppose any modeling knowledge beyond a basic understanding of SQL and to never lean on a term or concept before formally introducing it.

Following a helical structure while writing this text allowed me to carve out a clear path from the theoretical to the technical and kept me from drifting into areas that were not core to the central theme. Inevitably, some exercises had to be excluded because they did not cleanly fit within the framework of the chapters. Rather than leave them on the proverbial cutting...

Technical requirements

The scripts used to instantiate and load the examples in this chapter are available in the following GitHub repo: https://github.com/PacktPublishing/Data-Modeling-with-Snowflake/tree/main/extras.

The exceptional time traveler

What changed? This a question that’s been asked since the first data manipulation language (DML) operations were performed on a database. While Snowflake’s built-in table change tracking and streams can help answer this question, they are not enabled on tables by default. However, even in Snowflake’s Standard edition, all tables have a default time travel data retention period of one day.

The time travel feature can be combined with the EXCEPT set operator to isolate and compare any changes made. The exercise uses a randomly generated filter when selecting which records to update to make things interesting. The only way to solve the mystery is to use Snowflake’s exceptional time-traveling powers.

The exercise is explained in the file titled 01_exceptional_time_travel.sql.

The secret column type Snowflake refuses to document

Snowflake’s co-founders and chief architects, Benoit Dageville and Thierry Cruanes, spent many years working at Oracle. In fact, Oracle’s influence can be seen in many of the SQL constructs and functions that Snowflake supports. One such example is the concept of the virtual column.

Virtual columns straddle the line between physical and transformational modeling—between table and view. Virtual columns look like normal table columns, but their values are derived rather than stored on disc. They are an efficient way to embed simple business rules and transformational logic in a table without the overhead of maintaining views and incurring storage costs. Virtual columns can be defined through constants or transformational expressions such as the DEFAULT column operator. Strangely, they are not mentioned in the CREATE TABLE documentation at the time of writing (https://docs.snowflake.com/en/sql-reference/sql/create...

Read the functional manual (RTFM)

Snowflake’s technical documentation is among the clearest and most informative I have ever encountered (virtual columns aside). Paying attention to the usage notes and best practices or simply scrolling down the list of available functions helped me elevate my SQL game and discover new features when I first started, and it continues to pay dividends. One such discovery came—putting aside pride—while reading the usage notes for ORDER BY (https://docs.snowflake.com/en/sql-reference/constructs/order-by). There, I learned about the NULLS FIRST and NULLS LAST keywords and how they override the default ordering of NULL when arranging in ASC or DESC. It’s a feature that has come in handy many times since. Most importantly, it serves as a reminder to check the documentation periodically to help spot new features and functionality.

See the example in the file titled 03_order_by_nulls.sql.

Summary

If you have made it this far, thank you. Truly. Writing this book could not have been possible without input from my peers throughout the data community and their thought-provoking modeling questions and suggestions. I hope this book and its further reading materials have given you a solid base from which to expand your modeling practice in Snowflake and beyond. If you have any questions, nifty tricks to include in the extras section, or just want to say hi, find me on LinkedIn (https://www.linkedin.com/in/serge-cloud-connected/).

Here’s to taking modeling from a lost art to an essential skill in the world of data together.