Profile Column Cardinality Before Designing Storage

1. Compute distinct counts, null rates, and top frequencies per column.

duckdb -c "SELECT 'status' col, count(DISTINCT status) distincts, count(*) - count(status) nulls, count(*) total FROM 'events.parquet' UNION ALL SELECT 'user_id', count(DISTINCT user_id), 0, count(*) FROM 'events.parquet' UNION ALL SELECT 'device', count(DISTINCT device), count(*) - count(device), count(*) FROM 'events.parquet'"

Expected output: A per-column stats table prints: status has 4 distincts, user_id 2.1M, device 380, quantifying the design input.

2. Measure frequency skew on the low-cardinality columns.

duckdb -c "SELECT status, count(*) n, round(100.0 * count(*) / sum(count(*)) OVER (), 1) pct FROM 'events.parquet' GROUP BY status ORDER BY n DESC"

Expected output: One value such as completed dominates at 94 percent, meaning an index on it is useless for selectivity but a partial index on rare values could shine.

3. Derive the storage design from the measured numbers.

-- measured: status (4 values, 94% one value), user_id (2.1M), created (time)

-- wrong (designed without profiling): btree on every status value
CREATE INDEX idx_status ON events(status);            -- never used

-- right (from profile):
-- 1. enum-like low cardinality: CHECK + tiny type
ALTER TABLE events ALTER status TYPE text;
ALTER TABLE events ADD CONSTRAINT status_values
  CHECK (status IN ('pending','completed','failed','refunded'));

-- 2. rare-value selectivity: partial index skips the 94%
CREATE INDEX idx_failed ON events(created)
  WHERE status = 'failed';                             -- 6% of rows

-- 3. high-cardinality + time filter: composite, time first
CREATE INDEX idx_user_time ON events(created, user_id);

Expected output: The redesign matches the measured shape: partial index scans 6 percent of rows, composite serves time-ranged user queries, and the useless full btree is gone.

4. Validate the sample stats against the full dataset.

duckdb -c "SELECT count(DISTINCT status) FROM read_parquet(['events-*.parquet'])" && echo "compare with sample: 4 vs full:" && duckdb -c "SELECT count(DISTINCT status) FROM 'events.parquet'"

Expected output: The full-scan distinct count matches the sample within expected bounds, confirming the profile generalizes before you commit the design.

5. Pin the profile as a schema-design artifact.

-- profile.sql: re-runnable evidence attached to the schema change
-- 2026-10-22 sample=5.2% (events.parquet)
--   status   distinct=4    top=completed(94.1%)
--   user_id  distinct=2.1M nulls=0
--   device   distinct=380  nulls=1.9%
-- design: CHECK(status) + partial idx(failed) + composite(created,user_id)
SELECT now() AS profiled_at, current_database();

Expected output: The numbers live beside the DDL they justified, so the next redesign starts from evidence instead of folklore.

Problem

Distinct counts blow up memory

Solution

Use APPROX_COUNT_DISTINCT or sample-based estimation for the multi-million columns; exact counts matter only for the low-cardinality ones driving enum decisions.

Problem

Sample says 4 statuses, production shows 6

Solution

The sample missed rare values; enumerate statuses from the CHECK constraint process, not the sample, and treat cardinality floors as lower bounds.

Problem

Frequency skew changes seasonally

Solution

Profile both a busy and a quiet period before choosing partial-index predicates; a rare-in-October status can dominate in January.