Pipeline to Dashboardnihalsulaiman.com

Week 4 · Load to the warehouse › Day 27

Day 27. Security, cost and operations in Azure

⏱ 4h · learn 70m · lab 130m · ship 25m · review 15m🏷 marketing agencyBefore: Day 26

By the end of today you can

  • Run pipelines with managed identities and Key Vault instead of stored secrets
  • Apply least privilege, row-level security and auditing
  • Know backups, point-in-time restore and what they cost
  • Estimate and cap Azure SQL cost (serverless vs provisioned, auto-pause)

1. Why this matters

At a real company, data people are trusted with customer, revenue and payroll data. A portfolio that shows you handle identity, secrets, access and cost the way a security or finance team expects answers questions before they're asked.

2. Identity and secrets

  • Managed identity: an Azure resource (a VM, Container App, Function, Data Factory) gets an Entra identity. Grant that identity a database user (create user [vm-p2d-dagster] from external provider). The code uses DefaultAzureCredential. No password exists.
  • Key Vault: for secrets that must exist (vendor SFTP keys, API tokens). The pipeline's managed identity gets Key Vault Secrets User; code reads with SecretClient. Rotation 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 log it

3. Access inside the database

  • Roles per purpose: loader (write raw/stg/ops), transformer (dbt: write mart), report_reader (read mart), analyst (read stg + mart).
  • Row-level security (RLS): a security predicate function filters rows by user, so a client manager sees only their client's campaigns, enforced in the database. Power BI also has its own RLS (Week 6); for DirectQuery and shared databases, database RLS is the stronger guarantee.
  • Dynamic data masking hides columns like emails from non-privileged users.
  • Auditing to a storage account or Log Analytics: who queried what, 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);

4. Backups and restore

Azure SQL Database takes automatic backups with point-in-time restore (PITR) within a retention window (default 7 days, configurable), plus optional long-term retention. Restore creates a new database. Practise it once: restore to "10 minutes before I dropped the table" and compare.

5. Cost

Maths and principle

Principle: serverless bills for compute only while active; provisioned bills every hour. The break-even is the share of time the database is busy.

Equation: monthly compute \(\approx\) vCore-hours used × price per vCore-hour. Serverless with auto-pause: \(C_s = p_s \cdot \bar v \cdot h_{\text{active}}\). Provisioned: \(C_p = p_p \cdot v \cdot 730\). Serverless wins when \(h_{\text{active}}/730 < (p_p v)/(p_s \bar v)\). Serverless unit prices are higher per vCore-hour, so a database busy most of the day is cheaper provisioned.

Analogy: a taxi vs leasing a car. Where it breaks: Power BI scheduled refreshes every 30 minutes keep a serverless database awake all day. Align refreshes to load times.

Try it: a pipeline loads for 1 h/day and Power BI refreshes 4 times for 10 min each. Active hours/month (ignoring the auto-pause delay)? (≈ 1.67 h/day × 30 ≈ 50 h; add the minimum auto-pause delay after each activity window to be realistic.)

Practical cost controls: a budget with alerts on the resource group; auto-pause delay at the minimum that suits refreshes; turn off unused geo-replication; check storage growth monthly; use the free offer for the portfolio.

The 5-year lens

Write a one-page runbook per environment: identities and what they can do, where secrets live, how to restore, how to rotate a vendor key, budget and alert thresholds, who owns it. It's dull, and it's exactly what a hiring manager in a regulated company (hospitality payments, retail loyalty data) wants to see you think about. Mention PIPEDA (Canada's private-sector privacy law) and PCI DSS (card data: never store card numbers in your warehouse) in the README's data-handling section.

Lab

  1. Create a Key Vault, store the fake ads API token, read it from Python with DefaultAzureCredential (signed in with az login).
  2. Create roles loader, transformer, report_reader, analyst. Test each with a user (or contained SQL users on the local container) that a forbidden action fails.
  3. Implement RLS on mart.fct_ads_daily by client using sp_set_session_context. Test as two "client managers".
  4. Do a point-in-time restore after deleting a table (Azure), or a backup/restore cycle with BACKUP DATABASE/RESTORE (local container).
  5. Write docs/runbook.md and docs/cost.md (your estimate + the budget alert screenshot).

Ship

docs+feat(security): managed identity, Key Vault, roles, RLS, restore drill, cost note.

Check yourself

1. A Dagster VM in Azure needs to write to Azure SQL. Most secure setup?

2. Power BI refreshes every 15 minutes against a serverless Azure SQL database with auto-pause. What happens to cost?

3. Where should card numbers be stored in your BI warehouse?

My notes