Sync records for a record created from materialized view

Question

Issue with Dynamic Identifiers in Materialized View Causing Sync Failures 
 We are consuming data from an external view that only returns records where: 
 
 BPM_PROCESSING_STATUS IS NULL 
 BPM_CASEID IS NULL 
 
 This external view is built on top of a base table that contains all records. 
 
 Current Implementation

When a case is created in Appian, we update the base table using a Query Database Smart Service , populating: 
 
 BPM_CASEID 
 BPM_PROCESSING_STATUS (e.g., PENDINGFORAPPROVAL, REFERREDBACK, PROCESSED )

Since we do not have access to define a primary key on the source table/view (due to grant restrictions), we: 
 
 Created a Materialized View on top of the external view 
 Added a synthetic identifier column:

SQL

1 
 ROW_NUMBER() OVER (ORDER BY UNIQUE_ID) AS ID 
 
 Show more lines

Built an Appian Record Type using this materialized view (to leverage record filters, etc.) 
 Created a stored procedure to refresh the materialized view 
 Used:
 
 Execute Stored Procedure Smart Service 
 Sync Records Smart Service

Problem 
 The identifier ( ROW_NUMBER ) is not stable . 
 
 Initially, suppose we have 3000345 records 
 When cases are created for some UINs, those records are updated in the base table 
 As a result, they no longer satisfy the condition ( BPM_CASEID IS NULL , BPM_PROCESSING_STATUS IS NULL ) 
 Hence, they disappear from the external view and the materialized view 
 
 Example: 
 
 Record count reduces from 3000345 &rarr; 3000335 
 Previously generated IDs such as:
 
 3000271 , 3000313 , 3000341

After refresh:
 
 Some of these IDs (e.g., 3000341 ) no longer exist

Impact 
 
 The Record Type identifier changes dynamically 
 When we run Sync Records , Appian attempts to sync using old identifiers 
 Since those IDs no longer exist, sync fails

Attempted Workaround 
 
 Tried using Delete Record Smart Service 
 However, deletion is not allowed since the source is a materialized view

Key Concern 
 We need a way to: 
 
 Refresh the record data immediately 
 Ensure users do not create duplicate cases for the same UIN

Question 
 Is there a recommended approach in Appian to handle: 
 
 Dynamic datasets where records disappear after updates 
 Unstable identifiers (ROW_NUMBER-based) 
 Immediate refresh/sync of record data 
 Preventing duplicate case creation for the same UIN

Shubham Aware · Answer

Use a stable source key (UNIQUE_ID/UIN) as the record identifier - ROW_NUMBER() changes on every refresh, which is why sync fails. Appian only needs the key to be unique, non-null, and stable, not a real primary key.
Keep all rows in the source and add a status column to mark them processed instead of filtering them out.
Use record filters to hide processed rows. This stops records from disappearing, so sync never references missing Ids and avoids Delete Records, which can't run on a materialized view.
Enforce UIN uniqueness in the database or stored procedure, not in Appian sync logic.