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. 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.

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.

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) 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.