CH20-06: Data and Analytics Services
Goal
Data and analytics services store facts, serve application requests, ingest events, process datasets, govern meaning, and expose insight. The common mistake is choosing a database or analytics platform because it is popular instead of because it matches the data shape, access pattern, ownership model, and operating responsibility.
The durable idea is:
Data service selection starts with data model, access pattern, trust, governance, and operating responsibility.
The Short Version
Start with the data job:
| Data Need | Common Azure Direction |
|---|---|
| Relational operational data | Azure SQL Database or Azure Database for PostgreSQL. |
| Globally distributed document or key-value access | Azure Cosmos DB. |
| Low-latency cache | Azure Cache for Redis. |
| Object and file-like storage | Azure Storage and Blob Storage. |
| Analytics lake storage | Azure Data Lake Storage. |
| High-volume event ingestion | Azure Event Hubs. |
| Data engineering and lakehouse processing | Azure Databricks. |
| SQL analytics and warehousing | Azure Synapse Analytics. |
| Integrated analytics and BI platform | Microsoft Fabric. |
| Data catalog, classification, and lineage | Microsoft Purview. |
| Reporting and dashboards | Power BI. |
Service Reference
| Service | Purpose | Use When | Avoid When | Common Prompt |
|---|---|---|---|---|
| Azure SQL Database | Managed relational database service. | You need relational transactions, SQL querying, constraints, indexes, and managed operations. | The data model is primarily document, key-value, object, or analytics lake storage. | Is this relational operational data? |
| Azure Database for PostgreSQL | Managed PostgreSQL database service. | Teams need PostgreSQL compatibility, extensions, relational behavior, and managed Azure operations. | The workload does not benefit from PostgreSQL compatibility or relational modeling. | Is PostgreSQL the right relational engine? |
| Azure Cosmos DB | Globally distributed NoSQL database with multiple API options. | You need low-latency distributed access, document or key-value patterns, flexible schema, and scale characteristics that match the model. | You need traditional relational joins and constraints as the core design. | Does the access pattern fit a distributed NoSQL model? |
| Azure Cache for Redis | In-memory cache and data structure store. | You need low-latency caching, session-like data, rate counters, or performance acceleration. | You treat the cache as the system of record without durability planning. | What can be cached safely? |
| Azure Storage / Blob Storage | Durable object storage. | You need object, file-like, backup, media, logs, or unstructured storage. | You need relational transactions or low-latency query patterns over structured records. | Is this durable object storage? |
| Azure Data Lake Storage | Analytics-oriented storage built on Azure Storage. | You need a scalable lake foundation for analytics, zones, files, and big data processing. | You expect a lake alone to create trusted data products or governance. | Where does analytical data land? |
| Azure Event Hubs | Streaming ingestion for high-volume event data. | Clickstreams, telemetry, logs, IoT events, or event streams need ingestion for analytics. | You need durable business command processing; use Service Bus. | Is this analytics stream ingestion? |
| Azure Databricks | Data engineering, lakehouse, machine learning, and Spark-based analytics platform. | Teams need collaborative notebooks, Spark processing, lakehouse workflows, and data engineering at scale. | A simpler SQL warehouse, managed BI platform, or pipeline tool meets the need. | Is this data engineering or lakehouse processing? |
| Azure Synapse Analytics | Analytics service for data warehousing, SQL analytics, Spark, and integration scenarios. | You need Azure analytics workloads that combine SQL analytics, warehousing, and data integration patterns. | Microsoft Fabric is the strategic integrated platform for the organization, or a focused Databricks platform already owns the workload. | Is this SQL analytics or warehouse-oriented? |
| Microsoft Fabric | Integrated SaaS analytics platform across data engineering, warehouse, lakehouse, real-time analytics, and BI. | The organization wants a unified analytics experience with OneLake and Power BI integration. | The team needs lower-level platform control or has existing specialized analytics platforms. | Do we need an integrated analytics platform? |
| Microsoft Purview | Data governance, cataloging, classification, and lineage. | You need to discover, classify, govern, and understand data assets across the estate. | You expect it to fix poor ownership, data quality, or access design by itself. | How do people find and trust data? |
| Power BI | Business intelligence, semantic models, reports, and dashboards. | Users need governed reporting, dashboards, and business-facing analytics. | You are trying to use it as the primary data engineering or operational data store. | How will insight be consumed? |
Commonly Confused Services
| Comparison | Short Answer |
|---|---|
| Azure SQL vs Cosmos DB | Azure SQL fits relational transactional models; Cosmos DB fits distributed NoSQL access patterns when the model fits. |
| Azure SQL vs PostgreSQL | Both are relational managed services; choose by engine compatibility, team skill, ecosystem, and application requirements. |
| Storage vs Data Lake Storage | Blob Storage is durable object storage; Data Lake Storage adds analytics-oriented capabilities and lake organization patterns. |
| Cache vs database | Redis accelerates access; it should not casually replace the system of record. |
| Event Hubs vs database | Event Hubs ingests streams; databases store queryable state. |
| Databricks vs Synapse vs Fabric | Compare by workload, team skill, governance model, integration, operating control, and platform strategy. |
| Purview vs Power BI | Purview helps govern and understand data; Power BI helps consume and present insight. |
Operational Versus Analytical Data
Operational data serves application behavior. It cares about transactions, consistency, latency, ownership, and correctness for user or business workflows.
Analytical data serves learning, reporting, forecasting, and decision support. It cares about ingestion, history, transformation, lineage, governance, quality, and consumption.
The same business fact may appear in both worlds. An order record in an operational database supports checkout, fulfillment, refunds, and support. A curated order dataset in a lakehouse or warehouse supports revenue reporting, demand forecasting, and business analysis.
Architecture Patterns
Data and analytics services often appear in patterns such as:
Transactional application data with Azure SQL or PostgreSQL.
Globally distributed product, profile, or session-like document access with Cosmos DB.
Cache-aside with Azure Cache for Redis.
Object and lake storage with Blob Storage and Data Lake Storage.
Streaming ingestion with Event Hubs.
Lakehouse or data engineering with Databricks or Fabric.
SQL analytics and warehousing with Synapse or Fabric.
Data governance with Purview.
Business dashboards with Power BI.
Cloud Comparison
AWS and GCP users will recognize the responsibility families: relational database, NoSQL database, object storage, cache, stream ingestion, data lake, warehouse, lakehouse, governance catalog, and BI. Use those mappings to translate vocabulary, then ask the deeper Azure questions: where does the system of record live, who owns the data product, how is data governed, what access pattern dominates, and how will operations and cost be managed?
Common Mistakes
Choosing a database before understanding query patterns and consistency needs.
Using one shared database across services that need independent ownership.
Treating a data lake as a dumping ground.
Choosing an analytics platform before defining governance and data products.
Using a cache as the system of record.
Moving data into analytics without lineage, classification, or quality checks.
Assuming a global database removes the need for partitioning and access-pattern design.
If Someone Asks You
How do I choose Azure data and analytics services?
A good answer is:
Start with the data shape, access pattern, ownership, and trust requirement. Azure SQL and PostgreSQL fit relational operational data. Cosmos DB fits distributed NoSQL access patterns. Redis fits caching. Blob Storage and Data Lake Storage fit object and analytics storage. Event Hubs ingests streams. Databricks, Synapse, and Fabric support analytics and lakehouse or warehouse work depending on platform strategy. Purview supports governance and lineage, and Power BI serves reporting and dashboards.
What You Should Remember
Match data services to data shape, access pattern, ownership, governance, scale, and operations.
Questions to Check Understanding
Why are operational and analytics data choices different?
When might Cosmos DB be a better fit than a relational database?
Why does governance matter in analytics service selection?