Microsoft AZ-104 - Microsoft Azure Administrator - Azure Data Concepts

Azure Data Concepts Overview

The diagram shows how day-to-day business transactions become data for reporting and analysis, and where three common data roles contribute.

OLTP versus OLAP

OLTP and OLAP describe workload patterns, rather than two exclusive categories of database products. A database platform can support both, although systems are often optimized for one.

Characteristic	OLTP — Online Transaction Processing	OLAP — Online Analytical Processing
Purpose	Run business operations.	Analyze data and support decisions.
Example	Record an order or update inventory.	Calculate sales trends over several years.
Operations	Many short reads, inserts, updates, and deletes.	Complex queries and aggregations across large datasets.
Data	Primarily current operational records.	Often historical data combined from multiple sources.
Design	Often normalized to reduce duplication and maintain consistency.	Often uses dimensional models, such as fact and dimension tables.
Priority	Fast, reliable transactions and concurrency.	Efficient analysis of large amounts of data.

OLAP is read-heavy, not necessarily read-only: analytical systems still load, transform, and update data. Relational databases can support both transactional and analytical workloads.

ACID transactions

Transactional databases commonly provide ACID guarantees:

• Atomicity: A transaction completes entirely or is rolled back.

• Consistency: A transaction preserves defined database rules.

• Isolation: Concurrent transactions interact according to the configured isolation level.

• Durability: Committed changes survive failures within the system’s durability guarantees.

For example, transferring money should debit one account and credit another as one transaction, rather than completing only half the operation.

How the data moves

A data pipeline extracts operational data, cleans or transforms it, and makes it available for analytics:

• ETL: Extract → Transform → Load.

• ELT: Extract → Load → Transform.

This can separate demanding analytical queries from the database serving live application transactions.

Who does what?

Role	Typical responsibilities
Database administrator (DBA)	Database deployment, permissions, performance tuning, backup, recovery, and availability.
Data engineer	Data ingestion, integration, transformation, pipeline reliability, and preparation of analytical datasets.
Data analyst	Explore data, build analytical models, identify trends, and communicate findings through reports and dashboards.

Responsibilities overlap; these roles are not restricted to the single stage shown in the diagram.

Microsoft technology examples

A solution might use Azure SQL Database for transactions, Azure Data Factory for pipelines, Azure Synapse Analytics for analytical processing, and Power BI for reporting. These illustrate possible roles in a solution, rather than a required product combination.

Review tip: OLTP answers “What transaction is happening?” OLAP answers “What patterns can we learn from the data?”

Data Pipeline Drill down - Data Arrival

Data arrival is the entry point to a data pipeline. Data can arrive in batches or as a continuing stream of events. Either approach can feed transformations, storage, analytics, or automated actions.

The diagram also shows actions, such as initiating an alert workflow. An action is an additional use of processed results, rather than another letter in ETL.

ETL: Extract, Transform, Load

Stage	What happens	Example
Extract / ingest	Collect data from its source.	Read sales records, receive device messages, or discover new files.
Transform	Clean, filter, enrich, or aggregate the data.	Standardize timestamps or calculate average temperature.
Load	Write the results to a destination.	Store processed data in a data lake or database.

Batch versus streaming arrival

Characteristic	Batch	Streaming
Arrival pattern	Data collected into groups or files.	Events arrive continuously or frequently.
Processing	Scheduled or triggered processing of a bounded dataset.	Ongoing processing as events become available.
Typical use	Nightly sales imports or periodic historical analysis.	Device monitoring, fraud detection, or operational telemetry.
Latency	Includes waiting for the next processing run.	Usually lower latency, subject to windows, buffering, and destination behavior.

A file in Blob Storage can support either pattern. Its storage location does not determine whether the pipeline is batch or streaming.

How Azure Stream Analytics processes arriving data

Azure Stream Analytics is a managed stream-processing service built around inputs, a query, and outputs. Supported stream inputs include Event Hubs, IoT Hub, Blob Storage, and Azure Data Lake Storage Gen2. IoT Hub receives device messages; Event Hubs ingests event streams; storage inputs let jobs process supported data arriving in files.

A Stream Analytics query can:

• Filter events, for example, retain only readings above a threshold.

• Transform fields or calculate new values.

• Aggregate over time windows, for example, calculate each device’s average temperature every minute.

• Enrich events by joining them with reference data, such as device locations or configured thresholds. Microsoft Learn

What can happen to the results?

Destination or action	Example
Data lake storage	Write processed results as files in ADLS Gen2 for later analysis.
Database	Store results in a supported database output for application queries.
Downstream processing	Send results to Event Hubs, Service Bus, or an Azure Function.
Alert workflow	A Function or downstream consumer sends a notification or starts a response.

Stream Analytics can produce records that represent alert conditions, but email, Teams notifications, and response actions require downstream logic. Multiple configured outputs let the same job support storage and operational actions.

Example: Sensors send temperature readings to IoT Hub. Stream Analytics calculates a one-minute average for each sensor, writes the averages to ADLS Gen2, and sends threshold breaches to Service Bus. A downstream application consumes those messages and notifies an operator.

Timing matters: An event’s creation time can differ from its arrival time. Configure event timestamps and late-arrival/out-of-order policies so delayed messages are handled appropriately. Window duration and these policies affect when results become available.

ETL Data Pipeline

In this ETL design, Azure Synapse Analytics is the final analytical warehouse destination. More specifically, prepared data is loaded into a Synapse dedicated SQL pool for SQL analysis and reporting.

Phase	Service	What happens
Extract / ingest	Azure Data Factory	Collects data from sources and copies it into a landing area.
Transform	Azure Databricks	Cleans, joins, standardizes, and aggregates the data.
Load	Synapse dedicated SQL pool	Stores prepared data in analytical warehouse tables.
Consume	Power BI or applications	Queries the warehouse to produce reports and insights.

1. Extract data from the sources

The reference shows Azure Data Explorer (ADX) holding logs or telemetry, alongside other data sources. Azure Data Factory can retrieve ADX data using a KQL query and copy the results to storage. ADX is a separate service from the retired Synapse Data Explorer preview feature.

2. Land and transform the data

Data Factory copies source data into Azure Data Lake Storage Gen2. Azure Databricks reads those raw files, performs transformations, and writes curated files back to the lake.

For example, it could remove duplicate events, standardize timestamps, join device records with location information, and calculate hourly totals. The raw and curated areas can be different folders or containers; they do not necessarily require separate storage accounts.

3. Load prepared data into Synapse

The curated files are then loaded into tables in the dedicated SQL pool. Loading options include the T-SQL COPY statement and PolyBase, including through appropriately configured Data Factory copy activities.

Direction matters: for this load operation, data moves from ADLS Gen2 into Synapse, rather than from Synapse into the lake. PolyBase is a data-access/loading capability, not a separate storage destination.

4. Query the warehouse

Power BI or another application queries the warehouse, for example, to compare event volumes by location or analyze historical trends. Data Factory can orchestrate the overall sequence, ensuring that transformation completes before the warehouse load begins.

The diagram’s dashed lines represent orchestration, while solid lines represent data movement. The warehouse-load step can be orchestrated by Data Factory even though the dashed line is visually routed through the transformation stage.

Key distinction: Synapse is shown in the Load phase for this particular architecture. The broader Synapse product also includes pipelines and Spark capabilities, so it can participate in ingestion and transformation in other designs.

Extract data from the sources

A data pipeline is iterative: loading data into storage is often the beginning of further processing. The same retained data can be transformed repeatedly as business rules, analytical questions, and applications change.

The diagram separates data movement—solid arrows—from orchestration, shown with dashed arrows.

1. Extract and land the data

Azure Data Factory can copy data from sources such as databases, files, and log stores into a raw landing area in Azure Data Lake Storage Gen2.

Retaining an appropriately protected source copy lets you reprocess historical data without repeatedly extracting it from the original system. Retention, access controls, and privacy requirements determine what you should preserve and for how long.

2. Transform and store usable datasets

A transformation activity reads the landed data, performs operations such as cleaning, joining, deduplication, or aggregation, and writes a prepared dataset.

Data Factory can orchestrate Databricks notebooks, Spark processing, SQL activities, or mapping data flows. Mapping data flows provide transformations executed on managed Spark compute.

3. Reuse and transform again

A curated dataset can become the source for another pipeline. Alternatively, a new pipeline can return to retained raw data when earlier transformations removed details needed for a new use case.

For example:

Iteration	Input	Result
Initial preparation	Raw sales transactions	Cleaned, standardized sales records.
Reporting	Cleaned sales records	Daily revenue totals by store.
Future analysis	Detailed sales records plus customer data	Customer purchasing trends.
Revised business rules	Retained raw transactions	Recalculated historical results.

The diagram’s curved reuse arrow represents further processing—not an automatic transformation performed by storage.

Azure Data Factory coordinates the process

A pipeline groups activities into an executable workflow. Data Factory can:

• Start runs on a schedule, in response to a supported event, or on demand.

• Define dependencies, for example, transform only after ingestion succeeds.

• Pass parameters such as dates, source names, or destination paths.

• Run branches, loops, and parallel activities.

• Apply configured retries and follow success or failure paths.

• Monitor activity status and pipeline runs.

Data Factory manages the workflow; connected storage retains the data, while the selected processing service executes the transformation.

ETL versus ELT: When raw data is loaded into the lake before transformation, that part follows Extract → Load → Transform (ELT). The prepared result might then be loaded into a Synapse warehouse. A larger solution can therefore combine ETL and ELT patterns.

Practical design point: Make reruns safe. Use techniques such as deduplication, partition replacement, or upserts so retrying a pipeline does not accidentally duplicate data.

Drill down into transforming data with trasnformations and azure databricks

Transformation turns collected data into usable datasets. Typical operations include removing duplicates, correcting formats, joining sources, calculating totals, and enriching records with business context.

The diagram shows alternative processing options, rather than services that must run in sequence. Each can read input data and produce prepared output; Azure Data Factory can coordinate the workflow.

Option	What it provides	When it fits
Azure Databricks	A managed data platform built around Apache Spark and additional SQL, pipeline, and analytics capabilities.	Complex transformations, large datasets, lakehouse processing, streaming, and machine-learning preparation.
Azure HDInsight	Managed clusters for supported open-source frameworks, including Spark, Hive, and Hadoop.	Workloads that depend on a particular Hadoop ecosystem or cluster configuration.
Synapse Spark or SQL	Spark notebooks and SQL processing within a Synapse workspace.	Transformations integrated with an existing Synapse analytics solution.
Mapping Data Flows	Visually designed transformations in Data Factory or Synapse pipelines, executed on managed Spark compute.	Teams that prefer a visual approach to joins, filters, aggregations, and other transformations.

Mapping Data Flows still use a processing engine; they do not simply execute within the pipeline’s orchestration layer.

Azure Databricks in more detail

Databricks lets teams implement transformations using tools such as SQL and Python/PySpark. Distributed processing divides work across compute resources, helping process datasets that would be impractical on one machine.

For example, a Databricks job could:

1. Read raw transaction files from ADLS Gen2.

2. Standardize dates and remove duplicate records.

3. Join transactions with customer information.

4. Write detailed cleaned records and daily summaries back to storage.

Databricks supports managed compute and autoscaling options, but scaling behavior depends on the selected compute and workload configuration. It also has its own pipeline and job-orchestration capabilities; Data Factory is an integration option, not a prerequisite.

Where Azure Data Factory fits

Data Factory can start the selected transformation activity, pass parameters, wait for completion, and continue to the next step or follow an error-handling path. The transformation runs on the relevant processing service.

For example: copy files → run a Databricks notebook → validate results → load prepared data into Synapse.

Clarifying the HDInsight reference

Spark/Hive/Hadoop support processing, Kafka supports event streaming, and HBase provides a distributed database. They are not interchangeable transformation engines. Current HDInsight documentation lists supported cluster types; the sketch’s older Storm reference should not be assumed to represent a currently available option.

The transformed data can remain in the lake for reuse, feed another transformation, or be loaded into an analytical warehouse for reporting.

Load prepared data - Azure Synapse

Azure Synapse Analytics brings data integration, transformation, and analytical querying into one workspace. It helps turn arriving data into prepared datasets, warehouse tables, and reports.

Where Synapse fits

Component	Role in the pipeline
Synapse Pipelines	Copy data, schedule processing, and orchestrate activities such as Spark notebooks and SQL operations.
Apache Spark pools	Clean, transform, and enrich large datasets; support data preparation and machine-learning processing.
Serverless SQL pool	Query supported files in the data lake using SQL without first loading them into dedicated warehouse tables.
Dedicated SQL pool	Provide provisioned, distributed SQL compute and warehouse tables for analytical workloads.
Synapse Studio	Provide a web interface for developing, managing, and monitoring these capabilities.

These components can be combined, but every solution does not need all of them.

How a typical pipeline works

1. Data arrives: Operational databases, uploaded files, or events previously landed by services such as Stream Analytics provide source data.

2. Ingest: Synapse Pipelines copies the data into Azure Data Lake Storage Gen2 (ADLS Gen2).

3. Transform: A pipeline starts Spark or SQL processing to clean records, standardize formats, and calculate useful values.

4. Analyze: Serverless SQL queries the lake files directly, or curated data is loaded into a dedicated SQL pool.

5. Consume: Power BI or another application queries the prepared data to produce reports and insights.

For example, daily sales files could land in ADLS Gen2, be cleaned by Spark, and then support SQL queries calculating revenue by store.

Synapse and the data lake serve different purposes

ADLS Gen2 stores files. Synapse provides processing and analytical capabilities that work with those files. Dedicated SQL pools also maintain their own warehouse table storage; their data is not simply the same thing as the workspace’s lake files.

Serverless SQL can query supported lake data without copying it into a dedicated pool. Dedicated SQL supports loading data into warehouse tables for repeated analytical queries. Both SQL options can serve reporting tools, the diagram’s reporting arrows should be read as representing those two alternatives.

Compute and storage are managed separately

• Dedicated SQL: Compute can be scaled, paused, and resumed. Pausing retains stored data and associated storage charges.

• Spark: Automatic pausing can release idle compute while files remain in storage.

• Serverless SQL: Uses on-demand query processing rather than a dedicated compute pool that you manually pause.

Pausing compute does not mean deleting data or eliminating every charge.

Update to the reference: Synapse Data Explorer pools (Preview) retired on October 7, 2025. They are therefore omitted from the new diagram. Microsoft’s migration guidance points to Eventhouse in Microsoft Fabric for that capability; this retirement does not mean that all of Synapse Analytics retired.

Working with Data - Power BI

Power BI is the analysis and presentation layer that helps people turn prepared data into decisions. In the illustrated pipeline, Data Factory ingests data, Databricks transforms it, and Synapse provides analytical data that Power BI presents through models, calculations, and interactive reports.

This is one possible architecture; Power BI can connect to many other sources.

The main analytical questions

Analysis type	Question	Example	How Power BI helps
Descriptive	What happened?	Revenue decreased by 8% last month.	Summarizes results using charts, measures, trends, and KPIs.
Diagnostic	Why did it happen?	Did one region or product account for the decline?	Supports filtering, drill-down, comparisons, and exploration of contributing factors.
Predictive	What might happen next?	What revenue should we expect next month?	Presents forecasts or prediction results from suitable analytical models.
Prescriptive	What should we do?	Which action best improves revenue within our budget?	Helps compare scenarios, recommendations, costs, and constraints.
Cognitive / AI-assisted	What patterns or meaning can we infer?	What themes appear in customer feedback?	Can present AI-derived classifications, summaries, and insights.

These are analytical approaches, not five mandatory processing stages. “Cognitive analytics” is a broader AI-related term, rather than simply another name for drawing conclusions.

How Power BI works with the data

A semantic model organizes tables, relationships, and business calculations, for example, revenue, profit margin, or year-over-year growth. Interactive reports then let users explore those measures through visualizations, filters, and drill-through pages.

For a supported Synapse SQL connection, Power BI can use:

• Import: Copy data into the model and refresh it to obtain updates.

• DirectQuery: Send queries to the source as users interact with reports. Performance and freshness depend on the source, connectivity, and report configuration. Microsoft Learn

From insight to a decision

An analyst might first identify declining sales, investigate where the decline occurred, review a forecast, and compare possible responses. Power BI makes that evidence easier to explore and communicate.

Advanced forecasting, optimization, and AI interpretation may require models or services outside Power BI. Diagnostic patterns also do not automatically prove causation. People should assess data quality, assumptions, uncertainty, and business context before acting.

The connecting arrows in the diagram show related analytical perspectives, not a required sequence from diagnostic analysis to every other type.

Azure SQL

Azure SQL offers three main deployment choices: SQL Server on Azure VMs, Azure SQL Managed Instance, and Azure SQL Database. The primary decision is how much infrastructure control you need versus how much management you want Azure to handle.

Deployment option	What you receive	Typical use case
SQL Server on Azure VMs	A SQL Server instance on a VM, with control over the guest OS and database engine.	Applications requiring OS access, specific SQL Server configurations, or additional installed software.
Azure SQL Managed Instance	A managed SQL instance supporting multiple databases and many SQL Server instance-level capabilities.	Migrating existing SQL Server applications while reducing infrastructure administration.
Azure SQL Database	Managed databases, deployed individually or in elastic pools.	Applications that need database-level services without managing an entire SQL Server instance.

With the two PaaS options, Azure manages the underlying OS, engine patching, and built-in platform availability. Customers still manage their data, permissions, application design, and recovery requirements. SQL Server on VMs provides greater control but leaves more operational responsibility with the customer.

Azure SQL Managed Instance

Managed Instance integrates with a VNet and provides high SQL Server compatibility, including support for many instance-level features. However, the reference’s “100% compatible” wording is too absolute: assess the application’s required features before migration. Its principal service tiers are General Purpose and Business Critical.

Azure SQL Database: keep the choices separate

The reference combines several different configuration decisions:

Decision	Options	Meaning
Deployment	Single database or elastic pool	Allocate resources to one database or share a resource budget across databases.
Purchasing model	vCore or DTU	Choose how resources and performance are packaged and priced.
vCore service tier	General Purpose, Business Critical, Hyperscale	Select the architecture and capabilities appropriate to the workload.
DTU service tier	Basic, Standard, Premium	Select a bundled compute, memory, and I/O performance level.
Compute model	Provisioned or serverless, where supported	Use allocated compute or automatic compute scaling within configured limits.

Standard is not another name for General Purpose, and Premium is not another name for Business Critical. They belong to different purchasing models. \\

An elastic pool is useful when multiple databases have varying demand: they share pool resources rather than each requiring capacity sized for its individual peak.

Serverless is a compute option, not a separate database product or a synonym for an elastic pool. It is supported in eligible General Purpose and Hyperscale configurations; automatic pause behavior depends on the configuration.

Hyperscale is a service tier within Azure SQL Database. Its architecture separates compute and storage to support large databases and flexible scaling. It is not a fourth top-level Azure SQL deployment option.

Availability and regional recovery

Built-in high availability protects against certain infrastructure failures. Cross-region disaster recovery requires additional configuration:

• SQL Database: Active geo-replication or failover groups, as appropriate.

• Managed Instance: Failover groups for instance-level regional recovery.

• SQL Server on VMs: Configure suitable SQL Server and Azure availability/recovery technologies.

  
  

Geo-replication is generally asynchronous, so a forced regional failover can lose changes that have not reached the secondary. Replication also does not replace backups.

Review tip: Need OS control → SQL Server on VMs. Need a managed SQL instance with broad compatibility → Managed Instance. Need managed individual databases or shared elastic pools → SQL Database.

Other types of Azure SQL databases

Azure supports open-source relational databases, including PostgreSQL and MySQL, alongside the Microsoft SQL Server–based Azure SQL services.

The diagram contrasts managed PaaS services with self-managed databases on Azure VMs. These are alternative deployment approaches, the downward arrows do not represent required migration or data flow.

Database engine	Managed Azure option	Main consideration
PostgreSQL	Azure Database for PostgreSQL Flexible Server	PostgreSQL applications, supported extensions, and configurable compute, storage, and availability.
MySQL	Azure Database for MySQL Flexible Server	MySQL applications with managed maintenance, backups, and configurable availability.
MariaDB	The former Azure Database for MariaDB service has retired.	Consider a self-managed deployment or assess a compatible migration target.
Any of these engines	Self-managed on Azure VMs	Greater OS and engine control, with greater operational responsibility.

PostgreSQL Flexible Server

Azure manages the underlying infrastructure while you configure database access, supported extensions, sizing, networking, and availability.

For workloads requiring horizontal distribution, elastic clusters bring Citus-based sharding to PostgreSQL Flexible Server. Sharding distributes data across nodes; it differs from increasing the size of one server.

The reference’s older “Hyperscale (Citus)” terminology should not be confused with Azure SQL Database Hyperscale. Its successor branding, Azure Cosmos DB for PostgreSQL, is now on a retirement path and is not recommended for new projects; Microsoft points to PostgreSQL elastic clusters for migration.

MySQL Flexible Server

MySQL Flexible Server offers compute tiers such as Burstable, General Purpose, and Memory-Optimized. Supported configurations provide stop/start controls and high availability with automatic failover.

These features have restrictions: for example, high availability is not supported in the Burstable tier, and stop/start is restricted with read-replica configurations. Stopping compute does not eliminate retained storage charges.

Updates to the older reference

• PostgreSQL Single Server has retired; use Flexible Server for current managed deployments.

• MySQL Single Server has retired; Flexible Server is the current managed offering.

• Azure Database for MariaDB has retired. MariaDB itself can still be deployed and managed on Azure infrastructure. Migration to MySQL requires compatibility testing rather than assuming the engines are interchangeable. Microsoft Learn

SQL command categories in the supporting images

These databases use SQL, but syntax and supported commands vary by engine.

Category	Purpose	Common examples
DDL — Data Definition Language	Define or modify database structures.	CREATE, ALTER, DROP
DML — Data Manipulation Language	Insert or change stored records.	INSERT, UPDATE, DELETE
DQL — Data Query Language	Retrieve records; often treated as part of DML.	SELECT
DCL — Data Control Language	Manage database privileges.	GRANT, REVOKE

Review tip: Choose Flexible Server when you want Azure to manage the database infrastructure. Choose Azure VMs when requirements demand OS access or engine customization unavailable in the managed service.

Non structured daat storage

Azure offers different services for unstructured files and queryable NoSQL data.

Data requirement	Azure option	Example
Store files and objects	Azure Blob Storage	Images, videos, PDFs, and raw log files.
Store files for large-scale analytics	Azure Data Lake Storage Gen2	Raw and curated datasets used by Spark or analytical pipelines.
Query flexible application records	Azure Cosmos DB	Product documents, key-value entities, or graph relationships.

Blob Storage is optimized for large amounts of unstructured data. ADLS Gen2 builds on Blob Storage with hierarchical namespace and data-lake capabilities; it can hold structured, semi-structured, and unstructured files.

Cosmos DB data models and APIs

The APIs in the reference provide different interfaces and data models:

Cosmos DB API	Data model	Typical example
API for NoSQL — formerly SQL API	JSON documents	Products with different attributes.
API for MongoDB, RU-based	Documents accessed through MongoDB-compatible interfaces	An application using MongoDB drivers.
API for Apache Cassandra	Wide-column records	Partitioned event or time-series records.
API for Table	Key-value entities with properties	Entity lookup using partition and row keys.
API for Apache Gremlin	Graph vertices and edges	Relationships between people, accounts, or devices.

These are API choices, not a promise that one database can be queried interchangeably through every API. Also, the API for NoSQL’s SQL-like query syntax does not make it a relational SQL Server database.

Request units and capacity

A request unit (RU) represents the normalized resources consumed by a database operation. Reading, writing, and querying have different RU costs, influenced by factors such as item size, indexing, and query complexity.

Capacity option	How it works
Manual provisioned throughput	You configure an RU-per-second allocation.
Autoscale provisioned throughput	Cosmos DB adjusts RU/s within the configured scaling range as demand changes.
Serverless	You do not reserve RU/s; charges include the RUs consumed and stored data, subject to service limits.

Autoscale is a form of provisioned throughput. Serverless is a separate consumption-based capacity model. A good partition key and efficient queries remain important with either approach.

Example: An application could store product photographs in Blob Storage and product metadata in Cosmos DB. Each product document contains fields such as price, category, and the photograph’s location. This avoids treating the database as a general-purpose file repository.

Naming update: The diagram’s MongoDB entry refers to the RU-based Cosmos DB offering. The former Azure Cosmos DB for MongoDB vCore offering is now named Azure DocumentDB and uses a different capacity model.

Azure tools to work with data

Choose the tool according to the task: administering a database, writing queries, managing files, transforming data, or presenting insights.

The diagram updates the original references and adds tools for working with Blob Storage and data lakes.

Tool	Primary purpose	Typical tasks
SQL Server Management Studio (SSMS)	Detailed administration and development for Microsoft SQL database engines.	Run T-SQL, manage database objects and permissions, inspect execution plans, and investigate performance.
VS Code with the MSSQL extension	SQL development in a cross-platform editor.	Connect to supported SQL endpoints, edit and execute T-SQL, and work with database projects.
Azure Storage Explorer	Graphical access to Azure Storage data.	Browse containers and directories, upload/download files, and manage supported storage permissions.
AzCopy	Command-line data transfer.	Copy or synchronize supported storage data in repeatable scripts.
Synapse Studio	Develop and operate Synapse analytics workloads.	Run SQL scripts and Spark notebooks, build pipelines, and monitor execution.
Databricks notebooks and SQL tools	Explore and transform data using distributed compute.	Clean and join lake data, run SQL, and develop analytical processing.
Power BI	Business analysis and visualization.	Build semantic models, measures, interactive reports, and dashboards.
Azure portal, CLI, and PowerShell	Configure and automate Azure resources.	Create services, configure networking, scale resources, and assign Azure roles.

Database administration: SSMS

SSMS is a Windows application that connects to SQL Server, Azure SQL Database, Managed Instance, and supported Synapse SQL endpoints. Available management features depend on the connected service.

Two features in the reference need distinction:

• Query Store is a database-engine feature that records query performance history; SSMS provides interfaces for investigating it where supported.

• SQL Server Agent schedules jobs in SQL Server and Azure SQL Managed Instance. It is not available in Azure SQL Database; use alternatives such as Elastic Jobs or pipeline orchestration there. Microsoft Learn

Update: Azure Data Studio has retired

Azure Data Studio retired on February 28, 2026, and no longer receives updates or security fixes. Microsoft directs SQL development users toward VS Code with the MSSQL extension, while SSMS remains available for administration. The MSSQL extension targets Microsoft SQL engines; other database engines require appropriate clients or extensions.

Working with data lakes: files versus queries

Storage Explorer manages files and directories in ADLS Gen2; it is not a distributed SQL or Spark query engine. To analyze file contents at scale, use a processing service, for example, Synapse serverless SQL or Spark through Synapse or Databricks.

For example, you could upload Parquet files with Storage Explorer, query supported files through a Synapse SQL endpoint, and present the resulting analysis in Power BI.

Permissions still apply

A tool does not grant access by itself. Users need the relevant database permissions or storage data permissions, plus a working network path. Permission to manage an Azure resource does not automatically grant permission to read all its data.

Reference tip: SSMS → administer databases. VS Code → develop queries. Storage Explorer/AzCopy → manage files. Synapse/Databricks → process data. Power BI → communicate insights.

Azure functions

Azure Functions runs code in response to an event, such as an HTTP request, a scheduled time, a message, or a newly created blob. It is useful for focused processing and automation without managing the underlying servers.

How Azure Functions works

Component	Purpose
Function App	Hosts one or more functions with shared application configuration and a chosen language runtime.
Trigger	Starts an execution. Each function has one trigger.
Function code	Performs the work: validate data, calculate values, call services, or respond to a request.
Input/output bindings	Provide declarative integration with supported services. They are optional; code can also use SDKs or APIs.
Hosting plan	Determines scaling behavior, resource allocation, pricing, and execution limits.
Application Insights	Collects configured execution logs, failures, and performance telemetry.

The trigger types in the diagram are alternatives, not multiple triggers attached to one function. If different event types must start similar work, separate functions can call shared application logic.

Functions can use serverless hosting such as Flex Consumption, or run on Premium, a Dedicated App Service plan, or supported Container Apps hosting. Scaling behavior depends on the hosting option; “serverless” does not mean that every plan scales or bills identically.

Event Grid routes notifications; Functions executes code. For example, Event Grid can deliver a storage event to a function. Other functions can instead be invoked directly by HTTP, timer, queue, or other supported triggers.

The supplied language-version table is historical. Choose a currently supported language/runtime combination when creating the Function App rather than reusing those older versions.

Azure Functions in ETL

The second diagram shows a file-driven pipeline:

1. A raw file arrives in Blob Storage or ADLS Gen2.

2. Event Grid delivers a BlobCreated notification to an Event Grid-triggered function.

3. The function reads the file using a binding or SDK, with appropriate permissions.

4. Code validates and transforms the contents—for example, standardizing timestamps, filtering records, or adding calculated fields.

5. Valid results are loaded into curated storage or a database.

6. Invalid records follow a configured error path, such as quarantine storage and a notification workflow.

7. Prepared data becomes input to Synapse, reporting, or another pipeline.

The event normally identifies the affected object; it does not contain the entire file. Receiving the notification also does not automatically grant permission to read that file.

Functions and Data Factory can work together

A Data Factory or Synapse pipeline can invoke an HTTP-triggered function through an Azure Function activity. This is useful for custom processing that does not fit a standard pipeline activity.

For long-running work, avoid holding an HTTP request open. Use an asynchronous pattern, such as queued work or Durable Functions, with a way to check completion. The documented HTTP response limit is approximately 230 seconds, independently of a longer function execution-time setting.

Reliability and workload fit

• Handle duplicates safely: Retries or repeated event delivery must not produce duplicate business records. Use event identifiers, deterministic output names, or upserts as appropriate.

• Monitor failures: Configure Application Insights and the retry/dead-letter behavior applicable to the trigger.

• Match the processing engine to the job: Functions suits focused transformations and custom integration. Large distributed joins or extensive historical processing generally fit Spark, Databricks, or another analytical engine better. Microsoft Learn

Review tip: A trigger starts the function; bindings connect it to supported data sources and destinations; code performs the transformation.

Azure Functions and Static Azure web apps

Azure Static Web Apps hosts the frontend; Azure Functions provides server-side processing through an API. Together, they support an interactive application without requiring you to manage a traditional web server.

The diagram’s return arrow is simplified: the API response passes back through the Static Web Apps routing layer.

How a user request flows

1. Load the frontend. The browser downloads HTML, CSS, JavaScript, and images from Static Web Apps. Frameworks such as React or Angular can produce these frontend assets.

2. Request dynamic data. JavaScript calls an endpoint such as /api/products.

3. Execute server-side code. Static Web Apps routes the request to an HTTP-triggered Azure Function.

4. Access services. The function validates the request, applies business logic, and reads or writes authorized database or storage data.

5. Update the page. The response returns through Static Web Apps to the browser, typically as JSON.

Why “static” can still mean interactive

“Static” describes how the frontend files are hosted, not whether the application can change what users see. Browser-side JavaScript can call Functions to retrieve current information, submit forms, or perform calculations.

Responsibility	Service
Deliver frontend assets	Azure Static Web Apps
Run backend code and business logic	Azure Functions
Retain application records or files	Database or storage service
Build and publish code changes	GitHub Actions or Azure DevOps pipeline

Static Web Apps supports geographically distributed static content and repository-based build and deployment workflows. This does not imply that every backend API is automatically replicated globally.

Managed Functions versus a linked Function App

• Managed Functions: Deploy the frontend and API together. Static Web Apps manages the integrated Functions backend, subject to its supported features and restrictions.

• Bring your own Functions: Link a separately managed Function App when you need capabilities beyond the managed integration. Availability depends on the Static Web Apps plan.

Routing and security

The integrated /api path presents the frontend and backend under the same site origin, avoiding custom CORS configuration for those calls. Direct calls to a different domain may still require CORS configuration.

Static Web Apps also supports authentication integrations and role-based route restrictions. Backend code must still validate inputs, enforce application-specific authorization, and protect access to data. Keep database credentials out of frontend JavaScript, which users can inspect.

Example: A product catalog loads its interface from Static Web Apps. The browser calls /api/products; a Function reads product records and returns JSON. The browser displays the results without connecting directly to the database.

Execution limit: Static Web Apps API requests have a documented maximum duration of 45 seconds. For longer processing, return promptly and use an asynchronous job pattern rather than keeping the API request open.

Azure Data Products and Responsabilities

The deployment model changes who maintains the platform. The control/data-plane distinction changes which operations and permissions are involved. These are separate concepts: both IaaS and PaaS have management operations and data-access operations.

IaaS versus PaaS responsibilities

Area	Database on an Azure VM — IaaS	Managed database/storage — PaaS
Physical datacenter, hosts, and virtualization	Azure manages.	Azure manages.
Guest OS and database-engine maintenance	Customer manages, with optional automation.	Azure manages the underlying service platform.
Application code, data classification, and permissions	Customer responsibility.	Customer responsibility.
Networking and exposure	Customer configures VM and application network controls.	Customer configures supported firewall, private-access, and service settings.
Backup and recovery	Customer implements and operates a suitable strategy.	Azure provides service-specific mechanisms; customer selects available settings and validates recovery.
Availability and disaster recovery	Customer designs the VM/database architecture.	Customer selects service tiers, redundancy, and regional recovery options appropriate to requirements.

PaaS reduces infrastructure work, but it does not remove responsibility for protecting data, assigning permissions, or designing application recovery. Exact responsibilities depend on the individual service

For example, Azure SQL Database provides automated backups and built-in availability. You still need to choose appropriate retention, backup redundancy, regional recovery, and application retry behavior, and test restoration. Blob Storage has different protection mechanisms; do not assume every PaaS service includes the same backup features.

Control plane versus data plane

	Control plane	Data plane
Purpose	Manage the Azure resource.	Access or change its contents.
Examples	Create a database server, change a storage firewall, scale a service, delete a resource.	Query SQL rows, download a blob, write a file, retrieve a secret.
Authorization	Primarily Azure RBAC management permissions through Azure Resource Manager.	Service-specific authorization: data RBAC, database permissions, ACLs, or supported credentials.
Monitoring	Azure Activity Log for management operations.	Service-specific auditing and diagnostic logs, configured as required.

The operation, not the tool, determines the plane. Azure portal, CLI, and PowerShell can perform both management and data operations.

In the diagram, “service endpoint” means the service’s data-access address, not specifically the Azure VNet service endpoint feature.

Apply the appropriate security controls

• Management: Use least-privilege Azure RBAC, protected administrator identities, and PIM where appropriate. Azure Policy can enforce configuration requirements; it does not grant access.

• Data: Grant only the required SQL permissions, storage data roles, or ADLS Gen2 ACLs. Prefer identity-based access where supported.

• Network: Restrict connectivity through supported firewalls, private endpoints, and network rules. Network access alone does not authorize data access.

• Monitoring: Collect relevant management and data-access logs. Activity Log alone does not record every SQL query or blob read.

Management permissions can create an indirect path to data

A management role does not necessarily grant direct data access, but some powerful permissions can expose credentials or change access configuration.

For example, permission to list storage account keys can allow Shared Key data access when that authorization method is enabled. Permission to assign roles can also create a path to additional data permissions. Protect these capabilities accordingly. Microsoft Learn

Review distinction: IaaS/PaaS determines the maintenance boundary. Control plane/data plane distinguishes managing the resource from using its data.

Azure data and Purview

Microsoft Purview helps answer: What data do we have, where is it, what does it contain, and how does it move? It provides discovery, classification, cataloging, and lineage across supported data sources.

How discovery works

1. Register a source, such as Azure SQL Database or ADLS Gen2.

2. Configure authorized access and the required network connectivity.

3. Run a scan, selecting its scope, rules, and schedule.

4. Populate the Data Map with discovered metadata, such as tables, columns, files, schemas, and classifications.

5. Explore the catalog to find assets, understand business context, and investigate supported lineage.

Purview catalogs information about the data rather than relocating the source datasets into a central warehouse. Scanning can inspect source content where supported to identify classifications.

Coverage across the data stores

Data category	Examples	What to check
Relational databases	Azure SQL, SQL Server, PostgreSQL, MySQL	Connector support, scan permissions, and classification capabilities.
Files and data lakes	Blob Storage, ADLS Gen2	Supported file formats, folder scope, and content-inspection limits.
NoSQL	Supported Cosmos DB sources	Support for the specific API; do not assume every Cosmos DB API has identical coverage.
Analytics platforms	Synapse, Databricks, Power BI	Metadata, classification, and lineage support differ by connector and asset type.

Discovery does not imply identical classification support. A connector might collect an asset’s metadata without inspecting all its contents or capturing complete lineage.

Classification versus sensitivity labels

Concept	Meaning	Example
Classification	Describes the kind of data detected.	A column appears to contain email addresses.
Sensitivity label	Expresses an organizational sensitivity category.	Confidential or Highly Confidential.
Access control	Determines who can use the source data.	SQL permissions, storage data RBAC, or ADLS ACLs.

Data Map supports built-in classifications and custom classification rules, including pattern-based matching for supported sources and file types.

Correction to the reference: Trainable classifiers exist within Microsoft Purview Information Protection, but they are not supported by Purview Data Map. Classification also does not automatically grant access or encrypt the source data.

Following data through a pipeline

Connecting supported Data Factory or Synapse pipelines can add runtime lineage—for example:

Source table → Copy activity → Lake dataset → Supported transformation → Output dataset

This helps users investigate where a report’s data originated and which downstream assets might be affected by a change. Automatic lineage is limited to supported activities and connections; custom processing may require additional integration or documentation.

For example, Purview could identify customer-related columns in a SQL source, catalog the corresponding lake dataset, and show supported pipeline relationships. A data steward can then add an owner, description, and business terms so analysts can find and interpret the dataset.

Review tip: Data Map discovers and records metadata; the catalog makes it usable; source permissions still control access to the actual data.