Building a SIF Data Hub
Step through building a district's student-data hub, from publishing it as a DACPAC to serving every downstream system from one clean, SIF-modelled source.
1Publish the hub from a DACPAC
The whole hub is schema as code: every table, view and procedure is a file in a database project, built into one package, a DACPAC. Publishing compares the package with the target and applies only the differences, so the first install and every later upgrade use the same command. It refuses a change that would lose data unless you allow it. Choose where the hub will live.
Target: hsd-sql.database.windows.net · database HSD-Hub
- The whole schema is in Git: every change is reviewed, versioned and repeatable.
- The same package installs a new hub or upgrades an existing one.
- Visual Studio and AI tools such as Claude Code work well in a buildable database project, so extending the hub stays safe.
2Bring in SIF, with its definitions
The hub uses the SIF North America standard as its model, so a student, a school or an enrolment means the same thing whichever system it came from. Every table and column carries its definition inside the database, as an extended property: what it is, where it sits in the SIF specification, and whether it is standard SIF, a hub extension or vendor-specific. People read it in SSMS; AI tools read it with a query, with no separate document to find.
-- Any tool can read the definitions straight from the database.
SELECT t.name AS [table], c.name AS [column],
d.value AS [definition], p.value AS [sif_path]
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
LEFT JOIN sys.extended_properties d ON d.major_id = t.object_id
AND d.minor_id = c.column_id AND d.name = 'MS_Description'
LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id
AND p.minor_id = c.column_id AND p.name = 'Hub_SifPath'
WHERE SCHEMA_NAME(t.schema_id) = 'sif';
table column sif_path definition
----------------------- -------------- --------------- ---------------------------------
StudentSchoolEnrollment GradeLevelCode GradeLevel/Code Grade for this enrolment: 0 for …
StudentSchoolEnrollment ExitTypeCode ExitType/Code Why the enrolment ended: …
StudentPersonal LocalId LocalId The district's own student number … 3Model it right, checked by SystemsCatalog
Every hub table follows the same shape, which SystemsCatalog enforces as a top-priority principle: it extends dbo.Entity for its audit trail, has a GUID surrogate key, a business key people recognise, foreign keys named for the table they point to, and check constraints that stop obvious bad data at the door. Consistent keys are what keep the data linked and valid as it flows in from many systems.
Fix it with Claude, then check again.
4A schema for every source
SIF lives in its own schema, and so does every system that feeds the hub. Each source’s data lands in its own schema, shaped like that source, and is then mapped into SIF. Schemas are named for the domain, not the vendor, so changing vendors means a new mapping, not a new design. The dbo.Source registry gives each system a key that is stamped on every record it writes. Choose a source.
The dbo.Source registry
| SourceKey | Code | System | Schema |
|---|---|---|---|
| 2 | Sis | Student information system | sis |
| 1 | Hr | Human resources system | hr |
| 7 | Trnspt | Transportation system | trnspt |
| 19 | Fee | Fee payment system | fee |
| 18 | ContactPortal | Contact Portal (parents) | app |
| 12 | Hub | The hub itself | dbo |
5Every record audited
Because every table extends dbo.Entity, any record can answer: who created it, which system last changed it, and when. That makes cleanup and troubleshooting a query, not an investigation. It also drives the sync rules: a parent’s change from the portal is written back to the SIS, and the nightly SIS load won’t overwrite it.
sif.StudentContactPersonal · Priya Okafor
SourceKey- 18 · ContactPortal
CreatedOn- 2025-08-21 02:04:11
CreatedBy- sis-sync
UpdatedOn- 2026-02-10 19:42:37
UpdatedBy- priya.okafor (portal)
Discriminator- sif.StudentContactPersonal
IsActive- 1
Change history
- 2025-08-21 02:04SisCreated from the SIS nightly load
- 2025-11-03 02:05SisEmail updated in the SIS by the school office
- 2026-02-10 19:42ContactPortalMobile phone changed by the parent: 403-555-0142 → 403-555-0199
- 2026-02-10 19:43HubChange captured and written back to the SIS
- 2026-02-11 02:05SisNightly load skipped the phone: last written by the Contact Portal
6Serve everyone from the hub
Without a hub, every system that needs student data connects to every source it needs, and the SIS is read again for each one. With the hub, each source lands once, and every consumer reads clean SIF data shaped for it by a view. Fewer connections, far less traffic on your SIS, and one place to fix a problem. Move the sliders.
Student information systemOther sourcesConsuming systemsPoint to point assumes each consumer needs the SIS plus about half of the other sources.
What the hub serves
- Actful-KPIStaff and school assignments for dashboard access
- Bus routing vendorRider list from a shaped view, nightly
- Fee payment systemEnrolled students and grade, nightly
- Contact PortalContacts and relationships, live
- Directory / Entra IDStaff accounts and group membership, hourly
- ReportingEnrolment and contact completeness, daily
What you just saw
A fictional district builds its student-data hub and connects its systems to it:
- Publish the hub from a DACPAC. The whole hub is schema as code, so a new install and every upgrade run the same repeatable command, to Azure SQL, SQL Server or Amazon RDS.
- Bring in SIF, with its definitions. Every table and column carries its definition inside the database, so people and AI tools can read what the data means without hunting for a document.
- Model it right. Every table extends an audit table and has a surrogate key, a business key, named foreign keys and check constraints. SystemsCatalog checks each change against those rules.
- A schema for every source. SIF has its own schema, and so does each system that feeds the hub. Each source’s data is mapped into SIF and stamped with its source key.
- Every record audited. Who created a record, which system last changed it, and when, is one query away, and it drives the rules that keep a parent’s update from being overwritten.
- Serve everyone from the hub. Each source lands once, and every downstream system reads clean data shaped for it, with far fewer connections and far less traffic on your student information system.
Who it’s for
District IT leaders tired of maintaining a web of point-to-point integrations, and the teams who have to answer “where did this number come from?”
Ready to Make More Room for Learning?
With offices in Calgary, AB; Desert Hot Springs, CA; and Brooksville, FL, we serve schools, districts and educational organizations across North America. Let's talk about where you are and where you want to go.
Contact Us