{ "cells": [ { "cell_type": "markdown", "id": "7c09bdd7", "metadata": {}, "source": [ "# Featurizer Tutorial: Text → Edges → Centrality → Spine\n", "\n", "**The Path-2 move**: text *induces* a graph, and the graph becomes\n", "point-in-time-correct features. Six authors post short Spanish\n", "messages; three of them paste the **same campaign text** at\n", "staggered dates — the copy-paste signature of coordination. We run\n", "the full two-stage φ-bridge pipeline (ADR-0001/ADR-0014):\n", "\n", "```\n", "posts ─ SentimentBridge ────────→ bridge_sentiment (Path 1)\n", " └─── NearDuplicateEdgeBridge ─→ text_edges (Path 2, stage 1)\n", " └─ CentralityBridge ──→ post_centrality (stage 2, snapshots)\n", " └─ SQL spine ───→ feature matrix (as-of bounded)\n", "```\n", "\n", "**This tutorial executes against PostgreSQL** (`just db-up` first;\n", "the committed outputs were produced against the throwaway database)." ] }, { "cell_type": "markdown", "id": "eb79b874", "metadata": {}, "source": [ "## 1. Setup and data" ] }, { "cell_type": "code", "execution_count": 1, "id": "58454d19", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:30.463126Z", "iopub.status.busy": "2026-07-19T04:53:30.462931Z", "iopub.status.idle": "2026-07-19T04:53:30.554805Z", "shell.execute_reply": "2026-07-19T04:53:30.554331Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "✓ Seeded schema example_06: 6 authors, 6 posts, 2 as-of dates\n" ] } ], "source": [ "import sys\n", "from pathlib import Path\n", "\n", "# This tutorial EXECUTES against PostgreSQL (see the note above).\n", "sys.path.insert(0, str(Path.cwd().parent.parent)) # repo root, for `featurizer`\n", "sys.path.insert(0, str(Path.cwd().parent)) # examples/, for `_db`\n", "\n", "import create_data\n", "\n", "create_data.main() # (re)seed the example_06 schema" ] }, { "cell_type": "markdown", "id": "b7b7d485", "metadata": {}, "source": [ "The seeded posts: `a1`–`a3` share the campaign text (staggered\n", "dates), `a4`–`a6` are organic. Two as-of dates bound the backtest:\n", "at **2024-03-31** only the first copy exists; by **2024-06-30** the\n", "cluster has fully formed." ] }, { "cell_type": "code", "execution_count": 2, "id": "33b8913b", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:30.556142Z", "iopub.status.busy": "2026-07-19T04:53:30.556040Z", "iopub.status.idle": "2026-07-19T04:53:30.568103Z", "shell.execute_reply": "2026-07-19T04:53:30.567763Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1, 'a1', datetime.date(2024, 1, 15), 'Terrible el nuevo reglamento del corredor: o')\n", "(2, 'a2', datetime.date(2024, 2, 20), 'Terrible el nuevo reglamento del corredor: o')\n", "(3, 'a3', datetime.date(2024, 5, 10), 'Terrible el nuevo reglamento del corredor: o')\n", "(4, 'a4', datetime.date(2024, 1, 20), 'Excelente jornada en el puerto, servicio ráp')\n", "(5, 'a5', datetime.date(2024, 3, 5), 'La asamblea revisó el calendario de obras si')\n", "(6, 'a6', datetime.date(2024, 4, 12), 'Buena respuesta del operador aunque persiste')\n" ] } ], "source": [ "import psycopg\n", "import _db\n", "\n", "conn = psycopg.connect(_db.require_conninfo())\n", "cur = conn.cursor()\n", "cur.execute(\"set search_path to example_06\")\n", "cur.execute(\n", " \"select post_id, author_id, posted_at, left(body, 44) from posts order by post_id\"\n", ")\n", "for row in cur.fetchall():\n", " print(row)" ] }, { "cell_type": "markdown", "id": "16ad1d24", "metadata": {}, "source": [ "## 2. Path 1 — reduce each post to a sentiment scalar\n", "\n", "`SentimentBridge` is dependency-free with a **Spanish-register\n", "default lexicon** (never a silent English default). `persist=True`\n", "writes a real table — the orchestrated-asset flow you would wire\n", "into Dagster/Snakemake upstream of the SQL run." ] }, { "cell_type": "code", "execution_count": 3, "id": "91d08e12", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:30.569206Z", "iopub.status.busy": "2026-07-19T04:53:30.569144Z", "iopub.status.idle": "2026-07-19T04:53:30.801388Z", "shell.execute_reply": "2026-07-19T04:53:30.800981Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1, 'a1', -0.72)\n", "(2, 'a2', -0.72)\n", "(3, 'a3', -0.72)\n", "(4, 'a4', 0.7000000000000001)\n", "(5, 'a5', None)\n", "(6, 'a6', 0.0)\n" ] } ], "source": [ "from featurizer.bridge import SentimentBridge\n", "\n", "for table in (\"bridge_sentiment\", \"text_edges\", \"post_centrality\"):\n", " cur.execute(f\"drop table if exists {table}\") # idempotent re-runs\n", "\n", "sentiment = SentimentBridge(pk_col=\"post_id\", text_col=\"body\")\n", "sentiment.materialize(\n", " conn,\n", " source_table=\"posts\",\n", " pk=\"post_id\",\n", " carry_cols=[\"author_id\", \"posted_at\"],\n", " content_cols=[\"body\"],\n", " output_table=\"bridge_sentiment\",\n", " persist=True,\n", ")\n", "cur.execute(\n", " \"select post_id, author_id, sentiment from bridge_sentiment order by post_id\"\n", ")\n", "for row in cur.fetchall():\n", " print(row)" ] }, { "cell_type": "markdown", "id": "104e7a75", "metadata": {}, "source": [ "The pasted campaign scores **−0.72** wherever it appears; `a5`'s\n", "post has no lexicon evidence → `NULL` (no evidence ≠ neutral).\n", "\n", "## 3. Path 2, stage 1 — near-duplicate text induces edges\n", "\n", "MinHash/LSH over word shingles. An edge connects the **authors** of\n", "two near-duplicate posts and is knowable at the **later** post of\n", "the pair — causality lives on the edge timestamp. Self-copies are\n", "excluded (pasting your own text is not coordination)." ] }, { "cell_type": "code", "execution_count": 4, "id": "7f341cc4", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:30.802332Z", "iopub.status.busy": "2026-07-19T04:53:30.802238Z", "iopub.status.idle": "2026-07-19T04:53:31.000839Z", "shell.execute_reply": "2026-07-19T04:53:31.000463Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "('a1', 'a2', datetime.date(2024, 2, 20))\n", "('a1', 'a3', datetime.date(2024, 5, 10))\n", "('a2', 'a3', datetime.date(2024, 5, 10))\n" ] } ], "source": [ "from featurizer.bridge import NearDuplicateEdgeBridge\n", "\n", "edges = NearDuplicateEdgeBridge(\n", " pk_col=\"post_id\",\n", " entity_col=\"author_id\",\n", " text_col=\"body\",\n", " ts_col=\"posted_at\",\n", ")\n", "edges.materialize_edges(\n", " conn,\n", " source_table=\"posts\",\n", " output_table=\"text_edges\",\n", " content_cols=[\"post_id\", \"author_id\", \"posted_at\", \"body\"],\n", " persist=True,\n", ")\n", "cur.execute(\"select * from text_edges order by ts, src\")\n", "for row in cur.fetchall():\n", " print(row)" ] }, { "cell_type": "markdown", "id": "35b12242", "metadata": {}, "source": [ "Three edges — the (a1, a2) pair on **2024-02-20**, then a3 joins\n", "both on **2024-05-10**. Organic authors never appear.\n", "\n", "## 4. Path 2, stage 2 — centrality snapshots per as-of window\n", "\n", "Centrality is **non-local**: one future edge changes every node's\n", "score, so the graph is rebuilt *per window* from strictly pre-t₀\n", "edges — never computed once and sliced. The output is keyed\n", "`(node_id, as_of_date)`: an ordinary event stream the spine trends.\n", "The cheap metric tier is the default; betweenness/eigenvector/\n", "closeness are opt-in (`include_heavy=True`) so configs never get\n", "silently slower." ] }, { "cell_type": "code", "execution_count": 5, "id": "e5dcaab2", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:31.001938Z", "iopub.status.busy": "2026-07-19T04:53:31.001842Z", "iopub.status.idle": "2026-07-19T04:53:31.043579Z", "shell.execute_reply": "2026-07-19T04:53:31.043115Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "('a1', datetime.date(2024, 3, 31), 1.0, 0.0)\n", "('a2', datetime.date(2024, 3, 31), 1.0, 0.0)\n", "('a1', datetime.date(2024, 6, 30), 2.0, 1.0)\n", "('a2', datetime.date(2024, 6, 30), 2.0, 1.0)\n", "('a3', datetime.date(2024, 6, 30), 2.0, 1.0)\n" ] } ], "source": [ "from featurizer.bridge import CentralityBridge\n", "\n", "cur.execute(\"select as_of_date from as_of_dates order by as_of_date\")\n", "as_of_dates = [row[0] for row in cur.fetchall()]\n", "\n", "centrality = CentralityBridge(source_col=\"src\", target_col=\"dst\", directed=False)\n", "centrality.materialize_snapshots(\n", " conn,\n", " source_table=\"text_edges\",\n", " output_table=\"post_centrality\",\n", " as_of_dates=as_of_dates,\n", " causal_col=\"ts\",\n", " content_cols=[\"src\", \"dst\"],\n", " entity_col=\"node_id\",\n", " as_of_col=\"as_of_date\",\n", " persist=True,\n", ")\n", "cur.execute(\n", " \"select node_id, as_of_date, degree, clustering from post_centrality order by as_of_date, node_id\"\n", ")\n", "for row in cur.fetchall():\n", " print(row)\n", "conn.commit()" ] }, { "cell_type": "markdown", "id": "f201b390", "metadata": {}, "source": [ "At the March cut only the first pair exists (degree 1, clustering\n", "0); by June the triangle has closed (degree 2, clustering 1.0) —\n", "and the March rows *stay* what was knowable in March.\n", "\n", "## 5. The config — bridge outputs are ordinary entities\n", "\n", "`emit_yaml()` produces the entity + relationship fragments; this\n", "example commits them in `config.yaml` and **asserts equality**, so\n", "the declared config cannot drift from what the bridges emit." ] }, { "cell_type": "code", "execution_count": 6, "id": "5569bf91", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:31.044560Z", "iopub.status.busy": "2026-07-19T04:53:31.044461Z", "iopub.status.idle": "2026-07-19T04:53:31.047942Z", "shell.execute_reply": "2026-07-19T04:53:31.047628Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "alias: centrality\n", "table: post_centrality\n", "id: node_id\n", "variables:\n", " degree:\n", " type: numeric\n", " in_degree:\n", " type: numeric\n", " out_degree:\n", " type: numeric\n", " weighted_degree:\n", " type: numeric\n", " coreness:\n", " type: numeric\n", " clustering:\n", " type: numeric\n", "temporal_ix: as_of_date\n", "\n" ] } ], "source": [ "import yaml\n", "\n", "fragment = centrality.emit_yaml(\n", " output_table=\"post_centrality\",\n", " pk=\"node_id\",\n", " parent_alias=\"authors\",\n", " parent_key=\"author_id\",\n", " fk=\"node_id\",\n", " temporal_ix=\"as_of_date\",\n", ")\n", "declared = yaml.safe_load(Path(\"config.yaml\").read_text())\n", "assert fragment[\"entity\"] == {e[\"alias\"]: e for e in declared[\"entities\"]}[\"centrality\"]\n", "print(yaml.safe_dump(fragment[\"entity\"], sort_keys=False))" ] }, { "cell_type": "markdown", "id": "3d119beb", "metadata": {}, "source": [ "## 6. The spine — the feature matrix\n", "\n", "From here everything is standard featurizer: the snapshot stream\n", "and the sentiment column aggregate under the normal\n", "`<= as_of_date` causal bound." ] }, { "cell_type": "code", "execution_count": 7, "id": "2eb288d8", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:31.048880Z", "iopub.status.busy": "2026-07-19T04:53:31.048830Z", "iopub.status.idle": "2026-07-19T04:53:31.105535Z", "shell.execute_reply": "2026-07-19T04:53:31.105133Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.050\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36mplan\u001b[0m:\u001b[36m252\u001b[0m - \u001b[34m\u001b[1mStarting feature build for target authors\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.050\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m278\u001b[0m - \u001b[34m\u001b[1mbuild_features(authors) depth=0\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.050\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m278\u001b[0m - \u001b[34m\u001b[1mbuild_features(centrality) depth=1\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.050\u001b[0m | \u001b[1mINFO \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m298\u001b[0m - \u001b[1mMaximum recursion depth reached at depth 2; materializing centrality without traversing further.\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.051\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1270\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(authors).author_id -> Entity(centrality).node_id\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.051\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m278\u001b[0m - \u001b[34m\u001b[1mbuild_features(sentiment) depth=1\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.051\u001b[0m | \u001b[1mINFO \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m298\u001b[0m - \u001b[1mMaximum recursion depth reached at depth 2; materializing sentiment without traversing further.\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.051\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1270\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(authors).author_id -> Entity(sentiment).author_id\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.051\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'authors': 8 CTEs, 4932 chars\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-07-18 22:53:31.052\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'authors': 8 CTEs, 4932 chars\u001b[0m\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MAX(centrality.clustering)MAX(centrality.degree)MEAN(sentiment.sentiment)
as_of_dateauthor_id
2024-03-31a10.01.0-0.72
a20.01.0-0.72
a3NaNNaNNaN
a4NaNNaN0.70
a5NaNNaNNaN
a6NaNNaNNaN
2024-06-30a11.02.0-0.72
a21.02.0-0.72
a31.02.0-0.72
a4NaNNaN0.70
a5NaNNaNNaN
a6NaNNaN0.00
\n", "
" ], "text/plain": [ " MAX(centrality.clustering) MAX(centrality.degree) \\\n", "as_of_date author_id \n", "2024-03-31 a1 0.0 1.0 \n", " a2 0.0 1.0 \n", " a3 NaN NaN \n", " a4 NaN NaN \n", " a5 NaN NaN \n", " a6 NaN NaN \n", "2024-06-30 a1 1.0 2.0 \n", " a2 1.0 2.0 \n", " a3 1.0 2.0 \n", " a4 NaN NaN \n", " a5 NaN NaN \n", " a6 NaN NaN \n", "\n", " MEAN(sentiment.sentiment) \n", "as_of_date author_id \n", "2024-03-31 a1 -0.72 \n", " a2 -0.72 \n", " a3 NaN \n", " a4 0.70 \n", " a5 NaN \n", " a6 NaN \n", "2024-06-30 a1 -0.72 \n", " a2 -0.72 \n", " a3 -0.72 \n", " a4 0.70 \n", " a5 NaN \n", " a6 0.00 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import os\n", "from featurizer import Featurizer\n", "\n", "os.environ[\"DATABASE_URL\"] = _db.records_url(\"example_06\")\n", "featurizer = Featurizer(\"config.yaml\")\n", "df = featurizer.to_dataframe()\n", "df[\n", " [\n", " c\n", " for c in df.columns\n", " if \"MAX(centrality.degree)\" in c\n", " or \"MAX(centrality.clustering)\" in c\n", " or \"MEAN(sentiment.sentiment)\" in c\n", " ]\n", "]" ] }, { "cell_type": "markdown", "id": "d749fdd7", "metadata": {}, "source": [ "## 7. Reading the signal\n", "\n", "- The **coordinated trio** (`a1`–`a3`) carries centrality that\n", " *grows between the as-of dates* — degree 1 → 2, clustering 0 → 1\n", " as the copy-paste triangle closes — while every organic author\n", " stays `NULL` (never in the induced graph; never fabricated as 0).\n", "- The **campaign sentiment** (−0.72) travels with the pasted text.\n", "- Both signals are point-in-time correct: the March row knows\n", " nothing about May's paste.\n", "\n", "The heavier variants of everything here — multilingual NER counts,\n", "Louvain communities over the induced graph, embedding-trajectory\n", "novelty, change-point scores — follow the same\n", "`compute → materialize → emit_yaml` lifecycle; see the\n", "[bridge cookbook](https://ccd-ia.github.io/featurizer/engineering/bridge-cookbook/).\n", "For cheap graph features without any Python at all, see the native\n", "`graph_relationships` block in the\n", "[configuration reference](https://ccd-ia.github.io/featurizer/reference/configuration/)." ] }, { "cell_type": "code", "execution_count": 8, "id": "a68f831a", "metadata": { "execution": { "iopub.execute_input": "2026-07-19T04:53:31.106383Z", "iopub.status.busy": "2026-07-19T04:53:31.106329Z", "iopub.status.idle": "2026-07-19T04:53:31.107748Z", "shell.execute_reply": "2026-07-19T04:53:31.107462Z" } }, "outputs": [], "source": [ "conn.close()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.13" } }, "nbformat": 4, "nbformat_minor": 5 }