Designing a Governed Enterprise Data & AI Platform

A recent enterprise architecture engagement reinforced a pattern I keep coming back to: the hard part is not simply getting data into Snowflake or placing an LLM on top of it. The real work is deciding where trusted analytical truth lives, how identity survives source-system change, what can safely be exposed, and how agents can use enterprise data without becoming uncontrolled database clients.

The requirement

The organization needed a modern enterprise data and AI foundation while moving from a legacy operational platform to a next-generation platform. Data was spread across operational databases, CRM, customer-success and support SaaS applications, APIs and file-based feeds. The warehouse existed, but the target state needed a more durable approach for ingestion, customer identity, trusted business data, governed reporting and AI-agent access.

The architecture also had to support multiple ingestion styles rather than forcing every source into one pattern: scheduled batch for latency-tolerant data, API or incremental extraction for SaaS platforms, CDC for operational databases where inserts, updates and deletes matter, and event-based ingestion where continuous processing is justified.

On the AI side, the requirement was equally important: an enterprise agent such as Claude should be able to use Snowflake for historical, cross-system and certified analytical context, while current operational state and writes remain with the systems that own those transactions. Internal users and future customer-facing experiences also needed clearly separated access boundaries.

1. Start with capabilities, not a single ingestion tool

The first decision was not “Which connector do we standardize on?” It was to define a small set of ingestion capabilities and then choose the simplest reliable path for each source.

This matters because orchestration and change capture are not the same thing. Airflow, for example, is excellent at schedules, dependencies, retries, backfills and workflow control, but it is not itself the transaction-log CDC engine. When true CDC is required, I would use a purpose-built change-capture mechanism and let the orchestration layer coordinate the surrounding workflow.

Choose the ingestion pattern by source capability, volume, freshness, recovery and operating model — not by forcing every source through the same technology.

2. Raw → Curated → Identity → Gold

I use medallion principles, but I do not treat the layers as labels for folders. Each layer has a specific contract.

3. Identity before Gold

The most important design choice in this architecture was to avoid making a legacy customer ID, next-generation customer ID, or CRM account ID the enterprise key.

During a platform migration, the same real-world customer can exist under different identifiers across operational applications, CRM, customer-success platforms and support systems. If each Gold model independently joins those source keys, the organization ends up with multiple versions of customer truth.

Instead, I designed a persistent enterprise identity crosswalk:

High-confidence records map to the durable enterprise identity. Ambiguous records remain explicitly unresolved rather than being forced into a false match. That unresolved population is measurable and can be addressed through stewardship.

4. Gold should be stable even when source systems are not

During a legacy-to-next-generation migration, source schemas will drift. Field names change, business rules move, and one system may temporarily contain data the other does not. I do not want every report, semantic model or AI agent to absorb that volatility.

The pattern I use is to isolate source differences below Gold:

As migration progresses, the legacy adapter can eventually be retired without changing the business-facing contract. This is one of the strongest reasons to model Gold around durable business concepts instead of physical source tables.

5. Add a semantic layer for both BI and AI

Trusted tables alone are not enough. Business terms such as revenue, active customer, adoption, churn risk or service utilization need consistent definitions. Without a semantic layer, a BI dashboard and an AI agent can each produce technically valid SQL while still answering the business question differently.

I therefore place governed semantic definitions above Gold. In Snowflake, Semantic Views and Cortex analytical capabilities can provide a governed path for natural-language questions while keeping the underlying business meaning explicit.

6. Internal and customer access need different boundaries

Security is not “roles versus views.” They solve different parts of the problem. Roles answer who can access something. Governed views, data products and semantic models define what is exposed. Row-access and masking policies constrain which rows and sensitive attributes are visible.

I would typically maintain a common trusted core and create explicit consumption boundaries:

I would not automatically create one Semantic View per customer. A more scalable pattern is a reusable customer-facing semantic model with tenant-aware security, while internal users have access to broader metrics and cross-customer information when appropriate.

Separate virtual warehouses can still be valuable for BI, AI and customer-facing workloads because they improve workload isolation, scaling and cost attribution. I do not treat the warehouse itself as the primary security boundary.

7. Agent architecture: read and reason in Snowflake, verify and act in the source

The most useful design rule for AI agents is also one of the simplest:

Read and reason in Snowflake; verify and act in the source system.

I designed the agent path with a clear separation of authority. The enterprise agent sits outside the warehouse and receives an authenticated request. A governed tool/routing layer then decides whether the request needs analytical context from Snowflake or current operational state from a source system. The agent is never treated as an unrestricted database user.

For historical, cross-system or certified questions, the agent uses a governed Snowflake path backed by Gold data, semantic definitions and approved analytical tools. For anything that depends on the latest operational state—or requires a write—the agent calls the owning application's MCP/API, re-checks the current state where necessary, applies that system's authorization and business rules, and records the outcome.

For example, the agent can identify a high-value customer with declining adoption from governed warehouse data. If the next step is to change a CRM status, create a support case or update an operational record, it does not write to Snowflake and wait for the change to propagate. It calls the source directly, verifies the state, executes the action with a scoped identity, uses idempotency to protect retries, and links the action back to the original request with a common run or correlation ID.

8. MCP and agents: how the access pattern was designed

MCP became useful because it gives an external agent a standard way to discover and invoke approved tools, but I kept the responsibilities deliberately separate. Claude or another enterprise agent provides reasoning and orchestration. MCP is the tool interface. Snowflake and the source systems still enforce authentication, authorization and data permissions.

The design supports two Snowflake access patterns without unnecessarily stacking MCP layers. If the organization operates an external MCP service, that service can connect to Snowflake using normal authenticated Snowflake connectivity and a tightly scoped role. In that pattern, there is no requirement to create a second MCP server inside Snowflake. Alternatively, Snowflake can expose its own managed MCP endpoint and approved Snowflake-native analytical capabilities can be published through that endpoint.

That distinction matters because the architecture should not automatically become Claude → external MCP → Snowflake MCP → Snowflake. The cleaner implementation is to choose one governed Snowflake access path and keep source-system MCP/API tools separate for current-state verification and actions.

It is also important not to confuse three different concepts:

There are also two valid integration patterns that should not be accidentally drawn as mandatory layers on top of each other.

In Pattern A, an externally hosted MCP service connects to Snowflake using normal authenticated connectivity and a tightly scoped Snowflake role. In Pattern B, Snowflake itself exposes the managed MCP endpoint. Which one is better depends on governance, identity, operational ownership, and how much Snowflake-native AI capability the organization wants to expose.

How the agent flow works end to end

This is also why MCP is not the security boundary. It standardizes the tool call, but the tool server must validate identity and scope, and the underlying platform still has to enforce the final authorization.

9. Design observability and cost attribution into the architecture

A production AI/data platform needs to answer more than “Did the query run?” I use a shared correlation or run identifier across the agent request, model call, Snowflake query and source-system action where practical.

That creates a trace from user or service identity → agent/tool call → analytical query → source action → outcome. It also enables meaningful cost attribution across three different cost domains:

10. What I would validate before locking products

Good architecture should make irreversible decisions deliberately and postpone reversible implementation choices until there is evidence. I would validate the following before standardizing technology:

This keeps technologies such as Openflow, external orchestration, dedicated CDC tooling, Cortex Agent, a custom MCP service, or a dedicated MDM platform in the right place: they are implementation choices that should earn their way into the architecture based on requirements.

Architecture principles I would carry forward

What comes next

I do not publish client source code. The companion Architecture Lab uses a clean-room reference implementation written separately with synthetic data, generic source names and simplified business rules. Its purpose is to demonstrate the engineering pattern without reproducing a client's implementation.

Open the companion reference implementation →

The broader lesson is simple: enterprise AI becomes much easier to govern when the data architecture already knows what is trusted, who is allowed to see it, and which system is authoritative for an action.