Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 57 ยท Databases and Data

Data and Analytics Services

A map of Azure data and analytics: Data Lake Storage, Data Factory pipelines, Synapse, Fabric, Databricks, and batch versus streaming, with ETL examples.

Databases such as Azure SQL Database and Cosmos DB serve applications: many small reads and writes, answered in milliseconds. Analytics asks different questions: how did revenue change by region last quarter, which customers stopped ordering, what does the trend of ten million events look like? Those queries scan large amounts of data, so they run best on storage and engines designed for scanning, kept apart from the database that runs your shop.

Azure has many services in this area, and their names and packaging change often. This lesson is a map, not a manual: what each service is for, how they fit together in a typical pipeline, and the difference between batch and streaming. None of the services can run on this site, so commands and configuration are read-only. The runnable parts are Python stand-ins (in your browser) that perform the same steps a pipeline performs: extract, clean, transform, load and aggregate.

The typical shape: land, refine, serve

Most analytics platforms follow one pattern:

  1. Ingest: copy data from sources (databases, files, APIs, event streams) into a central store.
  2. Store: keep raw data cheaply in a data lake, unchanged, so you can reprocess it later.
  3. Transform: clean, join and aggregate it into tables people can query.
  4. Serve: expose the results to analysts, dashboards and machine-learning jobs.
flowchart LR
    S1["Sources<br/>databases, files, APIs"] --> I["Ingest<br/>Data Factory"]
    S2["Event streams"] --> EH["Event Hubs"]
    I --> L["Data lake<br/>raw, cleaned, curated"]
    EH --> L
    L --> T["Transform<br/>Spark, SQL"]
    T --> V["Serve<br/>warehouse, semantic model"]
    V --> R["Reports and dashboards"]

Every analytics service on Azure plays one of these roles. The names change; the roles do not.

Azure Data Lake Storage

Azure Data Lake Storage Gen2 is not a separate product: it is a storage account with the hierarchical namespace option turned on. That adds real directories and fast rename and delete of a whole folder, plus POSIX-style permissions, on top of Blob Storage's low cost and huge scale. Analytics engines read it through the abfss:// protocol, and it is the common landing zone for the other services below.

A popular way to organize the lake is the medallion layout, three zones of increasing quality:

Zone Contents
Bronze (raw) Data exactly as it arrived: CSV, JSON, logs. Never modified
Silver (cleaned) Validated, deduplicated, typed data, often in a columnar format such as Parquet
Gold (curated) Aggregates and models built for specific questions, such as daily sales by region

Keeping bronze untouched means a bug in your transformation is fixable: fix the code and rebuild silver and gold.

Azure Data Factory

Azure Data Factory (ADF) is the managed service for moving and orchestrating data. Its concepts:

  • Linked service: a connection to a data store or compute service (like a connection string, ideally using a managed identity).
  • Dataset: a named reference to data in a linked service (a folder, a table).
  • Activity: one step, such as Copy data, running a notebook, calling a stored procedure or running a Data Flow.
  • Pipeline: a group of activities with dependencies and conditions.
  • Trigger: what starts a pipeline: a schedule, a tumbling window (fixed, non-overlapping time slices that can be rerun), or an event such as a new blob arriving.
  • Integration runtime: the compute that executes copies, in Azure or on a self-hosted machine that can reach on-premises data.

A pipeline is stored as JSON. A trimmed, read-only illustration of a nightly copy (field details vary and are simplified here):

JSON
{
  "name": "NightlyCopySales",
  "properties": {
    "activities": [
      {
        "name": "CopySalesToLake",
        "type": "Copy",
        "inputs":  [{ "referenceName": "SalesSqlTable", "type": "DatasetReference" }],
        "outputs": [{ "referenceName": "LakeBronzeSales", "type": "DatasetReference" }]
      },
      {
        "name": "BuildDailyTotals",
        "type": "DatabricksNotebook",
        "dependsOn": [{ "activity": "CopySalesToLake", "dependencyConditions": ["Succeeded"] }]
      }
    ]
  }
}

You rarely write that JSON by hand; the studio designs it visually. Because pipelines are code-like artifacts, keep them in source control and deploy them with Bicep or a pipeline.

Synapse Analytics and Microsoft Fabric

Azure Synapse Analytics bundles several engines under one workspace: a dedicated SQL pool (a massively parallel data warehouse with provisioned compute), a serverless SQL pool (query files in the lake with T-SQL, paying per data scanned), Spark pools for notebooks and large transformations, and pipelines that are Data Factory under the hood.

Microsoft Fabric is Microsoft's newer, software-as-a-service analytics platform. It brings data engineering, warehousing, real-time analytics, data science and Power BI into one product, with a shared storage layer called OneLake and capacity-based billing. Microsoft's direction is clearly toward Fabric for new analytics projects, while Synapse remains in use for existing workloads. Because the packaging evolves, check the current guidance before choosing for a new project.

Azure Databricks

Azure Databricks is a managed Apache Spark platform (built in partnership with Databricks) with notebooks, job scheduling, a lakehouse table format (Delta Lake) and strong machine-learning tooling. Choose it when your team already uses Spark or Databricks, needs advanced data science, or wants a vendor-neutral lakehouse. Clusters are created per workload and shut down after use; leaving one running is a classic surprise bill (see Pricing, Cost Management and Budgets).

A quick selection guide:

You need Consider
Cheap, scalable file storage for everything Data Lake Storage Gen2
Scheduled copy and orchestration Data Factory (or the pipelines inside Fabric/Synapse)
A T-SQL warehouse Synapse dedicated SQL pool or a Fabric warehouse
One integrated SaaS analytics product Microsoft Fabric
Spark-first engineering and data science Azure Databricks
Ad hoc SQL over lake files Synapse serverless SQL or a Fabric lakehouse

An ETL pipeline you can run: a stand-in

The stand-in below does what a small pipeline does, with Python's standard library. It extracts CSV text (here an inline string; in Azure it would be a blob in the bronze zone), transforms it by rejecting bad rows, removing duplicates and converting types (silver), and loads a summary (gold). The data is invented. It is a model of the logic, not of ADF or Spark.

Python Bronze to silver to gold

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two habits from this tiny script scale to real pipelines: reject loudly (log why each row was rejected instead of silently dropping it) and keep the raw data (bronze never changes, so you can rerun everything). A good pipeline is also idempotent: running it twice on the same input gives the same result and no duplicates.

Batch versus streaming

Batch processing collects data over a period and processes it in one go, for example every night. It is simple, cheap and good enough for most reporting. Streaming processes events as they arrive, within seconds, for fraud alerts, live dashboards and IoT telemetry. On Azure, events typically arrive through Event Hubs (see Event Grid and Event Hubs) and are processed by Stream Analytics, Spark Structured Streaming or Fabric real-time tools.

Streaming engines usually group events into windows. A tumbling window is a fixed slice of time (every minute), with no overlap. The second stand-in counts invented events in tumbling windows of 10 seconds, including one event that arrives late.

Python Tumbling windows over events

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The late event (time 8, arriving after time 12) still lands in the correct window because grouping uses event time, not arrival time. Real engines use a watermark, a rule such as "wait up to two minutes for stragglers", to decide when a window is final.

Moving existing systems: Azure Migrate and database migration

Everything above assumes data starts in Azure or flows into it from files and databases. A very common first project is different: move what already exists. Azure has a service family for it.

Azure Migrate is the hub, in three phases:

  1. Discover. A lightweight appliance (a VM you deploy on your network) inventories servers, SQL Server instances and web apps, and watches CPU, memory and disk use over time. No agents are needed on each machine in the common case.
  2. Assess. Azure Migrate compares what each machine actually uses with the Azure catalog and recommends a VM size, disk type and monthly cost, and flags blockers such as an unsupported operating system. Sizing from measured usage, not from the old hardware, is what stops you from paying for idle cores (see Cost Management).
  3. Migrate. Replicate the servers to Azure and cut over, or move web apps and databases to a managed service.

Migration strategies, from least to most work: rehost (lift and shift a VM unchanged), replatform (move to a managed service such as Azure SQL or App Service), refactor (redesign for cloud services), and retire or retain (switch it off, or leave it and manage it with Arc, see Azure Arc, Lighthouse and Hybrid Management).

For databases, the Azure Database Migration Service moves SQL Server, MySQL and PostgreSQL databases to Azure targets. Two modes matter: offline migration stops the application during the copy (simple, needs a maintenance window) and online migration copies the bulk of the data and then keeps replicating changes until you cut over, so downtime is only minutes. A MySQL migration, for example, checks compatibility and the schema first, copies the data to Azure Database for MySQL (see Managed Open-Source Databases), then you repoint the application's connection string and verify row counts before decommissioning the source.

Assessing a server: a stand-in

The stand-in sizes three invented servers from their measured peak usage with a 20% safety margin, compared with the hardware they run on today.

JS Right-size servers from measured usage

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The intranet web server is a typical result: it ran on 16 cores but peaked at 8% CPU, so a 4 vCPU size is enough and costs a quarter of a 16 vCPU size. The ERP database shows the opposite case: its measured memory need is larger than anything in this small catalog, which is a prompt to look at memory-optimized series (see Virtual Machines) instead of forcing it onto a general size. The prices are invented.

Common mistakes

  • Querying the production database for analytics. Heavy scans slow your application. Copy data to the lake or a warehouse.
  • Overwriting raw data. Keep bronze immutable.
  • Leaving Spark or SQL pool compute running between jobs. Pause, auto-terminate or use serverless options.
  • Building streaming when nightly batch would do. Streaming costs more and is harder to operate.
  • Skipping data quality checks. A wrong number in a dashboard does more damage than a late one.

Planning a migration: goals, discovery and dependencies

Tools alone do not make a migration succeed; a plan does. Start with goals that are SMART: specific, measurable, achievable, relevant and time-bound. "Move to the cloud" is a wish; "move the 12 intranet servers to Azure by 30 September and cut hosting cost by 20%" is a goal you can check.

The planning loop with Azure Migrate has four verbs:

  • Discovery. Inventory every server, its operating system, installed software and what it talks to. Machines nobody remembers are the usual surprise.
  • Tracking. Collect performance data over a representative period (a month if you have month-end peaks) so sizing uses real peaks, not guesses.
  • Analysis. Assess readiness, right-size, and estimate cost for each candidate target.
  • Application dependency mapping. Record which servers call which. Moving a web server to Azure while its database stays on-premises sends every query across a WAN link, and a missed dependency is the classic cause of an outage after cut-over. Dependency analysis shows the connections (source, destination, port, process) so related servers move together in one move group, in the right order.

The lift and shift (rehost) approach moves each server unchanged to an Azure VM. It is the fastest way out of a datacenter whose lease is ending, and you can modernize afterwards. Its weakness is that you carry old problems along, so plan to right-size and later replatform.

JS Build move groups from dependencies

Press Run (or Ctrl + Enter) to see the console output here.

No output.

There are three groups. The web, app and database tiers must move together, the HR pair is a second group, and the printer server has no dependencies at all, which makes it a good first pilot. Groups can move on different weekends.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What turns a storage account into Azure Data Lake Storage Gen2?

    The hierarchical namespace adds real directories and POSIX-style permissions on top of Blob Storage.
  2. 2. In the medallion layout, what belongs in the bronze zone?

    Bronze is raw and immutable, which lets you rebuild silver and gold after fixing a bug.
  3. 3. Which Data Factory object starts a pipeline on a schedule or when a file arrives?

    Triggers start pipelines. Linked services connect to stores, datasets reference data, and integration runtimes provide compute.
  4. 4. Why does a streaming engine group by event time rather than arrival time?

    Network delays reorder events. Event time keeps results correct, and watermarks decide when to close a window.

Summary

  • Analytics workloads scan lots of data, so keep them away from the application database.
  • A typical platform ingests data, lands it in a data lake (bronze, silver, gold), transforms it and serves it.
  • Data Lake Storage Gen2 is a storage account with a hierarchical namespace; Data Factory orchestrates copies and pipelines through linked services, datasets, activities and triggers.
  • Synapse bundles SQL and Spark engines, Fabric is the newer all-in-one SaaS platform, and Databricks is a managed Spark and lakehouse platform.
  • Prefer batch unless you truly need results within seconds; streaming groups events into windows by event time.
  • Good pipelines reject bad data loudly, keep raw data and are idempotent.

Next: Service Bus Queues and Topics.