Tutorial: Migrate Snowflake Data Sharing to Delta Sharing¶

-- Snowflake: List all shares you provide
SHOW SHARES;

-- Detailed share information
SELECT
    share_name,
    database_name,
    owner,
    kind,
    created_on,
    comment
FROM INFORMATION_SCHEMA.APPLICABLE_ROLES
-- Or use:
SHOW GRANTS OF SHARE <share_name>;

-- Snowflake: List shares you consume
SHOW SHARES;

-- Filter to inbound shares
SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "kind" = 'INBOUND';

-- Databricks SQL: Check metastore sharing settings
-- Navigate to: Databricks Workspace > Catalog > Sharing > Enabled
-- Or via API:
-- GET /api/2.1/unity-catalog/metastore-summary

-- Create the Delta share
CREATE SHARE IF NOT EXISTS finance_data_share
COMMENT 'Financial data shared with Agency B per MOU-2026-042. Migrated from Snowflake share.';

-- Add tables to the share
ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.fct_invoice_aging;

ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.fct_payment_summary;

ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.dim_vendors;

ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.dim_cost_centers;

ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.dim_fiscal_periods;

-- Add views if needed
ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.v_vendor_performance;

ALTER SHARE finance_data_share
ADD TABLE analytics_prod.marts.v_aging_summary;

-- List all objects in the share
SHOW ALL IN SHARE finance_data_share;

-- Verify table data is accessible
SELECT COUNT(*) FROM analytics_prod.marts.fct_invoice_aging;

-- Create recipient for Agency B
CREATE RECIPIENT IF NOT EXISTS agency_b_finance_team
COMMENT 'Agency B data engineering team - MOU-2026-042';

-- Grant the share to the recipient
GRANT SELECT ON SHARE finance_data_share TO RECIPIENT agency_b_finance_team;

-- Get the activation link
DESCRIBE RECIPIENT agency_b_finance_team;
-- Copy the 'activation_link' value -- this is a one-time URL

-- Create recipient using the recipient's sharing identifier
CREATE RECIPIENT IF NOT EXISTS agency_b_databricks
USING ID 'aws:us-east-1:recipient-sharing-id'
COMMENT 'Agency B Databricks workspace';

GRANT SELECT ON SHARE finance_data_share TO RECIPIENT agency_b_databricks;

-- Create external location pointing to your ADLS Gen2
CREATE EXTERNAL LOCATION IF NOT EXISTS finance_external
URL 'abfss://finance@stadatalakeprod.dfs.core.windows.net/'
WITH (STORAGE CREDENTIAL finance_storage_cred);

-- Step 1: Accept the share (via activation link or sharing ID)
-- Navigate to Databricks Workspace > Catalog > Shared with me
-- Click on the pending share and accept it

-- Step 2: Create a catalog from the share
CREATE CATALOG IF NOT EXISTS agency_a_finance_shared
USING SHARE provider_org.finance_data_share;

-- Step 3: Query shared data
SELECT *
FROM agency_a_finance_shared.marts.fct_invoice_aging
WHERE aging_bucket = '60+'
ORDER BY amount DESC
LIMIT 100;

-- Step 4: Verify freshness
SELECT MAX(updated_at) AS latest_data
FROM agency_a_finance_shared.marts.fct_invoice_aging;

-- Run on Snowflake (existing share)
SELECT
    'fct_invoice_aging' AS table_name,
    COUNT(*) AS row_count,
    SUM(amount) AS total_amount,
    MAX(updated_at) AS latest_update
FROM FINANCE_DB.MARTS.FCT_INVOICE_AGING

UNION ALL

SELECT
    'dim_vendors',
    COUNT(*),
    NULL,
    MAX(updated_at)
FROM FINANCE_DB.MARTS.DIM_VENDORS;

-- Run on Databricks (new share)
SELECT
    'fct_invoice_aging' AS table_name,
    COUNT(*) AS row_count,
    SUM(amount) AS total_amount,
    MAX(updated_at) AS latest_update
FROM analytics_prod.marts.fct_invoice_aging

UNION ALL

SELECT
    'dim_vendors',
    COUNT(*),
    NULL,
    MAX(updated_at)
FROM analytics_prod.marts.dim_vendors;

-- Reconciliation query (run on Databricks after data is migrated)
SELECT
    'Row count match' AS check_name,
    CASE WHEN sf_count = db_count THEN 'PASS' ELSE 'FAIL' END AS status,
    sf_count AS snowflake_value,
    db_count AS databricks_value
FROM (
    SELECT
        (SELECT COUNT(*) FROM analytics_prod.validation.sf_invoice_aging_snapshot) AS sf_count,
        (SELECT COUNT(*) FROM analytics_prod.marts.fct_invoice_aging) AS db_count
)

UNION ALL

SELECT
    'Total amount match',
    CASE WHEN ABS(sf_total - db_total) / NULLIF(sf_total, 0) < 0.001 THEN 'PASS' ELSE 'FAIL' END,
    CAST(sf_total AS STRING),
    CAST(db_total AS STRING)
FROM (
    SELECT
        (SELECT SUM(amount) FROM analytics_prod.validation.sf_invoice_aging_snapshot) AS sf_total,
        (SELECT SUM(amount) FROM analytics_prod.marts.fct_invoice_aging) AS db_total
);

-- Create a connection to the partner's Snowflake
CREATE CONNECTION partner_snowflake
TYPE snowflake
OPTIONS (
    host 'partner-account.snowflakecomputing.com',
    port '443',
    user 'shared_reader',
    password SECRET ('scope', 'partner-snowflake-password')
);

-- Create a foreign catalog
CREATE FOREIGN CATALOG partner_census
USING CONNECTION partner_snowflake
OPTIONS (database 'CENSUS_DATA');

-- Query partner data via federation
SELECT * FROM partner_census.public.population_estimates LIMIT 100;