/*
Iceberg v3 Variant Performance Benchmark — Snowflake all queries
=================================================================

This file contains the table definitions, synthetic data generation scripts,
and the 11 benchmark queries used in the Iceberg v3 Variant Performance blog
post on the Snowflake engineering blog (https://www.snowflake.com/en/engineering-blog).

The companion file `iceberg-variant-databricks-all-queries.sql` reproduces
the same benchmark against Databricks Iceberg tables so the comparison can
be re-run end-to-end on either engine.

Prerequisites
-------------
  * Snowflake account with Iceberg v3 enabled. Set ICEBERG_VERSION_DEFAULT = 3
    at the account, database, or schema level, or ICEBERG_VERSION = 3 on the
    table itself, before running the DDL below.
  * An XS Gen2 warehouse (or larger) for representative latencies — the
    benchmark in the blog post uses XS Gen2.

Methodology
-----------
  * Each query is run with 2 warmup runs followed by 5 measured runs.
  * The reported metric is the median end-to-end latency in milliseconds.
  * Result caching is disabled before the measurement runs.

Sections
--------
  1. Table definitions (DDL)
  2. Synthetic data generation
  3. Benchmark queries (Q1–Q11) across the four workload categories:
       - Workload 1: accessing specific fields from a Variant object
       - Workload 2: accessing the entire Variant object
       - Workload 3: accessing specific elements from a Variant array
       - Workload 4: accessing entire Variant arrays
*/

ALTER SESSION SET USE_CACHED_RESULT = FALSE;


/* =========================================================
   Section 1 — Table definitions (DDL)
   ========================================================= */

-- events_variant: 500M-row event log with a single VARIANT payload column.
-- Used for Workload 1 (field extraction) and Workload 2 (full-object retrieval).
CREATE OR REPLACE ICEBERG TABLE events_variant (
    event_id BIGINT  NOT NULL,
    payload  VARIANT
) ICEBERG_VERSION = 3;

-- arrays_variant: 10M-row table with three array-shaped VARIANT columns
-- (256-element string array, 256-element integer array, 64×64 2D integer matrix).
-- Used for Workload 3 (array element access) and Workload 4 (full-array retrieval).
CREATE OR REPLACE ICEBERG TABLE arrays_variant (
    id         BIGINT NOT NULL,
    arr_text   VARIANT,
    arr_number VARIANT,
    arr_graph  VARIANT
) ICEBERG_VERSION = 3;


/* =========================================================
   Section 2 — Synthetic data generation
   ========================================================= */

-- ---------------------------------------------------------
-- 2.1  Populate events_variant (500M rows)
-- ---------------------------------------------------------
INSERT INTO events_variant
WITH
  event_types AS (
    SELECT ARRAY_CONSTRUCT(
      'page_view','click','scroll','purchase','add_to_cart',
      'remove_from_cart','search','login','logout','signup',
      'share','comment','like','bookmark','download',
      'upload','print','export','import','settings_change'
    ) AS v
  ),
  countries AS (
    SELECT ARRAY_CONSTRUCT(
      'US','CN','IN','BR','JP','RU','DE','GB','FR','MX',
      'ID','NG','PK','BD','VN','KR','TR','IT','ES','TH',
      'PH','EG','AR','PL','CA','MY','AU','NL','SA','PE',
      'CO','ZA','UA','RO','CL','CZ','BE','SE','PT','AT',
      'CH','IL','HK','SG','NZ','IE','DK','FI','NO','GR'
    ) AS v
  ),
  raw AS (
    SELECT
      (ROW_NUMBER() OVER (ORDER BY SEQ8())) - 1    AS event_id,
      UNIFORM(0::FLOAT, 1::FLOAT, RANDOM())        AS p_skew,
      UNIFORM(0, 3,  RANDOM())                     AS et_top4_idx,
      UNIFORM(0, 19, RANDOM())                     AS et_all_idx,
      UNIFORM(0, 49, RANDOM())                     AS country_idx,
      UNIFORM(0, 299999, RANDOM())                 AS duration,
      UNIFORM(0, 9999999, RANDOM())                AS user_id,
      UNIFORM(0::FLOAT, 1::FLOAT, RANDOM())        AS p_premium,
      LOWER(SUBSTR(MD5(RANDOM()::VARCHAR), 1, 16)) AS session_str,
      UNIFORM(0, 4999, RANDOM())                   AS load_ms,
      UNIFORM(0, 9999, RANDOM())                   AS render_ms,
      UNIFORM(0::FLOAT, 1::FLOAT, RANDOM())        AS p_is_bot,
      UNIFORM(0, 49, RANDOM())                     AS tag1,
      UNIFORM(0, 49, RANDOM())                     AS tag2
    FROM TABLE(GENERATOR(rowcount => 500000000))
  )
SELECT
  r.event_id,
  OBJECT_CONSTRUCT(
    'event_type', IFF(r.p_skew < 0.8, et.v[r.et_top4_idx]::VARCHAR, et.v[r.et_all_idx]::VARCHAR),
    'country',    c.v[r.country_idx]::VARCHAR,
    'duration',   r.duration,
    'user',       OBJECT_CONSTRUCT(
                    'id',         r.user_id,
                    'is_premium', r.p_premium < 0.15,
                    'session',    r.session_str
                  ),
    'metrics',    OBJECT_CONSTRUCT(
                    'timing', OBJECT_CONSTRUCT('load_ms', r.load_ms, 'render_ms', r.render_ms),
                    'flags',  OBJECT_CONSTRUCT('is_bot', r.p_is_bot < 0.05)
                  ),
    'tags',       ARRAY_CONSTRUCT('tag_' || r.tag1, 'tag_' || r.tag2),
    'label',      'item_' || MOD(r.event_id, 10000)
  )::VARIANT AS payload
FROM raw r, event_types et, countries c;


-- ---------------------------------------------------------
-- 2.2  Populate arrays_variant (10M rows)
--      Three Python UDFs generate each array shape.
-- ---------------------------------------------------------
CREATE OR REPLACE FUNCTION gen_arr_text()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'run'
AS $$
import random, hashlib
def run():
    return [hashlib.md5(random.randbytes(8)).hexdigest()[:16] for _ in range(256)]
$$;

CREATE OR REPLACE FUNCTION gen_arr_number()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'run'
AS $$
import random
def run():
    return [random.randint(0, 999999) for _ in range(256)]
$$;

CREATE OR REPLACE FUNCTION gen_arr_graph()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'run'
AS $$
import random
def run():
    return [[random.randint(0, 999999) for _ in range(64)] for _ in range(64)]
$$;

INSERT INTO arrays_variant
SELECT
    (ROW_NUMBER() OVER (ORDER BY SEQ8())) - 1 AS id,
    gen_arr_text()   AS arr_text,
    gen_arr_number() AS arr_number,
    gen_arr_graph()  AS arr_graph
FROM TABLE(GENERATOR(rowcount => 10000000));


/* =========================================================
   Section 3 — Benchmark queries (Q1–Q11)
   ========================================================= */

-- ---------------------------------------------------------
-- Workload 1 — Accessing specific fields from a Variant object
-- ---------------------------------------------------------

-- Q1: Top-level field aggregation
SELECT SUM(payload:duration::INT) FROM events_variant;

-- Q2: Deeply nested path traversal
SELECT SUM(payload:metrics:timing:load_ms::INT) FROM events_variant;

-- Q3: Filtered aggregation with predicates on variant fields
SELECT COUNT(event_id), SUM(payload:duration::INT)
FROM events_variant
WHERE payload:event_type::STRING = 'purchase'
  AND payload:country::STRING = 'US';

-- Q4: Grouped aggregation across variant fields
SELECT payload:event_type::STRING AS event_type,
       COUNT(event_id) AS cnt,
       SUM(payload:duration::INT) AS total_duration
FROM events_variant
GROUP BY payload:event_type::STRING;


-- ---------------------------------------------------------
-- Workload 2 — Accessing the entire Variant object
-- ---------------------------------------------------------

-- Q5: Full object retrieval with sort
SELECT payload FROM events_variant ORDER BY payload:duration::INT LIMIT 10;


-- ---------------------------------------------------------
-- Workload 3 — Accessing specific elements from a Variant array
-- ---------------------------------------------------------

-- Q6: Numeric element access
SELECT SUM(arr_number[0]::INT) FROM arrays_variant;

-- Q7: String element access
SELECT MIN(arr_text[0]::VARCHAR) FROM arrays_variant;

-- Q8: Nested 2D array element access
SELECT SUM(arr_graph[0][3]::INT) FROM arrays_variant;


-- ---------------------------------------------------------
-- Workload 4 — Accessing entire Variant arrays
-- ---------------------------------------------------------

-- Q9: Full numeric array retrieval
SELECT arr_number FROM arrays_variant ORDER BY arr_number[0]::INT LIMIT 10;

-- Q10: Full string array retrieval
SELECT arr_text FROM arrays_variant ORDER BY arr_text[0]::VARCHAR LIMIT 10;

-- Q11: Full 2D array retrieval
SELECT arr_graph FROM arrays_variant ORDER BY arr_graph[0][3]::INT LIMIT 10;
