Nimble Databricks Data Products

Nimble on Databricks — data products builder

Turn a natural-language brief like pricing analysis on dog products from walmart and amazon into working Databricks data products: discover agents → ingest live web search data into Delta → build dashboard and/or app → deliver links. Equally at home for a quick demo or a real, reusable data product.

You are the orchestrator. Databricks mechanics are delegated to the official databricks-* skills (see references/databricks-skills.md); this skill owns the Nimble glue and the gaps (agent discovery, ingestion-from-agents, the AI/BI dashboard JSON, branding).

Golden rules

• Discover, don't assume. Read agent names via nimble_agent_list(), input params via

nimble_agent_describe('<agent>'), and output fields by probing one call (to_json(parsing[0])) — never hardcode from memory (Amazon search takes keyword, not query).

• Probe before fanning out. Run one call per source first to learn its localization flag, field

names, and value formats — sources differ (some return numeric prices, others currency strings).

• One statement per Statements API call. Multiple ;-separated statements in one call are a parse error.
• Each Bash call is a fresh shell. Env vars and cd don't persist — set them inline. See references/preflight.md.
• Fail fast, then confirm. Run Phase 0 preflight first; recommend a warehouse + writable schema, then confirm before writing.
• Always ask the deliverable. Table / +dashboard / +app is a per-run choice.
• Branding is always on, neutral. "Powered by Nimble" + light theme + yellow accent. See references/branding.md.
• Leave artifacts in place. No teardown.
• Show your work and the headline. End with URLs and the one-sentence insight (e.g. the price gap).

Workflow

Track these as todos so nothing is skipped.

Phase 0 — Preflight (read-only, fail fast)

Lean on the `databricks-core` skill for the generic checks. 1. databricks current-user me → confirm auth; capture the username (for the default schema). 2. Find a RUNNING SQL warehouse: databricks warehouses list. Prefer one already RUNNING; if none, offer to start one. 3. Integration gate — confirm these exist: nimble_integration.tools.{nimble_search, nimble_extract, nimble_agent_run, nimble_agent_list, nimble_agent_describe}. Quick check: databricks functions list nimble_integration tools. If missing → STOP and walk the user through references/install-nimble-integration.md (Nimble cookbook). Do not try to auto-install. 4. Recommend + confirm the target: a warehouse and a writable catalog.schema (default users.<username>). Verify writability — some shared catalogs deny CREATE TABLE. Present the recommendation and let the user confirm or override before writing.

Details + exact commands: references/preflight.md.

Phase 1 — Interpret the brief + clarify (AskUserQuestion)

Parse the brief into: domain/entity · search terms · sources · analysis goal. Then ask (batch into one AskUserQuestion call):

• Deliverable — always ask: table / table + dashboard / table + dashboard + app.
• Sources — confirm the agents you matched (e.g. Amazon + Walmart SERP).
• Volume — default ~8–10 search terms, ~100+ rows/source.

Keep the brief's intent (the "analysis goal") — it picks the Phase 4 template and the headline.

Phase 2 — Discover agents + map a unified schema

See references/nimble-agents.md. 1. nimble_agent_list() via SQL, filter by the source/domain keywords. 2. For each chosen agent: nimble_agent_describe('<name>') → read its input params (required ones, exact names, localization/pagination flags). Output fields come from the §2.5 probe, not here. 3. Design one unified table with a source column + a normalized core (product_name, price, currency, rating, review_count, brand, url, …), keeping only fields the chosen agents actually emit. Multi-source comparison hinges on the shared columns.

Phase 3 — Ingest (control table + one set-based call)

See references/nimble-agents.md for the full SQL. Drive ingestion from a control table, not per-keyword files — it's reproducible and expandable (add a row, re-run). 0. Probe ONE call per source first (fail fast). Before fanning out, run a single nimble_agent_run per source and check: status, the real field names, the localization flag, and whether a price casts cleanly. This catches the Walmart-class surprises (localization, currency- string prices, product_price vs price) in ~40s instead of after a wasted full round. Highest- leverage step — see nimble-agents.md §2.5. 1. Create a control (queries) table <schema>.<table>_queries (source, agent, keyword, params_json, localization, enabled) and seed one row per (source × term). params_json uses each agent's real param name (from input_properties); set localization per agent (e.g. amazon_serp true, walmart_serp false). 2. Create the unified results table (source column + normalized core + raw VARIANT). 3. Run one INSERT that calls nimble_agent_run(q.agent, q.params_json, q.localization) via a correlated LATERAL join over the control table, with a /*+ REPARTITION(N) */ hint (N ≈ enabled rows, kept modest — high parallelism can trip API rate limits) so the agent calls run in parallel. It's one long statement → run it async with bash scripts/ingest.sh <WH> ingest.sql. 4. Reconcile against the control table (LEFT JOIN): a term that lands no items returns an empty result, and a correlated LATERAL drops empty rows — so reconcile to confirm every source is covered. If a source shows 0, re-check its localization flag (per-agent) and casts before building; see nimble-agents.md §6 for the diagnostic order.

Phase 4 — Build the deliverable(s)

Choose a template from the matched agents' vertical/entity_type:

Comparison depth (hybrid): always build the aggregate/category comparison; additionally try best-effort item-level matching across sources (normalize brand + key tokens). If confident matches exist, add a "same-product price gap" view; otherwise keep the aggregate comparison and note that item-level matching wasn't confident.

• Dashboard → use scripts/build_dashboard.py (compact spec → valid serialized_dashboard,

create + publish). It bakes in every Lakeview gotcha. Read references/dashboard-cookbook.md for the spec format and recipes.

• App → follow references/app-cookbook.md (delegates scaffold/deploy to databricks-apps;

adds the Nimble-specific SQL, branding, and the numeric-string / light-mode gotchas).

• Branding → references/branding.md (always applied).

Phase 5 — Verify, deliver & share

• Publish the dashboard / confirm the app is RUNNING; collect URLs.
• Summarize what was built and the headline insight (the comparison takeaway).
• Offer to share the dashboard/app link — if a Slack or Notion connector is available, offer to

post it there (Slack = the link + headline; Notion = a short dated page). Mention once; don't nag.

• Suggest next steps with sibling skills, e.g. competitor-intel / company-deep-dive for

business signals on the brands surfaced, or nimble-web-expert for a one-off deeper pull.

• Offer iterations (more charts, item-level matching, theming, a scheduled refresh job).

Reference map

• references/databricks-skills.md — which official databricks-* skill to use per phase.
• references/install-nimble-integration.md — setup when the integration gate fails.
• references/preflight.md — auth, warehouse, writable-schema discovery (exact commands).
• references/nimble-agents.md — discovery, schema mapping, ingestion SQL + gotchas.
• references/dashboard-cookbook.md — Lakeview JSON recipes + every gotcha (authoritative).
• references/app-cookbook.md — AppKit demo app glue + gotchas.
• references/branding.md — "Powered by Nimble", logo, colors.
• scripts/ingest.sh — async statement fan-out + poll.
• scripts/build_dashboard.py — compact spec → create + publish a dashboard.
• assets/nimble-logo.png — the Nimble mark for app branding.

Databricks App cookbook (AppKit analytics demo)

Scaffolding/deploy mechanics belong to the official `databricks-apps` / `databricks-app-design` skills — follow them. This file adds the Nimble-specific glue and the gotchas that cost real time.

Use an app (vs. just a dashboard) when the user wants an interactive, branded, sharable web UI.

Flow

1. Manifest → confirm the analytics plugin + its required field: databricks apps manifest (analytics requires analytics.sql-warehouse.id). 2. Init (warehouse must be RUNNING):

   databricks apps init --name <app-name> \
     --description "<brief> · Powered by Nimble" \
     --features analytics \
     --set analytics.sql-warehouse.id=<WH> \
     --run none

Name ≤26 chars, lowercase/hyphens/numbers. 3. SQL queries → put .sql files in config/queries/. Each is a query key. Use :param placeholders annotated with -- @param name TYPE:

   -- @param keyword STRING
   -- @param source STRING
   SELECT product_name, source, price, rating, review_count, product_url
   FROM users.<me>.<table>
   WHERE (:keyword = '' OR search_keyword = :keyword)
     AND (:source  = 'all' OR source = :source)
   ORDER BY review_count DESC NULLS LAST
   LIMIT 600

Keep result sets < 1 MB (LIMIT / aggregate) or the analytics endpoint errors. After writing them: npm run typegen (needs the warehouse running) → generates shared/appkit-types/analytics.d.ts. Read those generated types and use the exact field names in the UI. 4. UI (client/src/…) — build a single Explorer page driven by filters; see the patterns below. 5. Branding — see references/branding.md (logo into client/public/, header + footer). 6. Validate → databricks apps validate (typecheck + lint + build + smoke test). Fix all. 7. Deploy → databricks apps deploy → wait for app_status: RUNNING; the URL is printed. Confirm: databricks apps get <app-name> -o json | jq '{url, app:.app_status.state}'.

UI patterns (AppKit / @databricks/appkit-ui/react)

• Data: const { data, loading, error } = useAnalyticsQuery('query_key', params).

`params` MUST be `useMemo`'d or the hook refetch-loops: const params = useMemo(() => ({ keyword: sql.string(kw), source: sql.string(src) }), [kw, src]). Import helpers: import { sql } from '@databricks/appkit-ui/js'.

• Charts auto-fetch via queryKey+parameters; pick axes with xKey/yKey:

<BarChart queryKey="by_keyword" parameters={p} xKey="keyword" yKey="listings" orientation="horizontal" height={320} />. Available: BarChart, LineChart, AreaChart, ScatterChart, PieChart, DonutChart, HeatmapChart, RadarChart. (xKey/yKey, orientation, colors, height, showLegend are common props.)

• Tables: DataTable auto-fetches + filters/sorts/paginates from a queryKey. For a custom

cell (e.g. an "Open ↗" anchor), build a small table with the Table… primitives + a client-side search Input + sort Select over the useAnalyticsQuery data.

Gotchas (these bit us in the POC)

1. Numbers arrive as strings. The analytics JSON serializes DOUBLE/BIGINT/INT as strings, even though the generated types say number. Calling .toFixed() or doing arithmetic crashes the page (x.toFixed is not a function). Coerce everything: a toNum(v)=>{const n=Number(v); return Number.isFinite(n)?n:null} helper, used in every formatter, sort comparator, and .toFixed(). 2. Force light mode (branding is neutral-light): set <html lang="en" class="light"> in client/index.html. AppKit applies dark via a :root:not(.light) media query, so the light class disables it. All components use semantic tokens, so nothing else needs changing. 3. Update the smoke test. tests/smoke.spec.ts ships asserting the template's home page. After you replace the UI, update its selectors to match your page (unique headings, KPI labels, a known placeholder). Use real Playwright locators (getByRole, getByText, getByPlaceholder); avoid ambiguous getByText that matches multiple nodes (add .first() or use unique strings). If you keep multiple routes, the template's home/heading checks must still pass or validate fails. 4. Don't override @databricks/appkit versions; don't use custom endpoints for SELECTs (use config/queries/); don't use useAnalyticsQuery for non-warehouse data.

Minimal query set for an ecommerce comparison demo

overview_kpis.sql (params keyword, source) · by_keyword.sql · by_source.sql (avg price/rating per source) · price_vs_rating.sql · products.sql (the table) · keywords.sql (filter options). Mirror the column names from the unified ingest table.

Branding — "Powered by Nimble" (always on, neutral)

Branding is always applied (no flag). The look is neutral light: clean light UI, Nimble yellow used only as an accent — never as the page background.

Tokens

• Nimble yellow #F2F23B — accent only (links, highlights, primary chart series, the logo tile).
• Black #0A0A0A — text and the logo mark.
• Background white / near-white. Light theme everywhere.
• Logo asset: assets/nimble-logo.png (the black "N" mark on yellow). Bundled with this skill.

In a Databricks App

1. Copy the logo into the app's public dir: cp <skill>/assets/nimble-logo.png <app>/client/public/nimble-logo.png 2. Force light mode: <html lang="en" class="light"> in client/index.html. 3. Add a small reusable component and place it in the header (top-right) and the footer:

   function PoweredByNimble({ className = '' }: { className?: string }) {
     return (
       <a href="https://www.nimbleway.com" target="_blank" rel="noopener noreferrer"
          className={`inline-flex items-center gap-2 group ${className}`} aria-label="Powered by Nimble">
         <span className="text-xs font-medium text-muted-foreground group-hover:text-foreground">Powered by</span>
         <img src="/nimble-logo.png" alt="Nimble" className="h-6 w-6 rounded-[5px] shadow-sm" />
         <span className="text-sm font-semibold text-foreground">Nimble</span>
       </a>
     );
   }

4. Optional accent: set the primary chart series / link color to #F2F23B where it reads well on light. Keep contrast legible (yellow text on white is unreadable — use black text, yellow fills).

In an AI/BI dashboard

• Prefix the dashboard display_name and the top text widget with the mark + "Powered by Nimble"

(e.g. "🐶 Dog Products: Amazon vs Walmart · Powered by Nimble").

• Add a markdown text widget at the top: _Live web search · **Powered by Nimble**_.
• To accent charts yellow, set series color 1 to #F2F23B (the compact spec uses the default palette;

add colors per-chart only if asked — neutral default is fine).

• On AI/BI dashboards, render the brand as the title/text wordmark (the dependable option there);

use the logo image in the app, where it displays from client/public/.

Tone

Branding should feel like a tasteful "made with" credit, not a takeover. Neutral, professional, yellow as a spark — not a yellow wall.

AI/BI (Lakeview) dashboard cookbook — AUTHORITATIVE

There is no official Databricks skill for AI/BI dashboards, so this file + scripts/build_dashboard.py are the source of truth. Prefer the script — it bakes in every gotcha below. Hand-rolling the serialized JSON is the #1 source of broken dashboards.

TL;DR workflow

1. Write a compact spec JSON (datasets + widgets) — see the format in scripts/build_dashboard.py's header. 2. python3 scripts/build_dashboard.py --spec spec.json → it creates + publishes and prints the URLs. 3. To revise: edit the spec, rerun with --dashboard-id <id> (it PATCHes + republishes).

The script handles: counter top-level-aggregate rule, one-line queryLines, full table column objects, link columns, filter associativity, create/patch/publish, and URL building.

Spec quick reference (what you write)

{
  "display_name": "🐶 Amazon vs Walmart — Dog Products  ·  Powered by Nimble",
  "warehouse_id": "<WH>",
  "datasets": [{"name": "main", "query": "SELECT * FROM users.me.dog_products_compare"}],
  "widgets": [ /* text, filter, counter, bar, line, area, scatter, pie/donut, table */ ]
}

• pos [x, y, w, h] on a 6-column grid. Lay rows top-to-bottom.
• Field shorthand: a string = a column (categorical dimension); {"expr":"AVG(\price\)"} = a measure.

Recipes (per widget)

Title + branding text (always include — branding is on):

{"type":"text","md":"# 🐶 Dog Products: Amazon vs Walmart\n_Live web search · **Powered by Nimble**_","pos":[0,0,6,1]}

Global filters (put a couple near the top; they slice every widget on the SAME dataset):

{"type":"filter","dataset":"main","field":"source","title":"Source","select":"single","pos":[0,1,2,1]}
{"type":"filter","dataset":"main","field":"search_keyword","title":"Keyword","select":"multi","pos":[2,1,2,1]}

KPI counters (one measure each):

{"type":"counter","dataset":"main","label":"Listings","expr":"COUNT(`product_name`)","pos":[0,2,2,3]}
{"type":"counter","dataset":"main","label":"Avg Price","expr":"AVG(`price`)","format":"currency","pos":[2,2,2,3]}
{"type":"counter","dataset":"main","label":"Avg Rating","expr":"AVG(`rating`)","format":"number","decimals":2,"pos":[4,2,2,3]}

Bars (grouped; horizontal reads well for many categories):

{"type":"bar","dataset":"main","x":"search_keyword","y":{"expr":"COUNT(`product_name`)"},"orientation":"horizontal","title":"Listings per keyword","pos":[0,5,3,6]}
{"type":"bar","dataset":"main","x":"source","y":{"expr":"AVG(`price`)"},"color":"source","title":"Avg price by source","pos":[3,5,3,6]}

Scatter (raw points; great for price vs rating):

{"type":"scatter","dataset":"main","x":"price","y":"rating","color":"source","size":"review_count","pos":[0,11,3,7]}

Pie / donut (share):

{"type":"pie","dataset":"main","color":"source","angle":{"expr":"COUNT(`product_name`)"},"title":"Listings share by source","pos":[3,11,3,7]}

Table with clickable links (lead with the Open link):

{"type":"table","dataset":"main","title":"Products (sortable / searchable)","pos":[0,18,6,9],
 "columns":[
   {"field":"product_url","title":"Open","link":true},
   {"field":"product_name","title":"Product"},
   {"field":"source","title":"Source"},
   {"field":"search_keyword","title":"Keyword"},
   {"field":"price","title":"Price","kind":"number","number_format":"$0,0.00"},
   {"field":"rating","title":"Rating","kind":"number","number_format":"0.0"},
   {"field":"review_count","title":"Reviews","kind":"integer"},
   {"field":"sponsored","title":"Ad","kind":"boolean"}
 ]}

The gotchas (why the script exists)

1. Counter expression must be a top-level aggregate. AVG(\price\) works; ROUND(AVG(\price\),2) makes AI/BI treat the field as a GROUP BY dimension → the tile shows "No data." Round via the display format/decimals, never in the SQL expression. (The script warns if it sees a wrapper.) 2. `queryLines[]` are joined with NO whitespace. A multi-line dataset query welds tokens (avg_ratingFROM…) → parse error. Keep each dataset query on one line (the script collapses whitespace for you). 3. Tables need the full shape or show "no fields selected." Required spec-level keys: itemsPerPage, paginationSize, invisibleColumns, withRowNumber, condensed, allowHTMLByDefault; and every column needs displayName, order, visible, type, displayAs (+ link/image/boolean templates). The script emits all of this. 4. Clickable URL = column displayAs:"link" + linkUrlTemplate:"{{ @ }}" (+ linkTextTemplate). There is a real "link" display type (cached dashboards rarely use it, but it's valid). 5. Filters only span widgets on the same dataset, and the filter query needs the magic associativity field COUNT_IF(\associative_filter_predicate_group\). Keep all filtered widgets on one dataset (e.g. SELECT *) and let widgets aggregate via disaggregated:false. 6. Pre-aggregated datasets break global filters. If you must cap a chart (e.g. top-15 brands via a separate GROUP BY … LIMIT 15 dataset), know that the shared filters won't reach it — note that to the user, or keep it on the main dataset. 7. Lifecycle: create POST /api/2.0/lakeview/dashboards; update PATCH …/{id} (needs current etag); make it live POST …/{id}/published with {"embed_credentials":true}. URL: https://<host>/dashboardsv3/<id>/published.

Branding on dashboards

• Prefix display_name and the title text widget with the Nimble mark/“Powered by Nimble.”
• Pass "colors": ["#F2F23B", …] is not in the compact spec by default; if you want the yellow

accent as series color 1, add it to a chart's spec via references/branding.md guidance (or leave default palette — neutral is fine).

Delegation map — official Databricks agent skills

This skill is not a Databricks tutorial. For generic Databricks mechanics, use the official skills and follow their guidance. Repo: <https://github.com/databricks/databricks-agent-skills/tree/main/skills> (install with databricks aitools install if they aren't present locally).

How to use this in practice: when a phase needs a generic Databricks operation (e.g. "start a warehouse", "scaffold an app", "create a bundle"), consult the mapped skill rather than improvising. Reserve your own effort for the Nimble-specific steps, the dashboard JSON, and branding — that is where this skill's value lives.

If a mapped skill is not installed, tell the user how to get it (databricks aitools install) and proceed with the inline commands in this skill's references as a fallback.

Installing the Nimble × Databricks integration

Use this only when the Phase 0 integration gate fails — i.e. nimble_integration.tools.{nimble_search, nimble_extract, nimble_agent_run, nimble_agent_list, nimble_agent_describe} don't exist. Do not auto-install; walk the user through it (or point them at the cookbook) and stop until it's done.

Authoritative source: Nimble cookbook for Databricks — <https://github.com/Nimbleway/cookbook/tree/main/databricks>

What it sets up

Querying live web search data directly from SQL via Nimble APIs/agents, with results landing as governed Delta tables in Unity Catalog. It creates the nimble_integration catalog with a tools schema holding five table functions: nimble_search, nimble_extract, nimble_agent_list, nimble_agent_describe, nimble_agent_run.

Prerequisites (call these out — they matter)

• A serverless SQL warehouse with outbound networking enabled: turn on the preview

"Enable networking for isolated workloads in Serverless SQL Warehouses" and do a cold restart. This lets the warehouse make the outbound call to Nimble's API endpoint.

• Permission to create catalogs/schemas and CREATE FUNCTION.
• A Nimble API key: <https://online.nimbleway.com/account-settings/api-keys>
• Databricks CLI v0.205+ authenticated (databricks auth login).

Steps (from the cookbook)

# 1) Store the API key in a secret scope named `nimble`
databricks secrets create-scope nimble
databricks secrets put-secret nimble api_key   # paste token, then Ctrl-D
databricks secrets put-acl nimble users READ

# 2) Identify a serverless warehouse
WH=<your-warehouse-id>            # from: databricks warehouses list

# 3) Deploy catalog + schemas
python3 databricks/helpers/deploy_sql.py --file databricks/01_setup.sql --warehouse "$WH"

# 4) Install the table functions
for f in databricks/tools/*.sql; do
  python3 databricks/helpers/deploy_sql.py --file "$f" --warehouse "$WH"
done

(Clone the cookbook repo first so the databricks/... paths resolve.)

Verify

SELECT count(*) AS n FROM nimble_integration.tools.nimble_search('AI agents news', 5);
SELECT length(content) FROM nimble_integration.tools.nimble_extract('https://www.nimbleway.com');

Both should return non-zero. Once verified, return to Phase 0 and continue.

Optional — register with Genie

The cookbook includes databricks/helpers/create_genie_space.py to expose the five functions to a Genie space. Not required for this skill's dashboard/app demos.

Nimble agents — discover, introspect, ingest

This is the heart of the skill: find the right agents, learn their exact I/O at runtime, and load their output into a Delta table. Never hardcode an agent's params or output from memory — read them live. (Example trap: Amazon search wants keyword, not query.)

1. Discover agents

*Agent names in this file (`amazon_serp`, `walmart_serp`, `zillow_`, …) are illustrative only.**

The catalog evolves — always discover the actual names at runtime with nimble_agent_list() and

introspect with nimble_agent_describe. Never depend on a hardcoded name.

nimble_agent_list() returns one row per agent: name, display_name, description, vertical, entity_type, domain, managed_by, is_public. Query it via SQL:

SELECT name, display_name, vertical, entity_type, domain
FROM nimble_integration.tools.nimble_agent_list()
WHERE lower(domain) LIKE '%amazon%' OR lower(name) LIKE '%amazon%'
ORDER BY name;

Match the brief's sources (amazon, walmart, zillow, instagram, google_maps, …) against name/domain, and pick the entity_type that fits the goal:

• SERP (search results) → best for "find products by keyword" / assortment / pricing.
• PDP (product detail) → deep per-URL detail; needs URLs as input.
• CLP / best_sellers → category/ranking pages.

For most "analysis on X from <retailers>" briefs, *`_serp`** is the right call (keyword in → many product rows out).

2. Introspect the chosen agents — read the INPUTS

Read each chosen agent's input parameters at runtime with nimble_agent_describe (one row per param) — never hardcode them:

SELECT param_name, required, type, is_localization_param, is_pagination_param, default_value, examples_json
FROM nimble_integration.tools.nimble_agent_describe('amazon_serp')
ORDER BY required DESC;

• The required param is your search term — e.g. keyword, not query. Use its exact

param_name when you build params_json in §3/§4.

• `is_localization_param` flags the localization input (e.g. zip_code) and `is_pagination_param`

the pagination input (e.g. page). default_value / examples_json give sane starting values.

Do this for every chosen source — param names differ across agents.

Output fields come from a probe, not from `describe`. nimble_agent_describe returns inputs

only (by design — output schemas are large and best seen from a real call). Learn the emitted

fields by running the agent once and inspecting the payload — see §2.5 (to_json(parsing[0])).

Field names differ across retailers even within a vertical (Amazon emits price/rating, Walmart

emits product_price/product_rating); §4 coalesces the variants into one normalized column.

2.5 Probe ONE call per source before fanning out (fail fast)

This is the highest-leverage check in the whole skill. Before seeding the control table and running all N calls, run one nimble_agent_run per source and inspect three things — it surfaces the exact Walmart-class surprises in ~40s instead of after a wasted full round:

SELECT status,
       to_json(parsing[0]) AS first_item,                       -- see the REAL field names
       parsing[0]:price, parsing[0]:product_price               -- which price field exists?
FROM nimble_integration.tools.nimble_agent_run('walmart_serp', to_json(named_struct('keyword','dog food')), true);

Decide three things from the probe, per source: 1. localization flag — localization is per-agent, not global. If the probe comes back with an empty parsing, flip the flag and probe again before concluding the term is empty — agents differ on whether they expect true or false. 2. field names — they vary by source (e.g. price vs product_price); note them for the coalesce in §4. 3. value format — sample a price. Some sources return a plain number; others return a currency-formatted string like "$125.99" (or an empty string), which a bare CAST(... AS DOUBLE) rejects with INVALID_VARIANT_CAST. Use the defensive cast in §4 so either shape works.

3. Two tables: a control table + the unified results table

Don't hand-write one SQL file per keyword. Drive everything from a control (queries) table so the demo is set-based, reproducible, and expandable — add a row, re-run, done.

-- Control table: one row per (source × search term). The single source of truth for what to scrape.
CREATE OR REPLACE TABLE <schema>.<table>_queries (
  source       STRING,   -- 'amazon' | 'walmart' | …
  agent        STRING,   -- the Nimble agent name, e.g. 'amazon_serp'
  keyword      STRING,   -- the search term (for labelling/inspection)
  params_json  STRING,   -- full params for nimble_agent_run, built from the agent's input_properties
  localization BOOLEAN,
  enabled      BOOLEAN
);

INSERT INTO <schema>.<table>_queries VALUES
  -- localization is PER-AGENT (from the §2.5 probe): amazon_serp=true, walmart_serp=false.
  ('amazon','amazon_serp','dog food',  to_json(named_struct('keyword','dog food')),  true,  true),
  ('walmart','walmart_serp','dog food',to_json(named_struct('keyword','dog food')),  false, true),
  ('amazon','amazon_serp','dog toys',  to_json(named_struct('keyword','dog toys')),  true,  true);
  -- … one row per (source × term). params_json uses each agent's REAL param name.

-- Results table: unified, with a source column + normalized core + a raw VARIANT catch-all.
CREATE OR REPLACE TABLE <schema>.<table> (
  source STRING, search_keyword STRING, position INT,
  product_name STRING, brand STRING, price DOUBLE, currency STRING,
  rating DOUBLE, review_count INT, sponsored BOOLEAN,
  product_url STRING, image_url STRING,
  raw VARIANT, ingested_at TIMESTAMP
) COMMENT 'Nimble demo — <brief>. Powered by Nimble.';

Adjust the results columns to the vertical (social → account, post_url, likes, …; real estate → address, price, beds, baths, sqft, …). Always keep source, raw, ingested_at. Keep sources in the same vertical so one normalized schema fits all of them.

4. One set-based ingest (lateral correlated UDTF)

A single INSERT runs the agent for every enabled control row and explodes the results — no per-keyword files. The agent name and params come from the control-table columns via a correlated LATERAL call (verified working on Databricks):

Two rules make this robust across retailers (learned the hard way — see the gotchas after the SQL):

• Coalesce field-name variants (price vs product_price) so one INSERT serves all sources.
• Defensive numeric casts — strip non-numerics before casting, because some retailers return

currency strings ("$125.99") or "". Use try_cast(regexp_replace(...)), never a bare CAST.

INSERT INTO <schema>.<table>
SELECT /*+ REPARTITION(8) */   -- ≈ number of enabled rows; keep modest (see note below)
  q.source,
  q.keyword AS search_keyword,
  try_cast(v.value:position AS INT),
  CAST(coalesce(v.value:product_name, v.value:title) AS STRING) AS product_name,
  initcap(split(trim(CAST(coalesce(v.value:product_name, v.value:title) AS STRING)), ' ')[0]) AS brand,
  -- defensive numeric cast: strip $, commas, etc. then try_cast (NULL on junk instead of erroring)
  try_cast(regexp_replace(CAST(coalesce(v.value:price, v.value:product_price) AS STRING), '[^0-9.]', '') AS DOUBLE) AS price,
  CAST(coalesce(v.value:currency, '$') AS STRING) AS currency,
  try_cast(regexp_replace(CAST(coalesce(v.value:rating, v.value:product_rating) AS STRING), '[^0-9.]', '') AS DOUBLE) AS rating,
  try_cast(regexp_replace(CAST(coalesce(v.value:review_count, v.value:ratings_count) AS STRING), '[^0-9]', '') AS INT) AS review_count,
  try_cast(v.value:sponsored AS BOOLEAN) AS sponsored,
  CAST(coalesce(v.value:product_url, v.value:url) AS STRING) AS product_url,
  CAST(coalesce(v.value:image_url, v.value:image) AS STRING) AS image_url,
  v.value AS raw,
  current_timestamp()
FROM <schema>.<table>_queries q,
LATERAL nimble_integration.tools.nimble_agent_run(q.agent, q.params_json, q.localization) AS r,
LATERAL variant_explode(r.parsing) AS v
WHERE q.enabled AND r.status = 'success';

Adjust the coalesced field names to whatever §2.5 actually showed for your sources — these are examples, not a fixed list.

Key points:

• *`/+ REPARTITION(N) /` spreads the agent calls across N Spark tasks so they run *in

parallel. Without it, a tiny control table sits in one partition and the calls run serially (N × ~40s). Set N ≈ the number of enabled rows, and keep it modest** — each task is a live agent call, so very high parallelism can trip API rate limits (HTTP 429). A couple dozen is plenty; if you have hundreds of terms, batch them across runs rather than firing all at once.

• It's still one long-running statement, so submit it async and poll — don't use a 50s

wait_timeout. Use the helper: bash scripts/ingest.sh <WH> ingest.sql.

• A bare CAST(v.value:price AS DOUBLE) throws INVALID_VARIANT_CAST the moment a retailer returns

a formatted string — the try_cast(regexp_replace(...)) form is harmless on already-numeric data (Amazon) and saves a whole wasted round on string-formatted data (Walmart).

To expand later: INSERT INTO <table>_queries VALUES (…) more rows, then re-run the ingest (optionally guard with WHERE q.enabled AND <not already scraped>). That's the whole point of the control table — no new files, no edits to the ingest SQL.

5. Run it (async, one statement)

bash scripts/ingest.sh "$WH" ingest.sql      # submits the INSERT async, polls to completion

6. Verify against the control table — confirm every source is covered

Always reconcile results against the control table so you know each source landed data: 1. The UDTF returns an empty result set for a term that yields no items (rather than an error row). 2. A correlated inner LATERAL drops any control row that produced no items — so an empty source won't appear in the output unless you reconcile against the control table.

SELECT q.source, q.keyword, COALESCE(r.n, 0) AS rows
FROM (SELECT source, keyword FROM <schema>.<table>_queries WHERE enabled) q
LEFT JOIN (SELECT source, search_keyword AS keyword, COUNT(*) n
           FROM <schema>.<table> GROUP BY source, search_keyword) r
  USING (source, keyword)
ORDER BY rows;     -- any 0 = that (source,keyword) landed no items

Diagnostic when one source has 0 rows but another is healthy. Work through these in order before concluding the term itself is empty: 1. localization — the flag is per-agent; flip it for that source and re-run (the §2.5 probe should have settled this up front). This is the most common cause. 2. cast failure — a bare CAST on a currency-string price ("$125.99") aborts the INSERT; switch to the defensive try_cast(regexp_replace(...)) from §4. 3. field-name mismatch — the source uses product_price/title etc.; widen the coalesce. 4. otherwise — use a sibling agent for that source (e.g. a PDP agent over discovered URLs), or proceed with the sources that returned data and tell the user which one had no coverage for these terms.

Re-run is cheap: fix the control row (e.g. flip localization) or the cast, then re-run the ingest — optionally DELETE FROM <table> WHERE source = '<that source>' first so you don't double-count.

Search-term expansion

If the brief names a domain but not terms (e.g. "dog products"), expand to ~8–10 sensible subcategories (dog food, treats, toys, beds, leashes, collars, crates, harness) and confirm the list with the user in Phase 1 — then seed the control table with them.

Phase 0 — Preflight (exact commands)

Goal: confirm everything works before writing anything, then recommend a target and let the user confirm. Generic Databricks bits defer to the `databricks-core` skill.

1. Auth + identity

databricks current-user me -o json | jq '{user: .userName, active}'

Capture the username — the default write target is users.<username> (dots/@ become part of the schema name as-is, e.g. users.jane_doe).

2. A running SQL warehouse

databricks warehouses list -o json | jq -r '.[] | "\(.id)\t\(.state)\t\(.name)"'

Prefer a warehouse already RUNNING (no cold-start wait). If none is running, offer to start one: databricks warehouses start <id> (then poll until RUNNING). Hold the id as $WH.

3. Integration gate

databricks functions list nimble_integration tools -o json \
  | jq -r '.[].name' | grep -E '^(nimble_search|nimble_extract|nimble_agent_run|nimble_agent_list|nimble_agent_describe)$'

You want all five public wrappers present. If the catalog/schema or functions are missing → STOP and go to install-nimble-integration.md. Don't try to install it yourself.

Sanity-ping the live path (optional, ~few s):

databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id":"'"$WH"'","catalog":"nimble_integration","schema":"tools",
  "statement":"SELECT count(*) n FROM nimble_search(\"hello\", 3)","wait_timeout":"30s"}' \
  | jq '.status.state'

4. Recommend + confirm the target

Pick a default and verify writability rather than assuming — in the POC, CREATE TABLE was denied on a shared schema while users.<username> worked.

# Probe write permission with a throwaway table. ONE statement per call — the Statements API rejects
# multiple ';'-separated statements with a parse error, so CREATE and DROP are two separate calls.
databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id":"'"$WH"'",
  "statement":"CREATE TABLE IF NOT EXISTS users.<username>._nimble_probe (x INT)",
  "wait_timeout":"30s"}' | jq '{state:.status.state, err:.status.error.message}'
databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id":"'"$WH"'",
  "statement":"DROP TABLE IF EXISTS users.<username>._nimble_probe",
  "wait_timeout":"30s"}' | jq -r '.status.state'

If the CREATE fails, fall back to another schema the user owns. Then present the recommendation:

"I'll use warehouse `<name>` (`$WH`) and write to `users.<username>`. OK, or override?"

Only proceed to ingestion after the user confirms.

Running SQL throughout this skill

Use the Statements API and read .result.data_array:

databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id":"'"$WH"'","statement":"<SQL>","wait_timeout":"50s"}'

wait_timeout max is 50s. For longer work (agent calls), submit async ("wait_timeout":"0s") and poll GET /api/2.0/sql/statements/<id> — that's what scripts/ingest.sh does.

Two environment rules that bite:

• One statement per Statements API call. "statement" must contain a single SQL statement;

CREATE …; DROP … in one call is a parse error. Loop in bash to run several.

• Each Bash tool call is a fresh shell — env vars ($WH, $DIR, …) and cd do not persist

between calls. Re-set what you need inline in every block, or write paths/ids to a temp file and read them back.