--- title: "Write a data dictionary so AI returns correct SQL on the first run" description: "Give AI your schema, metric definitions, fiscal calendar, and data quality rules in one file, so its SQL matches your business logic." canonical: "https://scalewithsearch.com/articles/ai-memory-for-data-analysis" 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) # Write a data dictionary so AI returns correct SQL on the first run. You ask an AI assistant for a query: "Pull active customers from last quarter." It returns valid SQL that selects the wrong rows. The assistant does not know what "active" means in your business. It does not know which table stores customer status. It does not know that your fiscal quarters differ from calendar quarters. It does not know that `customer_status` has three inactive values to exclude. The assistant can write SQL. It cannot see your data model or your business rules. A data dictionary fixes that gap. It is one Markdown file, `data-dictionary.md`, that the assistant reads before it writes a query. The sections below use one fictional subscription business as the example. ## Know what the assistant must already know Take the request "Pull fiscal Q4 active customers with lifetime value (LTV) over 5,000." A correct query needs six facts: - the database schema: table names, relationships, and field meanings; - the business definition of "active": a completed purchase within 90 days, and a status other than `churned`, `cancelled`, or `paused`; - the LTV formula, its tables, and its exclusions; - the fiscal calendar: here, fiscal Q4 is July to September; - the known data quality issues: duplicate emails and test accounts; - which fields are reliable and which need validation. Each fact belongs in a section of the dictionary. For a broader view of what an agent should read before it starts, see [what context an agent should read](/articles/what-context-should-an-agent-read). ## Describe the tables you query Do not paste a full schema dump. List the tables and fields you use, with their business meaning. ```text ## Core tables ### customers Primary table for customer data. Updated nightly from Stripe. Key fields: - customer_id (PK, unique) - email (unique, but about 200 duplicates from a migration; use customer_id as the source of truth) - status: 'active', 'churned', 'cancelled', 'paused', 'trial' - Active = purchased within 90 days AND status = 'active' - Churned = no purchase in 90+ days OR status = 'churned' - created_at (account creation date, UTC) - first_purchase_date (first successful payment, UTC) - total_spend (lifetime revenue, in cents) - mrr (monthly recurring revenue, in cents, NULL for one-time customers) Relationships: - customers.customer_id -> purchases.customer_id (one to many) - customers.customer_id -> subscriptions.customer_id (one to many) ### purchases Transaction history. Includes one-time and subscription payments. Key fields: - purchase_id (PK, unique) - customer_id (FK to customers) - amount (in cents, includes tax) - purchase_date (UTC timestamp) - product_id (FK to products) - status: 'completed', 'refunded', 'failed', 'pending' - Only 'completed' counts toward revenue - Refunded transactions stay in the table with status = 'refunded' ### subscriptions Active and cancelled subscriptions. Key fields: - subscription_id (PK, unique) - customer_id (FK to customers) - plan_id (FK to plans) - status: 'active', 'cancelled', 'paused', 'past_due' - mrr (monthly value in cents) - start_date (subscription start, UTC) - cancelled_at (NULL if active, timestamp if cancelled) ``` The notes carry the value. "In cents" prevents a revenue figure that is 100 times too large. "Use customer_id as the source of truth" prevents a count that includes duplicates. ## Define each metric with its logic and its traps A formula alone is not enough. Record the definition, the SQL logic, and the notes that a new analyst would get wrong. ```text ## Key metrics ### Active customer Definition: purchased within the last 90 days AND status = 'active' SQL logic: WHERE c.status = 'active' AND EXISTS (SELECT 1 FROM purchases p WHERE p.customer_id = c.customer_id AND p.status = 'completed' AND p.purchase_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)) Notes: - Do not use the status field alone. Sync lag sometimes shows churned customers as 'active'. - The 90-day window is the company standard and matches the churn definition. - Active in a past period: at least one completed purchase inside that period. ### Customer lifetime value (LTV) Definition: total revenue from the customer since account creation, without refunds SQL logic: SELECT customer_id, SUM(amount) / 100 AS ltv FROM purchases WHERE status = 'completed' GROUP BY customer_id Notes: - Divide by 100. Amounts are stored in cents. - Exclude refunded transactions (status = 'refunded'). - Do not add projected MRR. A forecast LTV is a separate metric with its own name. - Pending and failed transactions do not count. ### Monthly recurring revenue (MRR) Definition: sum of all active subscription monthly values SQL logic: SELECT SUM(mrr) / 100 AS total_mrr FROM subscriptions WHERE status = 'active' Notes: - Already monthly. Annual plans are divided by 12. - Paused subscriptions are excluded. - Past-due subscriptions count until formally cancelled. ### Churn rate (monthly) Definition: percentage of customers who cancelled a subscription in a given month SQL logic: -- subscription customers who cancelled during the month -- divided by subscription customers at the start of the month -- times 100 Notes: - Count subscription customers only. Exclude one-time purchasers. - A paused subscription is not churn unless it is formally cancelled. - Calculate by calendar month, not a rolling 30 days. ``` ## Record the fiscal calendar and the default comparisons If your fiscal year differs from the calendar year, every quarterly report depends on this section. ```text ## Reporting periods ### Fiscal year Runs Oct 1 to Sep 30, not Jan to Dec. - Q1: Oct, Nov, Dec - Q2: Jan, Feb, Mar - Q3: Apr, May, Jun - Q4: Jul, Aug, Sep A request for "Q4 2025" means Jul to Sep 2025. ### Reporting frequency - Revenue: daily dashboard, monthly reports, quarterly board reports - Churn: monthly only - Customer acquisition: weekly trend, monthly detail - Product metrics: daily for monitoring, weekly for analysis ### Default comparisons - Monthly reports: compare with the same month last year, not the previous month - Quarterly reports: compare with the same quarter last year - Weekly reports: compare with the previous week, not the same week last year ``` The comparison rules look minor. They are the rules an assistant gets wrong most quietly, because both answers produce a plausible chart. ## Document the data quality issues Every database has defects. Write them down, with the filter that removes each one. ```text ## Known data issues ### Test accounts Exclude from all customer analysis: - any email that ends in @test.com - any email that ends in @example.com - customer_id values 1 to 100 (early test accounts) - any customer with 'test' in the name field Standard exclusion filter: WHERE email NOT LIKE '%@test.com' AND email NOT LIKE '%@example.com' AND customer_id > 100 ### Duplicate records - About 200 duplicate emails in customers, from the 2024 migration. - Use customer_id as the source of truth, not email. - To remove a duplicate, keep the record with the earliest created_at. ### Missing data - first_purchase_date is NULL for about 500 customers (accounts from before 2023). Use MIN(purchase_date) from purchases for these. - mrr is NULL for one-time customers. This is expected. - About 0.5% of purchases lack a product_id, from a payment processor error. ### Time zones - All timestamps are stored in UTC. - The dashboard shows Pacific time: UTC minus 8 hours in PST, minus 7 hours in PDT. - Convert before a date filter that must match the dashboard. ``` ## Store the queries you run every week Recurring queries should not change shape from week to week. Store the approved pattern, and the assistant reuses it. Name the SQL dialect at the top of the dictionary, such as `dialect:: MySQL 8`. The patterns below use MySQL functions: `DATE_FORMAT`, `DATE_SUB`, and `DATEDIFF`. PostgreSQL uses `to_char`, interval arithmetic, and date subtraction instead. Without a dialect line, the assistant picks one. A query written for one database then fails in the other. ```sql -- Monthly revenue report SELECT DATE_FORMAT(p.purchase_date, '%Y-%m') AS month, COUNT(DISTINCT p.customer_id) AS customers, COUNT(p.purchase_id) AS transactions, SUM(p.amount) / 100 AS revenue FROM purchases p JOIN customers c ON c.customer_id = p.customer_id WHERE p.status = 'completed' AND p.purchase_date >= '2024-01-01' AND c.email NOT LIKE '%@test.com' AND c.email NOT LIKE '%@example.com' AND c.customer_id > 100 GROUP BY month ORDER BY month DESC; -- Customer cohort analysis: signups by month, with first purchase behavior SELECT DATE_FORMAT(created_at, '%Y-%m') AS signup_month, COUNT(customer_id) AS signups, COUNT(first_purchase_date) AS converted, ROUND(COUNT(first_purchase_date) / COUNT(customer_id) * 100, 1) AS conversion_rate, AVG(DATEDIFF(first_purchase_date, created_at)) AS days_to_first_purchase FROM customers WHERE created_at >= '2024-01-01' AND email NOT LIKE '%@test.com' AND email NOT LIKE '%@example.com' AND customer_id > 100 GROUP BY signup_month ORDER BY signup_month DESC; ``` An earlier draft of the revenue report filtered on `email` without a join to `customers`. The `purchases` table has no email field, so that draft fails. The pattern above adds the join. A stored pattern is only useful after someone runs it once against the real schema. ## Compare the query with and without the dictionary Without the dictionary, a typical answer to "Write a query to pull fiscal Q4 active customers with LTV over 5,000" looks like this: ```sql SELECT customer_id, email, SUM(purchase_amount) AS ltv FROM customers JOIN purchases ON customers.id = purchases.customer_id WHERE status = 'active' AND purchase_date >= '2025-10-01' AND purchase_date < '2026-01-01' GROUP BY customer_id HAVING ltv > 5000; ``` It has five errors. It uses the calendar quarter, not the fiscal quarter. It trusts the status field alone and never checks for a completed purchase inside the period. It sums refunds and does not convert cents. It keeps test accounts. It uses field names that do not exist. With the dictionary, the same request should return a query that states its interpretation: ```sql -- Fiscal Q4 FY2025 = 2025-07-01 to 2025-09-30 -- Active in the period = status 'active' and a completed purchase inside the period -- LTV = lifetime completed purchases, converted from cents SELECT c.customer_id, c.email, SUM(p.amount) / 100 AS ltv, MAX(p.purchase_date) AS last_purchase FROM customers c JOIN purchases p ON c.customer_id = p.customer_id WHERE c.status = 'active' AND p.status = 'completed' AND c.email NOT LIKE '%@test.com' AND c.email NOT LIKE '%@example.com' AND c.customer_id > 100 GROUP BY c.customer_id, c.email HAVING SUM(CASE WHEN p.purchase_date >= '2025-07-01' AND p.purchase_date < '2025-10-01' THEN 1 ELSE 0 END) > 0 AND SUM(p.amount) / 100 > 5000 ORDER BY ltv DESC; ``` The header comments matter as much as the SQL. They let you check the interpretation before you trust the numbers. A quarter filter in `WHERE` would turn lifetime value into quarter revenue. The version above keeps LTV lifetime and tests the quarter in `HAVING`. ## Start small and grow the file from corrections Follow this order: 1. Pick your three most-used tables. 2. For each table, list the fields you query and their business meaning. 3. Write definitions for your top three to five metrics, with the SQL logic and the traps. 4. Document one known data quality issue, such as test accounts or duplicates. 5. Ask the assistant for a real query and check the result. 6. When the query is wrong, add the correct definition to the dictionary. Step 6 is where the file grows. Each wrong query exposes a rule that lived only in someone's head. [How corrections become part of the system](/articles/corrections-become-system-memory) covers how to make each fix change the next run. ## Keep the dictionary next to the schema A dictionary drifts when the schema changes and the file does not. Two habits keep them in step. First, keep the dictionary in the same repository as your migrations. Add this line to your migration checklist: a table or column change updates `data-dictionary.md` in the same pull request. A reviewer then sees the schema change and the dictionary change together. Second, generate the raw column list on a schedule and diff it against the dictionary. In MySQL and PostgreSQL, a query on `information_schema.columns` lists every table and column. Run it weekly and save the output as `schema-export.md`. Then ask the assistant to list the columns that appear in the export but not in the dictionary. Each one is either a column you never query, which you can ignore, or a column that needs an entry. Add a `last_verified` date to the top of the dictionary. Add a rule to `CLAUDE.md`: if the dictionary is older than 30 days, say so before you write a query. ## Run a five-query acceptance test Before the dictionary goes into daily use, test it with queries you already know the answers to. 1. Pick five questions from last month's reports, such as "How many active customers did we have on the last day of fiscal Q4?" 2. For each one, ask the assistant for the query in a fresh session, with only the dictionary as context. 3. Run each query and compare the result with the number in the report. 4. For each mismatch, find the definition the assistant misread or lacked. 5. Fix the dictionary entry, and run the same five questions again. The dictionary passes when all five results match the reports. A query that matches by chance still counts as a fail if its header comment states the wrong interpretation. The comment is what a reviewer reads first. ## Watch what changes Early queries still need business-logic fixes, but wrong table and field names stop. As the dictionary grows to cover your regular tables and metrics, your review moves from SQL repair to a check of the result. Measure it. Record how many attempts each query needs and how long the question takes to answer, before and after the dictionary. A dictionary does not make the assistant's numbers true. Run each new query against a period with a known answer before you use it in a report. The [guide to testing an AI agent before production](/articles/test-an-ai-agent-before-production) applies the same rule to any output that people act on. If you also keep a project-level `CLAUDE.md`, point it at `data-dictionary.md` instead of a copy of the definitions. The [CLAUDE.md template for business context](/articles/claude-md-template-business-context) shows that split. ## Related: AI memory - [Remove the context setup step that makes AI slow down your work](/articles/ai-workflow-bottleneck) - [Copy a CLAUDE.md template for your role and adapt it in 30 minutes](/articles/claude-md-template-examples) - [Measure the time you lose to AI context setup before you fix it](/articles/how-much-time-wasted-on-ai-context) ---- ```text .|########||. .|########||. .|########||. |##||. .||##|. |##||. .||##|. |##||. .||##|. |#|. .|#|. |#|. .|#|. |#|. .|#|. |#| |#| |#| |#| |#| |#| .#| |#. .#| |#. .#| |#. .#. .#| .#. .#| .#. .#| |#. .#| |#. .#| |#. .#| |# ...... #| |# ...... #| |# ...... #| .# ||#########| #| .# ||#########| #| .# ||#########| #| .#. |######||######|. .#. .#. |######||######|. .#. .#. |######||######|. .#. #. .##|###|##|#|######| .# #. .##|###|##|#|######| .# #. .##|###|##|#|######| .# || |##|#||||||||||||#||#| || || |##|#||||||||||||#||#| || || |##|#||||||||||||#||#| || # |#||||||||||||||||||||#| #. # |#||||||||||||||||||||#| #. # |#||||||||||||||||||||#| #. || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || # .#||||||||||||||||||||||||#| # # .#||||||||||||||||||||||||#| # # .#||||||||||||||||||||||||#| # || ....|||#||||||##|#|||#|#||##||||....|. || || ....|||#||||||##|#|||#|#||##||||....|. || || ....|||#||||||##|#|||#|#||##||||....|. || #. . ....|# ....#||| ||| .#||| ....#. .# #. . ....|# ....#||| ||| .#||| ....#. .# #. . ....|# ....#||| ||| .#||| ....#. .# # . ||||#| .#####|| . .#| .#||| ||||# #. # . ||||#| .#####|| . .#| .#||| ||||# #. # . ||||#| .#####|| . .#| .#||| ||||# #. .| ||||| #. |#|||||. || #. |#||. .||||| || .| ||||| #. |#|||||. || #. |#||. .||||| || .| ||||| #. |#|||||. || #. |#||. .||||| || || |.... #. ....|#. |. ...|. ....|| || || |.... #. ....|#. |. ...|. ....|| || || |.... #. ....|#. |. ...|. ....|| || #. .||||||##||||||##||####|||||||#||||||#| .# #. .||||||##||||||##||####|||||||#||||||#| .# #. .||||||##||||||##||####|||||||#||||||#| .# # .||####|###########################|. # # .||####|###########################|. # # .||####|###########################|. # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#||||||.#.|| # |. ..# #| #|||||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||||| .. |# ## |#| ...#|#|||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#|||#|# .# .#| #| ##|.#..#|||||#| # # .#||||||############|######|||||#| # # .#||||||############|######|||||#| # # .#||||||############|######|||||#| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # # |...||#||||||#||||#|#||||||#|||||#||| |.| # #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# #. .. ||||# .|||##|. |#| .|| || .|||#. #| # .# || | ...|| ...##| | #| .|. |. ##### .. .| || || | ...|| ...##| | #| .|. |. ##### .. .| || || | ...|| ...##| | #| .|. |. ##### .. .| || || ||||| || ||||#| . |. |. |#. ||||| |#| || || || ||||| || ||||#| . |. |. |#. ||||| |#| || || || ||||| || ||||#| . |. |. |#. ||||| |#| || || .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. .# |.....#|....|#.||||.|||##.|#|....||.#||.#. #. #.|||||||#############################| ||| .# #.|||||||#############################| ||| .# #.|||||||#############################| ||| .# || ##||#||#||||||#||#||#||#||##. || || ##||#||#||||||#||#||#||#||##. || || ##||#||#||||||#||#||#||#||##. || # .#|||||||||#||#|||||||||||#| # # .#|||||||||#||#|||||||||||#| # # .#|||||||||#||#|||||||||||#| # || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || || |#||||||||||||||||||||||#| || .# |#||||||||||||||||||||#| #. .# |#||||||||||||||||||||#| #. .# |#||||||||||||||||||||#| #. || |#||||||||||||||||||#| || || |#||||||||||||||||||#| || || |#||||||||||||||||||#| || #. .######|#|#########| .# #. .######|#|#########| .# #. .######|#|#########| .# .#. |#####||||#####| .#. .#. |#####||||#####| .#. .#. |#####||||#####| .#. |# ||########|| #. |# ||########|| #. |# ||########|| #. |# ...... #| |# ...... #| |# ...... #| |#. .#| |#. .#| |#. .#| |#. .#. |#. .#. |#. .#. .#| |#. .#| |#. .#| |#. |#| |#| |#| |#| |#| |#| .|#|. .|#|. .|#|. .|#|. .|#|. .|#|. |##||. .||##| |##||. .||##| |##||. .||##| .||########||. .||########||. .||########||. Scale With Search 2026 [scalewithsearch.com](https://scalewithsearch.com) ```