I’ve had a couple wins with AI in the design phase, where it helped me reach a conclusion that would’ve taken days of exploration, if I ever got there. Both were very long conversations explicitly about design with lots of back and forth, like whiteboarding. Both involved SQL in ClickHouse, which I’m ok but not amazing at — for example I often write queries with window functions, but my mental model of GROUP BY is still incomplete.

In one of the cases, I was searching for a way to extract a bunch of code that 5-6 queries had in common. Whatever this thing was, its parameters would have to include an array/tuple of IDs, and a parameter that would alter the table being selected from, neither of which is allowed in a clickhouse parameterized view. I could write a normal view for this, but performance would’ve been atrocious given ClickHouse’s ok-but-not-great query optimizer.

I asked AI for alternatives, and to discuss the pros and cons of each. I brought up specific scenarios and asked it how it thought the code would work. I asked it to bring what it knew about SQL’s relational algebra to find the an elegant solution.

It finally suggested a template (we’re using Go) to include another sql file, where the parameter is a _named relation_. It can be a CTE or a table, but it doesn’t matter as long as it has the right columns. Aside from poor tooling that doesn’t find things like typos, it’s been a huge win, much better than the duplication. And we have lots of tests that run against the real database to catch those typos.

Maybe this kind of thing exists out there already (if it does, tell me!) but I probably wouldn’t have found it.

This rings a bell. The model is a search engine, that understands concepts - to some degree. It can find a concept, that you currently need.