Brightspace Data Sets full vs differential: avoid duplicate rows

Choose where to run it

• In Rowseta: open Reports > New report and paste the query into the SQL editor on the Query tab. Sync the data sets listed below first.
• In your own setup: load your Data Hub extracts into your own SQL tool, one table per data set, named without spaces (User Enrollments becomes UserEnrollments), after applying differentials to the fulls. Another SQL tool may need small changes to these queries.

A free Rowseta Desktop app is coming soon.

This query needs User Enrollments. The reference explains its columns and the key used in the check below.

Check full and differential data for duplicates

With weekly fulls and daily differentials, a full extract gives the starting records. A differential contains changes, so the same record can appear again with newer values. Simply putting all those rows together would count repeated versions as separate records. That would make enrollment totals hard to interpret and could repeat a learner in a report.

Rowseta combines fulls and differentials during sync, with the newest row winning for a matching key. In your own setup, apply those changes yourself before running this query. A raw union of full plus differential rows is what this check catches.

For User Enrollments, the key is the pair OrgUnitId and UserId: the course or other organizational unit, together with the user. One user can have enrollments in several courses. That is expected, because each course gives the pair a different value. Counting UserId alone would mistake those separate enrollments for duplicates.

Run the complete query in your chosen tool. No value needs replacing. The query groups rows by the two key columns, counts the rows in each group and returns only groups whose count is greater than one.

-- No values need changing for UserEnrollments.
-- Its catalog key is the combination of OrgUnitId and UserId.
SELECT OrgUnitId, UserId, COUNT(*) AS DuplicateCount
FROM UserEnrollments
GROUP BY OrgUnitId, UserId
HAVING COUNT(*) > 1
ORDER BY OrgUnitId, UserId;

The HAVING COUNT(*) > 1 line is why an empty result is the expected outcome here. Groups with exactly one row do not appear. This check is specific to the User Enrollments key; other data sets can have different keys (check each data set reference), so changing only the table name would not make it a valid general duplicate check.

This also does not compare the text of every field or check whether every expected enrollment is present. It answers one question about the combined rows: does any organizational-unit and user pair appear more than once? Keep that narrow meaning when you use the result alongside your other checks.

Read the duplicate enrollment check

This fictional sample data returns no rows. Rowseta shows:

This report has no rows in the synced data.

Other SQL tools show an empty result.

This means there are no duplicate keys in the combined User Enrollments data for that sample; it does not mean that there are no enrollments. If your result contains rows instead, each returned pair has more than one row, and DuplicateCount tells you how many.