Schemas for Data Tables
The schema defines the structure of the tables in the database. Typically, schema versions increment when enhancements are made to the data model, such as adding new tables, columns, or improvements to relationships between each.
Schemas also include the definitions for the primary and foreign keys for the tables in the data models. For more information about these keys, see Primary Keys and Composite Keys in SAS Customer Intelligence 360: Data Dictionary.
Schema Versions
Before you download the tables, make sure that you
know which schema version you need. When you send REST calls to the SAS Customer Intelligence 360 Download
API, you can specify the query parameter
schemaVersion=x
(where x is an integer) to get download URLs for the
corresponding set of tables.
Each schema version represents enhancements to the data model. For example, a table in schema 3 might have additional columns that are not available in the version of that table with schema 1. In addition, some tables are available for only specific schema versions. For example, there are five SAS 360 Discover detail tables available with schema 4 that are not available with schema 3.
In addition, columns are added to the end of a table in newer schemas to minimize any potential impacts of table updates.
Review the following table to see the updates for the most recent schema versions:
|
Schema Version |
Description |
|---|---|
|
1 |
The default schema, which downloads the original tables from SAS 360 Discover. These tables do not take advantage of the streaming data platform or the Unified Data Model (UDM). In most situations, you should use schema 3 or 4 instead. |
|
3 |
Downloads tables that do not use the UDM. These tables include DBTs, ABTs, detail tables, and identity tables. Although schema 3 does not use the UDM, tables that use this schema can use the features of the streaming data platform. |
|
4 |
Downloads DBTs, ABTs, detail tables, identity tables, and metadata tables that use the UDM. The UDM enables you to use an event across all of SAS Customer Intelligence 360, because the data model introduces one set of tables for all data in SAS Customer Intelligence 360, regardless of your license. For more information, see the Preview of CI360 March 2020 release (20.03) community topic. |
|
5 |
Enables you to download the same UDM tables that are available with schema 4, but the schema also includes tables for SAS 360 Plan. |
|
6 |
Enables you to download UDM tables, tables for SAS 360 Plan, and tables from the Common Data Model (CDM). |
|
7 |
Includes enhancements to the tables that you can download with schema 6. For more information, see the Preview of CI360 March 2021 release (21.03) community topic. |
|
8 |
Includes the addition of new columns to existing UDM tables to enable cross-table queries to support future enhancements. For more information, see the Updates for Schema 8 in the UDM and CDM for SAS Customer Intelligence 360 community topic. |
|
9 |
Includes the addition of new columns to existing UDM tables to improve usage metrics and cross-table queries. In addition, new tables were added to support cost-center budgeting in SAS 360 Plan. |
|
10 |
Includes new tables in the UDM and CDM to support holdout control groups, usage metrics, and cross-table queries. For more information, see the Updates for Schema 10 in the UDM for SAS Customer Intelligence 360 community topic. |
|
11 |
Includes a new table in the UDM for tasks that connect to advertising platforms such as Facebook and Google. For more information, see the Updates for Schema 11 in the UDM for SAS Customer Intelligence 360 community topic. |
|
12 |
Includes new tables in the UDM for audience metadata. For more information, see the Updates for Schema 12 in the UDM for SAS Customer Intelligence 360 community topic. |
|
13 |
Includes enhancements to support A/B testing of email tasks, the optout method for emails, and usage data. For more information, see the Updates for Schema 13 in the UDM for SAS Customer Intelligence 360 community topic. |
|
14 |
Includes new tables in the UDM for audiences, updates to existing tables for A/B tests for Email tasks and events related to purchases. For more information, see the Updates for Schema 14 in the UDM for SAS Customer Intelligence 360 community topic. |
|
15 |
Includes enhancements to support additional channel metrics for usage tables and new columns for email tables. For more information, see the Updates for Schema 15 in the UDM for SAS Customer Intelligence 360 community topic. |
|
16 |
Includes enhancements to contextual identities, include timezone information in contact and response events, and more. For more information, see the Updates for Schema 16 in the UDM and CDM for SAS Customer Intelligence 360 community topic. |
|
17 |
Includes new tables for columns for journeys, new tables for SMS communications, event-related columns, and category updates. For more information, see the Updates for Schema 17 in the UDM for SAS Customer Intelligence 360 community topic. |
|
18 |
Includes updates to UDM tables for support server-side content delivery, email tasks, SMS replies, and contextual identity information. For more information, see the Updates for Schema 18 in the UDM for SAS Customer Intelligence 360 community topic. |
|
19 |
Includes updates to tables in the UDM and CDM for primary key or composite key information, new columns in journey tables for contextual identity information, usage data about the number of customer profiles that were processed for each license, and more. For more information, see the Updates for Schema 19 in the UDM for SAS Customer Intelligence 360 community topic. |
|
20 |
Includes enhancements in the UDM and CDM for more information about primary keys and foreign keys, new metadata tables with a complete history of metadata for items, updates to usage tables to capture the counts from occurrences of direct marketing tasks and more. For more information, see the Updates for Schema 20 in the UDM for SAS Customer Intelligence 360 community topic. |
|
21 |
Includes enhancements in the UDM for a new table to support decisioning events, a new table to record addressable mobile devices, enhancements to journey tables to record success criteria for audiences, and more. For more information, see the Updates for Schema 21 in the UDM for SAS Customer Intelligence 360 community topic. |
|
22 |
Includes enhancements in the UDM for a new table to support decisioning events, a new table to record addressable mobile devices, enhancements to journey tables to record success criteria for audiences, and more. For more information, see the Updates for Schema 22 in the UDM for SAS Customer Intelligence 360 community topic. |
|
23 |
Includes enhancements in the UDM for usage metrics for the audience exports, documentation of primary keys, tracking columns added to the EMAIL_SEND table, and more. For more information, see the Updates for Schema 23 in the UDM for SAS Customer Intelligence 360 community topic. |
The files that are provided by the download links are delimited files that represent the structure of the tables from the schema that existed when those files were created. The download links are only to the tables of the schema that is requested, but the structure of those files conforms to the most recent schema version.
However, files that comply with older schemas can be requested for download by using a date range that is earlier than the latest schema updates. For example, consider the following scenarios:
- Schema 23 is released, but you request a UDM download from date ranges where schema
16 was the most recent schema.
- You get files that conform to the schema 16 structure, because that was the current schema when that range of data was initially populated.
- Regardless of whether you use the “&schemaVersion=23” or the “&schemaVersion=16” parameter, the downloaded files would still contain data that uses the schema 16 structure. Only the schema.json is affected by that parameter.
- Schema 23 is released, and you request a UDM download from a date range where schema
17 was the most recent schema.
- You get files that contain the schema 23 structure, because that was the current schema when that range of data was initially populated.
- Regardless of whether you use the “&schemaVersion=23” or the “&schemaVersion=17” parameter, the downloaded files would still contain data that uses the schema 23 structure. Only the schema.json is affected by that parameter.
In addition, the response to REST API calls contains a URL to the version of the schema that you requested, but the tables themselves contain columns that were introduced in later schemas.
In some situations, there might be a mismatch between the contents of the tables and the schema version:
- The downloaded data might contain
more columns than are documented in the Help Center or in the schema files.
These columns might have been introduced for internal consumption already
and will be added to a future version of the schema.
The same scenario can happen when you are not using the most recent schema. The data that you download always uses the columns from the latest schema, but you might have requested a schema version that has fewer columns.
- A schema might add columns to
tables, but the columns are not populated with data until a later release of
SAS Customer Intelligence
360. For example, a table in the current schema might have nine
columns, but data records populate only seven columns. When columns are
added but not yet populated with data, you can ignore these columns when you
process the data.
When SAS Customer Intelligence 360 is updated to populate these columns, you might need to wait 2–3 days before the data is populated in the downloaded tables.
To mitigate any issues, configure your ETL processes to allow for the physical files to have more columns than what is described in the JSON schema and the documentation.
Viewing Available Schemas with the REST API
You can use the schemaVersions endpoint to view the schemas that are available in your environment. To view the schemas that are available for a set of tables, send a GET call similar to this example:
GET https://<external gateway host>/marketingGateway/discoverService/
dataDownload/schemaVersions?martType=<mart>The value for the martType parameter can be one of these values:
|
Value |
Description |
|---|---|
|
dbt-report |
Lists the schemas for the DBTs. |
|
detail |
Lists the schemas for the detail tables for SAS 360 Discover and the detail tables for SAS 360 Engage |