Design data integration and analytics solutions in Azure
Turn batch, streaming, storage, transformation, latency, security, and analytics requirements into an accountable Azure data architecture.
Suggested study time: 86 minutes • Intermediate • Complete original rewrite with a concise summary for every topic
By João Ricardo Dutra••Complete original content
1. Start with the integration outcome and data behavior
An Azure architect must recommend how data is ingested, moved, transformed, stored, queried, and presented during a cloud migration. The design must handle volumes and complexity that exceed a traditional database while preserving security, compliance, availability, and cost control.
Consider Tailwind Traders, a fictional global retailer and manufacturer. Product inventory, sales history, production logs, websites, point-of-sale systems, social channels, and IoT quality-control streams must be combined, reshaped into analytical models, and retained for querying, reporting, machine learning, and visualization.
Design integration with .
Design a data lake with .
Design engineering and analytics with .
Design enterprise analytics with .
Select hot, warm, and cold data paths.
Design real-time processing with .
The module assumes conceptual and practical familiarity with data integration. These decisions map to the AZ-305 skills for designing data storage and recommending data integration and analysis solutions.
Topic summary
Capture sources, velocity, volume, formats, latency, consumers, retention, security, and operating constraints before selecting an Azure service.
2. Separate integration, storage, processing, and serving responsibilities
A durable design decomposes the data platform. Ingestion receives batch files, database changes, API payloads, and event streams. Orchestration coordinates dependencies. preserves raw and curated data. Compute transforms or models it. Serving layers expose results to dashboards, applications, analysts, and machine-learning systems.
Architecture responsibilities.
Layer
Question
Representative Azure choices
Ingest and orchestrate
How does data arrive and which steps must run?
, Azure ,.
Store
Which format, namespace, retention, and access model are required?
,,,.
Process and analyze
Is the work relational, Spark-based, streaming, or mixed?
,,.
Serve and act
Who consumes results and at what latency?
, SQL endpoints, applications, alerts, .
Treat the platform as connected responsibilities rather than one all-purpose product.
Topic summary
Choose each platform component for a clear responsibility and define the contracts, security, and failure behavior between layers.
3. orchestrates hybrid ETL and ELT
is a managed cloud service for hybrid ETL, ELT, data movement, and workflow orchestration. Pipelines can collect data from disparate cloud and on-premises stores, coordinate transformations at scale, and publish prepared data to an analytical destination.
A common production flow has four stages: connect and collect into a central location; transform and enrich with mapping data flows or external compute such as ; publish through version control and CI/CD; and monitor schedules, runs, dependencies, and failures.
Data Factory does not need to be the storage or transformation engine. It can copy into , dispatch a notebook or stored procedure, and load while retaining the operational control plane for the workflow.
Topic summary
Use when the core requirement is managed movement and orchestration across heterogeneous, hybrid data systems.
4. Pipelines, activities, datasets, and linked services describe the workflow
Core objects.
Object
Responsibility
Design implication
Pipeline
Logical container for related activities.
Defines sequencing, parameters, control flow, retries, and deployment unit.
Activity
One movement, transformation, or control step.
Choose Copy, stored procedure, notebook, lookup, validation, or another operation.
Dataset
Named view of data used as an input or output.
Describes schema or location without embedding all connection details.
Linked service
Connection to a data store or compute service.
Centralizes endpoint, authentication, and integration-runtime association.
Data flow
Visual transformation graph executed on managed Spark.
Supports low-code transformations under pipeline scheduling and monitoring.
Objects separate connection, data shape, execution step, compute bridge, and orchestration.
Topic summary
Model connections, data, individual work, compute, and orchestration as separate Data Factory objects so each can be secured and changed independently.
5. The integration runtime determines where work executes
The integration runtime is the compute bridge between activities and linked services. It moves data, dispatches external transformations, executes mapping data flows, or runs SSIS packages. Its location affects network reachability, data residency, throughput, and cost.
Integration runtime choices.
Type
Primary use
Network boundary
Azure
Cloud-to-cloud copy, managed data flows, and activity dispatch.
Public endpoints or managed virtual network and private endpoints where supported.
Self-hosted
Movement between private/on-premises systems and cloud or other private systems.
Customer-provided Windows host with outbound connectivity.
Azure-SSIS
Managed execution of existing SQL Server Integration Services packages.
Azure-managed cluster that can integrate with a virtual network.
Azure Synapse pipelines support Azure and self-hosted runtimes, but not Azure-SSIS. Place compute near the data when possible and explicitly constrain the region when residency requirements prohibit automatic location selection.
Topic summary
Select the integration runtime from network reachability, transformation type, SSIS compatibility, geography, performance, and compliance.
6. Data Factory fits hybrid preparation and movement scenarios
For Tailwind Traders, linked services connect on-premises and cloud stores; a self-hosted runtime reaches private systems; datasets identify source and destination structures; activities run copies and stored procedures; and the pipeline publishes curated data for or machine learning.
Use the authoring and monitoring experience when low-code development improves delivery.
Validate current connectors for Azure, , AWS, Google Cloud, SaaS, databases, and files.
Use managed infrastructure when elasticity and reduced server administration matter.
Adopt Git integration, CI/CD, parameterization, secrets management, alerts, and rerunnable pipelines for production.
For new platform designs, compare Data Factory in , which Microsoft documents as the next generation of .
Topic summary
Data Factory is strongest when many sources, hybrid networking, repeatable orchestration, low-code authoring, and operational monitoring matter more than one analytical engine.
7. is Blob optimized for analytics
A data lake centralizes structured, semi-structured, and unstructured data in raw or native formats. is not a separate storage service: it is a set of analytics-oriented capabilities enabled on by turning on the hierarchical namespace.
It combines object-storage economics, tiers, replication, durability, lifecycle management, and disaster-recovery options with file-system semantics, Hadoop-compatible access, directory operations, and file-level security. Gen1 retired on February 29, 2024; new designs use Gen2 capabilities.
Topic summary
Use Gen2 when a scalable Blob-based repository also needs analytics-friendly directories, protocols, and fine-grained access.
8. Hierarchical namespace and ABFS optimize analytical file operations
With hierarchical namespace enabled, containers act as file systems and data is organized into real directories and nested paths. Renaming or deleting a directory becomes an atomic metadata operation instead of enumerating every blob that shares a prefix, improving Spark and Hadoop workflows.
The Azure Blob File System driver exposes the abfs or encrypted abfss URI schemes to HDFS-compatible engines. Apache Spark and other frameworks can access data without a separate copy. Plan partitions and file sizes carefully: a technically scalable lake can still perform poorly when it contains huge numbers of tiny files or badly chosen folders.
Hierarchical namespace adds first-class directory operations on the Blob foundation.
Topic summary
Enable hierarchical namespace for directory semantics and ABFS access, then design partitioning and file layout for the consuming engines.
9. RBAC and POSIX-like ACLs solve different authorization scopes
Azure role-based access control grants broad data actions and inherits through Azure resource scopes. POSIX-like access control lists can grant read, write, and execute permissions to users, groups, service principals, and managed identities at file and directory level. Default ACLs influence newly created children; changing existing descendants requires a recursive operation.
Use Microsoft Entra groups instead of assigning many individual ACL entries. Combine least privilege, managed identities, private endpoints, firewall rules, encryption with Microsoft-managed or customer-managed keys, logging, and lifecycle policy. Replication choices follow options; select redundancy from recovery and data-residency requirements rather than a blanket default.
Topic summary
Use RBAC for coarse Azure scope and ACLs for file-system detail, supported by identity, network, encryption, audit, and redundancy controls.
10. Ingest, access, and authorize are distinct lake operations
Unplanned or bulk files can enter through AzCopy, Azure CLI, PowerShell, SDKs, or Azure . Relational sources can be moved with from services such as ,, and . Streaming data can arrive through Azure ,,, or an appropriate processing engine.
Consumers can use Azure , PowerShell, Azure CLI, HDFS tools, REST APIs, or language SDKs. Access tooling does not replace authorization: every caller still needs a supported identity or credential and the appropriate RBAC or ACL decision.
Topic summary
Design separate ingestion, consumption, and authorization paths; the tool that reaches a lake does not define who is allowed to use the data.
11. A data lake separates storage growth from analytical compute
Tailwind Traders can land websites, point-of-sale, social, IoT, logs, JSON, CSV, images, and other native formats in one governed repository. Data engineers and analysts can then select ,,, or another compatible engine without coupling retention to one compute cluster.
Data lake scenarios.
Requirement
Why fits
Massive analytical history
scales independently from compute and preserves raw detail.
Many formats and producers
A shared repository reduces silos while retaining native data.
and streaming landing
Files and event outputs can use the same durable lake.
Hadoop and Spark analytics
ABFS and hierarchical namespace support common big-data access patterns.
Topic summary
Choose a lake when diverse raw data must be retained centrally and processed by independently scalable analytical engines.
12. Compare plain Blob capabilities with a lake-enabled account accurately
is built on , so the comparison is not two unrelated products. A general-purpose v2 or premium block blob account becomes lake-enabled when hierarchical namespace is turned on. Both retain Blob durability, replication, tiers, lifecycle policies, and APIs, subject to the published feature-support matrix.
design comparison.
Criterion
Blob account without hierarchical namespace
Lake-enabled Blob account
Namespace
Flat object namespace with simulated folders in names.
Hierarchical directories and atomic directory operations.
Authorization
Azure RBAC, SAS, and key-based mechanisms.
Azure RBAC plus POSIX-like file and directory ACLs.
Analytics access
Blob APIs and supported connectors; ABFS can address the endpoint.
Blob and Data Lake APIs with HNS-aware ABFS behavior.
Strongest fit
Object data such as media, backups, archives, and application blobs.
Analytical lakes where directory operations and fine-grained file access matter.
Topic summary
A data lake is a Blob account with hierarchical analytics capabilities; select HNS from workload semantics and supported-feature requirements.
13. unifies data engineering, analytics, and AI
is a managed analytics platform centered on Apache Spark and lakehouse patterns. It supports large-scale batch and streaming data, SQL analytics, collaborative notebooks, data engineering, and machine-learning lifecycles. Current development commonly uses Python and SQL, with additional support for Scala, R, Java, and platform APIs depending on the feature.
Its lakehouse model combines flexible object storage with reliable tables and governance. Delta Lake adds transactional behavior to lake data, while Unity Catalog can centralize permissions, lineage, and discovery across workspaces and data assets.
Topic summary
Use when Spark-scale engineering, lakehouse reliability, collaboration, SQL analytics, or integrated AI workflows are central.
14. Control plane and compute plane define the trust boundary
The control plane hosts managed platform services such as the web application, workspace metadata, job coordination, access controls, and control APIs. Data processing happens in a compute plane. Classic compute runs resources inside the customer Azure subscription; serverless compute runs in a Databricks-managed serverless compute plane.
The source material describes all runtime clusters as residing in the customer subscription, which remains true for classic compute but not for every current serverless option. The architecture must document which compute type is used, where data is processed, how storage is reached, and whether private connectivity and egress controls satisfy policy.
Compute choice changes the network and operational boundary even when the workspace experience looks similar.
Topic summary
Treat classic and serverless compute as different deployment boundaries and validate networking, data access, governance, and regional requirements.
15. Databricks environments target distinct personas
workload experiences.
Experience
Primary users
Typical work
Databricks SQL
Analysts and BI developers.
SQL warehouses, queries, dashboards, and integration over lakehouse data.
Data engineering and science
Data engineers and data scientists.
Collaborative notebooks, batch and streaming pipelines, Spark transformations, and exploratory work.
Machine learning and AI
Data scientists and ML engineers.
Experiment tracking, feature engineering, training, serving, monitoring, and MLOps.
Data can arrive through , Apache Kafka, Azure , or and remain in or other supported systems. Separate interactive development compute from production jobs, apply autoscaling and policies, and govern access through identities rather than notebook secrets.
Topic summary
Select the Databricks experience and compute policy from the user persona and lifecycle, not merely from a preference for notebooks.
16. Databricks is strongest when collaboration and Spark-scale processing coincide
For Tailwind Traders manufacturing, engineers, scientists, and analysts need to combine batch and streaming data, work with Python or SQL, connect to Azure stores, and deliver insights to . A shared Data Engineering workspace is a stronger fit than a SQL-only or ML-only experience when the immediate requirement is collaborative transformation rather than model production.
Prepare complex and unstructured data for review.
Build recommendation, churn, intrusion-detection, and predictive-maintenance solutions.
Create reusable multi-step engineering pipelines over lake data.
Use separate jobs and compute for production reliability.
Adopt governance, cost controls, observability, and lifecycle management before broad workspace adoption.
Topic summary
Choose Databricks when collaborative Spark engineering and flexible data processing are requirements; add SQL or ML capabilities when those workloads are actually needed.
17. combines SQL, Spark, and integration
is an enterprise analytics service that brings together data warehousing, big-data processing, data integration, and a unified authoring experience. Synapse SQL supplies distributed T-SQL; Apache Spark handles engineering and machine learning; pipelines orchestrate ETL and ELT; and Synapse Studio centralizes development and monitoring.
A dedicated SQL pool uses massively parallel processing. A control node receives T-SQL, plans work, and coordinates compute nodes that process distributed data. PolyBase can read and load relational or file-based external data, enabling data virtualization and efficient movement between a lake and SQL tables.
Topic summary
Use Synapse when a coordinated workspace for distributed SQL, Spark, lake access, and integration is preferable to assembling separate tools.
Reserved MPP data warehouse with persisted relational tables.
Predictable warehouse performance and provisioned capacity are required.
Serverless SQL pool
On-demand T-SQL over lake files.
Exploration, virtualization, and bursty file queries need no reserved cluster.
Apache Spark pool
Managed Spark for engineering, preparation, and ML.
Distributed code in Python, Scala, SQL, or C# is needed.
Synapse Pipelines
Data movement, control flow, and code-free transformations.
Orchestration should remain inside the Synapse workspace.
Synapse Link
Near-real-time analytics over supported operational stores.
Analytical queries should minimize impact on transactional sources.
Synapse Studio
Unified web IDE for data, develop, integrate, monitor, and manage.
Teams need one workspace experience and CI/CD surface.
Synapse is a workspace with multiple engines, not a single warehouse shape.
Topic summary
Select individual Synapse engines by workload and use the workspace to coordinate them under one security and development model.
19. Descriptive, diagnostic, predictive, and prescriptive analytics need different engines
Analytical questions and Synapse patterns.
Mode
Question
Possible design
Descriptive
What is happening?
Dedicated SQL warehouse for curated history or serverless SQL over prepared lake files.
Diagnostic
Why did it happen?
Interactive serverless SQL or Spark exploration that combines additional lake data.
Predictive
What is likely to happen?
Spark pools integrated with or .
Prescriptive
What action should be taken?
Spark or SQL models combined with Synapse Link and streaming services for timely decisions.
Topic summary
Start with the business question and latency, then choose the Synapse engine and supporting service that can produce the required evidence or action.
20. Data Factory and Synapse Pipelines share concepts but serve different platforms
Synapse Pipelines uses the same lineage of data-integration concepts as , including pipelines, activities, linked services, datasets, Azure integration runtime, self-hosted runtime, and mapping data flows. Synapse adds native orchestration of its SQL and Spark resources inside an analytics workspace; remains a dedicated integration service with Azure-SSIS support and broader independent deployment patterns.
Do not rely on a static feature comparison. Connector counts, template galleries, cross-region behavior, monitoring, and supported runtimes evolve. Microsoft currently positions Data Factory in as the next-generation integration experience, so a new design should compare Azure PaaS requirements, existing investments, migration effort, Fabric capacity and governance, and regional availability.
Topic summary
Use Data Factory for a standalone hybrid integration platform, Synapse Pipelines for orchestration inside Synapse, and evaluate Fabric Data Factory for new unified analytics designs.
21. A warm path analyzes a recent stream in near real time
The warm path processes events as they flow, persists a recent or queryable subset, and sends timely results to analytical clients. can aggregate, filter, join reference data, and evaluate temporal windows; Azure can buffer events; can add custom reactions; and or can serve recent results.
In an IoT utilization scenario, raw device signals may not directly say whether a machine is productive. The warm path joins signals with material, shift, or equipment reference data and applies rules that can differ by company. Its output is near real-time insight with a retained operational view.
Topic summary
Use a warm path for near-real-time aggregation and a recent serving store when seconds-to-minutes insight matters but every raw event need not be queried instantly.
22. A cold path preserves history and builds batch views
The cold path contains a durable long-term store, a batch-processing layer, and serving views. Original messages can remain in or while orchestrates transformations on , Apache Spark, Azure , or another engine.
Precomputed aggregates make long-range queries responsive and can be combined with recent warm-path results. This path fits legal retention, rarely accessed history, training data, changing pivots, retrospective analysis, and model building. access tiers and lifecycle rules control cost; the compute layer should be independently scheduled and scaled.
Topic summary
Use a cold path for immutable history, long retention, batch recomputation, compliance, and machine-learning datasets where latency is secondary to completeness and cost.
23. A hot path drives immediate alerts and actions
The hot path processes or displays latency-sensitive events in seconds or less. It supports operational alerting, fraud detection, customer-portal reactions, safety controls, and live fleet decisions. Keep the path short, partitionable, observable, and resilient because every additional dependency raises latency and failure risk.
Hot, warm, and cold path selection.
Path
Latency and scope
Representative need
Hot
Immediate event-by-event action, seconds or less.
Alert or update a live experience as data arrives.
Warm
Near-real-time windows and a recent subset.
Operational dashboard, utilization, or rolling aggregate.
Cold
processing over complete long-term history.
Compliance retention, retrospective analytics, and model training.
One source can feed all three paths when the business needs immediate action and complete history.
Topic summary
Select hot, warm, and cold paths from latency, history, query, retention, and action requirements; many architectures deliberately combine them.
24. is managed complex event processing
is a fully managed PaaS stream-processing engine. It continuously consumes event streams from applications, sensors, clickstreams, social feeds, and connected devices, applies SQL-like temporal logic, and routes results to actions, dashboards, or durable stores.
Events need time semantics so the engine can reason about order and windows. Supported event serializations include JSON, CSV, and Avro. A job has at least one streaming input, a query, and one or more outputs. The no-code editor complements query authoring for teams with mixed skills.
Topic summary
Use for managed, SQL-like processing of time-based event streams when infrastructure administration should be minimized.
25. Stream and reference inputs play different roles
Streaming inputs are unbounded event sequences. Current first-class sources include Azure ,,, Gen2, and Apache Kafka. is a general high-throughput event ingestor; IoT Hub adds device-oriented identity and messaging.
Reference inputs are static or slowly changing datasets used for lookup and correlation. , Gen2, and can provide reference data. Joining a truck GPS stream to depot, route, or driver reference data turns coordinates into business context.
Topic summary
Use streaming inputs for changing events and reference inputs for enrichment; choose the ingestion service from producer protocol, scale, and device requirements.
26. Queries transform events through time windows
The Stream Analytics query language can filter, project, sort, aggregate, join, detect patterns, call geospatial functions, and enrich a stream over time. Tumbling windows are fixed and nonoverlapping; hopping windows can overlap on a fixed cadence; sliding windows emit when membership changes; session windows follow bursts separated by inactivity; snapshot windows group events with the same timestamp.
Define event time with TIMESTAMP BY when the payload carries the authoritative timestamp. Design for late and out-of-order events, reference-data size, partition keys, and deterministic output. Test queries against representative data rather than only a clean sample.
Time semantics and partitioning are part of the query architecture.
Topic summary
Choose the window and event-time policy from the business question, then test ordering, lateness, partitioning, and state under realistic load.
27. Outputs can serve dashboards, storage, analytics, or automation
Representative Stream Analytics outputs.
Outcome
Possible outputs
Architecture note
Durable stream or lake
Azure ,, Gen2.
Preserve events for replay, batch analytics, or downstream consumers.
Operational serving
,, Azure .
Match write rate, key design, idempotency, and query pattern.
Analytics and visualization
,,.
Separate live visualization from complete historical analysis when needed.
Action
, Azure queues or topics.
Design retries, duplicate tolerance, throttling, and downstream failure handling.
A job can define multiple outputs with separate INTO clauses. Partition the output where supported and avoid a single writer becoming the bottleneck. File outputs need stable schemas, especially for Avro or Parquet.
Topic summary
Route each result to the sink that matches durability, query, visualization, or action needs, and design each output for partitioning and failure.
28. Stream Analytics supports several real-time enterprise patterns
Real-time scenarios.
Requirement
Stream-processing pattern
Building telemetry
Aggregate temperature, humidity, and fan runtime to optimize controls.
Web logs and clickstreams
Generate immediate product or content recommendations.
Geospatial analytics
Track fleets, detect geofences, or correlate weather and location.
Remote monitoring
Detect anomalies and support predictive maintenance for valuable equipment.
Point-of-sale fraud
Identify unusual amount, location, or behavior and trigger alerts.
For Tailwind Traders, GPS telemetry from delivery trucks can enter through Azure or , be enriched and evaluated by Stream Analytics, appear in , trigger an operational response, and land in a lake for later route analysis.
Topic summary
Stream Analytics is a strong fit when continuous telemetry must become a dashboard, alert, enriched record, or downstream event with minimal operational overhead.
29. Capacity, partitioning, reliability, security, and edge placement complete the design
Streaming Units represent allocated compute and memory. Scale and partition the job so input partitions, query logic, and output writers can operate in parallel. Monitor input events, output events, watermark delay, errors, utilization, and backpressure; prove the topology at peak rate and during sink throttling.
Cost: pay for provisioned Streaming Units and remove idle or oversized capacity.
Reliability: use checkpoints, understand exactly-once processing versus output delivery guarantees, and design replay or failover.
Security: use TLS 1.2, managed identities where supported, encrypted checkpoints, least privilege, and private networking options.
Placement: use cloud jobs for scale and Stream Analytics on IoT Edge or Azure Stack for supported ultra-low-latency hybrid scenarios.
Operations: version queries and infrastructure, automate deployment, test schema changes, and document late-event policy.
Topic summary
A production streaming design couples SQL logic with capacity, partitions, delivery semantics, security, monitoring, schema governance, and recovery.
30. Apply the requirements to the module assessment
Assessment reasoning.
Requirement
Best answer
Why
Combine on-premises reference data with cloud manufacturing logs and run stored procedures.
.
Hybrid linked services, a self-hosted runtime, orchestration, and stored-procedure activities satisfy the movement and control requirement.
Ingest and store real-time data from websites, point of sale, and social sources for later analysis.
.
The lake accepts diverse data at high scale and can receive streaming outputs for multiple analytical engines.
Retain 5 TB of rarely used legal data cost-effectively without deletion.
Cold storage path and appropriate archive/cool lifecycle policy.
Long retention and rare access favor low-cost durable storage over hot serving capacity.
can transform data, and can process live events, but neither replaces the integration and retention requirement expressed in these particular answers. Read the question for the dominant responsibility.
Topic summary
In exam scenarios, identify the dominant responsibility - orchestration, analytical storage, processing, or serving - before choosing a service.
31. Use a decision matrix instead of a product-first design
Service recommendation matrix.
Service
Recommend when
Do not treat as
Hybrid movement, ETL/ELT orchestration, low-code data flows, SSIS migration, and monitoring dominate.
The permanent analytical store or one universal compute engine.
Diverse raw and curated files need massive scale, hierarchical access, and independent compute.
A query engine or workflow orchestrator.
Spark engineering, streaming, lakehouse, collaborative data science, or ML lifecycle dominates.
A substitute for every relational warehouse or simple copy job.
Distributed SQL, Spark, integration, and a unified enterprise workspace are required.
One fixed engine or an automatic default for every new analytics platform.
SQL-like managed event processing and low-latency routing are required.
Long-term system of record or general batch engine.
The services compose: the correct answer may be an architecture, not a single product.
Topic summary
Recommend the smallest set of services that covers movement, storage, processing, latency, governance, and consumption without overlapping responsibilities unnecessarily.
32. Consolidate the chapter and extend the design safely
You can now distinguish integration from analysis, design Data Factory objects and runtimes, build a governed lake, place Spark and SQL workloads, combine hot/warm/cold paths, and route streaming results. Before implementation, validate region support, quotas, connectors, pricing, networking, security, and migration guidance against current Microsoft documentation.
Useful Copilot prompts include: compare Azure integration and analytics services for a stated workload; explain two practical use cases for each; or propose a secure batch-and-stream design that integrates SQL, NoSQL, and IoT into a warehouse and lake. Treat generated advice as a hypothesis and verify it against requirements and authoritative sources.
Validate every recommendation against current product documentation, then test the complete path with realistic data, failures, security controls, and consumer workloads.
