LAUSD Identity Migration: 250K+ Records to Ping AIC

How the sync worked

Snowflake held the authoritative student and staff records. The migration provisioned these identities from Snowflake into Ping Advanced Identity Cloud while maintaing live bidirectional synchronization changes in Snowflake flowed into Ping AIC, and changes originating in Ping AIC are written back to Snowflake.

Nothing interacted with Snowflake directly. Ping AIC reaches external data stores through a Remote Connector Server that hosted a Database Table Connector providing JDBC connectivity to Snowflake. In Ping AIC I configured the attribute mappings correlation rules and reconciliation that kept the systems aligned. The connector layer exposed an interesting failure I traced it through logs and external research to Snowflake JDBC's unsupported generated key retrival behavior. Instead of abandoning the writeback flow I reviewd the source code for the connector and identified a targeted patch that would allow for flow to work.

What went wrong

Synchronization jobs failed on a recurring basis, and always on the same class of operation.

The connector was written generically, against JDBC rather than against any particular database. When it prepared a statement, it asked the driver to return generated keys — the pattern you want after an INSERT, when you need the identifier the database just assigned. It applied that request to every operation, including UPDATE and DELETE.

Most drivers tolerate this. Snowflake's does not: it rejects a generated-keys request on operations where the concept does not apply, and the Snowflake objects in play were views, which are not directly mutable in the way the connector assumed. Every update and delete the connector attempted came back as a driver-level failure. Inserts were fine, which is why the problem read at first like an intermittent sync bug rather than a categorical one.

So: a generic connector abstraction, a database-specific incompatibility underneath it, and a production migration that could not proceed.

What I changed

The fix was in the connector's statement-creation path. Simplified, the behavior before:

// Generated keys requested for every operation, regardless of statement type.
PreparedStatement stmt =
    conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

And after:

// Only ask for generated keys where the operation — and the driver — support them.
PreparedStatement stmt = (operation == Operation.INSERT)
    ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
    : conn.prepareStatement(sql);

That is a reconstruction of the shape of the change, not the client's code, but it is the whole idea: make the generated-keys request conditional on the operation instead of unconditional.

Getting there was the actual work. I reproduced the failure in isolation against a Snowflake instance so I could iterate without touching district data, then followed a failing update from the synchronization job, through the connector's operation handling, down to the specific prepareStatement call that carried the flag. The driver error named the constraint clearly once I was reading it at the right layer; the difficulty was that the same connector had worked fine against other databases, so the connector itself was not where anyone was looking.

Testing the patch meant covering the operations that had been failing and the ones that had not: inserts still needed their generated keys, updates and deletes needed to succeed without them, and write-backs needed to round-trip. I validated against test cohorts before anything touched the real population.

How it shipped

Diagnose. Reproduce the failure, then trace it from the sync job through the connector to the JDBC call rather than guessing from the job-level error.

Patch. Make the generated-keys request conditional on operation type, and confirm the change doesn't regress the insert path that depended on it.

Validate. Exercise schema mappings, reconciliation behavior, updates, deletes, and writebacks against cohorts of increasing size small enough to inspect by hand at first.

Release. Roll bidirectional sync out incrementally. With no downtime tolerance during the school year, migrating 250,000+ identities in one cutover was not an acceptable risk.

Operate. Add logging and alerting around synchronization faults, so a failure announces itself instead of being discovered by someone querying the database the next morning.

Why this project matters to me

It was my first professional project that I got to own the implementation end to end. It was incredible for my confidence as an engineer. Getting the trust from my team to handle a core part of system with minimal hand holding. Going through development cycle from understanding the requirements, implementation and production delivery helped me believe in myself more. It helped me put into perspective how far I have come an engineer. After working on this project I knew I was capable of handling more complex and impactful work. It was the point where I started to fell less like a junior engineer and accelerated personal development I gained the confidence to take on more challenging and broader scoped work.