Snowflake Data Discovery: Context, Metadata, Statistics and Granularity

1.Set your context first: USE and the context functions

Discovery queries only make sense when you know where they run. An unqualified table name is resolved against the session's current database and schema, and the warehouse you are using is the one that gets billed. The USE command sets that context. It covers five things, each with its own variant: USE ROLE, USE SECONDARY ROLES, USE WAREHOUSE, USE DATABASE and USE SCHEMA.

USE changes state and returns nothing you can inspect. To confirm what is active, call the context functions. Run this check whenever a discovery query returns an unexpected "object does not exist" error or an empty result. The usual cause is that the query ran under a different role or schema than you thought.

SELECT CURRENT_ROLE(),
       CURRENT_SECONDARY_ROLES(),
       CURRENT_WAREHOUSE(),
       CURRENT_DATABASE(),
       CURRENT_SCHEMA();

Sources1

2.Reading metadata and statistics: SHOW and DESCRIBE

With the context set, two command families tell you what exists. Neither one reads table data.

SHOW <objects> lists every object of one type, such as SHOW DATABASES, SHOW WAREHOUSES, SHOW GRANTS or SHOW TABLES. Each row carries common properties (name, creation timestamp, owning role, comment) plus properties specific to that object type. For tables, Snowflake also maintains statistics that answer "how big is this?" without a scan. In SHOW TABLES output, the rows column gives the row count and the bytes column gives the number of bytes that a full-table scan would read. That byte figure may differ from the physical storage on disk. The output also includes cluster_by, the clustering key if one is defined. Row counts are not available for external tables, which return NULL in the rows column.

DESCRIBE <object>, which can be abbreviated to DESC, returns the details of a single object. For a table, the default (TYPE = COLUMNS) lists one row per column. DESCRIBE TABLE and DESCRIBE VIEW are interchangeable for this purpose. What DESCRIBE TABLE does not show is the table's object parameters. To see those, run SHOW PARAMETERS IN TABLE.

The output of either command can be queried like a result set, using the pipe operator (->>) or RESULT_SCAN. The output column names are lowercase, so you must double-quote them, for example SELECT "type".

Sources234

3.Profiling the elements a business goal needs

Metadata tells you a column exists. It does not tell you whether the column is populated well enough to support a metric. To find that out, query the data itself. Aggregate functions are the main profiling tool, because they collapse many rows into one figure: COUNT, COUNT_IF, MIN, MAX, AVG, MEDIAN, MODE and STDDEV for shape, and APPROX_COUNT_DISTINCT (an alias for HLL) for estimating cardinality.

Be careful with NULLs when you read those numbers.

Because aggregates skip NULLs, an average can look healthy while hiding a sparsely filled column. If every input is NULL, the aggregate returns NULL. A multi-column COUNT(col1, col2) skips any row where one of the listed columns is NULL. GROUP BY behaves differently: it keeps those rows and groups NULL as a value of its own.

On a very large table you can profile a subset first. SAMPLE (synonym TABLESAMPLE) returns randomly sampled rows, either as a fraction of the table or as a fixed number of rows. The default method is BERNOULLI, which keeps each row with probability p/100. SYSTEM (also called BLOCK) samples whole blocks of rows instead. You can specify a seed to make a fraction-based sample repeatable.

The last question is whether the available tables contain every element the business goal requires. The requirement may arrive as a BI report you have to rebuild or as a SQL analysis you have to reproduce. Either way, break it into the same three parts: the measures it reports, the dimensions it breaks those measures down by, and the level of data granularity required, meaning what one result row represents. Snowflake's semantic views use the same vocabulary. They call the parts metrics and dimensions, and they require the logical table for a dimension to have an equal or lower level of granularity than the logical table for the metric.

Use the statistics maintained by Snowflake to size each candidate table: the rows and bytes columns in SHOW TABLES, or ROW_COUNT in the Information Schema TABLES view. Then map each required element to the table that holds it. That mapping tells you which transformations are required. The documentation's sales example shows how this works. Gross revenue per product needs only retail_price and quantity, which are both in sales. Net profit also needs wholesale_price, which exists only in products, so the profit metric requires a table join. When a report needs the same metric at several levels, you do not need one query per level. As the documentation puts it, you could create separate reports, but it is more efficient to scan the data once.

SELECT p.product_ID, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit
  FROM products AS p, sales AS s
  WHERE s.product_ID = p.product_ID
  GROUP BY p.product_ID;

Once you know a join is required, the join type decides which rows survive. An INNER JOIN produces a row only where the ON condition matches. A LEFT OUTER JOIN also keeps every left-table row that has no match, and the right-table columns in those rows contain NULL. RIGHT OUTER JOIN does the same for the right table, and FULL OUTER JOIN keeps unmatched rows from both sides. If you leave out the join condition (an INNER JOIN without ON, or a comma join without a WHERE clause), the result is a Cartesian product, which is usually a mistake.

So before you choose a join, check whether every fact row has a match. In the query above, a sale whose product_ID is missing from products would silently drop out of the profit report.

Sources5678910

4.Deciding the level of granularity

Granularity means what a single result row represents: one sale, one product, one city or one state. GROUP BY is how you set it. The columns you group by become the grain, and every other column must be aggregated. Grouping the sample sales table by product_ID gives one row per product. Grouping the same table by state and city gives one row per city within its state. GROUP BY ALL groups by every SELECT item that is not an aggregate.

SELECT state, city, SUM(retail_price * quantity) AS gross_revenue
  FROM sales
  GROUP BY state, city;

+-------+---------+---------------+
| STATE |   CITY  | GROSS REVENUE |
+-------+---------+---------------+
|   CA  | SF      |            22 |
|   CA  | SJ      |            44 |
|   FL  | Miami   |            80 |
|   FL  | Orlando |           160 |
|   PR  | SJ      |           320 |
+-------+---------+---------------+

Look at SJ. California and Puerto Rico both have a city with that name. A query grouped by city alone would merge them into one row, which is the wrong grain for a per-city report. If the business needs several levels at once (city, state and a grand total), GROUP BY ROLLUP (state, city) returns all of them in one scan. List the most significant level first. In the rollup rows, the columns that were rolled up show NULL. The GROUPING function tells those NULLs apart from NULLs that are really in the data.

Sources119

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.DESCRIBE TABLE shows everything about a table, including its object parameters.Why is that wrong?

   DESCRIBE TABLE shows columns, or stage properties with TYPE = STAGE. To see object parameters you need SHOW PARAMETERS IN TABLE.

   Covered in Reading metadata and statistics: SHOW and DESCRIBE

2. 2.COUNT(col1, col2) counts every row where at least one of the columns has a value.Why is that wrong?

   A multi-column aggregate skips any row in which any of the listed columns is NULL. GROUP BY, by contrast, keeps those rows.

   Covered in Profiling the elements a business goal needs

3. 3.The ON clause is optional, so a join without one simply matches rows on their common columns.Why is that wrong?

   Except for NATURAL JOIN, leaving out the ON clause pairs every row of one table with every row of the other. The resulting Cartesian product is usually a user error.

   Covered in Profiling the elements a business goal needs