Most identity sync jobs look fine until you ask them to remove something.

Our Keycloak federation flow pulled users from Microsoft Entra ID, mapped AD groups to application roles, and kept users up to date on a schedule. New users appeared. Existing users got refreshed. Group assignments were added. On paper, the sync was working.

Then we looked at the other side of the problem.

What happens when a user is removed from an AD group? What happens when someone leaves the organization? What happens when a user still has a manually granted application-level Admin role that should not be wiped by automation?

That is where the simple “read group, add users” model started to fall apart. The sync knew how to add access, but the removal path was either incomplete, risky, or too expensive to implement naively. We had to answer a harder question: for every synced user, what access still comes from AD, what access should be revoked, and what access must be protected because it was granted outside AD?

This talk walks through how we redesigned that part of the sync.

We will start with the failure mode: group-by-group AD sync, stale Keycloak users, and roles that drift over time. Then we will show the design we moved toward: treating each sync run as a small data pipeline. We extract group membership from Microsoft Graph, build a temporary membership view, compute the difference between “what AD says now” and “what Keycloak currently has, and apply changes back in controlled batches.

A key part of the solution is using DuckDB as an embedded analytical layer for the heavy set operations. Instead of loading every user-group relationship into application objects and hoping memory holds, we let DuckDB handle joins, deduplication, and delta queries with bounded memory and spill-to-disk support.

The security problem is just as important as the data problem. Some roles are AD-managed and should be removed when AD removes them. Other roles are granted manually through the application and should persist through the next sync. We will discuss how to draw that boundary, how to avoid deleting protected assignments, and how to make deprovisioning predictable rather than accidental.

This is a practical talk about identity sync as a data architecture problem: not just “call the directory API”, but design the remove path, make ownership explicit, and keep the sync safe when the dataset grows.

Key Takeaways:

- Why does add-only AD sync create ghost users, stale roles, and access drift?
- How to model a sync run as a data pipeline: extract from Microsoft Graph, transform membership data, and load changes into Keycloak.
- How DuckDB can help compute user/group deltas without building large in-memory maps in application code.
- How to separate AD-managed access from manually granted or protected roles.
- Practical failure cases to handle: partial Graph failures, orphan users, batching, retries, and safe de-provisioning.

Intended Audience:
Intermediate backend, platform, IAM, and infrastructure engineers.

This talk is useful for anyone working on identity sync, directory integration, user provisioning, access control, or large relationship-heavy data updates where correctness matters as much as performance.

Technologies Used:
Microsoft Entra ID / Azure AD
Microsoft Graph API
Keycloak User Federation
DuckDB
Java