Office 365 → Snowflake user sync (Snowflake-native Python stored procedure)

Built a Snowflake-native Python stored procedure that pulls filtered user data from Microsoft Graph and lands it inside the Snowflake warehouse on a daily schedule. The entire pipeline runs inside Snowflake's Python runtime: no external server, no managed Python environment, no external scheduler, no external credential store. Just a stored procedure, a Snowflake Task, a Network Rule, and an EXTERNAL ACCESS INTEGRATION.

Right-tool-for-the-job version of what would normally be a Python-on-a-server (or Azure Function, or Logic App) integration job. The data needs to end up in Snowflake anyway; running the ingestion inside Snowflake eliminates an entire class of infrastructure overhead.

What it does

Each daily run pulls filtered users from Microsoft Graph (transitive group members of a designated sync group, so the scope is "everyone in a specific Entra ID group, including users picked up via nested group membership") with these attributes:

• Core identity fields: UPN, mail, display name, given name, surname, job title, department, office location, mobile, business phones.
• Manager ID and manager display name (resolved via a secondary Graph call per user).
• 15 extension attributes (the custom extensionAttribute1 through extensionAttribute15 properties used for region, division, market segment, distribution partner, and other taxonomy values per the naming standards).
• Account status (enabled, disabled, deletion-tracked).

Result lands in a Snowflake table via a MERGE statement that supports incremental updates: new users are inserted, existing users have changed attributes updated in place, and users removed from Entra ID are soft-deleted (a deleted_at timestamp is set rather than the row being purged), so downstream consumers can still see historical employee data for prior-period reporting without breaking referential integrity.

Setup components

• Entra ID app registration with the appropriate Microsoft Graph application permissions (User.Read.All, Group.Read.All, plus directory read for extension attributes). Client credentials flow.
• Snowflake Secret holding the Entra ID client ID / tenant ID / client secret. Read by the stored procedure at runtime; not exposed to consumers.
• Snowflake Network Rule allowing outbound HTTPS to login.microsoftonline.com and graph.microsoft.com.
• Snowflake EXTERNAL ACCESS INTEGRATION binding the Network Rule and Secret so the stored procedure can make authenticated outbound calls.
• Snowflake Task triggering the stored procedure daily. Snowflake's scheduler, not Windows Task Scheduler, not Azure Functions, not cron.
• Target table with the schema for the captured attributes plus created_at, updated_at, deleted_at, and audit columns.

What this eliminated

The natural way to build this integration would have been a Python script running on a server somewhere (Azure VM, on-prem box, Azure Function, AWS Lambda) calling Microsoft Graph and pushing the data into Snowflake via the Snowflake connector. That design carries a lot of operational tail.

• Where does the server run? Compute hosting, patching, monitoring. → The Python runtime is inside Snowflake; no host to manage.
• Python environment management. requirements.txt, version pinning, dependency drift. → Snowflake's Python runtime ships with the standard library and approved packages; dependencies declared in the procedure definition.
• Where does the schedule live? cron, Task Scheduler, Azure Function timer trigger. → Snowflake Task on the Snowflake account itself.
• Where do credentials live? Bitwarden + env vars + delivery into the host. → Snowflake Secret, read inside the stored procedure, never leaves the account.
• Network egress. Firewall rules from the host's IP range to Graph. → Snowflake's EXTERNAL ACCESS INTEGRATION with a Network Rule scoped to the Graph endpoints.
• Failure observability. External logging, alerting, retry orchestration. → Snowflake Task history, query history, and procedure logging on the Snowflake console.
• Onboarding new analysts. "Here is the server, here is the SSH key, here is the venv..." → "Here is the schema. The user table is fresh as of this morning."

For an integration whose output target was already Snowflake, the simplification was substantial.

What it enables

The user dimension table fed by this sync is the identity backbone for the broader Snowflake / Fabric analytics estate:

• Row-Level Security policies that filter customer-facing datasets by user attributes (region, division, manufacturer assignment) rely on a fresh per-user attribute set.
• Dynamic Entra ID groups (used for license assignment, Conditional Access scoping, Intune targeting, distribution lists, Fabric workspace access) all rely on the same underlying attribute taxonomy. Having that taxonomy queryable inside Snowflake means analytics and identity speak the same language.
• BI joins (ticket data joined to user, sales data joined to rep, manufacturer data joined to AG market segment) all anchor on the canonical user dimension.
• Historical employee reporting (who was in what role at what time) works because the soft-delete pattern preserves history rather than mutating away the past.

What this demonstrates

• Architectural judgment Choosing the Snowflake-native path eliminated an entire infrastructure layer rather than just building "the obvious thing." Most IT shops build this as a Python-on-a-server job; building it inside Snowflake is the more sophisticated answer when Snowflake is already the target.
• Cross-domain integration competence Microsoft Graph (identity), OAuth client credentials flow, Snowflake stored procedures, Snowflake Tasks, EXTERNAL ACCESS INTEGRATION, and SQL MERGE logic all in one project.
• Operational discipline Soft-delete tracking instead of destructive deletion, audit columns on every row, scoped Network Rule (not "outbound everywhere"), credentials in Snowflake Secret (not in code).
• Documentation-as-code instinct The setup is fully documented (Entra ID app registration, Snowflake Network Rule, EXTERNAL ACCESS INTEGRATION) so the next person can rebuild from the docs rather than reverse-engineer from the running system.
• Identity-as-the-substrate thinking The integration treats the Entra ID Department / extension-attribute taxonomy as the load-bearing input, consistent with the broader Darwinbox HRIS integration coming next.