What Is the Account Usage Store?

All RDBMS have a metadata catalog, Oracle has data dictionary views, dynamic performance views (v$), and a plethora of k$ and x$ tables—complex and hard to navigate. SQL Server and MySQL have the information_schema, and Snowflake has followed the same pattern by implementing an information schema for each created database.

Except for a single Snowflake account level holistic source of information, Snowflake supplies an imported database confusingly called SNOWFLAKE, where the Account Usage store resides, maintained by Snowflake, which also contains an information schema. Please note there are some features to be aware of. Account Usage views have a latency of between 45 minutes and 3 hours. Therefore, transactions can take time to appear in the views but typically store information for up to one year. But there is a way to gain access to data in real time by using information_schema, noting data is only stored for 14 days. I demonstrate both approaches. The distinction is important. The Account Usage store is an aggregated view of Snowflake’s centrally maintained data presented as a shared database. The information schema is driven from the global services layer accessing the FoundationDB key-value store. We use both throughout this chapter.

Then refresh as shown in Figure 6-1, after which the Snowflake database is viewable. SNOWFLAKE and SNOWFLAKE_SAMPLE_DATA are both imported databases. An imported database is one supplied by Snowflake as part of the initial account delivery. Like any other object, they can be dropped.

You may drop SNOWFLAKE_SAMPLE_DATA, but note UNDROP is not supported for an imported database that must be re-created along with granting imported entitlement to roles.

An illustration of Snowflake account usage. The top reads, find database objects, followed by a symbol which refresh icon is selected. Below it is a search bar that reads, starting with. Below it the text reads, Snowflake with a symbol which is selected. Snowflake sample data is written at the bottom and the symbol is selected again.

By default, only the ACCOUNTADMIN role can access the Account Usage views. Other roles can see the Snowflake database but not access the contents. Immutable history is restricted to one year, which is insufficient to support audit requests for greater than one year. We have a further requirement to periodically capture and retain all history.

Let’s dive into the Snowflake Account Usage store, along with an investigation of the global services layer. In line with the hands-on approach, I offer a script and walk-through with a strong recommendation to cut and paste each section as a practical learning experience. The code looks familiar to what is in Chapter 5, but the implementation is quite different.

Accessing the Account Usage Store

As discussed in Chapter 5, the only way to grant entitlement is via a role. The first step is to design our approach, as shown in Figure 6-2.

An illustration has two boxes. The text in the left box reads, views. Account usage and Snowflake is written below it. The text in the right box from top to bottom reads, reference data, monitoring objects, monitor owner, and monitor. The text reference data and views, point to monitoring objects which points to text that reads, report.

Our approach is to create a new database (MONITOR) containing two schemas: one for static reference data and the second to wrap the source Account Usage views to enrich, contextualize, summarize, and pre-filter the source data to make reporting easier.

While we must explicitly enable access to the Account Usage store, each database information schema is immediately accessible .

Database, Warehouse, and Schema

The following is a command set that changes role to SYSADMIN, then creates a database with 90-day time travel. Also created is a single, extra-small warehouse with two schemas in the database. Refresh your browser to see the new database and schemas .

USE ROLE sysadmin;

CREATE OR REPLACE DATABASE IDENTIFIER ( $monitor_database ) DATA_RETENTION_TIME_IN_DAYS = 90;

CREATE OR REPLACE WAREHOUSE IDENTIFIER ( $monitor_warehouse ) WITH

WAREHOUSE_SIZE      = 'X-SMALL'

AUTO_SUSPEND        = 60

AUTO_RESUME         = TRUE

MIN_CLUSTER_COUNT   = 1

MAX_CLUSTER_COUNT   = 4

SCALING_POLICY      = 'STANDARD'

INITIALLY_SUSPENDED = TRUE;

CREATE OR REPLACE SCHEMA IDENTIFIER ( $monitor_reference_schema );

CREATE OR REPLACE SCHEMA IDENTIFIER ( $monitor_owner_schema     );

In the preceding example, the labels are expanded by using the IDENTIFIER keyword. Using the SYADMIN role ensures all created objects remain owned by SYSADMIN and is a safeguard against unexpected manipulation .

Your worksheet should reflect Figure 6-3 when refreshed.

An illustration. The top reads, find database objects, followed by refresh icon, which is selected and two left side arrows. Below it is a search box. Below the search box, a list labeled, monitor, comprises information schema, monitor owner, public, and reference owner. Snowflake is written below the list.

Building Reference Data Sets

In the previous sections, we walked through setting up our environment. Now let’s focus on the practical implementation of monitoring objects. To do this, we must understand what we are monitoring and why.

Starting with a simple use case, our first objective is to examine the sample controls developed in Chapter 4 and determine whether a pattern-based approach can be derived, enabling future reuse. A design pattern is a code template that can easily be replicated, extended, or, better still, be data driven.

From experience, there are six different patterns to be developed that cover all known scenarios. Let’s begin our investigation into these patterns, starting with the simplest and adding notes to build up to the most complex. Note space does not permit a full explanation of each pattern, and this chapter builds upon principles outlined in Chapter 5, albeit in a different format.

With a fully historized reference data set , we achieve two objectives. First, we establish a temporal history for audit purposes because settings age out of the history after a year, and this is a weakness we must correct. Second, in the unlikely event a parameter is incorrectly set, we have a record of what the current value should be and of all historical values.

We must identify a way of ensuring parameter values are recorded for comparison, hence the need for reference data created in the reference_owner schema. We also introduce a way of historizing our reference data, we provide historical data to ensure the data lineage is preserved, and a brief digression is in order. Data warehouses often re-create a report at any point where the data warehouse holds information. But, if we only hold the most recent values for a record, we cannot store the history too. Enter Slowly Changing Dimension (SCD) modeling . A full explanation is beyond the scope of this chapter, but more information is at https://en.wikipedia.org/wiki/Slowly_changing_dimension . For our purposes, we need to know account parameter values at any point in history. SCD2 provides the answer; hence all historization for reference data is SCD2 based.

Finally, to ease building out each control pattern, we need to identify a way of grouping like controls in our reference data. Please take time to understand the companion script, Chapter_6_Account_Usage_Demo.sql, and all will become clear.

Referencing Schema Objects

Our reference schema contains three core tables along with their historized counterparts and a single secure view that denormalizes the data model for ease of use. A full explanation of denormalization is at https://en.wikipedia.org/wiki/Denormalization . Figure 6-4 shows the three tables and secure view we will create, along with their relationships.

A chart of reference owner. The top reads, monitor data, which is subdivided into two categories, monitor group and monitor parameter. The text, monitor control, is written between two categories.

The general pattern for creating a single table SCD2 implementation is shown in Figure 6-5. Please refer to the accompanying script, Chapter_6_Account_Usage_Demo.sql, for the complete implementation of all three tables and a secure view. Note Chapter_6_Account_Usage_Demo.sql takes a few minutes to run as there are many SQL statements .

Figure 6-5 shows the full suite of SQL statements and data flow required to implement our example.

A process chart of reference owner. The process starts with monitor group that points to stream which further points to v monitor group and then to merge operation. Merge operation is connected to hist monitor group. Below the monitor group, monitor group i d sequence, is written and points arrow to it.

Let’s overview Figure 6-5. We create sequence monitor_group_id to generate a surrogate key. Then we create tables monitor_group and hist_monitor_group also stream strm_monitor_group, which captures records as they are inserted into the monitor_group table. We define view v_monitor_group against both stream and history table, which does all the hard work of identifying delta records . With all objects created, we insert data into monitor_group and finally run the merge statement, which loads the SCD2 records into hist_monitor_group. Don’t worry. All will become clear. Just know there are dependencies on object creation order, which I now explain.

Change the role to reference_owner role.

USE ROLE      IDENTIFIER ( $monitor_reference_role   );

USE DATABASE  IDENTIFIER ( $monitor_database         );

USE SCHEMA    IDENTIFIER ( $monitor_reference_schema );

USE WAREHOUSE IDENTIFIER ( $monitor_warehouse        );

Create a sequence.

CREATE OR REPLACE SEQUENCE monitor_group_id_seq START WITH 10000;

Create tables.

CREATE OR REPLACE TABLE monitor_group

(

monitor_group_id        NUMBER(10) PRIMARY KEY NOT NULL,

monitor_group_name      VARCHAR(255)           NOT NULL,

last_updated            TIMESTAMP_NTZ DEFAULT current_timestamp()::TIMESTAMP_NTZ NOT NULL,

CONSTRAINT monitor_group_u1 UNIQUE ( monitor_group_name )

);

CREATE OR REPLACE TABLE hist_monitor_group

(

monitor_group_id        NUMBER(10)             NOT NULL,

monitor_group_name      VARCHAR(255)           NOT NULL,

valid_from              TIMESTAMP_NTZ,

valid_to                TIMESTAMP_NTZ,

current_flag            VARCHAR(1)

);

Create a stream.

CREATE OR REPLACE STREAM strm_monitor_group ON TABLE monitor_group;

Create a history view . Note that UNION ALL joins three sections. The first code block identifies records for INSERTs, the second for UPDATEs, and the third for DELETEs. An UPDATE is two actions: a logical DELETE and an INSERT. Hence in v_monitor_group declaration, dml_flag is never set to ‘U’.

CREATE OR REPLACE VIEW v_monitor_group

AS

SELECT monitor_group_id,

       monitor_group_name,

       valid_from,

       valid_to,

       current_flag,

       'I' AS dml_type

FROM   (

       SELECT monitor_group_id,

              monitor_group_name,

              last_updated        AS valid_from,

              LAG ( last_updated ) OVER ( PARTITION BY monitor_group_id ORDER BY last_updated DESC ) AS valid_to_raw,

              CASE

                 WHEN valid_to_raw IS NULL

                    THEN '9999-12-31'::TIMESTAMP_NTZ

                    ELSE valid_to_raw

              END AS valid_to,

              CASE

                 WHEN valid_to_raw IS NULL

                    THEN 'Y'

                    ELSE 'N'

              END AS current_flag,

              'I' AS dml_type

       FROM   (

              SELECT strm.monitor_group_id,

                     strm.monitor_group_name,

                     strm.last_updated

              FROM   strm_monitor_group     strm

              WHERE  strm.metadata$action   = 'INSERT'

              AND    strm.metadata$isupdate = 'FALSE'

              )

       )

UNION ALL

SELECT monitor_group_id,

       monitor_group_name,

       valid_from,

       valid_to,

       current_flag,

       dml_type

FROM   (

       SELECT monitor_group_id,

              monitor_group_name,

              valid_from,

              LAG ( valid_from ) OVER ( PARTITION BY monitor_group_id ORDER BY valid_from DESC ) AS valid_to_raw,

              CASE

                 WHEN valid_to_raw IS NULL

                    THEN '9999-12-31'::TIMESTAMP_NTZ

                    ELSE valid_to_raw

              END AS valid_to,

              CASE

                 WHEN valid_to_raw IS NULL

                    THEN 'Y'

                    ELSE 'N'

              END AS current_flag,

              dml_type

       FROM   (

              SELECT strm.monitor_group_id,

                     strm.monitor_group_name,

                     strm.last_updated AS valid_from,

                     'I' AS dml_type

              FROM   strm_monitor_group     strm

              WHERE  strm.metadata$action   = 'INSERT'

              AND    strm.metadata$isupdate = 'TRUE'

              UNION ALL

              SELECT tgt.monitor_group_id,

                     tgt.monitor_group_name,

                     tgt.valid_from,

                     'D' AS dml_type

              FROM   hist_monitor_group tgt

              WHERE  tgt.monitor_group_id IN

                     (

                     SELECT DISTINCT strm.monitor_group_id

                     FROM   strm_monitor_group     strm

                     WHERE  strm.metadata$action   = 'INSERT'

                     AND    strm.metadata$isupdate = 'TRUE'

                     )

              AND    tgt.current_flag = 'Y'

              )

       )

UNION ALL

SELECT strm.monitor_group_id,

       strm.monitor_group_name,

       tgt.valid_from,

       current_timestamp()::TIMESTAMP_NTZ AS valid_to,

       NULL,

       'D' AS dml_type

FROM   hist_monitor_group tgt

INNER JOIN strm_monitor_group strm

   ON  tgt.monitor_group_id   = strm.monitor_group_id

WHERE  strm.metadata$action   = 'DELETE'

AND    strm.metadata$isupdate = 'FALSE'

AND    tgt.current_flag       = 'Y';

Determine monitor groups, for example, we have account parameters, network policies, and time travel. there are others to add later when we encounter them, but for now, these three suffice. create INSERT statements for each.

INSERT INTO monitor_group ( monitor_group_id, monitor_group_name ) VALUES ( monitor_group_id_seq.NEXTVAL, 'ACCOUNT PARAMETER' );

INSERT INTO monitor_group ( monitor_group_id, monitor_group_name ) VALUES ( monitor_group_id_seq.NEXTVAL, 'NETWORK POLICY' );

INSERT INTO monitor_group ( monitor_group_id, monitor_group_name ) VALUES ( monitor_group_id_seq.NEXTVAL, 'TIME TRAVEL' );

Run the MERGE statement to historize the data, as dml_flag is never set to ‘U’, we only need to consider INSERT and (logical) DELETE operations. Note the number of rows inserted and number of rows updated in the returned result set.

MERGE INTO hist_monitor_group tgt

USING v_monitor_group strm

ON    tgt.monitor_group_id   = strm.monitor_group_id

AND   tgt.valid_from         = strm.valid_from

AND   tgt.monitor_group_name = strm.monitor_group_name

WHEN MATCHED AND strm.dml_type = 'D' THEN

UPDATE

SET   tgt.valid_to     = strm.valid_to,

      tgt.current_flag = 'N'

WHEN NOT MATCHED AND strm.dml_type = 'I' THEN

INSERT

(

tgt.monitor_group_id,

tgt.monitor_group_name,

tgt.valid_from,

tgt.valid_to,

tgt.current_flag

)

VALUES

(

strm.monitor_group_id,

strm.monitor_group_name,

strm.valid_from,

strm.valid_to,

strm.current_flag

);

Now query the history table to ensure the MERGE created the three records noting the valid_to date is set to ‘9999-12-31’ and current_flag = ‘Y’.

SELECT * FROM hist_monitor_group;

Storing Monitored Output

This section describes how to store result sets and maintain a full audit history of monitoring activity and extension when our monitoring requirements change. Figure 6-6 illustrates the components required to identify changes to our baseline security configuration held in reference data, then compared to the actual values set on our account, with any differences reported for consumption by monitoring teams .

An illustration has two boxes. The left one from top to bottom reads, account parameters, views, account usage, and Snowflake. The right one has text reference owner on the top. Below it text reads, monitor data, which points down to monitoring procedures. It points to logging which further points to report. At the bottom, monitor is written.

There is a second logging table for one complex use case described later .

Asynchronous Monitoring

These pattern runs asynchronously and should be called from a task periodically. Then the logged output should be queried via a second task for event propagation. We adopt this approach since it is impossible to embed the SHOW, LIST, and DESCRIBE commands’ output in a view or other abstraction mechanism, such as UDF.

Publication could be via external procedure wrapping email functionality or writing to an external stage. Monitoring screens can also be developed against the monitor_log table for use by your operational support team.

Account Parameters

The following pattern is for simple account parameter monitoring, TRUE/FALSE flag setting for a named parameter.

You may recall in Chapter 4 that we developed controls to prevent data from being unloaded to user-specified Internet locations and restrict data unloads to specified, system-mapped Internet locations.

USE ROLE accountadmin;

ALTER ACCOUNT SET prevent_unload_to_inline_url                    = TRUE;

ALTER ACCOUNT SET require_storage_integration_for_stage_creation  = TRUE;

ALTER ACCOUNT SET require_storage_integration_for_stage_operation = TRUE;

Two separate controls with a common implementation pattern. we are setting account level parameters.

Note our sample script Chapter_6_Account_Usage_Demo.sql initially creates only three monitor parameters in support of account parameter monitoring, providing our first set of controls to monitor .

USE ROLE      IDENTIFIER ( $monitor_owner_role   );

USE DATABASE  IDENTIFIER ( $monitor_database     );

USE SCHEMA    IDENTIFIER ( $monitor_owner_schema );

USE WAREHOUSE IDENTIFIER ( $monitor_warehouse    );

SELECT *

FROM MONITOR.reference_owner.v_monitor_data

WHERE monitor_parameter_value = 'TRUE';

Note fully qualified object reference. We should see three rows returned as per Figure 6-7.

A table with 5 columns and 3 rows. Columns are labeled from left to right as, row, 1, 2, 3; monitor group i d, 10000; monitor group name, account parameter; monitor control I D, 10001, 10002, 10003; monitor control name, A C 2, A C 3, A C 3; monitor parameter I D, 10000, 10001, 10002; monitor parameter name; and monitor value, true.

But how do we compare the reference data to the actual values?

First, we must run the SHOW command to access the Snowflake global services layer based on FoundationDB, the key-pair store that underpins all Snowflake metadata operations. We return to this theme later in this chapter .

SHOW PARAMETERS LIKE 'prevent_unload_to_inline_url' IN ACCOUNT;

However, there is a problem. SHOW is not a true SQL command since we cannot use it in a SQL statement. For example, we cannot create a view based on it. This does not work. Create or replace view v_show_parameters as SHOW parameters. However, we can use RESULT_SCAN to convert SHOW output to a result set, but we must understand the interaction. When RESULT_SCAN is used with last_query_id, we cannot run commands in between as the query_id changes.

SELECT "value" FROM TABLE ( RESULT_SCAN ( last_query_id()));

Instead, we must use a stored procedure to programmatically extract the value for a named parameter as the two statements run consecutively, noting we extend this code pattern later for network parameter monitoring .

CREATE OR REPLACE PROCEDURE sp_get_parameter_value ( P_PARAMETER STRING ) RETURNS STRING

LANGUAGE javascript

EXECUTE AS CALLER

AS

$$

   var sql_stmt  = "SHOW PARAMETERS LIKE '" + P_PARAMETER + "'IN ACCOUNT";

   var show_stmt = snowflake.createStatement ({ sqlText:sql_stmt });

   show_res = show_stmt.execute();

   show_op  = show_res.next();

   var sql_stmt  = `SELECT "value" FROM TABLE ( RESULT_SCAN ( last_query_id()));`;

   var exec_stmt = snowflake.createStatement ({ sqlText:sql_stmt });

   rec_set = exec_stmt.execute();

   rec_op  = rec_set.next();

   return rec_set.getColumnValue(1);

$$;

The following tests the stored procedure.

CALL sp_get_parameter_value ( 'prevent_unload_to_inline_url' );

The stored procedure should return “true”.

You may be wondering whether the stored procedure can be converted into a function and embedded in a SQL statement. In short, the answer at the time of writing, using a function, is not possible (but would have been so much simpler).

Having explained and implemented all dependent objects on which account parameter monitoring relies, Figure 6-8 illustrates how the components relate with sp_check_parameters being the next deliverable for development .

An illustration with 2 boxes. The left one from top to bottom reads, account parameters, Snowflake. The right one labeled, reference owner, reads monitor data on the top and points to three boxes which read from left to right, s p get parameter value, s p check parameters, and monitor log and log points to report.

Flowchart to explain the logic contained in sp_check_parameters in Figure 6-9.

A flowchart of parameters logic. The first box labeled, start, points to fetch all account parameter, to fetch account parameter value, no; to last Param, yes; to exact match, no; to INSERT, yes; to last Param, yes; to stop.

Let’s implement the sp_check_parameters stored procedure using JavaScript.

CREATE OR REPLACE PROCEDURE sp_check_parameters() RETURNS STRING

LANGUAGE javascript

EXECUTE AS CALLER

AS

$$

   var sql_stmt  = "";

   var stmt      = "";

   var recset    = "";

   var result    = "";

   var monitor_control_name    = "";

   var monitor_parameter_name  = "";

   var monitor_parameter_value = "";

   var parameter_value         = "";

   sql_stmt += "SELECT monitor_control_name, "

   sql_stmt += "       UPPER ( monitor_parameter_name  ), "

   sql_stmt += "       UPPER ( monitor_parameter_value ) "

   sql_stmt += "FROM   MONITOR.reference_owner.v_monitor_data "

   sql_stmt += "WHERE  monitor_group_name = 'ACCOUNT PARAMETER';";

   stmt = snowflake.createStatement ({ sqlText:sql_stmt });

   try

   {

       recset = stmt.execute();

       while(recset.next())

       {

           monitor_control_name    = recset.getColumnValue(1);

           monitor_parameter_name  = recset.getColumnValue(2);

           monitor_parameter_value = recset.getColumnValue(3);

           stmt = snowflake.createStatement ( { sqlText: "CALL sp_get_parameter_value(?);",

                                                binds:[monitor_parameter_name] } );

           res = stmt.execute();

           res.next();

           parameter_value = res.getColumnValue(1);

           sql_stmt  = "INSERT INTO monitor_log "

           sql_stmt += "SELECT monitor_log_id_seq.NEXTVAL, "

           sql_stmt += "       '" + monitor_parameter_name  + "', "

           sql_stmt += "       '" + parameter_value         + "', "

           sql_stmt += "       '" + monitor_control_name    + "', "

           sql_stmt += "       '" + monitor_parameter_name  + "', "

           sql_stmt += "       '" + monitor_parameter_value + "', "

           sql_stmt += "       current_timestamp()::TIMESTAMP_NTZ "

           sql_stmt += "FROM   dual "

           sql_stmt += "WHERE  UPPER ( '" + parameter_value + "' ) <> UPPER ( '" + monitor_parameter_value + "' );";

           stmt = snowflake.createStatement ({ sqlText:sql_stmt });

           try

           {

              stmt.execute();

              result = "Success";

           }

           catch { result = sql_stmt; }

       }

       result = "Success";

   }

   catch { result = sql_stmt; }

   return result;

$$;

Call the stored procedure to test. This should not insert any records in monitor_log .

CALL sp_check_parameters();

SELECT * FROM monitor_log;

Finally, mis-set a parameter then retest.

USE ROLE accountadmin;

ALTER ACCOUNT SET prevent_unload_to_inline_url = FALSE;

USE ROLE      IDENTIFIER ( $monitor_owner_role   );

USE DATABASE  IDENTIFIER ( $monitor_database     );

USE SCHEMA    IDENTIFIER ( $monitor_owner_schema );

USE WAREHOUSE IDENTIFIER ( $monitor_warehouse    );

CALL sp_check_parameters();

SELECT * FROM monitor_log;

You should see a single record, as shown in Figure 6-10.

A table with 7 columns and 1 row. Columns are labeled with text written in row. Monitor log I D, 10000; event description, prevent unload to inline U R L; event result, false; monitor control name, Snowflake N I S T A C 2; monitor parameter name, prevent unload to inline U R L; monitor parameter value, true; last updated, 20 21-12-27.

Synchronous Monitoring

The remainder of our monitoring configuration is much more straightforward and relies upon accessing the Account Usage views. We still rely upon reference data to determine our baseline criteria reflected in the following sections.

External Monitoring Tools

Some third-party tools require direct access to Snowflake Account Usage views to function. We should not allow direct access to any Snowflake objects but instead, create wrapper views and custom roles to limit data access to minimal objects required to achieve each objective. While this approach may be uncomfortable for vendors, we must not compromise our account security and must insist on vendors conforming to our security policies. Some vendors make provision for customization through a configuration file, as with Datadog.

Note the removal of the v_ prefix because we want to present our view to the consuming user in (almost) native format to make integration easy.

Finally, grant the role to a user, which I leave you to complete .

Cleanup

History Retention

We may need to hold the full history of every action in our account from inception to the current date. While Snowflake retains a one-year immutable history, we may need to periodically copy data to local tables before information ages out.

Summary

This chapter introduced the Account Usage store and built a new database and associated schemas for later use. You then enabled access to the Account Usage store.

Next, you developed a reference schema with metadata storing our account level settings and denormalizing it for later use, demonstrating a sample but extensible data model.

A full monitoring solution is not possible with the limited space available. The page budget has been significantly exceeded to make things clear. It is well worth the effort. I hope you agree and have plenty to pique your interest. I have included additional code in the accompanying file to assist your understanding.

Let’s turn our attention to ingesting data, where you learn more techniques on your journey to building the Snowflake Data Cloud.