Data warehouses and Snowflake

Two kinds of work: OLTP feeds, OLAP asks

The deck opens with two acronyms (p. 483):

Picture a chain of electronics stores in Rome and Milan. Each till is an operational site: all day it runs short transactions — scan, pay, update the stock — each touching a few rows found by their key. Head office asks how many keyboards each region sold last quarter: a question that reads every sale of the quarter, but only a few columns of each. "OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it" (p. 484); the slide draws the loop that joins them, from transactions to the warehouse, to analytics and decisions, and back to the business strategy.

Why not run both kinds of work on one database? Because they grow along different axes, as the slide "Challenges of Scale Differ" draws (p. 485, Fig. 1).

Two slides list the differences. Read each row as a consequence of the access pattern: a till looks up one product by its primary key, so OLTP keeps an "Index/hash on p.k." and only the "Current snapshot"; head office scans a year of sales, so the warehouse is built for "Lots of scans" and keeps "History". Even the schema follows: many narrow, normalised tables for OLTP (lesson 9), few wide ones for the warehouse — section 4 shows why.

A yin-yang closes the comparison (p. 506): "OLTP, Operational System, Data Entry" against "OLAP, Data Warehouse, Data Retrieval" — data enters through one half and comes out, analysed, through the other.

OLTP runs many short transactions on the current state; OLAP runs few long scans over history. They grow along different axes, so they live in different systems.

Copy it in advance: what a data warehouse is

The chain's data sits in many places — tills, web shop, loyalty cards, suppliers' spreadsheets — each with its own codes and its own idea of a "product". Page 487 names the problem: "Integrated Data Analytics", "Coherent Insights Extraction", "Heterogeneous and distributed data sources". The deck shows two ways to answer a question across such sources (Fig. 2).

The traditional approach is "Query driven", "Lazy", "On-demand" (p. 488): an integration system splits each question, sends a piece to every source through a wrapper — an adapter that speaks the source's language — and merges the answers, helped by metadata on what each source holds. The warehousing approach turns this around: "Information integrated in advance", "Stored in dwh for direct querying and analysis" (p. 490). An extractor/monitor at each source extracts its data and detects changes, the integration system merges them, and the result is loaded into the data warehouse that clients query. Lesson 4 met the same choice as "virtual ETL" against ETL (p. 175).

The reasons: the copy is fast because it is local and pre-integrated, and stale because it is only as fresh as its last load; it does not interfere because the long scans run on the copy; it keeps history because it is yours — the till overwrites the stock level, the warehouse keeps every sale. The right column is where copying in advance fails: data or sources that change too fast, too much data, questions nobody can foresee.

Two definitions to learn

Inmon's four adjectives are the ones to learn, and pp. 495–496 unfold them. Subject-oriented: "Organized by subject, not by application" (p. 495) — one Customer subject assembled from tills, web shop and loyalty cards. Integrated: many sources brought to one format and meaning; p. 495 calls the warehouse "A solution to data integration problem". Time-varying: every record carries its time, so the warehouse holds history, not only the present. Non-volatile: what is loaded is not overwritten. Page 496 prints these two together: under "Non-volatile" it lists "Historical" and "Time attributes are important", then "Updates infrequent" and "Maybe append-only" — data that is never overwritten piles up as history. Its examples are all history: "All transactions ever at a bank", "Complete client histories at insurance firm", "Financial information and portfolios of customers" — the app holds your balance, the warehouse every movement since you opened the account.

A data warehouse is a subject-oriented, integrated, time-varying, non-volatile collection of data used for decision making, integrated in advance from many sources.

Inside the warehouse: layers, tiers, ETL and marts

The "Generic Warehouse Architecture" (p. 497) draws the warehousing approach bottom-up: sources, an extractor/monitor each, an integrator, the warehouse with its metadata, query and analysis, clients. Its grey labels — "Design Phase", "Loading", "Maintenance", "Optimization" — name the jobs around it, and p. 500, "Issues in Data Warehousing", lists them as problems: warehouse design; extraction, with "Wrappers, monitors (change detectors)"; integration, "Cleansing & merging" (extraction and integration together make the loading); optimization; maintenance, which keeps the copy in step with changing sources.

Pages 498–499 see the same thing as layers of data:

• Single-layer: "Every data element is stored once only" — a "Virtual warehouse": operational and informational systems read the same "Real-time data".
• Two-layer: "Real-time + derived data", the "Most used approach in industry": operations read the real-time data, analysts a derived copy.
• Three-layer: "Transformation of real-time data to derived data really requires two steps". First the reconciled data, integrated and cleaned — the "Physical Implementation of the Data Warehouse"; then the derived data at the "View level", for "Particular informational needs".

The reconciled layer makes reports agree: every derived view starts from the same cleaned base. Read "really requires" as the argument for it, not as a law — the previous slide calls two layers the industry's usual choice.

Who reads the derived data? Decision support systems, "Information technology to help the knowledge worker (executive, manager, analyst) make faster & better decisions" (p. 501), with sample questions such as "What were the sales volumes by region and product category for the last year?", "How did the share price of comp. manufacturers correlate with quarterly profits over the past 10 years?" and "Which orders should we fill to maximize revenues?". "On-line analytical processing (OLAP) is an element of decision support systems (DSS)". Their architecture has three tiers (Fig. 3).

The arrow into tier 1 reads "extract, transform, load, refresh, etc." (p. 502): ETL — extract the data from the sources, transform it to the warehouse's format and meaning, load it, refresh it periodically. (Loading raw data first and transforming it inside the warehouse is often called ELT — not a slide term, but how Snowflake loads in section 6.) Tier 2 has two kinds of OLAP server (p. 503). ROLAP, a "Relational specialized DBMS to store and manage warehouse data" plus "OLAP middleware to support missing pieces", keeps the cube virtual, in tables queried with SQL. MOLAP, with "Array-based storage structures" and "Direct access to array data structures", stores the cube as an array addressed by the dimension values.

Last, scope (p. 504). An enterprise warehouse "collects all information about subjects that span the entire organization". Data marts are "Departmental subsets that focus on selected subjects", like the "Marketing data mart: customer, product, sales" — "Faster roll out, but complex integration in the long run", since separate marts end up defining "customer" differently. A virtual warehouse is "views over operational DBs", "Easy to build but require excess capability on operat. db servers".

ETL — extract, transform, load, refresh — fills the warehouse; data marts are its departmental subsets; ROLAP keeps the cube in relational tables, MOLAP in arrays.

The star, the snowflake and the cube

An operational schema is a network of entities and relationships; p. 508 draws it as an "ER Diagram" next to the warehouse's "Star Schema", one central box with the others around it. Page 509 draws the star in the course's ER notation (Fig. 4).

The centre is the fact, the event being measured: a sale. The rays are the dimensions, the points of view from which you analyse it. Page 510 adds the table of the fact — Sale, with CodProd, CodStore, CodPromo, CodTime underlined and the bullets Amount, Qty — and five lines:

• "Attributes all over": measures on the fact, descriptions on the dimensions.
• "FACT is an aggregate": a row is not one receipt line but the total for one combination of product, store, promotion and time — the grain chosen at design time.
• "FACT has composed key (from dimensions)": CodProd, CodStore, CodPromo, CodTime, each a reference to a dimension; Amount and Qty are the measures.
• "FACT is normalized (BCNF)": the only dependency goes from the whole key to the measures (lesson 9).
• "DIMENSION is NOT normalized": Store keeps city, region and country together, although CodStore → City → Region is transitive.

Why keep the dimensions un-normalised, after lesson 9? Because users never update them: only the batch load writes them, and it changes a city's region in every row at once, so the update anomalies of lesson 9 do not bite; the repetition costs little, since dimension tables are small; and every question saves joins. This is p. 505's "De-normalized table structure": a narrow, very long fact table among wide, short dimensions.

Normalise them anyway and the star becomes a snowflake schema, "Hierarchies for not normalized dimensions" (p. 512): Store points to City, City to Region, Region to Country; time becomes Day, Month, Year; Product gets its Producer and Category (Fig. 5). Page 511's pictograms explain the name: a star's dimensions all touch the fact, a snowflake's branch.

The price is joins: "sales by country" takes one join in the star (Sale with Store) and four in the snowflake. The gain is explicit hierarchies and a little space — little, because a dimension is tiny next to the fact table. And the snowflake schema shares only its name with Snowflake, the product of section 5.

Operations on the cube

Now see the facts as points in a space whose axes are the dimensions. Page 514 draws the cube of the store chain: Store (Roma-1, Roma-2, Milano-1, Milano-2) by Product (PCs, Mouses, Keyboards, TVs) by Time ("1 trim. 2003" to "4 trim. 2003"), with an arrow labelled Quantity on one small cube. Each of the 4 × 4 × 4 = 64 cells holds the measure of one combination: one row of the fact table. Page 513 defines the operations:

From sales by region and month, a roll-up removes a dimension — sales by region, each total the sum of that region's months; a drill-down adds one — by region, month and category, finer groups, same grand total. A slice fixes one dimension to one value; a dice keeps ranges of values on several. Pages 516–519 shade them on the cube (Fig. 6).

Each operation edits one star query: join the fact to the dimensions you need, filter and group on their attributes, sum the measures. The first question of p. 501, with 2003 as the last year:

-- p. 501: sales volumes by region and category, last year
SELECT st.Region, p.Category, SUM(s.Qty) AS Qty
FROM Sale s
  JOIN Store st ON s.CodStore = st.CodStore
  JOIN Product p ON s.CodProd = p.CodProd
  JOIN Time t ON s.CodTime = t.CodTime
WHERE t.Year = 2003
GROUP BY st.Region, p.Category;

Roll-up and drill-down change only the GROUP BY; slice and dice only the WHERE. Build the star yourself and watch it happen.

A star is one narrow fact table, dimension keys plus measures, among wide dimension tables. Every OLAP question joins the fact to the dimensions it names; roll-up and drill-down edit the GROUP BY, slice and dice the WHERE.

So old school? Snowflake's three layers

After the cube comes ".. So old school …" (p. 520), and the deck switches to a product. Its key features (p. 521): "Enterprise-ready cloud data warehouses that automatically scales for balancing performances and costs"; "Separation between compute and storage" — "Other databases combine the two together, meaning you must size for your largest workload and incur the cost that comes with it"; "Single place for storing all the data", one copy for every workload.

The middle one is the key. In a classic warehouse cluster every machine holds a share of the data and the processors that work on it. To have enough processors for the Monday-morning rush you buy machines that idle the rest of the week, and adding processors means moving data onto them. Separated, storage and compute each grow, shrink or stop on their own. Page 523 draws the two traditional designs, p. 524 Snowflake's (Fig. 7).

Snowflake takes one idea from each. The slides draw it; Snowflake's documentation names it, "a hybrid of traditional shared-disk and shared-nothing database architectures". Its layers, bottom up:

• Database Storage (p. 525): "Data in Snowflake is organized into the internal optimized, compressed, columnar format"; Snowflake manages "Organization, file size, structure, compression, metadata, statistics", and the data is "visible or accessible by customers only through SQL query executed in Snowflake". A column store (lesson 20) behind SQL: no indexes to build, no files to tune.
• Query Processing (p. 526): "Snowflake processes queries using virtual warehouses", each an "MPP compute cluster composed of multiple compute nodes allocated by Snowflake from a cloud provider". "Virtual warehouses are independent compute cluster and does not share compute resources with other virtual warehouses" — "No impact on the performance of other virtual warehouses".
• Cloud Services (p. 527): "Services that coordinate activities across Snowflake for processing user requests, from login to query dispatch": "Authentication", "Infrastructure management", "Metadata management", "Query parsing and optimization", "Access control". The VPC box around the layers on p. 524 is the cloud provider's private network.

Page 522, "Why is data warehousing important?", lists what a managed cloud warehouse adds: "Consistency", "Security and Data Protection" (multi-factor authentication, single sign-on, TLS), "Connectivity" (Python and Spark connectors, Node.js and .NET drivers), "Fault Tolerance". Separation pays most when teams share data: p. 542 gives the data scientists DATASCIENCE_WH and the DBAs DBA_WH, two warehouses over the same transactions table, and p. 545 builds a multi-cluster AS_WH for a changing load. The widget puts a team of analysts on AS_WH: that assignment is this lesson's scenario, not the slides'.

Storage is one shared, columnar copy; compute is any number of independent virtual warehouses that can be resized, multiplied or suspended without touching the data.

Loading and tuning: stages, Snowpipe, warehouses, caches, clustering keys

Clients connect through a web interface, command-line clients, ODBC and JDBC drivers (Tableau), native connectors for Python and Spark, and third-party ETL and BI connectors (p. 528). Snowflake loads CSV, TSV, JSON, Avro, ORC, Parquet and XML, compressed or not (p. 529); the slide also lists Amazon S3, Google Cloud Storage and Microsoft Azure among the "Types of data", but they are places to load from, not formats. Two ways in (p. 530):

A bulk load has four steps (p. 532): prepare the files, stage the data ("Make snowflake aware of the data"), execute COPY, schedule regular loads. A stage is "an intermediate, transient place used to process data for extracting, transforming and loading processes" (p. 533), classically an S3 bucket or an Azure blob store, "Long term cheap storage for raw data"; in Snowflake such an external stage is a named pointer to that place, written @name. COPY INTO then loads the files (p. 534, with the names of pp. 536–537):

-- load the stage's CSV files: '|'-separated, one header line
COPY INTO transactions
  FROM @snowpipe_copy_example_stage
  pattern = '.*.csv'
  file_format = (type = csv field_delimiter = '|' skip_header = 1);

pattern is a regular expression on file names; for JSON the format is file_format = (type = json). COPY's transformations are deliberately basic — the rest is SQL inside the warehouse, the ELT order of section 3. To leave files where they are, an external table queries them in place, "Useful when there is a lot of data externally but we want to query only a small subset of data", sped up by materialized views (p. 531). Fig. 8 compares the two ways in.

Snowpipe is a "Mechanism to enable loading of streaming data" with a "Serverless architecture": "It has its own processing", "No virtual warehouse instances involved" (p. 535). A pipe is a stored COPY that Snowflake runs whenever a new file is announced, set up in four steps: "Stage data", "Validate copy command", "Create Pipe", "Configure cloud event or use REST APIs". The hands-on of pp. 536–540, in run order:

1. Create the database and make it the current one (p. 536).

   CREATE DATABASE ingest_data;

   USE DATABASE ingest_data;

2. Create an external stage on the S3 bucket, then list the files Snowflake can now see there.

   CREATE OR REPLACE STAGE snowpipe_copy_example_stage
   url='s3://snowpipe-streaming/example_table';

   LIST @snowpipe_copy_example_stage;

3. Create the target table (p. 537).

   CREATE TABLE transactions
   (
     Transaction_Date DATE,
     Customer_ID NUMBER,
     Transaction_ID NUMBER,
     Amount NUMBER
   );

4. Create the pipe: the COPY of p. 534 wrapped in CREATE PIPE … AS; auto_ingest = true makes it listen for cloud events.

   CREATE OR REPLACE PIPE transaction_pipe
   auto_ingest = true
   AS COPY INTO transactions FROM
   @snowpipe_copy_example_stage
   file_format = (type = csv field_delimiter = '|'
     skip_header = 1);

5. Read the address (ARN) of the pipe's queue: in Snowflake's documentation it is the notification_channel column (p. 540).

   SHOW PIPES;

6. In the AWS console, "Amazon S3 > bucket_name > properties > events" (p. 538): a notification "Snowflake Notification" for "All object create events", prefix transactions, sent to the "SQS Queue" with that ARN — "Insert here your snowpipe queue id" (p. 539).

7. Upload a file and count the rows: "Now if a new file is uploaded on S3, the data arrives automatically on Snowflake" (p. 540) — with no COPY typed and no warehouse running.

   SELECT COUNT(*) FROM transactions;

Keeping it fast

With no indexes and no files to tune, five levers remain (p. 541): "Use dedicated virtual warehouses for different workloads", "Scale up for know large workloads", "Scale up with virtual warehouses for unknown workloads", "Design the production workflow to maximize cache usage", "Use cluster keys for partitioning large tables". Dedicated warehouses first (p. 542):

-- virtual warehouse for data scientists
CREATE WAREHOUSE DATASCIENCE_WH WITH WAREHOUSE_SIZE = 'SMALL'
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

AUTO_SUSPEND = 300 suspends the warehouse after 300 idle seconds, so it stops costing; AUTO_RESUME = TRUE restarts it at the next query. The DBAs get DBA_WH, XSMALL "because of queries size". Privileges complete the pattern (pp. 542–543): the PUBLIC role may use the database and its schema and read the table (GRANT USAGE, GRANT SELECT); each team's role may use its warehouse (GRANT USAGE ON WAREHOUSE … TO ROLE …); a user such as DS_1 gets that role and warehouse as defaults.

For a workload whose load changes, pp. 544–545 create a multi-cluster warehouse:

-- 1 to 3 XSMALL clusters, started as queries queue
CREATE WAREHOUSE AS_WH
WITH WAREHOUSE_SIZE = 'XSMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;

Its requirements are "a minimum and maximum number" and the "Enterprise Edition of Snowflake" (p. 544); SCALING_POLICY = 'STANDARD' starts a cluster as soon as queries queue (Snowflake's documentation). What is automatic here is suspend and resume, and the number of running clusters between the two counts; a warehouse's size changes only when you change it — so p. 521's "automatically scales" holds for clusters, not for sizes. The slide calls all this "scale up" — the first slip to correct.

"In Snowflake caching is automatic", says p. 546: "Results are cached for 24 hours", and "Snowflake knows when data has changed and it re-executes the query if necessary". The advice that follows mixes two caches.

Last lever (p. 547): "For very large tables, automatic partitioning scheme may not be optimal", so you can create "custom cluster keys to partition table according to the needs", "Normally used for columns involved frequently in WHERE, JOIN or ORDER BY statements"; "Only very large tables (multi terabyte size) will benefit from clustering". A clustering key keeps close values in the same micro-partitions, so filters on it prune more. Page 548, with the commas it forgot:

-- the transactions table, kept in Transaction_date order
CREATE TABLE transactions_clustered_date
(
  Transaction_date DATE,
  Transaction_id Integer,
  Customer_id String,
  Amount integer
) CLUSTER BY (Transaction_date);

Back to the puzzle

What did Snowflake sell, if the theory is old school? Not a new model: the star, the cube and ETL work inside it unchanged, and section 4's star query runs there as written. It changed the machinery, and one decision explains nearly every Snowflake slide: storage separated from compute. With the data stored once in cheap cloud storage, each team gets its own warehouse on the same table; a warehouse grows, multiplies or stops without moving a byte; a pipe loads files with no warehouse running; a cached result needs no warehouse at all. The old warehouse was sized "for your largest workload", with "the cost that comes with it" (p. 521). This one is sized minute by minute.

Two neighbours complete the picture. A data lake (lesson 4) keeps raw files and applies the schema when reading, a warehouse before loading — and external tables let Snowflake read lake files in place. Hadoop (lesson 31; Hive's SQL over files, lesson 32) and Spark (lesson 40) are engines over files: Snowflake loads the Parquet and ORC files they write (p. 529) and has a Spark connector (p. 528).