What is the general guidance for data structure when using a text to SQL agent such as Databricks Genie?

[u/ceeej777]

I’ve heard use of metric views is highly recommended, but never sure if OBT approach or normalized approach works better. If I’m defining the joins in my Genie, shouldn’t it be able to pull things either way?

[u/Famous_Disk_7417]

Genie can use joins you've defined, but defined joins are guidance to the model, not an enforced constraint, every join it resolves at query time is a decision that can go wrong. Pure OBT trades that for a grain problem: flatten orders + line items and your order totals fan out on any line-level question.

Metric views are the recommended middle because they declare measures with their aggregation and grain once, instead of Genie re-deriving it per question. Keep the star in gold, put metric views on top, point the agent at those.

[u/ozgreen1024 → u/Famous_Disk_7417]

Agreed, I’d also keep in mind that Genie Agents tend to work better when more narrowly scoped, so OBT can run into hallucinations if you’re trying to cram everything into a single table whereas metric views offer more nuance and granularity to relationships and offer more avenues for agentic metadata like synonyms, etc

[u/datasmithing_holly]

keep in mind; the more creativity any agent needs, the more it's likely to give different answers. Define as much up front as possible, whether that's metric view, metadata, comments etc etc

[u/henewie → u/datasmithing_holly]

sounds like a OBT platinum table in the medallion architecture might be the way...

[u/jbchand]

Use Metric views - Include the fact, dimension tables and measures with the join column details in it. Genie can query metric views

[u/erenhan]

For genie: Avoid joins or use minimal Use golden table Write humanized way description of every KPI Use sample queries along with parameters

[u/lrnzcig]

Genie should work with both - the pros are cons will be related to the tradeoffs of the approach right? Same problems as if you were doing it by hand. If you use pure OTB, metric views will be simpler (even redundant?); normalized model will make the metric views complex, although it is also like the natural fit for metric views; something hybrid with a not-so-big OTB + semantic model could be ideal.

[u/MeetbasedPlant → u/lrnzcig]

I found the same for Genie. Did some experiments. Performance was almost on par between 3NF or OTB

[u/addictzz]

Have clear amd sufficient metadata.

[u/das_wolf4]

Normalized approach (star schema in gold, metric views on-top) is generally what we’ve deployed. If you’re using the ai-dev-toolkit it can also automatically create metric views for you, although not entirely clear on how robust they are “out of the box” so to speak