TLDR; I wired up the MotherDuck MCP server to Claude Code, pointed it at a 24-object database, and got back a working interactive schema explorer without writing any code myself.

The setup

Our mca_data database on MotherDuck holds the core data assets for the environmental analysis in WECA: EPC certificates, greenhouse gas emissions, deprivation indices, housing tenure, boundary geometries, and the geography lookup tables that tie them all together. It has grown organically and contains 17 tables and 7 views across 5 thematic domains.

I always struggle to remember precisely what’s in it and what the tables and views are called. There was no ERD, no central schema reference. Just column comments and the knowledge in people’s heads.

I wanted to fix that without spending an afternoon building a documentation site.

The MotherDuck MCP server

Model Context Protocol (MCP) is Anthropic’s open standard for connecting AI assistants to external tools and data sources. MotherDuck ships an MCP server that exposes your cloud DuckDB databases directly to Claude, so there’s no copy-pasting connection strings or exporting schemas to text files.

Once the server is configured in ~/.claude/claude_desktop_config.json, Claude can call these database tools on its own:

Tool What it does
list_databases Enumerate all databases in your account
list_tables List tables and views (with comments) for a database
list_columns Get column names, types, and comments for a table
query Execute read-only SQL against MotherDuck
search_catalog Fuzzy-search across the full object catalogue

The workflow

The whole thing took one prompt and a few minutes. Compare that with the manual workflow I used previously which involved testing mermaid diagrams, draw.io and piping through LLMs.

1. One prompt

use the motherduck MCP to connect to mca_data database.
Introspect the database schema and create a playground to
help me explore the tables and views and their relationships.

Claude called list_tables once, then fanned out to 24 parallel list_columns calls, one per table and view, pulling back types, comments, and key column names in one go.

2. Relationship inference from shared key columns

With the column metadata collected, Claude matched key column names across tables to infer joins:

  • lsoa21cd / lsoa21_code connects boundary tables, IMD data, tenure data, and postcode lookups
  • ladcd / local_authority_code links the CA-to-LA hierarchy to GHG emissions tables
  • CAUTH25CD / cauthcd joins authority boundary tables to the lookup views
  • UPRN connects EPC certificate tables to the property reference dataset
  • POSTCODE threads EPC data through postcode and boundary lookups

View derivation chains were also mapped: raw_domestic_epc_certificates_tbl → epc_domestic_vw → epc_domestic_lep_vw, and so on.

3. The playground

Claude rolled all of that into a single self-contained HTML file (no external dependencies, no build step) using the playground skill. The result is embedded below.

The schema explorer

Have a poke around the mca_data schema:

Can’t see the embed? Open the full explorer in a new tab.

The playground has two main sections:

Schema Browser

  • All 24 objects listed in the left sidebar, grouped by theme (Boundaries, Geography/Lookup, EPC/Energy Performance, GHG Emissions, Deprivation/Demographics)
  • Views are distinguished from base tables with a teal badge
  • Clicking any item shows its description, column count, and join keys in the right panel
  • The Columns tab shows a filterable, searchable column list — join key columns are highlighted in gold
  • The SQL Snippet tab auto-generates a SELECT query with configurable LIMIT and a “key columns only” toggle

Relationship Diagram

  • A draggable SVG node graph with all 24 objects
  • Solid lines = join relationships, labelled with the connecting key column
  • Dashed lines = view derivation / composition relationships
  • Legend lets you toggle theme groups on/off to reduce clutter
  • Click a node to select it and sync back to the Schema Browser

Database structure

Here’s what’s in the database:

Boundaries (6 tables, 1 view)

Object Type Description
bdline_ua_lep_diss_tbl table WECA LEP dissolved boundary
bdline_ua_lep_tbl table LEP UA unitary authority boundaries
bdline_ua_weca_diss_tbl table WECA dissolved boundary
bdline_ward_lep_tbl table Ward boundaries for WECA LEP
ca_boundaries_bgc_tbl table Combined authority boundaries — all England
lsoa_2021_lep_tbl table LSOA 2021 boundaries for WECA LEP
ca_boundaries_inc_ns_vw view CA boundaries including North Somerset

Geography / Lookup (5 tables, 2 views)

Object Type Description
boundary_lookup_tbl table Postcode → OA / LSOA / MSOA / LAD hierarchy
ca_la_lookup_tbl table Combined authority → local authority
codepoint_open_lep_tbl table Codepoint Open postcodes for LEP
postcode_centroids_tbl table Full postcode centroids (60 columns)
open_uprn_lep_tbl table UPRN coordinates for WECA LEP
ca_la_lookup_inc_ns_vw view CA→LA lookup including North Somerset
weca_lep_la_vw view WECA LEP local authorities

EPC / Energy Performance (2 tables, 3 views)

Object Type Description
raw_domestic_epc_certificates_tbl table Domestic EPC register (93 columns)
raw_non_domestic_epc_certificates_tbl table Non-domestic EPC register (41 columns)
epc_domestic_vw view Domestic EPC + computed fields
epc_domestic_lep_vw view Domestic EPC filtered to WECA LEP
epc_non_domestic_lep_vw view Non-domestic EPC filtered to WECA LEP

GHG Emissions (2 tables, 1 view)

Object Type Description
la_ghg_emissions_tbl table LA GHG emissions — long/tidy format
la_ghg_emissions_wide_tbl table LA GHG emissions — wide format
ca_la_ghg_emissions_sub_sector_ods_vw view Emissions by Combined Authority

Deprivation / Demographics (2 tables)

Object Type Description
eng_lsoa_imd_tbl table IMD 2019 — deciles, ranks, scores for all domains
uk_lsoa_tenure_tbl table LSOA housing tenure breakdown

Key join patterns

If you’re querying this database, these are the joins you’ll use most often:

-- Enrich LSOA-level analysis with deprivation data
SELECT
  b.lsoa21cd,
  b.ladnm,
  imd.imd_decile,
  imd.employment_decile
FROM boundary_lookup_tbl b
JOIN eng_lsoa_imd_tbl imd ON b.lsoa21cd = imd.lsoa21_code;

-- EPC certificates with Combined Authority context
SELECT
  epc.CURRENT_ENERGY_RATING,
  epc.CONSTRUCTION_AGE_BAND,
  ca.cauthnm
FROM epc_domestic_lep_vw epc
JOIN boundary_lookup_tbl b ON epc.POSTCODE = b.pcds
JOIN ca_la_lookup_inc_ns_vw ca ON b.ladcd = ca.ladcd;

-- GHG emissions summarised by Combined Authority
SELECT
  cauthnm,
  calendar_year,
  la_ghg_sector,
  SUM(territorial_emissions_kt_co2e) AS total_kt_co2e
FROM ca_la_ghg_emissions_sub_sector_ods_vw
GROUP BY ALL
ORDER BY calendar_year, total_kt_co2e DESC;

What I learned

The MCP server made the introspection bit almost boring. The whole schema, 24 objects, roughly 300 columns, came back in a few seconds via parallel tool calls. No SHOW TABLES loops, no schema dump files to keep up to date.

I was surprised how much difference column comments made. Because the mca_data tables have decent COMMENT ON COLUMN metadata in DuckDB, Claude could figure out that lsoa21_code in eng_lsoa_imd_tbl is the same key as lsoa21cd in boundary_lookup_tbl, even though the names don’t match. Without those comments it would have been guessing.

The playground skill turned what could have been a wall of prose into something the rest of the team can actually click around. One prompt, one HTML file, no build pipeline. You can email it, commit it, or stick it on GitHub Pages.

Resources

<
Previous Post
Designing an R and Quarto Course for Analysts
>
Next Post
Building a Claude Code skill from an OpenAPI spec with mcp2cli