Data Lake Schema - Microsoft Entra ID
The School Data Sync (SDS) data lake contains the internal representation of the data that's imported from the institution’s Student Information System (SIS)/Student Management System (SMS). The exported data is slightly different than the data that's imported as the data is normalized and stored longitudinally. The longitudinal aspect of the lake is used to assist with providing analytics over users, classes, and data that may no longer be active in Microsoft 365.
SDS will also store an updated copy of your Microsoft Entra ID into the Data Lake. The copy of Microsoft Entra assists with user matching between your SIS / SMS and Microsoft Entra user object. At this stage, the match link isn't written to the Microsoft Entra user object, only to the linking table in the Data Lake.
The data is enhanced with a FirstSeenDateTime and a LastSeenDateTime. SDS may not know when data is added/removed from Microsoft Entra ID, it simply knows when it first or last saw the data.
FirstSeenDateTime is the time the record was first seen by SDS and the creation data of that row in the table. This does NOT mean it's the creation date of the record in Microsoft Entra ID as SDS might have run well after the data was added from an external system.
In a similar fashion, LastSeenDateTime is the moment SDS last saw the data in a sync. This isn't the deletion date of the record from Microsoft Entra ID, but the date (time) of the last time SDS saw the record during a sync.
The Related object column below indicates if this ID is a foreign key to a different table.
Resource: Microsoft Entra user
Representation of user records for accounts in Microsoft Entra ID.
Export type: AADUser
| Column Name | Column Type | Related Object | Description |
|---|---|---|---|
| ObjectId | String | Microsoft Entra Object Identifier | |
| UserPrincipalName | String | Microsoft Entra UserPrincipalName | |
| String | Primary Email Address in Microsoft Entra ID | ||
| MailNickName | String | mailNickName in Microsoft Entra ID | |
| GiveName | String | Microsoft Entra ID set giveName for user | |
| Surname | String | Microsoft Entra ID set surname for user | |
| DisplayName | String | Microsoft Entra ID set displayName for user | |
| AnchorId | String | School Data Sync initialized Roster value | |
| StudentId | String | School Data Sync initialized SIS ID / sourcedId value for Students | |
| TeacherId | String | School Data Sync initialized SIS ID / sourcedId value for Teachers | |
| Role | String | School Data Sync initialized Role value | |
| FirstSeenDateTime | DateTimeOffset | ||
| LastSeenDateTime | DateTimeOffset |
Resource: AADUserPersonMapping
Present only when Roster data is also present.
Mapping between AADUser entries and roster Person entries.
Export type: AADUserPersonMapping
| Column Name | Column Type | Related Object | Description |
|---|---|---|---|
| ObjectId | String | AADuser.ObjectId | Microsoft Entra Object Identifier |
| PersonId | String | Person.Id | Roster Person Identifier |
| FirstSeenDateTime | DateTimeOffset | ||
| LastSeenDateTime | DateTimeOffset |
Resource: AADGroup
Representation of group records in Microsoft Entra ID.
Export type: AADGroup
| Column Name | Column Type | Related Object | Description |
|---|---|---|---|
| ObjectId | String | AADuser.ObjectId | Microsoft Entra Object Identifier |
| DisplayName | String | displayName of group in Microsoft Entra ID | |
| String | Primary EmailAddress of group in Microsoft Entra ID | ||
| MailNickName | String | mailNickName of group in Microsoft Entra ID | |
| AnchorId | String | School Data Sync initialized Roster value | |
| SectionId | String | School Data Sync initialized SIS ID / sourcedId value for Section | |
| FirstSeenDateTime | DateTimeOffset | ||
| LastSeenDateTime | DateTimeOffset |
Resource: AADGroupMembership
Representation of mapping between AADGroup and AADUser in Microsoft Entra ID.
Export type: AADGroupMembership
| Column Name | Column Type | Related Object | Description |
|---|---|---|---|
| UserObjectId | String | AADuser.ObjectId | Microsoft Entra Object Identifier |
| GroupObjectId | String | AADGroup.ObjectId | displayName of group in Microsoft Entra ID |
| Role | String | Users role in Group (Owner or Member), not roster role | |
| FirstSeenDateTime | DateTimeOffset | ||
| LastSeenDateTime | DateTimeOffset |