Snowflake是否追踪仓库大小调整数值？如何查询该类数据？

WAREHOUSE_EVENTS_HISTORY 仅记录仓库的启停、挂起、恢复等生命周期事件，不会存储仓库大小变更的数值信息，你可以通过以下两种方案获取对应数据：

方案1：查询QUERY_HISTORY视图提取调整操作记录 #

所有仓库大小调整操作都会以ALTER WAREHOUSE语句的形式记录在查询历史中，你可以过滤这类语句直接提取调整前后的规格信息，参考查询语句如下：

SELECT 
  start_time AS adjust_time,
  user_name AS operator,
  role_name AS operate_role,
  warehouse_name,
  -- 提取调整后的仓库规格
  REGEXP_EXTRACT(query_text, 'WAREHOUSE_SIZE\\s*=\\s*\\'?([A-Z0-9-]+)\\'?', 1) AS adjusted_size,
  query_text AS full_alter_statement
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
  QUERY_TYPE = 'ALTER'
  AND QUERY_TEXT ILIKE '%SET WAREHOUSE_SIZE%'
  -- 按需调整查询时间范围
  AND start_time >= DATEADD(DAY, -90, CURRENT_TIMESTAMP())
ORDER BY adjust_time DESC;

注意：如果没有ACCOUNT_USAGE的访问权限，你可以替换为当前会话可用的INFORMATION_SCHEMA.QUERY_HISTORY，仅会返回当前角色有权限查看的操作记录。

方案2：通过计量视图验证规格变更 #

你可以查询WAREHOUSE_METERING_HISTORY视图的计费数据验证调整后的规格是否生效：

• Snowflake仓库单集群每小时消耗的计算积分和规格强绑定：X-SMALL每小时消耗1积分，每升高一级规格积分消耗翻倍，即SMALL为2、MEDIUM为4、LARGE为8，以此类推。
• 参考查询语句：

SELECT 
  start_time,
  warehouse_name,
  credits_used_compute,
  -- 反推对应时间段生效的仓库规格
  CASE CEIL(credits_used_compute)
    WHEN 1 THEN 'X-SMALL'
    WHEN 2 THEN 'SMALL'
    WHEN 4 THEN 'MEDIUM'
    WHEN 8 THEN 'LARGE'
    WHEN 16 THEN 'X-LARGE'
    WHEN 32 THEN 'XX-LARGE'
    WHEN 64 THEN 'XXX-LARGE'
    ELSE '高于XXX-LARGE规格'
  END AS warehouse_size_in_effect
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE warehouse_name = '你的仓库名称'
AND start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

内容的提问来源于stack exchange，提问作者Felipe Hoffa