Reviews Dashboard + Semantic Search

Goal: every uploaded customer review gets analyzed AND indexed in parallel — sentiment / topics / toxicity land in a warehouse table (for SQL dashboards), while semantic embeddings land in a vector collection (for “find reviews similar to this complaint”). One upload, two destinations, zero glue code.

Primitives used: Storage + Pipelines (two pipelines, same source) + Tables + Vector. The fan-out happens because K3 lets you have multiple ingest rules on a single source, each routing to its own pipeline + destination.

Shape:


   review.json  ──upload──►  Storage bucket
                                    │
                                    ▼  internal-S3 source fires two matching rules
                            ┌───────┴───────┐
                            │               │
                            ▼               ▼
                Pipeline A           Pipeline B
            (review_analysis)   (text_embedding_index)
                            │               │
                            ▼               ▼
                Tables: reviews     Vector: reviews_vec
                (sentiment, topics, (semantic embeddings
                 keywords, toxicity)  for recall)
                            │               │
                            ▼               ▼
              SQL queries / BI    search route / RAG
              dashboards          / "similar to X"

Prerequisites

dodil CLI + dodil auth login

A bucket — kb-reviews:


dodil data bucket create kb-reviews -d "Customer review intake"

Tables engine is auto-enabled at bucket creation; vector capacity is provisioned on demand when you run dodil data vector collection add below — no engine setup step.

1. Create the two destinations

A. Pipeline-bound table for sentiment

Pick the review_analysis template — sentiment + toxicity + keyword extraction, emits structured rows:


# Browse — the table facet is `warehouse_compatible: true` templates
dodil data table templates --search review -o json | jq '.templates[] | {id, name}'
 
# Create the table-bound pipeline
dodil data table pipeline create reviews -b kb-reviews \
  --description "Customer reviews — sentiment analyzed" \
  --template review_analysis

dodil data table create is the wrong verb here. It creates a plain Delta table from a column spec (--columns-json, --merge-key, --partition-column) and has no template or pipeline flags at all. Binding a Scriptum template to a table is dodil data table pipeline create (-t/--template, optional --folder-prefix), which is the CLI face of POST /:bucket/pipelines/table.

K3 atomically creates: the table + a Scriptum pipeline + an auto-generated ingest rule. Schema lazy-materializes on first ingest.

review_analysis (templates/ecommerce/) declares table_name review_analysis, warehouse_mode merge and merge_keys ["input_ref"], and accepts txt, md, html, htm, pdf. Its declared outputs are analysis, sentiment, aspects, toxicity, language, keywords, s3, warehouse. Note its output_dest defaults to return — the pipeline facet sets the warehouse destination for you, but if you drive the template directly you must ask for it.

B. Pipeline-mode vector collection for semantic recall


dodil data vector collection add reviews_vec -b kb-reviews \
  --description "Customer reviews — semantic embeddings" \
  --template text_embedding_index

Same pattern: collection + pipeline + auto-rule. Now you have two pipelines in kb-reviews, both pointing at the bucket’s internal-S3 source. Both will fire on every matching review upload.

Capture identifiers for later operational steps:

Both destinations are pipelines, so both ids come from the pipeline list — the ?facet= split is what separates them:


export TABLE_PIPELINE_ID=$(dodil data table pipeline list -b kb-reviews -o json \
  | jq -r '.pipelines[] | select(.name == "reviews") | .pipelineId')
export VECTOR_PIPELINE_ID=$(dodil data vector collection list -b kb-reviews -o json \
  | jq -r '.pipelines[] | select(.name == "reviews_vec") | .pipelineId')
 
# Confirm both rules are enabled and have matching globs
dodil data ingest list -b kb-reviews -o json \
  | jq '.rules[] | {ruleId, name, pipelineId, includePatterns, enabled}'

You should see two rules, both enabled: true, with overlapping include patterns (both will match **/*.json, **/*.txt, etc.). Each rule points at its own pipeline.

Why two rules instead of one shared rule? Each pipeline owns its destination, and rules carry pipeline_id. K3 dispatches one rule → one pipeline per match. To fan out to N pipelines, you need N rules. K3 generates them for you when you create pipeline-bound destinations via the facet routes (POST /:bucket/pipelines/table and POST /:bucket/pipelines/vector).

2. Upload a review

Stage some sample review JSON:


cat > review-001.json <<'EOF'
{
  "id": "REV-001",
  "customer_id": "cust-91872",
  "product_sku": "BAG-LEATHER-BROWN-01",
  "rating": 2,
  "title": "Hardware tarnished after one week",
  "body": "Bought this thinking it'd last. Within a week the gold-tone hardware started oxidizing. Customer service was slow and unhelpful. Disappointed for the price."
}
EOF
 
dodil data object create ./review-001.json -b kb-reviews -k reviews/2026/05/REV-001.json

Single upload, two pipelines fire in parallel.

3. Watch both ingest jobs


# Job from the Tables pipeline
dodil data ingest jobs -b kb-reviews -p "$TABLE_PIPELINE_ID" -o json \
  | jq '.jobs[] | {pipeline: "table", object: .object.key, status, rowsWritten}'
 
# Job from the Vector pipeline
dodil data ingest jobs -b kb-reviews -p "$VECTOR_PIPELINE_ID" -o json \
  | jq '.jobs[] | {pipeline: "vector", object: .object.key, status, chunksCreated, embeddingsWritten}'

You should see one job from each pipeline for the same object key. The Tables job’s rows_written counts how many structured rows landed in reviews; the Vector job’s chunks_created / embeddings_written count what landed in reviews_vec.

Cross-check via the object’s pipelineStatuses:


dodil data object show reviews/2026/05/REV-001.json -b kb-reviews -o json \
  | jq '.pipelineStatuses[]'

Two entries, one per rule that fired.

4. Query the dashboard table

Once the Tables drain completes, the structured rows are queryable. First, describe the table to see the resolved schema (template-driven, lazy):


dodil data table describe reviews -b kb-reviews -o json \
  | jq '{
      tableName, version,
      columns: [.columns[] | {name, type}],
      rowCount
    }'

For review_analysis you’ll typically see columns like source_key, review_id, sentiment_score, sentiment_label, intent, topics, toxicity_score, keywords, extracted_at. Exact column names depend on the template’s ScriptContract.

Now run SQL — the kind of queries that drive a BI dashboard. This is an ordinary Postgres-wire table, so point whatever you already use at it:

psql


psql "postgresql://$DODIL_USER:$DODIL_TOKEN@pg.uk-lon-1.dodil.io:5432/kb-reviews?sslmode=require"

Python (psycopg)


import psycopg
 
conn = psycopg.connect(
    host="pg.uk-lon-1.dodil.io", port=5432, dbname="kb-reviews",
    user=DODIL_USER, password=DODIL_TOKEN,
    sslmode="require",     # the pg wire terminates TLS; never `prefer`
)
with conn.cursor() as cur:
    cur.execute("SELECT sentiment_label, COUNT(*) FROM reviews GROUP BY 1 ORDER BY 2 DESC")
    print(cur.fetchall())

dodil data


dodil data sql -b kb-reviews "SELECT sentiment_label, COUNT(*) AS n FROM reviews GROUP BY 1 ORDER BY n DESC"
 
# force the Postgres wire instead of the gateway gRPC
dodil data pg -b kb-reviews "SELECT count(*) FROM reviews"


-- Overall sentiment distribution
SELECT sentiment_label, COUNT(*) AS n
FROM reviews
GROUP BY sentiment_label
ORDER BY n DESC;
 
-- Top complaint topics
SELECT topic, COUNT(*) AS n
FROM (SELECT UNNEST(topics) AS topic FROM reviews WHERE sentiment_label = 'negative')
GROUP BY topic
ORDER BY n DESC
LIMIT 10;
 
-- Toxicity outliers — needs CX team review
SELECT source_key, sentiment_score, toxicity_score
FROM reviews
WHERE toxicity_score > 0.5
ORDER BY toxicity_score DESC;

There is no freshness knob to set. Reads are read-your-writes by default — the runtime decides with a frontier check. dodil data table query --freshness is a deprecated no-op that the CLI marks as ignored, and the request field is likewise disregarded by the plane.

5. Query the semantic-search collection

Semantic search complements the SQL dashboard: “find reviews similar to this one specific complaint” is something SQL keyword-matching does badly.

dodil data


dodil data search "hardware oxidized after a week" \
  -b kb-reviews --table reviews_vec --top-k 10

content comes back on every hit; the bucket is the path segment, not a body field.

You’ll get back reviews whose content semantically resembles “hardware tarnished after a week” — including ones that use different wording ("clasp oxidized", "finish wore off", etc.) that a SQL keyword search would miss.

6. The killer combo — JOIN-like pattern across pillars

The real power comes from using both pillars together. Two-step pattern:

Tables SQL to narrow by structured criteria (sentiment, toxicity, date, product SKU).
Vector search for semantic recall, intersected with the narrowed set.

The search route has no pre-filter. k3-api hands the search template a hardcoded empty filter, so “restrict this semantic query to these keys” is a client-side intersection, not a request field. Everything below reflects that.

A. Negative reviews semantically similar to a specific complaint


import os, psycopg, requests
 
K3 = "https://api.data.dodil.io"
HEADERS = {
    "Authorization": f"Bearer {os.environ['DODIL_TOKEN']}",
    "Content-Type": "application/json",
}
 
# Step 1 — SQL narrows the candidate set (ordinary Postgres wire)
with psycopg.connect(
    host="pg.uk-lon-1.dodil.io", port=5432, dbname="kb-reviews",
    user=DODIL_USER, password=DODIL_TOKEN, sslmode="require",
) as conn, conn.cursor() as cur:
    cur.execute("""
        SELECT source_key FROM reviews
        WHERE sentiment_label = 'negative'
          AND extracted_at > NOW() - INTERVAL '30 days'
    """)
    negative = {row[0] for row in cur.fetchall()}
 
# Step 2 — semantic recall, then intersect. Over-fetch so the intersection
# still leaves enough rows.
hits = requests.post(
    f"{K3}/kb-reviews/search/vector",
    headers=HEADERS,
    json={"text": "hardware quality issues",
          "collectionNames": ["reviews_vec"],
          "topK": 200},
).json()["results"]
 
matched = [h for h in hits if h["object"]["key"] in negative][:20]

The inverse composition — KNN first, then a SQL predicate — is a single statement if you go to the data plane instead: SELECT … FROM reviews WHERE sentiment_label = 'negative' ORDER BY emb <=> '[…]' LIMIT 20. See Converging Engines.

B. Triage flow — surface negative-and-toxic reviews

For a CX-team notification pipeline. Note that table SQL does not live on the control plane — POST /:bucket/tables/_query was removed along with the rest of the /:bucket/tables/* data surface. Table DATA, DDL and ad-hoc SQL belong to the tables-gateway, so reach it over the Postgres wire (or POST /v1/databases/{db}/sql/query on the tables HTTP door):


import os, psycopg, requests
 
K3 = "https://api.data.dodil.io"
HEADERS = {
    "Authorization": f"Bearer {os.environ['DODIL_TOKEN']}",
    "Content-Type": "application/json",
}
 
with psycopg.connect(
    host="pg.uk-lon-1.dodil.io", port=5432, dbname="kb-reviews",
    user=DODIL_USER, password=DODIL_TOKEN, sslmode="require",
) as conn, conn.cursor() as cur:
    # No freshness argument — reads are read-your-writes by default.
    cur.execute("""
        SELECT source_key, sentiment_score, toxicity_score
        FROM reviews
        WHERE toxicity_score > 0.4
          AND sentiment_score < -0.5
          AND extracted_at > NOW() - INTERVAL '1 day'
    """)
    toxic = cur.fetchall()
 
for source_key, sentiment, toxicity in toxic:
    # For each toxic review, find similar ones for context. The route takes
    # text or an uploaded file — not a stored object key — so query by the
    # review's own text and drop the self-hit.
    similar = requests.post(
        f"{K3}/kb-reviews/search/vector",
        headers=HEADERS,
        json={"text": "hardware quality issues",
              "collectionNames": ["reviews_vec"],
              "topK": 4},
    ).json()
 
    print(f"TOXIC ALERT: {source_key} (sentiment={sentiment}, toxicity={toxicity})")
    for s in similar["results"]:
        if s["object"]["key"] != source_key:
            print(f"  similar: {s['object']['key']} (score={s['score']:.3f})")

This is what makes K3 different from “object storage with embeddings on the side” — the same upload feeds both structured analysis and semantic recall, queryable side-by-side.

7. Operational maintenance

Replay one pipeline without affecting the other

The two pipelines are independent — replay flows scope to one at a time:


TABLE_RULE_ID=$(dodil data ingest list -b kb-reviews -p "$TABLE_PIPELINE_ID" -o json | jq -r '.rules[0].ruleId')
VECTOR_RULE_ID=$(dodil data ingest list -b kb-reviews -p "$VECTOR_PIPELINE_ID" -o json | jq -r '.rules[0].ruleId')
 
SOURCE_ID=$(dodil data source list -b kb-reviews -o json \
  | jq -r '.sources[] | select(.name == "internal") | .sourceId')
 
# Replay only the Tables pipeline (e.g. after a template version bump)
dodil data ingest trigger -b kb-reviews -s "$SOURCE_ID" --rule "$TABLE_RULE_ID" --retry-failed

See Pipelines → Replay & Retry for the full replay matrix.

Disable one side without dropping data


# Stop semantic-indexing but keep dashboard ingest flowing
dodil data ingest update "$VECTOR_RULE_ID" -b kb-reviews --enabled=false

The Vector collection retains its existing rows; new uploads only feed Tables until you re-enable.

Common gotchas

Symptom	Cause	Fix
Only ONE pipeline fires on upload	The other rule’s globs don’t match the path/extension	List both rules’ `includePatterns`; both should cover your upload paths. Update the missing one via `dodil data ingest update --include "*/.json"`
Tables shows row but Vector hasn’t indexed yet (or vice versa)	Pipelines run async + independently — different latency per template	Normal. Re-query after the slower pipeline drains. Inspect `dodil data object show … -o json
Re-uploading the same key creates duplicate rows in Tables	Every PUT (including overwrites) fires the rule	`review_analysis` already declares `warehouse_mode: "merge"` with `merge_keys: ["input_ref"]`, so a re-run of the same object merges rather than appends. If you see duplicates, the merge key isn’t stable across runs. For Vector there is no PK dedup — embeddings accumulate
`source_key` in the table doesn’t match the Vector collection’s `source_key` metadata	Templates emit metadata using their own schema — the field names may differ slightly	Cross-reference using the object key (always present as `object.key` in vector results and as `source_key` in table rows by convention)
One pipeline keeps failing while the other succeeds	Schema mismatch / template-specific input requirement	Inspect the failing pipeline’s jobs via `dodil data ingest jobs -p $PIPELINE_ID`; read `errorDetails`. See Pipelines → Replay & Retry for diagnosis patterns

Cleanup


# Pause both rules
dodil data ingest update "$TABLE_RULE_ID" -b kb-reviews --enabled=false
dodil data ingest update "$VECTOR_RULE_ID" -b kb-reviews --enabled=false
 
# Delete in dependency-friendly order (no cascade across pillars)
dodil data ingest delete "$TABLE_RULE_ID" -b kb-reviews
dodil data ingest delete "$VECTOR_RULE_ID" -b kb-reviews
 
# Both destinations are pipelines — `pipeline delete` takes either id
dodil data pipeline delete "$TABLE_PIPELINE_ID" -b kb-reviews
dodil data pipeline delete "$VECTOR_PIPELINE_ID" -b kb-reviews
 
# The table rows survive the pipeline; drop them separately
dodil data table delete reviews -b kb-reviews
 
dodil data bucket delete kb-reviews

Reviews Dashboard + Semantic Search

Prerequisites

1. Create the two destinations

A. Pipeline-bound table for sentiment

B. Pipeline-mode vector collection for semantic recall

2. Upload a review

3. Watch both ingest jobs

4. Query the dashboard table

psql

Python (psycopg)

dodil data

5. Query the semantic-search collection

dodil data

curl

6. The killer combo — JOIN-like pattern across pillars

A. Negative reviews semantically similar to a specific complaint

B. Triage flow — surface negative-and-toxic reviews

7. Operational maintenance

Replay one pipeline without affecting the other

Disable one side without dropping data

Common gotchas

Cleanup

See also