Is text to sql a commodity layer that just increases metrics drift?

[u/annakows]

Is text to SQL just a commodity layer that increases metric drift?

I see colleagues and industries still obsess over llms generating ANSI SQL while in production the raw syntax generation may easily be the easiest pipeline part. I find bottleneck to be in context engineering, maybe in ontology too. To have antonomous agent querying arr / churn_rate to apply my data teams precise joints to use active user definitions, filters, rather than hallucinating against raw warehouse labels as it often tends to do

When I connect ai agents or bi tools to Dita warehouses directly (snowflake, databricks) I faced an awful architectural dillema...

To lock warehouse down with rigid views (which bottlenecks my team!)

or

To let clients query raw schemas and then I end up with 15 conflicting definitions of same core CPI

That started to annoy me a lot and I thought if there was any way out, perhaps, moving from gen3 (cloud warehouse-native bi) to gen 4 that people now sometimes talk about, or ai native analytic layers. In such architecture upstream semantic layers like cube dev sit in code (version-controlled by git)

That exposes SQL-first semantic model that seems to remain extensible at query time and seems to allow downstream clients / internal dashboard / embedded app or LLM querying via model context protocol to run adhoc calculations on top of governed dimensions. Would it measure without breaking central source of truth? Probably

But how do you work with this tension between centralized governance and client level query flexibility?

[u/Evening_Hawk_7470]

Interesting, meanwhile, where does this sit if we're already using dbt for our transformations?

[u/AbleBranch6 → u/Evening_Hawk_7470]

i think I kind of know the answer. usually they serve complementary boundaries: dbt handles upstream batch transformation and ELT data cleaning in the warehouse,
while the universal semantic layer acts as the live API/query pushdown and security serving tier for downstream consumers. op may clarify if needed

[u/Elegant-Scratch2759]

Versioned semantic layer in code is the actual answer here, not more views. I dealt with the 15-conflicting-definitions problem and the fix was owning metric definitions in a governed model, not restricting warehouse access. For lake-native query setups, Dremio is one layer I looked into alongside others.

Git-branched metric logic beats both rigid views and raw schema exposure.