Snowflake Datashare vs Query API

Snowflake Data Share Dataset Differences Compared to DAP Query API

If your institution consumes Instructure LMS data through both the Snowflake data share and the Query API, you will find the two do not always return identical results. This guide explains where they diverge and why.

Both are populated from the same upstream pipeline, and both are scoped to the accounts your organization is entitled to. Where they differ, it is because the Query API validates its output against a published schema and the data share does not.

Most of the differences below share one cause. The Query API validates its output against a schema that was defined separately from Canvas. Where the two disagree, Canvas is the accurate one: it holds values, keys, and records the schema does not account for. Validation enforces the schema anyway, so real data gets replaced with placeholders or left out of the response.

A future Query API release will align its output with what Canvas actually stores, which will close most of these gaps. Until then, the behavior described here is current.

Naming and layout

Everything in the data share lands in a single schema, SECURE_CUSTOMER_LMS, so table names carry a product group prefix to keep them distinct. The Query API uses a separate namespace per product, so its names are unprefixed. A table you query as accounts through the Query API is SECURE_CUSTOMER_LMS.CANVAS_ACCOUNTS in the share.

The prefix matters most where names collide across products:

Source object	Data share name
canvas accounts	CANVAS_ACCOUNTS
catalog accounts	CATALOG_ACCOUNTS
canvas assignments	CANVAS_ASSIGNMENTS
new_quizzes assignments	NEW_QUIZZES_ASSIGNMENTS
pandataevents canvas_lms	PANDATAEVENTS_CANVAS_LMS

Metadata fields

Alongside the Canvas data itself, every table carries a few fields describing how the record reached you.

Field	Where	What it tells you	Use it for
ROOT_ACCOUNT_UUID	Both	Which of your root accounts the record belongs to	Separating data when you hold more than one root account
_IS_DELETED	Both	Whether the record was hard deleted in Canvas	Applying deletions in your own system
_COMMIT_TIME	Both	When the record was written to the platform	Incremental loads, described below
_CDC_ELT_BATCH_TIME	Both	Which pipeline run delivered the record	Diagnostics, and checking a run landed completely across tables
_CDC_SHARD	Data share only	The Canvas shard the record came from	Diagnostics
_QUERYING_ACCOUNT	Data share only	The Snowflake account that ran your query	Auditing access on your side

The last two are there for diagnostics and auditing. You do not need them for day to day reporting, but quoting them helps us trace a record if you raise a question about one.

Using _COMMIT_TIME

_COMMIT_TIME is when the record was written to the platform, not when the change happened in Canvas. The two are usually close together, but they are not the same thing.

Use it to pull only what is new. Keep the highest _COMMIT_TIME you have already loaded, then on your next run ask for anything greater than it. That gives you every insert, update, and delete since your last pull.

Do not use it to work out the order in which things happened in Canvas. It reflects when the record was written to the platform, which does not always match the order the changes were made. For event ordering, use the timestamps on the record itself, such as created_at and updated_at where the table provides them.

_CDC_ELT_BATCH_TIME marks the pipeline run a record arrived in. Every record delivered by that run carries the same value, across every table it touched. That makes it useful for confirming a run reached you in full, and for lining up records from different tables that arrived together, rather than for tracking any single record.

Rows returned

Both surfaces return only the accounts you are entitled to. Past that, the Query API drops rows the share keeps. Two cases cause it:

• A field the published schema marks as required is empty in the source.

• A YAML or JSON field failed to convert.

In the first case the Query API omits the row rather than return it with a gap. In the second, the data share returns the row with the conversion error left in the field, so you can see what went wrong.

Nothing marks an omitted row. If you reconcile record counts between the two surfaces, expect the share to come back higher, and treat that as normal rather than as a sign of a problem.

Value substitutions

Where a value falls outside its published schema, the Query API replaces it. The data share returns it as Canvas recorded it.

Field type	Data share	Query API
Enumerated fields	Raw value, including ones outside the documented set	Out of set values become __dap_unspecified__
List valued enumerated fields	Raw list	Unrecognized entries become __dap_unspecified__, one element at a time
Required fields the source left empty	Empty	Filled with a default appropriate to the type
Booleans nested in JSON objects	true / false	The strings "true" / "false"

__dap_unspecified__ means the source held something the schema does not recognize. If you need to know what it held, the data share has it.

JSON and settings fields

A handful of fields are stored as a single JSON object. The share returns the object whole. The Query API splits each one into separate typed columns.

That split costs you keys. Each of these fields has a fixed published key list, currently between 3 and 42 entries depending on the field, and anything Canvas writes outside that list does not survive into the Query API. A few keys also appear under different names than the source uses.

Scalars nested inside these objects are JSON serialized by the Query API, so a string arrives wrapped in quotation marks. In the share they stay native JSON values, typed as they were recorded.

Deleted records

Deletes work the same way on both surfaces. The record stays, its identifying fields are kept, everything else is emptied, and _IS_DELETED is set. This lets you pick deletions up incrementally.

_IS_DELETED covers hard deletes only, where the record was removed from the Canvas database. Most deletions you see in the Canvas interface are soft deletes: the record stays in place with its workflow_state set to deleted. Those reach you as ordinary updates, with _IS_DELETED still false. If you are looking for content a user deleted, check workflow_state rather than _IS_DELETED.