One Data Lake, Three Clouds: Multi-Cloud Analytics with DataSpoc

production.raw.users
production.raw.orders
production.raw.products
production.curated.daily_revenue
acquired.raw.app_users
acquired.raw.app_events
acquired.raw.subscriptions
finance.raw.invoices
finance.raw.payments
finance.raw.budget_forecast

-- Match acquired app users with production users by email
SELECT
    p.name AS production_name,
    p.email,
    p.plan AS production_plan,
    a.subscription_tier AS acquired_plan,
    a.last_active AS acquired_last_active,
    f.total_invoiced
FROM production.raw.users p
JOIN acquired.raw.app_users a
    ON LOWER(p.email) = LOWER(a.email)
LEFT JOIN (
    SELECT customer_email, SUM(amount) AS total_invoiced
    FROM finance.raw.invoices
    GROUP BY customer_email
) f ON LOWER(p.email) = LOWER(f.customer_email)
ORDER BY f.total_invoiced DESC NULLS LAST
LIMIT 20;

┌─────────────────┬──────────────────────┬────────┬───────────────────┬────────────────┐
│      name       │        email         │  plan  │ subscription_tier │ total_invoiced │
├─────────────────┼──────────────────────┼────────┼───────────────────┼────────────────┤
│ Acme Corp       │ admin@acme.com       │ enterprise │ premium       │     125,400.00 │
│ TechFlow Inc    │ ops@techflow.io      │ business   │ pro           │      89,200.00 │
│ Global Systems  │ data@globalsys.com   │ business   │ premium       │      67,800.00 │
└─────────────────┴──────────────────────┴────────┴───────────────────┴────────────────┘

Table                          Status    Size      Last Refreshed
production.raw.users           cached    1.2 MB    2 min ago
production.raw.orders          cached    8.4 MB    2 min ago
acquired.raw.app_users         cached    3.1 MB    1 min ago
finance.raw.invoices           cached    5.6 MB    1 min ago
production.raw.products        stale     42 KB     3 days ago
acquired.raw.app_events        not cached  --      --

AWS S3 (production):
  └── IAM Policy: DataEngineers group → full access
  └── IAM Policy: Analysts group → read-only

GCS (acquired):
  └── IAM Policy: IntegrationTeam → read-only
  └── IAM Policy: AcquiredTeam → full access

Azure (finance):
  └── IAM Policy: FinanceTeam → full access
  └── IAM Policy: Executives → read-only

-- transforms/01_customer_360.sql
CREATE OR REPLACE TABLE gold.customer_360 AS
WITH production_data AS (
    SELECT
        LOWER(email) AS email,
        name,
        plan AS production_plan,
        created_at AS production_signup,
        DATE_DIFF('day', created_at, CURRENT_DATE) AS days_as_customer
    FROM production.raw.users
),
acquired_data AS (
    SELECT
        LOWER(email) AS email,
        subscription_tier AS acquired_plan,
        last_active AS acquired_last_active,
        feature_usage_score
    FROM acquired.raw.app_users
),
finance_data AS (
    SELECT
        LOWER(customer_email) AS email,
        SUM(amount) AS total_invoiced,
        COUNT(*) AS invoice_count,
        MAX(invoice_date) AS last_invoice_date
    FROM finance.raw.invoices
    WHERE status = 'paid'
    GROUP BY 1
),
order_data AS (
    SELECT
        LOWER(u.email) AS email,
        COUNT(*) AS total_orders,
        SUM(o.amount) AS total_revenue,
        MAX(o.order_date) AS last_order_date
    FROM production.raw.orders o
    JOIN production.raw.users u ON o.user_id = u.user_id
    WHERE o.status = 'completed'
    GROUP BY 1
)
SELECT
    p.email,
    p.name,
    p.production_plan,
    p.production_signup,
    p.days_as_customer,
    a.acquired_plan,
    a.acquired_last_active,
    a.feature_usage_score,
    f.total_invoiced,
    f.invoice_count,
    o.total_orders,
    o.total_revenue,
    o.last_order_date,
    CASE
        WHEN a.email IS NOT NULL THEN 'both_products'
        ELSE 'production_only'
    END AS product_usage,
    COALESCE(o.total_revenue, 0) + COALESCE(f.total_invoiced, 0) AS total_ltv
FROM production_data p
LEFT JOIN acquired_data a ON p.email = a.email
LEFT JOIN finance_data f ON p.email = f.email
LEFT JOIN order_data o ON p.email = o.email;

┌────────────────┬───────────┬──────────┐
│ product_usage  │ customers │ avg_ltv  │
├────────────────┼───────────┼──────────┤
│ both_products  │     2,340 │ 1,245.80 │
│ production_only│    10,110 │   342.50 │
└────────────────┴───────────┴──────────┘

from dataspoc_lens import LensClient

lens = LensClient()

# Tables from all clouds appear in one catalog
tables = lens.tables()
print(tables)
# ['production.raw.users', 'acquired.raw.app_users', 'finance.raw.invoices', ...]

# Cross-cloud query
result = lens.query("""
    SELECT COUNT(DISTINCT p.email) AS overlap
    FROM production.raw.users p
    JOIN acquired.raw.app_users a ON LOWER(p.email) = LOWER(a.email)
""")
print(f"Users on both platforms: {result}")