Part 2: Zero-Copy data federation Snowflake Customer 360 Data to Salesforce Sales Reps

-- ============================================================
-- COMPLETE DEMO SCRIPT: Accounts + Customer 360 + Final Table
-- ============================================================

-- Optional: set your context
-- USE ROLE <your_role>;
-- USE WAREHOUSE <your_warehouse>;
-- USE DATABASE <your_database>;
-- USE SCHEMA <your_schema>;

-- ------------------------------------------------------------
-- 1) Clean up old objects (safe for reruns)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS ACCOUNT_C360_SNAPSHOT;
DROP TABLE IF EXISTS CUSTOMER_360_METRICS;
DROP TABLE IF EXISTS ACCOUNTS;

-- ------------------------------------------------------------
-- 2) Create source tables
-- ------------------------------------------------------------
CREATE OR REPLACE TABLE ACCOUNTS (
    ACCOUNT_ID NUMBER,
    ACCOUNT_NAME STRING,
    INDUSTRY STRING,
    ANNUAL_REVENUE NUMBER(18,2),
    DELETED_AT TIMESTAMP_NTZ
);

CREATE OR REPLACE TABLE CUSTOMER_360_METRICS (
    ACCOUNT_ID NUMBER,
    HEALTH_SCORE FLOAT,
    CHURN_RISK FLOAT,
    NPS_SCORE NUMBER(5,2),
    ARR NUMBER(18,2),
    NEXT_BEST_PRODUCT STRING,
    LAST_ENGAGEMENT_DATE DATE
);

-- ------------------------------------------------------------
-- 3) Insert sample data (toy companies)
-- ------------------------------------------------------------
INSERT INTO ACCOUNTS (ACCOUNT_ID, ACCOUNT_NAME, INDUSTRY, ANNUAL_REVENUE, DELETED_AT) VALUES
(1001, 'Bright Blocks Toys',  'Toys', 25000000, NULL),
(1002, 'Rocket Wheels Co',    'Toys',  8000000, NULL),
(1003, 'Cuddle Critters Inc', 'Toys', 12000000, NULL),
(1004, 'Puzzle Planet Ltd',   'Toys', 30000000, NULL),
(1005, 'Old Toy Factory',     'Toys',  5000000, '2025-01-15 10:00:00'),
(NULL, 'Bad Row',             'Toys',   100000, NULL);

INSERT INTO CUSTOMER_360_METRICS (
    ACCOUNT_ID, HEALTH_SCORE, CHURN_RISK, NPS_SCORE, ARR, NEXT_BEST_PRODUCT, LAST_ENGAGEMENT_DATE
) VALUES
(1001, 0.91, 0.22, 60, 1800000, 'STEM Robot Kit',           '2026-03-10'),
(1002, 0.67, 0.45, 35,  650000, 'Turbo Track Racing Set',   '2026-03-05'),
(1003, 0.43, 0.81, 10,  420000, 'Deluxe Plush Care Pack',   '2026-02-20'),
(1004, 0.80, 0.70, 55, 2200000, 'Master Puzzle Collection', '2026-03-14'),
(9999, 0.95, 0.10, 70,  900000, 'Collector Train Set',      '2026-03-01'); -- no matching account

-- ------------------------------------------------------------
-- 4) Create final table using your view definition logic
-- ------------------------------------------------------------
CREATE OR REPLACE TABLE ACCOUNT_C360_SNAPSHOT AS
SELECT
    A.ACCOUNT_ID,
    UPPER(A.ACCOUNT_NAME) AS ACCOUNT_NAME,
    A.INDUSTRY,
    A.ANNUAL_REVENUE,
    C360.HEALTH_SCORE,
    C360.CHURN_RISK,
    C360.NPS_SCORE,
    C360.ARR,
    C360.NEXT_BEST_PRODUCT,
    C360.LAST_ENGAGEMENT_DATE,
    CASE
        WHEN C360.HEALTH_SCORE >= 0.8 THEN 'Healthy'
        WHEN C360.HEALTH_SCORE >= 0.5 THEN 'At Risk'
        ELSE 'Critical'
    END AS HEALTH_STATUS,
    CASE
        WHEN C360.CHURN_RISK >= 0.7 THEN 'High'
        WHEN C360.CHURN_RISK >= 0.4 THEN 'Medium'
        ELSE 'Low'
    END AS CHURN_RISK_LEVEL,
    CURRENT_TIMESTAMP() AS LAST_UPDATED
FROM ACCOUNTS A
INNER JOIN CUSTOMER_360_METRICS C360
    ON A.ACCOUNT_ID = C360.ACCOUNT_ID
WHERE A.ACCOUNT_ID IS NOT NULL
  AND A.DELETED_AT IS NULL;