Skip to main content
Version: 8

Using the Changed Record Queries

The Ed-Fi API platform contains data that gets updated frequently. The platform tracks inserts, updates, deletes, and key changes, and surfaces those changes to client systems through a feature called changed record queries, or "change queries." Change queries allow client systems to narrow requests for data to only data that has changed since a specified system version number. This allows client systems to stay in sync with the Ed-Fi API without having to pull a complete dataset.

About Change Queries​

The Change Queries feature was designed to provide a lightweight mechanism for tracking relevant API operations for enabling system integration developers to easily retrieve and apply changed data to downstream systems. It is conceptually similar to SQL Server Change Tracking, and thus the solution has many of the same properties and considerations, such as:

  • The API will return the most recent version of the resource. (This is different from a change data capture (CDC) system that provides a log of every change.)
  • Synchronization processing is greatly simplified when using snapshot isolation (which isolates the API client's work from ongoing changes to the underlying source database). However, this approach is only available if the Ed-Fi API host takes some extra steps to create and make snapshots available.
  • When snapshots are not available, the API client bears the ultimate responsibility for ensuring data consistency with downstream systems (guidance for implementing this type of processing is provided later in this document). A periodic full reinitialization of the target system's data (if possible) may be necessary to achieve consistency.
  • When snapshots are available and used for processing, this will likely limit the frequency with which processing can be performed (due to the technical constraints around creating and providing new snapshots).
  • The article on the Changed Record Queries feature has implementation details which may be of interest to some client system developers.

Overview of Change Query Endpoints​

note

The examples on this page assume a host that sets the path base to api and uses no tenant or route qualifier segments. Adjust the paths to match your host's routes; see API Routes.

The core operations for Change Queries processing are as follows.

Snapshots​

The API host makes a snapshot available by registering it in the Configuration Service; see Changed Record Queries.

The API client should set the Use-Snapshot HTTP header to true on each request. The value isn't case-sensitive, and spaces around it are ignored, so TRUE also works. Any other value, such as yes or 1, reads current data instead, without an error. Snapshots are read-only: a POST, PUT, or DELETE request to a resource that sends Use-Snapshot: true returns 405 Method Not Allowed with an Allow: GET header.

If a 404 Not Found response with the message Snapshot not found. is returned, the host hasn't set up a snapshot for change query processing, or the snapshot isn't available. The API doesn't fall back to current data in that case; contact the API host.

Available Change Versions Resource​

The Ed-Fi API uses a change version (as opposed to a date/time) in the form of a sequential long integer. The global Available Change Versions API resource provides information on the current change version. This resource allows clients to request just the resource items that have been created, updated, or deleted since the last synchronization processing was performed.

GET /api/changeQueries/v1/availableChangeVersions
{
"oldestChangeVersion": 0,
"newestChangeVersion": 100
}

Minimum and Maximum Change Version Parameters​

The Minimum Change Version and Maximum Change Version parameters allow clients to request the latest representation of all resources that were modified within the given change version window. These parameters are available on every data management resource, both as part of the Ed-Fi Data Standard and in extension models. The parameters work with both offset paging (the offset and limit parameters) and cursor paging (the pageToken and pageSize parameters). Using paging parameters in conjunction with change version parameters, all records can be retrieved over multiple calls.

  • GET /api/data/ed-fi/classPeriods?minChangeVersion=234378&maxChangeVersion=234974&offset=100&limit=500
  • GET /api/data/ed-fi/classPeriods?minChangeVersion=234378&maxChangeVersion=234974&pageToken=<token>&pageSize=500

Deletes Route​

The "Deletes" route allows clients to get the id and the natural key values for deleted resources. This route supports only the offset and limit paging parameters; a request with pageToken or pageSize returns 400 Bad Request.

GET /api/data/ed-fi/studentProgramAssociations/deletes?minChangeVersion=234378&offset=0&limit=25

Deletes Response for Resource with Composite Key​

[
{
"id": "cfa3fea8-edf4-4c50-b95e-1808e616460a",
"changeVersion": 234550,
"keyValues": {
"beginDate": "2021-08-30",
"educationOrganizationId": 255901,
"programEducationOrganizationId": 255901,
"programName": "Bilingual",
"programTypeDescriptor": "uri://ed-fi.org/ProgramTypeDescriptor#Bilingual",
"studentUniqueId": "604854"
}
}
]

KeyChanges Route​

The "keyChanges" route allows clients to get the natural key changes for updated resources. Not all resources support key changes. For those unsupported resources, the "keyChanges" route will always return an empty array. If there were multiple key changes in the change window defined on the request, there will be one entry per resource item affected with the initial and final key values for that resource. Like the "Deletes" route, this route supports only the offset and limit paging parameters.

Both the "Deletes" and "keyChanges" routes require the ReadChanges action on the resource in the API client's claim set. Without it, the request returns 403 Forbidden.

GET /api/data/ed-fi/classPeriods/keyChanges?minChangeVersion=104030&offset=100&limit=500

Key Changes Response for Resource with Composite Key​

[
{
"id": "e0f8848b-54ad-4bdd-be81-e96737ed7b46",
"changeVersion": 104036,
"oldKeyValues": {
"classPeriodName": "ORIGINAL",
"schoolId": 255901001
},
"newKeyValues": {
"classPeriodName": "UPDATE 2",
"schoolId": 255901001
}
}
]

Synchronization Using the Change Query Endpoints​

The primary purpose of the change queries feature is to support efficient movement of modified data from the Ed-Fi API to a downstream source system(s). This section covers the basics and borrows heavily from Microsoft's documentation entitled, Work with change tracking (SQL Server).

Overview​

The recommended implementation of a data synchronization solution contains the following elements:

  • Identify which API resources are to be processed and the order of processing (informed by Ed-Fi model dependencies).
  • Initial Synchronization (a one-time operation).
    • (Optional) If snapshot isolation is supported by the host, apply the Use-Snapshot: true header to all API calls in this run, starting with the next step.
    • Obtain the system's current change version (a.k.a., the synchronization version).
    • Obtain and process the initial data for identified resources (with no change version filters applied).
    • If successful, save the synchronization version for future change processing.
  • Perform Change Processing (repeating, on a schedule).
    • Obtain the saved synchronization version from the previous successful processing and add 1 (a.k.a., the starting change version).
    • (Optional) If snapshot isolation is supported by the host, apply the Use-Snapshot: true header to all API calls in this processing run, starting with the next step.
    • Obtain the system's current change version (a.k.a., the synchronization version).
    • Process for key changes on identified resources (as applicable, in dependency order).
    • Process for changes on identified resources (in dependency order).
    • Process deletes on identified resources (in reverse- dependency order).
    • If successful, save the new synchronization version value (for future Change Processing).

Identify resources to be processed​

While some use cases may be very focused on a small subset of Ed-Fi data, for more expansive processing consider using the resource dependency metadata endpoint to correctly order the processing for the referential integrity defined in the Ed-Fi model.

Obtain initial data​

Before an application can obtain changes for the first time, the application must obtain the system's current change version. This can be obtained using the availableChangeVersions route:

GET /api/changeQueries/v1/availableChangeVersions

If you're using a snapshot, send the Use-Snapshot: true header on this request too. The response reports the newest change version of the database the request reads from, so the synchronization version must come from the same snapshot as the data.

Available Change Versions Response​

{
"oldestChangeVersion": 0,
"newestChangeVersion": 114218
}

With the current (initial) change version identified, the initial data should be retrieved from the API using paged queries against the desired data management API resources. Use cursor paging, which performs better than offset paging on large resources, as follows:

  1. GET /api/data/ed-fi/students?limit=500 (the first request has no page token, so it uses limit; pageSize applies only with a pageToken)
  2. GET /api/data/ed-fi/students?pageToken=<Next-Page-Token>&pageSize=500
  3. ... (repeat until a response has no Next-Page-Token header)

To read a large resource in parallel, request partitions from the /partitions endpoint (for example, GET /api/data/ed-fi/students/partitions) and read each partition with cursor paging. Use cursor paging the same way when reading changes from the main data management resources. The keyChanges and deletes routes accept only offset and limit.

If retrieval of the initial data set succeeds, save the change version value obtained at the beginning of this initial processing for use in the next stage – processing for changes.

Use the change version to obtain changes​

The minChangeVersion value used for change processing should be the previously stored (processed) change version value, incremented by 1 to avoid processing any of the same changes again.

For resources that allow for primary/natural key changes, first use the keyChanges route with the minChangeVersion parameter as follows:

GET /api/data/ed-fi/sections/keyChanges?minChangeVersion=101
info

Since the API always returns the current state, for ease of implementation it is recommended that you apply the key changes to the target system before continuing on with processing the other changes.

To obtain changes that have been made to a particular resource, use the main data management API with the minChangeVersion parameter, as follows:

GET /api/data/ed-fi/sections?minChangeVersion=101

To obtain resource items that have been deleted, use the deletes route with the minChangeVersion parameter as follows:

GET /api/data/ed-fi/sections/deletes?minChangeVersion=101

Obtain consistent and correct results​

Obtaining the changed data for a resource requires multiple steps. Inconsistent or incorrect results could be returned if certain issues aren't considered and handled. For example, to obtain the changes that were made to the students and studentSectionAssociations resources, an application would perform the following steps:

  • Get the current change version of the system (a.k.a., the synchronization version).
  • Obtain and process all the changes for the students resource.
  • Obtain and process all the changes for the studentSectionAssociations resource.

During this processing, ongoing operations by other API clients could be occurring in the database that could affect the results that are returned by the previous steps, such as the following operations:

  • Changes can be made to the resources after the synchronization change version has been obtained. Therefore, more changes can be returned than expected.
  • Another API client could add new resource items in the time between the calls to fetch changes from the students resource and the calls to fetch changes from the studentSectionAssociations resource. Therefore, the results for the studentSectionAssociations resource could have items with foreign key values that don't exist in the data already processed for the students resource.

To overcome these challenges, we recommend that you use snapshot isolation (which must be supported and maintained by the API host). This will help to ensure consistency of the change information. If you don't (or can't) use snapshots, developing a synchronization process that uses change queries could require significantly more effort.

Use snapshot isolation​

Change queries has been designed to work well with snapshot isolation. Snapshot isolation must be supported and configured by the API host (see Changed Record Queries). All the steps that are required to obtain changes must be performed against a specific snapshot by applying the Use-Snapshot header set to true to each API request. This will ensure that the changes that are made to the data by other API clients won't be visible to the synchronization processing.

Apply the header to the availableChangeVersions request as well, before reading any data. Without it, the request reports the live database's newest change version, which can be ahead of the snapshot. For example, if the live database is at change version 200 and the snapshot is at 150, a client that reads the data from the snapshot but saves 200 as its synchronization version starts the next run at 201 and never processes changes 151 to 200.

Alternatives to snapshot isolation​

There are alternatives to using snapshot isolation, but they require more work to make sure all application requirements are met. Changes can occur after the synchronization version for the next enumeration has been obtained. There are two ways to handle this situation. The option that is used depends on the application and how it can handle the side-effects of each approach:

  • Include all changes, even those that have a change version larger than the synchronization version
    With this approach, all Change Queries API requests will only include the minChangeVersion parameter (the maxChangeVersion parameter will not be supplied).

    The resource items (including keyChanges and deletes) that have a change version larger than the synchronization version will be obtained again on the next synchronization. This must be expected and handled by the processing logic.

  • Ignore changes that have a change version larger than the synchronization version
    With this approach, all Change Queries API requests will include both the minChangeVersion and the maxChangeVersion parameters, the latter of which will be assigned to the synchronization version.

    This has the side effect that a new or updated resource item would be skipped if it was created or updated before the synchronization version was obtained, but then updated afterward. If there's a new resource item, a referential integrity problem might occur if there was a resource item in another resource that was created that referenced the skipped resource item. If there's an updated existing resource item, the resource item will be skipped and not synchronized until the next time.

  • Devise an approach that combines both the previous options
    With this approach, you could decide that depending on the operation you might want an application for which it is best to ignore changes newer than the next synchronization version in which the resource item was deleted, but creates/updates aren't ignored.

Adding to the potential list of issues that must be considered with either of the above options is how certain operations performed by other API clients can affect offset paging requests. For example, if while paging through a resource's items an item is deleted by another API client, it will impact the items that are included in the paging offsets calculated by the database engine, causing them all to shift. If an item that has already been processed is subsequently deleted during processing, all items will shift up in the page breaks, and the (formerly) first item on an upcoming page could be missed completely (never processed) as it shifts into a page that the client has already retrieved for processing.

The same outcome could result if new changes are ignored and an item is updated after it has been processed (because the item would no longer match the maxChangeVersion filter criteria and paging would shift as described above, resulting in the same "missed item" condition).

These page shifts are specific to offset paging. A cursor paging page token marks a position in the resource's ordering rather than a number of items to skip, so deleting an item doesn't shift later items into pages already read. Cursor paging still doesn't hold a consistent view of the data for the length of a walk, though: items created, changed, or deleted during the walk can appear, disappear, or be missed.

info

Choosing the approach that will work for the application when you are using change queries (or any custom tracking mechanism), requires significant analysis. Therefore, it is much simpler to use snapshot isolation.

info

For API-to-API change processing, consider using the API Publisher utility that was developed specifically for this use case.