Agent CI/CD with dbt: per-PR builds, versioned deploys, and an evaluation gate: code¶

{#-
  Route every node to one schema per environment.
  CI passes --vars '{"deploy_schema": "PR_42"}'; main uses the PROD default.
  The value is validated so a malformed var cannot become an arbitrary identifier.
-#}
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set schema = var('deploy_schema', target.schema) | string | upper -%}
    {%- if not modules.re.match('^(PROD|PR_[0-9]{1,6})$', schema) -%}
        {{ exceptions.raise_compiler_error("deploy_schema must be PROD or PR_<number>, got: " ~ schema) }}
    {%- endif -%}
    {{ schema }}
{%- endmacro %}

{#-
  Minimal semantic view materialization. The model body is the semantic view
  DDL body (TABLES ... METRICS ... COMMENT). CREATE OR ALTER keeps grants on the
  semantic view across runs, which CREATE OR REPLACE would drop.

  The Snowflake-Labs dbt_semantic_view package provides a fuller version of
  this materialization; it needs an external access integration so that
  dbt deps can download it inside Snowflake. This recipe avoids that.
-#}
{% materialization semantic_view, adapter='snowflake' %}
    {%- set target_relation = api.Relation.create(
        database=this.database, schema=this.schema, identifier=this.identifier, type='view') -%}

    {{ run_hooks(pre_hooks) }}

    {% call statement('main') -%}
        CREATE OR ALTER SEMANTIC VIEW {{ target_relation }}
        {{ sql }}
    {%- endcall %}

    {{ run_hooks(post_hooks) }}

    {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

select
    customer_id,
    customer_name,
    email,
    city,
    state,
    country,
    customer_created_date
from {{ ref('stg_customers') }}

select
    product_id,
    product_name,
    category,
    subcategory,
    unit_price
from {{ ref('stg_products') }}

-- One row per order line: the grain for product and category revenue.
select
    order_item_id,
    order_id,
    product_id,
    quantity,
    unit_price,
    discount_amount,
    line_total
from {{ ref('stg_order_items') }}

-- One row per order. Totals are pre-aggregated from line items so order-level
-- metrics never fan out when joined to lines.
with line_totals as (
    select
        order_id,
        sum(quantity) as total_quantity,
        sum(line_total) as order_total,
        sum(discount_amount) as total_discount
    from {{ ref('stg_order_items') }}
    group by order_id
)

select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_status,
    o.shipping_address_state,
    coalesce(l.total_quantity, 0) as total_quantity,
    coalesce(l.order_total, 0) as order_total,
    coalesce(l.total_discount, 0) as total_discount
from {{ ref('stg_orders') }} o
left join line_totals l on o.order_id = l.order_id

-- Body of CREATE OR ALTER SEMANTIC VIEW (see macros/semantic_view.sql).
-- ref() resolves to the tables dbt built in this environment's schema and
-- orders this model after them in the DAG.
TABLES (
    customers AS {{ ref('dim_customers') }} PRIMARY KEY (customer_id)
        COMMENT = 'One row per customer',
    products AS {{ ref('dim_products') }} PRIMARY KEY (product_id)
        COMMENT = 'One row per product',
    orders AS {{ ref('fct_orders') }} PRIMARY KEY (order_id)
        COMMENT = 'One row per order; totals already rolled up from lines',
    order_lines AS {{ ref('fct_order_lines') }} PRIMARY KEY (order_item_id)
        COMMENT = 'One row per order line; use for product and category revenue'
)
RELATIONSHIPS (
    orders_to_customers AS orders (customer_id) REFERENCES customers (customer_id),
    lines_to_orders AS order_lines (order_id) REFERENCES orders (order_id),
    lines_to_products AS order_lines (product_id) REFERENCES products (product_id)
)
FACTS (
    orders.order_total AS order_total COMMENT = 'Order amount after discounts',
    order_lines.line_total AS line_total COMMENT = 'Line amount after discounts',
    order_lines.quantity AS quantity COMMENT = 'Units on the line'
)
DIMENSIONS (
    customers.customer_name AS customer_name
        WITH SYNONYMS = ('customer', 'buyer') COMMENT = 'Customer full name',
    customers.customer_state AS state COMMENT = 'Two-letter US state of the customer',
    customers.customer_city AS city COMMENT = 'Customer city',
    products.product_name AS product_name
        WITH SYNONYMS = ('product', 'item') COMMENT = 'Product name',
    products.category AS category
        WITH SYNONYMS = ('product category', 'department') COMMENT = 'Product category',
    orders.order_date AS order_date COMMENT = 'Date the order was placed',
    orders.order_month AS DATE_TRUNC('month', order_date) COMMENT = 'First day of the order month',
    orders.order_status AS order_status
        COMMENT = 'Order status: completed, shipped, processing, or cancelled'
)
METRICS (
    orders.total_orders AS COUNT(orders.order_id)
        WITH SYNONYMS = ('order count', 'number of orders') COMMENT = 'Number of orders',
    orders.total_revenue AS SUM(orders.order_total)
        WITH SYNONYMS = ('revenue', 'sales') COMMENT = 'Sum of order totals after discounts',
    orders.average_order_value AS AVG(orders.order_total)
        WITH SYNONYMS = ('AOV') COMMENT = 'Average order total after discounts',
    order_lines.product_revenue AS SUM(order_lines.line_total)
        COMMENT = 'Revenue at line grain; use when grouping by product or category',
    order_lines.units_sold AS SUM(order_lines.quantity)
        WITH SYNONYMS = ('units', 'items sold') COMMENT = 'Units sold',
    customers.customer_count AS COUNT(customers.customer_id)
        COMMENT = 'Number of customers'
)
COMMENT = 'Retail orders, customers, and products for the retail assistant agent'

select
    customer_id,
    first_name || ' ' || last_name as customer_name,
    email,
    city,
    state,
    country,
    created_at::date as customer_created_date
from {{ ref('raw_customers') }}

select
    order_item_id,
    order_id,
    product_id,
    quantity,
    unit_price::number(10, 2) as unit_price,
    discount_amount::number(10, 2) as discount_amount,
    (quantity * unit_price::number(10, 2)) - discount_amount::number(10, 2) as line_total
from {{ ref('raw_order_items') }}

select
    order_id,
    customer_id,
    order_date::date as order_date,
    status as order_status,
    shipping_address_city,
    shipping_address_state
from {{ ref('raw_orders') }}

select
    product_id,
    product_name,
    category,
    subcategory,
    unit_price::number(10, 2) as unit_price
from {{ ref('raw_products') }}

-- 01_setup.sql
-- Creates the roles, warehouse, database, schemas, and stages the pipeline uses.
-- Run once as ACCOUNTADMIN (or a role with CREATE ROLE, CREATE DATABASE,
-- CREATE WAREHOUSE, and MANAGE GRANTS). Everything after this runs as the
-- COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER role.

SET cookbook_db       = 'COOKBOOK_AGENT_CI_CD_WITH_DBT';
SET cookbook_wh       = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_WH';
SET deployer_role     = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER';  -- CI/CD service role
SET consumer_role     = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_USER';      -- people who chat with the prod agent
SET ops_schema        = 'COOKBOOK_AGENT_CI_CD_WITH_DBT.OPS';
SET prod_schema       = 'COOKBOOK_AGENT_CI_CD_WITH_DBT.PROD';
SET me                = '"' || CURRENT_USER() || '"';

USE ROLE ACCOUNTADMIN;

-- Roles ----------------------------------------------------------------------
CREATE ROLE IF NOT EXISTS IDENTIFIER($deployer_role)
  COMMENT = 'Cookbook agent-ci-cd-with-dbt: CI/CD deployer';
CREATE ROLE IF NOT EXISTS IDENTIFIER($consumer_role)
  COMMENT = 'Cookbook agent-ci-cd-with-dbt: uses the production agent';
GRANT ROLE IDENTIFIER($deployer_role) TO ROLE SYSADMIN;
GRANT ROLE IDENTIFIER($consumer_role) TO ROLE SYSADMIN;
GRANT ROLE IDENTIFIER($deployer_role) TO USER IDENTIFIER($me);
GRANT ROLE IDENTIFIER($consumer_role) TO USER IDENTIFIER($me);

-- Warehouse ------------------------------------------------------------------
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($cookbook_wh)
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
GRANT USAGE ON WAREHOUSE IDENTIFIER($cookbook_wh) TO ROLE IDENTIFIER($deployer_role);
GRANT USAGE ON WAREHOUSE IDENTIFIER($cookbook_wh) TO ROLE IDENTIFIER($consumer_role);

-- Database and schemas -------------------------------------------------------
-- OPS holds pipeline plumbing (dbt project objects, stages, eval dataset, gate
-- procedures). PROD holds the production tables, semantic view, and agent.
-- Each pull request gets its own PR_<number> schema, created and dropped by CI.
CREATE DATABASE IF NOT EXISTS IDENTIFIER($cookbook_db);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($ops_schema);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($prod_schema);
GRANT OWNERSHIP ON SCHEMA IDENTIFIER($ops_schema) TO ROLE IDENTIFIER($deployer_role) COPY CURRENT GRANTS;
GRANT OWNERSHIP ON SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($deployer_role) COPY CURRENT GRANTS;
GRANT OWNERSHIP ON DATABASE IDENTIFIER($cookbook_db) TO ROLE IDENTIFIER($deployer_role) COPY CURRENT GRANTS;

-- Cortex and evaluation privileges for the deployer --------------------------
-- Evaluations need CORTEX_USER and EXECUTE TASK (runs are managed by tasks).
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE IDENTIFIER($deployer_role);
GRANT EXECUTE TASK ON ACCOUNT TO ROLE IDENTIFIER($deployer_role);

-- Consumer access to production only -----------------------------------------
-- CORTEX_AGENT_USER is enough to chat with an agent; CORTEX_USER also works.
-- Future grants apply to the semantic view and agent the pipeline creates later.
-- Because the pipeline adds versions to one agent object (it never replaces it),
-- these grants survive every deployment.
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_AGENT_USER TO ROLE IDENTIFIER($consumer_role);
GRANT USAGE ON DATABASE IDENTIFIER($cookbook_db) TO ROLE IDENTIFIER($consumer_role);
GRANT USAGE ON SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($consumer_role);
GRANT SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($consumer_role);
GRANT USAGE ON FUTURE AGENTS IN SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($consumer_role);
-- Cortex Analyst runs the SQL it generates against the semantic view's base
-- tables with the caller's role, so the consumer also needs SELECT on them.
-- dbt recreates the mart tables on every build; the future grant applies to
-- each new table, and the ALL grant covers tables that exist when 01 is rerun.
GRANT SELECT ON FUTURE TABLES IN SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($consumer_role);
GRANT SELECT ON ALL TABLES IN SCHEMA IDENTIFIER($prod_schema) TO ROLE IDENTIFIER($consumer_role);

-- Pipeline plumbing, owned by the deployer ------------------------------------
USE ROLE IDENTIFIER($deployer_role);
USE WAREHOUSE IDENTIFIER($cookbook_wh);
USE SCHEMA IDENTIFIER($ops_schema);

-- Evaluation configs must be read as single-column text, so the stage uses a
-- CSV format with FIELD_DELIMITER = NONE.
CREATE FILE FORMAT IF NOT EXISTS YAML_FILE_FORMAT
  TYPE = 'CSV'
  FIELD_DELIMITER = NONE
  RECORD_DELIMITER = '\n'
  SKIP_HEADER = 0
  FIELD_OPTIONALLY_ENCLOSED_BY = NONE
  ESCAPE_UNENCLOSED_FIELD = NONE;

-- CREATE AGENT ... FROM @stage and ADD VERSION FROM @stage read agent_spec.yaml
-- server-side; with the default client-side encryption they fail with
-- "No spec file present for the agent". Use server-side encryption.
CREATE STAGE IF NOT EXISTS AGENT_SPECS
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
  DIRECTORY = (ENABLE = TRUE)
  COMMENT = 'Rendered agent_spec.yaml files, one directory per deployment';
CREATE STAGE IF NOT EXISTS EVAL_CONFIGS
  FILE_FORMAT = YAML_FILE_FORMAT
  COMMENT = 'Rendered evaluation configs, one directory per deployment';

SHOW SCHEMAS IN DATABASE IDENTIFIER($cookbook_db);

-- 02_pipeline_procedures.sql
-- Three caller's-rights procedures that the CI script calls, plus a results log.
--   DEPLOY_AGENT_VERSION  import a spec directory from the AGENT_SPECS stage as a new named version
--   RUN_EVAL_GATE         run an evaluation, wait for it, and raise an error if scores are below threshold
--   PROMOTE_AGENT_VERSION move the PRODUCTION alias and the default version to a gated version
--
-- These procedures build DDL from their inputs (agent and stage names cannot be
-- bind variables), so every input is checked against a strict pattern first.
-- They run with caller's rights: the CI role needs the privileges, not the
-- procedure owner. Run as the deployer role after 01_setup.sql.

SET cookbook_wh   = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_WH';
SET deployer_role = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER';
SET ops_schema    = 'COOKBOOK_AGENT_CI_CD_WITH_DBT.OPS';

USE ROLE IDENTIFIER($deployer_role);
USE WAREHOUSE IDENTIFIER($cookbook_wh);
USE SCHEMA IDENTIFIER($ops_schema);

-- One row per gate decision, pass or fail. Use it to learn the normal score
-- range for your dataset before you tighten thresholds.
CREATE TABLE IF NOT EXISTS EVAL_GATE_RESULTS (
    evaluated_at      TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
    run_name          VARCHAR,
    target_schema     VARCHAR,
    run_status        VARCHAR,
    scores            VARIANT,
    thresholds        VARIANT,
    records_evaluated INTEGER,
    records_errored   INTEGER,
    passed            BOOLEAN
);

CREATE OR REPLACE PROCEDURE DEPLOY_AGENT_VERSION(
    TARGET_SCHEMA VARCHAR, SPEC_DIR VARCHAR, VERSION_COMMENT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Create the agent from a staged spec, or add a named version from it. Returns the new version name.'
EXECUTE AS CALLER
AS
$$
DECLARE
    spec_prefix VARCHAR;
    agent_fqn   VARCHAR;
    agent_count INTEGER;
    live_count  INTEGER;
    new_version VARCHAR;
    bad_input   EXCEPTION (-20001, 'DEPLOY_AGENT_VERSION: invalid TARGET_SCHEMA, SPEC_DIR, or VERSION_COMMENT');
BEGIN
    spec_prefix := '@' || CURRENT_DATABASE() || '.OPS.AGENT_SPECS/';
    IF (NOT REGEXP_LIKE(TARGET_SCHEMA, '^(PROD|PR_[0-9]{1,6})$')
        OR NOT STARTSWITH(SPEC_DIR, spec_prefix)
        OR NOT REGEXP_LIKE(SUBSTR(SPEC_DIR, LENGTH(spec_prefix) + 1), '^[A-Za-z0-9_-]+(/[A-Za-z0-9_-]+)*/$')
        OR NOT REGEXP_LIKE(VERSION_COMMENT, '^[A-Za-z0-9 _.-]{1,200}$')) THEN
        RAISE bad_input;
    END IF;
    agent_fqn := CURRENT_DATABASE() || '.' || TARGET_SCHEMA || '.RETAIL_ASSISTANT';

    EXECUTE IMMEDIATE 'SHOW AGENTS LIKE ''RETAIL_ASSISTANT'' IN SCHEMA ' || CURRENT_DATABASE() || '.' || TARGET_SCHEMA;
    SELECT COUNT(*) INTO :agent_count FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    IF (agent_count = 0) THEN
        -- First deployment: creates VERSION$1 plus a LIVE version from the spec.
        EXECUTE IMMEDIATE 'CREATE AGENT ' || agent_fqn
            || ' COMMENT = ''Retail assistant. Versions are imported by CI; do not edit in place.'''
            || ' FROM ' || SPEC_DIR;
    ELSE
        -- ADD VERSION FROM a stage fails while a LIVE version exists. Commit it
        -- first so any Snowsight edits are preserved as a named version. That
        -- version is never promoted: only versions imported from Git are.
        EXECUTE IMMEDIATE 'SHOW VERSIONS IN AGENT ' || agent_fqn;
        SELECT COUNT(*) INTO :live_count FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
            WHERE "spec_file_path" ILIKE '%/versions/live/';
        IF (live_count > 0) THEN
            EXECUTE IMMEDIATE 'ALTER AGENT ' || agent_fqn
                || ' COMMIT COMMENT = ''Live version committed by CI before import; not promoted''';
        END IF;
        EXECUTE IMMEDIATE 'ALTER AGENT ' || agent_fqn || ' ADD VERSION FROM ' || SPEC_DIR
            || ' COMMENT = ''' || VERSION_COMMENT || '''';
    END IF;

    EXECUTE IMMEDIATE 'SHOW VERSIONS IN AGENT ' || agent_fqn;
    SELECT 'VERSION$' || MAX(TRY_TO_NUMBER(SPLIT_PART("name", '$', 2)))
        INTO :new_version
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        WHERE "name" IS NOT NULL;
    RETURN new_version;
END;
$$;

CREATE OR REPLACE PROCEDURE RUN_EVAL_GATE(
    RUN_NAME VARCHAR, CONFIG_PATH VARCHAR, TARGET_SCHEMA VARCHAR,
    MIN_ANSWER_CORRECTNESS FLOAT, MIN_LOGICAL_CONSISTENCY FLOAT, TIMEOUT_SECONDS INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = 'Start an agent evaluation, wait for it, log scores, and raise if any score is below its threshold.'
EXECUTE AS CALLER
AS
$$
DECLARE
    config_prefix VARCHAR;
    run_status    VARCHAR DEFAULT 'CREATED';
    waited        INTEGER DEFAULT 0;
    partial_since INTEGER DEFAULT -1;
    scores        VARIANT;
    n_records     INTEGER;
    n_errors      INTEGER;
    ac            FLOAT;
    lc            FLOAT;
    passed        BOOLEAN;
    bad_input     EXCEPTION (-20001, 'RUN_EVAL_GATE: invalid RUN_NAME, CONFIG_PATH, or TARGET_SCHEMA');
    timed_out     EXCEPTION (-20002, 'RUN_EVAL_GATE: evaluation did not finish before TIMEOUT_SECONDS');
    gate_failed   EXCEPTION (-20003, 'RUN_EVAL_GATE: scores below threshold; see OPS.EVAL_GATE_RESULTS');
BEGIN
    config_prefix := '@' || CURRENT_DATABASE() || '.OPS.EVAL_CONFIGS/';
    IF (NOT REGEXP_LIKE(RUN_NAME, '^[A-Za-z0-9_-]{1,128}$')
        OR NOT REGEXP_LIKE(TARGET_SCHEMA, '^(PROD|PR_[0-9]{1,6})$')
        OR NOT STARTSWITH(CONFIG_PATH, config_prefix)
        OR NOT REGEXP_LIKE(SUBSTR(CONFIG_PATH, LENGTH(config_prefix) + 1), '^([A-Za-z0-9_-]+/)+[A-Za-z0-9_-]+[.]yaml$')) THEN
        RAISE bad_input;
    END IF;

    -- Evaluations run in the session's current database and schema (OPS).
    CALL EXECUTE_AI_EVALUATION('START', OBJECT_CONSTRUCT('run_name', :RUN_NAME), :CONFIG_PATH);

    LOOP
        CALL EXECUTE_AI_EVALUATION('STATUS', OBJECT_CONSTRUCT('run_name', :RUN_NAME), :CONFIG_PATH);
        SELECT "STATUS" INTO :run_status FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
        IF (run_status IN ('COMPLETED', 'CANCELLED')) THEN
            BREAK;
        END IF;
        -- PARTIALLY_COMPLETED is documented as final, but a run can report it
        -- for a short time and then move to COMPLETED. Keep polling for up to
        -- 120 seconds before treating it as final; the gate still fails closed
        -- on anything but COMPLETED.
        IF (run_status = 'PARTIALLY_COMPLETED') THEN
            IF (partial_since < 0) THEN
                partial_since := waited;
            END IF;
            IF (waited - partial_since >= 120) THEN
                BREAK;
            END IF;
        ELSE
            partial_since := -1;
        END IF;
        IF (waited >= TIMEOUT_SECONDS) THEN
            RAISE timed_out;
        END IF;
        SELECT SYSTEM$WAIT(20);
        waited := waited + 20;
    END LOOP;

    -- Average each metric over all records. A record without a score (for
    -- example, an agent error) counts as 0 so failures cannot raise the average.
    SELECT OBJECT_AGG(metric_name, avg_score::VARIANT), MAX(n_records), MAX(n_errors)
      INTO :scores, :n_records, :n_errors
      FROM (
        SELECT METRIC_NAME AS metric_name,
               ROUND(AVG(COALESCE(EVAL_AGG_SCORE, 0)), 3) AS avg_score,
               COUNT(DISTINCT RECORD_ID) AS n_records,
               COUNT(DISTINCT IFF(ERROR IS NOT NULL AND ERROR <> '', RECORD_ID, NULL)) AS n_errors
          FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_EVALUATION_DATA(
                 CURRENT_DATABASE(), :TARGET_SCHEMA, 'RETAIL_ASSISTANT', 'CORTEX AGENT', :RUN_NAME))
         WHERE METRIC_NAME IS NOT NULL
         GROUP BY METRIC_NAME);

    ac := GET(scores, 'answer_correctness')::FLOAT;
    lc := GET(scores, 'logical_consistency')::FLOAT;
    -- Fail closed: a missing metric, a partial run, or a NULL comparison is a failure.
    passed := COALESCE(run_status = 'COMPLETED'
              AND ac IS NOT NULL AND ac >= MIN_ANSWER_CORRECTNESS
              AND lc IS NOT NULL AND lc >= MIN_LOGICAL_CONSISTENCY, FALSE);

    INSERT INTO EVAL_GATE_RESULTS
        (run_name, target_schema, run_status, scores, thresholds, records_evaluated, records_errored, passed)
    SELECT :RUN_NAME, :TARGET_SCHEMA, :run_status, :scores,
           OBJECT_CONSTRUCT('answer_correctness', :MIN_ANSWER_CORRECTNESS,
                            'logical_consistency', :MIN_LOGICAL_CONSISTENCY),
           :n_records, :n_errors, :passed;

    IF (NOT passed) THEN
        RAISE gate_failed;
    END IF;
    RETURN OBJECT_CONSTRUCT('run_name', RUN_NAME, 'status', run_status, 'scores', scores, 'passed', passed);
END;
$$;

CREATE OR REPLACE PROCEDURE PROMOTE_AGENT_VERSION(TARGET_SCHEMA VARCHAR, VERSION_NAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'Point the PRODUCTION alias and the default version at a gated version.'
EXECUTE AS CALLER
AS
$$
DECLARE
    agent_fqn VARCHAR;
    bad_input EXCEPTION (-20001, 'PROMOTE_AGENT_VERSION: invalid TARGET_SCHEMA or VERSION_NAME');
BEGIN
    IF (NOT REGEXP_LIKE(TARGET_SCHEMA, '^(PROD|PR_[0-9]{1,6})$')
        OR NOT REGEXP_LIKE(VERSION_NAME, '^VERSION[$][0-9]{1,6}$')) THEN
        RAISE bad_input;
    END IF;
    agent_fqn := CURRENT_DATABASE() || '.' || TARGET_SCHEMA || '.RETAIL_ASSISTANT';
    -- Reassigning an alias moves it off the previous version in one statement.
    EXECUTE IMMEDIATE 'ALTER AGENT ' || agent_fqn || ' MODIFY VERSION ' || VERSION_NAME || ' SET ALIAS = PRODUCTION';
    -- Pin the default so the next ADD VERSION does not become the default before it is gated.
    EXECUTE IMMEDIATE 'ALTER AGENT ' || agent_fqn || ' SET DEFAULT_VERSION = ''' || VERSION_NAME || '''';
    RETURN agent_fqn || ' PRODUCTION -> ' || VERSION_NAME;
END;
$$;

SHOW PROCEDURES IN SCHEMA IDENTIFIER($ops_schema);

-- 03_eval_dataset.sql
-- Creates the evaluation input table and registers it as an evaluation dataset.
-- Every PR and every production deploy is scored against this same dataset, so
-- scores are comparable across runs. Ground truth states literal values from
-- the seed data and scopes each question to absolute dates so answers do not drift.
-- Run as the deployer role after 01_setup.sql.

SET cookbook_db   = 'COOKBOOK_AGENT_CI_CD_WITH_DBT';
SET cookbook_wh   = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_WH';
SET deployer_role = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER';
SET ops_schema    = 'COOKBOOK_AGENT_CI_CD_WITH_DBT.OPS';

USE ROLE IDENTIFIER($deployer_role);
USE WAREHOUSE IDENTIFIER($cookbook_wh);
USE SCHEMA IDENTIFIER($ops_schema);

CREATE OR REPLACE TABLE RETAIL_EVAL_QUESTIONS (
    input_query  VARCHAR,
    ground_truth VARIANT
);

INSERT INTO RETAIL_EVAL_QUESTIONS
SELECT column1, PARSE_JSON(column2) FROM VALUES
  ('What was total revenue across all orders?',
   '{"ground_truth_output": "Total revenue across all 50 orders is $7,144.03 after discounts. Rounding to the nearest dollar is acceptable."}'),
  ('How many orders were placed in September 2023, and what was their total revenue?',
   '{"ground_truth_output": "3 orders were placed between September 1 and September 30, 2023, with total revenue of $501.90."}'),
  ('Which product category had the highest revenue?',
   '{"ground_truth_output": "Home & Garden had the highest revenue at $1,756.71. Electronics was second at $1,554.59. The answer must name Home & Garden."}'),
  ('How many orders are in processing status?',
   '{"ground_truth_output": "6 orders have status processing."}'),
  ('What was total revenue for orders placed between January 1 and December 31, 2024?',
   '{"ground_truth_output": "Total revenue for orders placed in calendar year 2024 is $3,522.64 across 24 orders."}'),
  ('What will the weather be in Austin tomorrow?',
   '{"ground_truth_output": "The response should say that weather is outside the retail data the agent can access. It must not invent a forecast."}');

-- Recreate the dataset so this script stays idempotent. The dataset is a
-- snapshot of the table; rerun this script after you change the questions.
DROP DATASET IF EXISTS RETAIL_EVAL_DATASET;

CALL SYSTEM$CREATE_EVALUATION_DATASET(
  'Cortex Agent',
  'COOKBOOK_AGENT_CI_CD_WITH_DBT.OPS.RETAIL_EVAL_QUESTIONS',
  'COOKBOOK_AGENT_CI_CD_WITH_DBT.OPS.RETAIL_EVAL_DATASET',
  OBJECT_CONSTRUCT('query_text', 'INPUT_QUERY', 'expected_tools', 'GROUND_TRUTH')
);

SHOW DATASETS IN SCHEMA IDENTIFIER($ops_schema);

-- 04_ci_service_user.sql
-- The GitHub Actions identity: a SERVICE user that authenticates with GitHub's
-- OIDC token (workload identity federation). No password, key pair, or PAT is
-- created, so there is no secret to store in GitHub or rotate.
--
-- SUBJECT must match the `sub` claim GitHub puts in the token exactly. Both
-- workflows run in the GitHub environment `snowflake-ci`, so one subject covers
-- pull requests and main. Replace example-org/example-repo with your repository.
-- Run as ACCOUNTADMIN or USERADMIN (CREATE USER).

SET ci_user        = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_CI';
SET deployer_role  = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER';
SET cookbook_wh    = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_WH';
SET github_subject = 'repo:example-org/example-repo:environment:snowflake-ci';

USE ROLE ACCOUNTADMIN;

CREATE USER IF NOT EXISTS IDENTIFIER($ci_user)
  TYPE = SERVICE
  WORKLOAD_IDENTITY = (
    TYPE = OIDC
    ISSUER = 'https://token.actions.githubusercontent.com'
    SUBJECT = $github_subject
  )
  DEFAULT_ROLE = $deployer_role
  DEFAULT_WAREHOUSE = $cookbook_wh
  COMMENT = 'Cookbook agent-ci-cd-with-dbt: GitHub Actions deployer (OIDC)';

-- DEFAULT_ROLE selects the role but does not grant it.
GRANT ROLE IDENTIFIER($deployer_role) TO USER IDENTIFIER($ci_user);

SHOW USER WORKLOAD IDENTITY AUTHENTICATION METHODS FOR USER IDENTIFIER($ci_user);

-- 99_teardown.sql
-- Removes everything this recipe created: the database (every schema, table,
-- semantic view, agent, dbt project object, stage, dataset, and procedure),
-- the warehouse, the CI service user, and both roles. Dropping the roles also
-- removes the account-level EXECUTE TASK grant made in 01_setup.sql.
-- No account parameters were changed, so there is nothing to restore.
-- Run as ACCOUNTADMIN.

SET cookbook_db   = 'COOKBOOK_AGENT_CI_CD_WITH_DBT';
SET cookbook_wh   = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_WH';
SET deployer_role = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_DEPLOYER';
SET consumer_role = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_USER';
SET ci_user       = 'COOKBOOK_AGENT_CI_CD_WITH_DBT_CI';

USE ROLE ACCOUNTADMIN;

DROP DATABASE IF EXISTS IDENTIFIER($cookbook_db);
DROP WAREHOUSE IF EXISTS IDENTIFIER($cookbook_wh);
DROP USER IF EXISTS IDENTIFIER($ci_user);
DROP ROLE IF EXISTS IDENTIFIER($consumer_role);
DROP ROLE IF EXISTS IDENTIFIER($deployer_role);

SHOW DATABASES LIKE 'COOKBOOK_AGENT_CI_CD_WITH_DBT%';
SHOW WAREHOUSES LIKE 'COOKBOOK_AGENT_CI_CD_WITH_DBT%';
SHOW ROLES LIKE 'COOKBOOK_AGENT_CI_CD_WITH_DBT%';
SHOW USERS LIKE 'COOKBOOK_AGENT_CI_CD_WITH_DBT%';