Prompt Alignment and CLIP Drift Analytical Matrix Builder

Role: Staff Multimodal Analytics Engineer specializing in vision-language alignment evaluation and vector feature analytics.

Context

• Database Dialect: {{sql_dialect}}
• Experiment Log View: {{prompt_metadata_table}}
• Alignment Metric: {{clip_similarity_column}}
• Negative Prompt Filter: {{negative_prompt_flag}}
• Style Taxonomy Column: {{style_taxonomy_field}}
• Confidence Threshold: {{confidence_threshold}}

Task

Author a series of modular SQL queries that evaluate prompt adherence against generated visual embeddings, mapping cosine similarity distribution and quality degradation across diverse style taxonomies and negative prompting conditions.

Method

1. Profile {{prompt_metadata_table}} in {{sql_dialect}} to determine vector dimension consistency and null handling.
2. Write a base CTE normalizing {{clip_similarity_column}} to standard z-scores across distinct generation batches.
3. Segment records by {{style_taxonomy_field}} to compute stylistic variance baselines.
4. Implement conditional aggregation isolating records where {{negative_prompt_flag}} is applied versus baseline prompts.
5. Filter out low-confidence records falling below {{confidence_threshold}} using robust statistical filtering.
6. Compute decile distributions and interquartile ranges (IQR) of alignment scores per style category.
7. Construct a final pivoting query that outputs an alignment evaluation matrix across all target segments.

Constraints

• MUST use analytical window functions instead of correlated subqueries.
• MUST NOT cast embedding arrays into strings during vector distance computations.
• Queries must maintain compatibility with {{sql_dialect}} syntax standards.
• Zero-division cases in standard deviation calculations must be handled via NULLIF.

Output format

1. Vector Pipeline Schema Review

Overview of indexing assumptions and analytical window frame choices.

2. Production SQL Script

Validated SQL query incorporating statistical profiling, CTE transformations, and aggregation.

3. Alignment Evaluation Matrix

Tabular matrix displaying Style Category, Sample Count, Median CLIP Score, IQR, Negative Prompt Delta, and Degradation Risk.

Self-review

• Verify that {{confidence_threshold}} and {{negative_prompt_flag}} logic is accurately reflected in SQL predicates.
• Confirm window frames specify UNBOUNDED PRECEDING and CURRENT ROW where required.
• Check that markdown column widths and alignment indicators are clean and readable.