Connecting an AI Agent to Your Data Warehouse: The Access Patterns

The decision nobody frames properly

Every AI analytics deployment starts with the same question, usually asked too casually: how does the model get to the data?

It gets answered by whoever is running the pilot, in an afternoon, and the answer becomes permanent. Six months later it is the reason the security review stalls or the accuracy is bad.

There are four patterns. They differ in accuracy, in blast radius, and in how hard they are to defend to a CISO.

Pattern 1: Direct SQL execution against the warehouse

The model generates SQL and something runs it against Snowflake, BigQuery, or Databricks.

Why teams pick it: It works on day one and requires no data modeling.

What it costs: Accuracy, mostly. The model sees raw physical schema, so it faces every problem that comes with 480 tables and columns named amt_net_adj. Expect exact-match accuracy well below what the same model achieves against a modeled layer.

The security shape: Manageable if you are disciplined. Run it as a dedicated service account with read-only grants on an explicit allowlist of schemas. Never the analyst's credentials. Never a role with write. Set a statement timeout and a bytes-scanned cap, because a generated query with a bad join will scan your entire fact table and the invoice will explain why.

When it is right: Small, well-named schemas. Prototypes. Never as the permanent architecture for a 500-table warehouse.

Pattern 2: Through a semantic layer

The model queries defined metrics and dimensions rather than tables. dbt's semantic layer, Cube, LookML, or an internal equivalent.

Why it wins on accuracy: The model is choosing from thirty governed metrics instead of four hundred ambiguous columns. Net sales has one definition. Join paths are already resolved and correct. The entire class of fan-out errors disappears, because the model is not writing joins.

What it costs: The semantic layer has to exist. If you have one, this is straightforwardly the best pattern. If you do not, budget four to twelve weeks to model the metrics that answer most questions, and understand that this is the work, not a prerequisite to the work.

The security shape: The best of the four. The model cannot express a query outside the defined surface, so the blast radius is bounded by construction rather than by policy. Row-level security applies at the semantic layer and is inherited automatically.

The limit: Questions outside the modeled metrics cannot be answered at all. That is a real constraint and it is also the property that makes the pattern safe. Most teams find the trade worth it.

Pattern 3: Tool-calling over an API layer

The model does not write queries. It calls defined functions: get_sales(region, period), get_margin_by_category(...). This is the shape MCP servers and similar tool protocols standardize.

Why it is attractive: Maximum control. Every function is written and tested by your team. Auth, rate limiting, logging, and row-level security all live in code you own. Auditors like it because it looks like every other integration they have reviewed.

What it costs: Coverage and maintenance. Every question shape needs a function. You will ship twenty, discover users want eighty, and spend a year writing them. The system is rigid in exactly the way natural language interfaces are supposed to not be.

When it is right: High-stakes or regulated environments, and anywhere the write path is involved. If an agent can trigger a purchase order, it should be through a reviewed function with validation, not through generated SQL. This is the correct pattern for actions even when queries use a different one.

Pattern 4: Replicate to a purpose-built store

Copy the data the agent needs into a separate database it owns.

Why teams do it: Full isolation. Zero load on production. You can shape the schema for the model rather than for the OLTP system, which is a genuine accuracy benefit.

What it costs: A second copy of your data, with everything that implies. Freshness lag. Pipeline maintenance. A new location for the same PII, which means a new entry in the data map, a new retention policy, and a new thing to explain in the SOC 2 audit.

When it is right: Genuine performance isolation requirements, or a source system that cannot tolerate query load. Rarely worth the overhead otherwise.

What to actually do

For most mid-market analytics deployments, the right answer is a combination, not a choice.

1. Reads through a semantic layer. Best accuracy, smallest blast radius. If it does not exist yet, build it for the 30 to 40 metrics that cover most questions. This is the highest-return work in the entire project.
2. Writes through tool calls only. Never generated SQL. Every write is a reviewed function with validation and an approval gate.
3. Direct SQL as a fallback for the tail, read-only, on a dedicated service account, allowlisted schemas, hard timeouts and scan caps.
4. Log every query, always. Query text, user, timestamp, rows returned. You will need this in month four when someone asks where a number came from, and you cannot reconstruct it retroactively.

The non-negotiable baseline

Regardless of pattern: read-only by default, a dedicated service identity that is never a human's credentials, an explicit schema allowlist rather than a denylist, inherited row-level security, statement timeouts, bytes-scanned limits, and full query logging.

None of that is exotic. All of it gets skipped in pilots, and the pilot architecture is what ships.

Key takeaways

• Four access patterns exist: direct SQL, semantic layer, tool calls over an API, and replication to a separate store. The choice is usually made casually in a pilot and becomes permanent.
• Direct SQL works on day one and costs accuracy. The model faces raw physical schema with 480 tables and columns named amt_net_adj. Fine for prototypes, wrong as a permanent architecture.
• A semantic layer is the best read pattern on both accuracy and security. The model picks from thirty governed metrics instead of four hundred ambiguous columns, and fan-out errors disappear because it is not writing joins.
• The semantic layer's limit, that questions outside modeled metrics cannot be answered, is the same property that makes it safe. Blast radius is bounded by construction rather than policy.
• Writes should always go through reviewed tool-call functions with validation, never generated SQL, even when reads use a different pattern.
• Recommended combination: reads through a semantic layer, writes through tool calls, direct read-only SQL as a tail fallback with timeouts and scan caps, and full query logging from day one.
• The baseline that always gets skipped in pilots: read-only default, dedicated service identity, schema allowlist not denylist, inherited row-level security, statement timeouts, bytes-scanned caps. Pilot architecture is what ships.