What Should a Data + AI Architecture You'd Actually Recommend to a Company Look Like in 2026?

This weekend, I reworked an old personal project. Not to add yet another AI demo, but to answer a specific question: what should a Data + AI architecture you'd actually recommend to a company look like in 2026?

Digging into the old version of the project (a data platform for a food-delivery use case: Snowflake, dbt, Airflow, plus an AI layer), I found the real problem. The cancellation rate was calculated independently in three places: two dashboards and the text-to-SQL module's prompt. Three different definitions of the same number, with nothing guaranteeing they'd stay aligned over time.

Worse: I was about to add a fourth version, the one an LLM could have generated on its own with direct access to the tables. An AI doesn't invent business logic that doesn't already exist somewhere, it repeats whatever it's given to read, with the confidence of a system that never doubts itself. If its version of the cancellation rate had diverged from the two existing dashboards, it would have produced a wrong answer with the same confidence as a right one.

I stopped before that happened. I redesigned the architecture around a simple principle: one definition per metric, one layer that everyone reads (dashboards, RAG, text-to-SQL), and nobody touches the raw data. Not even the AI.

The problem in detail

Concretely, here's what the duplication looked like in code. The daily city revenue mart computed the cancellation rate directly in its SQL query:

-- Avant la couche sémantique : chaque mart réécrivait sa propre logique
select
    order_date,
    city,
    count(*) as orders,
    round(div0(count_if(order_status = 'Cancelled'), count(*)), 4) as cancel_rate
from fact_orders
group by 1, 2

The restaurant performance mart did the same computation, but with its own variation of the SQL, in a different part of the repo. And the text-to-SQL prompt described, in a plain string passed to the LLM, its own explanation of what a "cancellation rate" is, with no programmatic link to the actual SQL running in dbt.

The risk isn't hypothetical. A SQL expression copy-pasted three times always ends up drifting: someone fixes an edge case in one copy and forgets the other two, someone changes the business definition ("does a refund count as a cancellation?") in only one place, or worse, nobody notices the three definitions already disagree on orders with an ambiguous status. This kind of drift is invisible as long as nobody compares the three numbers side by side. And once an AI reads the raw data to answer a business question, it becomes a fourth drift point, less visible than the first two since it generates its answer on the fly instead of materializing it in a dashboard you can audit.

The full architecture

The solution I put in place rests on a classic medallion stack (Bronze, Silver, Gold), with one more layer on top: a semantic layer, the single source of truth for every consumer, human or AI.

S3 landing (immutable) -> RAW (Bronze, COPY INTO + metadata)
   -> STAGING (Silver, dbt) -> MARTS (Gold, dbt) -> SEMANTIC (dbt)
        |-- BI / dashboards
        `-- AI: enrichment (writer, -> AI schema) / RAG / text-to-SQL (readers, SEMANTIC only)

Each layer has a strict role. Bronze copies the source data verbatim, with no business logic whatsoever, not even a TRIM(). Silver types and deduplicates, one row per business key. Gold builds the star schema (fact_orders, dim_customer, etc.) and the business marts. None of these layers recomputes a metric already defined elsewhere: that's the rule missing from the original version of the project.

The real novelty is the semantic layer, and it comes down to three pieces, all native dbt, with no new tool introduced.

1. One dbt macro per metric, defined exactly once:

{% macro cancellation_rate_expr(status_col='order_status', cancelled_value="'Cancelled'") -%}
    ROUND(DIV0(COUNT_IF({{ status_col }} = {{ cancelled_value }}), COUNT(*)), 4)
{%- endmacro %}

Every mart and every semantic view that needs the cancellation rate calls this macro instead of rewriting the SQL expression:

-- Grain: one row per (order_date, city). Every measure calls the shared
-- macro definitions in macros/metrics.sql.
select
    order_date,
    city,
    count(*) as orders,
    {{ delivered_orders_expr() }} as delivered_orders,
    {{ cancellation_rate_expr() }} as cancel_rate,
    {{ gross_merchandise_value_expr() }} as gross_merchandise_value,
    {{ average_order_value_expr() }} as average_order_value
from {{ ref('fact_orders') }}
group by 1, 2

2. Thin semantic views on top of the Gold marts, which recompute nothing: they just expose what the mart has already produced.

-- Thin, documented pass-through of mart_daily_city_revenue. Not
-- re-deriving GMV/cancel_rate/AOV here, on purpose: the semantic layer's
-- job is to be the one place consumers are granted access to, not
-- another place metric logic lives.
select
    order_date,
    city,
    orders,
    delivered_orders,
    cancel_rate,
    gross_merchandise_value,
    average_order_value
from {{ ref('mart_daily_city_revenue') }}

3. A metric dictionary (metrics.yml), documenting each metric: name, description, grain, owning semantic view, SQL. It's loaded directly by the Python code that builds the LLM's prompt. No copy-pasting between the YAML and the prompt: the same file serves both.

I deliberately ruled out dedicated semantic-layer products (dbt Semantic Layer / MetricFlow, Cube, LookML). These tools deliver real value when several BI tools need a shared, dynamic query interface over dozens of metrics. Here, there are about a dozen metrics and a single programmatic consumer. Adding a query-planning service, a new deployment target, and a new modeling language for this volume is infrastructure sized for a problem I don't have. The day several BI tools need dynamic dimensional slicing on these metrics, the metrics.yml dictionary is exactly the artifact that migration would start from.

The AI layer and its guardrails

Three AI capabilities read this semantic layer, each under a dedicated Snowflake role that never exceeds its boundary.

Batch enrichment classifies customer reviews (sentiment, topic) in an idempotent, replayable way. Before a comment reaches the LLM provider, it goes through deliberately simple PII scrubbing:

_EMAIL_RE = re.compile(r"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")
_CREDIT_CARD_RE = re.compile(r"\b(?:\d[ -]?){13,16}\b")
_PHONE_RE = re.compile(r"\b(?:\+?\d{1,3}[ -]?)?(?:\(\d{2,4}\)[ -]?)?\d{3,4}[ -]?\d{3,4}[ -]?\d{0,4}\b")

This isn't a general PII compliance solution: it doesn't detect names or addresses, which would need an NER model. But at the scale of a free-text customer comment, a handful of well-tested patterns (email, phone, credit card) catches the most obvious leaks, with no added latency or external service. Every enrichment attempt, successful or failed, is logged with the model name, its version, and the prompt version used, so a failure stays visible and replayable instead of silently lost.

Text-to-SQL is the most exposed capability, so it gets the most guardrails, in depth rather than as a single barrier. The first layer is static validation of the LLM-generated SQL, with a real parser (sqlglot), not a list of banned keywords:

DISALLOWED_NODE_TYPES = (
    exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Alter, exp.Create,
    exp.Merge, exp.Command, exp.Grant, exp.Copy,
)
 
def validate_and_prepare(candidate_sql, allowed_tables, default_schema, row_limit):
    statements = sqlglot.parse(candidate_sql, read="snowflake")
    ...
    disallowed = list(root.find_all(*DISALLOWED_NODE_TYPES))
    if disallowed:
        kinds = sorted({type(n).__name__ for n in disallowed})
        raise GuardrailViolation(f"Disallowed operation(s) found: {', '.join(kinds)}.")
 
    _check_table_allowlist(root, allowed_tables, default_schema)
    _enforce_row_limit(inner, row_limit)

A keyword blocklist like "drop" not in sql.lower() gets tripped up by a column named dropoff_time or by a quoted string literal. Actually parsing the syntax tree avoids that kind of false negative.

But the real guarantee isn't there. It's in the Snowflake role under which the query actually executes:

CREATE ROLE IF NOT EXISTS AI_READONLY_ROLE
    COMMENT = 'RAG + text-to-SQL. SELECT on SEMANTIC only.';
 
GRANT USAGE ON SCHEMA SEMANTIC TO ROLE AI_READONLY_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA SEMANTIC TO ROLE AI_READONLY_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA SEMANTIC TO ROLE AI_READONLY_ROLE;
 
ALTER ROLE AI_READONLY_ROLE SET STATEMENT_TIMEOUT_IN_SECONDS = 30;

This role has no write privilege anywhere, and no access outside the SEMANTIC schema. That's the defense in depth I mentioned: if the application-level validation has a bug, if a prompt injection finds a flaw in the parser, the worst possible outcome is a confusing error returned to the user, never a read of an out-of-bounds table or a write. The guarantee that actually matters comes from the database itself, not from the Python code running in front of it.

This design choice has another, more subtle but equally important consequence: because the AI reads exactly the same semantic views and the same metric definitions as a dashboard, an AI answer and a number shown on a dashboard can never diverge simply because the AI has different or looser access to the data. That's exactly the original problem disappearing.

Data quality and observability

A dbt test's severity is never implicit in this project, it's declared explicitly, line by line:

- name: sales_amount
  tests:
    - dbt_utils.expression_is_true:
        expression: ">= 0"
        config:
          severity: warn

A blocking test (severity: error, the default) stops the Airflow DAG before AI enrichment or publishing runs: primary key uniqueness/non-nullness, referential integrity, accepted values. A warning test (severity: warn) is logged but blocks nothing, for example a slightly stale freshness check or a soft business rule. A singular test even checks that the semantic layer never silently drops rows relative to the Gold mart it wraps:

with gold as (
    select count(*) as n from {{ ref('mart_daily_city_revenue') }}
),
semantic as (
    select count(*) as n from {{ ref('sem_revenue_daily') }}
)
select gold.n as gold_row_count, semantic.n as semantic_row_count
from gold, semantic
where gold.n != semantic.n

Three control tables answer "what ran, with what result" without digging through logs: INGESTION_RUNS traces every ingestion attempt by run and source file, AI.ENRICHMENT_LOG traces every AI enrichment attempt (including the number of PII redactions, to spot an abnormal spike without rereading every raw comment), and PIPELINE_RUNS summarizes every full run of the Airflow DAG. A single run_id runs through all three tables, so any row in fact_orders traces back to the exact source file and the Airflow run that loaded it.

What I deliberately left out

On a reference project, it's tempting to add "impressive" tools to make it look more "enterprise." I deliberately ruled out Kafka, Spark, Kubernetes, Databricks, and a dedicated vector database, not out of unfamiliarity, but because none of them solves a problem this platform actually has, at its actual scale.

Kafka solves a sub-minute event-delivery problem. Nothing in the business requirements (revenue reporting, review enrichment, delivery SLA) needs that kind of aggressive freshness: the sources are extracted in daily batches. Spark solves a distributed-compute problem for volumes too large for a standard warehouse, while every transformation here is expressed as SQL that Snowflake executes natively. Kubernetes orchestrates independently scalable services, while here a handful of services (Airflow, Postgres, Streamlit) run comfortably on a single-host Docker Compose. And for the RAG's vector search, a content-hash-cached embedding matrix answers in milliseconds at the scale of a few hundred thousand customer reviews, with no extra infrastructure.

Each of these tools is legitimate, just for a problem of a different scale. Adopting them here would have added real operational complexity (clusters to manage, schema registries, cross-system reconciliation) for a benefit that isn't measurable. That's exactly the trap I wanted to avoid: a project that looks impressive on paper but isn't justified in practice.

What I'd change at larger scale

None of this is set in stone, and the repo explicitly documents the triggers that would justify changing each choice.

• A real need for sub-minute freshness (for example live order tracking) would justify adding a streaming ingestion path alongside the batch path, not instead of it.
• If vector search became too slow at scale, ParquetVectorStore could be swapped for pgvector or a managed vector database, with no pipeline rewrite, since VectorStore is defined as an interface (Protocol).
• If several BI tools needed dynamic dimensional slicing on the metrics, migrating metrics.yml's definitions to a real semantic-layer product would be the natural next step.
• If the team outgrew a single-host Airflow, moving to a managed Airflow (MWAA, Composer, Astronomer) would be the next step.

None of these changes is a rewrite from scratch. They're additive changes or same-interface swaps, because the extension points (Source, VectorStore, the metrics.yml dictionary) were built for exactly that from the start.

Conclusion

The difference between an AI you show off in a demo and an AI you actually dare to connect to production data rarely comes down to which model you picked. It comes down to what's underneath: does the data the AI reads have a single, tested definition shared with the rest of the system, or is the AI adding its own version of the truth to a pile that already had too many.

The full code, the seven ADRs documenting every architecture choice, and the tests that validate them are on GitHub: github.com/Tianarandr/end-to-end-de-pipeline.