Interview Question

"How would you design a data sharing architecture where multiple business units can share curated datasets without copying data? What are the security and governance implications?"

Secure Data Sharing Fundamentals

Snowflake enables direct, secure data sharing between accounts without copying or transferring data. The provider retains control over the data.

Sharing Architecture

Creating Shares

-- 1. Create a share
CREATE SHARE sales_share
    COMMENT = 'Sales data shared with analytics team';

-- 2. Grant usage on database to share
GRANT USAGE ON DATABASE analytics_db TO SHARE sales_share;

-- 3. Grant usage on schema
GRANT USAGE ON SCHEMA analytics_db.sales TO SHARE sales_share;

-- 4. Grant SELECT on tables
GRANT SELECT ON TABLE analytics_db.sales.orders TO SHARE sales_share;
GRANT SELECT ON TABLE analytics_db.sales.customers TO SHARE sales_share;

-- 5. Create a secure view for sharing (mask sensitive data)
CREATE OR REPLACE SECURE VIEW shared_sales_view AS
SELECT 
    order_id,
    order_date,
    customer_id,
    product_category,
    amount,
    region,
    -- Mask sensitive columns
    NULL AS customer_email,
    NULL AS payment_method
FROM analytics_db.sales.orders;

-- 6. Grant SELECT on secure view
GRANT SELECT ON VIEW analytics_db.sales.shared_sales_view TO SHARE sales_share;

-- 7. Add consumer accounts to the share
ALTER SHARE sales_share ADD ACCOUNT = consumer_account_1;
ALTER SHARE sales_share ADD ACCOUNT = consumer_account_2;

-- 8. Check share status
SELECT 
    share_name,
    share_type,
    owner_name,
    created_on,
    source_database
FROM information_schema.shares
WHERE share_name = 'SALES_SHARE';

Consumer Side

-- 1. Create database from share
CREATE DATABASE sales_analytics_db FROM SHARE provider_account.sales_share;

-- 2. Grant access to roles
GRANT IMPORTED PRIVILEGES ON DATABASE sales_analytics_db TO ROLE analyst;

-- 3. Query shared data
SELECT * FROM sales_analytics_db.sales.shared_sales_view;

-- 4. Monitor shared data usage
SELECT 
    table_name,
    query_count,
    last_query_time
FROM information_schema.database_usage
WHERE database_name = 'SALES_ANALYTICS_DB';

Real-World Scenario: Uber

Question: "How do you share real-time trip data with partner companies while ensuring they only see their own trips and can't access competitor data?"

Solution: Row-Level Sharing with Security

-- 1. Create a share with row-level security
CREATE SHARE partner_trips_share
    COMMENT = 'Trip data shared with partners';

-- 2. Create a secure view with row-level filtering
CREATE OR REPLACE SECURE VIEW partner_trips_view AS
SELECT 
    trip_id,
    pickup_location,
    dropoff_location,
    trip_date,
    trip_distance,
    fare_amount,
    partner_id,
    -- Mask driver information
    NULL AS driver_id,
    NULL AS driver_name,
    -- Mask rider information
    NULL AS rider_id,
    CONCAT(LEFT(rider_name, 1), '***') AS rider_name_masked
FROM trips
WHERE partner_id = CURRENT_SESSION():partner_id;  -- Dynamic filtering

-- 3. Create a mapping table for partner access
CREATE TABLE partner_access_map (
    partner_id VARCHAR(100),
    partner_name VARCHAR(200),
    allowed_tables ARRAY,
    max_rows_per_query NUMBER
);

-- 4. Create row access policy for partners
CREATE OR REPLACE ROW ACCESS POLICY partner_access_policy 
AS (partner_id VARCHAR) RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'PARTNER_MANAGER') THEN TRUE
        WHEN EXISTS (
            SELECT 1 FROM partner_access_map 
            WHERE partner_id = partner_id
        ) THEN TRUE
        ELSE FALSE
    END;

-- 5. Apply policy and share
ALTER TABLE trips ADD ROW ACCESS POLICY partner_access_policy ON (partner_id);
GRANT SELECT ON TABLE trips TO SHARE partner_trips_share;

-- 6. Add partner accounts
ALTER SHARE partner_trips_share ADD ACCOUNT = partner_uber_account;

Data Marketplace

-- 1. Browse marketplace listings
SHOW MARKETPLACE LISTINGS;

-- 2. Create a listing (provider side)
CREATE OR REPLACE LISTING my_data_listing
    TITLE = 'Company Sales Data'
    DESCRIPTION = 'Aggregated sales data by region and product category'
    COMMENT = 'Updated daily'
    ENABLED = TRUE
    DATA_SHARING_SCOPE = 'PRIVATE'
    CONSUMER_ENTITIES = ('consumer_account_1', 'consumer_account_2');

-- 3. Set up listing refresh schedule
ALTER LISTING my_data_listing 
    SET REFRESH_SCHEDULE = 'USING CRON 0 6 * * * UTC';

-- 4. Monitor listing usage
SELECT 
    listing_name,
    consumer_account,
    query_count,
    last_query_time,
    data_freshness
FROM information_schema.marketplace_listing_usage
WHERE listing_name = 'MY_DATA_LISTING';

Best Practices