SQL and Messy Data With DuckDB

Use TypeScript and DuckDB to validate messy CSV exports with regional totals.

Introduction

30 Second Summary

A customer can send two files that describe the same people in different ways. Blank values, duplicate rows, or mismatched customer numbers can make a believable total wrong.

In this project, you will turn those messy files into a released SQL investigation with evidence for every decision. The finished package uses TypeScript with DuckDB to pair defensible findings with tests inside a private GitHub release.

What You'll Build

The finished investigation prints six sorted North revenue rows from January through June 2025, then proves the result against a newly generated export.

By the end of this project, you'll have:

  • A deterministic fixture generator that recreates 300 customers, 2,000 raw orders, and 24 expected regional rows for every check.
  • Five incremental SQL investigations that make every data-quality decision visible. You can trace 2,000 raw rows through 1,893 valid orders to 24 regional totals.
  • A defensible evidence pack that shows the required failed join, a zero-row injection test, fresh-export validation, and a tagged v0.1.0 release.
  • Secret Mission: Tamper with the checked-in expected file and production SQL to prove that only a real query regression defeats the fresh-export check.

Are there any prerequisites?

You need a Windows computer with internet access. No existing setup is assumed because the guide includes every installation and sign-in check.

Before We Start

Before any hands-on work begins, commit to the investigation you are about to release. This keeps every later decision focused on evidence that analysts, support leads, security reviewers, and maintainers can reproduce.

Set Up and Publish the Private Starter

A trustworthy investigation must behave the same way on another computer. Missing tools or unpinned packages would weaken every result you produce later.

This step prepares Cursor with the required runtime. You will generate deterministic fixtures before publishing the starter to a private GitHub repository.

In this step, get ready to:
  • Verify the pinned Windows toolchain.
  • Generate the deterministic CSV fixtures.
  • Publish the first commit to a private repository.
Verify the Windows toolchain

The project depends on specific versions of Node.js, Git, and GitHub CLI. Version checks prevent package or command differences from becoming investigation errors.

  • Visit the Cursor download page in your browser.
  • Download the Windows installer.
  • Run the installer.
  • Press the Windows key to open Windows search.
  • Type Cursor into the search field.
  • Press Enter to open Cursor.
  • Select Terminal from the top menu.
  • Select New Terminal.

Node.js runs the TypeScript starter. The project requires Node.js 24.22.2.

  • Check your Node.js version by running:
node --version

✔️ I see the required version

Your output shows v24.22.2. Node.js is ready for the pinned dependencies.

ⓧ I see an older version

An older runtime can reject the project's package versions. Upgrade Node.js before continuing.

  • Visit the Node.js 24.22.2 release page.
  • Download the Windows MSI installer for your computer's architecture.
  • Run the installer.
  • Return to Cursor.
  • Select Terminal from the top menu.
  • Select New Terminal.
  • Run the version check again:
node --version

What does this check prove?

The new terminal reloads your Windows path. The output should now show v24.22.2.

ⓧ Command not found

Windows cannot find Node.js yet. Install the required release before continuing.

  • Visit the Node.js 24.22.2 release page.
  • Download the Windows MSI installer for your computer's architecture.
  • Run the installer.
  • Return to Cursor.
  • Select Terminal from the top menu.
  • Select New Terminal.
  • Run the version check again:
node --version

What does this check prove?

A version result proves Windows can now find Node.js. Confirm that it shows v24.22.2.

The Node.js installer also provides npm. This package manager installs the exact dependency graph recorded by the starter.

  • Check that npm is available by running:
npm --version

You should see a numeric npm version. This confirms that the package manager is available in Cursor's terminal.

PowerShell blocked npm?

A PowerShell execution policy can block the npm script. Change the policy for this terminal session by running:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned

This setting applies only to the current terminal session. Follow your organization's Group Policy if Windows says the policy is managed.

Ask for help with the policy message: Help me understand why PowerShell blocked npm and how a process-scoped RemoteSigned policy affects this terminal.

Git records each investigation milestone. The project uses Git 2.56.0.

  • Check your Git version by running:
git --version

✔️ I see the required version

Your output includes 2.56.0. Git is ready to record the investigation history.

ⓧ I see an older version

  • Visit the Git for Windows installation page.
  • Download Git for Windows 2.56.0.
  • Run the installer.
  • Return to Cursor.
  • Open a new terminal from Terminal.
  • Run the version check again:
git --version

What does this check prove?

The output should now include 2.56.0.

ⓧ Command not found

  • Visit the Git for Windows installation page.
  • Download Git for Windows 2.56.0.
  • Run the installer.
  • Return to Cursor.
  • Open a new terminal from Terminal.
  • Run the version check again:
git --version

What does this check prove?

A version result proves Windows can find Git. Confirm that the output includes 2.56.0.

GitHub CLI connects your local Git history to GitHub. The project uses GitHub CLI 2.103.3.

  • Check your GitHub CLI version by running:
gh --version

✔️ I see the required version

Your output includes 2.103.3. GitHub CLI is ready.

ⓧ I see an older version

  • Visit the GitHub CLI installation page.
  • Choose the Windows installation option.
  • Install GitHub CLI 2.103.3.
  • Return to Cursor.
  • Open a new terminal from Terminal.
  • Run the version check again:
gh --version

What does this check prove?

The output should now include 2.103.3.

ⓧ Command not found

  • Visit the GitHub CLI installation page.
  • Choose the Windows installation option.
  • Install GitHub CLI 2.103.3.
  • Return to Cursor.
  • Open a new terminal from Terminal.
  • Run the version check again:
gh --version

What does this check prove?

A version result proves Windows can find GitHub CLI. Confirm that it includes 2.103.3.

Authentication lets GitHub CLI create the private remote under your account. The status check reveals whether this computer already has a valid session.

  • Check your authentication state by running:
gh auth status

✔️ I am authenticated

The status confirms an authenticated GitHub account. Your local project can publish through GitHub CLI.

ⓧ I need to sign in

The sign-in flow may open your browser. Follow the displayed authentication steps for your GitHub account.

  • Start the authentication flow by running:
gh auth login

What does this command do?

GitHub CLI guides you through a one-time authentication flow. It stores the resulting session for later repository commands.

  • Complete the browser authentication flow.
  • Return to Cursor's terminal.
  • Confirm the new session by running:
gh auth status

What should you see?

The status now confirms an authenticated GitHub account.

Create the starter and generate fixtures

The starter pins TypeScript 7.0.2 with tsx 4.23.15. It also pins the DuckDB Node API at 1.5.6-r.2 so the database runs inside the program's memory.

  • Move to your Desktop by running:
Set-Location -Path ~/Desktop -PassThru
  • Create the sql-and-messy-data folder by running:
New-Item -Path . -Name "sql-and-messy-data" -ItemType "Directory"

PowerShell prints the new directory entry. The folder now has a predictable location on your Desktop.

  • Move into the new folder by running:
Set-Location -Path ./sql-and-messy-data -PassThru
  • Select File from Cursor's top menu.
  • Select Open Folder.
  • Choose the sql-and-messy-data folder from your Desktop.
  • Confirm the folder selection.
  • Open a new terminal from Terminal.
  • Save every supplied starter file under its exact path.

What is in the starter?

The runtime files generate fixtures. They also load the CSV files into an in-memory DuckDB database.

The documentation files define requirements and tests. docs/decisions.md keeps ADR-002 unfinished until you resolve the join mismatch in Step 4.

✔️ I saved the starter files

The starter is ready for dependency installation. Confirm that package.json and make-data.ts are visible in Cursor's file sidebar.

  • Save all open starter files.

ⓧ I want to double check the full code

Compare your core configuration and runtime files with these complete references. Keep the supplied documentation templates under their exact paths.

{
  "name": "sql-and-messy-data",
  "version": "0.1.0",
  "private": true,
  "type": "module",
  "scripts": {
    "typecheck": "tsc --noEmit",
    "data": "tsx make-data.ts",
    "explore": "tsx explore.ts",
    "check": "tsx check.ts",
    "mustfail": "tsx mustfail.ts"
  },
  "dependencies": {
    "@duckdb/node-api": "1.5.6-r.2"
  },
  "devDependencies": {
    "@types/node": "24.19.2",
    "tsx": "4.23.15",
    "typescript": "7.0.2"
  }
}

Why this file matters

The package file pins every dependency. Its scripts provide one stable command for each project operation.

{
  "compilerOptions": {
    "target": "ES2024",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "strict": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "verbatimModuleSyntax": true,
    "allowImportingTsExtensions": true,
    "noEmit": true,
    "types": ["node"]
  },
  "include": ["*.ts"]
}

Why this file matters

This configuration matches Node.js 24 with ES2024 and NodeNext. Strict checks surface unsafe TypeScript assumptions before execution.

node_modules/
.check-*/
*.duckdb
.DS_Store

Why this file matters

Git ignores installed packages and temporary check directories. It also ignores local DuckDB files and macOS metadata.

import { mkdir, writeFile } from "node:fs/promises";
import { join } from "node:path";

type Order = {
  order_id: number;
  customer_id: string;
  order_date: string;
  amount: string;
};

function mulberry32(seed: number) {
  return () => {
    let value = (seed += 0x6d2b79f5);
    value = Math.imul(value ^ (value >>> 15), value | 1);
    value ^= value + Math.imul(value ^ (value >>> 7), value | 61);
    return ((value ^ (value >>> 14)) >>> 0) / 4294967296;
  };
}

function csvCell(value: string | number) {
  const text = String(value);
  return /[",\n]/.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
}

function toCsv(headers: string[], rows: Array<Array<string | number>>) {
  return [headers, ...rows]
    .map((row) => row.map(csvCell).join(","))
    .join("\n") + "\n";
}

function formatDate(date: Date, slash: boolean) {
  const year = date.getUTCFullYear();
  const month = String(date.getUTCMonth() + 1).padStart(2, "0");
  const day = String(date.getUTCDate()).padStart(2, "0");
  return slash ? `${month}/${day}/${year}` : `${year}-${month}-${day}`;
}

function cents(text: string) {
  const [whole = "0", fraction = "00"] = text.split(".");
  return Number(whole) * 100 + Number(fraction.padEnd(2, "0").slice(0, 2));
}

function money(value: number) {
  return `${Math.floor(value / 100)}.${String(value % 100).padStart(2, "0")}`;
}

const random = mulberry32(20250308);
const regions = ["North", "South", "East", "West"] as const;
const customerRegion = new Map<number, string>();
const customers: Array<Array<string | number>> = [];

for (let id = 1; id <= 300; id += 1) {
  const region = regions[id % regions.length]!;
  customerRegion.set(id, region);
  customers.push([
    `C${String(id).padStart(5, "0")}`,
    `Customer ${String(id).padStart(3, "0")}`,
    region
  ]);
}

const orders: Order[] = [];
const firstDay = Date.UTC(2025, 0, 1);

for (let orderId = 1; orderId <= 1960; orderId += 1) {
  const customerId = Math.floor(random() * 300) + 1;
  const dayOffset = Math.floor(random() * 181);
  const date = new Date(firstDay + dayOffset * 86_400_000);
  const randomAmount = (5 + random() * 495).toFixed(2);
  orders.push({
    order_id: orderId,
    customer_id: orderId % 50 === 0 ? "" : String(customerId),
    order_date: formatDate(date, orderId % 2 === 0),
    amount: orderId % 70 === 7 ? "" : randomAmount
  });
}

for (let index = 0; index < 40; index += 1) {
  orders.push({ ...orders[index * 49]! });
}

const uniqueValid = [
  ...new Map(
    orders
      .filter((order) => order.customer_id !== "" && order.amount !== "")
      .map((order) => [JSON.stringify(order), order])
  ).values()
];

const totals = new Map<string, { region: string; month: string; count: number; cents: number }>();
for (const order of uniqueValid) {
  const customerId = Number(order.customer_id);
  const region = customerRegion.get(customerId)!;
  const month = order.order_date.includes("-")
    ? order.order_date.slice(0, 7)
    : `2025-${order.order_date.slice(0, 2)}`;
  const key = `${month}|${region}`;
  const total = totals.get(key) ?? { region, month, count: 0, cents: 0 };
  total.count += 1;
  total.cents += cents(order.amount);
  totals.set(key, total);
}

const expected = [...totals.values()].sort(
  (left, right) => left.month.localeCompare(right.month) || left.region.localeCompare(right.region)
);

await mkdir("sample-data", { recursive: true });
await writeFile(
  join("sample-data", "customers.csv"),
  toCsv(["customer_id", "customer_name", "region"], customers)
);
await writeFile(
  join("sample-data", "orders.csv"),
  toCsv(
    ["order_id", "customer_id", "order_date", "amount"],
    orders.map((order) => [order.order_id, order.customer_id, order.order_date, order.amount])
  )
);
await writeFile(
  join("sample-data", "expected.csv"),
  toCsv(
    ["region", "month", "order_count", "revenue"],
    expected.map((row) => [row.region, row.month, row.count, money(row.cents)])
  )
);

console.log(`customers.csv rows: ${customers.length}`);
console.log(`orders.csv rows: ${orders.length}`);
console.log(`expected.csv rows: ${expected.length}`);

Why this file matters

The fixed seed makes every generated export repeatable. The generator deliberately includes blanks and exact duplicates for later investigation.

import { DuckDBInstance } from "@duckdb/node-api";

export async function openDb() {
  const instance = await DuckDBInstance.create(":memory:");
  const connection = await instance.connect();

  await connection.run(`
    CREATE TABLE orders_raw AS
    SELECT * FROM read_csv(
      'sample-data/orders.csv',
      header = true,
      columns = {
        'order_id': 'BIGINT',
        'customer_id': 'BIGINT',
        'order_date': 'VARCHAR',
        'amount': 'VARCHAR'
      }
    );
  `);

  await connection.run(`
    CREATE TABLE customers_raw AS
    SELECT * FROM read_csv(
      'sample-data/customers.csv',
      header = true,
      columns = {
        'customer_id': 'VARCHAR',
        'customer_name': 'VARCHAR',
        'region': 'VARCHAR'
      }
    );
  `);

  return { connection, instance };
}

Why this file matters

The database exists only in memory. Its raw tables preserve the conflicting customer identifier types that the investigation must expose.

import { readFile } from "node:fs/promises";
import { openDb } from "./db.ts";

function csvCell(value: unknown) {
  const text = value === null || value === undefined ? "" : String(value);
  return /[",\n]/.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
}

const region = process.argv[2] ?? null;
const sql = await readFile(new URL("./sql/05_region.sql", import.meta.url), "utf8");
const { connection, instance } = await openDb();

try {
  const reader = await connection.runAndReadAll(sql, { region });
  const rows = reader.getRowObjectsJson();
  console.log("region,month,order_count,revenue");
  for (const row of rows) {
    console.log([row.region, row.month, row.order_count, row.revenue].map(csvCell).join(","));
  }
  console.log(`rows: ${rows.length}`);
} finally {
  connection.disconnectSync();
  instance.closeSync();
}

Why this file matters

This is the production entry point for the final regional view. It binds the region separately from the SQL text.

import { spawnSync } from "node:child_process";
import { mkdtemp, readFile, rm } from "node:fs/promises";
import { dirname, join } from "node:path";
import { fileURLToPath } from "node:url";
import { openDb } from "./db.ts";

const projectRoot = dirname(fileURLToPath(import.meta.url));

function expect(condition: boolean, message: string): asserts condition {
  if (!condition) throw new Error(message);
}

function stripSemicolon(sql: string) {
  return sql.trim().replace(/;+\s*$/, "");
}

function runTypeScript(script: string, cwd: string, args: string[] = []) {
  const result = spawnSync(
    process.execPath,
    ["--import", "tsx", join(projectRoot, script), ...args],
    { cwd, encoding: "utf8" }
  );
  if (result.status !== 0) {
    throw new Error(result.stderr || result.stdout || `${script} failed`);
  }
  return result.stdout.trim();
}

function csvCell(value: unknown) {
  const text = value === null || value === undefined ? "" : String(value);
  return /[",\n]/.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
}

function printRows(rows: Array<Record<string, unknown>>) {
  const headers = Object.keys(rows[0] ?? {});
  console.log(headers.join(","));
  for (const row of rows) {
    console.log(headers.map((header) => csvCell(row[header])).join(","));
  }
  console.log(`rows: ${rows.length}`);
}

async function focusedCheck(requestedPath: string) {
  const sql = stripSemicolon(await readFile(join(projectRoot, requestedPath), "utf8"));
  const name = requestedPath.replaceAll("\\", "/").split("/").at(-1);
  const { connection, instance } = await openDb();
  try {
    let query = sql;
    let values: Record<string, string | null> | undefined;
    if (name === "02_clean.sql") {
      query = `SELECT count(*) AS clean_rows FROM (${sql}) result`;
    } else if (name === "03_dates.sql") {
      query = `SELECT count(*) AS dated_rows, count(*) FILTER (WHERE order_date IS NULL) AS invalid_dates, min(order_date) AS first_date, max(order_date) AS last_date FROM (${sql}) result`;
    } else if (name === "04_revenue.sql") {
      query = `SELECT count(*) AS customers, sum(order_count) AS orders, sum(revenue) AS revenue FROM (${sql}) result`;
    } else if (name === "05_region.sql") {
      values = { region: "North" };
    }
    const reader = values
      ? await connection.runAndReadAll(query, values)
      : await connection.runAndReadAll(query);
    printRows(reader.getRowObjectsJson() as Array<Record<string, unknown>>);
  } finally {
    connection.disconnectSync();
    instance.closeSync();
  }
}

async function fullCheck() {
  const previousCwd = process.cwd();
  const tempDir = await mkdtemp(join(projectRoot, ".check-"));
  let connection: Awaited<ReturnType<typeof openDb>>["connection"] | undefined;
  let instance: Awaited<ReturnType<typeof openDb>>["instance"] | undefined;
  try {
    runTypeScript("make-data.ts", tempDir);
    process.chdir(tempDir);
    ({ connection, instance } = await openDb());

    const sql = async (name: string) =>
      stripSemicolon(await readFile(join(projectRoot, "sql", name), "utf8"));

    const profileReader = await connection.runAndReadAll(await sql("01_profile.sql"));
    const profile = profileReader.getRowObjectsJson()[0];
    expect(profile !== undefined, "profile returned no row");
    expect(String(profile.row_count) === "2000", "row_count must be 2000");
    expect(String(profile.blank_customer_ids) === "40", "blank_customer_ids must be 40");
    expect(String(profile.blank_amounts) === "28", "blank_amounts must be 28");
    expect(String(profile.exact_duplicates) === "40", "exact_duplicates must be 40");

    const clean = await sql("02_clean.sql");
    const cleanReader = await connection.runAndReadAll(
      `SELECT count(*) AS n FROM (${clean}) result`
    );
    expect(String(cleanReader.getRowObjectsJson()[0]?.n) === "1893", "clean rows must be 1893");

    const dates = await sql("03_dates.sql");
    const dateReader = await connection.runAndReadAll(
      `SELECT count(*) AS n, count(*) FILTER (WHERE order_date IS NULL) AS invalid FROM (${dates}) result`
    );
    expect(String(dateReader.getRowObjectsJson()[0]?.n) === "1893", "dated rows must be 1893");
    expect(String(dateReader.getRowObjectsJson()[0]?.invalid) === "0", "invalid dates must be 0");

    const revenue = await sql("04_revenue.sql");
    const revenueReader = await connection.runAndReadAll(
      `SELECT sum(order_count) AS n FROM (${revenue}) result`
    );
    expect(String(revenueReader.getRowObjectsJson()[0]?.n) === "1893", "joined orders must be 1893");

    const region = await sql("05_region.sql");
    const regionReader = await connection.runAndReadAll(region, { region: null });
    expect(regionReader.getRowObjectsJson().length === 24, "regional output must have 24 rows");

    connection.disconnectSync();
    instance.closeSync();
    connection = undefined;
    instance = undefined;

    const allOutput = runTypeScript("explore.ts", tempDir);
    const actualCsv = allOutput
      .split(/\r?\n/)
      .filter((line) => !line.startsWith("rows:"))
      .join("\n")
      .trim();
    const expectedCsv = (await readFile(join(tempDir, "sample-data", "expected.csv"), "utf8")).trim();
    expect(actualCsv === expectedCsv, "explore.ts output must match fresh expected.csv");

    const injection = runTypeScript("explore.ts", tempDir, ["North' OR '1'='1"]);
    expect(injection.endsWith("rows: 0"), "injection-shaped input must return 0 rows");

    console.log("AC-2 PASS: five SQL files matched fresh exports (2000/40/28/40, 1893, 24).");
  } finally {
    connection?.disconnectSync();
    instance?.closeSync();
    process.chdir(previousCwd);
    await rm(tempDir, { recursive: true, force: true });
  }
}

const requestedPath = process.argv[2];
if (requestedPath) {
  await focusedCheck(requestedPath);
} else {
  await fullCheck();
}

Why this file matters

The full check creates a fresh temporary export. It tests the production entry point before removing the temporary files.

import { openDb } from "./db.ts";

const { connection, instance } = await openDb();
try {
  try {
    await connection.run(`
      SELECT count(*)
      FROM orders_raw AS orders
      JOIN customers_raw AS customers
        ON orders.customer_id = customers.customer_id;
    `);
    throw new Error("The uncast join unexpectedly succeeded.");
  } catch (error) {
    const message = error instanceof Error ? error.message : String(error);
    if (!message.includes("Conversion Error")) throw error;
    console.log("AC-3 PASS: DuckDB rejected the uncast BIGINT-to-VARCHAR join.");
  }
} finally {
  connection.disconnectSync();
  instance.closeSync();
}

Why this file matters

This harness preserves the required failed join. It passes only when DuckDB rejects the mismatched raw identifier types.

# Requirements

## User stories

1. As an analyst, I need monthly regional order counts and revenue so I can explain first-half performance.
2. As a support lead, I need blank and duplicate counts so I can report export quality without silently repairing it.
3. As a security reviewer, I need the region value bound separately from SQL so injection-shaped input is treated as data.
4. As a forward-deployed engineer, I need a failed join test so the customer ID mismatch is visible before the fix.
5. As a maintainer, I need checks against a fresh export so a stale expected file cannot approve broken SQL.

## Acceptance criteria

- AC-1: North prints six monthly rows.
- AC-2: The fresh-export check passes all five SQL files.
- AC-3: The uncast join fails and the harness reports the expected failure.
- AC-4: Injection-shaped region input prints zero rows.
- AC-5: Three README findings cite numbers visible in AC-1.
- AC-6: The repository is private, issue 1 is closed, and pull request 2 is merged.
- AC-7: The repository has at least seven commits and the README contains an `OS:` line.

Why this file matters

The requirements connect each technical check to a stakeholder. The acceptance criteria define the evidence needed for delivery.

# SQL Questions

1. What are the raw row count, blank customer ID count, blank amount count, and exact duplicate count?
2. How many unique orders remain after blank customer IDs and amounts are dropped and amount becomes `DECIMAL(18,2)`?
3. How can both supplied date formats become one `DATE` without throwing on the format that does not match?
4. Which side of the customer join should be cast, and what customer-level order counts and revenue result?
5. What are order count and revenue by region and month when region is supplied as a bound parameter?

Why this file matters

These five questions set the order of the SQL investigation. Each later query answers one question.

# Test Plan

Run every command in Cursor's terminal and capture the command with its output.

## AC-1: North happy path

```powershell
npx tsx explore.ts North
```

Expected final line:

```text
rows: 6
```

## AC-2: Fresh-export regression check

```powershell
npx tsx check.ts
```

Expected line:

```text
AC-2 PASS: five SQL files matched fresh exports (2000/40/28/40, 1893, 24).
```

## AC-3: Required failure

```powershell
npx tsx mustfail.ts
```

Expected line:

```text
AC-3 PASS: DuckDB rejected the uncast BIGINT-to-VARCHAR join.
```

## AC-4: Injection-shaped input

```powershell
npx tsx explore.ts "North' OR '1'='1"
```

Expected final line:

```text
rows: 0
```

## AC-5: Findings

Each of the three README findings must contain a number visible in the AC-1 output.

## AC-6: GitHub close-out

```powershell
gh repo view --json visibility
```

```text
{"visibility":"PRIVATE"}
```

```powershell
gh issue view 1 --json state
```

```text
{"state":"CLOSED"}
```

```powershell
gh pr list --state merged
```

Expected: the list includes pull request 2.

## AC-7: History and environment

```powershell
git log --oneline
```

Expected: at least seven commits. Confirm README contains a line starting `OS:`.

Why this file matters

The test plan names every acceptance command and success signal. Later screenshots must show commands running in Cursor's terminal.

# Architecture

```mermaid
flowchart LR
  M[make-data.ts] --> C[sample-data/customers.csv]
  M --> O[sample-data/orders.csv]
  M --> E[sample-data/expected.csv]
  D[db.ts] --> C
  D --> O
  Q1[sql/01_profile.sql] --> D
  Q2[sql/02_clean.sql] --> D
  Q3[sql/03_dates.sql] --> D
  Q4[sql/04_revenue.sql] --> D
  Q5[sql/05_region.sql] --> D
  X[explore.ts] --> Q5
  T[check.ts] --> M
  T --> X
  T --> E
  F[mustfail.ts] --> D
  R[README.md] --> T
  P[docs/test-plan.md] --> R
  A[docs/requirements.md] --> P
  S[docs/sql-questions.md] --> Q1
  S --> Q2
  S --> Q3
  S --> Q4
  S --> Q5
  ADR[docs/decisions.md] --> Q4
  TALK[talk-track.txt] --> R
```

## Limits

- Blanks are dropped, not repaired.
- Two date formats are supported.
- Nothing persists because DuckDB runs in-process and in memory.
- One operating system is tested: Windows.

## Source map

- DuckDB 1.5 CSV import: https://duckdb.org/docs/1.5/data/csv/overview
- DuckDB 1.5 prepared statements: https://duckdb.org/docs/1.5/sql/query_syntax/prepared_statements
- DuckDB Node API release: https://github.com/duckdb/duckdb-node-neo/releases
- TypeScript 7 announcement: https://devblogs.microsoft.com/typescript/announcing-typescript-7-0/
- Node.js 24 release: https://nodejs.org/en/blog/release/v24.22.2
- GitHub pull requests: https://docs.github.com/en/pull-requests/how-tos/create-pull-requests/creating-a-pull-request
```

Why this file matters

The diagram shows how generation feeds the database and checks. Its limits make the investigation boundaries visible.

# TypeScript for the Job, Part Two: SQL and Messy Data With DuckDB

A forward-deployed engineering investigation of a messy customer order export.

OS: Windows

## Run

```powershell
npm install
```

```powershell
npx tsx make-data.ts
```

```powershell
npx tsx explore.ts North
```

```powershell
npx tsx check.ts
```

```powershell
npx tsx mustfail.ts
```

## Three findings

1. Analyst: In `YYYY-MM`, North produced `NUMBER` orders and `NUMBER` in revenue.
2. Support lead: North's highest-volume month was `YYYY-MM` with `NUMBER` orders.
3. Security reviewer: The bound North query returned `6` monthly rows, while the injection-shaped input returned `0` rows.

## Validation outputs

- AC-1: Replace with the six North result lines and `rows: 6`.
- AC-2: Replace with the AC-2 PASS line.
- AC-3: Replace with the AC-3 PASS line.
- AC-4: Replace with `rows: 0`.
- AC-5: Confirm all three findings contain numbers from AC-1.
- AC-6: Replace with private, closed-issue, and merged-PR output.
- AC-7: Replace with at least seven one-line commits.

## Screenshot evidence

1. Toolchain: capture `node --version` and its output in Cursor's terminal.
2. Fixtures: capture `npx tsx make-data.ts` and its output.
3. Happy path: capture `npx tsx explore.ts North` and its output.
4. Fresh check: capture `npx tsx check.ts` and its output.
5. Required failure: capture `npx tsx mustfail.ts` and its output.
6. Injection test: capture `npx tsx explore.ts "North' OR '1'='1"` and its output.
7. Close-out: capture `git log --oneline` after AC-6 and AC-7 are complete.

## Limits

Blanks are dropped, two date formats are supported, DuckDB data does not persist, and Windows is the tested OS.

Why this file matters

The README reserves places for commands and evidence. It also records Windows as the tested operating system.

Dependency installation uses the pins in package.json. It also generates package-lock.json with the exact dependency tree.

  • Install the project dependencies by running:
npm install

The first installation can take a few minutes while npm downloads the packages. A completed summary returns control to the terminal.

Dependency installation failed?

Confirm that package.json is inside the open sql-and-messy-data folder. Confirm that Node.js still reports v24.22.2.

Ask for help with the installation output: Help me diagnose this npm install failure using the error output and my package.json pins.

The fixture generator uses the same seed on every run. Repeatability gives analysts and reviewers the same messy evidence.

  • Generate the three CSV fixtures by running:
npx tsx make-data.ts

You should see customers.csv rows: 300. The next lines should show orders.csv rows: 2000 and expected.csv rows: 24.

What do these counts prove?

The 300 customer rows provide the reference data. The 2,000 order rows include the deliberate mess that later SQL must measure.

The 24 expected rows represent four regions across six months. A fixed seed makes those totals reproducible.

Row counts do not match?

Compare make-data.ts with the full reference. Confirm that the seed remains 20250308.

Ask for help comparing your generator: Help me find why my generated customer, order, or expected row count differs from the required result.

Commit and publish the private repository

A local snapshot makes the generated fixtures and lockfile reproducible. The private remote gives the investigation a controlled delivery history.

  • Initialize Git inside sql-and-messy-data by running:
git init

Git confirms that it initialized a repository in the open folder.

  • Set the primary branch name by running:
git branch -M main
  • Stage the starter files by running:
git add .
  • Create the first commit by running:
git commit -m "Files and the lockfile"

Git prints the new commit summary. It lists the starter files recorded in the first snapshot.

Git asked for your identity?

Git needs an author name and email before it can create the commit. Follow the official Git identity guide with the name and email supplied for this project.

Ask for help with the identity message: Help me configure my Git author identity safely before retrying this commit.

Repository creation publishes the current branch immediately. The private flag limits access to accounts you explicitly authorize.

  • Create the private repository by running:
gh repo create sql-and-messy-data --private --source=. --push

GitHub CLI creates sql-and-messy-data and pushes the first commit. The local main branch now tracks the private remote.

Repository creation failed?

Confirm that GitHub CLI still reports an authenticated account. Confirm that the repository name is not already in use under that account.

Ask for help with the command output: Help me diagnose why GitHub CLI could not create or push my private repository.

Before the final check, what visibility do you expect GitHub to report for the new repository?

  • Verify the repository visibility by running:
gh repo view --json visibility

You should see {"visibility":"PRIVATE"}. This proves that the starter was pushed without making the investigation public.

That is the foundation in place. Your deterministic fixtures and private first commit are now reproducible from one repository.

Your private starter is published. Next, you will turn the messy export into measurable requirements and write the first profiling query.

Design the Investigation and Profile the Mess

Your private starter now recreates the same messy export on demand. The next job is to turn that export into a tracked investigation.

Before you clean any row, data profiling measures the defects that the customer needs explained. A tracked issue in GitHub ties those measurements to explicit requirements.

In this step, get ready to:
  • Connect the five project documents to a tracked customer outcome.
  • Create a private board that shows issue 1 in progress.
  • Profile the raw order export with the first committed SQL query.
Review the investigation and create issue 1

The five documents in docs/ define the investigation before implementation begins. Together, they connect the customer problem to the query sequence and its tests.

  • In the Explorer sidebar in Cursor, read docs/requirements.md to identify the user stories and acceptance criteria.
  • Read docs/sql-questions.md to identify the five questions that shape the SQL investigation.
  • Read docs/architecture.md to trace how the fixtures reach the query runners and checks.
  • Read docs/decisions.md to identify the current data policies and reversal triggers.
  • Read docs/test-plan.md to identify the evidence required by AC-1 through AC-7.

How do the documents fit together?

  • The requirements describe what the analyst, support lead, security reviewer, and maintainer need to trust.
  • The SQL questions divide that customer problem into five measurable investigations.
  • The architecture shows where each query fits in the runnable package.
  • The decisions and test plan record the policies that make the result defensible.

An issue gives the investigation one tracked customer outcome. Its body comes directly from docs/requirements.md so the implementation stays tied to the acceptance criteria.

  • Create issue 1 from the requirements document by running this command:
gh issue create --title "SQL on a messy export: clean, join, prove" --body-file docs/requirements.md

What does this command do?

The command creates an issue titled SQL on a messy export: clean, join, prove. The --body-file flag copies the complete requirements document into the issue body.

This repository has no earlier issue or pull request. GitHub therefore assigns this issue the number 1.

You'll see a URL for the newly created issue. The issue now carries the customer stories and AC-1 through AC-7.

Issue creation did not finish?

  • Switch back to the Cursor terminal inside the sql-and-messy-data folder.
  • Confirm that GitHub CLI authentication from the previous step remains active.
  • Confirm that docs/requirements.md appears in the Explorer sidebar.

Use this prompt for help: Help me diagnose why my GitHub CLI issue creation command failed.

Create the private project board

A board in GitHub Projects makes the investigation status visible beside the repository. The board starts with issue 1 as the active customer outcome.

  • In your web browser, return to GitHub.
  • Click your profile picture in the top-right corner.
  • Click Your profile.
  • Click Projects.
  • Click New project.
  • Select Board as the layout.
  • Enter sql-and-messy-data in the project name field.
  • Click Create project.

You'll see the new project as a board with status columns. This layout gives issue 1 a visible path from planned work to completed evidence.

  • Open the project's Settings.
  • Set the project visibility to Private.
  • Return to the sql-and-messy-data repository page from earlier.
  • Select the repository's Projects section.
  • Click Link a project.
  • Select the sql-and-messy-data project.
  • Return to the sql-and-messy-data project board.

The repository and project are now linked. That connection keeps the engineering work beside the issue that explains why it exists.

  • Click Add item from repository in the bottom row of the board.
  • Select the sql-and-messy-data repository.
  • Select issue 1 titled SQL on a messy export: clean, join, prove.
  • Click Add selected items.
  • Drag the issue 1 card to In progress.

The customer outcome is now visible in In progress. You have a private planning trail before changing any data.

Issue 1 is missing from the board?

  • Confirm that the board is linked to the sql-and-messy-data repository.
  • Select Add item from repository again.
  • Search for part of the issue title if issue 1 is not visible immediately.

Use this prompt for help: Help me add issue 1 to my GitHub Project board.

Write and verify the profile query

The first SQL query measures the raw orders_raw table inside DuckDB. It preserves the mess while counting blank keys, blank amounts, and exact duplicates.

  • Click the New Folder control beside the sql-and-messy-data folder in the Explorer sidebar.
  • Submit sql as the folder name.

You'll see a new sql folder inside sql-and-messy-data.

  • Create a file inside sql with the Explorer's New File control.
  • Submit 01_profile.sql as the file name.

The Explorer now shows sql/01_profile.sql as the first investigation file.

  • In sql/01_profile.sql, paste the profile query below:
WITH deduplicated AS (
  SELECT DISTINCT *
  FROM orders_raw
)
SELECT
  (SELECT count(*) FROM orders_raw) AS row_count,
  (SELECT count(*) FROM orders_raw WHERE customer_id IS NULL) AS blank_customer_ids,
  (SELECT count(*) FROM orders_raw WHERE amount IS NULL OR trim(amount) = '') AS blank_amounts,
  (SELECT count(*) FROM orders_raw) - (SELECT count(*) FROM deduplicated) AS exact_duplicates;

What does this query do?

  • The common table expression named deduplicated creates a distinct comparison set without changing orders_raw.
  • The row_count subquery counts every raw order row.
  • The blank checks count missing customer IDs and unusable amount values.
  • The final subtraction calculates how many raw rows disappear under DISTINCT.
  • Save sql/01_profile.sql.

Before you run the profile, do you expect its four measurements to match the requirements exactly?

  • Run the focused profile check in Cursor's terminal by running this command:
npx tsx check.ts sql/01_profile.sql

What does this command do?

The command runs check.ts with sql/01_profile.sql as the focused query. The existing harness opens the in-memory database and prints the returned row.

You'll see one result row containing 2000, 40, 28, and 40.

The final line is rows: 1. That is the first hard proof complete: the raw export's defects are visible before any cleanup.

Profile check did not return the four counts?

  • Confirm that 01_profile.sql sits inside the sql folder.
  • Confirm that the file contains orders_raw in every raw-table reference.
  • Confirm that you saved the file before running the check.

Use this prompt for help: Help me debug my SQL profile counts.

✔️ Awesome, I've got everything!

Great. Double-check that you saved sql/01_profile.sql.

ⓧ I'd like to double check the full code

WITH deduplicated AS (
  SELECT DISTINCT *
  FROM orders_raw
)
SELECT
  (SELECT count(*) FROM orders_raw) AS row_count,
  (SELECT count(*) FROM orders_raw WHERE customer_id IS NULL) AS blank_customer_ids,
  (SELECT count(*) FROM orders_raw WHERE amount IS NULL OR trim(amount) = '') AS blank_amounts,
  (SELECT count(*) FROM orders_raw) - (SELECT count(*) FROM deduplicated) AS exact_duplicates;

The four measurements now match the acceptance criteria. The query can move into source control as the first investigation result.

  • Return to docs/requirements.md from earlier.
  • Compare the profile result with the four required raw-export counts.
  • Stage the sql folder and create the profile commit by running:
git add sql
git commit -m "Step 1: profiled the export"

What does this commit capture?

The first command stages the new sql folder. The second command records the profile query with the message Step 1: profiled the export.

You'll see Git confirm a new commit containing sql/01_profile.sql.

  • Send the profile commit to the private remote by running this command:
git push

What does this command do?

The command sends the new commit from your local main branch to its existing private remote. The repository now carries the profile query beside the starter.

You'll see Git confirm that the remote branch is up to date with your local commit.

Commit or push did not finish?

  • Confirm that the terminal remains inside the sql-and-messy-data folder.
  • Reuse the Git identity configuration from the previous step if Git requests your identity.
  • Confirm that your GitHub CLI authentication remains active if the remote rejects the push.

Use this prompt for help: Help me diagnose my profile commit or push failure.

Before you rerun the final check, do you expect committing the query to change its four profile measurements?

  • Verify the committed profile query by running this command again:
npx tsx check.ts sql/01_profile.sql

What does this final check prove?

The focused check executes the committed query against the deterministic raw export. A stable result proves that source control preserved the investigation you tested.

You'll see one result row containing 2000, 40, 28, and 40.

The output ends with rows: 1. Your issue, board, query, local history, and private remote now agree on the first piece of evidence.

Final profile result changed?

  • Compare sql/01_profile.sql with the full-code tab above.
  • Regenerate the deterministic fixtures only if the sample-data files were edited after setup.
  • Rerun the focused check after saving the corrected query.

Use this prompt for help: Help me explain why my deterministic profile result changed.

You have made the messy export measurable and tied the result to tracked customer requirements. Next, you'll clean the unusable rows while preserving evidence of the broken customer join.

Clean Orders and Expose the Broken Join

Your committed SQL profile turned the messy export into measurable evidence. The four counts expose raw rows, blank customer IDs, blank amounts, and exact duplicates.

Those counts cannot support regional analysis until the unusable rows are removed. This step applies the approved cleaning policy before using DuckDB to expose the unchanged customer key mismatch.

In this step, get ready to:
  • Remove unusable orders under the approved null policy.
  • Normalize both supplied date formats into one date type.
  • Run the unchanged failure harness to expose mismatched customer keys.
Clean the orders

A null policy defines how incomplete records affect an analysis. The approved policy drops orders with no usable customer ID or amount.

  • Create sql/02_clean.sql inside the existing sql folder with this code:
SELECT DISTINCT
  order_id,
  customer_id,
  order_date,
  CAST(amount AS DECIMAL(18,2)) AS amount
FROM orders_raw
WHERE customer_id IS NOT NULL
  AND nullif(trim(amount), '') IS NOT NULL;

What does this query do?

  • The SELECT DISTINCT clause keeps one copy of each exact order row.
  • The customer condition removes orders with no usable customer key.
  • The amount condition treats empty text as a missing value.
  • The DECIMAL(18,2) cast gives money a fixed scale of two decimal places.
  • Save sql/02_clean.sql.

Before you check the query, how many unique valid orders do you expect to remain after all three rules apply?

  • Count the cleaned orders by running this command in Cursor's terminal:
npx tsx check.ts sql/02_clean.sql

What does this check do?

The focused check runs sql/02_clean.sql against the generated export. It counts the rows returned by your cleaning policy.

You will see clean_rows with a value of 1893. The final line shows rows: 1 because the check returns one summary row.

Clean count differs from 1893?

Confirm that SELECT DISTINCT applies to all four selected columns. Check both conditions in the WHERE clause.

Make sure the amount cast uses DECIMAL(18,2).

Help me diagnose why my cleaned order count differs from 1893.

✔️ Awesome, I've got everything!

Your cleaning query now returns exactly 1,893 unique valid orders. Make sure sql/02_clean.sql is saved.

ⓧ I'd like to double check the full code

SELECT DISTINCT
  order_id,
  customer_id,
  order_date,
  CAST(amount AS DECIMAL(18,2)) AS amount
FROM orders_raw
WHERE customer_id IS NOT NULL
  AND nullif(trim(amount), '') IS NOT NULL;
  • Stage the SQL changes by running this command:
git add sql

What does staging do?

This stages the current changes inside the sql folder. The next commit records that exact SQL state.

  • Record the cleaning result by running this command:
git commit -m "Step 2: cleaned 1893 orders"

What does this commit capture?

This commit connects the cleaning policy to its measured result. Git prints a summary showing that the new SQL file was recorded.

Strong start. Your approved cleaning policy now produces 1,893 reproducible orders.

Commit did not complete?

Confirm that the focused check succeeded before committing. Make sure sql/02_clean.sql is saved inside the existing sql folder.

Help me diagnose why the cleaning commit did not complete.

Normalize both date formats

Date normalization converts different text formats into one comparable type. Tolerant parsing lets each row try both supplied formats without stopping on the first mismatch.

  • Create sql/03_dates.sql inside the existing sql folder with this code:
WITH orders_clean AS (
  SELECT DISTINCT
    order_id,
    customer_id,
    order_date,
    CAST(amount AS DECIMAL(18,2)) AS amount
  FROM orders_raw
  WHERE customer_id IS NOT NULL
    AND nullif(trim(amount), '') IS NOT NULL
)
SELECT
  order_id,
  customer_id,
  CAST(
    coalesce(
      try_strptime(order_date, '%Y-%m-%d'),
      try_strptime(order_date, '%m/%d/%Y')
    ) AS DATE
  ) AS order_date,
  amount
FROM orders_clean;

How does date normalization work?

  • The orders_clean expression repeats the approved cleaning policy inside this self-contained query.
  • The first try_strptime call handles dates shaped like %Y-%m-%d.
  • The second parser handles dates shaped like %m/%d/%Y.
  • The coalesce call selects the successful parse before the value becomes a DATE.
  • Save sql/03_dates.sql.

Before you run the check, do you expect either supplied date format to remain invalid?

  • Measure the normalized rows by running this command in Cursor's terminal:
npx tsx check.ts sql/03_dates.sql

What does this date check measure?

The focused check counts every normalized row. It also counts rows where the resulting date is null.

You will see dated_rows as 1893. You will also see invalid_dates as 0.

Seeing invalid dates?

Check that each format string matches the full value shape. Confirm that both parser calls use the same order_date value.

Verify that coalesce wraps both parser calls before the DATE cast.

Help me diagnose why my normalized dates include null values.

✔️ Awesome, I've got everything!

Your date query preserves all 1,893 clean orders. Every supplied date now becomes a valid date value.

ⓧ I'd like to double check the full code

WITH orders_clean AS (
  SELECT DISTINCT
    order_id,
    customer_id,
    order_date,
    CAST(amount AS DECIMAL(18,2)) AS amount
  FROM orders_raw
  WHERE customer_id IS NOT NULL
    AND nullif(trim(amount), '') IS NOT NULL
)
SELECT
  order_id,
  customer_id,
  CAST(
    coalesce(
      try_strptime(order_date, '%Y-%m-%d'),
      try_strptime(order_date, '%m/%d/%Y')
    ) AS DATE
  ) AS order_date,
  amount
FROM orders_clean;
  • Stage the normalized date query by running this command:
git add sql

What is staged now?

Git stages the saved date query from the sql folder. The earlier cleaning commit remains unchanged.

  • Record the normalized date result by running this command:
git commit -m "Step 3: normalized two date formats"

What does this commit preserve?

This commit preserves the two-format parsing rule with its zero-invalid-date result. Git prints a commit summary when the snapshot is recorded.

You now have a committed order set with one date type across every valid row.

Date commit missing?

Confirm that sql/03_dates.sql is saved. Check that the focused date command finished successfully.

Help me diagnose why the normalized date commit is missing.

Expose the broken join

Your orders are now clean. Their dates now share one type.

A join also depends on compatible keys. The unchanged failure harness tests whether the order export and customer export agree about customer identity.

Before you run the harness, do you think the uncast join will succeed or fail?

  • Test the unchanged join harness by running this command in Cursor's terminal:
npx tsx mustfail.ts

What does the failure prove?

You will see AC-3 PASS: DuckDB rejected the uncast BIGINT-to-VARCHAR join..

The order key uses BIGINT. The customer key uses VARCHAR values with a leading C.

The harness recognizes the conversion failure as required evidence. It remains unchanged so the mismatch stays visible.

That failure is valuable evidence. You have proved the exports disagree before introducing a cast.

Your cleaning and date policies are committed. Next, you will document the key decision before delivering the regional revenue view.

Cast Customer Keys and Deliver Regional Revenue

Your Cursor workspace now holds 1,893 clean orders with normalized dates. The required failure also proved that the two exports represent customer keys differently.

The next business view needs customer regions. In this step, you will document the key policy before applying it in DuckDB. You will then use SQL to deliver monthly regional revenue through a bound parameter.

In this step, get ready to:
  • Complete ADR-002 with the customer-key cast policy.
  • Join clean orders to customers while preserving all 1,893 valid orders.
  • Group monthly regional revenue through a named bound parameter.
Record the customer-key decision

An architecture decision record captures a technical choice with its tradeoffs. ADR-002 makes the customer-key conversion reviewable before that conversion affects revenue totals.

  • Return to docs/decisions.md in Cursor.
  • Locate the ADR-002: Cast the prefixed customer key heading.
  • Replace the pending ADR-002 content with the following decision:
## ADR-002: Cast the prefixed customer key

Choice: Strip the leading `C` from `customers_raw.customer_id` and cast that side to `BIGINT`.
Alternative beaten: Format every numeric order key as prefixed text.
Cost: A malformed customer key fails conversion, and leading zero presentation is not preserved in the join value.
Reversal trigger: Reverse when prefixes, leading zeros, or nonnumeric characters carry business meaning.

What Does This Decision Record?

  • The choice keeps orders_raw.customer_id numeric.
  • The conversion removes the leading C from the customer export before casting that value to BIGINT.
  • The cost exposes malformed customer keys through a failed conversion.
  • The reversal trigger protects prefixes or leading zeros when they gain business meaning.
  • Save docs/decisions.md.

You should now see a choice, an alternative, a cost, and a reversal trigger under ADR-002.

Missing Part of ADR-002?

Check that ADR-002 still sits between ADR-001 and ADR-003. Keep each decision field on its own line.

Ask for help comparing the decision record if needed: Help me check whether ADR-002 contains a choice, alternative, cost, and reversal trigger.

Join customer revenue with the cast

A common table expression gives a query a named intermediate result. The customer-revenue query uses two of them to preserve the approved cleaning and date policies before joining customers.

Plan the Customer-Revenue Query

  • The orders_clean expression preserves exact deduplication, blank-row removal, and fixed-scale amounts.
  • The orders_dated expression normalizes both accepted date formats.
  • The join removes the customer prefix with substr() before casting the remaining digits to BIGINT.
  • The final result counts orders and sums revenue for each customer.
  • Use Cursor's Explorer sidebar to create 04_revenue.sql inside the existing sql folder.
  • Write the customer-revenue query from SQL question 4 using the plan above.
  • Compare your completed file with the relevant tab below.

✔️ Awesome, I've got everything!

Your query now carries the approved order policies into the customer-level revenue view.

ⓧ I'd like to double check the full code

WITH orders_clean AS (
  SELECT DISTINCT
    order_id,
    customer_id,
    order_date,
    CAST(amount AS DECIMAL(18,2)) AS amount
  FROM orders_raw
  WHERE customer_id IS NOT NULL
    AND nullif(trim(amount), '') IS NOT NULL
),
orders_dated AS (
  SELECT
    order_id,
    customer_id,
    CAST(
      coalesce(
        try_strptime(order_date, '%Y-%m-%d'),
        try_strptime(order_date, '%m/%d/%Y')
      ) AS DATE
    ) AS order_date,
    amount
  FROM orders_clean
)
SELECT
  customers.customer_id,
  customers.customer_name,
  count(*) AS order_count,
  CAST(sum(orders.amount) AS DECIMAL(18,2)) AS revenue
FROM orders_dated AS orders
JOIN customers_raw AS customers
  ON orders.customer_id = CAST(substr(customers.customer_id, 2) AS BIGINT)
GROUP BY customers.customer_id, customers.customer_name
ORDER BY revenue DESC, customers.customer_id;

How Does the Revenue Query Work?

  • The two expressions reproduce the accepted cleaning and date-normalization logic.
  • The join converts customer keys such as C00001 into the numeric value used by the order export.
  • The grouping produces one row per customer.
  • The final ordering places the highest revenue first.
  • Save sql/04_revenue.sql.

Before you run the focused check, do you expect the casted join to preserve all 1,893 valid orders?

  • Check the customer-revenue query by running this command:
npx tsx check.ts sql/04_revenue.sql

What Does This Check Do?

The focused check runs sql/04_revenue.sql through the existing in-memory database loader. It summarizes the joined customer count, order count, and revenue.

You should see one result row with an orders value of 1893. That result proves the cast closes the key mismatch without dropping a valid order.

Joined Order Count Is Not 1893?

Check that substr(customers.customer_id, 2) starts after the leading customer prefix. Confirm that the cast applies to the customer side of the join.

Ask for help tracing the count if needed: Help me find why my casted customer join does not preserve 1,893 orders.

  • Create the customer-revenue checkpoint by running these commands:
git add .
git commit -m "Step 4: joined casted customer IDs"

What Does This Checkpoint Save?

The first command stages ADR-002 with sql/04_revenue.sql. The second command records the casted customer join in Git history.

The repository now has a reviewable decision beside the implementation that follows it.

Commit Did Not Complete?

Check the terminal message for an unstaged file or an identity warning. The earlier setup should still provide your configured Git identity.

Ask for help interpreting the message if needed: Help me diagnose why the casted customer join commit did not complete.

Group regional revenue safely

The customer join unlocks regional analysis. A named prepared parameter keeps the region value separate from the query text.

Plan the Regional Query

  • The two existing expressions preserve the same cleaning and date policies.
  • The final selection groups order_count and fixed-scale revenue by region and month.
  • The named $region parameter filters one region when a value is supplied.
  • A null parameter keeps all 24 regional month rows available.
  • Use Cursor's Explorer sidebar to create 05_region.sql inside the existing sql folder.
  • Write the monthly regional query from SQL question 5 using the plan above.
  • Compare your completed file with the relevant tab below.

✔️ Awesome, I've got everything!

Your final query now exposes monthly regional totals through a bound value.

ⓧ I'd like to double check the full code

WITH orders_clean AS (
  SELECT DISTINCT
    order_id,
    customer_id,
    order_date,
    CAST(amount AS DECIMAL(18,2)) AS amount
  FROM orders_raw
  WHERE customer_id IS NOT NULL
    AND nullif(trim(amount), '') IS NOT NULL
),
orders_dated AS (
  SELECT
    order_id,
    customer_id,
    CAST(
      coalesce(
        try_strptime(order_date, '%Y-%m-%d'),
        try_strptime(order_date, '%m/%d/%Y')
      ) AS DATE
    ) AS order_date,
    amount
  FROM orders_clean
)
SELECT
  customers.region,
  strftime(orders.order_date, '%Y-%m') AS month,
  count(*) AS order_count,
  CAST(sum(orders.amount) AS DECIMAL(18,2)) AS revenue
FROM orders_dated AS orders
JOIN customers_raw AS customers
  ON orders.customer_id = CAST(substr(customers.customer_id, 2) AS BIGINT)
WHERE customers.region = CAST($region AS VARCHAR)
   OR CAST($region AS VARCHAR) IS NULL
GROUP BY customers.region, strftime(orders.order_date, '%Y-%m')
ORDER BY month, customers.region;

How Does the Regional Query Work?

  • The join uses the customer-key policy recorded in ADR-002.
  • The strftime() expression converts each normalized date into a sortable month.
  • The named parameter receives its value from explore.ts.
  • The final ordering sorts the output by month before region.
  • Save sql/05_region.sql.

Before you run the regional view, how many monthly rows do you expect for North during the first half of 2025?

  • Run the North regional view with this command:
npx tsx explore.ts North

What Does This Regional Run Do?

The command passes North to the existing production entry point. That entry point binds the value to $region before executing the query.

You should see a CSV header followed by six North rows from 2025-01 through 2025-06. The final line should show rows: 6.

That is the regional view working end to end. The cleaned export now produces a result an analyst can inspect month by month.

Before you try the injection-shaped value, do you expect it to alter the query or behave like an ordinary region name?

  • Test the bound parameter with the required injection-shaped value by running:
npx tsx explore.ts "North' OR '1'='1"

Why Does This Return Zero Rows?

The input reaches DuckDB as a value for $region. Its quote characters never become part of the SQL structure.

The complete string matches no region. This is why the SQL injection shaped input returns rows: 0.

The zero-row result proves the input stayed data. The query did not widen its result set.

Before you run the unfiltered view, how many region and month combinations do you expect across four regions and six months?

  • Run the regional entry point without a region value by running:
npx tsx explore.ts

What Does the Unfiltered Run Prove?

Without a command-line region, explore.ts binds a null value. The second condition in the filter then keeps every region.

You should see all 24 regional month rows. The final line should show rows: 24.

Regional Results Look Wrong?

Check that both filter conditions use $region. Confirm that the query groups by both region and the formatted month.

Ask for help comparing the expected row counts if needed: Help me diagnose my six-row North result, zero-row injection test, or 24-row unfiltered result.

  • Create the regional-revenue checkpoint by running these commands:
git add .
git commit -m "Step 5: grouped monthly region revenue"

What Does This Checkpoint Save?

The first command stages the final regional SQL file. The second command records the grouped revenue view in repository history.

The repository now has separate checkpoints for the casted customer join and the regional aggregation.

Regional Commit Did Not Complete?

Confirm that sql/05_region.sql is saved before checking the terminal message. An unchanged file gives Git nothing new to commit.

Ask for help interpreting the commit state if needed: Help me diagnose why the grouped regional revenue commit did not complete.

For the final check, do you expect the committed query to return the same six sorted North rows?

  • Verify the committed regional view by running:
npx tsx explore.ts North

What Does the Final Check Confirm?

This reruns the production entry point after both commits exist. It confirms that the saved regional query still binds North and preserves chronological sorting.

You should see the CSV header, six North rows from 2025-01 through 2025-06, and rows: 6.

Your investigation now turns mismatched exports into a safe monthly revenue view. Next, you will validate the evidence before merging and releasing the handoff.

Validate, Merge, and Release the Evidence

Your five SQL files now turn the messy export into a safe six-month regional view.

A working query still needs acceptance evidence before another person can trust the handoff.

The tests in this step connect every requirement to visible terminal output.

You will merge that evidence through GitHub.

You will publish the finished investigation as release v0.1.0.

In this step, get ready to:
  • Turn the AC-1 through AC-5 results into the README evidence pack.
  • Merge pull request 2 so issue 1 closes.
  • Publish v0.1.0 with verified close-out evidence.
Build the acceptance evidence pack

An evidence pack links each claim to terminal output. This keeps the findings traceable to a repeatable check.

  • Switch back to Cursor from the previous step.
  • Return to the terminal in the sql-and-messy-data workspace.
  • Recreate the first two screenshot states by running these commands:
node --version

npx tsx make-data.ts

What Do These Reruns Prove?

  • The version command confirms that Node.js v24.22.2 is still running the project.
  • The fixture command prints a customer count of 300.
  • The fixture command prints an order count of 2000.
  • The fixture command prints an expected-result count of 24.
  • Capture the toolchain screenshot with the version command visible in Cursor's terminal.
  • Capture the fixture screenshot with all three generated row counts visible in Cursor's terminal.

Before you run the acceptance suite, do you expect the injection-shaped region value to change the query structure?

  • Run the AC-1 through AC-4 checks by entering these commands one at a time:
npx tsx explore.ts North

npx tsx check.ts

npx tsx mustfail.ts

npx tsx explore.ts "North' OR '1'='1"

How to Read the Acceptance Outputs

  • AC-1 prints six North result rows.
  • The AC-1 months run from 2025-01 through 2025-06.
  • The AC-1 final line is rows: 6.
  • AC-2 prints AC-2 PASS: five SQL files matched fresh exports (2000/40/28/40, 1893, 24)..
  • AC-3 prints AC-3 PASS: DuckDB rejected the uncast BIGINT-to-VARCHAR join..
  • AC-4 ends with rows: 0.
  • Capture the AC-1 screenshot with the command plus all six North rows visible in Cursor's terminal.
  • Capture the AC-2 screenshot with the fresh-export pass line visible in Cursor's terminal.
  • Capture the AC-3 screenshot with the required-failure pass line visible in Cursor's terminal.
  • Capture the AC-4 screenshot with the injection-shaped command plus rows: 0 visible in Cursor's terminal.

What Does AC-5 Prove?

AC-5 checks whether every business finding points back to a number in the North output. This prevents the README from making unsupported claims.

  • Select README.md in Cursor's Explorer sidebar.
  • Replace the AC-1 placeholder with the real CSV header.
  • Add the six North result rows beneath the AC-1 header.
  • Add rows: 6 beneath the AC-1 result rows.
  • Update the first finding with one North month plus its order count plus its revenue.
  • Update the second finding with North's highest-volume month plus its order count.
  • Update the third finding with the bound-query result of 6 rows.
  • Include the injection-shaped result of 0 rows in the third finding.

Record the Remaining Evidence

The validation section carries the exact proof for each acceptance criterion. The final checks must agree with this record after the merge.

  • Replace the AC-2 placeholder with the fresh-export pass line.
  • Replace the AC-3 placeholder with the required-failure pass line.
  • Replace the AC-4 placeholder with rows: 0.
  • Replace the AC-5 placeholder with a statement confirming that all three findings use AC-1 numbers.
  • Replace the AC-6 placeholder with the required private-repository state.
  • Include the required closed state for issue 1 in the AC-6 evidence.
  • Include the required merged state for pull request 2 in the AC-6 evidence.
  • Replace the AC-7 placeholder with the seven-commit requirement plus the OS: Windows evidence.
  • Confirm that the README screenshot list names the toolchain capture.
  • Confirm that the README screenshot list names the fixture capture.
  • Confirm that the README screenshot list names the four acceptance-test captures.
  • Confirm that the README screenshot list names the final close-out capture.
  • Save README.md.

Your README now connects AC-1 through AC-5 to observed output. The close-out entries define the delivery state that AC-6 and AC-7 must prove.

The talk track turns the evidence into a short handoff for an analyst or reviewer. It explains each choice in the order that another engineer would inspect it.

  • Switch back to talk-track.txt from the starter files.
  • Replace its template with the following handoff text:
I received two exports that disagreed about customer identifiers and date formats. I first profiled the raw orders instead of cleaning them silently. The profile showed 2000 rows, 40 blank customer IDs, 28 blank amounts, and 40 exact duplicate rows.

I documented the policy before implementation. Blank customer IDs and amounts are dropped, exact duplicates are removed, and amounts become fixed-scale decimals. That leaves 1893 unique valid orders. I normalized ISO and US-style dates with tolerant parsing.

The join was designed to fail before it was fixed. Orders held numeric customer IDs, while customers held five-digit IDs prefixed with C. I kept the order key numeric, stripped the prefix from the customer key, and cast that customer value to BIGINT. The reversal trigger is business meaning in prefixes or leading zeros.

The final query groups order count and revenue by region and month. Region is a named bound parameter, not concatenated SQL. The injection-shaped input returns zero rows.

The check generates a fresh export in a temporary directory, runs the production entry point, compares all 24 rows, and removes the temporary files. The private repository carries the issue, decision records, failed test, screenshots, pull request, and v0.1.0 release.

What Does This Handoff Cover?

The talk track starts with the raw profile. It follows the investigation through cleaning, date normalization, the failed join, the cast decision, parameter safety, and fresh-export validation.

Its final paragraph names the review trail plus the release. This gives a reviewer one narrative that matches the repository evidence.

  • Save talk-track.txt.
  • Confirm that its final paragraph names release v0.1.0.

Does the Handoff Miss a Decision?

Compare the customer-key paragraph with ADR-002. Check that the choice, cost, plus reversal trigger remain aligned.

If a paragraph is missing, help me compare talk-track.txt with the investigation evidence.

✔️ Awesome, I've got everything!

Your saved talk track now matches the investigation's complete handoff story.

ⓧ I'd like to double check the full code

I received two exports that disagreed about customer identifiers and date formats. I first profiled the raw orders instead of cleaning them silently. The profile showed 2000 rows, 40 blank customer IDs, 28 blank amounts, and 40 exact duplicate rows.

I documented the policy before implementation. Blank customer IDs and amounts are dropped, exact duplicates are removed, and amounts become fixed-scale decimals. That leaves 1893 unique valid orders. I normalized ISO and US-style dates with tolerant parsing.

The join was designed to fail before it was fixed. Orders held numeric customer IDs, while customers held five-digit IDs prefixed with C. I kept the order key numeric, stripped the prefix from the customer key, and cast that customer value to BIGINT. The reversal trigger is business meaning in prefixes or leading zeros.

The final query groups order count and revenue by region and month. Region is a named bound parameter, not concatenated SQL. The injection-shaped input returns zero rows.

The check generates a fresh export in a temporary directory, runs the production entry point, compares all 24 rows, and removes the temporary files. The private repository carries the issue, decision records, failed test, screenshots, pull request, and v0.1.0 release.

Why Compare the Full File?

This reference lets you compare every paragraph in one place. Matching it keeps the handoff consistent with the tested investigation.

Merge the documented handoff

A pull request adds a review trail to the Git history. The closing keyword connects that review to the original customer issue.

  • Return to Cursor's terminal.
  • Create the delivery branch by running these commands:
git switch -c sql

git add .

git commit -m "Document findings and handoff"

git push -u origin sql

What Does This Delivery Sequence Do?

  • The first command creates the sql branch.
  • The second command stages the completed evidence files.
  • The third command records the handoff in repository history.
  • The final command publishes the branch to the private remote.
  • Confirm that the commit output includes Document findings and handoff.
  • Confirm that the push output names the remote sql branch.

Did the Branch Push Fail?

If Git reports that the branch already exists, confirm that Cursor shows sql as the current branch. Rerun the remaining commands after that check.

If the remote rejects authentication, help me diagnose my GitHub push from Cursor.

  • Return to the private sql-and-messy-data repository on GitHub.
  • Select Compare & pull request.
  • Set the base branch to main.
  • Set the compare branch to sql.
  • Enter Document findings and handoff as the pull request title.
  • Enter Closes #1 in the pull request description.
  • Select Create Pull Request.
  • Confirm that GitHub assigns pull request number 2.

Why Use Closes #1?

The closing keyword links pull request 2 to issue 1. Merging into the default branch closes that tracked customer outcome.

  • Select Merge pull request.
  • Select Confirm merge.
  • Return to the linked private sql-and-messy-data project board.
  • Drag issue 1 to Done.

That is the review trail complete. Pull request 2 is merged while issue 1 now records the delivered outcome.

Publish the release and verify the close-out

A release freezes the handoff at a named version. Attaching the talk track keeps the technical evidence beside the customer-facing explanation.

  • Return to the repository's Releases page.
  • Select Draft a new release.
  • Select Choose a tag.
  • Enter v0.1.0.
  • Select Create new tag.
  • Set Target to main.
  • Drag talk-track.txt from the sql-and-messy-data folder into the binaries box.
  • Select Publish release.

The release page now shows tag v0.1.0 with talk-track.txt attached. Your handoff now has a versioned delivery point.

Before you run the close-out checks, do you expect every tracked delivery state to agree with the README?

  • Verify AC-6 plus AC-7 by running these commands in Cursor's terminal:
gh repo view --json visibility

gh issue view 1 --json state

gh pr list --state merged

git log --oneline

What Do the Close-Out Checks Prove?

  • The repository check returns {"visibility":"PRIVATE"}.
  • The issue check returns {"state":"CLOSED"}.
  • The pull request list includes pull request 2.
  • The history shows at least seven one-line commits.
  • The history includes Document findings and handoff.
  • Return to README.md in Cursor.
  • Confirm that the file still contains OS: Windows.
  • Confirm that the AC-6 entry agrees with the terminal states.
  • Confirm that the AC-7 entry agrees with the commit history.
  • Capture the seventh screenshot with the AC-6 plus AC-7 close-out evidence visible in Cursor's terminal.

Does a Close-Out State Disagree?

Check the delivered resource that failed its acceptance criterion. Keep the README evidence unchanged while you correct the repository state.

If the mismatch is unclear, help me diagnose my AC-6 and AC-7 close-out results.

You have completed the full handoff. The private repository now carries tested findings, a closed customer issue, a merged review trail, plus a versioned release.

Secret mission

Prove the Fresh-Export Test Cannot Be Fooled

A regression check is only credible when it trusts the right evidence. You will alter a checked-in result before changing production SQL. The two probes reveal what the fresh-export check actually protects.

Clean Up Your Resources

Clean Up Your Resources

These resources have no ongoing cost under GitHub Free. Decide whether to keep them, pause the local work, or delete every project resource.

Resources you used:

  • The local sql-and-messy-data folder containing the Node.js project, generated CSV fixtures, five SQL files, README evidence, and talk-track.txt.
  • The private GitHub repository sql-and-messy-data containing both branches, issue 1, pull request 2, the release tag, and the commit history.
  • The published GitHub release v0.1.0 with talk-track.txt attached as its release asset.
  • The private GitHub Project sql-and-messy-data with issue 1 in Done.
  • The seven terminal screenshot files captured as investigation evidence.

Keep everything running

No action needed. Choose this if you plan to revisit the investigation or use its evidence in a future handoff.

  • Retain the local sql-and-messy-data folder.
  • Retain the seven screenshot files.
  • Leave the sql-and-messy-data repository private.
  • Leave the private GitHub Project available.
  • Leave the v0.1.0 release published.

Pause - I'll come back to this later

Pausing frees local memory while preserving every artifact. In-process DuckDB leaves no server or database service to suspend.

  • Close Cursor.
  • Keep the local sql-and-messy-data folder in place.
  • Keep the seven screenshot files in their current saved location.
  • Leave the private GitHub repository available.
  • Leave the private GitHub Project available.
  • Leave the v0.1.0 release published.

Delete - I don't want to use this again

Deleting the released work is permanent. Your important evidence stays safe when you copy it before removing the resources.

  • Copy any README.md, talk-track.txt, or screenshot evidence you want to keep to a location outside sql-and-messy-data.
  • Open the published v0.1.0 page from the repository's Releases area to confirm talk-track.txt is attached.
  • Use the release options menu on that page to delete the v0.1.0 release.
  • Open Settings for the sql-and-messy-data GitHub Project to confirm its visibility is Private.
  • Use the deletion control in the project settings to delete the private GitHub Project.

Repository Deletion Scope

Deleting the private repository removes all repository history and tracked work. This includes issue 1 and pull request 2.

  • Open the repository settings page for sql-and-messy-data to confirm its visibility is PRIVATE.
  • Use the repository deletion control in Settings to delete the private sql-and-messy-data repository.
  • Delete the seven screenshot files from their current saved location.
  • Locate the local sql-and-messy-data folder by searching for its exact name in File Explorer.
  • Delete the local sql-and-messy-data folder.

Cleanup complete. Every resource from this investigation is now removed.

Nice Work!

Nice Work!

You did it! Your private TypeScript investigation now turns a messy customer export into defensible monthly findings.

Your SQL evidence preserves the failed join. Your tagged GitHub release packages the validated handoff.

You learned how to:

  • Deterministic fixtures: Generate the same 300 customers plus 2,000 raw orders on demand. Compare the result with 24 independent expected rows.
  • Defensible SQL: Profile blanks plus duplicates before cleaning the export. Normalize two date formats before casting the mismatched customer key.
  • Evidence-backed delivery: Prove parameter safety through a zero-row injection test. Validate fresh exports with DuckDB. Merge the work through pull request 2. Publish v0.1.0 with talk-track.txt attached.
  • Secret Mission: Proved that tampering with sample-data/expected.csv cannot fool the fresh-export check. Showed that a real regression in sql/02_clean.sql does fail it.

Ready to quiz yourself?