Microsoft DP-600: Database Design for AI Workloads

AI workloads do not eliminate the need for good database design. They add new access patterns: vector similarity, retrieval-augmented generation, model enrichment, agent tool calls, and conversational queries. A database serving those workloads still needs transactional correctness, clear schemas, predictable keys, security, indexing, and lifecycle management.

SQL database in Microsoft Fabric is designed to support AI applications with relational data and vector search in the same transactional engine. Current Microsoft guidance highlights native vector data types and indexes, low-latency transactional queries, hybrid retrieval patterns, and integration with AI orchestration frameworks. That makes it possible to keep business state and embeddings close when the use case benefits from joining semantic similarity with relational filters.

Database design for AI therefore belongs inside Microsoft Data Platform Engineering.

Keep the system of record relational

Customer, order, product, entitlement, and transaction state should remain modeled with stable keys, constraints, and relationships.

AI-generated summaries or embeddings should not become the authoritative record for data the business already models structurally.

Good AI architecture adds semantic access without replacing the relational truth the application depends on.

Store embeddings with provenance

Embeddings should be associated with the source record, source text, embedding model, dimensions, and version needed to understand how the vector was produced.

Database embeddings become operationally safer when a team can rebuild them after model changes or source updates.

Do not treat an opaque vector column as self-explanatory long-term data.

Use vector search with structured filters

Similarity alone is rarely enough for enterprise retrieval.

Relational filters can enforce tenant, product, date, state, or authorization constraints while vector search ranks semantically similar candidates.

Hybrid retrieval is strongest when semantic and structured signals work together rather than forcing every condition into the embedding space.

Separate transactional and analytical patterns

An operational AI application may need millisecond reads and writes, while large historical analytics may belong in lakehouse, warehouse, or eventhouse systems.

The same Fabric platform can support those workloads, but they should not be collapsed into one database simply because the data is related.

Design each storage layer around the access pattern that matters.

Design for embedding refresh

Source text changes, embedding models improve, and chunking strategies evolve.

The database should support identifying stale vectors and rebuilding them without breaking the underlying business record.

Keep generation version metadata and use background jobs or pipelines for refresh rather than blocking transactional writes on expensive embedding calls.

Use AI enrichment without hiding lineage

AI-generated classifications, summaries, or embeddings can be stored alongside operational data when they support the application.

Mark those fields as derived and preserve enough lineage to know which model and prompt produced them.

This prevents downstream users from confusing generated attributes with source-system facts.

Protect write paths

Agents and copilots can make databases easier to query, but write operations still need authorization, validation, and transaction rules.

AI-assisted SQL should enter the same review and deployment process as manually authored SQL when it changes schema or business data.

Generated code should never bypass constraints merely because the model produced a plausible statement.

Benchmark vector design separately

Embedding model, dimensions, vector index settings, chunk size, and query patterns all affect retrieval quality.

Vector design should be evaluated with known relevant records and production-like filters before the database is declared “RAG ready.”

A fast vector query that returns the wrong evidence is not a successful database design.

Design the database for change

AI capabilities will evolve faster than core business schemas. Keep model-specific fields, vectors, and orchestration metadata modular enough that they can be replaced.

For teams working across Fabric data engineering, the durable architecture is to preserve relational truth, add semantic access deliberately, keep derived AI data versioned, protect write paths, and choose storage engines according to workload rather than one fashionable pattern.

Schema naming becomes more important when both humans and AI generate queries. Clear table names, explicit keys, documented dimensions, and consistent status values reduce ambiguity. A model can generate SQL quickly, but it cannot infer a business definition that the schema itself does not express.

Vector search should be isolated from transactional correctness. Similarity can suggest which product description, document, or case is relevant, but the final business decision should re-read the authoritative record before a write or financial calculation. This protects the application from acting on stale embedding context.

Embedding storage should include deletion semantics. If a source row is deleted or access is revoked, the corresponding vector should not remain searchable indefinitely. Use stable source identifiers so vector cleanup can follow the source lifecycle.

Hybrid retrieval can benefit from precomputed business fields. Instead of asking the model to infer geography, product family, or entitlement from text, store those attributes as relational columns and filter before or alongside vector search. Structured truth is cheaper and more reliable than encoding every fact semantically.

Index selection should follow workload. A table supporting high write volume and occasional semantic lookup may need a different vector strategy from a read-heavy knowledge store. Benchmark insertion, update, and query behavior together instead of optimizing only the similarity query.

RAG workloads also need source text that fits the retrieval unit. Long documents may require chunk tables with foreign keys back to the business object, while short product records can often embed one row directly. The schema should represent that relationship explicitly so citations and updates remain manageable.

AI agents can expose databases through tools or MCP servers. Those integrations should call stored procedures, views, or constrained APIs that express approved operations rather than exposing raw database authority. The database remains the final enforcement layer for transactions and access.

Operational databases and analytics platforms can still share data through Fabric without becoming one physical store. Replication, mirroring, shortcuts, or pipelines can move or expose data according to freshness and ownership requirements. AI architecture should use those platform capabilities instead of forcing every analytical feature into the transactional engine.

Finally, design for model change. Embeddings, prompts, and retrieval patterns will evolve. Keeping AI-derived columns separate, versioned, and rebuildable lets the core business schema remain stable. A durable AI database is one where the semantic layer can be replaced without rewriting the system of record.

Data retention and privacy requirements also apply to embeddings and derived AI fields. If a source record must be deleted, related chunks, vectors, summaries, and caches should be removed according to the same policy. Derived data should not become a hidden way to keep information longer than the source system allows.

Database APIs exposed to agents should favor stable business contracts over raw table access. Stored procedures, GraphQL, MCP tools, or application APIs can encapsulate authorization and validation while still letting the agent retrieve current data. This reduces schema coupling between the language layer and the physical database.

Use observability to separate database failures from model failures. Query latency, deadlocks, authorization denials, vector-index performance, and embedding refresh backlog should be monitored independently. Otherwise an AI application can appear unreliable while the underlying issue is ordinary database contention.

The long-term goal is composability. Relational state, vector search, analytics, and AI orchestration should be able to evolve at different speeds. A database designed around stable keys, explicit lineage, and modular derived data gives the AI layer room to change without destabilizing the business system.

Schema evolution should also account for agents and generated queries. Renaming a column or changing a relationship can break prompts, tools, or retrieval logic that are not visible in ordinary application code. Treat AI-facing views and APIs as contracts and version them carefully when the underlying schema changes.

For sensitive data, create purpose-built projections rather than giving the AI layer access to full operational tables. A view that exposes only approved columns, rows, and derived fields can simplify authorization and reduce the chance that irrelevant sensitive data enters a prompt or tool response.

Testing should include adversarial queries and edge records. Very long text, missing metadata, duplicate business keys, unusual Unicode, and sparse relational fields can expose retrieval or tool assumptions that ordinary sample data hides.

Document which fields are authoritative, which are derived by AI, and which exist only for search. That distinction prevents downstream developers from accidentally using a generated summary or vector-derived label as though it were a trusted business fact.

Migration planning should treat vector indexes, derived summaries, and AI-facing views as rebuildable dependencies. If the core schema changes, the team should know which derived artifacts must be regenerated, which can remain compatible, and how to validate the new version before traffic moves.