Contoso Airlines' management has been asking the same question for months and nobody can answer it with data: which routes are genuinely profitable by season. It is not an idle query; the winter schedule, the renegotiation of charges with two airports and the decision to keep or close the Palma–Munich route all depend on it. Answering it means crossing three years of sales from db-reservas, the real occupancy of the flights, the fares applied that live in Cosmos DB, the per-rotation costs from the crew system and the operational delays: some 400 million rows.

The first instinct — firing that query at db-reservas — is exactly what must not be done. This lesson builds the alternative: an analytics platform that answers big questions about historical data without touching the operational databases even once. And with it, the module closes.

Important cost warning: analytics is where bills spiral fastest. A Synapse dedicated SQL pool costs more than €1,000 a month if you leave it running, and you have to pause it by hand. Serverless SQL pools bill per TB read, so a badly written query over unpartitioned data can cost more than a whole day of virtual machines. To learn, use serverless SQL with small files, and delete the resource group when you finish.

1. Why operational databases are no good for analytics
2. The analytics architecture and its five pieces
3. Azure Data Lake Storage Gen2
4. The bronce, plata and oro layers
5. File formats: CSV versus Parquet and Delta
6. Azure Data Factory: the nightly pipeline
7. Copy activity versus mapping data flows
8. Azure Synapse Analytics
9. Microsoft Fabric, honestly
10. Power BI and the profitability dashboard
11. Data governance with Microsoft Purview
12. The costs of analytics and how they spiral
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

A transactional database (OLTP) and an analytical one (OLAP) are optimized for opposite things:

The practical consequence: an analytical query against the operational database walks over millions of rows, fills the engine's memory, invalidates its caches and locks resources. Even if it eventually works, it degrades ticket sales for every customer for as long as it runs. That is why the two worlds are kept apart, and the separation has a business advantage too: the analytical history keeps data that the operational database deletes or archives.

Every modern analytics platform, whatever it is called, has the same five pieces:

Ingestion (bringing the data in), storage (keeping it cheaply and raw), transformation (cleaning and aggregating it), serving (exposing it for querying) and consumption (visualizing it). Azure has a service for each piece, and what matters is understanding each one's role before its menus.

Data Lake Storage Gen2 is not a separate service: it is an Azure Storage account, the same one from lesson 02-04, with one option turned on at creation time: the hierarchical namespace. That changes two fundamental things:

• Real directories. In Blob Storage, 2026/07/ventas.parquet is a flat name that simulates folders; renaming "a folder" holding a million files means copying and deleting them one by one. With a hierarchical namespace, the directory genuinely exists and renaming it is an atomic operation that takes milliseconds. For an analytics engine that writes and reorganizes thousands of files, the difference is enormous.
• POSIX ACLs. In addition to RBAC access control, you can assign read, write and execute permissions at directory and file level to Microsoft Entra ID users and groups. Contoso uses this so that Nuria Peña's finance team can read the oro layer but has no access to the personal data in the bronce layer.

Everything learned in 02-04 still applies: Hot, Cool and Archive access tiers with lifecycle policies, redundancy, encryption and access with an Entra ID identity instead of keys.

Organizing the lake into three layers — also known as the medallion architecture — avoids the usual fate of data lakes: turning into a swamp where nobody knows which file is trustworthy.

The three rules that make it work: the bronce layer is never modified (if a transformation fails, it is redone from there without bothering the source again); transformations only move forwards; and only the oro layer is connected to dashboards, which stops every analyst building their own version of the truth.

Look at the path in bronce: the data is stored partitioned by date in anio=/mes=/dia= directories. That is not cosmetic. When a query filters on August 2026, the engine reads only that directory instead of three years of files. It is the optimization that saves the most money in this entire lesson.

The reason the columnar format matters so much is directly financial. The profitability query uses 4 columns out of a 40-column table. In CSV you have to read all 400 GB; in Parquet you read around 12 GB, and since Synapse serverless bills per TB read, the same query costs about 30 times less. Practical rule: data comes in however it comes, but in the lake it is stored in Parquet (or Delta if rows have to be updated), always partitioned by date.

Data Factory is the integration and orchestration service. Its five concepts, which are the same in Synapse Pipelines and in Fabric:

That last concept is the one that causes the most confusion and the one Contoso needs to understand properly: to read db-reservas, which is only reachable through a private endpoint, you need a runtime with access to the virtual network; and to read the legacy billing system in the Barcelona basement you would need a self-hosted runtime, an agent installed on an on-premises server that establishes the outbound connection.

Contoso's nightly pipeline copies the previous day's bookings into the bronce layer:

Three design decisions are written into that JSON. The query brings back only the previous day instead of the whole table: that is called an incremental load and it is the difference between a two-minute copy and a two-hour one that also punishes the operational database. The destination is Parquet in the lake. And the retry policy stops a transient network failure forcing somebody to relaunch the pipeline by hand in the morning.

The trigger is a tumbling window at 03:00, not a simple schedule, because it guarantees non-overlapping time windows and it lets you reprocess a specific day if an error is found: you relaunch the window for 11 August and only that directory is corrected.

The criterion is straightforward: use the copy activity to move data and data flows only when the transformation justifies them. If your team can write SQL or Python, transforming with a Spark notebook or with serverless Synapse views is usually cheaper and easier to version in Git than a visual flow.

Synapse is a workspace that brings several engines together. What matters is knowing which one to use:

The serverless pool is the natural way in, because it lets you query the lake without loading anything anywhere:

OPENROWSET reads the files where they are; the ** walks the partition subdirectories. There are no tables to create and no data to load, and if more files land on that path tomorrow, the same query picks them up. When a query like this is used daily, it is saved as a view in a serverless database and Power BI connects to it.

The dedicated pool, on the other hand, is a full data warehouse with its own columnar storage and table distribution. It brings constant performance and high concurrency, and it costs what it costs: you pay per hour it is running, used or not, so it is paused when it is not needed. Contoso, with 400 million rows and a dozen analysts, starts with serverless and will only consider a dedicated pool if concurrency demands it.

Microsoft Fabric is the evolution towards which Microsoft is converging all of this: Data Factory, Synapse, Power BI and the lake in a single SaaS platform, with common storage called OneLake, Delta format by default and billing by capacity instead of by service.

The honest position is three statements. First: this lesson's concepts — lake layers, Parquet, pipelines, SQL and Spark engines — are the same in Fabric, so nothing you have learned is lost. Second: Fabric is where new development is pointing, and for a project starting today it deserves serious evaluation. Third: Synapse and Data Factory are still fully supported and they are what is deployed in most organizations, including this course's Contoso, which already has its platform built and is not going to rebuild it for a new arrival.

Power BI is the consumption layer: it connects to the oro layer or to the serverless views and produces the dashboard that answers management's question. Contoso's has one page per question: revenue and margin by route and season, average occupancy against break-even, the trend over the last 36 months, and per-route detail.

Two technical decisions worth knowing: Import mode copies the data into the Power BI model and gives instant answers, at the price of the data being as fresh as the last refresh; DirectQuery mode queries the source on every interaction, always up to date but slower and with a cost per query — important here, because every interaction with serverless reads TB and bills for them. For a dashboard refreshed every night, Import is the right and the cheapest choice.

Once you have a lake with three layers, dozens of files and several teams consuming from it, new questions appear: where does this number come from? Who can see this column? Where is there personal data?

Microsoft Purview answers that with a catalog that automatically scans the sources and builds an inventory, classifies sensitive data (detecting that a column contains email addresses or identity document numbers) and shows the lineage: which source feeds which file and which report. For Contoso it matters especially because of GDPR: knowing exactly which lake layers hold passengers' personal data is a requirement, not a convenience. It is only introduced here; governance is picked up again with Azure Policy in lesson 04-06.

The four causes of unpleasant bills, with their remedies:

And one hygiene measure that stops the slow drip: apply a lifecycle policy to the lake, like the one from lesson 02-04, so that bronce data more than a year old moves to the Cool tier and anything more than three years old to Archive. The oro layer, which is the one being queried, stays in Hot.

• Querying the operational database to produce reports. It degrades ticket sales for as long as it runs. It is the mistake this whole lesson exists because of.
• Forgetting to pause the dedicated SQL pool. It is Azure's most common surprise bill and the easiest to avoid.
• Storing the lake in CSV. It multiplies the cost of every serverless query by tens and loses the data types.
• Not partitioning by date. Without partitions, every query reads the whole history even if it only asks for one month.
• Modifying the bronce layer. It is the faithful copy of the source and the safety net for redoing any transformation. Touch it and you lose that.
• Connecting dashboards to the plata layer "because it is more complete". Every analyst ends up with their own version of the truth and two reports give different figures in the same meeting.
• Copying personal data into the oro layer with no need. GDPR applies in the lake just the same; pseudonymize on the way into plata.
• Tip: always start with serverless SQL. Only when you have measured that concurrency or performance are not enough should you consider a dedicated pool.
• Tip: set a budget alert specifically for the analytics resource group. It is the one that drifts fastest.

Three sources have to be brought into the lake: db-reservas (growing by 40,000 rows a day), the Cosmos DB fare catalog (changing twice a day) and a monthly CSV of fuel costs sent by an external supplier.

1. For each source, state the load strategy (full or incremental), the frequency and the type of trigger.
2. What type of integration runtime does each one need, and why?
3. In what format and with what directory structure would you store them in bronce?

The profitability query walks over three years of bookings: 400 GB in CSV or the equivalent 40 GB in Parquet partitioned by year and month, and it uses 4 columns out of 40. It will run once a day to refresh the dashboard and, occasionally, interactively.

1. Which Synapse engine would you choose, and why do you rule out the others?
2. Estimate the order of magnitude of the data read in CSV versus partitioned Parquet, if the query filters on a single year.
3. Which Power BI connection mode would you use, and what impact does it have on cost?

Nuria Peña spots that the analytics resource group has gone from €300 to €2,400 a month. You find: a dedicated SQL pool active for six weeks with no queries in the last four, unpartitioned CSV files throughout the bronce layer, a pipeline copying dbo.Reservas in full every night, and three data flows that only rename columns.

1. Rank the four problems by potential saving.
2. Propose a concrete fix for each one.
3. What preventive measure would you put in place so that it does not happen again?

Solution 1:

1. Strategies: db-reservas, incremental by date (only the previous day's bookings), daily in the small hours, with a tumbling window trigger so that specific days can be reprocessed. Cosmos DB, incremental via the change feed (lesson 03-03) or a full daily load, given that the catalog is small. The fuel CSV, a full monthly load with an event-based trigger that fires when the file appears in storage.
2. db-reservas and Cosmos DB: an Azure runtime with access to the virtual network, because both sit behind a private endpoint and are not reachable from the internet. The CSV: a managed Azure runtime if the supplier drops it in a storage account, or self-hosted if it had to be collected from an on-premises server.
3. In Parquet, partitioned by date: bronce/reservas/anio=2026/mes=08/dia=11/, bronce/tarifas/anio=2026/mes=08/dia=11/ and bronce/combustible/anio=2026/mes=08/. The original CSV is kept exactly as it arrived alongside its Parquet version, because the bronce layer must be able to reproduce the source.

Solution 2:

1. The serverless SQL pool. The dedicated pool is ruled out because one daily query and a few interactive ones do not justify paying for a warehouse by the hour, and you would have to remember to pause it; Spark is ruled out because the transformation is a simple SQL aggregation and spinning up a cluster adds start-up minutes and cost without contributing anything.
2. In unpartitioned CSV you read all 400 GB, since the row-based format forces you to walk every column and there are no partitions to discard. In partitioned Parquet, filtering on one year, you read roughly a third of the data and only 4 columns out of 40: on the order of 1–2 GB. The difference is two orders of magnitude, and since serverless bills per TB read, that is also the difference on the bill.
3. Import, with a nightly refresh after the pipeline. With DirectQuery, every interaction by every user with the dashboard would fire a query against serverless and bill for the data read; with Import you pay for one read a day and the answers are instant.

Solution 3:

1. Ranked by saving: (a) the idle dedicated pool, which on its own explains most of the overspend; (b) the unpartitioned CSV, which makes every serverless query more expensive; (c) the nightly full copy, which pays for transfer, storage and load on the operational database; (d) the trivial data flows, which spin up a Spark cluster to rename columns.
2. Fixes: pause the dedicated pool immediately and, if there have been no queries in four weeks, delete it and work with serverless; convert the bronce layer to Parquet partitioned by date and rewrite the query paths; change the pipeline to an incremental load with a day parameter and a tumbling window trigger; replace the three flows with a copy activity with column mapping, or with a serverless view.
3. Prevention: a budget alert specifically for the analytics resource group (lesson 01-03 and module 8), an Automation runbook that pauses the dedicated pool outside working hours, and a monthly Azure Advisor review. In module 4, Azure Policy is added to directly prevent certain resources from being created without tags or outside the permitted regions.

This lesson closes module 3 and Contoso Airlines' data map is complete. You know why a transactional database is no good for analytics — row storage versus columnar, normalization versus star schema, latency per operation versus volume processed — and why firing the profitability report at db-reservas would have degraded ticket sales. You know the five pieces of an analytics platform and which service fills each one. You have created Data Lake Storage Gen2 understanding what it adds over Blob Storage — a hierarchical namespace with real directories, and POSIX ACLs — and you have organized it into the bronce, plata and oro layers with their three rules: bronce is never touched, transformations only move forwards, and only oro feeds the dashboards. You know why Parquet partitioned by date is the decision that saves the most money, and when Delta is needed.

You have built the ingestion with Azure Data Factory, with its linked services, datasets, activities, pipelines, triggers and integration runtimes, and with a nightly incremental pipeline that copies the previous day's bookings into the lake with retries and reprocessable windows; and you can tell when a copy activity is enough and when paying for a mapping data flow is justified. In Synapse you have compared the four engines and queried the lake with serverless SQL through OPENROWSET, without loading anything anywhere, knowing that the dedicated pool is paid for by the hour it runs and has to be paused. You can place Microsoft Fabric as the convergence Microsoft is heading towards without what you have learned losing its value, you have taken the oro layer into Power BI choosing Import over DirectQuery on performance and cost grounds, and you know Microsoft Purview for cataloging, classifying and tracing data lineage.

Recapping the whole module: you started with the criteria for choosing a data service and came away with a reasoned map of five stores. You deployed db-reservas on Azure SQL Database with its schema, its indexes, its private endpoint and its point-in-time restore. You put the fare catalog in Cosmos DB with the partition key /origenDestino, its request units and its consistency level chosen piece of data by piece of data. You migrated the legacy portal to Azure Database for MySQL without rewriting a line of WordPress, and the crew system to Azure Database for PostgreSQL with PostGIS, PgBouncer and autovacuum under control. And today you have built the analytics platform that finally answers which routes are profitable by season.

That entire platform — compute, storage, networking and now data — has been built with minimal security settings: administrator passwords typed in by hand, connection strings with secrets inside them, broad permissions because that was the quick way, a WAF that does not exist yet, and not a single policy stopping anyone from creating an untagged resource in the wrong region. It was a conscious decision so that we could move forward, and now it is time to close it properly. In module 4, Security in Azure, you will start with Microsoft Entra ID and identity management, move on to RBAC and managed identities so that applications authenticate without a single password, centralize secrets in Azure Key Vault, protect the perimeter with DDoS protection and a web application firewall, assess your whole posture with Microsoft Defender for Cloud and finish by enforcing the rules of the game with Azure Policy. See you there.