sentiment-analysis

# Sentiment Analysis with AI Functions Apply this skill when building tables that turn free-form text into structured sentiment / topic / entity columns using Databricks' built-in [AI functions](https://docs.databricks.com/aws/en/large-language-models/ai-functions). This skill assumes the SDP and governance basics are already applied -- it adds AI-function-specific rules on top. For bronze / silver / gold CREATE statements, read [pipeline-templates.md](pipeline-templates.md). ## Skill Composition | Skill | Required? | Why | |-------|-----------|-----| | `@table-governance` | Always | Comments, TBLPROPERTIES, column descriptions, UC tags | | `@sdp-basics` | Always | Layer prefix, audit columns, constraints, formatting | | `@pii-management` | When source text or joined columns contain personal data | Reviews can leak names/emails -- mask before exposing | | `@sentiment-analysis` | This skill | AI-function selection, output schema, cost guardrails | ## Choose the Right AI Function | Function | When to Use | Output | |----------|-------------|--------| | `ai_analyze_sentiment(text)` | Single overall sentiment per row | STRING in `{positive, negative, neutral, mixed}` | | `ai_classify(text, ARRAY('a','b',...))` | Bucket text into a fixed taxonomy (topics, intents) | STRING from the supplied label set | | `ai_extract(text, ARRAY('field1','field2'))` | Pull named entities or fields out of text | STRUCT<field1:STRING, field2:STRING, ...> | | `ai_summarize(text, max_words)` | Compress long text into a short blurb | STRING | | `ai_translate(text, lang)` | Normalize multilingual input before sentiment | STRING | | `ai_query(endpoint, prompt)` | Custom prompts that the built-ins don't cover | depends on endpoint | Prefer the built-ins (`ai_analyze_sentiment`, `ai_classify`, `ai_extract`, `ai_summarize`) over `ai_query` -- they are cheaper, governed, and don't require a serving endpoint. ## Standard Output Schema Every Silver-layer sentiment table MUST expose at least these columns, in this order, before the standard `data_quality_flag` and audit columns: | Column | Type | Source | Notes | |--------|------|--------|-------| | `<entity>_id` | identifier | source | Primary key from the source row (e.g. `review_id`) | | `source_text` | STRING | source | Raw text fed to the AI functions (kept for debugging / re-runs) | | `sentiment_label` | STRING | `ai_analyze_sentiment` | One of `positive`, `negative`, `neutral`, `mixed` | | `topic_label` | STRING | `ai_classify` | From a project-defined topic taxonomy | | `extracted_entities` | STRUCT | `ai_extract` | Named entities (product, person, location, etc.) | | `summary` | STRING | `ai_summarize` (optional) | Short summary, only when source text is long | If you add additional AI-derived columns, name them with the prefix `ai_` (e.g. `ai_intent`) so they are easy to audit and drop in downstream filtering. ## Layer Rules - **Bronze:** MUST NOT call AI functions. Land the raw text plus stable identifiers only. - **Silver:** Run AI functions here. Use a `MATERIALIZED VIEW` so calls execute only on refresh. Filter junk text before calling AI. Gate each call with a `CASE`. Add `data_quality_flag` (`MISSING_TEXT`, `AI_NULL_RESPONSE`, `CLEAN`). - **Gold:** MUST NOT call AI functions and MUST NOT contain `source_text`. Aggregate sentiment by business dimensions. ## Cost and Quality Guardrails AI functions are billed per call and per token. Apply ALL of the following: - **Pre-filter junk text.** Always gate AI calls behind a `LENGTH(TRIM(text)) >= 5` (or domain-appropriate minimum) check. - **Materialize, don't view.** Use `MATERIALIZED VIEW`, not `VIEW`, so AI calls run once per refresh, not per query. - **Cap the dev volume.** During development, add a deterministic `WHERE MOD(HASH(review_id), 100) = 0` sample so iteration runs over ~1% of rows. Remove before production. - **Stable inputs.** Pass the raw column directly, not a non-deterministic expression. AI outputs change with input phrasing. - **Reuse outputs.** Downstream tables should consume the silver AI columns -- never re-call the AI function in gold. - **CDC for downstream.** Set `delta.enableChangeDataFeed = "true"` on silver/gold so consumers can incrementally pick up new sentiment without rerunning AI. ## Quality Constraints for AI Outputs Add `CONSTRAINT` clauses to silver tables to guard against drift in AI label values. Copy the clauses from [pipeline-templates.md](pipeline-templates.md). If a constraint trips frequently, the AI label set has drifted -- don't widen the constraint silently. Update the taxonomy in code review. ## PII Considerations Customer reviews routinely contain names, emails, phone numbers, and order IDs that count as PII. Follow `@pii-management`: - Annotate `source_text` as `-- [PII: FREEFORM - HIGH]`. - Do NOT propagate `source_text` to gold. - Do NOT join silver sentiment to PII-bearing customer attributes in gold; aggregate first. - If `extracted_entities.staff_name` is a real employee name, mask or hash it before exposing externally. ## Worked Example: Bakehouse Customer Reviews The `@sentiment-analysis` skill is demoed end-to-end against the **AI/BI Bakehouse** Marketplace share. The install notebook ([`marketplace_data/install_bakehouse.py`](../../marketplace_data/install_bakehouse.py)) lands the share in a scratch catalog and then mirrors the relevant tables into `<catalog>.<bakehouse_schema>` so the demo lives under a single, predictable namespace: | Source table | Used for | |--------------|----------| | `<catalog>.<bakehouse_schema>.customer_reviews` | Bronze -- raw review text + stable IDs | | `<catalog>.<bakehouse_schema>.franchises` | Gold -- enrich aggregates with `franchise_name` | | `<catalog>.<bakehouse_schema>.transactions` | Gold (optional) -- join basket value to sentiment for revenue-impact views | Recommended Genie Code prompt to drive the full pipeline (substitute your catalog and schema): > Build a bronze, silver, and gold pipeline from `<catalog>.<bakehouse_schema>.customer_reviews`. Add sentiment, topic, and extracted entities on silver using AI functions, then aggregate sentiment by franchise on gold. Apply `@table-governance`, `@sdp-basics`, `@pii-management`, and `@sentiment-analysis`. ## Sentiment-Analysis Checklist Before completing any sentiment table, verify ALL of the following: - [ ] Bronze contains raw text only -- no AI calls - [ ] Silver uses `MATERIALIZED VIEW` (not `VIEW`) - [ ] AI calls are gated behind a non-empty / minimum-length filter - [ ] Output columns include `sentiment_label`, `topic_label`, `extracted_entities` as appropriate - [ ] `CONSTRAINT` clauses pin sentiment / topic to a known label set - [ ] `data_quality_flag` distinguishes `MISSING_TEXT` vs `AI_NULL_RESPONSE` vs `CLEAN` - [ ] `delta.enableChangeDataFeed` set on silver/gold - [ ] `audit_timestamp` and `source_system` are the LAST columns - [ ] PII guidance from `@pii-management` is applied to `source_text` and `extracted_entities` - [ ] Gold contains aggregates only -- no `source_text`, no AI calls