Test Snowflake SQL Locally with Your AI Agent

Introduction

Developing SQL against a real Snowflake warehouse can be slow and expensive. Every time you test a query, a warehouse starts and you pay for the compute. Building a report often takes many test runs, so the time and cost quickly add up. Worse, SQL errors can be hard to spot. A query may return a believable number that reaches a dashboard before anyone checks it against the raw data.

We gave Claude Code access to the LocalStack MCP server and its Snowflake client tool. We then gave it CSV files with a SaaS company’s billing data and asked it to build a quarterly “Revenue by Plan” report using a local Snowflake emulator. It also had to verify every number.

Claude Code started the emulator, loaded the CSV files, and wrote the report. It found two problems in its first query, fixed them, and checked the results against the raw data. All this happened locally, without using a real Snowflake warehouse. Here is what happened and how you can try it yourself.

How the Snowflake client tool works

LocalStack for Snowflake is a local emulator that supports the Snowflake protocol. After starting the emulator, you can point a client to snowflake.localhost.localstack.cloud:4566 and run DDL, DML, and queries as you would with a real Snowflake account.

The LocalStack MCP server connects your AI agent to the emulator. This blog uses two tools from the LocalStack MCP server:

• localstack-management starts, stops, and checks the LocalStack container. The agent uses it to start the Snowflake emulator.
• localstack-snowflake-client runs SQL. check-connection checks whether the emulator is available. execute runs a query string or a .sql file. You can also provide a database, schema, warehouse, and role for each call.

Behind the scenes, the client tool uses the Snowflake CLI (snow) and manages the LocalStack connection profile. The Snowflake CLI is the only Snowflake-side tool you need to install. When the agent runs a query, it receives the results as text. It can read those results and decide what to do next, just as you would when working in a SQL console.

Prerequisites

• Docker, running.
• A valid LOCALSTACK_AUTH_TOKEN, available with a free LocalStack account. LocalStack for Snowflake is a licensed emulator, so check that your plan includes it.
• The Snowflake CLI (snow) on your PATH.
• Node.js, to run the MCP server through npx.
• Claude Code, or any other MCP client.

Step 1: Set up the MCP server

The MCP server ships with a wizard that writes the client configuration for you:

npx -y @localstack/localstack-mcp-server init

The wizard checks if Docker is available and reads LOCALSTACK_AUTH_TOKEN from your environment. If the token is missing, it asks you to enter it. The wizard then finds your installed MCP clients and configures the ones you choose.

The LocalStack tools will be available the next time you start your agent. You do not need to start the emulator yourself. The agent will do that in Step 3.

Step 2: Get the sample data

The dataset contains three CSV files similar to those exported from a billing system:

• plans.csv
• customers.csv
• payments.csv

Download them into a data folder:

mkdir -p data && cd data
curl -sO https://blog.localstack.cloud/data/test-snowflake-sql-locally-with-your-ai-agent/plans.csv
curl -sO https://blog.localstack.cloud/data/test-snowflake-sql-locally-with-your-ai-agent/customers.csv
curl -sO https://blog.localstack.cloud/data/test-snowflake-sql-locally-with-your-ai-agent/payments.csv
cd ..

plans.csv contains four subscription tiers:

plan_id,plan_name,monthly_price
1,Starter,29
2,Pro,99
3,Business,299
4,Enterprise,999

customers.csv contains 39 customers. Each customer has a plan_id, a status (active or churned), a signup_date, and a churn_date. The churn_date is blank for active customers.

payments.csv contains 495 rows. Each row represents a paid monthly invoice and includes a payment_date and an amount. Some customers made payments before leaving partway through the quarter. This detail becomes important later.

Step 3: Hand the agent the task

Open Claude Code in the folder that contains your data directory. Select your model (e.g., claude-opus-5) and use the following prompt. The key requirement is verification: the agent must check the report before calling it complete.

You have three CSV exports of our billing data in ./data: plans.csv,
customers.csv, and payments.csv. Finance wants a quarterly "Revenue by Plan"
report for Q2 2026. Build it on our local Snowflake emulator and prove it's
correct before you call it done.

1. Start the LocalStack Snowflake emulator with the LocalStack management tool,
   then confirm you can reach it.
2. Create a database, schema, and one table per CSV, and load the CSVs through a
   stage: PUT the files to a stage, then COPY INTO the tables.
3. Build the report as a single query, one row per plan tier, with active MRR,
   active customer count, and total revenue collected during Q2 2026
   (2026-04-01 to 2026-06-30). Write the most direct query that answers this.
4. Before you trust it, re-derive every figure a second way straight from the raw
   tables and reconcile it against the report. Don't call it correct until every
   number matches. If one doesn't, find the cause, fix the query, and re-check.
5. Save the SQL and a short REPORT.md.

Run all SQL through the LocalStack Snowflake client tool. The emulator is
Postgres-backed, so send statements in small batches, not one big script.

Step 4 is important. An agent that only writes a query may return incorrect results without noticing. Asking it to compare the results with the raw data helps it find and fix its own mistakes.

Step 4: The agent starts LocalStack and loads the data

The agent first started the emulator and checked the connection:

● localstack-management(action: "start", service: "snowflake")
  ⎿ 🚀 Snowflake emulator started successfully!
     Health check: {"success": true}

● localstack-snowflake-client(action: "check-connection")
  ⎿ Connection name  localstack
     Status           OK
     Host             snowflake.localhost.localstack.cloud

Next, it created a database, a schema, and one table for each CSV file. It uploaded the files to an internal stage and loaded them with COPY INTO:

● localstack-snowflake-client(action: "execute",
    query: "PUT file://.../data/payments.csv @BILLING_STAGE OVERWRITE=TRUE")
  ⎿ payments.csv → payments.csv.gz | UPLOADED

● localstack-snowflake-client(action: "execute",
    query: "COPY INTO PAYMENTS FROM @BILLING_STAGE/payments.csv.gz
            FILE_FORMAT=(TYPE=CSV SKIP_HEADER=1 EMPTY_FIELD_AS_NULL=TRUE)")
  ⎿ payments.csv.gz | LOADED | rows_parsed 495 | rows_loaded 495 | errors_seen 0

The three tables contained 4, 39, and 495 rows. The agent checked that these row counts and the total payment amount matched the original CSV files.

The agent also handled two details. The emulator compresses uploaded files, so COPY had to use the .gz filename. It also used EMPTY_FIELD_AS_NULL to load blank churn_date values as NULL.

Step 5: The first report, and the two bugs

The first query joined the three tables and grouped the results by plan. It ran without errors and returned numbers that looked reasonable:

● localstack-snowflake-client(action: "execute", file_path: "sql/report.sql")
  ⎿ PLAN_NAME   | ACTIVE_MRR | COLLECTED_Q2
     Starter     | 3,654      | 812
     Pro         | 9,504      | 2,277
     Business    | 21,229     | 5,382
     Enterprise  | 37,962     | 8,991

However, checking the results against the raw tables revealed errors in both money columns.

Active MRR was about 12 times too high. MRR should count each customer once. However, joining the PAYMENTS table created one row per payment. As a result, the query counted each customer’s monthly price once for every invoice they had paid.

Collected revenue had a different problem. The query filtered for customers with status = 'active'. This filter is correct for MRR, but not for collected revenue. It excluded seven customers who paid during Q2 and later churned, leaving out $3,279 in revenue.

Neither problem caused an error, and the results still looked believable. That made both bugs easy to miss.

Step 6: The fix, and the proof

The fix was to calculate MRR and collected revenue separately. The agent used one CTE for each calculation, then joined both results to the plan list. It calculated MRR from CUSTOMERS only, which prevented duplicate customer counts. It calculated revenue from PAYMENTS without a status filter, so payments from churned customers were included:

WITH active_by_plan AS (
    SELECT plan_id, COUNT(*) AS active_customers
    FROM customers
    WHERE status = 'active'
    GROUP BY plan_id
),
q2_by_plan AS (
    SELECT c.plan_id, SUM(pay.amount) AS q2_collected
    FROM payments pay
    JOIN customers c ON c.customer_id = pay.customer_id
    WHERE pay.payment_date >= DATE '2026-04-01'
      AND pay.payment_date <  DATE '2026-07-01'
    GROUP BY c.plan_id
)
SELECT
    pl.plan_name,
    COALESCE(a.active_customers, 0) * pl.monthly_price AS active_mrr,
    COALESCE(a.active_customers, 0)                    AS active_customers,
    COALESCE(q.q2_collected, 0)                        AS q2_collected_revenue
FROM plans pl
LEFT JOIN active_by_plan a ON a.plan_id = pl.plan_id
LEFT JOIN q2_by_plan     q ON q.plan_id = pl.plan_id
ORDER BY pl.plan_id;

The corrected report:

The agent then verified the results. It calculated every value again using correlated subqueries instead of joins and arithmetic instead of SUM. It compared the two sets of results one value at a time.

It also checked the totals directly against the raw PAYMENTS table without any joins. This check would reveal any missing or duplicate rows. All checks passed:

● localstack-snowflake-client(action: "execute", file_path: "sql/reconciliation.sql")
  ⎿ cell-by-cell reconciliation: 12/12 PASS
     grand totals vs raw tables (no joins): MRR 5,873 · customers 27 · revenue 20,741 (match)

What it cost

The full session took about nine minutes with claude-opus-5 and cost $2.80 in model usage. This included creating the report, verifying the results, and saving the SQL. The agent made 42 tool calls, including 27 calls to the Snowflake client.

Because everything ran locally, there was no Snowflake compute cost. Running the same queries on a real account would have used billable compute. The missing-revenue bug could also have gone unnoticed until someone questioned the numbers later.

Conclusion

The Snowflake client tool in the LocalStack MCP server lets an agent complete common data tasks. It can start the emulator, create schemas, load CSV files, run queries, and read the results. Because everything runs locally, the agent can test and verify queries many times without Snowflake compute costs.

The two bugs in this example were common: a join duplicated values, and a filter removed valid rows. Both produced believable but incorrect results. Verifying the report against the raw data exposed them before the report was used. If you build reports or data transformations for Snowflake, local verification can help you find these problems early.

Learn More

• LocalStack for Snowflake: getting started, configuration, and the local emulator.
• Snowflake feature coverage: what the emulator supports.
• LocalStack MCP server: the server, its tools, and setup.
• Model Context Protocol: how agents talk to tools.
• LocalStack Slack Community: join for questions and discussion.