CRM System – How to Approach User Acceptance Testing for Merged Constituent Records

duplicationmerge

I've been Googling to read up and understand what UATs are and how to conduct them, but I'm not quite understanding how to apply it in this scenario.

My acceptance criteria would be that all historic data from duplicate profiles that are transactions, interactions, and custom fields like booleans and strings get merged into the master record.

I'm confused about the "testing" part of UAT…what is there to test? don't we just look at the profiles and confirm that the data is there? Does that count as testing?

I'm struggling to build a test plan because I don't really see what there is to test.

Any help/suggestions/readings/articles is immensely appreciated.

Background:
We have about 3 million records (record being the constituent profile in the CRM), and we have identified at least a quarter of the records are duplicates (common cases being a person has 5+ profiles).

We've gone through the activities of building our criteria and identifying which of these duplicates would be marked as the master record, the duplicate records would then be merged into the master record.

Our next step is to validate the master records to ensure the historic profile data from duplicate records remains intact (transactions, interactions, and any custom fields like booleans and strings that were added on as part of their registrations, merged into the master record).

Task:
I am to build a UAT plan for multiple teams to validate the merge (I did express that my background is not related to what is being asked…but I'll try, because no one else in the company has any idea what to do at this point, budget is really really tight so cannot hire contractors)

Best Answer

With 3 million records, and 25% duplicates, you've got 750,000 constituent records that need to be merged & verified. That's a lot of manual work to merge & verify all 750,000 constituents individually.

Presumably there is some automation that you are going to use to identify & merge all of these constituent records. For your User Acceptance Testing, you don't need to verify every merge, you just need to verify a sampling of different scenarios. Your task is to devise a list of test cases that represent those different scenarios. In short, you are trying to do comprehensive (not random) spot-checking

Usually, you can devise the scenarios based on the same business rules that were used to create the automation.

For example:

Ensure that for a given merge set, a single constituent record is active, and the duplicates are deactivated/deleted.
You merge two constituents, each with different home addresses. Both constituents have that address set to preferred/primary. Should the merged record have both addresses? Which address should be the preferred/primary address?
You merge two constituents with the same last name, and same address, but different first names. How should these names be combined? Are they treated as a single person (perhaps a nickname & formal name), or as a couple/spouses/household?
You have two constituents with different first & last names, but the same address. Should they be treated as a couple/household & merged? Should they remain separate, but both active? Should you assume that one moved out & another moved in at the same address, and simply invalidate the address on the older of the two records?
etc etc... for any scenarios you care about.

As part of your test cases, you would need to provide answers for the questions I pose above, in order to tell testers what they are looking for. Essentially, you provide a scenario, and desired result. Testers need to validate that the actual result matches with the desired result. If it matches, the test case passes. If it doesn't match, then the test case fails.

In some cases you might want to combine scenarios, to sort out possible conflicts in your criteria.

Given that you have an existing database to work with, you may want to identify specific records to use as part of UAT. Your test case could identify the specific records, the pre-merge data, and the expected post-merge results. Testers would then look up those records and verify the results match what is expected.¹

Once you've devised your comprehensive list of scenarios & desired results, you'll have a comprehensive test plan to use for your User Acceptance Testing!

¹In software development, even the testing is automated, rather than doing this check manually--but for a one-time project that level of automation is probably not necessary--it's more work than just doing it manually.)

Related Solutions

Why MySQL Flags a Duplicate Key Where None Exists

I tried running your code in MySQL 5.7.12 for MacOS. I can't seem to duplicate this, but I found something interesting.

SQL-Fiddle has MySQL 5.7.17. You got the error in MySQL 5.7.13. Was anything with regard to dropping and adding a secondary index fixed between those versions ? I believe it was. Please note the Release Notes for 5.7.14, bulletpoint 21 under the subheading Bugs Fixed:

InnoDB: An assertion was raised during rollback of an ALTER TABLE operation that dropped and added a secondary index. (Bug #22005726)

Question: What is a secondary index ? According to the MySQL Documentation for Clustered and Secondary Indexes right under the heading How Secondary Indexes Relate to the Clustered Index:

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

This show that a UNIQUE INDEX is just a secondary index. A secondary index would have a copy of the PRIMARY KEY attached to every index entry.

My assertion is that a bug was introduced in 5.7.13 that did not exist in 5.7.12 when it comes to dropping and creating an index on the same column(s) at the same time.

Look back at your ALTER TABLE command

mysql> ALTER TABLE my_table
        DROP INDEX `idx_new_id`,
        ADD UNIQUE INDEX `idx_new_id` USING BTREE (`new_id `);

You dropped an index with a specific index name (idx_new_id) and with a specific column list (just one column, new_id) and then said add a new index with the same name and the same column list but make it the index UNIQUE. The net effect is that the index was never really dropped and the UNIQUE attribute was simply attached to the already existing non-unique index. Then, under the hood, mysqld attempted to load data into the table which is already populated and an index that's still populated, thus creating a duplicate key error. Please note I am only asserting this. I would have to look at source code for 5.7.13 to see if this is indeed occurring.

Notwithstanding, 5.7.12 and 5.7.14 cannot reproduce this problem. This indicates that this strange condition was addressed when making 5.7.14.

In light of all my conjecture, here are my suggestions:

SUGGESTIONS

SUGGESTION #1 : Upgrade to 5.7.14 or above

SUGGESTION #2 : Change the ALTER TABLE to use a different index name

mysql> ALTER TABLE my_table
       DROP INDEX `idx_new_id`,
       ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

SUGGESTION #3 : Do 2 ALTER TABLE commands

mysql> ALTER TABLE my_table DROP INDEX `idx_new_id`;
mysql> ALTER TABLE my_table ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

GIVE IT A TRY !!!

SQL Server – Duplicate Records Returned from Table with No Duplicates

There are some tricky scenarios which can result in the same row being read twice from an index, even under the READ COMMITTED isolation level.

Your query does not qualify for an allocation order scan, so the storage engine will read the data from the table in the order of the clustered key.

For your table, you have the InProgress as the first column of the clustered key. It's likely that you're getting row or page locks as you scan through the table. If you read a row near the start of the scan, release the lock on it, that row is updated such that InProgress changes from 0 to 1, and then the row is read again in a different page then you could see duplicate WorkID values from your query.

There are lots of workarounds. You could insert into a heap and simply remove duplicate values. You could add a DISTINCT to the query. You could also enable a row-versioning isolation level, to provide a stable view of the committed state of the database, either as at the beginning of the transaction (snapshot isolation), or as at the beginning of the statement (read committed snapshot isolation).

Perhaps it's appropriate to add locking hints or to change the structure of the table. For a rather fun solution (probably not appropriate for production), you could try reading the index backwards. This can be done with a superfluous TOP along with an ORDER BY. Below is a very simple demo to illustrate the point:

CREATE TABLE #WorkTable (
    InProgress TINYINT NOT NULL,
    WorkID INT NOT NULL
    , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

The following query has the Ordered:false property but it'll still read the data in clustered key order:

SELECT WorkId
FROM #WorkTable;

However, the following query will read the data in reverse clustered order:

SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

We can see this by looking at the scan properties:

For your table, this means that if a row is updated such that InProgress changes from 0 to 1 it will be far less likely that it'll show up twice. It may not show up at all which could be a different problem.