Compare Unique Records to Tickets

[DylanBlumenberg]

Hey everyone, I'm looking for some brainstorming help. I'll explain the situation.

We have an automated process that imports a CSV file with new hire data and stores it on an import set table. Duplicate new hires records may be loaded onto the table multiple times. Each new hire person has a unique ID.

Once an hour, a flow runs and queries the records on the import set table, looking for any created in the last 15 minutes. For each record found, the flow then takes one of two actions:

• Creates a new hire ticket if one does not already exist for that new hire.
• Updates an existing new hire ticket if one already exists and the new hire data has changed. 

When the flow creates a new hire ticket, it writes the unique new hire ID to the ticket, so that's what it uses to check if the new hire ticket exists or not.  

I have alerts set up if any step in the process fails, but to help me sleep at night, I'd like some sort of report to ensure that 1 unique new hire record correctly creates 1 unique new hire ticket, and none are missed for some reason. Basically, on a daily basis, when a totally unique record is created on the import set table, I want to ensure that a new hire ticket is created. This is where I could use some ideas. 

I've created scripted relationships between the two tables but I'm not finding a way to report on that. I've done some looking at identifying unique records but again, not finding a way to report on that. Does anyone have any other ideas?