redshift-profile

Redshift Column Profile

Discovers what a column actually contains. Read-only, MCP-composed.

When to use / NOT

• Use when user wants enum check, null rate, value distribution.
• NOT for free-text columns (top-100 is noise) or huge tables without user-confirmed full-scan.

Inputs

Form	Parsed
<schema>.<table> <col>	direct
<schema> <table> <col>	direct
<table> <col>	schema missing → ask user
(no args)	ask "which table.column?"

Quote identifiers in SQL as "<schema>"."<table>"."<col>".

Flow

1. Resolve column metadata (type, nullable, comment):

   list_columns(schema_name, table_name, include_comments=true). Find the row whose name == <col> (MCP returns {name, type, nullable, comment} — NOT column_name / data_type; nullable is the string "YES" / "NO").

   If column missing → error column_not_found.

2. Type branch (lowercase prefix match on type):

   branch	Redshift types	top-N	min/max
   string	varchar, char, text, bpchar, nvarchar	✓	—
   numeric	int, integer, bigint, smallint, numeric, decimal, real, double, float	✓	✓
   date	date, timestamp, timestamptz, timetz, time	✓	✓
   boolean	bool, boolean	✓	—
   unsupported	super, geometry, geography, hllsketch, varbyte	—	—

   unsupported → emit error JSON, stop.

3. Cardinality + null + total (one execute_sql):

   SELECT COUNT(*) AS total_rows,
          SUM(CASE WHEN "<col>" IS NULL THEN 1 ELSE 0 END) AS null_count,
          COUNT(DISTINCT "<col>") AS distinct_count
   FROM "<schema>"."<table>";
   

   COUNT(DISTINCT) excludes NULL — the null bucket lives in Step 4. If total_rows == 0 → emit empty profile, skip Steps 4-5.

4. Top-100 (one execute_sql, all branches):

   WITH base AS (
       SELECT "<col>"::text AS v, COUNT(*) OVER () AS total_rows
       FROM "<schema>"."<table>"
   )
   SELECT v AS value, COUNT(*) AS count,
          ROUND(COUNT(*)*100.0 / MAX(total_rows), 2) AS pct
   FROM base GROUP BY v ORDER BY count DESC NULLS LAST LIMIT 100;
   

   ::text normalizes booleans/numerics/dates so JSON shape is uniform. The NULL bucket appears as {"value": null, ...} — intentional; downstream skills handle it. distinct_count_truncated = distinct_count > 100.