Building a Snowflake Cost Monitoring Setup Using Only Omni's AI Agent

※ This is the English translation of the article above.
https://dev.classmethod.jp/articles/snowflake-cost-monitoring-with-omni/
※ Note: The instructions to the Agent were given in Japanese, but the text in this article has been translated into English. Please keep this in mind.

Hello, this is Sagara.

In our internal Snowflake sandbox environment, rising costs had become an issue. To address this, I tried using only the Agent feature of the BI tool Omni to build everything end to end — from requirements definition to creating a Semantic Layer and setting up a Routine.

https://dev.classmethod.jp/articles/omni-try-routine/

https://docs.omni.co/ai/routines

I've already summarized what the Routine feature itself does in the article above, so I'll skip the feature overview in this post.

:::message
A note up front: this article omits the details of the dialogue with the AI (verbatim prompt logs) and instead focuses on "what design and deliverables I ultimately arrived at." Also, the scope was to complete everything — from requirements definition to writing the Routine prompt text — using only Omni's built-in Agent feature, without any external AI tools.
:::

Background and Problem

Costs were trending upward in our internal Snowflake sandbox account. In particular, usage related to SPCS, Snowflake CoWork, and CoCo would sometimes cause sudden cost spikes.

Investigating each of these drivers by writing SQL every time is tedious, so I wanted a mechanism that visualizes costs daily and automatically posts to Slack when the increase is significant.

Technical Approach

This time, I modeled Snowflake's ACCOUNT_USAGE schema in the BI tool Omni and built the following two things:

1. A Semantic Layer (Topics and Views) for Snowflake cost analysis
2. A Routine that automatically delivers the previous day's cost changes to Slack

The build flow was as follows. Everything from requirements definition to creating the Routine was done purely through dialogue with Omni's Agent feature.

1. Tell the Omni Agent the monitoring requirements and have it interview me and design the Topic/View structure
2. Within the same Agent conversation, have it implement both the Semantic Layer (Topic/View) YAML code and the Routine
3. Run the created Routine and check the delivered content

Omni offers several AI features, including a "Modeling Agent" that assists with YAML editing in the model IDE. This time I pushed the Agent feature all the way from requirements definition onward. As for Routines, in addition to creating them from the Routines page, there is also a way to create a new Routine purely through a chat with the Omni Agent.

https://docs.omni.co/ai

Limitations

• Omni models (Views/Topics) are not applied immediately just by telling the Agent "please change this" in chat. Until you save the files in the model editor or via Git and Publish (merge) the branch, the Agent cannot see the updated model. So even though I say "done entirely with the Agent," note that publishing model changes still requires manual operation.
• What the Omni Agent can do in chat is create new Routines only. Management operations such as fine-tuning the schedule, destination, and recipients after creation, or pausing/deleting a Routine, must be done via the Edit routine link shown in the conversation, or from the Routines page.

Prerequisites

• Snowflake account: I'm using our internal Snowflake sandbox account
• Snowflake: You need a role that can reference the SNOWFLAKE.ACCOUNT_USAGE schema (equivalent to IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE)
• Omni: A Snowflake connection already configured, on a plan where the Agent and Routine features are available
• Slack integration: If you use Slack as the Routine delivery destination, the Omni–Slack integration must be set up in advance
  • https://docs.omni.co/share/deliveries/slack

Preparation

Check the role that can reference Snowflake's ACCOUNT_USAGE

Confirm that the role assigned to Omni's connection user can reference the SNOWFLAKE.ACCOUNT_USAGE schema.

-- Run with the role assigned to Omni's connection user, and check
-- whether IMPORTED PRIVILEGES on the SNOWFLAKE database has been granted
SHOW GRANTS TO ROLE <role name of Omni connection user>;

If IMPORTED PRIVILEGES on the SNOWFLAKE database appears in the list, you're good.

Bring the ACCOUNT_USAGE schema into the Omni model

Run a schema refresh from Omni's model screen to bring each view in the SNOWFLAKE.ACCOUNT_USAGE schema into the model.

If the list of views under ACCOUNT_USAGE appears in the model's View list, you're all set.

Trying It Out

1. Give the Omni Agent the requirements, then design and implement

I opened the Agent in Omni, conveyed the following two goals, and had it interview me and design the Model/View/Topic structure that should be built. I asked it to raise questions from its side for anything unclear.

I want to monitor Snowflake costs on a daily basis.
Please design an Omni model structure (Views and Topics) that can achieve the following two things.
If anything is unclear, please interview me before implementing.

1. I want to create a dashboard that shows daily usage
2. Using an Omni Routine, I want to compare yesterday and the day before,
   and notify Slack of credit consumption along with the driving objects and their owner roles
   (if something looks anomalous, I also want to know which objects to check and their owner roles,
   and for warehouses, who in particular was running queries)

Here's what came back:

I replied as follows:

1. Credits are fine as-is
2. I want everything included — CoWork, CoCo, Trust Center, etc. — extracted without omissions
3. JST basis
4. What I want notified to Slack is "all resources that consumed 1 credit or more that day"
5. Single account only
6. <Slack channel name>, at 6:00 in the morning

It then generated tasks and started working:

As shown below, Views get created one after another.

This time, three Views were created.

Relationships were defined as well.

Two Topics were also created, as shown below.

Finally, the Routine was created. It's nice that the Routine creation screen automatically appears in the IDE so you can review it.

After Merge-ing the generated code, the work in the IDE is done.

About the generated code

For reference, here's the code the Agent generated this time.

:::details account_usage__snowflake_cost_hourly_jst (the View behind the Snowflake Daily Cost Topic — click to expand)

# Reference this view as account_usage__snowflake_cost_hourly_jst
label: Snowflake Cost (JST)
description: METERING_HISTORY を JST に変換した時間単位のクレジット消費。全サービスタイプが漏れなく含まれる、コスト監視の基準View。entity_type / resource_name
  で要因オブジェクトを特定できる。

sql: |
  SELECT
    CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', m.start_time) AS usage_at_jst,
    m.service_type,
    m.entity_type,
    m.name AS resource_name,
    m.database_name,
    m.schema_name,
    m.credits_used,
    m.credits_used_compute,
    m.credits_used_cloud_services
  FROM ${account_usage__metering_history} AS m

dimensions:
  usage_at_jst:
    sql: '"USAGE_AT_JST"'
    label: Usage At (JST)
    description: 利用時刻（JST）。日次監視には [date] を使用。
    timeframes: [ raw, hour, date, week, month, quarter, year, hour_of_day, day_of_week_name ]
    convert_tz: false
    synonyms: [ 利用日, 日付, usage date ]

  service_type:
    sql: '"SERVICE_TYPE"'
    label: Service Type
    description: 課金サービスタイプ。METERING_HISTORY のため全サービスが網羅される。
    synonyms: [ サービス, サービスタイプ ]

  entity_type:
    sql: '"ENTITY_TYPE"'
    label: Entity Type
    description: コストを発生させたオブジェクトの種別（WAREHOUSE, PIPE, MATERIALIZED_VIEW, TASK 等）。
    synonyms: [ オブジェクト種別, リソース種別 ]

  resource_name:
    sql: '"RESOURCE_NAME"'
    label: Resource Name
    description: コストを発生させたオブジェクト名（Warehouse名・Pipe名・Table名など）。
    synonyms: [ リソース, オブジェクト, 要因オブジェクト ]

  database_name:
    sql: '"DATABASE_NAME"'
    label: Database

  schema_name:
    sql: '"SCHEMA_NAME"'
    label: Schema

  resource_full_name:
    sql: COALESCE(${database_name} || '.' || ${schema_name} || '.' || ${resource_name}, ${resource_name})
    label: Resource Full Name
    description: DB.SCHEMA.NAME 形式の完全修飾名。オーナーロールの結合キー。

  credits_used:
    sql: '"CREDITS_USED"'
    hidden: true

  credits_used_compute:
    sql: '"CREDITS_USED_COMPUTE"'
    hidden: true

  credits_used_cloud_services:
    sql: '"CREDITS_USED_CLOUD_SERVICES"'
    hidden: true

measures:
  credits_used_total:
    sql: ${credits_used}
    label: Credits Used
    format: number_4
    description: 消費クレジット合計（compute + cloud services）。
    aggregate_type: sum
    synonyms: [ クレジット, コスト, 消費量 ]

  credits_compute:
    sql: ${credits_used_compute}
    label: Credits (Compute)
    format: number_4
    aggregate_type: sum

  credits_cloud_services:
    sql: ${credits_used_cloud_services}
    label: Credits (Cloud Services)
    format: number_4
    aggregate_type: sum

  resource_count:
    sql: ${resource_full_name}
    label: Resource Count
    aggregate_type: count_distinct

ai_context: |
  Snowflakeコスト監視の基準View。ACCOUNT_USAGE.METERING_HISTORY は課金される全サービスタイプ（Warehouse、Pipe、Auto Clustering、Materialized View、Serverless Task、Snowflake CoWork、Cortex 各種、Trust Center 等）を含むため、コスト総額はこのViewを唯一の真実として扱う。日付は全てJST（Asia/Tokyo）変換済み。
  - 「コスト」「クレジット」は credits_used_total を使う
  - 「要因オブジェクト」は resource_name（+ database_name / schema_name / resource_full_name）
  - entity_type が WAREHOUSE の場合、誰がクエリを回していたかは account_usage__warehouse_query_attribution_jst を参照
  - ACCOUNT_USAGE は最大3時間程度の反映遅延があるため当日データは不完全な場合がある

:::

:::details account_usage__object_owner_role (a lookup View for retrieving the owner role of cost-driving objects — click to expand)

# Reference this view as account_usage__object_owner_role
label: Object Owner Role
description: Table / View / Task / Pipe のオーナーロールを DB.SCHEMA.NAME
  の完全修飾名で統合したルックアップ。コスト要因オブジェクトの確認担当者（オーナーロール）を特定するために使う。削除済みオブジェクトは除外。

sql: |
  SELECT
    t.table_catalog || '.' || t.table_schema || '.' || t.table_name AS resource_full_name,
    t.table_type AS object_type,
    t.table_owner AS owner_role,
    t.owner_role_type
  FROM ${account_usage__tables} AS t
  WHERE t.deleted IS NULL

  UNION ALL

  SELECT
    v.table_catalog || '.' || v.table_schema || '.' || v.table_name AS resource_full_name,
    'VIEW' AS object_type,
    v.table_owner AS owner_role,
    v.owner_role_type
  FROM ${account_usage__views} AS v
  WHERE v.deleted IS NULL

  UNION ALL

  SELECT
    k.task_database || '.' || k.task_schema || '.' || k.task_name AS resource_full_name,
    'TASK' AS object_type,
    k.task_owner AS owner_role,
    k.owner_role_type
  FROM ${account_usage__tasks} AS k
  WHERE k.deleted IS NULL

  UNION ALL

  SELECT
    p.pipe_catalog || '.' || p.pipe_schema || '.' || p.pipe_name AS resource_full_name,
    'PIPE' AS object_type,
    p.pipe_owner AS owner_role,
    p.owner_role_type
  FROM ${account_usage__pipes} AS p
  WHERE p.deleted IS NULL

dimensions:
  resource_full_name:
    sql: '"RESOURCE_FULL_NAME"'
    label: Resource Full Name
    description: DB.SCHEMA.NAME 形式の完全修飾名。コストViewとの結合キー。
    primary_key: true

  object_type:
    sql: '"OBJECT_TYPE"'
    label: Object Type
    description: BASE TABLE / VIEW / TASK / PIPE などのオブジェクト種別。

  owner_role:
    sql: '"OWNER_ROLE"'
    label: Owner Role
    description: オブジェクトのオーナーロール。異常時に確認を依頼すべきロール。
    synonyms: [ オーナーロール, オーナー, 所有者, 担当ロール ]

  owner_role_type:
    sql: '"OWNER_ROLE_TYPE"'
    label: Owner Role Type
    description: ROLE / DATABASE_ROLE などのロール種別。

measures:
  object_count:
    label: Object Count
    aggregate_type: count

ai_context: |
  コスト要因オブジェクトのオーナーロールを引くためのルックアップView。resource_full_name（DB.SCHEMA.NAME）で snowflake_cost_hourly_jst に結合する。owner_role が「確認すべき担当ロール」。Warehouse はオーナーロールを持たないため、Warehouse の責任者特定には warehouse_query_attribution_jst を使う。

:::

:::details account_usage__warehouse_query_attribution_jst (the View behind the Snowflake Warehouse Query Attribution Topic — click to expand)

# Reference this view as account_usage__warehouse_query_attribution_jst
label: Warehouse Query Attribution (JST)
description: QUERY_ATTRIBUTION_HISTORY と QUERY_HISTORY を結合し、Warehouse
  クレジットをクエリ単位でユーザー・ロールに帰属させたView。日付は JST。Warehouse
  コストが急増した際に「誰が特にクエリを回していたか」を特定するために使う。

sql: |
  SELECT
    CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', qa.end_time) AS usage_at_jst,
    qa.warehouse_name,
    qa.query_id,
    qa.query_parameterized_hash,
    qa.query_tag,
    qa.user_name,
    qh.role_name,
    qh.role_type,
    qh.warehouse_size,
    qh.database_name,
    qh.schema_name,
    LEFT(qh.query_text, 300) AS query_text_preview,
    qh.total_elapsed_time,
    qa.credits_attributed_compute,
    qa.credits_used_query_acceleration
  FROM ${account_usage__query_attribution_history} AS qa
  LEFT JOIN ${account_usage__query_history} AS qh
    ON qh.query_id = qa.query_id

dimensions:
  usage_at_jst:
    sql: '"USAGE_AT_JST"'
    label: Usage At (JST)
    description: クエリ終了時刻（JST）。
    timeframes: [ raw, hour, date, week, month, hour_of_day ]
    convert_tz: false
    synonyms: [ 日付, 利用日 ]

  warehouse_name:
    sql: '"WAREHOUSE_NAME"'
    label: Warehouse
    synonyms: [ ウェアハウス ]

  query_id:
    sql: '"QUERY_ID"'
    label: Query ID
    primary_key: true

  query_parameterized_hash:
    sql: '"QUERY_PARAMETERIZED_HASH"'
    label: Query Parameterized Hash
    description: パラメータを正規化したクエリハッシュ。反復クエリの特定に使う。

  query_tag:
    sql: '"QUERY_TAG"'
    label: Query Tag

  user_name:
    sql: '"USER_NAME"'
    label: User
    description: クエリを実行したユーザー。
    synonyms: [ ユーザー, 実行者, 誰 ]

  role_name:
    sql: '"ROLE_NAME"'
    label: Role
    description: クエリ実行時のロール。
    synonyms: [ ロール, 実行ロール ]

  role_type:
    sql: '"ROLE_TYPE"'
    label: Role Type

  warehouse_size:
    sql: '"WAREHOUSE_SIZE"'
    label: Warehouse Size

  database_name:
    sql: '"DATABASE_NAME"'
    label: Database

  schema_name:
    sql: '"SCHEMA_NAME"'
    label: Schema

  query_text_preview:
    sql: '"QUERY_TEXT_PREVIEW"'
    label: Query Text (先頭300文字)

  total_elapsed_time:
    sql: '"TOTAL_ELAPSED_TIME"'
    hidden: true

  credits_attributed_compute:
    sql: '"CREDITS_ATTRIBUTED_COMPUTE"'
    hidden: true

  credits_used_query_acceleration:
    sql: '"CREDITS_USED_QUERY_ACCELERATION"'
    hidden: true

measures:
  credits_attributed_total:
    sql: ${credits_attributed_compute}
    label: Credits Attributed
    format: number_4
    description: クエリに帰属された Warehouse コンピュートクレジット。
    aggregate_type: sum
    synonyms: [ クレジット, コスト ]

  credits_query_acceleration:
    sql: ${credits_used_query_acceleration}
    label: Credits (Query Acceleration)
    format: number_4
    aggregate_type: sum

  query_count:
    sql: ${query_id}
    label: Query Count
    aggregate_type: count_distinct
    synonyms: [ クエリ数, 実行回数 ]

  user_count:
    sql: ${user_name}
    label: User Count
    aggregate_type: count_distinct

  total_elapsed_seconds:
    sql: ${total_elapsed_time} / 1000
    label: Total Elapsed (sec)
    format: number_1
    aggregate_type: sum

ai_context: |
  Warehouse コストの人・ロール別内訳View。credits_attributed_compute はクエリ単位に帰属された実際のWarehouseクレジットで、warehouse_name 単位の合計は METERING_HISTORY の Warehouse クレジットにほぼ一致する（クラウドサービス分やアイドル時間分は含まれない）。
  - 「誰がクエリを回していたか」→ user_name / role_name で credits_attributed_total を降順
  - 「どのクエリが重かったか」→ query_id, query_text_preview で降順
  - 日付は usage_at_jst[date] を使う

:::

:::details snowflake_daily_cost.topic (the main Topic for the Routine — click to expand)

base_view: account_usage__snowflake_cost_hourly_jst
base_view_label: Cost
label: Snowflake Daily Cost (JST)
group_label: Snowflake Cost Monitoring
display_order: 1
description: Snowflakeの日次クレジット消費を全サービスタイプ横断で監視するTopic。サービスタイプ・要因オブジェクト・オーナーロール別に分解できる。日付はすべてJST。

default_filters:
  account_usage__snowflake_cost_hourly_jst.usage_at_jst:
    time_for_duration: [ 30 days ago, 30 days ]

joins:
  account_usage__object_owner_role: {}

ai_context: |
  Snowflakeコスト監視のメインTopic。ACCOUNT_USAGE.METERING_HISTORY ベースなので、Warehouse・Serverless Task・Auto Clustering・Materialized View・Snowpipe・Snowflake CoWork・Cortex 各種・Trust Center など課金される全サービスタイプが漏れなく含まれる。

  使い分け:
  - 「今日/昨日のコスト」「日次推移」→ usage_at_jst[date] × credits_used_total
  - 「何が原因か」→ service_type, entity_type, resource_name で分解
  - 「誰に確認すべきか」→ object_owner_role.owner_role（Table/View/Task/Pipe のオーナーロール）
  - Warehouse は owner_role を持たないため、entity_type = 'WAREHOUSE' の場合は Snowflake Warehouse Query Attribution Topic でユーザー・ロール別の内訳を見る
  - 前日と一昨日の比較には period over period（1日前）を使う

  注意: ACCOUNT_USAGE は最大3時間程度の反映遅延があるため、当日分は不完全な場合がある。前日以前で比較すること。

sample_queries:
  daily_credits_by_service_type:
    query:
      fields:
        [
          "account_usage__snowflake_cost_hourly_jst.usage_at_jst[date]",
          account_usage__snowflake_cost_hourly_jst.service_type,
          account_usage__snowflake_cost_hourly_jst.credits_used_total
        ]
      base_view: account_usage__snowflake_cost_hourly_jst
      filters:
        account_usage__snowflake_cost_hourly_jst.usage_at_jst:
          time_for_duration: [ 30 days ago, 30 days ]
      limit: 5000
      sorts:
        - field: account_usage__snowflake_cost_hourly_jst.usage_at_jst[date]
          desc: true
      topic: snowflake_daily_cost
    description: ダッシュボードの主要タイル。日次推移をサービスタイプで分解する。
    prompt: 直近30日の日次クレジットをサービスタイプ別に見たい
  yesterday_resources_with_owner:
    query:
      fields:
        [
          account_usage__snowflake_cost_hourly_jst.service_type,
          account_usage__snowflake_cost_hourly_jst.entity_type,
          account_usage__snowflake_cost_hourly_jst.resource_name,
          account_usage__object_owner_role.owner_role,
          account_usage__snowflake_cost_hourly_jst.credits_used_total
        ]
      base_view: account_usage__snowflake_cost_hourly_jst
      filters:
        account_usage__snowflake_cost_hourly_jst.usage_at_jst:
          is: yesterday
      limit: 500
      sorts:
        - field: account_usage__snowflake_cost_hourly_jst.credits_used_total
          desc: true
      topic: snowflake_daily_cost
    description: Slack通知用の基本クエリ。要因リソースをオーナーロール付きで抽出。
    prompt: 前日のリソース別クレジットとオーナーロールを多い順で
  yesterday_vs_day_before:
    query:
      fields:
        [
          account_usage__snowflake_cost_hourly_jst.service_type,
          account_usage__snowflake_cost_hourly_jst.entity_type,
          account_usage__snowflake_cost_hourly_jst.resource_name,
          account_usage__object_owner_role.owner_role,
          account_usage__snowflake_cost_hourly_jst.credits_used_total
        ]
      base_view: account_usage__snowflake_cost_hourly_jst
      filters:
        account_usage__snowflake_cost_hourly_jst.usage_at_jst:
          is: yesterday
      limit: 500
      sorts:
        - field: account_usage__snowflake_cost_hourly_jst.credits_used_total
          desc: true
      topic: snowflake_daily_cost
      period_over_period:
        - field_name: account_usage__snowflake_cost_hourly_jst.usage_at_jst
          time_unit_name: DAY
          periods_ago: 1
    description: Routine用。前日 vs 一昨日のリソース別増減。
    prompt: 前日と一昨日のリソース別クレジットを比較して

:::

:::details snowflake_warehouse_query_attribution.topic (a Topic for identifying the responsible party behind Warehouse costs — click to expand)

base_view: account_usage__warehouse_query_attribution_jst
base_view_label: Query Attribution
label: Snowflake Warehouse Query Attribution (JST)
group_label: Snowflake Cost Monitoring
display_order: 2
description: Warehouseクレジットをクエリ単位でユーザー・ロールに帰属させたTopic。Warehouseコスト急増時に「誰が特にクエリを回していたか」「どのクエリが重かったか」を特定する。日付はJST。

default_filters:
  account_usage__warehouse_query_attribution_jst.usage_at_jst:
    time_for_duration: [ 30 days ago, 30 days ]

joins: {}

ai_context: |
  Warehouse コストの人・ロール・クエリ別内訳Topic。Snowflake Daily Cost Topic で entity_type = 'WAREHOUSE' のコスト増加を見つけたら、このTopicで原因者を特定する。

  使い分け:
  - 「誰がクエリを回していたか」→ user_name / role_name × credits_attributed_total 降順
  - 「どのクエリが重いか」→ query_id, query_text_preview × credits_attributed_total 降順
  - 「反復している重いクエリ」→ query_parameterized_hash で集約し query_count と credits_attributed_total
  - 特定Warehouseに絞るときは warehouse_name でフィルタ

  注意: credits_attributed_total はクエリに帰属されたコンピュートクレジットのみで、アイドル時間やクラウドサービス分は含まれない。総額は Snowflake Daily Cost Topic を正とする。

sample_queries:
  top_users_yesterday:
    query:
      fields:
        [
          account_usage__warehouse_query_attribution_jst.warehouse_name,
          account_usage__warehouse_query_attribution_jst.user_name,
          account_usage__warehouse_query_attribution_jst.role_name,
          account_usage__warehouse_query_attribution_jst.credits_attributed_total,
          account_usage__warehouse_query_attribution_jst.query_count
        ]
      base_view: account_usage__warehouse_query_attribution_jst
      filters:
        account_usage__warehouse_query_attribution_jst.usage_at_jst:
          is: yesterday
      limit: 100
      sorts:
        - field: account_usage__warehouse_query_attribution_jst.credits_attributed_total
          desc: true
      topic: snowflake_warehouse_query_attribution
    description: Warehouseコストの原因者特定。
    prompt: 前日の Warehouse クレジットをユーザー・ロール別に多い順で
  heaviest_queries_yesterday:
    query:
      fields:
        [
          account_usage__warehouse_query_attribution_jst.query_id,
          account_usage__warehouse_query_attribution_jst.warehouse_name,
          account_usage__warehouse_query_attribution_jst.user_name,
          account_usage__warehouse_query_attribution_jst.role_name,
          account_usage__warehouse_query_attribution_jst.query_text_preview,
          account_usage__warehouse_query_attribution_jst.credits_attributed_total
        ]
      base_view: account_usage__warehouse_query_attribution_jst
      filters:
        account_usage__warehouse_query_attribution_jst.usage_at_jst:
          is: yesterday
      limit: 20
      sorts:
        - field: account_usage__warehouse_query_attribution_jst.credits_attributed_total
          desc: true
      topic: snowflake_warehouse_query_attribution
    description: 異常時に確認すべき具体的なクエリ。
    prompt: 前日の重いクエリをクレジット順に20件

:::

:::details relationships (the two relationships that were added — click to expand)

# コスト要因オブジェクトにオーナーロールを付与する
- join_from_view: account_usage__snowflake_cost_hourly_jst
  join_to_view: account_usage__object_owner_role
  join_type: always_left
  on_sql: ${account_usage__snowflake_cost_hourly_jst.resource_full_name} =
    ${account_usage__object_owner_role.resource_full_name}
  relationship_type: many_to_one

# Warehouse コストを日次でクエリ実行者に紐づける
- join_from_view: account_usage__snowflake_cost_hourly_jst
  join_to_view: account_usage__warehouse_query_attribution_jst
  join_type: always_left
  on_sql: ${account_usage__snowflake_cost_hourly_jst.resource_name} =
    ${account_usage__warehouse_query_attribution_jst.warehouse_name} AND
    ${account_usage__snowflake_cost_hourly_jst.usage_at_jst[date]} =
    ${account_usage__warehouse_query_attribution_jst.usage_at_jst[date]}
  relationship_type: one_to_many

:::

:::details Omni Routine configuration (notify at 6:00 JST every morning — click to expand)

[Name]
Snowflake日次コスト監視（前日 vs 一昨日）

[Schedule]
0 6 ? * * *  （タイムゾーン: Asia/Tokyo）

[Destination]
Slackチャンネル: <Slackチャンネル名>

[Prompt]
Snowflake Daily Cost (JST) Topic を使って、前日（JST）と一昨日（JST）のクレジット消費を比較してください。

1. まず全体サマリ: 前日の総クレジットと一昨日の総クレジット、増減量と増減率。

2. 次に「前日に1クレジット以上消費したリソース」を全件リストアップしてください。サービスタイプ、エンティティタイプ、リソース名、オーナーロール（object_owner_role の owner_role）、前日クレジット、一昨日クレジット、増減量を、前日クレジットの多い順に並べてください。前日と一昨日の比較には period over period（1日前）を使ってください。

3. 上記のうち entity_type が WAREHOUSE のリソースが1つ以上ある場合は、Snowflake Warehouse Query Attribution (JST) Topic で、該当Warehouseについて前日のクレジットをユーザー別・ロール別に多い順で上位10件出してください。あわせて前日の重いクエリ上位5件（クエリID、ユーザー、ロール、クエリテキスト冒頭、帰属クレジット）も出してください。

最後に、増加が目立つリソースについて「確認すべきオブジェクトとそのオーナーロール」「Warehouseの場合は特にクエリを回していたユーザー」を簡潔に箇条書きでまとめてください。数値の計算は必ずクエリ結果に含めて行い、自分で暗算しないでください。

:::

2. Test-run the Routine

Let's try running the Routine created by the Agent.

As shown below, the message was delivered to the specified Slack channel. In line with the conditions I specified, it showed credit consumption for yesterday and the day before, along with a list of resources whose usage exceeded the 1-credit threshold I had set.

Conclusion

Using only Omni's Agent feature, I went end to end — from requirements interviewing, to implementing the Semantic Layer (Views/Topics), to creating and test-running the Routine.

For quickly checking out what's possible, I highly recommend it — it even codes while checking the actual data in Snowflake!

When building something at a production-ready level, I'd recommend clarifying what you want to achieve and making your initial prompt as concrete as possible.