--- title: "Give your AI your schema and metric definitions so its first SQL query runs" description: "Document tables, joins, metric logic, reporting rules, and data quirks in files your AI reads, so generated SQL uses your schema instead of guesses." canonical: "https://scalewithsearch.com/articles/ai-for-data-analysts" date: "2026-01-28" modified: "2026-09-25" --- ## Site navigation - [Scale With Search](https://scalewithsearch.com/) - Real estate - Real estate - [Real estate](https://scalewithsearch.com/for/real-estate) - Work - Start here - [Send your brief](https://scalewithsearch.com/work#send-your-brief) - [Prepare your six-question brief](https://scalewithsearch.com/work#prepare-your-six-question-brief) - Build - [Site build, content library with SEO, signal desk](https://scalewithsearch.com/work) - For your business - Trades and home services - [Auto body and collision shops](https://scalewithsearch.com/for/auto-body-and-collision-shops) - [Foundation and home repair contractors](https://scalewithsearch.com/for/foundation-and-home-repair) - [Garage door and fencing contractors](https://scalewithsearch.com/for/garage-door-and-fencing-contractors) - [HVAC contractors](https://scalewithsearch.com/for/hvac-contractors) - [Janitorial and commercial cleaning companies](https://scalewithsearch.com/for/janitorial-and-commercial-cleaning) - [Locksmiths](https://scalewithsearch.com/for/locksmiths) - [Moving companies](https://scalewithsearch.com/for/moving-companies) - [Pest control companies](https://scalewithsearch.com/for/pest-control-companies) - [Plumbing and electrical contractors](https://scalewithsearch.com/for/plumbing-and-electrical-contractors) - [Restoration and water or fire damage companies](https://scalewithsearch.com/for/restoration-and-water-fire-damage) - [Roofing companies](https://scalewithsearch.com/for/roofing-companies) - [Towing companies](https://scalewithsearch.com/for/towing-companies) - [Tree services and landscaping companies](https://scalewithsearch.com/for/tree-services-and-landscaping) - [Solar installers](https://scalewithsearch.com/for/solar-installation) - [General contractors](https://scalewithsearch.com/for/general-contractors-and-construction) - [Paving, concrete, and flooring contractors](https://scalewithsearch.com/for/paving) - Practices and professional services - [Bookkeeping and tax practices](https://scalewithsearch.com/for/bookkeeping-and-tax-practices) - [Dental practices](https://scalewithsearch.com/for/dental-practices) - [Family and criminal defense law firms](https://scalewithsearch.com/for/family-and-criminal-defense-law-firms) - [Med spas and aesthetics practices](https://scalewithsearch.com/for/med-spas-and-aesthetics) - [Personal injury law firms](https://scalewithsearch.com/for/personal-injury-law-firms) - [Veterinary clinics](https://scalewithsearch.com/for/veterinary-clinics) - [Gyms and fitness studios](https://scalewithsearch.com/for/fitness) - [Therapy and outpatient health practices](https://scalewithsearch.com/for/therapy-and-outpatient-health) - [Medical billing companies](https://scalewithsearch.com/for/medical-billing) - [Insurance agencies](https://scalewithsearch.com/for/insurance-agencies) - [Financial advisors](https://scalewithsearch.com/for/financial-advisors) - [Property management companies](https://scalewithsearch.com/for/property-management) - [Recruiting and staffing agencies](https://scalewithsearch.com/for/recruiting-and-staffing) - [Architects and interior designers](https://scalewithsearch.com/for/architects-and-interior-designers) - [Logistics and supply chain companies](https://scalewithsearch.com/for/logistics-and-supply-chain) - Agencies, MSPs, and manufacturing - [IT and managed service providers](https://scalewithsearch.com/for/it-and-managed-service-providers) - [Machine shops and precision manufacturers](https://scalewithsearch.com/for/machine-shops-and-precision-manufacturing) - [Marketing agencies and freelancers](https://scalewithsearch.com/for/marketing-agencies-and-freelancers) - [SEO agencies and consultants](https://scalewithsearch.com/for/seo-agencies-and-consultants) - [Small manufacturers and fabricators](https://scalewithsearch.com/for/small-manufacturers-and-fabricators) - Restaurants, shops, studios, and nonprofits - [Restaurants and hospitality businesses](https://scalewithsearch.com/for/restaurants-and-hospitality) - [Retail stores and ecommerce sellers](https://scalewithsearch.com/for/retail-and-ecommerce) - [Photographers, event planners, and travel agents](https://scalewithsearch.com/for/photographers) - [Churches and nonprofits](https://scalewithsearch.com/for/churches-and-nonprofits) - [All industries](https://scalewithsearch.com/for/) - Learn - For your office - [Office job guides](https://scalewithsearch.com/guides/) - [Browser calculators](https://scalewithsearch.com/tools/) - Start here - [How it works](https://scalewithsearch.com/how-it-works) - [Free Starter Kit](https://scalewithsearch.com/kit/business-memory-starter-kit.zip) - [Synthetic specimen](https://scalewithsearch.com/specimen/working-session-specimen.zip) - Guides - [The Complete Guide to Business Memory for AI Agents](https://scalewithsearch.com/articles/business-memory-for-ai-agents-guide) - [The Complete Small-Business Guide to AI Agent Governance](https://scalewithsearch.com/articles/ai-agent-governance-guide-small-business) - [The Complete Guide to Leaving Vendor AI Memory](https://scalewithsearch.com/articles/leaving-vendor-ai-memory-guide) - Articles by cluster - [Business memory](https://scalewithsearch.com/articles/business-memory-for-ai-agents-guide) - [Agent governance](https://scalewithsearch.com/articles/ai-agent-governance-guide-small-business) - [Migration and ownership](https://scalewithsearch.com/articles/leaving-vendor-ai-memory-guide) - For machines - [llms.txt](https://scalewithsearch.com/llms.txt) - [llms-full.txt](https://scalewithsearch.com/llms-full.txt) - [Machine view](https://scalewithsearch.com/?view=machine) - Company - Evidence - [Proof](https://scalewithsearch.com/proof) - Company - [About](https://scalewithsearch.com/about) # Give your AI your schema and metric definitions so its first SQL query runs. You need monthly recurring revenue (MRR) by customer segment. You ask AI for the SQL. The query looks reasonable. You run it. Error: table `customers` does not exist. Your table is `accounts`. MRR is not a column; you calculate it from `subscription_value` and `billing_frequency`. Customer segment is stored as `tier_id`, not `segment_name`. You paste the schema, explain the MRR calculation, and clarify the tier mapping. The second query is closer, but it joins on `user_id` when it should join on `account_id`, because your users table is not your accounts table. Twenty minutes later the SQL works. Tomorrow, for another report, the assistant has forgotten the schema and you start over. An analyst does not need AI that writes generic SQL. You need AI that knows your database. ## Why a generic assistant writes broken SQL ChatGPT can write SQL. It cannot write your SQL, because it does not know your schema or your business logic. It also does not know how your tables relate, your naming conventions, or what a field means in your company. You work with five kinds of knowledge that the assistant does not have: - the schema: table names, column names, data types, relationships; - business logic: how revenue is calculated, what "active user" means, how churn is defined; - reporting formats: what stakeholders want, how to group, what is a metric and what is a dimension; - data quality: which columns are reliable, which have null problems, which need cleaning; - recurring queries: the monthly reports and dashboards that run the same way each time. Without them, table names, column names, and join logic are guesses. Sometimes the guess is close. Usually it is wrong. ## What the files must record The schema file records your actual tables, columns, data types, primary keys, foreign keys, and indexes. It says that `accounts` exists and `customers` does not. The business logic file records how revenue is calculated. It also defines "active user": a login in the last 30 days, a purchase, or an app open. It defines churn. It says which columns feed which metric. The same file records relationships: how users relate to accounts, how transactions relate to subscriptions, which joins work, and which joins create duplicates. The reporting file records stakeholder formats, default date ranges, rounding rules, and whether a report groups by month, week, or day. The quirks section records the legacy column that no longer updates and the table with null problems. It also names the slow join to avoid and the calculation with edge cases. ## Build four context files Claude Code reads a file named `CLAUDE.md` when a session starts. Use it to point to four files and to set rules: ```text ## Data context Schema: context/database-schema.md (generated; do not edit by hand) Metrics: context/business-logic.md Reporting: context/reporting-standards.md Saved queries: context/common-queries.md ## Rules Use only tables and columns that appear in database-schema.md. Use metric definitions from business-logic.md. Do not invent a definition. Avoid tables marked deprecated. Queries are read-only. Never write UPDATE, DELETE, or DDL. ``` `database-schema.md` lists each table and what it stores, columns with types and descriptions, keys, common joins, and tables to avoid. Generate it from the database so it does not drift. In Postgres, a query on `information_schema.columns` returns the table, column, and type for each column: ```sql SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position; ``` Write the result into the file on a schedule. Add descriptions by hand in a second file. `business-logic.md` holds metric formulas for MRR, churn, lifetime value (LTV), and customer acquisition cost (CAC). It also holds business definitions such as active user and qualified lead, segmentation rules, and edge cases. `reporting-standards.md` holds stakeholder formats, default date ranges for monthly and quarterly reports, grouping preferences, and chart standards. `common-queries.md` holds the reports that run on a schedule and the SQL pattern for each. A business logic entry can be this short: ```text ## MRR Source: subscriptions (join accounts on account_id; never on user_id) Formula: monthly plans = subscription_value; annual plans = subscription_value / 12 Include: status = 'active' on the last day of the month Segment: accounts.tier_id (1 = starter, 2 = growth, 3 = enterprise) Round: whole dollars in stakeholder reports Owner: finance analytics; last confirmed 2026-07-01 ``` ## Before and after Before: "Write SQL for MRR by customer segment." The assistant uses table `customers` (does not exist), column `mrr` (does not exist), and `segment` (stored as `tier_id`). You paste the schema, explain MRR, and clarify the tiers. The rewrite joins on `user_id`. You fix the join. It runs, but slowly, because it reads a deprecated table. Twenty minutes gone, and tomorrow you repeat it. After: the same prompt. The assistant reads the schema, business logic, and reporting files and returns: ```sql SELECT a.tier_id, date_trunc('month', s.period_end) AS month, ROUND(SUM(CASE s.billing_frequency WHEN 'monthly' THEN s.subscription_value WHEN 'annual' THEN s.subscription_value / 12.0 END)) AS mrr FROM accounts a JOIN subscriptions s ON s.account_id = a.account_id WHERE s.status = 'active' GROUP BY a.tier_id, date_trunc('month', s.period_end) ORDER BY month, a.tier_id; ``` It uses the accounts table, calculates MRR by the documented rule, joins on `account_id`, groups by `tier_id`, and rounds as the stakeholders expect. You run it and check the totals against last month's finance report. Next request: "Add churn rate to that report." The assistant reads the churn definition in `business-logic.md`, uses the right columns, and extends the query. You check the first run by hand. ## A worked setup An analyst at a software company sets up the four files, mostly by copying documentation that already exists. When she asks for SQL, the assistant knows the schema, the logic, and the reporting standards. When she asks for Python to clean data, the assistant knows which columns have null problems. When she asks for a dashboard query, it knows what stakeholders want. When the schema changes, she regenerates `database-schema.md`, and every later query uses the new schema. When a definition changes, such as a new MRR rule or churn window, she updates `business-logic.md` with the date and the owner. The assistant follows the new rule. When the assistant repeats a mistake, she writes the fix into the file. [How corrections become part of the system](/articles/corrections-become-system-memory) explains why that works better than a chat reminder. The files also become the data dictionary. New analysts read them to onboard. Stakeholders read them to see where a number comes from. Plain Markdown keeps them readable by people and tools alike; see [why plain text is the durable layer for AI memory](/articles/plain-text-ai-memory). ## Test before you trust Run three checks after setup, and again after each schema change. 1. Ask for a query on a table that does not exist. Pass: the assistant says it is not in the schema file. Fail: it invents columns. 2. Ask for MRR. Pass: the result matches the finance report for a closed month. Fail: fix the definition, not the query. 3. Ask for a query that deletes rows. Pass: the assistant refuses under the read-only rule. Fail: remove write permissions from the credential it uses. The broader method for proving a run before it touches real systems is in [how to test an AI agent before production](/articles/test-an-ai-agent-before-production). Which files load first, and why, is covered in [what context should an agent read before it starts](/articles/what-context-should-an-agent-read). ## Keep the schema file from drifting A schema file that is three migrations behind is worse than none. The assistant trusts it and writes queries against columns that moved. Set two rules. First, regenerate `database-schema.md` on a schedule, such as every Monday, or as a step in your migration process. Second, add the generation date to the top of the file. Then add a rule to `CLAUDE.md`: if the schema file is older than seven days, say so before you write a query. Keep hand-written descriptions in a second file, `context/schema-notes.md`, keyed by table and column. The generated file never overwrites them, and the assistant reads both. ## Data limits Put structure in the files, not customer rows. Claude Code sends the content it reads, including query results it sees, to the model provider over the network ([Claude Code: Data usage](https://code.claude.com/docs/en/data-usage)). If the assistant runs queries, give it a read-only credential with access only to the schemas it needs. Keep personal data out of the prompts and out of the results it reads. ---- ```text .|########||. .|########||. .|########||. |##||. .||##|. |##||. .||##|. |##||. .||##|. |#|. .|#|. |#|. .|#|. |#|. .|#|. |#| |#| |#| |#| |#| |#| .#| |#. .#| |#. .#| |#. .#. .#| .#. .#| .#. .#| |#. .#| |#. .#| |#. .#| |# ...... #| |# ...... #| |# ...... #| .# ||#########| #| .# ||#########| #| .# ||#########| #| .#. |######||######|. .#. .#. |######||######|. .#. .#. |######||######|. .#. #. .##|###|##|#|######| .# #. .##|###|##|#|######| .# #. .##|###|##|#|######| .# || |##|#||||||||||||#||#| || || |##|#||||||||||||#||#| || || |##|#||||||||||||#||#| || # |#||||||||||||||||||||#| #. # |#||||||||||||||||||||#| #. # |#||||||||||||||||||||#| #. || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || # .#||||||||||||||||||||||||#| # # .#||||||||||||||||||||||||#| # # .#||||||||||||||||||||||||#| # || ....|||#||||||##|#|||#|#||##||||....|. || || ....|||#||||||##|#|||#|#||##||||....|. || || ....|||#||||||##|#|||#|#||##||||....|. || #. . ....|# ....#||| ||| .#||| ....#. .# #. . ....|# ....#||| ||| .#||| ....#. .# #. . ....|# ....#||| ||| .#||| ....#. .# # . ||||#| .#####|| . .#| .#||| ||||# #. # . ||||#| .#####|| . .#| .#||| ||||# #. # . ||||#| .#####|| . .#| .#||| ||||# #. .| ||||| #. |#|||||. || #. |#||. .||||| || .| ||||| #. |#|||||. || #. |#||. .||||| || .| ||||| #. |#|||||. || #. |#||. .||||| || || |.... #. ....|#. |. ...|. ....|| || || |.... #. ....|#. |. ...|. ....|| || || |.... #. ....|#. |. ...|. ....|| || #. .||||||##||||||##||####|||||||#||||||#| .# #. .||||||##||||||##||####|||||||#||||||#| .# #. .||||||##||||||##||####|||||||#||||||#| .# # .||####|###########################|. # # .||####|###########################|. # # .||####|###########################|. # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#||||||############|######|||||#| # # .#||||||############|######|||||#| # # .#||||||############|######|||||#| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# || | ...|| ...##| | #| .|. |. ##### .. .| || || | ...|| ...##| | #| .|. |. ##### .. .| || || | ...|| ...##| | #| .|. |. ##### .. .| || || ||||| || ||||#| . |. |. |#. ||||| |#| || || || ||||| || ||||#| . |. |. |#. ||||| |#| || || || ||||| || ||||#| . |. |. |#. ||||| |#| || || .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. #.|||||||#############################| ||| .# #.|||||||#############################| ||| .# #.|||||||#############################| ||| .# || ##||#||#||||||#||#||#||#||##. || || ##||#||#||||||#||#||#||#||##. || || ##||#||#||||||#||#||#||#||##. || # .#|||||||||#||#|||||||||||#| # # .#|||||||||#||#|||||||||||#| # # .#|||||||||#||#|||||||||||#| # || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || .# |#||||||||||||||||||||#| #. .# |#||||||||||||||||||||#| #. .# |#||||||||||||||||||||#| #. || |#||||||||||||||||||#| || || |#||||||||||||||||||#| || || |#||||||||||||||||||#| || #. .######|#|#########| .# #. .######|#|#########| .# #. .######|#|#########| .# .#. |#####||||#####| .#. .#. |#####||||#####| .#. .#. |#####||||#####| .#. |# ||########|| #. |# ||########|| #. |# ||########|| #. |# ...... #| |# ...... #| |# ...... #| |#. .#| |#. .#| |#. .#| |#. .#. |#. .#. |#. .#. .#| |#. .#| |#. .#| |#. |#| |#| |#| |#| |#| |#| .|#|. .|#|. .|#|. .|#|. .|#|. .|#|. |##||. .||##| |##||. .||##| |##||. .||##| .||########||. .||########||. .||########||. Scale With Search 2026 [scalewithsearch.com](https://scalewithsearch.com) ```