All lessons

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

Azure SQL Database, hands on

Create an Azure SQL database (or a free local SQL Server container), connect from Python with Microsoft Entra tokens and no passwords, set up schemas and roles, and bulk-load fast.

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

Module 4 · about 40 minutes · read first: 4.1. You’ll be able to create an Azure SQL database safely (or run the same engine locally for free), connect from Python without a password, organise it into raw, staging and mart schemas with least-privilege users, and load rows fast.

Connecting to a warehouse the safe way means showing an ID badge, not knowing the door code: the pipeline proves who it is with a token Azure gives it, and each badge opens only the rooms it needs.

1. Why this matters

Most companies that use Power BI and Microsoft 365 put their warehouse in Azure SQL or SQL Server. Today you build the connection layer every later load in this course uses, and you build it the way a security team would approve.

2. Create the database

Option A: Azure. In the Azure portal create a resource group (a folder for related resources), for example rg-p2d, then an Azure SQL Database. Microsoft offers a free Azure SQL Database offer with a monthly allowance; the terms change, so read them in the portal as you create it. Choose:

  • Authentication: Microsoft Entra only (Entra is Microsoft’s sign-in service, formerly Azure Active Directory), or Entra plus SQL logins. Make yourself the Entra admin.
  • Networking: public endpoint, allow your own IP address only. Leave “allow Azure services” off unless you need it.
  • Compute: serverless with auto-pause, so it costs nothing while idle. On day one, set a budget alert in Cost Management.

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 module works on both. Use B for heavy experiments; use A for Entra sign-in and your portfolio screenshots. (The password above is for a throwaway local container only.)

3. Connect from Python

Install the Microsoft ODBC Driver 18 for SQL Server (follow Microsoft’s install page for your system), 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)}}  # 1256 = 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 tries several ways to prove who you are. On your laptop it uses your az login. In Azure it uses a managed identity (an identity Azure gives a VM or container, covered in Lesson 4.6). So there is no password anywhere. Tokens expire after a while; for long-running services, create the token when each connection opens (SQLAlchemy’s do_connect event is the usual place).

For the local container, use a SQL login read from .env, with TrustServerCertificate=yes (local only, never in production). Microsoft also has a newer native Python driver, mssql-python; check its status before choosing it for production.

4. Schemas and users

A schema is a named folder of tables inside a database. Four folders keep the layers apart:

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

-- a loader that can write raw, stg and ops, but cannot drop the database
create user [p2d-loader] from external provider;          -- an 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;

This is least privilege: each identity can do exactly its job and nothing more. Like keys in a hotel: a guest’s card opens one room, a cleaner’s opens one floor, and only the manager’s opens the safe.

5. Loading fast

Method Speed When
df.to_sql(...) with fast_executemany=True Good, up to around a million rows Daily loads
executemany without fast mode Very slow: one round trip per row Never for bulk
bcp command-line tool Fast Big one-off loads from CSV
BULK INSERT or OPENROWSET from Azure Blob storage Fastest in Azure Files already in storage
dlt with its SQL Server destination Good, plus schema changes handled If you use dlt

The reason is the network. Every round trip (send a request, wait for the answer) takes time, often 10 to 50 ms to a cloud database. Row by row, that waiting adds up to hours.

The job. Load 100,000 rows of ad stats. Each round trip to Azure SQL takes 30 ms.
Row by row. 100,000 trips × 30 ms = 3,000 s, about 50 minutes of pure waiting, before the database has done any real work.
Batches of 10,000. fast_executemany sends rows as arrays: 10 trips × 30 ms = 0.3 s of waiting.
The rest. Now the data's size and the database's write speed dominate, which is what you want. Batches that are too big hit memory and log limits, so 10,000 to 100,000 rows is a good range to tune in.
Entra token
A short-lived pass from Microsoft's sign-in service that proves who the pipeline is, instead of a password.
Managed identity
An identity Azure gives a VM, container or function, so code running there can get tokens with no stored secret.
Schema
A named folder of tables inside one database: raw, stg, mart, ops.
Round trip
One request to the database and its answer. Fewer, bigger trips load faster.
Physical meaning
A security reviewer asks "where is the database password stored?" and the answer is "there isn't one: the pipeline's VM has a managed identity that can write only to raw, stg and ops". That one sentence often decides whether your pipeline is allowed near customer data.

“A strong admin password in a config file is secure enough.” One shared admin login means one leak exposes everything, and nobody can tell which job did what. Security teams say yes to Entra-only sign-in, managed identities, IP-restricted or private network access, one least-privilege user per job, and auditing turned on. Build it that way, and say so in your README.

Maths and principle

Principle: network round trips dominate small-batch loading. Batching turns N trips into N/b.

Equation: for N rows of s bytes in batches of b, with round-trip time RTT:

T≈Nb⋅RTT+N⋅sbandwidth

Row by row (b=1) at 30 ms for 100,000 rows: 3,000 s. Batches of 10,000: 0.3 s of waiting plus the transfer.

Analogy: moving house with one van trip instead of one trip per box.

Where it breaks: huge batches hit memory and transaction-log limits. Tune b between about 10,000 and 100,000 rows.

Try it: 500,000 rows, RTT 20 ms, b = 5,000. Waiting time? (100 trips × 20 ms = 2 s.)

6. Try it

  1. Create the database (option A, B, or both). If A, set a budget alert straight away.
  2. Build warehouse/azure_sql.py: an engine factory that uses an Entra token (A) or a SQL login (B), chosen by a setting.
  3. Create the four schemas, a loader user and a reader role.
  4. Load raw.ads_daily from your staging Parquet with to_sql and fast_executemany. Time it at 10,000, 100,000 and 1,000,000 rows (repeat rows to scale up). Then time 10,000 rows without fast mode. Put the four timings in a table.
  5. Create ops.load_audit(load_id, asset, partition_key, rows, started_at, finished_at, status) and insert one row per load.

What to check: connecting as the reader and running insert into mart... fails with a permission error; grep -ri password src/ finds no real secret; the timing table shows fast mode many times faster than plain mode. Commit as feat(warehouse): Azure SQL connection with Entra tokens, schemas, roles, timed bulk load.

  • Run the same engine two ways: Azure SQL (serverless, auto-pause, budget alert) or a free local SQL Server container.
  • Connect with Microsoft Entra tokens via DefaultAzureCredential: your az login locally, a managed identity in Azure, no password anywhere.
  • Split the database into raw, stg, mart and ops schemas, and give each identity only the rights its job needs.
  • Batch your inserts: round trips dominate, and fast_executemany turns hours of waiting into seconds.

Check yourself

1Which way of connecting avoids storing any password for a pipeline running in Azure?
2Loading 200,000 rows with pyodbc executemany without fast_executemany is slow because...
3What permission should the Power BI reader have?

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.