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.
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.
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 usesDefaultAzureCredentialfrom 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.cominstead 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.
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 , vCores , and about 730 hours in a month:
Serverless wins when . Each activity window adds its own length plus the auto-pause delay to , 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
- Create a Key Vault, store the fake ads API token, and read it from Python with
DefaultAzureCredential(signed in withaz login). - Create the roles
loader,transformer,report_readerandanalyst. For each, test that a forbidden action fails (on the local container, use contained SQL users). - Add RLS on
mart.fct_ads_dailyby client usingsp_set_session_context. Test as two different client managers. - Restore drill: in Azure, delete a table and do a point-in-time restore; on the local container,
BACKUP DATABASEthenRESTOREto a new name. - Write
docs/runbook.mdanddocs/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
Sources
- Microsoft Learn: What are managed identities for Azure resources? https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview
- Microsoft Learn: Row-level security https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security
- Microsoft Learn: Serverless compute tier for Azure SQL Database https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
- Microsoft Learn: Restore a database from a backup (point-in-time restore) https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
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.