CD2 Quiz submissions missing / losing data

[KeithSmith_au]

I am having some issues with this table, and I suspect that there is an underlying issues, probably to do with the statement in the documentation that:

Stores attributes for the last submitted quiz.

This tables persists one record per user_id and quiz_id

My assumption of fetching any data for any table in CD2 is that executing a snapshot, then subsequent delta queries using the "from" of the most recent fetched data, and an unbounded "until", and applying the subsequent upsert/deletes should bring the dataset to the same place as taking a snapshot.  i.e. a complete incremental query from the first time in the data should be the same as a snapshot.

For this table, this is not the case.  I fetched the incremental delta, then fetched a snapshot - which both return data to the same time.  I compared my built up data to the snapshot, and I have MORE records than the snapshot - things have disappeared, but no D incremental deletions have been processed.

I have also fetched an incremental delta specifying FROM the 01 January this year.  I know that a LOT of the quizzes in these courses have multiple submissions. As the ID of the quiz_submission table changes for each submission, and as it is supposed to keep only the most recent submission for each user and quiz_id, I would be expecting to have a lot of "D" updates as the old records are replaced.  I only found 66 out of nearly 700,000 records.

This leads me to believe that the incremental update query for this table is not returning appropriate deletions.

However, there is a bigger problem here as well.  Some of the records that have disappeared (no longer in the latest snapshot) have NO records for that user_id and quiz_id in the table - but the user is still enrolled and active in the course.  So not only are quiz_submissions disappearing without the incremental update being recorded, but we are losing data.

Can anyone shed any light on what is going on here - or do I just raise a support ticket?

During the beta release I requested that this table not hold only the most recent quiz submission, but that it expose all of them - which may correct this problem.

[Edina_Tipter]

Hi @KeithSmith_au,

I believe that the issue you describe is connected to the fix we released yesterday. Please check under the Canvas Known Issues section: https://community.canvaslms.com/t5/Known-Issues/Canvas-Data-2-Inconsistent-snapshot-query-results-fo...

Basically, if you applied incremental queries on top of any snapshot then there were no missed records or updates. But before the fix it could have happened that the snapshot did not capture everything. That piece was fixed.

As for the deleted records you were expecting, the "D" in our service means that the record was hard deleted in Canvas (SQL delete) which is very rare. What is more probable that records are soft deleted (workflow state set to "deleted") which is equivalent to a "U" upsert (update or insert). So maybe this explains why you received less "D"s than expected.

I hope this helps but let me know if you have further questions.

PS:  if a bug is suspected, it's best to create a support ticket because the goal of the forum would be to share or ask questions from community peers but Instructure is checking these less regularly. This, to make sure that critical issues are handled with the right urgency. 🙂

[KeithSmith_au]

The support ticket has been raised and is being investigated.

As far as missing "D" records, this table is a bit of an exception.  You are not exposing all the Canvas data - supposedly only the most recent quiz submission - this means that the records have not been hard deleted from Canvas, but removed from CD2 - so should be reflecting a "D" in the feed.

Presumably there is some saving in space due to not holding all the quiz submission detail (some of which could be large), but all the keys would still need to be held in order to generate correct incremental updates.

It would be preferable to actually have the data match the Canvas data, and return all submissions (and expose the versions information to make sense of them).