DE//AGENT
Explore
Tutorial Lesson 2 of 10 · The Python & SQL Series community beginner

First hands-on — a correct SELECT with a justified join

Stand up a local DuckDB through the DuckDB MCP server, inspect before you query with list_tables and describe_table, and write one SELECT with a join whose cardinality you can justify. You read CSV and Parquet straight into the engine, state the grain of the result, and finish by proving the join cannot fan out — the habit the sql-review skill demands.

At a glance

When First query against a new local dataset; Teaching a new analyst the inspect-then-query habit
Where SQL
Why The inspect-then-query habit plus a justified join is the smallest complete unit of correct SQL.

Learning objectives

  • Inspect a database with list_tables and describe_table before writing SQL
  • Write a SELECT with a join whose cardinality is stated and justified
  • Prove a join cannot fan out before writing it

What you need

  • DuckDB via the MCP server — uvx mcp-server-motherduck --db-path /de-agent/data/warehouse.duckdb
  • A CSV or Parquet file to load
  • [object Object]

Steps

  1. Run DuckDB locally through the DuckDB MCP server, which queries DuckDB databases and MotherDuck, including reading Parquet/CSV directly from object storage. (from mcp-duckdb )
  2. Inspect before you query — list_tables and describe_table, then query, are the three tools the server exposes. (from mcp-duckdb )
  3. The DuckDB documentation covers the embedded analytics SQL engine, Parquet and CSV readers, and in-process query execution. (from ref-duckdb-docs )
  4. Every join states its cardinality (1:1, N:1, N:N) and justifies it. (from skill-sql-review )
  5. Prove a join cannot fan out before writing it. (from skill-sql-review )
  6. Close by writing a grain statement for each SELECT — the artifact the SQL Reviewer emits for every result set. (from agent-sql-reviewer )

Check yourself

  • describe_table names every column and key you used in the join, and you can say which side is the many side.
  • Your join's cardinality is written down (1:1, N:1, N:N) with a one-line justification.
  • Counting rows before and after the join shows no inflation — the join cannot fan out.

Worked example

Start the DuckDB MCP server on a local database — from mcp-duckdb 

uvx mcp-server-motherduck --db-path /de-agent/data/warehouse.duckdb

Register the server with your agent — from mcp-duckdb 

mcpServers:
  duckdb:
    command: uvx
    args:
      - mcp-server-motherduck
      - --db-path
      - /de-agent/data/warehouse.duckdb

Sourced from

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

  • SELECT * hides which columns the join keys came from — name them.
  • Parquet and CSV readers mean no import step — point the query at the file.
  • A join that cannot fan out on today's data can fan out tomorrow — state the cardinality, not the current counts.

Production considerations

  • Keep the MCP server pointed at a local file (mcp-server-motherduck --db-path) so experiments never touch shared data.
  • Read the DuckDB documentation's Parquet and SQL pages — they cover what the review skill checks.

Diagram

first correct SELECT
   |
   |- start mcp-server-motherduck --> local DuckDB
   |
   |- list_tables --> describe_table --> keys and columns
   |
   |- SELECT with join --> cardinality stated (1:1, N:1, N:N)
   |
   |- row count before/after --> no inflation --> grain statement

Registry stats

Verification community · Oct 23, 2026
Completeness 81
Trust multiplier ×1.25
Composite 34.15 · adequate
Freshness 1 days since last source check
Difficulty beginner
Version 0.1.0
License CC-BY-4.0
Security flags none