data-insight-recipes

Public: https://git.binary.ovh/brahm/data-insight-recipes

Copy-paste Python, SQL, Excel, and Redshift for finding patterns in any table — including a warehouse table whose useful fields sit inside a JSON / SUPER column.

The seven recipes are not the only way. They are layer 2–3 of a stack that works on unknown data:

flatten JSON  →  triage columns  →  charts + recipes
     →  if X+Y+Z then F  →  investigation tickets  →  slice  →  repeat

Design, with every approach listed and when to combine them: docs/APPROACHES.md.

What a high / low / mean / cluster / outlier / r value means: docs/INTERPRET.md.

If X + Y + Z + A + C then F: docs/COMBINATIONS.md.

---

5 minutes

git clone https://git.binary.ovh/brahm/data-insight-recipes.git
cd data-insight-recipes
chmod +x run.sh
./run.sh

Python 3.10+, stdlib only. ./run.sh runs the stack on a flat sales table and on a Redshift-shaped extract (payload is JSON). It checks that flatten-then-pipeline still recovers the planted structures.

Any new extract (including Redshift JSON)

# 1. UNLOAD / export a sample to events.csv  (column `payload` holds JSON)
# 2. This repo writes the flatten SQL *and* runs the stack on the sample
python3 -m recipes redshift-sql events.csv --json-col payload --table analytics.events
python3 -m recipes pipeline     events.csv --json-col payload --table analytics.events --out results/mine

pipeline will:

1. Flatten payload into dotted columns (payload.sale.revenue, …).
2. Triage — which columns are measures / dimensions / time / junk.
3. Histogram every measure, bar every dimension (ASCII + report.html).
4. Run the recipes on every matching column.
5. Combine them (CMB-OUT-CONC, CMB-STACK, …).
6. Write investigation tickets with placeholder Redshift + SQLite SQL.

Then open results/mine/tickets.json / report.html. Run the SQL for INV-001. Slice. Re-run.

Already-flat spreadsheet? Skip --json-col:

python3 -m recipes pipeline path/to/file.csv --out results/mine
python3 -m recipes iterate  path/to/file.csv --slice region=West

The stack

layer	question	command / file
0 flatten	is a column JSON / SUPER / an array?	recipes flatten, redshift/
1 triage	which columns deserve a chart?	recipes triage
2 univariate	high / low / pile / outlier / missing	recipes 1, 2, 7 + charts.py
3 relationships	r, group lift, trend, lookup	recipes 3–6
4 shape	1-D modes, multi-column outliers	recipes/clusters.py
5 combine	if X and Y and Z then F	recipes/combine.py
6 tickets	INV-00N + placeholder SQL	recipes/investigate.py
7 slice	--slice COL=VALUE, go back to 1	HOW-TO.md

Recipes (layers 2–3)

#	recipe	looks for	Python	SQL	Excel
0	profile / triage	kind + whether to work the column	profile.py, triage.py	sql/00_profile.sql	excel/README.md
1	outliers	Tukey 1.5×IQR	outliers.py	sql/01_outliers.sql	helper column
2	concentration	Gini + top-10% lift	concentration.py	sql/02_concentration.sql	sort + running %
3	trends	month-over-month ±25% (median)	trends.py	sql/03_trends.sql	pivot by yyyy-mm
4	correlations	Pearson |r| ≥ 0.45 after outliers	correlations.py	sql/04_correlations.sql	=CORREL
5	group effects	category median ≥ 1.6× the rest	group_effects.py	sql/05_group_effects.sql	=MEDIAN(FILTER(...))
6	associations	A nearly determines B	associations.py	sql/06_associations.sql	two-way pivot
7	missingness	blanks that travel together	missingness.py	sql/07_missingness.sql	flag + COUNTIFS

Four more that close real gaps in docs/APPROACHES.md (each one used to say "documented; not implemented, needs a library" — all four are pure Python, no dependencies):

#	recipe	looks for	why it's not redundant with the above
1b	outliers (MAD)	median ± 3.5×1.4826×MAD	outliers.py's Tukey fence widens on a skewed column; MAD doesn't. Run both, read the gap.
4b	rank correlations	Spearman |ρ| ≥ 0.45	Pearson only sees straight lines. A curved-but-monotonic pair (y=x², y=log x) scores r≈0 and ρ≈1.
8	rules	Apriori-lite: support/confidence/lift over 1–3 item sets	associations.py is pairs only. This mines "if A and B then C" straight from the values, not from a list of finding types.
9	segments	k-means (k=2–5, elbow-picked) on standardized measures	clusters.py finds 1-D piles and multi-column outliers; this finds real multivariate groups, the stdlib substitute for sklearn's KMeans/HDBSCAN.

recipes/outliers_mad.py, recipes/rank_correlations.py, recipes/rules.py, recipes/segments.py. In both iterate and pipeline: --only outliers_mad,rank_correlations,rules,segments.

JSON in Excel: excel/flatten.md. JSON in Redshift: redshift/README.md.

Layout

docs/          APPROACHES.md  INTERPRET.md  COMBINATIONS.md
recipes/       Python stack (stdlib). python3 -m recipes pipeline FILE
sql/           SQLite-flavoured twins + apply.py
redshift/      SUPER / VARCHAR flatten + triage SQL
excel/         Excel 365 / Sheets walkthrough + JSON expand
data/          sample_orders.csv
               sample_events.csv / .jsonl   (orders inside `payload`)
               sample_sessions.jsonl        (events inside `properties`)
HOW-TO.md      the loop, including the JSON first step

Related

• insight-miner — same kinds of findings, scored by bias-corrected statistics.
• patternlab — unsupervised discovery engine.

License

MIT. Sample tables are synthetic.