Analytics workflows for column-oriented databases
1. Analytics workflows for column-oriented databases
So far, we've explored the fundamentals of column-oriented databases and some of their more advanced techniques. Now, it's time to focus on how these tools are used to power analytics workflows.2. Common table expressions with Snowflake
Common table expressions are a popular technique to make queries more readable when working with traditional SQL databases. Luckily, we can leverage all the benefits of common table expressions with Snowflake. Common table expressions, or CTEs, can be thought of as named sub-queries or temporary tables defined using the WITH keyword. CTEs create an object that can be later queried, reducing the amount of data that is being queried or JOIN'ed downstream. CTEs are more modular than directly writing sub-queries and are easier to troubleshoot when something goes wrong. On the right is the general form of a CTE. The CTE starts with the WITH keyword, followed by a name and AS. In the parentheses, a query is provided. The output of this query will be available via the name provided earlier. Finally, a query is written and executed against the named sub-query. Let's take a look at an example!3. Writing common table expressions
On the left, a CTE is written to store the results of data about books with a price greater than twenty-five dollars in the premium books temporary object. Then, the premium books object is used to find the minimum and maximum average reviews for each author. Here, only one temporary object is created using the WITH keyword. However, this syntax can be extended to create multiple temporary objects. To do this, a comma is added after the first CTE is defined. After this comma, an additional name and query can be provided, separated by the AS keyword. Using this pattern, multiple temporary objects can be created in a single common table expression and referenced later in the query.4. Views with Snowflake
In addition to common table expressions, Snowflake also provides functionality to create and query views. Similar to working with traditional SQL databases, views allow query results to be accessed like a table. Snowflake supports both materialized and non-materialized views, which we'll take a closer look at next. Views are defined using the CREATE VIEW keywords, followed by the desired name of the view. The AS keyword and query to populate the view follow, completing the view definition.5. Creating views with Snowflake
On the left is a non-materialized view, titled premium books. This view contains the title, author, and average reviews from the books table for all books with a price greater than twenty-five dollars. Since this view is non-materialized, the query executes every time the premium-books object is called. Non-materialized views should be thought of as "named definitions" of a query and help more with organization within an analysis rather than performance. On the right is a materialized view. The only difference between these two definitions is the keyword MATERIALIZED. When the view definition is executed, the results are stored as a table. This leads to better performance but requires refreshing as the underlying data source, in this case, the books table, changes. When deciding between a non-materialized and materialized view, it's important to keep this trade-off between performance and data recency in mind. If data is frequently updated, it's best to use non-materialized views to ensure data returned by the view is up-to-date. However, if data is slowly-changing, materialized views will certainly do the trick.6. Let's practice!
Excellent! Now that we've explored how common table expressions and views are used to streamline analytics workflows, it's time to practice with a few exercises!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.