Recognises an index the target engine cannot create or run
Snowflake has no CREATE INDEX at all, so index definitions come out commented out and the real lever is a clustering key. MySQL cannot index a TEXT/BLOB column without a prefix length, and the script will fail at that statement.
Can AI do this?
AI does this partly Some of the tested AI models master this; others do not show it. 5 of 10 models master it.
| Model | Tier | Probability of mastery | Status |
|---|---|---|---|
| gemini-4-argon | Flagship | 100% | Mastered |
| gemini-3.1-pro-preview | Flagship | 90% | Not shown |
| gpt-6-astra | Flagship | 89% | Not shown |
| claude-fable-5-1 | Flagship | 100% | Mastered |
| gemini-3.8-flash | Mid tier | 90% | Not shown |
| gpt-6.1-sol | Mid tier | 91% | Mastered |
| claude-sonnet-5-5 | Mid tier | 100% | Mastered |
| gemini-3.5-flash-lite | Low cost | 0% | Not mastered |
| gpt-6-luna | Low cost | 90% | Not shown |
| claude-haiku-5-5 | Low cost | 100% | Mastered |
Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.
What it looks like in practice
Expected result: The index and the engine-specific lever to use
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: Snowflake; ix_dim_account_number_current commented out, no cluster_by set, and every point lookup on dim_account scans it. MySQL; relationship_manager_notes (TEXT) left in an index after the generator warned Acceptable: physical_options.snowflake: {cluster_by: [account_number]} and the Databricks equivalent — as the sample model has them — and the notes column bounded to a VARCHAR in the model, or removed from the index
Also known as: Index that cannot exist, or will not run (generated DDL)
Related knobits
Builds on
All 33 knobits of Generate and review DDL.
Source: Review checklist 08-ddl, row 13; this skill (08-ddl.md)