Canvas Data 2 - Incremental "until" to maintain referential integrity

[KeithSmith_au]

In the API specification for using incremental queries, the "until" parameter (only used with "since" has the following explanation:

until

string <date-time> End timestamp (in UTC); only those records are returned that have not been changed after the specified date and time. If omitted (recommended), defaults to the commit time of the latest record.

I am not sure that I agree with the recommendation to always omit this.  If attempting to create an updated set of tables with referential integrity, it is important to ensure that all the tables are fetched to the same commit time. If running a series of incremental queries, there is a risk (albeit possible small) that some tables may be fetched prior to an update / refresh, and others after.  This risks a referential integrity issue with foreign keys.

I would have thought that the better practice would be to run a single incremental query with a since and omitted "until" date.  This will then return am "until" value which can be used as the "until" timestamp on all other table queries.

When "until" is specified on the input, the result returned for "until" matches the input.  For this to work, I need to know for sure (and I believe it to the the case) that the "until" value returned when an unbounded input is supplied is not the latest commit timestamp for the table, but the timestamp for the latest commit across all tables (i.e. the timestamp of the updated CD2 set).

[LeventeHunyadi]

DAP does not guarantee referential integrity for a specific point in time. However, it guarantees eventual consistency. In other words, even if there is a foreign key or discriminated key that has no matching primary key, you are assured that you will be receiving the missing primary key record in a future update. The reason for this behavior is that the data is processed in an asynchronous distributed system in which there might be skew in how the data arrives. However, in practice, a missing primary key for a foreign/discriminated key is rare.

The until timestamp is specific to the table, and is not a global commit time across all tables.

However, until has its uses. In the case of large datasets (e.g. request logs), it can help ensure a more speedy response time and/or break down the result-set into manageable chunks. The chunks can be retrieved with multiple chained queries, as described in the documentation.

[KeithSmith_au]

If there is never any guarantee of referential integrity, how can there be a guarantee of eventual consistency, given that at any point in time there will be a lack of consistency.

In order to understand the limitations of this data, I need to know how the data is propagated into the warehouse. I was (possibly erroneously) under the impression that the entire database for a Canvas Instance was being replicated via some form of log shipping, with the updates for all tables being processed from a stream that was cut off at a point in time, hence ensuring that once the replication of the stream had completed, all tables would have consistency within the Instance.

From what you have said, I now get the impression that the individual tables are being replicated independently, which is going to lead to a lot of mismatch, especially given how many tables are used with reference to each other.

Could someone please provide some detailed documentation on exactly how this data is being replicated so that we can understand the limitations of this tool.

[LeventeHunyadi]

DAP has referential integrity but not at a specific point in time. The data comes from a distributed system, and we don't have a global state across all tables. Records arrive in streaming replication, with at-least-once delivery semantics, but there is no single stream, there are multiple streams. This is why we can guarantee eventual consistency: records eventually get there but there might be some jitter. For example, the update for a record of an assignment (foreign record) might come a little bit sooner than the corresponding user who handed in the assignment (primary record). The difference doesn't usually have practical consequences as the granularity of human interaction is way less than the processing speed of the system. (Users don't spawn in sub-second, and hand in a brand new assignment.) But there is a slight chance that there might be a skew, and this is what is communicated with eventual consistency, not strict consistency.

DAP is an analytics system, not an operational database, and analytics systems in general don't have (or need) strong consistency guarantees. In exchange, they scale to vast amounts of data at reasonable cost. This is a compromise we have made. Unlike in CD 1, there is no cut-off point. In exchange, you get much more fresh data.

[KeithSmith_au]

The granularity of the human interaction may be of relevance if the data was being streamed in near to real time, or a continuous but lagging stream, resulting in the updates happening at the same rate as in the primary table, but this does not appear to be the case.  All the updates for a period occur very rapidly in batched, effectively compressing time.  The biggest cause of integrity issues is the change in keys that is performed in a user merge.  A single action replicates a potentially large number of updates into many tables as the foreign key that references the user is updated. Magnify this by user merge (or change in instance for a consortia) being processed via SIS import for a number of users at the same time, and there can be many key changes occurring.

If there are multiple streams, I assume that any given table is only processed by a single stream, otherwise there would be issues with the potential arrival of updates out of order, and the potential for incorrect states to be applied, even if only for a period of time until a correction was applied.

  e.g. record currently has value A as at 02:00, update timestamped 18:00 is applied with value C.  Table is queried (at 18:02) for incremental values until 16:00, and the result is returned as A (since the table is updated to 18:00, but the last value prior to 16:00 was A).  An update is then streamed at 18:03 timestamped 15:00 with value B.  If the same incremental query is now performed (at 18:04), still for values until 16:00, then the result returned would now be B.

Given that the "until" timestamp is valid for a table, and that updates to a table are applied in chronological order (to avoid issues of temporary invalidity), then consistency between tables should be able to be obtained by fetching data from all tables up until a common "until" - and thus ignoring anything past this.  To get a common "until", it would be necessary to find the oldest "at" across all the tables.

Finding the "at" for all table can be done by requesting an incremental without an until, and then getting the "at" date.  This is expensive, as all the data will be fetched, and then a second call made for the same if the "common" until time should be different. Alternatively, a request can be made with a "since" parameter well in the future.  This will return an error of the form:

---

"message": "The ‘since’ parameter in the request (2099-12-31T02:00:00Z) is greater than the latest data available until 2023-05-14T21:19:17.153Z. Please adjust and retry the query."

The message can then be parsed to find the desired "until" parameter.  This is feasible, but relies on the error message format remaining the same.  It would be preferable if the "error" timestamps in this message, and the one for requesting records with a "since" before the first available data could be returned as attributes of the error ("since" and "at") rather than just embedded in the message.

This approach would still need multiple calls to be made - one for each table.  To obtain integrity across a Consortia, then the calls need to be made for every instance.  With 12 instances, and the number of tables (90 odd currently) this is a lot of calls.  It would be preferable for something like the "table" query to be extended to retrieve the "at" date for each table as well.  It would also be useful for that query to return the schema version (not the details, just the version number) for the table.

Establishing a consistent starting point will sill be problematic.  Due to the differing "at" times, the snapshots are of limited use.  I see two alternatives:

1. Use a snapshot to load base data.  Do not trust this until after an incremental common time can be found that is greater than any of the "at" for any of the snapshot loads.  This will mean a lag of a few hours.
2. Establish the starting point using incremental queries to a common time.  Unfortunately this is messy, as this requires finding the very first data time for each table - as using "since" before the start of data returns an error message if that timestamp is less than the first data, rather than returning the data (with potentially a warning that the first available data was from "since"). As the error message gives the first available time, this could be used, but again requires parsing the error message unless the "since" is returned as an attribute of the error as well as part of the message.

How we move forward will depend on whether or not any of the APIs can be extended to return additional information in the short term.

[ColinMurtaugh]

My take is that trying to ensure complete referential integrity would take a lot of effort and, at least for us, wouldn't bring significant benefits. We'd be twisting the system to do something it wasn't designed for, and that's always a red flag for me.

Instead, we're just syncing every table once an hour, and taking the latest data available. By using the built-in 'syncdb' command, the since/at/until timestamps are completely managed by the library and our orchestration code is very simple.

Any inconsistencies at the edge won't significantly change the reporting/analytics that we generate using this data. For example, if we were to receive some submissions records before we received their corresponding assignment record, those submissions would just be ignored by our queries until the assignment showed up (probably in the next syncdb run). Since we're not expecting this database to contain realtime data, that doesn't cause a problem with the reporting that we generate. 

Is there something that you're looking to do with this data that would not be able to tolerate eventual consistency?

--Colin

[KeithSmith_au]

I agree that in some way we are twisting the system to do something it wasn't designed to do - but we were pushed this way by Instructure as the real-time alternatives we are currently using do not scale or offer incremental data approaches (and don't cover all the data we require). We currently utilise various REST API calls either directly (horrendous cost if there are hundreds of thousands of calls required) or to run various reports - some of which time out on our volumes, and others which can take 45 minutes to run, and transfer hundreds of megabytes of essentially the same data to just detect a few changes. We were advised that CD2 was going to solve these issues - but it also generates its own challenges.

We are looking to use this data for a number of purposes.  One is to effectively synchronise the state of some courses (enrolments mainly) with the SIS - and to implement rules that handle updates made in Canvas itself. Lagging data we can handle, but inconsistencies that cause things to appear and disappear will cause noise and result in updates being attempted which are not necessary.

Results are also being pushed through to other systems for storing compliance and other purposes.  This will be done on an incremental basis - pushing through changes. Having results being removed due to referential issues is not something we want to contemplate.

A lot of these issues and concerns arise from the user_id and the way in which it can be changed. In order to understand the magnitude of this, and why this may be relevant to us but not of a concern to others, it is important to note that we are forced to use a Consortia due to volumes.

We have over 1,250 schools each with their own sub account.  There are over 1.3 million users, of which over 650,000 are active.  We have around 450,000 active courses. Our largest courses have over 120,000 active enrolments, and the most used course will have over 200,000 submissions.

This is supported by 11 Canvas instances sitting under one additional instance that is the Consortia root.  The Consortia root only handles logins - all other objects are in one of the child instances.  The consortia is set up with a trust, and any user can participate in courses in any of the instances - but this means that the user and the course data are in different instances.  This all works, but there are Canvas limitations when working across instances which impact on some functionality - limitations which do not occur if the user and course are on the same Canvas instance.

In order to minimise these impacts, users are assigned to the instance where they are expected to have most of the courses - which works really well for the majority of school students who attend a single school.  Where it gets more problematic is where students take courses at multiple schools, where teachers teach at multiple schools.  The real impact occurs when students or teachers move schools (transition from primary to secondary being a major change, but moves happen all the time for many reasons).  We did a lot of work to try to minimise the need to move a user from one instance to another by analysing past movements and trying to keep the most commonly moved between schools in the same instance, but obviously this is not perfect.

In order to facilitate moving a user from one instance to another, Instructure developed the "home_account" feature in the users sis import.  This uses Canvas user merge functionality to "move" a user from one instance to another.  What this does is actually create a new canvas_user_id - and then changes all the previous references to point to that new user - across all the Canvas instances in the consortia - and in all the tables (so enrolments, assignments etc.).  When a user has their base location changed, and that location is in a different instance to where they previously were, we do a home_account merge of the user via SIS import.  (We don't actually check whether the location has changed instances, we simply compare the user attributes with the previous attributes, and for any change be it location, name, email etc. simply send a SIS import with home_account TRUE always - which will move if necessary, or leave where currently located).

When dealing with the data for our Canvas implementation, we aggregate the data from all 12 instances (as there are references that point between instances, integrity can only be achieved by doing this).  We add instance references to all the data so that we avoid duplicate keys. Not only do we need to deal with the difference in currency between tables, we also have to address the timing differences within the same table across 11 or 12 instances.  

With the volume of user moves, and the number of different instances, we have a lot of potential for large numbers of enrolment and result entities to have no corresponding user - and for users to be :"missing" any results.  If the timing is wrong, sending report to downstream systems that certain users no longer have current compliance training results simply because the keys haven't been updated is something to be avoided.

[ColinMurtaugh]

Ah - this is quite a complex setup! Our system is much smaller, but we reduce some of the synchronization complexity by preventing some kinds of changes directly in Canvas. For example, we don't allow enrollments to be changed in Canvas, so we can consider our SIS data authoritative for that.

I don't know enough about how the consortia setup works (or about your compliance requirements) to suggest anything, but I wish you luck!

--Colin