Azure Data Engineering Services

Data Engineering & Modern Data Platforms · Lesson 55 · Modern Data Platforms and Cloud

Lesson Overview

Identity for TechLearningPro already lives in Microsoft Entra ID. That fact makes Azure a plausible account boundary. It does not make a Synapse workspace, a Fabric capacity, Data Factory, a storage account, and Event Hubs into one platform. A proposal that turns all of them on in the same quarter will publish two completion marts and a second log beside the Kafka topics from Module 4.

AWS Data Engineering Services fixed four workloads and refused a duplicate log. This lesson asks the same questions of Azure Data Lake Storage Gen2, Azure Data Factory, Azure Synapse Analytics, Azure Event Hubs, and Microsoft Fabric. The workloads do not change.

1. The completion mart. Grain (learner_id, lesson_id, attempt_number), definition completion-v4, on the board by 06:00. About forty Monday readers, about five on Wednesday.
2. Raw exploration of one day of progress, slower, never the official number.
3. The provisional activity tile, five-minute freshness, labeled provisional.
4. Enrollment change capture, one replication slot on PostgreSQL, the slot from CDC with Kafka and Debezium.

The volumes stay the teaching platform's volumes: about 80 million progress events and about 40 GB of compacted Parquet a day, about 8 million enrollment rows with about 200,000 changes a day, about 120,000 purchase lines a day. Payment instruments still never land.

Azure adds a choice the AWS lesson did not have to make inside one vendor: dedicated SQL pool, serverless SQL, and a Fabric warehouse can all answer the Monday question. Running more than one of them as the serving copy is the drift this lesson is written to stop. Databricks on Azure remains the engine from the Databricks lesson. This lesson is the first-party map, and it says when that engine is the thing you already chose so Synapse does not become a second copy.

Learning Objectives

By the end of this lesson you will be able to:

1. Explain what the hierarchical namespace on ADLS Gen2 changes for directory operations, ACLs, and a Spark commit, compared with a flat object prefix.
2. Separate Azure RBAC data-plane roles from POSIX-style ACLs, and predict which one wins when both are present.
3. Choose Data Factory Copy, a mapping data flow, Synapse Spark, or T-SQL for a given transform, including the spin-up cost of a data flow.
4. Assign the Monday mart to a dedicated SQL pool or a Fabric warehouse, and assign one-day exploration to serverless SQL or a lake query, with one owner.
5. Pick HASH, ROUND_ROBIN, or REPLICATE for a table and tie the choice to a join you can name.
6. Use a day-scoped delete-and-insert or a partition switch as an idempotent publish, and say when switch is worth the alignment rules.
7. Decide whether Event Hubs, including its Kafka endpoint and Capture, may replace the running Kafka cluster, and which Module 4 features the pilot must prove.
8. Place Fabric capacity, OneLake, and shortcuts beside Synapse and Data Factory without paying for two serving marts.
9. Keep enrollment on one change feed, and describe a watermark copy as a different contract from a replication slot.
10. Write the Azure decision record in the same shape as the AWS one, ready for the cost lesson.

Prerequisites

• Lesson 54's four workloads and the rule of one owner per workload. This lesson changes the services, not the questions.
• Data Lake Architecture for zones and file size. ADLS is a lake substrate with a directory tree, not a new zone model.
• ETL and ELT Architecture. Copy into the lake and transform with SQL is still ELT. A mapping data flow that hides Spark is still a cluster.
• Apache Airflow Architecture and DAGs if the morning graph already exists. Data Factory does not have to replace it on day one.
• Lesson 46 for the slot and WAL. Lesson 47 for which signal may page.
• The Databricks lesson, if Azure Databricks is already the Spark and table standard. First-party services then have to justify themselves as the serving path or the orchestrator, not as a second lake.
• /learn/postgresql for the operational enrollment database. /learn/kafka for the log you already run.

Detailed Core Concepts

ADLS Gen2 is Blob storage with a directory tree

A storage account with the hierarchical namespace enabled is ADLS Gen2. The same account still has a Blob endpoint and a DFS endpoint. Analytics engines should use the DFS endpoint:

abfss://progress@tlpdata.dfs.core.windows.net/raw/progress/event_date=2026-11-10/part-00017.snappy.parquet

The hierarchical namespace is the feature that makes directory rename a single metadata operation. Spark and lakehouse commit protocols use rename to publish a set of files. On a flat object store, rename is copy-plus-delete, and a failure in the middle is a prefix you must reconcile. On ADLS Gen2, a directory rename is the primitive those commits expect. Turning the namespace on later, on an account full of data, is a migration. Create the analytics account with the namespace enabled. A second account without it, "because an older SDK complained," becomes the bucket where commits go to degrade.

Access tiers are Hot, Cool, Cold, and Archive, set per blob, with minimum durations and retrieval behavior that lesson 58 will price. Hot for the incident window. Cool or Cold for days you still read occasionally. Archive for bytes you can wait to rehydrate. A lifecycle policy implements that. It is not a backup. Soft delete and versioning are what a bad overwrite needs. Point-in-time restore, where you enable it, has a retention you must have tested. An unread replica in another region is a cost line until someone restores it once.

Shared keys on the account are a password that bypasses Entra ID. For this platform, shared key access should be disabled and callers should use Entra ID: a managed identity for Data Factory and Synapse, a user identity for analysts. A connection string in a pipeline parameter is the incident.

Private endpoints for the DFS and Blob endpoints keep the traffic on the private network. A storage firewall that allows only those paths, plus the integration runtime you actually use, is the network half of lesson 57. This lesson's requirement is narrower: the raw container is not reachable from the public internet, and the pipeline identity is the identity in the design, not a developer's laptop key that was convenient during the pilot.

RBAC and ACLs are both real, and RBAC is coarse on purpose

ADLS Gen2 evaluates Azure RBAC and POSIX-style ACLs. A data-plane role such as Storage Blob Data Reader or Storage Blob Data Contributor, assigned on the account or the container, grants that data action. ACLs do not then subtract it. The principal can read or write what the role allows, across the trees the scope covers. ACLs matter for identities that do not already have a data-plane role covering the operation. They are how you grant a single directory to a single job without handing it the container.

The practical design is: no broad data-plane role for humans on the raw account; container-level RBAC only for the platform identities that must administer; ACL execute on parent directories and read or write on the specific prefix for everyone else. A project that "fixes" a 403 by assigning Storage Blob Data Owner at the account has turned off the ACL model and called it progress. Lesson 57 tests this with two identities. This lesson forbids the account-wide data owner role for the daily job.

Data Factory is control flow, and a data flow is a Spark cluster with a canvas

A Data Factory pipeline is activities, linked services, datasets, and triggers. The integration runtime is where the activity runs.

• The Azure integration runtime runs in Azure and is the default for cloud sources the factory can reach.
• The self-hosted integration runtime runs on machines you operate, inside a network that can see a closed PostgreSQL endpoint. It is a patching and availability responsibility. Two nodes is the minimum if the morning extract depends on it. One forgotten VM is a 06:00 incident with a healthy-looking cloud factory.
• The Azure-SSIS runtime exists to lift SSIS packages. TechLearningPro does not have that estate. Standing one up "in case" bills a cluster you do not need.

Copy activity moves bytes: from PostgreSQL to a dated Parquet prefix, from a vendor SFTP drop into raw, from ADLS into a dedicated SQL pool. Data integration units and parallelism decide throughput. Copy is idempotent when the destination is a date prefix you overwrite, or when the SQL load is a staging table you truncate first. Copy into an ever-growing folder without a date, with retries enabled, duplicates the day the activity fails after the files land and before the activity reports success.

Mapping data flows compile to Spark on a cluster that spins up for the run. That is legitimate for a transform that is awkward in SQL and that you are willing to wait and pay for. It is a poor home for completion-v4 if the definition is relational and the warehouse is about to read the result anyway. ELT wins: Copy lands Parquet, the dedicated pool or the Fabric warehouse runs the SQL you can review, and the data flow does not get a second definition. Debug sessions for data flows stay warm for their time-to-live. A session left on overnight is a quiet invoice. Turn it off, and prefer a triggered run as the thing you trust.

Triggers matter for correctness. A schedule trigger fires at a clock time and can overlap itself if a run overruns. A tumbling-window trigger gives the pipeline a window start and end, can depend on the previous window, and retries a window without also retrying tomorrow. The daily mart wants a window: event_date equals the window, reruns replace that date, late windows stay visible. Storage-event triggers fit a file drop whose arrival is the signal. They fit a vendor drop. They do not fit the completion definition, which needs enrollment and progress together, not "a file appeared."

Factories belong in git. The published JSON is the artifact. A hot fix in the live canvas that never returns to the branch will be overwritten, or it will be the only copy of the truth, and both outcomes have happened to teams. Parameters carry the date. They do not carry connection strings.

Dedicated SQL, serverless SQL, and Spark pools are three products in one workspace

A Synapse workspace can contain a dedicated SQL pool, a serverless SQL pool, Spark pools, and pipelines that look like Data Factory. The overlap with Data Factory is real. Pick one orchestrator for the morning graph.

The dedicated SQL pool is MPP. A control node plans. Compute nodes and distributions execute. You pay in DWUs while the pool is resumed, and you pay for storage while it is paused. Pause is how a development pool spends nights. Resume is not instantaneous. The pool that serves the 06:00 board is resumed before the board, or it is a different, smaller pool you paused. Production pause is a decision with a timestamp, not a reflex.

Tables are distributed HASH, ROUND_ROBIN, or REPLICATE.

• HASH(learner_id) co-locates a learner's rows. Joins on learner_id between two hash-distributed tables can run locally on the distributions. A hash on a skewed key, such as a nullable status, sends the nulls and the popular value to one distribution and leaves the others idle.
• ROUND_ROBIN spreads rows without regard to a key. It loads fast. It shuffles on a join. Staging tables are often round-robin heaps for that reason.
• REPLICATE copies a small dimension to every compute node. Lesson attributes belong here when they are small. The fact does not.

The default index for a large fact is a clustered columnstore. It compresses and it scans. A heap is for staging you are about to rewrite. Statistics drive the optimizer. A pool that was loaded by COPY and never analyzed will choose a join strategy from guesses. Schedule statistics updates on the tables the board joins.

Serverless SQL pool queries files in ADLS with OPENROWSET or external tables and is billed per terabyte processed. CETAS (CREATE EXTERNAL TABLE AS SELECT) can write Parquet back to the lake. There is no DWU to pause because there is no dedicated warehouse sitting under the query. Serverless SQL is the Athena-shaped tool: one day of raw, a cutoff in how you grant and how you write SQL, results you should treat as sensitive. It is a weak place to pin forty concurrent Monday dashboards that each join enrollment to progress. It is a strong place to validate a partition before the dedicated pool publishes.

Spark pools in the same workspace autoscale and auto-pause. They run the PySpark you already trust, including Delta from the Delta lesson. If Azure Databricks is the standard, do not also keep a Synapse Spark pool "for the same jobs" unless you enjoy two runtimes and two bills. One Spark platform.

Synapse Link attaches an analytical replica to some operational Azure databases, notably Azure SQL and Cosmos DB. It is not the enrollment path for PostgreSQL in this course. Naming it in the diagram because the icon exists will not move the slot.

Fabric is a capacity, and OneLake is the lake it shares

Microsoft Fabric prices a capacity in capacity units and runs lakehouse, warehouse, data integration, and Power BI on that capacity. OneLake stores lakehouse tables as Delta. Shortcuts can point at an ADLS container or at S3 without copying the raw bytes into a second lake. The shortcut is a name. The query still spends capacity units when someone scans.

The warehouse in Fabric is a T-SQL serving surface over that shared storage. For a team whose analysts already live in Power BI and whose raw data can remain in ADLS via a shortcut, Fabric is a coherent serving choice: one capacity, one lake name, the mart as Delta or as the warehouse table, row-level security in the serving engine the portal uses.

The failure mode is running Fabric beside a dedicated SQL pool that also thinks it is the mart. Shortcuts make the duplication feel cheap because the raw files were not copied, and then two SQL engines apply completion-v4 with a one-line difference. Pick one.

Choose Fabric when the organization is standardizing on capacity units and Power BI in the same tenant, and you can size the capacity for Monday and accept sharing or isolation among workloads on that capacity. Choose a dedicated SQL pool when you already have a DWU design, a pause schedule, and distribution keys you have explained, and Fabric would be a second warehouse. Choose Data Factory plus ADLS plus serverless SQL, with no dedicated pool, when exploration is the product and the Monday concurrency is still a forecast. Revisit when forty people queue.

Capacity units are shared. A Spark notebook and a warehouse query and a semantic-model refresh can contend. Isolation inside Fabric is a capacity and a workspace decision, analogous to WLM or a second Redshift workgroup. Lesson 58 prices a second capacity the same way it prices a second warehouse: only with an owner and a workload.

Event Hubs is a log with a Kafka-shaped door

Standard Event Hubs throughput units are the capacity unit: on the order of 1 MB/s ingress and 2 MB/s egress per unit, with a partition count you choose up front on many tiers. Partitions are the shards. A partition key routes a record. A constant key or a bare lesson_id during one live session hotspots a partition. learner_id spreads progress. Premium and Dedicated change the isolation and the price. They do not change the hash of a bad key.

Capture writes the stream to ADLS or Blob on a time and size window, in Avro or Parquet, under a path that includes the hub and the partition. Capture is the Firehose-shaped landing. It is at-least-once. The hub itself retains events for the retention you set, and consumers with offsets can replay inside that retention. Capture files are the lake. The hub is the log. Keep that sentence, because teams delete the hub retention once Capture exists and then discover they cannot rewind a consumer, only reread files.

The Kafka endpoint lets Kafka clients talk to Event Hubs. Consumer groups work. Many client settings work. The endpoint is not a Kafka distribution you can assume is feature-complete. Before Module 4 moves, a pilot has to prove the features you actually use: the transactional producer and the exactly-once sink pattern from the transactions lesson, compacted topics if any consumer depends on them, Kafka Streams state stores and changelogs, Schema Registry compatibility, and Connect. A console producer that sends "hello" proves the port is open. It does not prove the enrollment pipeline.

If those features are load-bearing, keep Kafka, or move Kafka to a managed Kafka service only after the same pilot. Event Hubs is the right bus for Azure-native producers that can live with Event Hubs' own contract, landed via Capture into a prefix of their own. It is the wrong silent replacement for the progress topic.

One change feed for enrollment

A Data Factory Copy that selects PostgreSQL rows where updated_at is greater than the last watermark is a batch extract. It is a respectable ELT increment. It misses deletes unless the source keeps them. It has no slot, and it does not retain WAL, which is a virtue, and it is not the feed lesson 46's consumers already use. Running the watermark copy beside Debezium means two extracts. Run the watermark extract only in the quarter you retire the slot, with deletes accounted for and subscribers moved.

If the source moves to Azure Database for PostgreSQL, logical decoding still exists, and the slot still retains WAL when the consumer stalls. The page does not change because the server moved region.

Selection

Architecture and Design Diagrams

mermaidCopy
flowchart LR
  subgraph sources [Sources]
    PG["PostgreSQL enrollment"]
    APP["Progress producers"]
  end
  subgraph log [One log]
    K["Kafka topics"]
  end
  subgraph lake [ADLS Gen2]
    RAW["raw containers"]
    CUR["curated Parquet"]
  end
  subgraph orch [One publisher]
    ADF["Data Factory or Airflow"]
  end
  subgraph serve [One mart]
    DSQL["Dedicated SQL or Fabric warehouse"]
    SSQL["Serverless SQL exploration"]
  end
  PG --> K
  APP --> K
  K --> RAW
  RAW --> ADF
  ADF --> CUR
  CUR --> DSQL
  RAW --> SSQL
  DSQL --> BOARD["Monday board"]
  SSQL --> INV["Single-day investigation"]

The side door, if an Azure service must emit events and cannot use the Kafka client, is Event Hubs plus Capture. It has its own container.

mermaidCopy
flowchart LR
  EDGE["Azure producer"] --> EH["Event Hubs"]
  EH --> CAP["Capture"]
  CAP --> RAW2["raw/edge"]
  RAW2 --> JOB["Compact to Parquet"]
  JOB --> MART["Reviewed transform into the one mart"]

Technical Explanation

How a date partition stays idempotent on ADLS

The sink writes objects under event_date=yyyy-MM-dd. A compaction job, Spark on the pool you standardized, reads that directory and writes a new directory of larger Parquet files, then replaces the published directory with a rename. The hierarchical namespace is why that replacement is a directory operation rather than a long copy. Readers of the curated path should see the old directory or the new one. A job that writes files in place into the directory analysts are scanning will show them a partial day. Write to a temporary directory, then rename into the published name.

Forbidden columns fail the job before the rename. A rerun overwrites the same date. Counts before and after the rerun match. That is the test.

Data Factory passes the window start as event_date. The pipeline is not allowed to compute "yesterday" from the clock inside the activity in a way a retry can change. The window parameter is the date. A retry of Tuesday's window still writes Tuesday.

What the dedicated pool does during publish

Load the day's Parquet into a round-robin staging heap with COPY, using the workspace managed identity. Then publish into the hash-distributed columnstore table.

At this volume, delete the serving rows for that event_date and insert from staging, inside a transaction if the pool's transaction covers the statements you need, and follow with a grain check. When the day's delete becomes too heavy, define the serving table with a range partition on event_date and switch a loaded, aligned table into that partition. Switch is a metadata move. It demands the same distribution, index, and column definitions on both sides. A switch from a table that is "almost" the same shape fails, and the fallback of a quiet delete-and-insert is better than a switch you have never rehearsed. Rehearse switch on a development pool before it is the 05:30 step.

HASH(learner_id) serves learner-aligned joins. The Monday board's group-by on lesson_id and event_date cares that the columnstore and the predicate eliminate rowgroups. Distribution does not prune by date. A where-clause on event_date still matters, and a partitioned table can eliminate partitions. Those are different mechanisms. People arriving from Hive expect the directory to be the prune. On the dedicated pool the directory is gone. The rows live in distributions, and partition elimination is a table property you created on purpose.

Pause and DWU scale are operational. Scale up before a heavy backfill, scale down after, and expect running queries to be affected by the move. A dashboard WLM-equivalent in Synapse is workload groups and a classifier. The load and the board should not share an unlimited pool of resources at 05:50. If you cannot separate them, move the load earlier. Evidence is wait time, not a feeling that SQL "should be fast."

What serverless SQL is allowed to touch

Grant the exploration identity read on the raw and curated containers' specific paths, using ACLs, not an account-level data reader role. Teach the queries to filter event_date in the file path. OPENROWSET on a wildcard of the whole container will scan the year and the invoice will be the first anyone hears of it. A view that already contains the date filter, granted to analysts, is a better contract than a raw BULK path everyone edits.

Serverless SQL does not become correct by being cheap on a small day. The definition of completion still has a version. A serverless view that reimplements completion-v4 "for convenience" is a second mart. Exploration reads raw or reads the curated files the publisher wrote. It does not invent the rate.

Event Hubs Capture files are small until you compact them

Capture's window writes many files, often per partition per window. Spark will crawl. The compaction job is part of the landing, not an optimization you schedule after the first angry notebook. The same few-hundred-megabyte Parquet target from the lake lesson applies. Avro capture is an ingest format. The curated path is Parquet.

Consumer lag on the hub is the tile's cursor if the tile reads Event Hubs. It is not the mart's freshness. The mart's freshness remains the published date against the clock, from lesson 47.

Failure behavior

A Copy activity that retries after writing files into an append-only folder duplicates. The destination prefix includes the window, and the first step of the retry deletes that window's output or overwrites it.

A self-hosted integration runtime with one node will take the extract down during patching. Two nodes, and a pipeline alert on IR status, belong in the design if the extract cannot use the Azure IR.

A dedicated pool COPY that is allowed to skip errors will publish nulls in columns the grain depends on. Fail the load. The reject rows are a count.

A Fabric capacity that saturates will slow the warehouse and the notebook together. The metric is capacity utilization and throttling, and the response is a bigger capacity, a quieter neighbor workload, or a different window. Adding a dedicated SQL pool beside it during the incident creates the second mart. It is not the incident response.

An Event Hubs Kafka pilot that did not test transactions will fail on the first exactly-once sink, in production, on the day you cut over. The pilot list is a gate.

Practical Implementation or Design Walkthrough

Problem

The account is Azure. Progress files land in ADLS Gen2. Publish curated.completion_attempt on a dedicated SQL pool by 06:00. Investigators use serverless SQL. Fabric is evaluated and, in this walkthrough, not selected, because the team already operates the dedicated pool and will not run both. The decision record says what would make Fabric the serving engine later.

Layout

textCopy
abfss://lake@tlpdata.dfs.core.windows.net/raw/progress/event_date=2026-11-10/
abfss://lake@tlpdata.dfs.core.windows.net/curated/completion/event_date=2026-11-10/

The job identity has ACL write on curated/completion and read on raw/progress. It does not have Storage Blob Data Owner on the account. Humans who investigate have read ACL on raw/progress through a group, and no write.

Data Factory window

The tumbling-window trigger passes windowStart. The first activity deletes the curated directory for that date if the retry must be clean, or the Spark job overwrites via a temp-directory rename. A minimal pipeline shape, enough to review in git:

jsonCopy
{
  "name": "tlp_completion_day",
  "properties": {
    "activities": [
      {
        "name": "CompactAndDefine",
        "type": "SparkJob",
        "dependsOn": [],
        "policy": {
          "timeout": "02:00:00",
          "retry": 1,
          "retryIntervalInSeconds": 120
        },
        "typeProperties": {
          "sparkJobDefinition": "tlp_completion_v4",
          "args": ["--event_date", "@{formatDateTime(trigger().outputs.windowStart, 'yyyy-MM-dd')}"]
        }
      }
    ]
  }
}

The activity type in a given factory might be a Spark job definition, a Synapse notebook activity, or an Airflow task that submits the same arguments. The contract is the argument: the window date, a single retry that is safe because the date is overwritten, and a timeout that leaves margin before 06:00. If your orchestrator is still Airflow, this JSON never gets deployed. The DAG passes the same argument. Two publishers are the defect, not the missing icon.

I labeled the activity type as a sketch of the control-flow contract. Bind it to the activity your factory actually runs, and keep the date expression. Do not compute the date inside the Spark main method from the system clock.

Spark projection and grain

The job runs on the Spark platform you standardized. The logic is the same gate as the AWS walkthrough, pointed at abfss.

pythonCopy
from pyspark.sql import SparkSession, functions as F
import sys

event_date = sys.argv[sys.argv.index("--event_date") + 1]
spark = SparkSession.builder.getOrCreate()
src = f"abfss://lake@tlpdata.dfs.core.windows.net/raw/progress/event_date={event_date}/"
raw = spark.read.parquet(src)
forbidden = [c for c in ("email", "card_token", "full_name") if c in raw.columns]
if forbidden:
    raise RuntimeError(f"forbidden columns in raw progress: {forbidden}")

day = raw.filter(F.col("event_id").isNotNull()).dropDuplicates(["event_id"])
# completion-v4 is a reviewed function. This placeholder keeps the grain explicit.
attempts = day.groupBy("learner_id", "lesson_id", "attempt_number").agg(
    F.max("event_time").alias("completed_at")
)
(
    attempts.withColumn("event_date", F.lit(event_date))
    .withColumn("definition_version", F.lit("completion-v4"))
    .repartition(8)
    .write.mode("overwrite")
    .parquet(
        f"abfss://lake@tlpdata.dfs.core.windows.net/curated/completion/event_date={event_date}/"
    )
)

Overwrite of a directory on ADLS is safe when readers use the published table after the job succeeds, and when a failure leaves the previous directory intact. If your runtime's overwrite deletes first, write to .../completion_tmp/event_date= and rename the directory into place. Know which behavior your Spark version has. Do not discover it on a Monday.

Eight partitions remain a dev default. Size production files from bytes, toward a few hundred megabytes each.

The groupBy above is a stand-in that keeps a single row per grain for the exercise. The real completion-v4 rule, including what makes an attempt complete, stays in the reviewed module the platform already versioned. A lesson that redefines completion in an illustration would be the second mart.

Dedicated SQL pool

sqlCopy
CREATE TABLE curated.completion_attempt (
  learner_id VARCHAR(64) NOT NULL,
  lesson_id VARCHAR(64) NOT NULL,
  attempt_number INT NOT NULL,
  completed_at DATETIME2,
  event_date DATE NOT NULL,
  definition_version VARCHAR(32) NOT NULL
)
WITH (
  DISTRIBUTION = HASH(learner_id),
  CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE staging.completion_attempt (
  learner_id VARCHAR(64) NOT NULL,
  lesson_id VARCHAR(64) NOT NULL,
  attempt_number INT NOT NULL,
  completed_at DATETIME2,
  event_date DATE NOT NULL,
  definition_version VARCHAR(32) NOT NULL
)
WITH (
  DISTRIBUTION = ROUND_ROBIN,
  HEAP
);

COPY INTO staging.completion_attempt
FROM 'https://tlpdata.dfs.core.windows.net/lake/curated/completion/event_date=2026-11-10/*.parquet'
WITH (
  FILE_TYPE = 'PARQUET',
  CREDENTIAL = (IDENTITY = 'Managed Identity')
);

DELETE FROM curated.completion_attempt
WHERE event_date = '2026-11-10';

INSERT INTO curated.completion_attempt
SELECT * FROM staging.completion_attempt;

TRUNCATE TABLE staging.completion_attempt;

Confirm COPY INTO path and credential syntax against the dedicated pool's current statements when you build the pipeline. The design is: managed identity, Parquet, staging heap, day-scoped replace, then truncate staging only after the checks. When you outgrow the delete, switch a partition you have rehearsed.

Serverless check, not a second definition

sqlCopy
SELECT COUNT(*) AS attempts
FROM OPENROWSET(
  BULK 'https://tlpdata.dfs.core.windows.net/lake/curated/completion/event_date=2026-11-10/*.parquet',
  FORMAT = 'PARQUET'
) AS rows
WHERE rows.definition_version = 'completion-v4';

Compare that count to the dedicated pool. They read the same files and the same published table. A mismatch is a failed publish. An analyst query against raw progress for one day uses a path that already contains event_date=2026-11-10, not a container wildcard.

What was rejected in this walkthrough

Fabric warehouse as a second serving copy. Event Hubs for progress. A mapping data flow that reimplements completion-v4. A watermark Copy of enrollment. A crawler-equivalent that infers the mart schema from folders. Each rejection has a reopen condition in the selection table.

Enterprise Real-World Scenarios

TechLearningPro moves the lake, not the log

The company standardizes new analytics accounts on Azure because Entra ID is already the identity plane. Kafka, Connect, and Debezium stay where they are for the quarter. The migration is the raw prefix onto ADLS Gen2 with the hierarchical namespace, compaction via the existing Spark jobs retargeted at abfss, and a dedicated SQL pool for the mart. Data Factory orchestrates only if the team is ready to move the DAG. Until that review, Airflow remains the publisher and Data Factory is not "also on."

Serverless SQL is granted to investigators through a group ACL on the date-partitioned curated and raw paths. A shared key that a contractor used in the pilot is rotated and shared key access is disabled before the first production container is marked complete. The self-hosted IR appears only if PostgreSQL cannot be reached by the Azure IR. Enrollment is not extracted by Copy, because the slot already has consumers.

The reopen list is written down. Fabric becomes the serving engine if the BI platform standardizes on Fabric capacity and the dedicated pool's DWU bill is the thing being replaced, not supplemented. Event Hubs becomes the progress log only after the Kafka-feature pilot passes. Someone will ask to keep the pool "one more quarter" after Fabric is live. That quarter is the dual-mart quarter, and the answer is a cutover date.

Northline's seller portal needs a predicate, and finance needs a close

Northline's instructors each see their own order lines. About 500,000 lines a day. Finance closes a month.

On the dedicated SQL pool the portal role is bound to a row-level security predicate on seller_id, the SQL Server pattern: an inline table-valued function, a security policy, and a test identity that selects another seller and receives no rows. Finance uses a role that is not filtered, and that role is not the portal's login. The close is close_month on the same table or on a monthly published table with a definition version. Workload groups keep the monthly close from taking the portal's resources. If they cannot, finance gets a scheduled publish into a small table the portal never scans, not a second warehouse with a second grain.

If Northline later picks Fabric, the same predicate has to exist in the Fabric warehouse the portal queries. A Power BI semantic model that filters seller_id in DAX, while the warehouse still grants the full table to the same identity, is a report filter. It is not row-level security. Lesson 57 makes that test mandatory on whichever engine won. The Azure choice does not change the test.

A mapping data flow that "cleans" orders in the canvas, while T-SQL also cleans them, will disagree on refunds. One transform, in git, in SQL or in Spark, reviewed like the completion definition.

Best Practices

• Create the analytics storage account with the hierarchical namespace on, shared keys off, and a private endpoint.
• Grant job identities with ACLs on prefixes. Keep account-level data-plane roles for true administrators, few of them.
• Pass event_date from the trigger window or the DAG. Overwrite that date on retry.
• Put completion-v4 in one reviewed module. Data Factory, Spark, and T-SQL do not each grow a private copy.
• Stage as round-robin, serve as hash-distributed columnstore, and update statistics on the serving table.
• Give serverless SQL path-scoped read and a date in the path. Compare its count to the serving table before publish.
• Keep one orchestrator. If Airflow stays, Data Factory does not also fire the mart.
• Pilot Event Hubs against the Kafka features you use before any cutover conversation.
• Treat Fabric capacity and a dedicated pool as alternatives for the mart. Shortcuts do not make two SQL definitions cheap.
• Write the reopen conditions in the same table shape as lesson 54 so lesson 58 can price one design.

Common Mistakes and Anti-Patterns

A storage account without the hierarchical namespace, because the first upload used the Blob endpoint. Spark commits become copy-and-delete. Create the account correctly. Migrating later is a project.

Storage Blob Data Owner on the factory's managed identity. ACLs stop meaning anything for that identity. The 403 goes away and so does the boundary around enrollment files.

A mapping data flow as the home of the business definition. The canvas is hard to review, the cluster spin-up eats the morning, and a SQL mart downstream reimplements the same rule. Land files, transform in reviewed SQL or Spark.

Debug sessions as a workspace habit. The TTL expires when it expires, not when the meeting ends. The bill is a data-flow cluster. Triggered runs are the production path.

Dedicated SQL and a Fabric warehouse both certified. Shortcuts avoid a second raw copy and do nothing for a second completion-v4. One publisher, one serving engine, a cutover date if you change engines.

Serverless SQL with a container-wide wildcard. The first curious query scans history. Put the date in the path and grant the narrow prefix.

Event Hubs Kafka endpoint after a hello-world producer. Transactions, compaction, Streams, registry, and Connect are the pilot. Capture is not a rewind of the hub.

Watermark Copy beside Debezium. Two extracts, and deletes disagree. One feed.

A single-node self-hosted IR in front of the only extract. Patching it is downtime. Two nodes, or an architecture that uses the Azure IR because the network was opened on purpose.

COPY that skips bad files. Nulls in the grain, green pipeline, wrong Monday. Fail the load.

Troubleshooting and Design Review

Spark rename is slow or partial files appear in the published directory. Confirm the account has the hierarchical namespace and that the job writes to a temporary directory before rename. An overwrite that deletes in place will show readers a short day if they read during the job. Publish after success, through a view or a table name you swap.

403 from a job that "has RBAC." If the role is not a data-plane role, ACLs apply and a missing execute bit on a parent directory fails the child. If the role is a data-plane role at the account, ACLs are not your deny. Read the error and the role scope before you add a wider role.

The tumbling window skips or overlaps. The window's start parameter is the date. A schedule trigger that computes yesterday at runtime will double-fire after a delay. Look at the trigger type first.

Dedicated pool publish is slow at 05:40. Check file counts on the Parquet prefix, the staging distribution, and whether statistics exist. Then check workload groups for a dashboard already running. Tiny files and a hash-distributed staging table are the usual self-inflicted causes. Compact, stage round-robin, then insert.

Serverless count and dedicated count disagree. They are not reading the same path, or the dedicated delete-and-insert published a different date than the files. Fail the DAG. Do not average them.

Event Hubs lag on one partition. Inspect the partition key for that hour. Capture can be healthy while a consumer on one partition is stuck. Those are different cursors.

The slot's WAL grows and Data Factory is green. Factory health does not include a slot you did not open in the factory. The PostgreSQL query from lesson 46 is still the page.

Fabric capacity throttles during the board. Identify which workload consumed the units. Moving the mart to a dedicated pool in the same morning creates two boards. Isolate or scale, and keep one definition.

Design review:

• Which workload does this Azure service own, and what already owns it?
• Is the storage account HNS, private, and free of shared keys?
• Which identity has a data-plane role, and which has only ACLs?
• What is the publish: a directory rename, a partition switch, or a SQL transaction?
• If Kafka moved to Event Hubs, which Module 4 features passed the pilot?
• How many engines can currently answer "what is the completion rate"?

Interview Questions and Answers

Q1. What does the hierarchical namespace change for a Spark job committing a day of Parquet?

It makes directory rename a metadata operation. The job can write a temporary directory and rename it into the published event_date path, so readers see the previous day or the new day. On a flat object container, that rename is a copy and a delete, and a failure leaves a partial prefix you have to reconcile. I create analytics accounts with the namespace enabled. I still do not read the directory while the job is mid-write. The published name moves after success.

Q2. A pipeline identity has read ACLs on one folder and Storage Blob Data Reader on the account. What can it read?

The account-level data-plane role. ACLs do not reduce a data action RBAC already granted. That identity can read blobs across the account scope, including containers the ACL was meant to hide. I remove the broad role and keep the ACL, with execute on parents and read on the prefix. I use account-level data roles for a small set of administrators, not for the daily job.

Q3. When is a mapping data flow the wrong place for completion-v4?

When the rule is relational and a warehouse or a reviewed Spark job will publish the mart anyway. The data flow is a Spark cluster with spin-up, a canvas that is harder to diff, and a second copy of the rule the moment T-SQL also implements it. I use Copy or Spark to land clean Parquet, and I keep the definition in one module. I use a data flow when the transform is genuinely awkward in SQL and the team accepts the runtime and the review process for that graph.

Q4. How do you choose dedicated SQL pool versus serverless SQL for TechLearningPro?

Dedicated SQL, sized in DWUs and paused only when resume fits the clock, serves the Monday mart: concurrency, distribution, columnstore, workload groups. Serverless SQL, billed per terabyte scanned, serves one-day investigation over Parquet with the date in the path. I do not point the board at serverless and also keep a dedicated copy "just in case" unless one is explicitly not certified. Fabric warehouse replaces the dedicated pool in that sentence if Fabric is the standard. It does not sit beside it.

Q5. Why is staging ROUND_ROBIN and the fact HASH(learner_id)?

Staging should load quickly and then be rewritten. Round-robin does that. The fact is hashed on learner_id so joins to other learner-distributed tables stay local. I replicate small lesson attributes. I do not hash on a skewed status column. Date pruning on the dedicated pool comes from the predicate and, if I defined them, range partitions. It does not come from the hash. I update statistics after the load so the optimizer is guessing less.

Q6. When would you switch a partition instead of deleting the day and inserting?

When the day's delete and insert cost more than the operation can spare, and I have rehearsed switch between two tables that match in distribution, columns, and index. Switch is a metadata publish of that partition. At the teaching volume, a day-scoped delete and insert is acceptable and easier to read. I do not introduce switch for the first time at 05:30. I also do not skip the grain check because switch succeeded. Alignment is not uniqueness.

Q7. What must an Event Hubs Kafka-endpoint pilot prove before it can replace the progress topic?

The features Module 4 actually uses. Transactions and the exactly-once sink pattern, compacted topics if any consumer needs them, Kafka Streams changelogs, schema compatibility, and Connect. Consumer groups and a test message are the start of the pilot, not the end. Capture landing is a separate decision about files, and it does not give me an offset to rewind once hub retention is gone. If the pilot fails, Kafka stays, and Event Hubs is only a side door for producers that speak its native contract.

Q8. What is a self-hosted integration runtime for, and what does it cost operationally?

It runs Copy and other activities inside a network that the Azure IR cannot see, such as a PostgreSQL endpoint with no public path. I operate those nodes: patching, capacity, and at least two of them if the extract is on the critical path. If I can reach the source with the Azure IR over a private path I designed, I do not add a self-hosted fleet to feel safer. A single node in front of enrollment extract is a patching outage.

Q9. How is a watermark Copy of PostgreSQL different from the Debezium slot?

The watermark extract pulls rows changed since a timestamp, on a schedule, and it sees deletes only if the source recorded them. It does not hold WAL. The slot streams the log, retains WAL if the consumer stalls, and is what current subscribers already consume. Both are valid contracts. Both together are two feeds. I keep the slot until a cutover moves every subscriber and accounts for deletes. A green Copy activity does not tell me the slot is healthy.

Q10. Fabric shortcuts made the raw lake visible without a copy. Why is that not permission to keep Synapse dedicated SQL as well?

The shortcut avoids a second raw file. It does not avoid a second SQL implementation of completion, a second grant model, or a second capacity bill. I use shortcuts so Fabric can read the ADLS raw zone while Fabric is the serving engine, or so exploration can see raw without a copy. I set a cutover date if the serving engine changes. Two certified marts is the defect the shortcut makes easy to rationalize.

Hands-On Exercises

Exercise: Publish one day on ADLS and a dedicated SQL shape

Problem statement. Use a development storage account with the hierarchical namespace enabled, or a local directory layout that mirrors event_date= if you have no Azure subscription. Produce the Azure decision record for the four workloads and publish one day of attempts. If you have no dedicated pool, run the delete-and-insert in PostgreSQL and write the distribution and index as comments on the real design. Do not stand up Event Hubs for the sample.

Tasks.

1. Write the owner table, including Fabric versus dedicated SQL, and the reopen condition for Event Hubs.
2. Land sample Parquet under event_date. Run a Spark or Python rewrite that fails if email is present and that is idempotent on rerun.
3. Show the staging versus serving distribution choice and the day-scoped replace. Run the grain check. Inject a duplicate and show the failure.
4. Write the serverless-style count against the same files and compare it to the serving count.
5. State the identity model: which caller has an account-level data role, which has an ACL, and why shared keys are off.
6. Name the freshness signal for the board, distinct from pipeline success and from Event Hubs lag.

Expected deliverables. Decision record, layout, rewrite script, SQL publish, check evidence, identity note, freshness note.

Evaluation rubric.

Lesson Summary

ADLS Gen2 is the lake disk with a directory tree. Enable the hierarchical namespace, disable shared keys, and grant prefixes with ACLs. A data-plane RBAC role at the account overrides the careful ACL. Data Factory is control flow: Copy lands bytes, tumbling windows give you a date you can rerun, and mapping data flows are Spark you should use only when SQL or your existing Spark job is the wrong tool. One orchestrator publishes. Dedicated SQL pool is the MPP mart: round-robin staging, hash-distributed columnstore serving, statistics, workload groups, pause only when resume fits 06:00. Serverless SQL explores a dated path and is billed by the scan. Fabric is a capacity and a OneLake that can replace that serving story, shortcuts included, and becomes a second mart if you keep both certified. Event Hubs plus Capture is a side-door log and landing. Its Kafka endpoint replaces Module 4 only after a pilot of the features you already depend on. Enrollment stays on one slot until a real cutover.

Google Cloud Data Engineering Services puts the same four workloads on Cloud Storage, BigQuery, Dataflow, and Pub/Sub. Security then binds identity and row-level tests to whichever engine you kept. Cost prices the DWU you forgot to pause, the serverless wildcard, and the second capacity. Module 6 operates the record. The capstone has to pick one serving engine with the reopen conditions still on the page.

Further Reading

• Azure Data Lake Storage Gen2 introduction, hierarchical namespace, ACLs, and access tiers.
• Access control model for Data Lake Storage for how RBAC and ACLs evaluate together.
• Azure Data Factory documentation, integration runtimes, tumbling-window triggers, and mapping data flows.
• Azure Synapse Analytics, dedicated SQL pool distribution and indexing, COPY INTO, serverless SQL OPENROWSET, and workload management.
• Microsoft Fabric documentation, capacities, OneLake, shortcuts, and the warehouse.
• Azure Event Hubs documentation, throughput, Capture, and the Kafka protocol support matrix you will use as the pilot checklist.
• Row-level security for the security predicate and policy on a dedicated SQL pool.
• Lessons 46, 47, and 54 for the slot, the freshness page, and the workload table this lesson mirrors.