5.1 Direct Data Sharing Architecture & Security

5.1 Direct Data Sharing Architecture & Security

Traditional data sharing across corporate entities has long been plagued by operational friction, security vulnerabilities, and data staleness. Legacy architectures rely on batch extraction scripts, secure FTP transfers, API gateways, or public cloud object storage bucket policies. These mechanisms create unmanaged, divergent data copies, incur massive egress and storage costs, and sever central governance.

Snowflake solves this paradigm through Native Direct Data Sharing. By leveraging Snowflake's multi-tenant metadata catalog and multi-cluster shared data architecture, Direct Data Sharing enables cross-account data access without physically copying or moving data files between accounts.

Zero-Copy Data Sharing Architecture

In Snowflake's three-tier architecture (Cloud Services, Virtual Warehouses, and Centralized Storage), data sharing operates entirely at the Cloud Services (metadata) layer.

┌────────────────────────────────────────────────────────────────────────┐
│                            PROVIDER ACCOUNT                            │
│                                                                        │
│   ┌─────────────────────┐               ┌──────────────────────────┐   │
│   │   Base Data Table   │               │       Secure Share       │   │
│   │  (Micro-partitions  │               │    (Catalog Grants &     │   │
│   │   in Cloud Storage) │◄──────────────┤    Consumer Metadata)    │   │
│   └──────────┬──────────┘               └─────────────┬────────────┘   │
└──────────────┼────────────────────────────────────────┼────────────────┘
               │                                        │
               │ Immutable Micro-Partition Pointers     │ Shared Metadata
               ▼                                        ▼
┌────────────────────────────────────────────────────────────────────────┐
│                            CONSUMER ACCOUNT                            │
│                                                                        │
│   ┌─────────────────────┐               ┌──────────────────────────┐   │
│   │ Consumer Warehouse  │──────────────►│ Mounted Shared Database  │   │
│   │ (Executes Queries & │ Reads Data    │ (Read-Only Representation│   │
│   │  Pays All Compute)  │               │   of Provider Schema)    │   │
│   └─────────────────────┘               └──────────────────────────┘   │
└────────────────────────────────────────────────────────────────────────┘

The Zero-Copy Mechanics

1. Storage Mechanics: When a provider publishes a table to a share, the underlying immutable micro-partitions remain stored in the provider's cloud storage bucket. No replication, file copying, or export takes place.
2. Metadata Registration: The provider's Cloud Services layer constructs a Secure Share object. This share contains metadata pointers referencing the specific micro-partitions that constitute the shared tables or views.
3. Live, Real-Time Visibility: Because the consumer queries the provider's live micro-partitions through active metadata pointers, any transaction committed by the provider (INSERT, UPDATE, DELETE, MERGE) is immediately visible to consumer queries. There is zero replication latency or synchronization delay.
4. Decoupled Compute & Billing:
   • Provider Cost: The provider pays only for the persistent data storage of the base tables in their account. The provider pays zero compute credits when consumers query the shared data.
   • Consumer Cost: The consumer must provision their own virtual warehouse to query the mounted shared database. All compute charges (warehouse credits and associated cloud services) are billed 100% to the consumer's account.
   • Cloud Services Charges: Provider cloud services credits are not consumed when a consumer compiles and runs a query against a share; the consumer's Cloud Services layer processes the query plan.

Regional and Cloud Boundaries

A foundational rule tested on the SnowPro Advanced: Architect exam is the geographical and cloud boundary of standard Direct Data Sharing:

Architect Rule: Standard Direct Data Sharing requires both the provider account and consumer account to reside in the same cloud provider and the same geographic cloud region (e.g., both accounts on AWS us-east-1 or both on Azure westeurope). Direct Data Sharing across different regions or across different cloud providers cannot be executed directly; it requires Snowflake Database Replication or Cross-Cloud Auto-Fulfillment.

Secure Share Administration Workflow

A Share is a first-class, account-level securable object in Snowflake. Creating and administering shares requires strict role privileges and an exact sequence of declarative SQL grants.

Privilege Prerequisites

• To create a share: The user must hold the ACCOUNTADMIN system role, or hold a custom role that has been explicitly granted the global CREATE SHARE privilege.
• To grant privileges to a share: The acting role must own the share or hold ACCOUNTADMIN, and must hold the USAGE privilege on the database and schema containing the shared objects, as well as SELECT on the objects.

-- Delegating share administration to a dedicated governance role
USE ROLE ACCOUNTADMIN;
CREATE ROLE data_sharing_admin;
GRANT CREATE SHARE ON ACCOUNT TO ROLE data_sharing_admin;
GRANT USAGE ON WAREHOUSE governance_wh TO ROLE data_sharing_admin;
GRANT ROLE data_sharing_admin TO USER architect_lead;

Step-by-Step Provider Share Creation

The provider executes four distinct stages to author and publish a share:

USE ROLE data_sharing_admin;

-- Step 1: Create the empty Share object
CREATE SHARE partner_sales_share
  COMMENT = 'Outbound daily aggregated sales orders for regional distributors';

-- Step 2: Grant USAGE on the database container
GRANT USAGE ON DATABASE corp_sales_db TO SHARE partner_sales_share;

-- Step 3: Grant USAGE on the schema container
GRANT USAGE ON SCHEMA corp_sales_db.distributor_analytics TO SHARE partner_sales_share;

-- Step 4: Grant SELECT on specific tables and secure views
GRANT SELECT ON TABLE corp_sales_db.distributor_analytics.dim_products 
  TO SHARE partner_sales_share;

GRANT SELECT ON VIEW corp_sales_db.distributor_analytics.sec_vw_orders 
  TO SHARE partner_sales_share;

-- Step 5: Associate consumer accounts with the Share
ALTER SHARE partner_sales_share ADD ACCOUNTS = myorg.consumer_east, xy12345;

Account Identifier Conventions in ADD ACCOUNTS

When adding consumer accounts via ALTER SHARE ... ADD ACCOUNTS, architects can specify accounts using either modern Organization URLs or legacy Account Locators:

• Organization Account Identifier (Recommended): <org_name>.<account_name> (e.g., acme_global.distributor_us). This format is resilient to cloud migration and human-readable.
• Account Locator: <account_locator> (e.g., XY12345). If the account is in a different region, the locator format requires region qualifiers (xy12345.us-east-1), though direct sharing remains restricted to the same region unless replication is established.

Inspecting and Modifying Shares

-- List outbound shares authored by this account
SHOW SHARES;

-- View objects and privileges granted to a specific share
DESCRIBE SHARE partner_sales_share;

-- View which consumer accounts are granted access
SHOW GRANTS OF SHARE partner_sales_share;

-- Revoke consumer access
ALTER SHARE partner_sales_share REMOVE ACCOUNTS = xy12345;

Consumer-Side Database Mounting

Once added to the share, the consumer account sees the inbound share and mounts it as a read-only database:

-- Executed within the Consumer Account
USE ROLE ACCOUNTADMIN;

-- Step 1: View incoming shares available to this account
SHOW SHARES;

-- Step 2: Create a local database mounted directly from the inbound share
CREATE DATABASE distributor_sales_inbound 
  FROM SHARE myorg.provider_account.partner_sales_share;

-- Step 3: Grant access on the mounted database to consumer internal roles
GRANT USAGE ON DATABASE distributor_sales_inbound TO ROLE bi_analyst;
GRANT USAGE ON ALL SCHEMAS IN DATABASE distributor_sales_inbound TO ROLE bi_analyst;
GRANT SELECT ON ALL TABLES IN DATABASE distributor_sales_inbound TO ROLE bi_analyst;
GRANT SELECT ON ALL VIEWS IN DATABASE distributor_sales_inbound TO ROLE bi_analyst;

Secure Objects: Views, Materialized Views & UDFs

Snowflake only supports secure views in shares (standard views cannot be added to a share), and Secure Objects—Secure Views, Secure Materialized Views, and Secure UDFs—exist to protect both the logic and the data behind them.

Why Standard Views Are Insecure

Standard views present two weaknesses whenever users should see only part of the underlying data — inside your own account as well as across accounts:

1. Query Definition Exposure: Standard view definitions can be inspected via GET_DDL(), SHOW VIEWS, or the INFORMATION_SCHEMA.VIEWS catalog. If a view definition contains proprietary algorithms, business logic, or sensitive internal table names, standard views expose that code.
2. The Optimizer Predicate Pushdown Attack (CRITICAL EXAM TRAP): Snowflake's cost-based query optimizer aggressively pushes user-supplied filter predicates (WHERE clauses) down into the innermost query blocks before joins or complex transformations execute. In multi-tenant environments, a malicious consumer can exploit predicate pushdown to infer sensitive rows they are not permitted to see via runtime errors or timing channels.

Mechanics of the Predicate Pushdown Attack

Consider an underlying provider table containing records across multiple client tenants, protected by a view filtering on tenant_id:

-- Provider creates a standard view meant to isolate Tenant A
CREATE VIEW sales_db.public.v_tenant_a_orders AS
SELECT order_id, customer_ssn, transaction_amount
FROM sales_db.internal.all_orders
WHERE tenant_id = 'TENANT_A';

A malicious user in Tenant A wants to verify whether a competitor, TENANT_B, has processed an order with a specific Social Security Number pattern. The user executes a query injecting a division-by-zero expression:

SELECT order_id 
FROM sales_db.public.v_tenant_a_orders
WHERE 1 / (CASE WHEN customer_ssn LIKE '999%' THEN 0 ELSE 1 END) = 1;

• In a Standard View: The optimizer pushes the user's WHERE clause down to the base table all_orders before the view's WHERE tenant_id = 'TENANT_A' filter is applied.
• If a record belonging to TENANT_B matches the 999% pattern, the expression triggers a Division by zero runtime exception. The attacker observes the query failure and successfully deduces that a sensitive record exists in the competitor's hidden dataset!

How SECURE Views Prevent Side-Channel Leakage

-- Create a Secure View
CREATE OR REPLACE SECURE VIEW sales_db.public.sec_vw_tenant_a_orders AS
SELECT order_id, customer_ssn, transaction_amount
FROM sales_db.internal.all_orders
WHERE tenant_id = 'TENANT_A';

-- Or convert an existing view to secure
ALTER VIEW sales_db.public.v_tenant_a_orders SET SECURE;

When a view is defined as SECURE:

1. Optimizer Pushdown Suppression: The Snowflake query compiler strictly guarantees that the view's internal query and filters are fully evaluated first. User-supplied filter predicates are never pushed down past the secure view boundary. The division-by-zero attack fails because unauthorized rows are filtered out before the user's predicate executes.
2. DDL Concealment: For any user or consumer role that does not own the secure view, Snowflake hides the view definition text. Calling GET_DDL('VIEW', ...) or querying INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION returns NULL or empty strings.
3. Query Profile Redaction: Execution details and operator stats in the Snowflake Query Profile are redacted for consumer queries referencing secure views, preventing consumers from reconstructing data distribution statistics.

Performance Implications of Secure Views

Because secure views disable predicate pushdown across the view boundary, the optimizer cannot prune micro-partitions as aggressively when consumers supply selective filters. Queries against secure views may scan more micro-partitions than identical queries against standard views. Architects must balance security requirements against query performance.

Secure Materialized Views & Secure UDFs

• Secure Materialized Views (CREATE SECURE MATERIALIZED VIEW): Provide precomputed query results maintained automatically by Snowflake's serverless maintenance service. They offer the performance benefits of materialized views while enforcing DDL hiding and optimizer isolation.
• Secure UDFs (CREATE SECURE FUNCTION): Prevent consumers from viewing proprietary code, formulas, or machine learning scoring weights embedded in SQL, Python, Java, or JavaScript UDFs.

Cross-Account Privileges, Operational Constraints & Exam Traps

Architects designing cross-account data topologies must understand the absolute operational boundaries enforced on shared databases within consumer accounts.

1. Strictly Read-Only Access

Consumers have read-only access to mounted shared databases. The following operations are strictly rejected by the Snowflake compiler:

• DML statements: INSERT, UPDATE, DELETE, MERGE, TRUNCATE.
• DDL modifications: ALTER TABLE ... ADD COLUMN, DROP TABLE, DROP SCHEMA.
• Writing to tables via stages or Snowpipe into the shared database.

2. Time Travel Restrictions on Shared Objects (CRITICAL EXAM TRAP)

• Consumers CANNOT use Time Travel on shared tables.
• Executing SELECT * FROM shared_db.sch.table AT(OFFSET => -300); or BEFORE(STATEMENT => '<query_id>') in a consumer account immediately produces a compilation error: Time travel is not supported for shared tables.
• Architect Rationale: The Time Travel retention lifecycle (DATA_RETENTION_TIME_IN_DAYS) is managed and paid for exclusively by the provider account on base tables. Consumers cannot dictate or query historical snapshots across the share boundary.

3. Resharing Is Controlled by the Provider

• By default, a consumer cannot reshare objects from an imported database; attempts to grant them to an outbound share fail.
• A provider can allow resharing. Consumers check the resharing_settings column in SHOW DATABASES for the imported database before building a reshare.
• Even when allowed, a direct share cannot be reshared with accounts outside the provider's organization.
• This keeps the provider in control of licensing and provenance — data is not daisy-chained to unknown parties.

4. Zero-Copy Cloning Constraints

• Consumers cannot clone a shared database or any schema/table within it.
• Executing CREATE DATABASE local_db CLONE shared_db; or CREATE TABLE local_tbl CLONE shared_db.sch.shared_table; fails.
• The CTAS Exception: While cloning is blocked, consumers can copy shared data into local permanent tables using CREATE TABLE local_tbl AS SELECT * FROM shared_db.sch.shared_table;. Once executed, the new table is a separate, independent physical copy stored in the consumer's account, incurring consumer storage fees and severing live updates from the provider.

5. Streams and Change Tracking on Shared Tables

• A consumer can create a Stream on a shared table or secure view, provided the provider enabled CHANGE_TRACKING = TRUE on the underlying source table.
• This allows consumer accounts to implement downstream Change Data Capture (CDC) architectures and incremental consumption pipelines without polling full tables.

Operational Capability Comparison Matrix