> For the complete documentation index, see [llms.txt](https://developerdocs.instructure.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developerdocs.instructure.com/services/dap/dataset/dataset-additional-notes/snowflake-data-share-vs-export-api.md). # 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`. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://developerdocs.instructure.com/services/dap/dataset/dataset-additional-notes/snowflake-data-share-vs-export-api.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.