Week 4 · Load to the warehouse › Day 23

Day 23. Azure SQL Database, hands on

1. Why this matters

This is #TODO item 1 in your own plan: real pipelines writing to Azure SQL. Today you build the connection layer every later load uses, and you do it the way a security team would approve.

2. Create the database

Option A: Azure. In the Azure portal create a resource group rg-p2d, then Azure SQL Database. Microsoft offers a free Azure SQL Database offer (a General Purpose serverless database with a monthly allowance of vCore-seconds and storage; check the current terms in the portal when you create it). Choose:

• Authentication: Microsoft Entra only or Entra + SQL. Set yourself as Entra admin.
• Networking: public endpoint, allow your client IP only. Don't tick "allow all Azure services" unless you need it.
• Compute: serverless with auto-pause so it costs nothing while idle. Set a cost alert in Cost Management on day one.

Option B: local, free, offline. The same engine in Docker:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Str0ng!Passw0rd" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest

Everything in this week works on both. Use B for heavy experiments; use A for the portfolio screenshot and Entra auth.

3. Connect from Python

Install the Microsoft ODBC Driver 18 for SQL Server (Windows installer, or apt/brew per Microsoft docs), then:

import struct, urllib.parse
import sqlalchemy as sa
from azure.identity import DefaultAzureCredential

SERVER, DB = "p2d-sql.database.windows.net", "p2d"
odbc = (f"Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{SERVER},1433;Database={DB};"
        "Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30")

def entra_token_connect_args():
    tok = DefaultAzureCredential().get_token("https://database.windows.net/.default").token.encode("utf-16-le")
    return {"attrs_before": {1256: struct.pack(f"<I{len(tok)}s", len(tok), tok)}}  # SQL_COPT_SS_ACCESS_TOKEN

engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={urllib.parse.quote_plus(odbc)}",
                          connect_args=entra_token_connect_args(), fast_executemany=True, pool_pre_ping=True)

DefaultAzureCredential uses your az login locally and a managed identity in Azure (Day 27), so there's no password anywhere. For the local container, use a SQL login in the connection string from .env, with TrustServerCertificate=yes (local only).

Microsoft also ships a newer native Python driver (mssql-python); check its status before choosing it for production.

4. Schemas and users

create schema raw;   create schema stg;   create schema mart;   create schema ops;   -- ops = audit, watermarks

-- a loader identity that can write raw/stg/ops but not drop the database
create user [p2d-loader] from external provider;          -- Entra app or managed identity
-- (local container: create login p2d_loader with password = '...'; create user p2d_loader for login p2d_loader;)
grant select, insert, update, delete, alter on schema::raw to [p2d-loader];
grant select, insert, update, delete, alter on schema::stg to [p2d-loader];
grant select, insert, update on schema::ops to [p2d-loader];

-- a reader for Power BI
create role report_reader;
grant select on schema::mart to report_reader;

5. Loading fast