Appian ACD301: The Duplicate Applicant Record

The Duplicate Applicant Record

A licensing office stores applicants in a record type Applicant with a unique text field TaxId. During a bulk import of 40,000 rows, the team discovers that 1,200 rows share TaxIds with existing records, and 300 of those TaxIds appear twice within the import file itself. The import runs as a process with a Write to Data Store Entity node. The current data store entity has no error handling configured, so the whole batch fails when a duplicate is hit. Downstream, Application records reference Applicant by a many-to-one relationship, and case workers have already created 5,000 applications against applicants that exist today. Leadership wants the import completed today without creating duplicate applicants and without orphaning any existing application.

1. How should the team structure the import so that existing applicants are matched and updated rather than duplicated, and how should in-file duplicates be handled before the write?

Read the worked answer

The reliable approach is to split the load into a staging record type and a reconciliation step, not to point the Write node directly at Applicant.

Import all 40,000 rows into a staging record type with no uniqueness constraints. This guarantees the load itself never fails.
Run an expression or query to group staging rows by TaxId and flag any TaxId with a count > 1. For the 300 in-file duplicates, apply an explicit business rule - typically keep the most recent row by a source timestamp and discard the rest, logging the discarded rows. This decision must be made explicitly; silently picking one is a data-quality risk.
For the remaining rows, query Applicant by TaxId in batches (never one query per row) to determine match vs. new.
Use update-if-exists / insert-if-new logic keyed on TaxId, then delete or archive the staging rows.

Because Application references Applicant by record ID, updating existing applicants is essential - inserting new applicants for the 1,200 matches would leave the 5,000 existing applications pointing at the old records while new applications point at the new ones, splitting the same person across two records.

2. The Write to Data Store Entity node is currently failing the entire batch on the first duplicate. What configuration change addresses this, and what is the trade-off?

Read the worked answer

The node should be configured so that individual row failures do not abort the whole transaction - the data store entity's write behavior needs to tolerate errors per row and report them, rather than failing the batch.

The trade-off is important: allowing partial success means the process can no longer assume "all 40,000 rows landed." The team must therefore:

Capture the per-row error output from the write node.
Route failed rows to a quarantine table or a process error log with the reason.
Reconcile counts at the end (rows attempted = inserted + updated + rejected) and fail the process loudly if the numbers don't balance.

Tolerating errors without reconciliation just converts a loud failure into a silent one, which is worse. The correct pattern is tolerant write + mandatory count reconciliation.

3. After the import, a query on `Applicant` by `TaxId` becomes slow. What is the most likely cause given the record type design, and what should be checked first?

Read the worked answer

The first thing to check is whether TaxId is actually indexed and whether the uniqueness constraint is enforced at the database level. A field marked unique in the record type designer is not automatically a fast lookup key unless the underlying column is indexed.

Second, check whether the new 40,000 rows introduced a large number of null or placeholder TaxId values (common when rejected rows are written with a blank key). Nulls in an indexed column can skew query plans.

Third, confirm the query is filtering on the indexed field rather than applying a filter after retrieval - a query that pulls all applicants and filters in an expression will not benefit from any index.

The order matters: verify the index exists before considering partitioning or archiving, because adding infrastructure to an unindexed column solves nothing.