Data QA: Identifying Duplicate Attribute Values

Introduction

Duplicate attribute values are those where multiple features share the same value for the same attribute. In many cases this is natural and expected; for example, the province (county/state) field for an address database is very likely to be the same for many records.

However, in some cases duplicate values are invalid and unexpected; for example, a field that should be a unique ID instead contains a value that is identical to that of another feature (1,2,3,4,4,5,6).

Duplicate ID values, in particular, can cause integrity errors when loaded into a database with a unique indexing key.

Sometimes duplicate values are valid as long as they have a specific relationship with another field; for example:

1. The first column has duplicates, but a combination of column 1 and 2 is unique (so with 2a, 2b, 2c, 2c, the final 2c is invalid because it does not match the pattern).

2. The first column has duplicates but it is valid because it matches column 2 (so with 2b, 2b, 2b, 2c, the 2c value is invalid because it does not match the pattern).

Duplicate attribute values can be identified with either the DuplicateFilter transformer or the Matcher transformer. The Matcher transformer is good for finding relationships where one column has duplicates and it is invalid because another column differs (the 2b, 2b, 2b, 2c example).

The Matcher transformer is also better at extracting all duplicate records, as the DuplicateFilter lets the first instance of a duplicate record pass. But the same behavior makes the DuplicateFilter more memory efficient, so that is preferred if possible.

It's fairly simple to identify and count duplicate values, but making fixes may require manual intervention, depending on what sort of contents the field is meant to contain.

The source data and template workspace can be downloaded from the Files section.

Step-by-Step Instructions

The source dataset for this example is an Excel spreadsheet containing business license information for the City of Vancouver.

There are multiple columns, but in particular, the three key ID numbers are of interest:

• RecordID is a unique ID number throughout the entire dataset; each record should have a different value.
• LicenseNumber is an ID number that can be reused and may therefore not be unique. There may also be multiple records for the same LicenseNumber; for example, a single business that operates at multiple addresses will have a record in the dataset for each address (but with the same LicenseNumber).
• LicenseRSN is a unique ID number per license; each license issued should have a unique LicenseRSN. There may be multiple records with this value as well, because it should match each record of a multiple-LicenseNumber feature.

The scenario here is to check for duplicate values. RecordID must be fully unique. Each LicenseRSN might have multiple records, but it should only ever match to a single LicenseNumber.

Part 1: Locating Duplicate Attribute Values

Follow these steps to learn how to identify duplicate attribute values.

1. Open FME Workbench

Open FME Workbench and start a new workspace.

2. Add an Excel Reader

Add a reader for the source data. On the top menu, click Add Reader. In the Add Reader dialog:

• Format: Microsoft Excel
• Dataset: /businesslicenses.xlsx
  • Browse to downloaded dataset

Review the parameters in the dialog, then click OK to close it, and click OK again to add the reader to the canvas. The default parameter values are fine for this example.

3. Add a DuplicateFilter Transformer to Check for Duplicate RecordID Values

Add a DuplicateFilter transformer to the canvas, connected to the reader feature type. In the parameters dialog, set the following:

• Key Attributes: RecordID

Enable Feature Caching and run the workspace. Once the run is complete, click the green magnifying glass icon on the Duplicate output port to open the data cache in Data Preview. One duplicate RecordID is found and output to the Duplicate output port. The RecordID number is 4854.

Only the duplicate record is displayed; the record it duplicates is not also flagged. In many cases, this is enough. However, to compare both records, open the full dataset:

When the "Advanced Fireworks Group" business license was canceled, it appears the "Killarney Cafe Dept." (the next license issued) was given the same record ID number in error.

4. Add a Matcher Transformer to Check for Duplicate LicenseRSN Values

Duplicate values are not a problem unless the duplicates have a different value for LicenseNumber (LicenseRSN should have a 1:1 match with LicenseNumber). Add a Matcher transformer connected to the Unique output port of the DuplicateFilter. 

In the parameters dialog, set the following:

• Check Attributes: Enabled
  • Attribute Matching Strategy: Match Selected Attributes
  • Selected Attributes: LicenseRSN
  • Attributes That Must Differ: LicenseNumber
• Check Geometry: Disabled

5. Inspect the Output from the Matcher

Run the workspace and inspect the Matched output port on the Matcher. Four records are identified as matching:

For "Electronic Enterprises Corp.," one of the LicenseNumbers contains a typo (120097 instead of 120079). The cause of the other duplicate is harder to determine.

This transformer returns both instances of the duplicate record, not just one. This comes with a performance cost, so it is not always the best solution.

6. Add a Tester Transformer to Test for Issued Licenses

A final check is for a duplicate LicenseNumber with a different LicenseRSN. Because the LicenseNumber value can be reused, there may be many of these, but the duplication is only a problem when both licenses are active at the same time. Add a Tester transformer connected to the Matcher's NotMatched output port. 

 In the parameters dialog, set the following and click OK:

• Test Clauses:
  • Left Value: Status
  • Operator: =
  • Right Value: Issued

7. Add a Second Matcher Transformer

Add another Matcher transformer to the workspace, connected to the Tester's Passed port. Set up the parameters as before, except this time:

• Check Attributes: Enabled
  • Attribute Matching Strategy: Match Selected Attributes
  • Selected Attributes: LicenseNumber
  • Attributes That Must Differ: LicenseRSN
• Check Geometry: Disabled

8. Run the Workspace and Inspect the Matcher's Matched Output Port

Run the workspace and inspect the Matched output port on the second Matcher transformer. Ten features exit through the Matched port:

These features have a duplicate LicenseNumber that does not match the same LicenseRSN number.

Part 2: Counting Duplicate Attribute Values

To count duplicate attribute values, first decide whether each instance of a duplicate value counts as one issue, or whether matching values together count as a single issue. For example, if two features share a LicenseNumber of 17-107142, this could count as one issue or two. Also decide whether to count each type of duplicate separately or together.

9. Add a StatisticsCalculator Transformer to Count Duplicate Features

To count duplicate features, add a StatisticsCalculator transformer connected to the second Matcher's Matched port. Set the transformer to analyze any attribute, and set the following:

• Statistics to Calculate:
  • Attribute: _match_id
  • Total Count: Enabled

Click OK to close the transformer.

10. Count Matches as a Single Issue

The DuplicateFilter transformer filtered the duplicate RecordID features, so only one feature was flagged as a duplicate; the record it duplicates is not counted. The Matcher transformer returns two features for each match. To count each match as a single issue instead of two, switch the connection from the Matcher's Matched port to its SingleMatched port:

Additional Resources 

Tutorial: Data Validation and QA with FME [Article]

Data QA: Identifying Duplicate Features with FME [Article]

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.