AZ-305 Data Integration and Analysis Design: Data Factory, Data Lake, Databricks, Synapse, Fabric and Stream Analytics

Data integration and analysis design decides how data moves from where it is created to where it is analysed, how it is transformed on the way, and which engine answers each kind of question. The official task has two skills: recommend a solution for data integration, and recommend a solution for data analysis. In practice that means choosing between a small set of Azure services, each with a clear job.

Two pieces of current Microsoft guidance shape every recommendation. Microsoft's AZ-305 learning content says it recommends Microsoft Fabric over new Azure Synapse Analytics deployments, and the Data Factory documentation points to Data Factory in Fabric as the next generation for new data integration. Azure Data Factory and Synapse remain fully supported and are the right answer when a scenario already runs them or needs a feature only they offer. As everywhere in AZ-305, the right design meets every stated requirement (residency, private networking, no code, least administrative effort, lowest cost) and nothing more. Choosing relational tiers and storage redundancy belongs to the two sibling topics on relational and on semi-structured and unstructured storage.

Azure Data Factory is a managed, serverless data integration service whose pipelines orchestrate activities: they move data, transform it and control the order in which steps run. A pipeline is a logical group of activities; linked services hold connection details, datasets describe the data, and triggers start runs.

Activities fall into three groups, and the exam expects you to pick the right one:

• Data movement: the Copy activity. It copies data from a source connector to a sink connector with built-in parallelism and no code. Data Factory has connectors for databases, files, SaaS applications such as Salesforce, REST and HTTP endpoints, SFTP and the Azure data stores. When data only has to move from one store to another without being reshaped, the Copy activity is the tool; transformation services add cost and complexity there.
• Data transformation. Mapping data flows are designed visually; Data Factory turns them into Spark code and runs them on clusters it provisions and scales through the Azure integration runtime, so a team that doesn't write code and doesn't want to manage clusters can join, aggregate and cleanse large files. External transformation activities dispatch work to other compute, for example the Databricks Notebook, Databricks Jar and Databricks Python activities, a stored procedure, or HDInsight jobs.
• Control flow. Lookup (read a small result set or a single value, such as a watermark), Get Metadata (properties of a file or folder), Web (call an endpoint and return its response), ForEach, If Condition, Until and Execute Pipeline. These return small results for decisions; they are not designed for bulk data movement.

Dependencies and retries make a pipeline a workflow. Each activity can depend on others with a condition (Succeeded, Failed, Completed, Skipped), so a notebook can wait until two Copy activities have both succeeded, and each activity has its own retry count and interval. That combination (SaaS and file connectors, then existing Databricks notebooks, with dependencies and retries) is why Data Factory, not Logic Apps, Event Grid or Stream Analytics, is the orchestrator for data pipelines. Logic Apps is an application-integration workflow service, Event Grid only routes events, and Stream Analytics only queries streams.

Synapse pipelines share most of Data Factory's engine and activities inside a Synapse workspace, with a few documented gaps; the one that matters most for design is that they don't support the Azure-SSIS integration runtime.

The integration runtime (IR) is the compute that runs Data Factory activities, and the right one depends on where the data is and how it is networked. Each linked service names its IR through connectVia; if it doesn't, the default autoresolve Azure IR is used.

Private Azure stores without VMs mean a managed virtual network. A self-hosted IR on a VM in a peered virtual network can also reach private endpoints, but then you own the VM, its patching and its scaling. A public-network Azure IR can't connect to a store whose public access is disabled.

Self-hosted IR high availability comes from multiple nodes. One self-hosted IR can be associated with up to four machines (nodes). You install the runtime on each machine and register it with the same authentication key; the logical runtime then keeps working if a node fails and spreads work across nodes. A second, separately registered self-hosted IR gives no automatic failover, and an availability set doesn't help because only machines registered to the IR take its work.

Location decides residency. The data factory's own region stores metadata and starts triggers; the integration runtime's location decides where copy and data-flow compute runs.

• A public-network autoresolve Azure IR makes a best effort to run the copy in the sink store's region, and falls back to the factory's region (or a nearby one) when it can't detect it.
• In a managed virtual network, an autoresolve Azure IR always uses the factory's region.
• For a strict residency requirement, create an Azure IR with an explicit region and point the linked services at it with connectVia. Moving or duplicating the factory isn't needed.

To run existing SQL Server Integration Services (SSIS) packages in Azure with little or no change, provision an Azure-SSIS integration runtime in Azure Data Factory. It is a fully managed cluster of Azure VMs dedicated to running SSIS packages. You host the SSIS catalog (SSISDB) in Azure SQL Database or Azure SQL Managed Instance (or use the file system or Azure Files as the package store), and keep deploying and managing packages with SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS). Pipelines run packages with the Execute SSIS Package activity.

• On-premises data: join the Azure-SSIS IR to a virtual network that is connected to the datacenter through ExpressRoute or a site-to-site VPN. A managed virtual network is a feature of the Azure IR, not the Azure-SSIS IR, and a self-hosted IR can't run SSIS packages itself (it can act only as a proxy for the Azure-SSIS IR's data access).
• Cost: the Azure-SSIS IR is billed while it runs, so stop it when no packages run and start it before they do, for example with pipeline activities scheduled around the package window. Scaling out adds nodes and therefore cost.
• Synapse estates: Synapse pipelines don't support the Azure-SSIS IR. A company that has standardised on Synapse still provisions the Azure-SSIS IR in a data factory and runs its packages there, alongside the Synapse pipelines.

Rebuilding packages as mapping data flows or Databricks notebooks is a rewrite. It can be the right long-term modernisation, but lift-and-shift with the Azure-SSIS IR is the minimal-change route.

A trigger decides when a pipeline runs, and the choice follows from whether work is driven by time, by time windows with history, or by events.

As a rule, irregular arrivals that need prompt processing suit an event trigger, because the run starts when the file is created; schedule and tumbling window triggers run on the clock whether or not new data has arrived.

Incremental loading copies only what changed since the last run. Microsoft documents four incremental-copy patterns for Data Factory:

• Watermark (delta column): a Lookup reads the stored old watermark, another Lookup reads the current maximum of a column such as LastModifiedDate, the Copy activity's source query selects rows between the two, and a stored procedure saves the new watermark. It needs no database feature, only a reliable column that changes with every update.
• Change Tracking in SQL Server or Azure SQL Database: more precise than a watermark (it sees deletes), but it must be enabled on the database and the tables.
• LastModifiedDate for file sources: copy only the files whose last-modified time falls after the previous run.
• Time-partitioned folder or file names (for example /yyyy/mm/dd/): copy only the newest partition.

Separately from those four, SQL Server and Azure SQL also offer change data capture (CDC), which records the full row changes, and Data Factory can read it with its CDC resource or a mapping data flow.

Copying the full table and filtering afterwards doesn't reduce what crosses the network. When the source sits on premises behind a firewall that allows no inbound connections, the incremental copy still runs on a self-hosted IR inside that network.

Azure Data Lake Storage is a set of capabilities on an Azure Storage account with the hierarchical namespace enabled, and it is the standard landing and curated store for Spark and SQL analytics engines. (Data Lake Storage Gen1 is retired; "Data Lake Storage" now means this.) Engines connect through the ABFS driver.

• Real directories: with a flat Blob namespace a "folder" is only a name prefix, so renaming or deleting it means touching every blob. With a hierarchical namespace, directory rename and delete are single, atomic metadata operations, which matters to Spark jobs that write to a temporary folder and rename it.
• POSIX-style ACLs on directories and files, alongside Azure RBAC.
• In-place upgrade: an existing general-purpose v2 or Blob Storage account can be upgraded to enable the hierarchical namespace without copying data. The upgrade is one-way, writes are disabled while it runs, some features may be incompatible, and Microsoft recommends validating it in a non-production environment first. Workloads that use the WASB driver should move to ABFS. Creating a new account and copying the data also works, but moves every byte.

How authorization works. Azure RBAC is evaluated first; if a role assignment (such as Storage Blob Data Contributor) grants the operation, ACLs aren't checked. With no role assigned, ACLs decide, and they follow POSIX rules:

Access ACLs control the object they are set on. Default ACLs exist only on directories and are copied to child items created afterwards, so when another identity or pipeline creates new subfolders (such as daily date folders), a default ACL entry is what gives the principal access to them. Read on parent directories isn't needed just to traverse them, and a role assigned at container scope is broader than a folder-level least-privilege requirement allows.

Azure Databricks is a managed Apache Spark platform for code-first data engineering, data science and machine learning, built around the lakehouse: open Delta Lake tables in the data lake governed centrally by Unity Catalog. Choose it when teams write Spark code in Python, SQL or Scala, collaborate in shared notebooks, and need governance across workspaces.

Delta Lake stores data as Parquet files plus a transaction log.

• Writes such as MERGE (upsert), UPDATE and DELETE commit atomically, so concurrent readers see the previous version or the new one, never a half-applied batch. Overwriting plain Parquet partitions has no transaction log, so it gives neither isolation nor history.
• Every commit creates a table version that time travel can query (VERSION AS OF, TIMESTAMP AS OF), but only while the files and log entries still exist. VACUUM deletes unreferenced files older than delta.deletedFileRetentionDuration (7 days by default), and log entries are kept for delta.logRetentionDuration (30 days by default). To query a table as it was up to a month ago, raise both retention properties to cover that window.

Unity Catalog is the account-level governance layer. One metastore is attached to multiple workspaces, so grants on catalogs, schemas, tables, columns and rows are defined once and enforced in every workspace, and it captures data lineage automatically. A separate Hive metastore per workspace means separate permission management and no central lineage.

Compute type drives cost. All-purpose compute is for interactive analysis and shared notebooks; it can be shared and auto-terminates after inactivity. Job compute is created by the jobs scheduler for a run and terminated when the run ends, and is billed at a lower rate. Scheduled jobs should run on job compute (one per job gives isolation), keeping all-purpose clusters for interactive work. Serverless compute for jobs and notebooks is a further option where it is available to the workspace.

In an Azure Synapse workspace, the serverless SQL pool queries files in the data lake on demand and bills per data processed, while a dedicated SQL pool is a provisioned MPP data warehouse for persisted tables with consistent performance. Synapse remains supported; for a new analytics platform, current Microsoft guidance is Microsoft Fabric.

Reducing serverless cost means reducing data read. Store data as Parquet (compressed and columnar, so a query reads only the columns it references) and partition folders (for example by year and month), filtering with filepath() so whole folders are skipped. The general rule: serverless cost follows the bytes scanned, so only changes that make queries read less data reduce it.

Isolating workloads in a dedicated pool uses workload management. Workload groups reserve a minimum percentage of resources and cap the maximum for a set of requests, and workload classifiers assign requests (by user, role, label or session context) to a group. With a group reserved for reporting and another capped for loads, a nightly load can't take resources from Power BI users. Leaving everything in the default workload group gives no isolation.

Synapse also includes Apache Spark pools and Synapse pipelines; as noted earlier, its pipelines don't run SSIS packages.

Microsoft Fabric is a software-as-a-service analytics platform that brings data integration (Data Factory), data engineering (Spark), data warehousing, real-time intelligence, data science and Power BI together, with every workload storing its data in OneLake. You buy a Fabric capacity and create workspaces; there are no separate Azure resources per engine to deploy and govern. Microsoft recommends Fabric over new Synapse deployments. Because one capacity covers every engine, Fabric minimises the number of services to deploy and govern; assembling separate Azure services such as Data Factory, Databricks and storage also works, but means more resources to manage.

OneLake is a single logical lake per tenant, storing tables in the open Delta Parquet format. Three features avoid building copy pipelines:

Mirroring is the zero-ETL answer for operational analytics. Reporting queries that slow a production Azure SQL Database move to the mirrored copy in Fabric, where changes arrive in near real time and the tables can be joined with lakehouse data in Spark, SQL and Power BI. A read replica or read scale-out also offloads reads, but it doesn't put the data where Spark and the lakehouse can use it. For Azure Cosmos DB for NoSQL, mirroring replicates container data without consuming the request units provisioned for transactions and without using the analytical store; Microsoft lists continuous backup as a prerequisite and suggests the 7-day tier, which is free, when you enable it for mirroring. Check the current prerequisites on Microsoft Learn, as mirroring sources are still evolving. Azure Synapse Link for Azure Cosmos DB is no longer supported for new projects; Microsoft directs customers to Fabric mirroring.

Direct Lake is a Power BI semantic-model storage mode for Fabric. The model reads Delta tables in OneLake directly, loading the columns a query needs into memory, so reports perform close to Import mode without a scheduled refresh that copies data, and they reflect new data after each load.

Real-time analysis in Azure uses Azure Stream Analytics for continuous SQL-like queries over streams, organises data into hot, warm and cold paths, and uses Azure Data Explorer for interactive analytics over large volumes of logs and telemetry.

Azure Stream Analytics

Stream Analytics is a fully managed real-time analytics service. A job reads inputs such as Event Hubs, IoT Hub or Blob/Data Lake Storage, runs a continuous query in a SQL-like language, and writes to outputs such as Power BI, Azure SQL Database, Cosmos DB, Data Lake Storage, Event Hubs, Service Bus and Azure Functions. It needs no Spark skills or cluster; Databricks Structured Streaming is the code-first alternative. Data Factory and serverless SQL work on data at rest.

Reference data enriches a stream with a static or slowly changing dataset, held in memory and joined with no time window. It can come from Blob Storage, Data Lake Storage or Azure SQL Database; for SQL Database you set a refresh rate (and optionally a delta query) so changes are picked up. A SQL table isn't a streaming input, and calling a function per event adds code and latency.

Hot, warm and cold paths

Event Hubs retention is measured in days (up to 7 on Standard, 90 on Premium and Dedicated), so it is never a multi-year archive; Capture to the lake is. A live Power BI streaming tile isn't queryable history, and a nightly warehouse load is too stale for a warm path.

Azure Data Explorer

Azure Data Explorer is a fully managed analytics service for large volumes of log, telemetry and time-series data. It ingests in near real time (queued ingestion in minutes, or streaming ingestion in seconds), indexes data automatically, and answers ad-hoc Kusto Query Language (KQL) queries in seconds, with built-in time-series functions such as series_decompose_anomalies() and series_decompose_forecast(). The same engine powers Real-Time Intelligence eventhouses in Fabric.

Two table policies drive its cost:

• The retention policy decides how long data stays in the table before it is deleted.
• The caching policy decides how much of it stays in the cluster's local hot cache (SSD and memory). Data outside the hot cache is still queryable, only slower.

Matching the hot cache to the period queried often and the retention to the period that must stay queryable lets you size the cluster for the hot data only. Continuous export copies data to an external table in the lake and deletes nothing. It is a shorter retention policy, set alongside the export, that removes older data from the table, so queries over that data would then need external tables. Use streaming ingestion only when you need seconds-level latency.