-- =============================================================================
-- PERISCUBE LTD. | PRINCIPAL ARCHITECTURE & FINOPS DIAGNOSTIC PACK
-- File       : 02_warehouse_credit_leak_check.sql
-- Platform   : Snowflake Data Cloud & dbt Data Vault 2.1 Lakehouse
-- Author     : Principal Architects / Engineers (Periscube Ltd.)
-- Entity     : Periscube Ltd. (Registered in England & Wales, No. 07691565)
-- Website    : https://periscube.com/offers/exadata-finops-auditor/
-- License    : Free Permission Diagnostic Asset ($0) - Read-Only Execution
-- =============================================================================
-- SAFETY & SOVEREIGNTY GUARANTEE:
--   1. 100% READ-ONLY: Queries SNOWFLAKE.ACCOUNT_USAGE metadata views only.
--   2. ZERO WAREHOUSE ALTERATION: Never alters warehouse sizes or suspends jobs.
--   3. ZERO PII EXPOSURE: Inspects query telemetry and partition pruning ratios
--      without reading customer tables or confidential business records.
-- =============================================================================

USE ROLE ACCOUNTADMIN; -- Or any FinOps governance role granted SNOWFLAKE.OBJECT_VIEWER
USE DATABASE SNOWFLAKE;
USE SCHEMA ACCOUNT_USAGE;

-- =============================================================================
-- [01/05] IDLE WAREHOUSE CREDIT BURN & AUTO-SUSPEND LEAKAGE (LAST 30 DAYS)
-- Compares billed compute hours against actual query execution seconds
-- =============================================================================
WITH metering AS (
    SELECT
        warehouse_name,
        ROUND(SUM(credits_used_compute), 2) AS billed_compute_credits,
        ROUND(SUM(credits_used_cloud_services), 2) AS billed_cloud_svc_credits,
        ROUND(SUM(credits_used), 2) AS total_billed_credits
    FROM snowflake.account_usage.warehouse_metering_history
    WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
      AND warehouse_name IS NOT NULL
    GROUP BY warehouse_name
),
execution_load AS (
    SELECT
        warehouse_name,
        COUNT(*) AS total_queries_30d,
        ROUND(SUM(execution_time) / 1000.0 / 3600.0, 2) AS active_execution_hours,
        ROUND(AVG(queued_overload_time) / 1000.0, 2) AS avg_queue_sec
    FROM snowflake.account_usage.query_history
    WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
      AND warehouse_name IS NOT NULL
    GROUP BY warehouse_name
)
SELECT
    m.warehouse_name,
    m.total_billed_credits AS credits_30d,
    ROUND(m.total_billed_credits * 12 * 2.40, 0) AS est_annual_spend_gbp,
    COALESCE(e.total_queries_30d, 0) AS queries_30d,
    COALESCE(e.active_execution_hours, 0) AS active_query_hrs,
    COALESCE(e.avg_queue_sec, 0) AS avg_queue_wait_sec,
    CASE
        WHEN COALESCE(e.active_execution_hours, 0) < (m.billed_compute_credits * 0.25)
        THEN 'CRITICAL: HIGH IDLE AUTO-RESUME BURN (>75% IDLE CAPACITY)'
        WHEN COALESCE(e.active_execution_hours, 0) < (m.billed_compute_credits * 0.50)
        THEN 'WARNING: UNDER-UTILIZED WAREHOUSE TIER / LONG AUTO-SUSPEND'
        ELSE 'HEALTHY: HIGH COMPUTE UTILIZATION EFFICIENCY'
    END AS finops_verdict
FROM metering m
LEFT JOIN execution_load e
  ON m.warehouse_name = e.warehouse_name
ORDER BY m.total_billed_credits DESC;

-- =============================================================================
-- [02/05] REMOTE OBJECT-STORE SPILLING & MEMORY THRASHING OFFENDERS
-- Queries spilling to remote S3/Blob/GCS incur 5x-12x runtime & credit inflation
-- =============================================================================
SELECT
    query_id,
    user_name,
    role_name,
    warehouse_name,
    warehouse_size,
    ROUND(total_elapsed_time / 1000.0, 1) AS elapsed_sec,
    ROUND(bytes_scanned / POWER(1024, 3), 2) AS scanned_gb,
    ROUND(bytes_spilled_to_local_storage / POWER(1024, 3), 2) AS local_spill_gb,
    ROUND(bytes_spilled_to_remote_storage / POWER(1024, 3), 2) AS remote_spill_gb,
    LEFT(REGEXP_REPLACE(query_text, '\\s+', ' '), 110) AS query_snippet
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  AND (bytes_spilled_to_remote_storage > 0 OR bytes_spilled_to_local_storage > 5 * POWER(1024, 3))
ORDER BY bytes_spilled_to_remote_storage DESC, bytes_spilled_to_local_storage DESC
LIMIT 25;

-- =============================================================================
-- [03/05] MICRO-PARTITION PRUNING FAILURE & FULL SATELLITE SCAN OFFENDERS
-- Detects queries scanning >= 80% of micro-partitions on tables > 50 GB
-- =============================================================================
SELECT
    query_id,
    warehouse_name,
    database_name,
    schema_name,
    ROUND(bytes_scanned / POWER(1024, 3), 2) AS bytes_scanned_gb,
    partitions_scanned,
    partitions_total,
    ROUND((partitions_scanned / NULLIF(partitions_total, 0)) * 100, 1) AS partition_scan_pct,
    ROUND(total_elapsed_time / 1000.0, 1) AS elapsed_sec,
    'CRITICAL: BROKEN CLUSTERING KEY OR NON-SARGABLE LOAD_DTS FILTER' AS diagnostic_flag,
    LEFT(REGEXP_REPLACE(query_text, '\\s+', ' '), 110) AS query_snippet
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  AND partitions_total > 1000
  AND bytes_scanned > 50 * POWER(1024, 3)
  AND (partitions_scanned::FLOAT / NULLIF(partitions_total, 0)) >= 0.80
ORDER BY bytes_scanned DESC
LIMIT 25;

-- =============================================================================
-- [04/05] REDUNDANT DBT FULL-REFRESH MATERIALIZATIONS ON DATA VAULT MODELS
-- Flags recurring CREATE OR REPLACE TABLE rebuilds that should be incremental
-- =============================================================================
SELECT
    database_name,
    schema_name,
    warehouse_name,
    COUNT(*) AS full_rebuild_count_14d,
    ROUND(SUM(total_elapsed_time) / 1000.0 / 3600.0, 2) AS total_rebuild_hours,
    ROUND(SUM(bytes_written_to_result) / POWER(1024, 3), 2) AS total_written_gb,
    LEFT(REGEXP_REPLACE(ANY_VALUE(query_text), '\\s+', ' '), 100) AS sample_ddl_pattern
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  AND query_type IN ('CREATE_TABLE_AS_SELECT', 'CREATE_TABLE')
  AND query_text ILIKE 'CREATE OR REPLACE%TABLE%'
  AND bytes_written_to_result > 5 * POWER(1024, 3)
GROUP BY database_name, schema_name, warehouse_name
HAVING COUNT(*) >= 5
ORDER BY total_rebuild_hours DESC
LIMIT 20;

-- =============================================================================
-- [05/05] FAIL-SAFE & TIME-TRAVEL STORAGE WASTE ON STAGING / RAW LANDING TABLES
-- Identifies staging tables paying permanent 7-day Fail-Safe overhead
-- =============================================================================
SELECT
    table_catalog AS database_name,
    table_schema AS schema_name,
    table_name,
    is_transient,
    ROUND(active_bytes / POWER(1024, 3), 2) AS active_gb,
    ROUND(time_travel_bytes / POWER(1024, 3), 2) AS time_travel_gb,
    ROUND(failsafe_bytes / POWER(1024, 3), 2) AS failsafe_gb,
    ROUND((time_travel_bytes + failsafe_bytes) / NULLIF(active_bytes, 0) * 100, 1) AS storage_overhead_pct,
    'CONVERT STAGING/LANDING TABLES TO TRANSIENT (DATA_RETENTION_TIME_IN_DAYS = 0)' AS action_required
FROM snowflake.account_usage.table_storage_metrics
WHERE deleted = FALSE
  AND (failsafe_bytes + time_travel_bytes) > 25 * POWER(1024, 3)
  AND (
      table_schema ILIKE '%STG%'
      OR table_schema ILIKE '%STAGE%'
      OR table_schema ILIKE '%RAW%'
      OR table_schema ILIKE '%LANDING%'
      OR table_name ILIKE 'STG_%'
  )
ORDER BY (failsafe_bytes + time_travel_bytes) DESC
LIMIT 25;

-- =============================================================================
-- AUDIT COMPLETE. To submit sanitized output for a 5-Day Principal Bottleneck
-- Diagnostic (£2,500 flat fee, 3x ROI refund guarantee):
-- https://periscube.com/offers/5-day-exadata-diagnostic/
-- =============================================================================