How to Safely Connect AI Tools to Your Database Without Hurting Production

AI database demos are getting a lot of attention.

Someone connects Claude Code, Codex, Cursor, or another AI tool to a database through a Model Context Protocol (MCP) server. Then they ask a business question in plain English. The AI reads the schema, writes SQL, runs the query, and explains the answer.

For a demo, this looks great.

For a real company, the setup needs more care.

The issue is not that AI should never query databases. It absolutely should. The issue is where the query runs, what access the AI has, how expensive the query can become, and whether the answer follows your actual business definitions.

A production application database is usually built for Online Transaction Processing (OLTP): orders, logins, payments, inventory updates, customer records, and product workflows. Analytics questions are usually Online Analytical Processing (OLAP): joins, scans, aggregations, cohorts, funnels, attribution, and historical comparisons.

Those two workloads behave differently.

A read-only AI query can still scan large tables, use bad joins, ignore indexes, consume CPU and I/O, and slow down the same database your application depends on. If the AI has broad access, it may also pull sensitive fields, use the wrong definition of a metric, or produce an answer that is hard to reproduce later.

This guide is a practical way to think about safer AI analytics.

It is written for data engineers, analysts, technical marketers, GTM teams, and founders who want AI-assisted analytics without turning their live app database into an uncontrolled query surface.

The safest setup depends on the question

Do not start with “which MCP server should we use?”

Start with the type of question your team wants to ask.

Question type	Better data surface	Why
“What is the status of this order?”	Production API or narrow read-only replica query	Small operational lookup
“Why did revenue drop last week?”	Warehouse or analytics replica	Needs joins, history, definitions, and filters
“Which campaigns brought repeat customers?”	Warehouse	Needs ad data + customer/order data
“Which users are stuck in onboarding?”	Product analytics store or warehouse	Needs event data and behavioral logic
“Can you inspect this table structure?”	Staging/dev database or read replica	Engineering/debugging use case
“Find all customers who match this condition”	Warehouse with scoped fields and audit logs	May touch sensitive data and large tables

A useful rule:

• Use production for the application.
• Use a read replica for controlled operational reads or engineering analysis.
• Use a warehouse for business analytics, cross-source reporting, and repeated self-serve questions.
• Use AI agent instructions and query controls regardless of the database surface.

Pattern 1: Use a read replica when the AI needs application database data

A read replica is a copy of your primary database that receives changes from the primary. The application keeps using the primary. Analytical or AI-generated read queries use the replica.

This is often the simplest first improvement if your current idea is “connect AI to production Postgres or MySQL.”

When this pattern fits

Use a read replica when:

• the data lives in your application database
• the AI needs mostly fresh data
• the queries are read-only
• the questions are mostly operational or engineering-facing
• you are not yet ready to build a full warehouse

Example questions:

• “Show recent failed payments by error code.”
• “Which organizations created more than 100 projects this week?”
• “Check whether this customer has duplicate records.”
• “Find slow-growing workspaces in the last 30 days.”

This is not the best pattern for cross-source analytics, complex marketing attribution, long-term historical reporting, or business-wide metric definitions. For those, use a warehouse.

Recommended architecture

CopyApplication users  ->  Primary Postgres/MySQL database
AI tools / MCP     ->  Read replica with read-only user and query limits

The important part is that the AI never receives the primary database connection string.

PostgreSQL setup checklist

If you are on a managed database provider such as Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, Neon, Supabase, or Railway, create the replica from the provider console. Most managed providers expose this as “create read replica,” “replica,” “branch,” or “read-only replica.”

For self-managed PostgreSQL, replication setup usually involves Write-Ahead Log (WAL) streaming, pg_basebackup, a replication role, and standby configuration. If you are not already comfortable managing PostgreSQL replication, use the managed provider option first.

After the replica exists, create a separate AI user on the replica or primary, depending on how your provider replicates roles.

Copy-- Create a dedicated login role for AI/database assistant access
CREATE ROLE ai_readonly LOGIN PASSWORD 'use-a-secret-manager-generated-password';

-- Allow the role to connect to the database
GRANT CONNECT ON DATABASE app_db TO ai_readonly;

-- Allow schema usage
GRANT USAGE ON SCHEMA public TO ai_readonly;

-- Give read access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;

-- Give read access to future tables created in this schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO ai_readonly;

-- Put a default timeout on statements from this role
ALTER ROLE ai_readonly SET statement_timeout = '30s';

-- Avoid abandoned open transactions
ALTER ROLE ai_readonly SET idle_in_transaction_session_timeout = '60s';

Then connect your MCP server or AI SQL tool using the replica host, not the primary host.

Copypostgresql://ai_readonly:<password>@replica-host:5432/app_db?sslmode=require

Start with a strict timeout such as 10 to 30 seconds. Increase it only if you see legitimate queries getting cancelled.

MySQL setup checklist

For managed MySQL, use your provider’s read replica feature. Amazon RDS, Google Cloud SQL, PlanetScale, Azure Database for MySQL, and other providers have their own replica flow.

For self-managed MySQL, replication usually means enabling binary logs on the source, creating a replication user, copying data to the replica, and configuring the replica to follow the source. If this is your production database and your team has not done this before, prefer managed replicas.

Create a dedicated AI user:

CopyCREATE USER 'ai_readonly'@'%' IDENTIFIED BY 'use-a-secret-manager-generated-password';

GRANT SELECT ON app_db.* TO 'ai_readonly'@'%';

FLUSH PRIVILEGES;

For timeout control, MySQL supports MAX_EXECUTION_TIME for read-only SELECT statements. You can set it at the session level when opening the connection:

CopySET SESSION max_execution_time = 30000;

That value is in milliseconds. In this example, read-only SELECT statements are limited to 30 seconds.

Some MCP/database tools let you run connection initialization SQL. If yours does, set the session timeout at connection startup. If it does not, use a database proxy or query wrapper that sets it before executing AI-generated queries.

Add a basic AI query policy for replica access

A replica reduces production risk, but it does not remove all risk. A bad query can still overload the replica, create lag, or produce poor answers.

Add a short query policy to the AI agent or skill file:

Copy# Database query rules

You are querying a read replica, not the primary database.

Rules:
1. Only generate SELECT queries. Never generate INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, CREATE, GRANT, or REVOKE.
2. Always inspect table names and column names before writing the final query.
3. Avoid SELECT * unless the user is explicitly inspecting a small table sample.
4. Always add a LIMIT for exploratory row-level queries.
5. For event, order, log, message, or activity tables, always include a time filter unless the user asks for a full historical aggregate.
6. Prefer aggregate queries over returning raw records.
7. Before running a query that may scan a large table, first run EXPLAIN or ask for confirmation.
8. If the question needs marketing, CRM, payments, or spreadsheet data, say that the replica alone is not enough and recommend using the warehouse.

This does not guarantee perfect SQL, but it makes the default behavior much safer.

What can still go wrong

A read replica is safer than the primary, but it is not a full analytics system.

Watch for:

• replication lag when long queries overload the replica
• missing historical snapshots if the production table only stores current state
• missing external data such as ads, CRM, spreadsheets, and support tickets
• inconsistent metric definitions if the AI invents business logic from raw tables
• sensitive columns being available because the replica mirrors production schema

Use a replica for controlled database access. Use a warehouse when the goal is business analytics.

Pattern 2: Use a warehouse for business analytics and cross-source questions

A warehouse is the better home for most AI analytics workflows.

The point of a warehouse is not just “store a copy of the data.” It gives you a separate place to combine data, model it, clean it, document it, and query it without touching the production application database.

For example, a GTM team rarely wants only app database data. They want to ask:

• Which Google Ads campaigns brought customers with high repeat purchase rate?
• Which HubSpot pipeline stages are leaking deals?
• Which Shopify products drive revenue but get poor landing page conversion?
• Which Search Console keywords eventually lead to revenue?

Those questions need data from multiple systems. A production database alone cannot answer them.

When this pattern fits

Use a warehouse when:

• non-engineering teams will ask recurring business questions
• you need data from multiple tools
• queries will scan months or years of history
• you need modeled metrics such as revenue, gross margin, conversion rate, customer acquisition cost, or lifetime value
• you want auditability and reproducible answers
• you want AI tools to answer questions without touching production systems

Pick the warehouse based on your stage

Team stage	Good first choice	Notes
Small team, already on Postgres	Separate Postgres warehouse	Simple and familiar, but not ideal for very large event data
Ecommerce/GTM data with moderate scale	BigQuery or Snowflake	Strong for cross-source analytics and ad hoc queries
High-volume event/product analytics	BigQuery, Snowflake, ClickHouse, or Databricks	Choose based on existing stack and query patterns
Small team without data engineers	Managed platform like Daymark, Fivetran + warehouse, or Airbyte Cloud + warehouse	Lower maintenance

Do not over-engineer the first version. The goal is to move analytical workloads away from production and create a reliable data surface for AI.

Tool options for ELT/ETL

ETL means Extract, Transform, Load. ELT means Extract, Load, Transform.

In modern analytics stacks, ELT is common: load raw data into the warehouse first, then transform it with SQL/dbt.

Useful options:

Need	Tool options
Fast managed connectors	Fivetran, Airbyte Cloud, Stitch, Hevo
Open-source connector framework	Meltano, Airbyte open source, Singer taps/targets
Python-first custom pipelines	dlt, Dagster, Airflow with custom Python tasks
Workflow orchestration	Airflow, Dagster, Prefect
SQL transformations	dbt
Warehouse	Snowflake, BigQuery, Redshift, Postgres, ClickHouse, Databricks

For many small teams, the practical order is:

1. Start with a managed connector if the source is common.
2. Use dbt for modeling the loaded data.
3. Add Airflow, Dagster, or Prefect only when pipeline orchestration becomes complex.
4. Build custom extraction only when existing connectors do not fit.

Practical setup: Postgres/MySQL to Snowflake with ELT

This is a common path for teams that want AI analytics without production database risk.

Step 1: Create a warehouse destination

In Snowflake, create separate objects for raw data, modeled data, and AI querying.

CopyCREATE WAREHOUSE AI_ANALYTICS_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

CREATE DATABASE ANALYTICS;

CREATE SCHEMA ANALYTICS.RAW;
CREATE SCHEMA ANALYTICS.MARTS;

Use RAW for synced source data and MARTS for modeled analytics tables.

Step 2: Create a dedicated AI role

CopyCREATE ROLE AI_ANALYST_ROLE;

GRANT USAGE ON WAREHOUSE AI_ANALYTICS_WH TO ROLE AI_ANALYST_ROLE;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE AI_ANALYST_ROLE;
GRANT USAGE ON SCHEMA ANALYTICS.MARTS TO ROLE AI_ANALYST_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.MARTS TO ROLE AI_ANALYST_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.MARTS TO ROLE AI_ANALYST_ROLE;

Start AI access from MARTS, not raw tables. You can later allow selected raw tables if analysts need deeper exploration.

Step 3: Add a cost guardrail

Create a Snowflake resource monitor for the AI warehouse. This protects you from accidental expensive exploration.

Example policy:

• notify at 50 percent usage
• notify again at 75 percent usage
• suspend at 100 percent usage

This is important because AI-generated queries can be more exploratory and repetitive than human-written dashboard queries.

Step 4: Choose a connector

For fastest setup, use a managed connector:

• Fivetran: Postgres/MySQL to Snowflake
• Airbyte Cloud: Postgres/MySQL to Snowflake
• Hevo or Stitch: similar managed flow

Typical connector setup:

1. Create a read-only source user in Postgres/MySQL.
2. Connect the source database to the ETL tool.
3. Select the tables to sync.
4. Use incremental sync where possible.
5. Load into ANALYTICS.RAW.
6. Monitor first sync time and row counts.

If you want open-source control, use Meltano or Airbyte open source. If you prefer Python-first pipelines, use dlt.

Step 5: Model the tables with dbt

Create dbt models for the business layer.

A simple folder structure:

Copydbt_project/
  models/
    staging/
      stg_app__users.sql
      stg_app__orders.sql
      stg_ads__campaigns.sql
    marts/
      fct_orders.sql
      dim_customers.sql
      mart_growth_daily.sql
      mart_channel_performance.sql

The AI should primarily query marts, not raw replicated source tables.

This gives the AI better context:

• cleaned column names
• joined dimensions
• consistent date fields
• excluded sensitive columns
• business-friendly models
• metric-ready tables

Step 6: Connect the AI tool to the warehouse

Give Claude/Codex/MCP the Snowflake connection for AI_ANALYST_ROLE, scoped to the AI_ANALYTICS_WH warehouse and ANALYTICS.MARTS schema.

The AI connection should not use your admin role. It should not have access to every database. It should not use the same warehouse as production dashboards if you want cost isolation.

Practical setup: Airflow orchestration for custom pipelines

Use Airflow when the pipeline has multiple steps, dependencies, retries, or schedules.

Example flow:

Copy1. Extract changed rows from Postgres read replica
2. Load into Snowflake RAW schema
3. Run dbt staging models
4. Run dbt marts models
5. Run basic data quality checks
6. Notify Slack if the pipeline fails

A lightweight Airflow design:

Copyfrom airflow.decorators import dag, task
from datetime import datetime

@dag(start_date=datetime(2026, 1, 1), schedule="0 * * * *", catchup=False)
def sync_app_to_warehouse():
    @task
    def extract_from_replica():
        # Pull rows updated since last successful run.
        # Store output in object storage or directly load to warehouse staging.
        pass

    @task
    def load_to_snowflake():
        # Copy extracted files into ANALYTICS.RAW tables.
        pass

    @task
    def run_dbt_models():
        # Run dbt build for staging and marts.
        pass

    @task
    def run_quality_checks():
        # Check row counts, freshness, nulls, and duplicate keys.
        pass

    extract_from_replica() >> load_to_snowflake() >> run_dbt_models() >> run_quality_checks()

sync_app_to_warehouse()

You do not need Airflow on day one if a managed connector already solves the problem. Add it when pipeline dependencies become important.

What to sync first

Start with tables that answer real business questions.

For SaaS:

• users
• accounts/workspaces
• subscriptions
• invoices
• events
• feature usage
• CRM accounts/deals
• ad campaigns

For ecommerce:

• orders
• order line items
• customers
• products
• refunds
• transactions
• inventory
• ad spend
• web analytics
• search queries

Avoid syncing everything just because you can. Syncing fewer tables with better models is usually more useful than syncing the entire production schema without context.

Pattern 3: Give the AI a schema guide, not just schema access

Most AI SQL demos let the model inspect table names and columns. That is useful, but not enough.

Application schemas are designed for the product, not for analysis. Column names may be cryptic. Important definitions may live in application code. Some tables may be internal implementation details. Some columns may be deprecated.

If the AI only sees raw schema, it may generate technically valid SQL and still answer the wrong business question.

The better approach is to give the AI a short analytics guide.

What the guide should include

Create a file like analytics_context.md or a Claude/Codex skill file.

Include:

1. The trusted schemas/tables to use.
2. Tables to avoid.
3. Join keys.
4. Time fields to use for each table.
5. Business metric definitions.
6. Known caveats.
7. Query safety rules.
8. Examples of good SQL.

Example:

Copy# Analytics context for AI SQL assistant

## Trusted schemas

Use these schemas first:
- analytics.marts
- analytics.staging only if marts does not contain the needed field

Avoid these schemas unless the user explicitly asks for engineering/debugging help:
- raw
- internal
- audit_logs

## Important tables

### analytics.marts.fct_orders
Use for revenue, order count, average order value, refunds, and repeat purchase analysis.

Important fields:
- order_id: unique order ID
- customer_id: customer key
- order_created_at: use this for revenue reporting by date
- net_revenue: revenue after discounts and refunds
- gross_revenue: revenue before refunds
- channel: normalized marketing/source channel

### analytics.marts.dim_customers
Use for customer attributes and first purchase date.

Important fields:
- customer_id
- first_order_at
- country
- acquisition_channel

## Metric definitions

Revenue:
Use SUM(net_revenue) from analytics.marts.fct_orders.
Do not use gross_revenue unless the user asks for pre-refund revenue.

Average order value:
SUM(net_revenue) / COUNT(DISTINCT order_id)

Repeat purchase rate:
Customers with 2 or more orders / customers with at least 1 order in the selected period.

## Query rules

- Prefer analytics.marts tables.
- Always filter large fact tables by date unless the user asks for all-time totals.
- Use order_created_at for order reporting.
- Use customer_id for customer joins.
- Do not query raw PII fields.
- Add LIMIT 100 to exploratory row-level outputs.
- For expensive queries, first produce SQL and explain expected cost before running.

This guide is more useful than exposing 300 raw tables and hoping the AI guesses correctly.

How to keep the guide maintainable

Do not write a huge document.

Start with:

• 5 to 10 important tables
• 10 to 20 key metrics
• common joins
• fields to avoid
• examples of good queries

Update it when the AI makes a mistake.

If the AI uses the wrong date field, add that correction. If it joins customers and orders incorrectly, add the correct join. If it queries deprecated tables, mark them as deprecated.

This is a practical way to improve AI analytics quality without building a complex semantic layer from day one.

Pattern 4: Add a query validation layer before execution

A prompt is helpful, but it is not a control system.

For production-like usage, add a query validation step between the AI and the database.

CopyUser question
  -> AI writes SQL
  -> validator checks SQL
  -> database runs approved SQL
  -> AI summarizes result

The validator can be simple at first.

Basic rules to enforce

Reject queries that contain:

• INSERT
• UPDATE
• DELETE
• DROP
• TRUNCATE
• ALTER
• CREATE
• GRANT
• REVOKE
• COPY
• CALL
• multiple statements separated by semicolons

Require:

• only SELECT statements
• LIMIT for raw row outputs
• date filters on large event/order/log tables
• maximum query timeout
• maximum rows returned
• allowed schemas only

For PostgreSQL, you can parse SQL with libraries such as:

• sqlglot
• pglast
• sqlparse for lighter checks, though it is not enough for strong enforcement

For multi-dialect support, sqlglot is usually a practical option because it can parse and inspect many SQL dialects.

Example validation approach

Copyimport sqlglot

BLOCKED = {"INSERT", "UPDATE", "DELETE", "DROP", "TRUNCATE", "ALTER", "CREATE", "GRANT", "REVOKE"}
ALLOWED_SCHEMAS = {"analytics", "public"}
LARGE_TABLES = {"events", "orders", "activity_logs", "messages"}

def validate_ai_sql(sql: str) -> tuple[bool, str]:
    try:
        expressions = sqlglot.parse(sql, read="postgres")
    except Exception as e:
        return False, f"SQL could not be parsed: {e}"

    if len(expressions) != 1:
        return False, "Only one SQL statement is allowed."

    expression = expressions[0]

    command = expression.key.upper()
    if command != "SELECT":
        return False, "Only SELECT queries are allowed."

    upper_sql = sql.upper()
    if any(token in upper_sql for token in BLOCKED):
        return False, "Query contains a blocked SQL operation."

    if "LIMIT" not in upper_sql and not any(fn in upper_sql for fn in ["COUNT(", "SUM(", "AVG(", "MIN(", "MAX("]):
        return False, "Exploratory row-level queries must include a LIMIT."

    # Add deeper checks here:
    # - referenced schemas/tables
    # - date filter requirement for large tables
    # - max selected columns
    # - blocked sensitive columns

    return True, "OK"

Do not treat this as perfect security. Treat it as a practical guardrail. Combine it with read-only credentials, timeouts, row limits, and isolated compute.

Add EXPLAIN for expensive queries

Before running a query that touches large tables, ask the database for the plan.

In PostgreSQL:

CopyEXPLAIN SELECT ...;

Avoid EXPLAIN ANALYZE as the default safety check because it actually executes the query. Use plain EXPLAIN first.

Your AI tool can follow this flow:

1. Generate SQL.
2. If large tables are involved, run EXPLAIN.
3. If the estimated row count or cost is high, ask the user to narrow the time range or run against the warehouse.
4. Otherwise, run the query with timeout and row limit.

This one step prevents many “AI wrote a full table scan” incidents.

Pattern 5: Use a semantic layer for important metrics

A semantic layer defines business metrics in one place so every tool uses the same definition.

This matters because AI can write SQL, but it does not automatically know what your company means by “revenue,” “active customer,” “conversion,” “pipeline,” or “retention.”

For example, revenue could mean:

• gross sales
• net revenue after refunds
• recognized revenue
• subscription monthly recurring revenue
• paid invoices only
• orders excluding test orders

If each AI query invents the definition again, answers will drift.

When to add a semantic layer

Add one when:

• different teams argue about metric definitions
• the same question gets different answers in different tools
• AI is being used by non-technical users
• you need governed self-serve analytics
• dashboards and AI answers should match

Tool options

Common options:

• dbt Semantic Layer / MetricFlow
• Cube
• Looker semantic model
• Transform-style metric layer patterns
• a lightweight internal YAML metric registry if your team is small

You do not need to start with a full enterprise semantic layer. You can start with a simple metrics file that the AI reads.

Example:

Copymetrics:
  revenue:
    label: Revenue
    description: Net revenue after discounts and refunds.
    sql: SUM(net_revenue)
    base_model: analytics.marts.fct_orders
    time_dimension: order_created_at

  average_order_value:
    label: Average Order Value
    description: Net revenue divided by distinct orders.
    sql: SUM(net_revenue) / NULLIF(COUNT(DISTINCT order_id), 0)
    base_model: analytics.marts.fct_orders
    time_dimension: order_created_at

  repeat_purchase_rate:
    label: Repeat Purchase Rate
    description: Share of customers with two or more orders.
    base_model: analytics.marts.customer_order_summary

Then tell the AI:

CopyWhen the user asks for a metric that exists in metrics.yml, use that definition exactly. Do not recreate the metric from raw tables unless the user asks for a custom definition.

This is simple, but it prevents a lot of inconsistent answers.

Pattern 6: Protect sensitive fields before AI can query them

The safest approach is to avoid exposing sensitive fields to the AI connection in the first place.

Do not rely only on the prompt saying “do not query private data.”

Practical controls

Use a combination of:

• separate database role for AI access
• schema/table permissions
• column-level permissions where supported
• masked fields in warehouse models
• PII redaction before sending results to the model
• row-level security for tenant/customer boundaries
• audit logs for every query

PII means Personally Identifiable Information. Examples include email, phone number, full name, address, government ID, and payment-related identifiers.

For analytics, the AI usually does not need raw PII. It needs grouped counts, trends, segments, and IDs that are safe to inspect.

Better default for analytics models

Instead of giving the AI direct access to raw customer tables, create modeled tables with safer fields:

Copycustomer_id
customer_key_hash
country
region
signup_date
first_order_date
acquisition_channel
customer_segment
lifetime_orders
lifetime_revenue

Avoid by default:

Copyemail
phone
full_name
address
payment_token
access_token
api_key
notes/free-text fields with private information

If a user explicitly needs record-level work, route that through a more controlled workflow with approval, logging, and narrower permissions.

Pattern 7: Monitor what AI actually runs

This is one of the most useful practices and it is often skipped.

If an AI tool can run SQL, you should be able to answer:

• What query did it run?
• Who asked the question?
• Which database role was used?
• How long did the query run?
• How many rows came back?
• Did it time out?
• Which tables were touched?
• Did it create replica lag or warehouse cost spikes?

PostgreSQL monitoring

Enable pg_stat_statements to track query execution statistics.

Basic setup:

CopyCREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Depending on your hosting provider, you may also need to add pg_stat_statements to shared_preload_libraries and restart the database.

Useful query:

CopySELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Use this to find:

• most expensive AI-generated queries
• repeated bad query patterns
• queries returning too many rows
• tables that need better indexing or warehouse modeling

For live activity:

CopySELECT
  pid,
  usename,
  state,
  now() - query_start AS running_for,
  query
FROM pg_stat_activity
WHERE usename = 'ai_readonly'
ORDER BY query_start ASC;

To cancel a problematic query:

CopySELECT pg_cancel_backend(<pid>);

Use pg_terminate_backend only when cancellation is not enough.

MySQL monitoring

Use Performance Schema to inspect statement activity and summaries.

Useful tables include:

Copyperformance_schema.events_statements_current
performance_schema.events_statements_history
performance_schema.events_statements_summary_by_digest

Example query:

CopySELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT,
  AVG_TIMER_WAIT,
  SUM_ROWS_SENT,
  SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

This helps identify query patterns that are consuming the most time.

For currently running queries:

CopySHOW FULL PROCESSLIST;

To stop a bad query:

CopyKILL QUERY <process_id>;

Warehouse monitoring

For Snowflake:

• use a separate warehouse for AI queries
• enable resource monitors
• inspect query history by user, role, warehouse, and time period
• set auto-suspend aggressively for the AI warehouse

For BigQuery:

• use separate projects or service accounts for AI workloads
• set custom quotas where possible
• monitor jobs by user/service account
• require partition filters for large partitioned tables
• use dry runs to estimate bytes processed before execution

For ClickHouse:

• use query logs
• set max execution time
• set max memory usage
• use readonly users for AI access

The key is not the specific tool. The key is to separate AI workloads so you can monitor and limit them.

Pattern 8: Start with internal users, then expand access

Do not launch AI database access to the whole company on day one.

Roll it out in stages.

Stage 1: Engineering sandbox

Use:

• local database
• staging database
• sample data
• no sensitive data
• no production access

Goal:

• test SQL generation quality
• test your prompt/skill file
• identify dangerous query patterns

Stage 2: Read replica for technical users

Use:

• read replica
• read-only role
• query timeout
• audit logging
• limited schemas
• AI query policy

Goal:

• support debugging and controlled internal analysis
• learn which questions should move to the warehouse

Stage 3: Warehouse for business users

Use:

• synced source data
• modeled marts
• metric definitions
• PII-safe tables
• separate AI warehouse/compute
• query validation layer

Goal:

• let GTM, marketing, product, and operations teams ask business questions safely

Stage 4: Governed self-serve AI analytics

Use:

• semantic layer
• role-based access
• data quality checks
• query cost controls
• result caching
• lineage
• monitoring dashboards
• feedback loop for wrong answers

Goal:

• make AI analytics reliable enough for repeated team usage

What not to do

Avoid these patterns:

1. Giving an AI tool the production admin connection string.
2. Using the same database user your application uses.
3. Allowing write access “because we trust the prompt.”
4. Exposing every table and column by default.
5. Letting the AI query raw PII fields for normal analytics.
6. Running unbounded queries with no timeout.
7. Skipping query logs because “it is just internal.”
8. Treating AI-generated SQL as correct because it ran successfully.
9. Letting business users query production before you have tested common questions.
10. Assuming MCP replaces your data warehouse, metric definitions, or data quality process.

A practical default architecture

For most teams, this is a safe and useful target architecture:

CopyProduction app database
        |
        | replication / ELT
        v
Read replica or warehouse
        |
        | dbt / modeling / metric definitions
        v
Analytics marts + semantic context
        |
        | scoped credentials + query validator + timeout
        v
AI tool / MCP server
        |
        | query logs + cost monitoring + feedback loop
        v
Business answer

This keeps AI useful without putting the live application database at the center of every business question.

Final recommendation

AI SQL generation is useful. MCP is useful. Direct database access can be useful too, especially for engineers and small controlled use cases.

But for business analytics, the reliable path is still a proper analytics layer:

• use a read replica when the AI needs fresh application data
• use a warehouse when the question needs history, joins, and multiple sources
• give the AI a schema and metric guide
• validate SQL before execution
• restrict credentials
• protect sensitive fields
• monitor every query
• improve the guide when the AI makes mistakes

The goal is not to block people from asking questions.

The goal is to let more people ask better questions without risking production performance, data privacy, or metric trust.

If you want to package this as a real SKILL.md

If you are turning this guidance into a reusable agent skill, use the actual SKILL.md structure from the Claude Code skills docs. That format uses YAML frontmatter first, then a Markdown body with the instructions the agent should follow.

For a broader community example, see the OpenClaw data-analyst skill on GitHub. It is not a database-safety skill specifically, but it is a useful reference for how longer data and SQL-oriented skills are structured in practice.

Here is a minimal version of what this article’s guidance could look like as a real skill:

Copy---
name: sql-safety
description: Generate read-only analytics SQL safely against approved warehouse or replica tables. Use when answering business questions with SQL and you need guardrails around cost, PII, and metric definitions.
---

# SQL Safety

## Instructions

1. Use read-only access only.
2. Prefer analytics marts over raw application tables.
3. Never query the primary production database for ad hoc business analysis.
4. Avoid `SELECT *` unless inspecting a tiny sample.
5. Add a time filter to large event, order, and log tables.
6. Add `LIMIT` to exploratory row-level queries.
7. Prefer aggregates over raw records.
8. Use the team’s metric definitions exactly.
9. Do not return raw PII unless the workflow explicitly allows it.
10. If a query is likely to be expensive, explain the risk before running it.

A quick production-readiness checklist

Before you move from a sandbox or read replica to real internal usage, make sure this baseline is true:

• The AI does not use the primary production database connection.
• It uses a dedicated read-only database user or role.
• Query timeouts are enabled.
• Exploratory queries have row limits.
• Access is limited to approved schemas and tables.
• Sensitive fields are blocked, masked, or excluded.
• Query logs are available for review.
• Someone can cancel runaway queries.
• Warehouse usage has budget or resource controls.
• The AI has a schema and metric guide.
• The rollout starts with internal technical users first.
• Wrong answers are reviewed and fed back into the guide.