Onboarding a Read-Only HR System with 38,000 Accounts
A 38,000-account JDBC source backs a homegrown HR system of record. The connector is read-only SQL, has no native disable, and has no last-modified column; every nightly aggregation is a full table scan taking eight to nine hours, finishing after the 6 a.m. identity refresh and overlapping the morning provisioning window. Correlation is by employee ID. About 400 records - contractors entered before their ID is issued - have a null employee ID, and several share a single departmental mailbox as their email. HR retains terminated rows for seven years for audit and will not permit deletes. The business wants terminated contractors to lose access the same day.
1. Design the termination path given that the source supports neither disable nor delete.
Read the worked answer
Decision: make the source look like a delete, but only through a controlled view - do not let IIQ delete identities.
Options and why the choice falls where it does:
- Native disable - unavailable; no writable status column.
- Ask HR to expose an
enabledflag - the correct long-term fix, but the business wants same-day revocation now. Raise it as a change request in parallel. - Filter terminated rows out of the aggregation query (a database view with
WHERE status <> 'TERMINATED'). IIQ then sees those accounts as absent and runs its account-deletion logic per the connector's configured delete behaviour. This preserves HR's audit rows because the deletion is logical, not physical.
The trap: on an authoritative source, account deletion and identity deletion are different switches. Configure the connector so the account is removed/disabled and the identity is marked terminated without deleting the identity record, otherwise you lose the history audit cares about and break certifications that reference past access.
Also confirm the disable-on-termination reaches the downstream targets (AD, ERP) rather than only the IIQ link - the goal is access removal, not a clean aggregation graph.
2. Propose how to cut the nine-hour aggregation without a schema change from the HR team.
Read the worked answer
Decision: partition the full aggregation first; pursue delta only as a follow-up with a proper watermark.
- Partitioning splits the read into N concurrent tasks against disjoint key ranges (for example, four partitions on a numeric employee key). It needs no source change and cuts wall-clock time roughly linearly until the database or the app server saturates. Requirements: a stable, evenly distributed, indexed key, and a composite task that fails cleanly if one partition dies.
- Delta aggregation is better long term but needs a reliable last-modified column and a soft-delete flag. Without the soft-delete flag, a termination is invisible to a delta run, so the account never disappears and the same-day revocation promise silently breaks. Treat the delta column as a change request to HR, not a quick win.
- Scheduling is the cheapest lever: move aggregation earlier or move the identity refresh after it, so the overlap with the provisioning window disappears even before performance improves.
Measure per-partition timings and check the query plan; if the scan dominates, partitioning divides it. If the JDBC connection pool or the database CPU is the ceiling, more partitions make it worse - size partitions to the bottleneck, not to the row count.
3. The 400 contractors with null employee IDs are failing to correlate. What correlation strategy do you deploy, and what do you refuse to do?
Read the worked answer
Deploy a layered rule; refuse name- or email-only matching.
- Primary: employee ID when present.
- Secondary, for the 400: a contractor requisition or badge ID that is unique per person. Validate uniqueness against the current identity set before enabling the rule - a secondary key that is reused across rehires will merge two people and that error is expensive to unwind (entitlements, certifications, audit trail).
- Explicitly reject email fallback for this population. Several contractors share a departmental mailbox; correlating on it would attach multiple human beings to one identity.
- Explicitly reject display-name matching. It looks like it fixes the 400 and actually creates silent merges that only surface at termination, when two people's access is revoked together.
For the residual unmatched accounts, do not let them disappear: route them to an exception report reviewed daily, and add a scheduled task that counts uncorrelated accounts and alerts above a threshold. A correlation rule that matches "most" records without a visible unmatched count is a control failure waiting for an audit.