All lessons

Module 4: Load to the warehouse · Lesson 4.6 · about 40 min

Security, cost and operations in Azure

Run pipelines with managed identities and Key Vault instead of stored secrets, lock rows down per client, practise a restore, and keep a serverless database from billing all day.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 4 · about 40 minutes · read first: 4.2, 4.5. You’ll be able to run a pipeline with no stored passwords, keep the secrets that must exist in Key Vault, give each role only what it needs, filter rows per client inside the database, practise a restore, and estimate and cap what a serverless database costs.

Treat company data like a hotel treats guests' rooms: every person and program gets a key card that opens only their doors, the master keys sit in a safe, there is a spare plan if a room floods, and the lights switch off when nobody is in.

1. Why this matters

Data people are trusted with customer, revenue and payroll data. A pipeline that shows you handle identity, secrets, access and cost the way a security or finance team expects answers their questions before they ask. In regulated businesses (hotel payments, retail loyalty programmes) it decides whether your work goes live at all.

2. Identity and secrets

  • Managed identity. An Azure resource (a VM, a Container App, a Function, Data Factory) gets its own Microsoft Entra identity. Make that identity a database user (create user [vm-p2d-dagster] from external provider) and grant it rights. The code uses DefaultAzureCredential from Lesson 4.2. No password exists.
  • Key Vault. For secrets that must exist (a vendor’s SFTP key, an ad platform’s API token), store them in Azure Key Vault. Give the pipeline’s managed identity the Key Vault Secrets User role. Rotating a secret then happens in one place.
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

kv = SecretClient("https://kv-p2d.vault.azure.net", DefaultAzureCredential())
ads_token = kv.get_secret("ads-api-token").value        # never print or log it

3. Access inside the database

  • Roles per purpose: loader (writes raw, stg, ops), transformer (dbt: writes mart), report_reader (reads mart), analyst (reads stg and mart).
  • Row-level security (RLS): a filter function decides which rows each user sees, so a client manager sees only their own client’s campaigns, enforced by the database itself. Power BI has its own RLS too (Lesson 6.4); for DirectQuery and shared databases, database RLS is the stronger guarantee.
  • Dynamic data masking shows aXXX@XXXX.com instead of an email to users without the right permission.
  • Auditing to a storage account or Log Analytics: who queried what, and when.
create function ops.fn_client_filter(@client varchar(50))
returns table with schemabinding as
return select 1 as ok
       where @client = cast(session_context(N'client') as varchar(50)) or is_member('db_owner') = 1;

create security policy ops.client_policy
  add filter predicate ops.fn_client_filter(client) on mart.fct_ads_daily
  with (state = on);

-- the app sets the client for this session, then queries normally
exec sp_set_session_context @key = N'client', @value = 'Client A', @read_only = 1;
select sum(spend) from mart.fct_ads_daily;      -- only Client A's rows

4. Backups and restore

Azure SQL Database takes automatic backups and supports point-in-time restore (PITR) within a retention window (7 days by default, configurable), plus optional long-term retention. A restore creates a new database beside the old one; you then copy back what you need or swap. Practise it once, before you need it: delete a table, restore to ten minutes before, compare. A backup you have never restored is a hope, not a plan.

5. Cost: when serverless bills you

Serverless compute bills for vCore time while the database is active, then pauses after an auto-pause delay of idle time (you choose the delay, within limits Microsoft sets). While paused you pay only for storage. Provisioned compute bills every hour of the month. Serverless costs more per vCore-hour, so it wins only when the database is idle much of the time.

Like a taxi meter that keeps running for a while after you get out. One long ride a day is cheap. Many short rides spread across the day keep it running all day.

A quiet day. The pipeline loads for one hour, 05:00 to 06:00. Nothing else touches the database.
The pause delay. With a 1-hour auto-pause delay, the database stays awake until 07:00, then pauses. Billed: about 2 hours.
Refreshes everywhere. Power BI is set to refresh every 30 minutes from 07:00 to 19:00. Each refresh wakes it before it can pause, so it is awake from 05:00 to about 20:00: about 15 billed hours.
Line them up. Two refreshes: one right after the load, one at noon. The first overlaps the load's delay; the noon one costs its own delay. Billed: under 4 hours, with the data no less fresh, because the data only changes once a day.

Practical cost controls: a budget with alerts on the resource group; the shortest auto-pause delay that suits your refreshes; refreshes timed to the loads; no geo-replication you do not need; a monthly look at storage growth; and the free offer for a portfolio project. Prices change, so take the per-vCore-hour price from the Azure pricing page when you do the sums.

Managed identity
An Azure-issued identity for a VM or service, so code there can sign in with no stored password.
Key Vault
The safe for the secrets that must exist, read at run time by identities allowed to.
Row-level security
A database rule that filters rows per user, so each person sees only their slice whatever tool they use.
Auto-pause delay
How long a serverless database stays awake, and billed, after the last activity before it sleeps.
Physical meaning
An agency's database cost jumps from a few dollars to a few hundred one month. The bill shows the database never paused: someone set a dashboard to refresh every 15 minutes, all night. Moving the refresh to just after the morning load brings the bill back, with no loss of freshness.

“Serverless means you only pay when queries run.” You pay while the database is awake, which includes the auto-pause delay after each burst of activity. Frequent small refreshes keep it awake all day, and then serverless can cost more than provisioned.

Write a one-page runbook per environment: which identities exist and what they can do, where secrets live, how to restore, how to rotate a vendor key, budget and alert thresholds, and who owns it. It is dull, and it is exactly what a manager in a regulated business wants to see. In the README’s data-handling section, mention the privacy law that applies where you work (for example PIPEDA in Canada) and PCI DSS for card data: never store card numbers in an analytics warehouse; load tokens or totals instead.

Maths and principle

Principle: serverless bills compute only while awake; provisioned bills every hour. The break-even depends on how much of the month the database is awake.

Equation: with price per vCore-hour p, vCores v, and about 730 hours in a month:

Cserverless≈ps⋅v‾⋅hawake,Cprovisioned≈pp⋅v⋅730

Serverless wins when hawake/730<(ppv)/(psv‾). Each activity window adds its own length plus the auto-pause delay to hawake, unless windows overlap.

Analogy: a taxi against leasing a car. A taxi is cheap for one trip a day and expensive if you keep it waiting outside all day.

Where it breaks: frequent refreshes merge into one long awake period. Line them up with the loads.

Try it: a 1-hour load and 4 refreshes of 10 minutes, spread hours apart, with a 1-hour delay. Awake hours a day? (Load 1 + 1 = 2 h; each refresh 1/6 + 1 ≈ 1.17 h, × 4 ≈ 4.67 h; total ≈ 6.67 h a day, about 200 h in a 30-day month.)

6. Try it

  1. Create a Key Vault, store the fake ads API token, and read it from Python with DefaultAzureCredential (signed in with az login).
  2. Create the roles loader, transformer, report_reader and analyst. For each, test that a forbidden action fails (on the local container, use contained SQL users).
  3. Add RLS on mart.fct_ads_daily by client using sp_set_session_context. Test as two different client managers.
  4. Restore drill: in Azure, delete a table and do a point-in-time restore; on the local container, BACKUP DATABASE then RESTORE to a new name.
  5. Write docs/runbook.md and docs/cost.md (your estimate, the refresh schedule you chose, and a screenshot of the budget alert).

What to check: the two client managers see different totals from the same query; the restored copy has the deleted table; your cost note shows awake hours per day and how you reduced them. Commit as docs+feat(security): managed identity, Key Vault, roles, RLS, restore drill, cost note.

  • Managed identities remove passwords; Key Vault holds the secrets that must exist, read at run time.
  • Give each role only what it needs, and enforce per-client rows with database row-level security.
  • Automatic backups allow point-in-time restore to a new database; practise the restore before you need it.
  • Serverless bills while awake, including the auto-pause delay after every burst; align refreshes with loads.
  • Never store card numbers in an analytics warehouse; load tokens or totals.

Check yourself

1A Dagster VM in Azure needs to write to Azure SQL. What is the most secure setup?
2Power BI refreshes every 15 minutes against a serverless Azure SQL database with auto-pause. What happens to the cost?
3Where should card numbers be stored in your BI warehouse?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.