-- ============================================================================
-- join-strategy-diagnostics.sql
-- Find out whether a slow JOIN is missing the broadcast threshold, hitting a
-- skewed shuffle partition, or falling under the dynamic-file-pruning
-- thresholds on the large table -- using real system.query.history columns
-- plus the documented AQE/DFP size and file-count thresholds.
-- Source: Sqlism (sqlism.com) -- "How a Single JOIN Can Make a Databricks
-- Query 100x Slower"
--
-- Requires: access to system.query.history, and DESCRIBE DETAIL access to the
-- tables in your join. Replace '<warehouse-id>' and 'catalog.schema.table'.
-- ============================================================================

-- 1) Size and file count of each table in the join -- start here. Compare
--    against the documented defaults:
--      - Broadcast-eligible if well under ~30MB (spark.databricks.adaptive.autoBroadcastJoinThreshold)
--      - Dynamic file pruning needs the LARGE (probe-side) table to be at
--        least 10GB AND at least 10 files (deltaTableSizeThreshold /
--        deltaTableFilesThreshold) -- check sizeInBytes and numFiles below.
DESCRIBE DETAIL catalog.schema.small_lookup_table;
DESCRIBE DETAIL catalog.schema.large_fact_table;

-- 2) Shuffle volume and execution time for recent joins on this warehouse --
--    a join with large shuffle_read_bytes relative to produced_rows is a
--    shuffle join, not a broadcast join; this does NOT by itself confirm
--    skew, just that a shuffle happened at all.
SELECT
    statement_id,
    start_time,
    execution_duration_ms,
    shuffle_read_bytes,
    read_bytes,
    produced_rows,
    total_task_duration_ms
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND shuffle_read_bytes > 0
ORDER BY execution_duration_ms DESC
LIMIT 50;

-- 3) Rough skew signal from system.query.history alone: a large gap between
--    total_task_duration_ms (sum across all tasks) and execution_duration_ms
--    (wall clock) can indicate most tasks finished quickly while one or a
--    few ran far longer -- confirm with the Spark UI / query profile's own
--    per-task metrics for the specific statement_id before concluding skew.
SELECT
    statement_id,
    start_time,
    execution_duration_ms,
    total_task_duration_ms,
    ROUND(total_task_duration_ms / NULLIF(execution_duration_ms, 0), 1) AS task_to_wallclock_ratio
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND shuffle_read_bytes > 0
ORDER BY task_to_wallclock_ratio DESC
LIMIT 50;