Deleted USER_DIM accounts and WORKFLOW_STATE

[BenjaminSelby]

Hi. We've just started using Canvas Data. I have a question about the USER_DIM table. 

When I create a test user via the Canvas web app, and then later delete that user via the Canvas web app, they seem to appear in the USER_DIM table with WORKFLOW_STATE=REGISTERED. Some appear as WORKFLOW_STATE=PRE_REGISTERED. This doesn't seem correct... The schema doc for Canvas Data does not seem to explain the workflow states in any detail. Can someone please elaborate? 

The reason why this appears to be a problem is that deleted Canvas users who have been set up as Parent Observers seem to still get email notifications regarding observee course activity, even though the user has been deleted. I was wondering if this is something to do with their workflow state not being set to DELETED - ? 

[stimme]

Hi  @BenjaminSelby ‌, I looked for 'deleted' user_dim.workflow_state records and found mostly test students. The exceptions were user accounts that I believe were merged with other user accounts. Records on the user_dim table are a bit unusual, because the id fields are (I think) unique across Canvas, not just your institution. Please check the pseudonym_dim.workflow_state field. It may show 'deleted' for the users you deleted, which would confirm that you deleted their login from your institution account.

The problem with notifications sounds like one for Instructure Support and (if need be) your CSM.

[millerjm]

There isn't a difference in the user table whether they are teacher/student/observer in Canvas. 

Are you deleting the user from a COURSE or from CANVAS?  It seems like this is enrollment related notifications in which case I would check enrollment_dim table for those details. 

[millerjm]

It looks like these are the workflow_states for user_dim:  registered, deleted, pre_registered, creation_pending

You are correct, documentation is lacking for what these actually mean in canvas data. 

registered = user is active in Canvas (we add all of our students/teachers via SIS and they all are added this way)

deleted = user deleted entirely from Canvas

pre_registered = manually made user status of some sort

creation_pending = manually made user status of some sort

In the API documentation, you have these options: 

user[skip_registration]

Automatically mark the user as registered.If this is true, it is recommended to set "pseudonym[send_confirmation]" to true as well. Otherwise, the user will not receive any messages about their account creation.The users communication channel confirmation can be skipped by setting "communication_channel[skip_confirmation]" to true as well.

pseudonym[send_confirmation]

Send user notification of account creation if true. Automatically set to true during self-registration.

pseudonym[force_self_registration]    

Send user a self-registration style email if true. Setting it means the users will get a notification asking them to “complete the registration process” by clicking it, setting a password, and letting them in. Will only be executed on if the user does not need admin approval. Defaults to false unless explicitly provided.

communication_channel[skip_confirmation]

Only valid for site admins and account admins making requests; If true, the channel is automatically validated and no confirmation email or SMS is sent. Otherwise, the user must respond to a confirmation message to confirm the channel.If this is true, it is recommended to set "pseudonym[send_confirmation]" to true as well. Otherwise, the user will not receive any messages about their account creation.

I would guess that pre_registered and creation_pending has some combinations of these settings (or doesn't have these settings).  But registered is an active, definitely not deleted user.  Since we rarely create users manually I'm not sure how to replicate pre_registered or creation_pending. 

If you delete a user, they definitely will show as deleted in user_dim.  Are you waiting for new files?  Remember these files are about 2 days old when you get them so you have to wait. 

Joni

[BenjaminSelby]

Hi Joni. Thanks for your input. 

I deleted these users from Canvas altogether, not just from a course. I did this by going to each user's profile page and clicking the link 'Delete from <School Name>'.

Strangely, if I create URLs for these deleted users based on their canvas ID values (e.g. https://<domain>/accounts/1/users/<user_ID>), the page at that URL shows a message saying "Access Denied - You do not have access to that resource". I am an admin, so should have access to all resources. So, the deleted users are are no longer available in Canvas. However, they appear in the USER_DIM datafile as 'registered', and not 'deleted'. 

I deleted these users early this week. Maybe Monday or Tuesday? I refreshed our USER_DIM this morning. I will try again on Monday, but it does seem like they should appear as 'deleted' by now. 

Regardless, they should not still be receiving observer emails. This should have stopped as soon as I deleted them from Canvas. 

[millerjm · Accepted solution]

Are they showing as active enrollment, user, and pseudonym if you do this?

select * from enrollment_dim ed left outer join user_dim ud on ud.id = ed.user_id

left outer join pseudonym_dim pd on pd.user_id = ud.id

where PUT USER CRITERIA HERE

You can put the pd.unique_id or pd.sis_user_id or you can also use the user_id.global_canvas_id but you'd have to add your shard id to that number to make the query work.  

Are you using redshift hosted by Canvas or local data warehouse?  I would begin to wonder if your data files have actually updated.  If you are using redshift, I have had multiple occasions where the cluster we were on for AWS Redshift ran out of disk space, or there was a problem with the import process and our data did not update for a while.  They don't do much monitoring of the import processing.  If you want to check for sure, look for the most recent updated_at date in submission_dim or the timestamp in requests to see when the latest row was added.