Connecting AI to the database

Step 1: Load the data into DuckDB

Until now, you've been working with the CSV directly through pandas. Load it into a DuckDB database so you can query it with SQL and -- in the next step -- connect AI directly to it.

Direct AI to create a DuckDB database from the CSV:

Create a DuckDB database at ~/dev/analytics/p8/experiment.duckdb. Load materials/ab-test-data.csv into a table called ab_test_data. Use explicit column types based on the data dictionary: visitor_id as VARCHAR, page_version as VARCHAR, visit_date as DATE, tour_selected as VARCHAR, booking_completed as BOOLEAN, booking_value as DECIMAL, visitor_source as VARCHAR. Don't let DuckDB infer the types -- specify them.

Specifying column types explicitly matters. DuckDB's type inference is usually good, but "usually good" is not the same as "correct." If booking_value gets inferred as INTEGER instead of DECIMAL, you lose precision. If booking_completed gets inferred as VARCHAR instead of BOOLEAN, your queries need different syntax. Explicit types from the data dictionary prevent silent errors.

Step 2: Before MCP

Before connecting AI to the database, test what happens without the connection. Ask AI a question about the data by describing the schema verbally:

I have a DuckDB database with a table called ab_test_data. It has columns: visitor_id, page_version, visit_date, tour_selected, booking_completed, booking_value, visitor_source. Write a SQL query to find the weekly visitor count by visitor_source for the experiment period.

AI generates a query. It might be correct -- or it might guess at column values, assume date formats, or invent columns that don't exist. The point is not whether AI gets it right or wrong. The point is that AI is working from your description, not from the actual data. Every assumption AI makes about the schema is based on what you told it, not what the database contains.

Note what AI produces. Save it. You will compare this to the same question asked after the MCP connection.

Step 3: Set up the MCP connection

MCP -- Model Context Protocol -- connects AI directly to external tools. Instead of describing your database to AI, AI reads the schema itself. Same protocol regardless of which AI coding tool you use.

Open materials/duckdb-mcp-config.json. It contains the configuration for the DuckDB MCP server. The config specifies the server name, the command to run it, and the path to your database file.

Add the MCP server to Claude Code. In your Claude Code settings, add the DuckDB MCP server configuration from the file. The exact location depends on your setup -- Claude Code's MCP settings can be in your project's .mcp.json file or in your global Claude Code configuration.

Once configured, restart Claude Code or reload the MCP servers. Claude Code should confirm the DuckDB server is connected and show the available tools: listing tables, describing schemas, running queries.

This is the first time AI has direct access to your data infrastructure. Before this moment, AI worked from descriptions. After this moment, AI reads the actual schema, checks actual column names, sees actual value distributions. The capability shift is categorical, not incremental.

Step 4: After MCP

Ask the same question you asked in Step 2, but now AI has the MCP connection:

Using the DuckDB connection, find the weekly visitor count by visitor_source for the experiment period.

Compare the two responses. AI now reads the schema directly. It knows the exact column names, data types, and available values. The query should be grounded in reality rather than inference.

The difference might be subtle (AI happened to guess the schema correctly before) or dramatic (AI hallucinated a column name or assumed wrong values). Either way, the mechanism is different. AI with database access is not doing the same thing faster -- it is doing something it categorically could not do before.

Step 5: Explore with MCP

Use the MCP-connected DuckDB to explore the data more deeply. Direct AI to examine the visitor_source distribution across the experiment period:

Query the ab_test_data table: show the weekly count and percentage of visitors by visitor_source, ordered by week. I want to see if the traffic composition changed during the experiment.

Look at the results. The visitor_source distribution should show a shift around week 3. Before week 3, paid_ad visitors make up roughly 15% of traffic. After week 3, paid_ad visitors jump to roughly 30%. Something changed in the traffic sources mid-experiment.

This is a confound. If paid ad visitors behave differently from organic visitors -- different booking rates, different tour preferences -- then the traffic shift contaminates the experiment. The test assumed the visitor population was stable across the 60 days. It wasn't.

Step 6: The ad budget confound

Ask Marco about traffic sources during the test period.

He is surprised. "Oh, the Google Ads! I doubled the budget in the third week because bookings were slow. My web developer said it wouldn't affect the test."

It does affect the test. If paid ad visitors have different booking behavior than organic visitors (they often do -- paid traffic tends to be higher-intent but less familiar with the brand), then doubling the ad budget changed the composition of visitors seeing each page version. The observed difference between page A and page B is now a mix of the page design effect and the traffic composition effect. They are confounded.

Marco discovered this because you asked. He had not connected the ad budget change to the experiment. His web developer told him it wouldn't matter. It does.

You now have two confounds: the pricing display difference (Unit 2) and the ad budget shift (this unit). The next unit accounts for both.