Defensible Analytics

What is Data Vault

Prerequisites

What You’ll Learn

What You’ll Need

What You’ll Build

Reference Architecture

From Logical Architecture Zones to Implemented Snowflake Objects

For the sake of simplicity, this guide will not delve into utilizing multiple Snowflake accounts. However, adopting a multi-account strategy, taking advantage of Snowflake's remarkable Secure Data Sharing, could unlock significant value for your organization. A multi-account strategy is one where a single Organization has multiple Accounts, each serving a specific purpose. This provides you with the flexibility to distribute databases across multiple accounts, where shared read-only access to data with zero copying, and thus enabling the use of different Editions for different Accounts, which enable different feature sets and have different compute pricing. Databases may also be replicated for business continuity and disaster recovery purposes to other accounts.

Step 0: Login to your Snowflake Account, Create a Workspace File

-- Defaulting Secondary Roles to None ------------------------------------------
ALTER USER SET DEFAULT_SECONDARY_ROLES = ();

Step 1: Platform Role, Warehouse, and Database

-- Platform Administration Role ------------------------------------------------
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS PLT_ADMIN
  COMMENT = 'Platform administrator role for managing shared platform objects';

GRANT ROLE PLT_ADMIN TO ROLE SYSADMIN;

-- Administration Warehouse ----------------------------------------------------
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS ADMIN_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Administration warehouse for platform admin tasks';

GRANT ALL PRIVILEGES ON WAREHOUSE ADMIN_WH TO ROLE PLT_ADMIN WITH GRANT OPTION;

-- Platform Database -----------------------------------------------------------
CREATE DATABASE IF NOT EXISTS PLT
  COMMENT = 'Common centralized platform database for shared objects and utilities';

DROP SCHEMA IF EXISTS PLT.PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE PLT TO ROLE PLT_ADMIN WITH GRANT OPTION;

Step 2: Platform Governance Schema

-- Platform Database: Governance -----------------------------------------------
USE ROLE PLT_ADMIN;

CREATE SCHEMA IF NOT EXISTS PLT.GOVERNANCE
  WITH MANAGED ACCESS
  COMMENT = 'Platform governance objects, such as classification tags for annotating data assets';

CREATE DATABASE ROLE IF NOT EXISTS PLT.GOVERNANCE_R
  COMMENT = 'Read access to platform governance objects and tag definitions';
CREATE DATABASE ROLE IF NOT EXISTS PLT.GOVERNANCE_A
  COMMENT = 'Apply and invoke access to platform governance objects';
CREATE DATABASE ROLE IF NOT EXISTS PLT.GOVERNANCE_W
  COMMENT = 'Create and manage platform governance objects';

GRANT DATABASE ROLE PLT.GOVERNANCE_R TO DATABASE ROLE PLT.GOVERNANCE_A;
GRANT DATABASE ROLE PLT.GOVERNANCE_A TO DATABASE ROLE PLT.GOVERNANCE_W;

GRANT DATABASE ROLE PLT.GOVERNANCE_R TO ROLE PUBLIC;

-- GOVERNANCE_R: read access to governance schema and tag values
GRANT USAGE ON DATABASE PLT TO DATABASE ROLE PLT.GOVERNANCE_R;
GRANT USAGE ON SCHEMA PLT.GOVERNANCE TO DATABASE ROLE PLT.GOVERNANCE_R;

-- GOVERNANCE_A: apply platform governance objects
GRANT MONITOR ON SCHEMA PLT.GOVERNANCE TO DATABASE ROLE PLT.GOVERNANCE_A;
GRANT USAGE ON FUTURE DATA METRIC FUNCTIONS IN SCHEMA PLT.GOVERNANCE TO DATABASE ROLE PLT.GOVERNANCE_A;
-- Note that the APPLY privilege may not be granted to future tags, but granted after tags are created

-- GOVERNANCE_W: create and manage platform governance objects
GRANT CREATE DATA METRIC FUNCTION ON SCHEMA PLT.GOVERNANCE TO DATABASE ROLE PLT.GOVERNANCE_W;
GRANT CREATE TAG ON SCHEMA PLT.GOVERNANCE TO DATABASE ROLE PLT.GOVERNANCE_W;

CREATE TAG IF NOT EXISTS PLT.GOVERNANCE.INFO_CLASSIFICATION
  ALLOWED_VALUES 'Public', 'Internal', 'Restricted'
  COMMENT = 'Information classification level of the tagged data object or column';
GRANT READ ON TAG PLT.GOVERNANCE.INFO_CLASSIFICATION TO DATABASE ROLE PLT.GOVERNANCE_R;
GRANT APPLY ON TAG PLT.GOVERNANCE.INFO_CLASSIFICATION TO DATABASE ROLE PLT.GOVERNANCE_A;

CREATE TAG IF NOT EXISTS PLT.GOVERNANCE.DOMAIN
  COMMENT = 'Domain association of the tagged data object or column';
GRANT READ ON TAG PLT.GOVERNANCE.DOMAIN TO DATABASE ROLE PLT.GOVERNANCE_R;
GRANT APPLY ON TAG PLT.GOVERNANCE.DOMAIN TO DATABASE ROLE PLT.GOVERNANCE_A;

This guide could not possibly cover the vast array of what is possible using the powerful governance features available. Cortex Code includes built-in data governance skills designed to help you understand, protect, and monitor the data in your Snowflake account.

Snowflake provides a set of built-in DMFs — such as SNOWFLAKE.CORE.NULL_COUNT, SNOWFLAKE.CORE.DUPLICATE_COUNT, and SNOWFLAKE.CORE.FRESHNESS — that cover generic quality checks out of the box. Custom DMFs in PLT.GOVERNANCE should encode Data Vault-specific quality standards that have no system equivalent, such as LDTS staleness, future LDTS values, hash key and business key duplicates, and ghost records.

Snowflake provides a set of built-in Tags — such as SNOWFLAKE.CORE.SEMANTIC_CATEGORY and SNOWFLAKE.CORE.PRIVACY_CATEGORY — that cover generic sensitive data classification out of the box. Custom Tags stored in PLT.GOVERNANCE should encode your organization's specific or Data Vault-specific tags that have no system equivalent. The INFO_CLASSIFICATION tag is an example. You can map user-defined tags to system-defined classification tags. For example, you can set up a tag map so that every time the system tag SNOWFLAKE.CORE.SEMANTIC_CATEGORY = 'NAME' is applied to a column, the custom tag INFO_CLASSIFICATION = 'Restricted' is also applied. See Sensitive Data Classification.

Snowflake's Data Protection Policies — masking, row access, aggregation, projection — enforce the information classification at the column or row level. Restricted data is typically restricted to a list of named persons, where that list is typically given a domain-specific role and governed in domain-specific manner. Thus, these policies will be created later in the domain-oriented schemas, not in PLT.GOVERNANCE.

Snowflake does not support future grants for tags — GRANT APPLY ON TAG is issued per object immediately after each CREATE TAG.

Step 3: Platform Admin Tools Schema

-- Platform Database: Admin Tools ----------------------------------------------
CREATE SCHEMA IF NOT EXISTS PLT.ADMIN_TOOLS
  WITH MANAGED ACCESS
  COMMENT = 'Common platform administration tools and utilities';

CREATE OR REPLACE PROCEDURE PLT.ADMIN_TOOLS.CREATE_SCHEMA_AND_ROLES(
    DATABASE_NAME   VARCHAR,
    SCHEMA_NAME     VARCHAR,
    SCHEMA_SUBJECT  VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Base helper: creates a managed-access schema, paired _R/_W database roles, and standard privilege grants. Called by zone-specific wrappers.'
EXECUTE AS CALLER
AS
$$
DECLARE
    v_db        VARCHAR DEFAULT UPPER(DATABASE_NAME);
    v_schema    VARCHAR DEFAULT UPPER(SCHEMA_NAME);
    v_subject   VARCHAR DEFAULT SCHEMA_SUBJECT;
BEGIN

    -- Ensure database-wide roles exist
    EXECUTE IMMEDIATE 'CREATE DATABASE ROLE IF NOT EXISTS ' || v_db || '.DB_R'
        || ' COMMENT = ''Database-wide read access''';
    EXECUTE IMMEDIATE 'CREATE DATABASE ROLE IF NOT EXISTS ' || v_db || '.DB_W'
        || ' COMMENT = ''Database-wide write and create access''';

    -- Create the managed access schema
    EXECUTE IMMEDIATE
        'CREATE SCHEMA IF NOT EXISTS ' || v_db || '.' || v_schema
        || ' WITH MANAGED ACCESS'
        || ' COMMENT = ''' || REPLACE(v_subject, '''', '''''') || '''';

    -- Create schema-specific database roles
    EXECUTE IMMEDIATE
        'CREATE DATABASE ROLE IF NOT EXISTS ' || v_db || '.' || v_schema || '_R'
        || ' COMMENT = ''Read access to ' || REPLACE(v_subject, '''', '''''') || ' (' || v_schema || ')''';
    EXECUTE IMMEDIATE
        'CREATE DATABASE ROLE IF NOT EXISTS ' || v_db || '.' || v_schema || '_W'
        || ' COMMENT = ''Write and create access to ' || REPLACE(v_subject, '''', '''''') || ' (' || v_schema || ')''';

    -- _R granted to DB_R and to _W (so _W inherits _R)
    EXECUTE IMMEDIATE 'GRANT DATABASE ROLE ' || v_db || '.' || v_schema || '_R TO DATABASE ROLE ' || v_db || '.DB_R';
    EXECUTE IMMEDIATE 'GRANT DATABASE ROLE ' || v_db || '.' || v_schema || '_R TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    -- _W granted to DB_W
    EXECUTE IMMEDIATE 'GRANT DATABASE ROLE ' || v_db || '.' || v_schema || '_W TO DATABASE ROLE ' || v_db || '.DB_W';

    -- Grant USAGE and MONITOR on database and schema to _R
    EXECUTE IMMEDIATE 'GRANT USAGE, MONITOR ON DATABASE ' || v_db
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT USAGE, MONITOR ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';

    RETURN 'SUCCESS: Created schema ' || v_db || '.' || v_schema
        || ' with roles ' || v_schema || '_R, ' || v_schema || '_W.';
END;
$$;

CREATE OR REPLACE PROCEDURE PLT.ADMIN_TOOLS.CREATE_LZ_SCHEMA_AND_ROLES(
    DATABASE_NAME   VARCHAR,
    SCHEMA_NAME     VARCHAR,
    SOURCE_SYSTEM   VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Creates a Landing Zone schema and roles for a source system, adding insert-only write and stage/pipe future grants to enforce immutability after initial load.'
EXECUTE AS CALLER
AS
$$
DECLARE
    v_db     VARCHAR DEFAULT UPPER(DATABASE_NAME);
    v_schema VARCHAR DEFAULT UPPER(SCHEMA_NAME);
BEGIN

    CALL PLT.ADMIN_TOOLS.CREATE_SCHEMA_AND_ROLES(:v_db, :v_schema, :SOURCE_SYSTEM);

    -- _R: read-only access to landing zone objects
    EXECUTE IMMEDIATE 'GRANT SELECT ON FUTURE TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';

    -- _W: insert-only write access (LZ data is immutable after initial load)
    EXECUTE IMMEDIATE 'GRANT INSERT ON FUTURE TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT MONITOR, OPERATE ON FUTURE PIPES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT USAGE, READ, WRITE ON FUTURE STAGES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE PIPE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE STAGE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    RETURN 'SUCCESS: Created schema ' || v_db || '.' || v_schema
        || ' for source system ' || SOURCE_SYSTEM || ' with future grants applied.';
END;
$$;

CREATE OR REPLACE PROCEDURE PLT.ADMIN_TOOLS.CREATE_DOMAIN_SCHEMA_AND_ROLES(
    DATABASE_NAME   VARCHAR,
    SCHEMA_NAME     VARCHAR,
    DOMAIN          VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Creates a domain-oriented schema and roles with full analytical object future grants (tables, views, dynamic tables, functions, procedures, streams, tasks, DMFs, and policies). Base procedure for DV and DW zone wrappers.'
EXECUTE AS CALLER
AS
$$
DECLARE
    v_db     VARCHAR DEFAULT UPPER(DATABASE_NAME);
    v_schema VARCHAR DEFAULT UPPER(SCHEMA_NAME);
BEGIN
    
    CALL PLT.ADMIN_TOOLS.CREATE_SCHEMA_AND_ROLES(:v_db, :v_schema, :DOMAIN);

    EXECUTE IMMEDIATE 'ALTER SCHEMA ' || v_db || '.' || v_schema
        || ' SET TAG PLT.GOVERNANCE.DOMAIN = ''' || REPLACE(DOMAIN, '''', '''''') || '''';

    -- _R: read access to domain schema objects
    EXECUTE IMMEDIATE 'GRANT SELECT, REFERENCES ON FUTURE TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT SELECT ON FUTURE VIEWS IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';

    -- _W: write access and pipeline management
    EXECUTE IMMEDIATE 'GRANT SELECT ON FUTURE STREAMS IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT MONITOR, OPERATE ON FUTURE TASKS IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT MONITOR, OPERATE ON FUTURE DYNAMIC TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    -- _W: schema-level CREATE privileges
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE DYNAMIC TABLE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE FUNCTION ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE STREAM ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE TASK ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE STAGE ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE DATA METRIC FUNCTION ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE MASKING POLICY ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE ROW ACCESS POLICY ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';
    EXECUTE IMMEDIATE 'GRANT CREATE TAG ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    RETURN 'SUCCESS: Created domain schema ' || v_db || '.' || v_schema
        || ' for ' || DOMAIN || '.';
END;
$$;

CREATE OR REPLACE PROCEDURE PLT.ADMIN_TOOLS.CREATE_DV_SCHEMA_AND_ROLES(
    DATABASE_NAME   VARCHAR,
    SCHEMA_NAME     VARCHAR,
    DOMAIN          VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Creates a Data Vault schema and roles for a domain, enforcing insert-only writes to preserve Data Vault immutability.'
EXECUTE AS CALLER
AS
$$
DECLARE
    v_db     VARCHAR DEFAULT UPPER(DATABASE_NAME);
    v_schema VARCHAR DEFAULT UPPER(SCHEMA_NAME);
BEGIN
    
    CALL PLT.ADMIN_TOOLS.CREATE_DOMAIN_SCHEMA_AND_ROLES(:v_db, :v_schema, :DOMAIN);

    -- _W: insert-only (DV tables are immutable)
    EXECUTE IMMEDIATE 'GRANT INSERT ON FUTURE TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    RETURN 'SUCCESS: Created Data Vault schema ' || v_db || '.' || v_schema
        || ' for ' || DOMAIN || ' with future grants applied.';
END;
$$;

CREATE OR REPLACE PROCEDURE PLT.ADMIN_TOOLS.CREATE_DW_SCHEMA_AND_ROLES(
    DATABASE_NAME   VARCHAR,
    SCHEMA_NAME     VARCHAR,
    DOMAIN          VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Creates a Data Warehouse schema and roles for a domain, granting full DML write access and semantic view creation for AI-agent consumption.'
EXECUTE AS CALLER
AS
$$
DECLARE
    v_db     VARCHAR DEFAULT UPPER(DATABASE_NAME);
    v_schema VARCHAR DEFAULT UPPER(SCHEMA_NAME);
BEGIN

    CALL PLT.ADMIN_TOOLS.CREATE_DOMAIN_SCHEMA_AND_ROLES(:v_db, :v_schema, :DOMAIN);

    -- _W: full write access to DW tables
    EXECUTE IMMEDIATE 'GRANT INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    -- _R/_W: semantic view access (DW schemas are AI-agent consumable)
    EXECUTE IMMEDIATE 'GRANT SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_R';
    EXECUTE IMMEDIATE 'GRANT CREATE SEMANTIC VIEW ON SCHEMA ' || v_db || '.' || v_schema
        || ' TO DATABASE ROLE ' || v_db || '.' || v_schema || '_W';

    RETURN 'SUCCESS: Created Data Warehouse schema ' || v_db || '.' || v_schema
        || ' for ' || DOMAIN || ' with future grants applied.';
END;
$$;

Step 4: Landing Zone Database

-- Development LZ Ingestion Role -----------------------------------------------
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS DEV_LZ_INGEST
  COMMENT = 'Ingestion role for the development landing zone';

GRANT ROLE DEV_LZ_INGEST TO ROLE SYSADMIN;

-- Development LZ Ingestion Warehouse ------------------------------------------
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS DEV_INGEST_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for automated data ingestion workloads';

GRANT ALL PRIVILEGES ON WAREHOUSE DEV_INGEST_WH TO ROLE PLT_ADMIN WITH GRANT OPTION;
GRANT USAGE, OPERATE ON WAREHOUSE DEV_INGEST_WH TO ROLE DEV_LZ_INGEST;

-- Development LZ Database -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS DEV_LZ
  COMMENT = 'Development landing zone for raw data ingestion';

DROP SCHEMA IF EXISTS DEV_LZ.PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE DEV_LZ TO ROLE PLT_ADMIN WITH GRANT OPTION;

Development Environment

You may note that this serves as a development environment example. Objects developed here might later be promoted to a TST_LZ for testing, and then a main LZ, for production use. We're leaving those out here for the sake of brevity. However, in those test and production environments, ingestion of data is typically performed by an automated Service User. In the event multiple Service Users will be used to ingest data using multiple solutions, you may wish to create multiple ingestion roles.

Step 5: Enterprise Memory and Information Delivery Zone Databases

-- QA (Dev/Test) Analyst Role --------------------------------------------------
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS QA_ANALYST
  COMMENT = 'General analyst role with read-only access to the information delivery zone';

GRANT ROLE QA_ANALYST TO ROLE PLT_ADMIN;


-- Data Transformation and Engineering Warehouses ------------------------------
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS DEV_XFORM_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for automated data transformation workloads';

GRANT ALL PRIVILEGES ON WAREHOUSE DEV_XFORM_WH TO ROLE PLT_ADMIN WITH GRANT OPTION;

CREATE WAREHOUSE IF NOT EXISTS ENGINEERING_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for general development and testing use';

GRANT ALL PRIVILEGES ON WAREHOUSE ENGINEERING_WH TO ROLE PLT_ADMIN WITH GRANT OPTION;
GRANT USAGE, OPERATE ON WAREHOUSE ENGINEERING_WH TO ROLE QA_ANALYST;

-- Data Vault Database ---------------------------------------------------------
CREATE DATABASE IF NOT EXISTS DEV_DV
  COMMENT = 'Enterprise memory of domain-oriented data vault models';

DROP SCHEMA IF EXISTS DEV_DV.PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE DEV_DV TO ROLE PLT_ADMIN WITH GRANT OPTION;

-- Data Warehouse Database -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS DEV_DW
  COMMENT = 'Information delivery of domain-oriented models';

DROP SCHEMA IF EXISTS DEV_DW.PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE DEV_DW TO ROLE PLT_ADMIN WITH GRANT OPTION;

Transformation Role and Ingestion Privileges

You may be wondering, why did we not grant privileges to the ingestion role, nor ever create a role for transforming data in the data vault? We will do this later, after the schemas in these databases are in place.

Business Architecture, Domains and Ontologies

Domains

Note: The domain examples above are inspired by the TBM Taxonomy's Business Layer Solutions Hierarchy.

Domain Taxonomy and Ontologies

From Logical Business Architecture to Implementation

Step 6: Sample Source System Schema

-- Landing Zone Schemas --------------------------------------------------------
USE ROLE PLT_ADMIN;
USE WAREHOUSE ADMIN_WH;

CALL PLT.ADMIN_TOOLS.CREATE_LZ_SCHEMA_AND_ROLES('DEV_LZ', 'TPCH_REF', 'Static Reference Data');
CALL PLT.ADMIN_TOOLS.CREATE_LZ_SCHEMA_AND_ROLES('DEV_LZ', 'TPCH_CUSTOMER_SYS', 'Customer System');
CALL PLT.ADMIN_TOOLS.CREATE_LZ_SCHEMA_AND_ROLES('DEV_LZ', 'TPCH_ORDERS_SYS', 'Order System');

GRANT DATABASE ROLE DEV_LZ.DB_W TO ROLE DEV_LZ_INGEST;

Note the simplicity of creating the source system schemas, granting write access to the functional role DEV_LZ_INGEST, by utilizing a common stored procedure that creates the managed access schema, access roles, and grants privileges to those roles. When multiple service users are able to write to the landing zone, be sure use specialized functional roles, assigning those the schema-specific write access roles.

Step 7: Domain-Oriented Schemas

-- Enterprise Memory and Information Delivery Schemas --------------------------
CALL PLT.ADMIN_TOOLS.CREATE_DV_SCHEMA_AND_ROLES('DEV_DV', 'SALESMKT', 'Sales & Marketing');
CALL PLT.ADMIN_TOOLS.CREATE_DW_SCHEMA_AND_ROLES('DEV_DW', 'SALESMKT', 'Sales & Marketing');
CALL PLT.ADMIN_TOOLS.CREATE_DV_SCHEMA_AND_ROLES('DEV_DV', 'CUSTSERV', 'Customer Service');
CALL PLT.ADMIN_TOOLS.CREATE_DW_SCHEMA_AND_ROLES('DEV_DW', 'CUSTSERV', 'Customer Service');

Note that while the stored procedure creates the schemas and access roles, we don't yet have domain-oriented functional roles to which we may grant the access roles.

Step 8: Domain-Oriented Roles and Privileges

-- Domain-Oriented Data Engineer Roles -----------------------------------------
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS SALESMKT_ENGINEER
  COMMENT = 'Data engineering role for the development of Sales & Marketing objects and transformations';
GRANT ROLE SALESMKT_ENGINEER TO ROLE PLT_ADMIN;

CREATE ROLE IF NOT EXISTS CUSTSERV_ENGINEER
  COMMENT = 'Data engineering role for the development of Customer Service objects and transformations';
GRANT ROLE CUSTSERV_ENGINEER TO ROLE PLT_ADMIN;

USE ROLE ACCOUNTADMIN;

GRANT EXECUTE TASK ON ACCOUNT TO ROLE SALESMKT_ENGINEER;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE CUSTSERV_ENGINEER;

-- Domain-Oriented Data Engineer Privileges ------------------------------------
USE ROLE PLT_ADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE ENGINEERING_WH TO ROLE SALESMKT_ENGINEER;
GRANT USAGE, OPERATE ON WAREHOUSE DEV_XFORM_WH TO ROLE SALESMKT_ENGINEER;

GRANT DATABASE ROLE DEV_LZ.DB_R TO ROLE SALESMKT_ENGINEER;
GRANT DATABASE ROLE DEV_DV.SALESMKT_W TO ROLE SALESMKT_ENGINEER;
GRANT DATABASE ROLE DEV_DV.DB_R TO ROLE SALESMKT_ENGINEER;
GRANT DATABASE ROLE DEV_DW.SALESMKT_W TO ROLE SALESMKT_ENGINEER;

GRANT USAGE, OPERATE ON WAREHOUSE ENGINEERING_WH TO ROLE CUSTSERV_ENGINEER;
GRANT USAGE, OPERATE ON WAREHOUSE DEV_XFORM_WH TO ROLE CUSTSERV_ENGINEER;

GRANT DATABASE ROLE DEV_LZ.DB_R TO ROLE CUSTSERV_ENGINEER;
GRANT DATABASE ROLE DEV_DV.CUSTSERV_W TO ROLE CUSTSERV_ENGINEER;
GRANT DATABASE ROLE DEV_DV.DB_R TO ROLE CUSTSERV_ENGINEER;
GRANT DATABASE ROLE DEV_DW.CUSTSERV_W TO ROLE CUSTSERV_ENGINEER;

GRANT DATABASE ROLE DEV_DW.DB_R TO ROLE QA_ANALYST;

Conclusion And Resources

-- Explore as a Platform Admin -------------------------------------------------
USE ROLE PLT_ADMIN;
USE SECONDARY ROLES NONE;
USE WAREHOUSE ADMIN_WH;

SHOW DATABASES ->> SELECT "name", "comment" FROM $1 WHERE "kind" = 'STANDARD';

SHOW SCHEMAS IN DATABASE DEV_LZ ->> SELECT "database_name", "name", "comment" FROM $1;

-- Explore as a QA Analyst -----------------------------------------------------
USE ROLE QA_ANALYST;
USE WAREHOUSE ENGINEERING_WH;

SHOW DATABASES ->> SELECT "name", "comment" FROM $1 WHERE "kind" = 'STANDARD';

SHOW SCHEMAS IN DATABASE DEV_DW ->> SELECT "database_name", "name", "comment" FROM $1;

SHOW TAGS IN DATABASE PLT;

SELECT * FROM TABLE(DEV_DW.INFORMATION_SCHEMA.TAG_REFERENCES('DEV_DW.CUSTSERV', 'SCHEMA'));

SELECT * FROM TABLE(DEV_DW.INFORMATION_SCHEMA.TAG_REFERENCES('DEV_DW.SALESMKT', 'SCHEMA'));

SHOW SCHEMAS IN DATABASE DEV_DV; -- This will error, as the role doesn't have access

SHOW SCHEMAS IN DATABASE DEV_LZ; -- This will error, as the role doesn't have access

-- Explore as Dev LZ Ingest ----------------------------------------------------
USE ROLE DEV_LZ_INGEST;
USE WAREHOUSE DEV_INGEST_WH;

SHOW DATABASES ->> SELECT "name", "comment" FROM $1 WHERE "kind" = 'STANDARD';

SHOW SCHEMAS IN DATABASE DEV_LZ ->> SELECT "database_name", "name", "comment" FROM $1;

SHOW SCHEMAS IN DATABASE DEV_DV; -- This will error, as the role doesn't have access

What You Learned

What's Next