Analytics workflows for column-oriented databases

Introduction to NoSQL

Jake Roach

Data Engineer

Common table expressions with Snowflake

Common table expressions (CTEs):

  • Named sub-queries/temporary tables, defined using the WITH keyword
  • Creates a object that can be later queried
  • Reduce the amount of data that is being queried and/or JOIN'ed
  • More modular, easier to troubleshoot
WITH <cte-name> AS (
    SELECT
        ....
    FROM <table-name>
    [JOIN | WHERE | ...]
)

SELECT
    ...
FROM <cte-name>;
1 https://docs.snowflake.com/en/user-guide/queries-cte
Introduction to NoSQL

Writing common table expressions

WITH premium_books AS (
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price > 25.00
)
SELECT
    author,
    MIN(avg_reviews) AS min_avg_reviews,
    MAX(avg_reviews) AS max_avg_reviews
FROM premium_books
GROUP BY author;
  • Creating a premium_books temporary object
  • Using premium_books downstream

$$

Can creating multiple temporary objects:

WITH 
    <first-name> AS (...),
    <second-name> AS (...),
    ...
...
;
Introduction to NoSQL

Views with Snowflake

Views:

  • Allow query results to be accessed like a table
  • Non-materialized and materialized
CREATE VIEW <view-name> AS
  SELECT
      ...
  FROM <table-name>
  [WHERE | JOIN | ...];
Introduction to NoSQL

Creating views with Snowflake

CREATE VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;
SELECT * FROM premium_books;
  • Query executes when premium_books is called
  • "Named definition" of a query
CREATE MATERIALIZED VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;
SELECT * FROM premium_books;
  • Results are stored upon execution
  • Better query performance, requires refreshing
Introduction to NoSQL

Let's practice!

Introduction to NoSQL

Preparing Video For Download...