Start With Follow-Up Questions (Very Important in Interviews)

Before jumping into the solution, I would ask clarifying questions such as:

1. Where is the user–role–organization mapping stored?

Example: Is it stored in a table like USER_ROLE_ORG with columns: USER_ID, ROLE_ID, ORG_ID, IS_ACTIVE?

1. Can a third-party user belong to multiple orgs or groups?
2. Is there an audit requirement or rollback requirement?
3. Should the users be permanently deleted or only deactivated?

These questions help confirm assumptions and avoid designing the wrong solution.

Database Cleanup – Deactivating Third-Party Users

Assuming we have a table like:

And ROLE_ID = 3 represents third-party users.

Approach 1: Direct SQL Update

A simple solution is:

But this has issues:

1. We don’t know exactly who was deactivated.
2. There may be 10,000+ users, so traceability is important.
3. If something goes wrong, rollback is difficult.

Better Approach: Use Audit + Stored Procedure

I would design a stored procedure with the following steps:

1. Identify the users to be deactivated
2. Save them in a TEMP_USER_LIST.
3. Insert the same list into an AUDIT_USER_DEACTIVATION table
4. USER_ID
5. ROLE_ID
6. ORG_ID
7. DEACTIVATION_TIME
8. IS_COMPLETED (0/1) ← for tracking Appian side cleanup later
9. Perform SQL update
10. Mark users as inactive in the main table.

This covers database-level cleanup with full traceability.

Appian-Side Cleanup — Removing Users From Third-Party Groups

After DB deactivation, we still need to remove the users from Appian Groups.

Easy approach create an MNI that loops through 10,000 users.This will create 10,000 instances → massive load → difficult to track failures.

Scalable Appian Design — Batch Processing + Recursive Execution

Batch Removal Using Sub-Process

1. Create an expression rule that fetches only users with IS_COMPLETED = 0 from audit.
2. Use a Process Model with:
3. Batch size (e.g., 100 users at a time)
4. Loop or recursive chaining until all users are processed

High-Level PM Flow:

1. Node 1 – Query Audit Table
2. Fetch up to 100 records (batch size).
3. Node 2 – MNI Sub-Process (100 max)
4. Each instance:
5. Removes the user from the Third-Party Group using Smart Service
6. Updates IS_COMPLETED = 1 in the audit table
7. Node 3 – Timer Event
8. Add 1–2 min cooldown to reduce load.
9. Node 4 – Decision Node
10. If any users are still IS_COMPLETED = 0,
11. → Call same process again (recursive call).

This produces:

1. Efficient processing
2. No huge MNI bursts
3. Audit tracking
4. Restart capability if anything fails
5. Safe and scalable for 10,000+ records

Tips for Interview:

Interviewers do not expect a perfect solution.

They check your ability to:

1. ask the right clarifying questions
2. understand data models
3. design a safe, trackable, scalable solution
4. explain your approach clearly

As long as your logic is structured and scalable, you will stand out.