Brightspace Data Sets schema: download SQL table scripts

Download the repository

Open the repository on GitHub (opens in a new tab), select Code > Download ZIP, then unzip it. Open a terminal in the downloaded repository's folder.

Create the empty tables

Choose your database's folder under sql/: sqlserver, postgres, snowflake, bigquery, databricks or duckdb. Each has one file per data set and all-tables.sql for all 144 tables.

For DuckDB, run the README's command:

duckdb file.db < sql/duckdb/all-tables.sql

For SQL Server, choose an empty database and run sql/sqlserver/all-tables.sql with sqlcmd or SSMS. The other engines have equivalent scripts; follow their connection instructions in the repository README.

The scripts create tables only if absent. They preserve existing tables and rows, create no key constraints and load no data.

SQL Server and DuckDB scripts were loaded and tested. Postgres scripts were checked only through DuckDB's Postgres-derived parser, providing compatibility evidence, not validation on Postgres. Snowflake, BigQuery and Databricks scripts were checked statically. Postgres, Snowflake, BigQuery and Databricks scripts have not been run on those databases.

Load your BDS CSV extracts

For this example, start with empty tables. Unzip full extracts for Organizational Units, Users and User Enrollments. Name the files as below and put them in the terminal's current folder. The load matches columns by name. Open file.db in DuckDB and run:

INSERT INTO OrganizationalUnits BY NAME SELECT * FROM read_csv('OrganizationalUnits.csv', header = true);
INSERT INTO Users BY NAME SELECT * FROM read_csv('Users.csv', header = true);
INSERT INTO UserEnrollments BY NAME SELECT * FROM read_csv('UserEnrollments.csv', header = true);

For other databases, use your database's own CSV import. Before combining extracts, follow the full vs differential guide to apply changes by key and avoid duplicate rows. Loading these fulls again would append rows.

Join enrollments to courses and users

This DuckDB query uses three tables. It groups enrollment rows by course and user IDs, keeping people and courses with the same names separate. Names are for display only.

SELECT Courses.OrgUnitId, ANY_VALUE(Courses.Name) AS CourseName,
       Users.UserId, ANY_VALUE(Users.FirstName) AS FirstName,
       ANY_VALUE(Users.LastName) AS LastName, COUNT(*) AS EnrollmentRows
FROM UserEnrollments
JOIN OrganizationalUnits AS Courses ON Courses.OrgUnitId = UserEnrollments.OrgUnitId
JOIN Users ON Users.UserId = UserEnrollments.UserId
WHERE Courses.Type = 'Course Offering'
GROUP BY Courses.OrgUnitId, Users.UserId
ORDER BY Courses.OrgUnitId, Users.UserId;

With fictional sample data, courses 100 and 200 are both named Biology, and users 10 and 20 are both named Alex Lee. The result keeps all four course/user pairs separate, each with EnrollmentRows equal to 1. This counts loaded enrollment rows, not students in a selected role.