Step 1: Bring Your Data Home (Safely)

First, we use MotherDuck's hybrid architecture to create a local copy of our database. With a single SQL command, we can replicate a database from our MotherDuck cloud account to a local DuckDB file.

For this example, I'm using the Foursquare places dataset called FSQ:

Copy code
-- Filename: clone_db.sql
attach 'md:';
attach 'local.db' as local_db;
COPY FROM DATABASE fsq TO local_db;

Running this command pulls the data from MotherDuck and creates a local_fsq.duckdb file on my machine. Now I have a perfect, isolated sandbox.

Practical Tip: If your production dataset is very large, you don't need to pull all of it. DuckDB's SAMPLE feature lets you grab a representative subset of your data, keeping your local copy manageable and responsive.

Step 2: Give Your AI a Map (Schema as XML)

An LLM's biggest limitation is context. To get quality SQL, we need to provide the AI with a map of our database structure.

Through conversations with researchers at MotherDuck, we've found that providing the schema as an XML file within the prompt's context is particularly effective for getting good results.

We can automate this with a simple Python script that connects to our local DuckDB file, extracts the schema, and saves it as an XML file:

Copy code
# Filename: scripts/get_schema.py
"""Script to extract database schema from DuckDB and output as XML.

This script connects to a DuckDB database, extracts the schema information,
and outputs it in a machine-readable XML format that can be used in Cursor.
"""

import duckdb
import xml.etree.ElementTree as ET
from pathlib import Path

def get_schema_as_xml(db_path: str) -> ET.Element:
    """Extract schema from DuckDB database and return as XML Element.
    
    Args:
        db_path: Path to the DuckDB database file
        
    Returns:
        ET.Element: XML Element containing the database schema
    """
    # Connect to the DuckDB database
    conn = duckdb.connect(db_path)
    
    # Get all tables
    tables = conn.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'main'").fetchall()
    
    # Create XML root
    root = ET.Element("database")
    root.set("name", Path(db_path).stem)
    
    # For each table, get its schema
    for (table_name,) in tables:
        table_elem = ET.SubElement(root, "table")
        table_elem.set("name", table_name)
        
        # Get column information
        columns = conn.execute(f"""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_schema = 'main' AND table_name = '{table_name}'
            ORDER BY ordinal_position
        """).fetchall()
        
        for col_name, data_type, is_nullable in columns:
            column_elem = ET.SubElement(table_elem, "column")
            column_elem.set("name", col_name)
            column_elem.set("type", data_type)
            column_elem.set("nullable", is_nullable)
    
    conn.close()
    return root

def save_schema_to_file(root: ET.Element, output_path: str) -> None:
    """Save the XML schema to a file with pretty printing.
    
    Args:
        root: XML Element containing the schema
        output_path: Path where to save the XML file
    """
    ET.indent(root)
    tree = ET.ElementTree(root)
    tree.write(output_path, encoding="utf-8", xml_declaration=True)

if __name__ == "__main__":
    db_path = "local.db"
    output_path = "schema/local_db_schema.xml"
    
    root = get_schema_as_xml(db_path)
    save_schema_to_file(root, output_path)
    print(f"Schema saved to {output_path}")

Now, whenever we chat with our AI, we'll include this local_db_schema.xml file as context.

Step 3: Define the Rules of Engagement

This is where we automate the "run and fix" loop. In Cursor, we can create rules to give the LLM persistent instructions for the project.

First, we define our SQL rule. We tell the AI that whenever it writes a SQL file, it should immediately execute it using the DuckDB CLI against our local database file. This creates the essential feedback mechanism:

Copy code
---
description: 
globs: *.sql
alwaysApply: false
---
# SQL Rules
This rule applies to all SQL files in the project.
## File Pattern
*.sql
## Description
When working with SQL files, we use DuckDB as our database engine. SQL files should be executed using the command `duckdb local.db -f {file}`.
## Formatting
- Use 4 spaces for indentation
- Use SQLFluff for formatting with DuckDB dialect
- Format on save
## Commands
- Run SQL file: duckdb local.db -f {file}
## Best Practices
- Use consistent naming conventions
- Include comments for complex queries
- Use proper indentation for readability
- Follow DuckDB's SQL dialect specifications

Next, we set up similar rules for Python work, directing the AI to use uv for package management. This ensures clean, reproducible environments for any data visualization or scripting we do.

Copy code
---
description: 
globs: *.py
alwaysApply: false
---
# Python Rules
This rule applies to all Python files in the project.

## File Pattern
*.py

## Description
When working with Python files, we use uv as our package manager and runtime. Python files should be executed using the command `uv run {file}`.

## Formatting
- Use 4 spaces for indentation
- Follow PEP 8 style guide
- Use Ruff for code formatting and linting
- Format on save

## Best Practices
- Use type hints where appropriate
- Include docstrings for functions and classes
- Use virtual environments for dependency management

With these pieces in place, our intelligent co-pilot is ready to waddle into action.

The First Question

We start by asking for the basic data.

Prompt: "Give me a SQL query for restaurants in Oakland, CA."

By providing our schema and SQL rules as context, the AI generates a correct query, saves it to a file, and immediately runs it using the DuckDB CLI. It sees that the query executes successfully and returns over 3,000 rows.

From Data to Visualization

A table with 3,000 rows isn't particularly insightful. Let's visualize it.

Prompt: "Let's use Folium to chart this data on a map. Create the map in HTML and then serve it with Python."

Recognizing the need for visualization, the AI switches from SQL to Python. Following our rules, it adds folium and pandas to our pyproject.toml file, writes a Python script to read the SQL output and generate a map, and serves it on a local webserver. Just like that, we have an interactive map showing every restaurant in our dataset.

Iterating for Clarity

The map looks a bit crowded with individual points. Let's refine it.

Prompt: "Can we render this as a heatmap instead of points?"

The AI modifies the Python script, importing the HeatMap plugin from Folium and regenerating the map. Now we have a much clearer view of restaurant density across Oakland.

The 'Aha!' Moment - Self-healing SQL

Now for the real test. Let's ask a much more complex question that requires spatial analysis.

Prompt: "Load the spatial extension for DuckDB. Find me three 1-acre locations where we have high restaurant density, but no African cuisine within one mile. Score the locations based on the number of other restaurants nearby."

This is where things get interesting. The AI's first attempt at this complex spatial query returns... zero results.

In a traditional workflow, this is where you'd start the tedious debugging cycle. But in our closed-loop system, the AI recognizes its failure. It sees the empty result set and immediately begins troubleshooting itself. It thinks, "The query ran but returned nothing. Let's run a diagnostic query. Do we even have any African restaurants in the dataset?" It runs a COUNT(*) on that category, confirms the data exists, and then reevaluates its initial query. It realizes its initial spatial join was too restrictive and broadens the search radius before running the query again.

This is when you realize you're working with something more than just a code generator. The AI is functioning as an analyst. It can reason about its own failures and adjust course without your intervention.

After a few self-corrections, it produces a new query that works, identifying three promising locations.

Putting It All Together

Prompt: "Add these three proposed locations as colored boxes on our heatmap."

The AI updates the Python script one more time, adding a new layer to our Folium map. We now have a complete, informative visualization: a heatmap of existing restaurant density with three clear boxes highlighting the top-scoring, underserved areas for our new venture.