Introduction
Customers expect relevant, timely product suggestions as they browse, search, and purchase online. A mid-to-large e-commerce retailer with multi-region catalogs, high seasonal traffic, and separate systems for catalog, orders, and web events needed to modernize its recommendation capabilities to increase average order value (AOV), reduce time to convert, and personalize promotion exposure. The retailer used Microsoft Fabric SQL as the serving layer for a personalization platform that combines streaming behavior, curated history, and product embeddings—delivering measurable improvements in engagement and conversion while keeping costs and compliance under control.
Business challenges
· Siloed data: Events, orders, CRM, and product assets lived in separate systems, making it slow and complex to compute features for recommendations.
·
Freshness of insights: Session-level signals (like clicks or cart additions) needed to influence recommendations within minutes.
·
Low-latency experience: On-site recommendations had to feel instantaneous, with response times under 200 milliseconds at the 95th percentile (P95).
·
Scalability and cost: The platform needed to manage millions of requests per day without driving up inference costs.
·
Governance: Regional data restrictions, PII handling, and auditability were mandatory for compliance.
Solution overview
The solution is built on a fully managed architecture that uses Fabric SQL as the single serving layer for features and embeddings. This is paired with Lakehouse storage for heavy data transformations and Azure OpenAI for embedding generation and advanced ranking logic.
Key capabilities
· Real-time event processing: Ingests session events in near real time (via event streams and micro-batches).
·
Curated data foundation: Lakehouse layers (bronze, silver, and gold) organize historical data for feature engineering and model training.
·
Unified serving layer: Fabric SQL serves as the “hot” layer, exposing user/session features, product metadata, and embeddings through shortcuts to Lakehouse gold tables.
·
Fast candidate generation: Materialized views and result set caching accelerate recommendation lookups.
·
Lightweight scoring service: A stateless API that pulls features from Fabric, scores candidates, applies business rules, and returns the top-N product recommendations.
Architecture
-
Event ingestion
- Component: Fabric Event streams
- How it works: Client-side events (page views, searches, add-to-cart) are ingested via Fabric Event streams.
- Storage:
- Raw stream storage: Written to One Lake in Delta Lake format for historical analysis.
- Hot stream storage: Landed into Fabric Lakehouse tables for near real-time consumption.
-
Feature engineering
- Component: Fabric Lakehouse + Fabric Pipelines + Fabric Notebooks
- How it works:
- Raw events in One Lake are transformed via notebooks.
- Features such as session frequency, co-viewed products, and recency metrics are derived.
- Gold-level feature tables are stored in Lakehouse curated tables.
- Exposure: Gold tables are exposed to Fabric SQL DB using shortcuts, allowing downstream consumption via standard SQL.
-
Embedding pipeline
- Component: Azure OpenAI
- How it works:
- Pipelines call Azure OpenAI to generate embeddings.
- Product metadata embeddings and vector pointers are stored in Fabric Lakehouse tables.
- For large catalogs:
- Embeddings are offloaded to an approximate nearest neighbor (ANN) vector store.
- Metadata and vector references remain in Fabric SQL DB for joins with behavioral features.
-
Recommendation service
- Component: Fabric SQL DB + Application Service Layer (App Service)
- How it works:
- Candidate generation uses Materialized Views in Fabric SQL DB joining gold feature tables + product metadata.
- The service layer fetches candidates, applies model scoring, re-ranking, and business rules.
- Recommendations are returned to the client.
-
Monitoring
- Component: Power BI + Fabric SQL DB
- How it works:
- Power BI connects directly to Fabric SQL DB and Lakehouse tables for dashboards.
- KPIs tracked include recommendation CTR, revenue uplift, and embedding freshness.
Impact of Fabric SQL DB
· Native integration with Fabric ecosystem: Unlike Azure SQL DB, Fabric SQL DB is deeply integrated with Lakehouse, Event streams, and pipelines, removing the need for complex ETL jobs in AI/ML workloads.
· Unified compute and storage model: Fabric SQL operates on OneLake data without duplication, whereas Azure SQL DB typically requires data movement and synchronization.
· Single source for serving features: Eliminates ad-hoc file reads and complex orchestration, providing a unified feature source.
· Fast candidate generation: Materialized views and result set caching enabled quicker candidate queries.
· Governance and traceability: All features and scores are logged in Fabric, supporting explainability and audits during model reviews.
· Cost efficiency: Keeping only hot features and candidate lists in Fabric while storing the rest in the Lakehouse reduces compute costs for inference.
· Lower operational overhead: No need to manage provisioning, scaling, or sharding—Fabric SQL automatically leverages Fabric capacities.
· Simpler BI and operations: The same artifacts feed both the recommendation service and Power BI dashboards, removing sync errors and stale data.
Best practices
· Be selective with data persistence: Storing every historical column increases costs and slows query performance.
·
Optimize vector search: Avoid exact nearest-neighbor searches on full catalogs in SQL at scale; use hybrid filters or an ANN service instead.
·
Manage cache freshness: Keep cache time-to-live (TTL) short to prevent stale recommendations after promotions or inventory changes.
·
Invest in observability: Comprehensive instrumentation and reliable logging is essential for accurate attribution and performance tracking.
Interested in learning more?
As a Microsoft Fabric Featured Partner, MAQ Software brings deep expertise in helping organizations unlock the full potential of Microsoft Fabric. Whether you're looking for guidance on implementing data solutions or optimizing your existing platform, we’re here to support you every step of the way.
Contact us at CustomerSuccess@MAQSoftware.com or explore our apps or consulting services on Microsoft Azure Marketplace: