Comparing Export Methods
This page outlines the high-level differences between the Study Data Extract (SDE), the Study File Format (SFF), and the CDB Raw Export Type.
Key Concepts
The following sections provide key concepts for each export method.
Study File Format API
- A self-describing API that has the ability to extract full and incremental study data and study design data from a particular study. Self-describing means the API provides users with all the information they need to consume the content downstream.
- The manifest.json file provides the schema and description of each column along with additional metadata that users can use to understand the CSV files and ensure their integrations do not break.
- Available for both EDC and CDB customers.
- Does not offer customization.
The SFF API offers two retrieval modes:
- Full Data: Full data refers to the entirety of the study and study design data up until the point the package is generated.
- Incremental Data: Incremental data refers to any changes (additions, modifications, or deletions) of records between the previous package of study data.
Study Data Extract
- An extract containing the full set of study and study design data that can be scheduled or run on-demand as a job.
- Supports CSV, SAS, and XPT file formats.
- Limited customization.
- Run from EDC Tools or via the EDC REST API.
- Available for delivery to an FTP or AWS S3 location.
- Not available in CDB natively.
CDB Raw Export
- An export that contains listings and transforms listings into a standardized format.
- Includes clinical data (core listings) and operational data (system listings) by default.
- Supports the inclusion of custom listings, queries, observations, and protocol deviations.
- Customizable through CQL Listings and included in the export definition.
- Supports CSV and SAS file formats.
- Available via the Export user interface (scheduled or on-demand) or through a REST API.
- Can deliver user interface jobs to an FTP location.
- Available for CDB and Clinical Reporting users.
Key Differences
The following sections highlight the primary distinctions between SFF API, SDE, and CDB Raw Export.
Use Cases
Each export method serves a distinct need.
Study File Format
The SFF acts as a self-describing API that targets integrations, reporting, and analytics through a fast and reliable connection. It allows repeated rapid data loads, which keep target systems in sync. SFF also offers incremental data, which allows users to maintain up-to-date data without relying on full loads, thereby enhancing performance.
Users utilize the SFF in a programmatic way. It uses a standard output format that supports data from three sources:
- EDC
- CDB
- Non-Veeva EDC data
Study Data Extract
The SDE only contains Veeva EDC data. It delivers data through CSV or SAS datasets, with limited customization. The intention is for users to use it for analytics and downstream processing of the complete study data. The data can be ingested by a machine or human.
CDB Raw Export
The CDB Raw export has similar use cases to the SDE but allows the inclusion of both EDC and external third party data.
Delivery Methods
Delivery mechanisms vary across the three export methods. The primary distinction lies between API retrieval and scheduled jobs.
Study File Format
You retrieve the SFF through multiple endpoints, and the system publishes the packages at a given time (15 minutes for incremental packages, 24 hours for full packages). You don’t start a job to “kick off” the SFF, instead, you download the SFF packages directly through the API. Also, the SFF has no interface, aside from the functionality to enable the feature in EDC Tools.
Study Data Extract
The SDE is a job. You can access it through the user interface via EDC Tools or start it on-demand through an API. You retrieve the ZIP packages through the interface or the API. However, it is a job that requires scheduling or starting, though scheduling happens through the interface only. The system can deliver it to an external destination through FTPS.
CDB Raw Export
Similarly, the CDB Raw Export is a job that you schedule or run on-demand through the CDB or Clinical Reporting Export interface and deliver to an external connection similar to the SDE. You also run it through an API endpoint.
Versioning
Both SDE and SFF use versioning. The SDE adds a new version to the general release when a change occurs to the columns, datatypes, or other changes that might impact the files’ structure. The SDE maintains all versions, and any version deprecation is announced in advance to give customers time to upgrade.
The SFF, which acts as a self-describing API, has two versioning concepts that launch during general release cycles:
- API Version: This changes when structural changes occur to the API request or response payloads.
- Package Version: This changes when structural changes occur to the package schema that may be destructive.
SFF differs from the SDE in that additional columns do not necessarily require a version change, since the manifest.json file is for users to read programmatically, and therefore it can understand changes such as column additions. If the manifest.json file changes existing attributes (for example, those that may cause a breaking change while consuming), we’ll consider that change for a new package version. The SFF only maintains two active versions (the current version and the previous version) for its API and package, respectively.
The CDB Raw Export does not have a concept of package versioning. However, when the system adds new columns to the CQL result set from the parent listing, the system detects a change indicator for the impacted export definitions and export listings, which users have to manually accept in order to change the output of their export results.
Customizations
The SDE has limited customizations, including dataset selection, formatting, and file name customization. The SFF has no customizations. The purpose of the SFF is to provide users with a standard and unified format, with the expectation that they will apply any customizations or transformations of the data downstream. The CDB Raw Export offers more customization, since users choose which listings are included in an export definition and can include Custom CQL Listings and listings created by the Listing Builder in the output.
File Contents
The SFF consists of a ZIP file containing a data folder that houses all of the CSV files and a manifest.json file. The manifest.json file drives the self-describing aspect of the feature and allows users to programmatically ingest the content of the CSV files into their downstream systems.
The SDE consists of a ZIP file containing CSV files, SAS, and/or XPT files. The CDB Raw Export consists of a ZIP file containing CSV files and/or SAS files.
All of the ZIP files contain clinical data and operational or system data. The SDE has study design information in the definitions folder for human readability. In contrast, the SFF has the necessary study design information in the manifest.json file, which programmatically describes data from the clinical data files. The SFF study design information updates with the full package extraction when a study design change occurs in EDC.
Since the SFF contains restricted data, you must grant custom users the Study File Format Access permission, which includes Restricted Data Access by default.
Clinical Data
All export formats display clinical data on a per-form basis. This means that each file represents a form definition, and each row in the file represents a form’s record. The system “flattens” records to represent the Clinical Data study hierarchy, where clinical data items appear as columns, and the data in the cells represent the item’s values.
Each file contains header columns to represent key concepts, such as the Study, Study Site, and Country, that a row of data belongs to. This represents the Clinical Data Study Hierarchy of the CRF.
Formatting Differences
The following table provides key differences between the clinical data formatting of the SDE, SFF, and CDB Raw Export.
| Data Field | SFF | SDE | CDB Raw Export |
|---|---|---|---|
| Form Statuses | Includes Submitted and In Edit data from the eCRFs. | Includes all form statuses, with the option to exclude blank form data. | Includes Submitted data by default in the CQL projection. |
| Dates | Has the normalized date and the “raw” date as entered by the Site. | Has the normalized and formatted date and the “raw” date as entered by the Site. | Has the normalized date and the “raw” date as entered by the Site. |
| Datetimes |
|
Has four columns for datetimes:
|
Has the normalized datetimes and the “raw” datetimes in the format as entered by the Site (no timezones attached). |
| Booleans | Represented as true/false. | Offers a choice to format booleans. | Represented as true/false. |
| Dynamic Columns |
Has additional columns for:
|
Has additional columns for:
|
Has additional columns for:
|
| Names vs. Labels | Uses the “name” value primarily. Labels and Display Override Labels are stored in separate CSV files in the full SFF package extraction. | Uses “label” values primarily. | Includes the “name” value primarily. Some Sys Listings include the labels. Custom listings may include labels depending on the CQL. |
| External IDs | Present in the manifest.json file in the study_design block. |
Present in the CSV files. | Present in some of the Sys Listings. |
| Unique Identifiers | The SFF has the concept of the Row ID (ROWID), which is unique for a given row of data and is the foundation for helping track incremental changes between packages. | The SDE uses the Globally Unique Identifier, or GUID column to help users identify a unique key for the dataset. | Present in some of the Sys Listings. |
| Repeating Item Groups | The SFF does not assume that data from a non-repeating item group should repeat for repeating item group instances. You can transform and fill in the data if you need to do so programmatically and use the manifest file to help. | The SDE applies data from non-repeating item groups’ rows to repeating item groups’ rows, with the exception of Local Lab data that is in the compact format (versions 23R1 and later) and coding request data. This helps with the human readability aspect of the SDE. | The CDB Raw Export does not assume that data from a non-repeating item group should repeat for repeating item group instances. You can transform and fill in the data if you need to do so programmatically and use the manifest file to help. |
Headers
The following header columns remain static across all clinical forms and appear before the columns that represent Items and their values.
Note in the SFF, the ROWWRITEDT and ROWID columns appear at the end of each file.
| Data Field | SDE | SFF | CDB Raw Export |
|---|---|---|---|
| Study ID | STUDYNAME | Study.Name | Study Name |
| Study Label | Study Label | ||
| Country Name | COUNTRY | Country Name | |
| Site Country Code | SITECOUNTRY | Site.Country | Three Country Code Abbreviation in SFF |
| Study Site Number | SITENUM | SITENUM | Site.Number |
| Subject ID | SUBJID | SUBJID | Subject.Name |
| Event Group Definition Label | EGROUP | Event Group Definition Label | |
| Event Group Definition Name | EGROUPDEF | EGROUPNAME | EventGroup.Name |
| Event Group Definition External ID | EGROUPEID | Event Group Definition External ID | |
| Event Definition Label | EVENT | Event Definition Label | |
| Event Definition Name | EVENTDEF | EVENTNAME | Event.Name |
| Event Definition External ID | EVENTEID | Event Definition External ID | |
| Event Date | EVENTDT | Event Date | |
| Visit Method | VISMETHOD | Visit Method | |
| Event Status | Event Status | ||
| Event Group Sequence Number | ESEQ | EGSEQ | EventGroup.SeqNbr |
| Form Definition Label | FORM | Form Definition Label | |
| Form Definition Name | FORMDEF | FORMNAME | Form.Name |
| Form Definition External ID | FORMEID | Form Definition External ID | |
| Form Sequence Number | FSEQ | FSEQ | Form.SeqNbr |
| Form Status | FORMSTATUS | FORMSTATUS | |
| Form Created Datetime | CREATEDT | CREATEDDT | |
| Form First Submission Datetime | FIRSTSUBMITDT | FIRSTSUBMITDT | |
| Form Last Submission Datetime | LASTSUBMITDT | LASTSUBMITDT | |
| Item Group Definition Label | IGROUP | Item Group Definition Label | |
| Item Group Definition Name | IGROUPDEF | Item Group Definition Name | |
| Item Group Definition External ID | IGROUPEID | Item Group Definition External ID | |
| Item Group Sequence Number | IGSEQ | IGSEQ | ItemGroup.SeqNbr |
| Form Last Modified Datetime | DLASTMOD | FORMLASTMODDT | Form.LastModifiedDate |
| Datetime row written to file | ROWWRITEDT | ||
| Unique ID for the row | ROWID | ||
| Internal Form ID | FGUID | ||
| Internal Item Group ID | IGGUID | ||
| Form Marked Intentionally Left Blank | FORMILB | ||
| Last run of the SDE | LASTRUN | ||
| Form Link Item Definition Name | <Form Link ItemDef Name>_DEF | ||
| Form Link Item Definition Internal ID | <Form Link ItemDef Name>_LINKEDFORMID | ||
| Form Link Item Definition Internal Link ID | <Form Link ItemDef Name>_LINKID | ||
| Linked To Forms and their Items | LINKEDTO | ||
| Form and display items that the Item is Linked To | ITEMLINKEDTO | ||
| Linked Item | LINKEDITEM |
Coding
The following columns appear on clinical forms with Medical Coding enabled in EDC, specifically for MedDRA and WHO Drug Dictionaries. SFF and CDB Raw Export do not support the J-Drug Dictionary.
MedDRA Dictionary
| SDE | SFF | CDB Raw Export |
|---|---|---|
| CRSTATUS | CRSTATUS | CodingStatus |
| DICTTYPE | DICTTYPE | |
| DICTVER | DICTVER | DictionaryRelease |
| SOC | SOC | SOC |
| SOCID | SOCID | SOCCD |
| HLGT | HLGT | HLGT |
| HLGTID | HLGTID | HLGTCD |
| HLT | HLT | HLT |
| HLTID | HLTID | HLTCD |
| PT | PT | PT |
| PTID | PTID | PTCD |
| LLT | LLT | LLT |
| LLTID | LLTID | LLTCD |
| PRIMPATH | PRIMPATH | PrimaryPath |
| LASTCODEDAT | LASTCODEDDT | LastCodedDate |
| LASTCODEDBY | LASTCODEDBY | LastCodedBy |
WHO Drug Dictionary
| SDE | SFF | CDB Raw Export |
|---|---|---|
| CRSTATUS | CRSTATUS | CodingStatus |
| DICTTYPE | DICTTYPE | |
| DICTVER | DICTVER | DictionaryRelease |
| ATC1 | ATC1 | ATC1 |
| ATC1CD | ATC1CD | ATC1CD |
| ATC2 | ATC2 | ATC2 |
| ATC2CD | ATC2CD | ATC2CD |
| ATC3 | ATC3 | ATC3 |
| ATC3CD | ATC3CD | ATC3CD |
| ATC4 | ATC4 | ATC4 |
| ATC4CD | ATC4CD | ATC4CD |
| PREFNAME | PREFNAME | PreferredName |
| PREFCODE | PREFCODE | PreferredCD |
| PREFBASE | PREFBASE | |
| PREFLABEL | PREFLABEL | |
| DRUGNAME | DRUGNAME | DrugName |
| DRUGCODE | DRUGCODE | DrugCD |
| LASTCODEDBY | LASTCODEDBY | LastCodedBy |
| LASTCODEDAT | LASTCODEDDT | LastCodedDate |
Labs
The following columns appear on clinical forms with Local Labs enabled in EDC.
| SDE | SFF | CDB Raw Export |
|---|---|---|
| LBDTC | LBDTC | LBDTC |
| LBDTC_UTC | ||
| LBDTC_USER | ||
| LBDTC_RAW | LBDTC_RAW | LBDTC_RAW |
| LBLOC | LBLOC | LBLOC |
| LBAGE | LBAGE | LBAGE |
| LBAGE_U | LBAGE_UOM | LBAGE_UOM |
| LBAGE_TRANS | LBAGE_TRANSLATED | LBAGE_TRANSLATED |
| LBAGE_TRANSU | LBAGE_UOM_TRANSLATED | LBAGE_UOM_TRANSLATED |
| LBFAST | LBFAST | LBFAST |
| LBFAST_D | LBFAST_DECODE | LBFAST_DECODE |
| LBFEMALECYCLE | LBFEMALECYCLE | LBFEMALECYCLE |
| LBFEMALECYCLE_D | LBFEMALECYCLE_DECODE | LBFEMALECYCLE_DECODE |
| LBTEST | LBTEST | LBTEST |
| LABMODIFIER | LABMODIFIER | LABMODIFIER |
| LABMODIFIER_D | ||
| LBORRES | LBORRES | LBORRES |
| LBORRES_U_D | LBORRES_UOM_DECODE | DECODE_LBORRES |
| LBORRES_TRANS | LBORRES_TRANSLATED | TRANSLATED_LBORRES |
| LBORRES_TRANSU | LBORRES_UOM_TRANSLATED | UOMTRANSLATED_LBORRES |
| LBORNRLO | LBORNRLO | LBORNRLO |
| LBORNRHI | LBORNRHI | LBORNRHI |
| LBORNRLO_UOM | UOM_LBORNRLO | |
| LBORNRLOHI_U | LBORNRHI_UOM | UOM_LBORNRHI |
| LBORNRLO_TRANS | LBORNRLO_TRANSLATED | TRANSLATED_LBORNRLO |
| LBORNRHI_TRANS | LBORNRHI_TRANSLATED | TRANSLATED_LBORNRHI |
| LBORNRLO_UOM_TRANSLATED | UOMTRANSLATED_LBORNRLO | |
| LBORNRLOHI_TRANSU | LBORNRHI_UOM_TRANSLATED | UOMTRANSLATED_LBORNRHI |
| LBOVRDNRLO | LBOVRDNRLO | LBOVRDNRLO |
| LBOVRDNRHI | LBOVRDNRHI | LBOVRDNRHI |
| LBOVRDNRLO_UOM | UOM_LBOVRDNRLO | |
| LBOVRDNRLOHI_U | LBOVRDNRHI_UOM | UOM_LBOVRDNRHI |
| LBOVRDNRLO_TRANS | LBOVRDNRLO_TRANSLATED | TRANSLATED_LBOVRDNRLO |
| LBOVRDNRHI_TRANS | LBOVRDNRHI_TRANSLATED | TRANSLATED_LBOVRDNRHI |
| LBOVRDNRLO_UOM_TRANSLATED | UOMTRANSLATED_LBOVRDNRLO | |
| LBOVRDNRLOHI_TRANSU | LBOVRDNRHI_UOM_TRANSLATED | UOMTRANSLATED_LBOVRDNRHI |
| LBSTNRC | LBSTNRC | LBSTNRC |
| LBSTNRC_D | LBSTNRC_DECODE | DECODE_LBSTNRC |
| LBOVRDNRC | LBOVRDNRC | LBOVRDNRC |
| LBOVRDNRC_D | LBOVRDNRC_DECODE | DECODE_LBOVRDNRC |
| LBNRIND | LBNRIND | LBNRIND |
| LBCLSIG | LBCLSIG | LBCLSIG |
Operational Data
Operational data in the SFF, SDE, and CDB Raw Export tracks a subject’s progress within the context of a Clinical Data study object (for example, a Form, Event, or Query).
The SFF has the following operational data files:
- SYS_EVENTS
- SYS_FORMS
- SYS_SUBJECTS
- QUERIES
- QUERY_MSGS
- SYS_ILB
- SYS_LINKS
- SYS_SITES
- SYS_PD
The CDB Raw Export has the following operational data files:
- Sys_Events
- Sys_Forms
- Sys_ILB
- Sys_Links
- Sys_PD
- Sys_Sites
- Sys_Subjects
The SDE has the following operational data files:
- SYS_EVT
- SYS_FORM
- SYS_SUB
- SYS_Q
- SYS_QT
- SYS_ILB
- SYS_LINKS
- SYS_SITE
- SYS_ASM
- SYS_ASMR
- SYS_ANALYTES
- SYS_LABLOC
- SYS_LABRANGES
- SYS_PD
- SYS_RAND
- SYS_SAFC
- SYS_SAFM
SYS_FORM & SYS_FORMS
The following table compares the SDE, SFF, and CDB Raw Export by available columns for SYS_FORM and SYS_FORMS datasets. The column order is not the exact order within each export.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study.Label | Study Label | |
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Site.CountryName | Country Name | |
| SITECOUNTRY | SITECOUNTRY | Site.Country | Three Country Code Abbreviation in SFF |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| EGROUP | EventGroup.Label | Event Group Definition Label | |
| EventGroup.RepeatLabel | Event Group Definition Repeat Label | ||
| EGROUPDEF | EGROUPNAME | EventGroup.Name | Event Group Definition Name |
| Event Group |
SYS_EVT & SYS_EVENTS
The following table compares the SDE, SFF, and CDB Raw Export by available columns for SYS_EVT and SYS_EVENTS datasets. The column order is not the exact order within each export.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study.Label | Study Label | |
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Site.CountryName | Country Name | |
| SITECOUNTRY | SITECOUNTRY | Site.Country | Three Country Code Abbreviation in SFF |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| EGROUP | EventGroup.Label | Event Group Definition Label | |
| EventGroup.RepeatLabel | Event Group Definition Repeat Label | ||
| EGROUPDEF | EGROUPNAME | EventGroup.Name | Event Group Definition Name |
| EGROUPORDER | Event Group Order | ||
| EGROUPEID | EventGroup.ExternalID | Event Group Definition External ID | |
| ESEQ | EGSEQ | EventGroup.SeqNbr | Event Group Sequence Number |
| EVENT | Event.Label | Event Definition Label | |
| EVENTDEF | EVENTNAME | Event.Name | Event Definition Name |
| EVENTEID | Event.ExternalID | Event Definition External ID | |
| EVENTORDER | Event Order | ||
| EVENTDT | EVENTDT | Event.Date | Event Date |
| VISMETHOD | VISMETHOD | Event.VisitMethod | Visit Method |
| PLANNEDDT | PLANNEDDT | Event.PlannedDate | Event Planned Date |
| OVERDUEDT | Event Overdue Date | ||
| STATUS | EVENTSTATUS | Event.Status | Event Status |
SYS_ILB
The following table compares the SDE, SFF, and CDB Raw Export by available columns for the SYS_ILB dataset. The column order is not the exact order within each export.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study.Label | Study Label | |
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Site.CountryName | Country Name | |
| SITECOUNTRY | Site.Country | Three Country Code Abbreviation in SFF | |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| EGROUP | EventGroup.Label | Event Group Definition Label | |
| EventGroup.RepeatLabel | Event Group Definition Repeat Label | ||
| EGROUPDEF | EGROUPNAME | EventGroup.Name | Event Group Definition Name |
| EGROUPEID | EventGroup.ExternalID | Event Group Definition External ID | |
| ESEQ | EGSEQ | EventGroup.SeqNbr | Event Group Sequence Number |
| EVENT | Event.Label | Event Definition Label | |
| EVENTDEF | EVENTNAME | Event.Name | Event Definition Name |
| EVENTEID | Event.ExternalID | Event Definition External ID | |
| FORM | Form.Label | Form Definition Label | |
| FORMDEF | FORMNAME | Form.Name | Form Definition Name |
| FORMEID | Form.ExternalID | Form Definition External ID | |
| FSEQ | FSEQ | Form.SeqNbr | Form Sequence Number |
| IGROUP | ItemGroup.Label | Item Group Definition Label | |
| IGROUPDEF | IGROUPNAME | ItemGroup.Name | Item Group Definition Name |
| IGROUPEID | ItemGroup.ExternalID | Item Group Definition External ID | |
| IGSEQ | IGSEQ | ItemGroup.SeqNbr | Item Group Sequence Number |
| ITEM | Item.Label | Item Definition Label | |
| ITEMDEF | ITEMNAME | Item.Name | Item Definition Name |
| ITEMEID | Item.ExternalID | Item Definition External ID | |
| LABANALYTENAME | LABANALYTENAME | LABANALYTENAME | Lab Analyte Name |
| ILBREAS | ILBREASON | ILBREASON | Intentionally Left Blank Reason |
| ROWWRITEDT | Datetime the row is written to the file | ||
| GUID | ROWID | SDE: Internal Item ID SFF: Unique ID for the row |
|
| LASTRUN | Last run of the SDE |
SYS_LINKS
The following table compares the SDE, SFF, and CDB Raw Export by available columns for the SYS_LINKS dataset. The column order is not the exact order within each export.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study Label | ||
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Country Name | ||
| SITECOUNTRY | Site.Country | 3-Country Code Abbreviation in SFF | |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| EGROUP | Event Group Definition Label | ||
| EGROUPDEF | EGROUPNAME | EventGroup.Name | Event Group Definition Name |
| ESEQ | EGSEQ | EventGroup.SeqNbr | Event Group Sequence Number |
| EGROUPEID | Event Group Definition External ID | ||
| EVENT | Event Definition Label | ||
| EVENTDEF | EVENTNAME | Event.Name | Event Definition Name |
| EVENTEID | Event Definition External ID | ||
| FORM | Form Definition Label | ||
| FORMDEF | FORMNAME | Form.Name | Form Definition Name |
| FORMEID | Form Definition External ID | ||
| FSEQ | FSEQ | Form.SeqNbr | Form Sequence Number |
| IGROUP | Item Group Definition Label | ||
| IGROUPDEF | ItemGroup.Name | Item Group Definition Name | |
| IGROUPEID | Item Group Definition External ID | ||
| IGSEQ | ItemGroup.SeqNbr | Item Group Sequence Number | |
| ITEM | Item Definition Label | ||
| ITEMDEF | FormLink.Name | Item Definition Name | |
| ITEMEID | Item Definition External ID | ||
| FORMCREATEDDT | Form Created Datetime | ||
| GUID | LINKID | Form.LinkID | Link ID |
| CREATEDB | LINKCREATEDBY | Link Form Created By | |
| LINKCREATEDDT | LINKCREATEDDT | Form.LinkCreatedDate | Link Created Datetime |
| FGUID | FORMID | Form.ID | Internal Form ID |
| LASTRUN | Last run of the SDE | ||
| ROWWRITEDT | Datetime the row is written to the file | ||
| ROWID | Unique ID for the row |
SYS_SUB / SYS_SUBJECTS
The following table compares the SDE, SFF, and CDB Raw Export by available columns for SYS_SUB and SYS_SUBJECTS datasets. The column order is not the exact order within each export.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| CASEBDEF | CASEBOOKVER | Subject Casebook Version | |
| STUDYID | Study Label | ||
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Country Name | ||
| SITECOUNTRY | Site.Country | 3-Country Code Abbreviation in SFF | |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| IXRSID | IXRSID | Subject IXRS ID | |
| STATUS | SUBSTATUS | Subject.Status | Subject Status |
| SUBRESTRICTED | Subject Restricted | ||
| SDVPLAN | SDVPLAN | SDV Plan | |
| DMRPLAN | DMRPLAN | DMR Plan | |
| FROZEN | FROZEN | Subject Frozen | |
| LOCKED | LOCKED | Subject Locked | |
| SIGNED | SIGNED | Subject Signed | |
| LATESTARM | LATESTARM | Latest Arm | |
| LATESTCOHORT | LATESTCOHORT | Latest Cohort | |
| LATESTSUBSTUDY | LATESTSUBSTUDY | Latest Substudy | |
| CNSNTDT | CNSNTDT | Initial Consent Date | |
| SCRDDT | SCRDDT | Screened Date | |
| SCRFAILDT | SCRFAILDT | Screen Failed Date | |
| ENRDDT | ENRDDT | Enrolled Date | |
| RDMDDT | RDMDDT | Randomized Date | |
| STARTTRTDT | STARTTRTDT | Started Treatment Date | |
| ENDTRTDT | ENDTRTDT | End of Treatment Date | |
| WTHDRWNDT | WTHDRWNDT | Withdrawn Date | |
| STARTFLLWUPDT | STARTFLLWUPDT | Started Follow Up Date | |
| LOSTFLLWUPDT | LOSTFLLWUPDT | Lost to Follow Up Date | |
| CMPLTDT | CMPLTDT | End of Study Date | |
| SUBLASTMODBY | Subject Last Modified By | ||
| LASTMODDT | SUBLASTMODDT | Subject Last Modified Datetime | |
| GUID | SYSID | Subject.ID | Internal Subject ID |
| LASTRUN | Last run of the SDE | ||
| ROWWRITEDT | Datetime the row is written to the file | ||
| ROWID | Unique ID for the row |
SYS_Q / QUERIES
The following table compares the SDE and SFF by available columns for the SYS_Q (queries) dataset. The column order is not the exact order within each export. Note, in CDB queries are available via Query Listings, which can be broken down by source or status.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study Label | Study Label | |
| STUDYNAME | Study Name | ||
| COUNTRY | Country Name | ||
| SITECOUNTRY | Site.Country | 3-Country Code Abbreviation in SFF | |
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| Subject.Status | Subject Status | ||
| EGROUP | Event Group Definition Label | ||
| EGROUPDEF | EGROUPNAME | Event Group Definition Name | |
| EGROUPEID | Event Group Definition External ID | ||
| ESEQ | EGSEQ | Event Group Sequence Number | |
| EVENT | Event Definition Label | ||
| EVENTDEF | EVENTNAME | Event.Name | Event Definition Name |
| EVENTEID | Event Definition External ID | ||
| EVENTDT | Event.Name | Event Date | |
| EVENTSTATUS | Event Status | ||
| FORM | Form Definition Label | ||
| Source Form Source | |||
| FORMDEF | FORMNAME | Form.Name | Form Definition Name |
| FORMEID | Form Definition External ID | ||
| FSEQ | FSEQ | Form Sequence Number | |
| IGROUP | Item Group Definition Label | ||
| IGROUPDEF | IGROUPNAME | Item Group Definition Name | |
| IGROUPEID | Item Group Definition External ID | ||
| IGSEQ | IGSEQ | Item Group Sequence Number | |
| ITEM | Item Definition Label | ||
| ITEMDEF | ITEMNAME | Item.Name | Item Definition Name |
| ITEMEID | Item Definition External ID | ||
| QUERYID | QUERYNAME | Query.Name | Query Name |
| GUID | QUERYID | Query.ID | Internal Query ID |
| SRCTYPE | Source Type | ||
| SRCSYS | ORIGINSYS | Origin.Sys | Source System Name / Origin System Name |
| SRCID | ORIGINID | Origin.ID | Source ID / Origin ID |
| ORIGINNAME | Origin Name | ||
| SRCUSER | Origin.User | Source User | |
| STATUS | QUERYSTATUS | Query.Status | Query Status |
| QUERYTYPE | Query Type | ||
| RESTRICTED | QUERYRESTRICTED | Query Restricted | |
| MANUAL | MANUALQUERY | Query.Category | Manual Query |
| RULEID | RULEDEF | Rule.Definition | Rule Definition Name |
| TRIGID | TRIGID | Trigger.ID | Trigger Name |
| QTEXT | FIRSTQUERYMSG | Query.FirstMessage | First Query Message |
| Query.LatestMessage | Latest Query Message | ||
| QTEXTBASE | First Query Message in the Base Language | ||
| QTEXTENG | First Query Message in English | ||
| OBSSOURCEVAL | Observed Source Value (for Quick Queries) | ||
| QUERYTEAM | QUERYTEAM | Query.Team | Query Team |
| QTFRESP | QUERYTFRESP | OpenToReply | Query time to first response from created date |
| QAGE | QUERYAGE | Query.Age | Query Age |
| QCHGDATA | QUERYCHGDATA | CausedDataChange | Did the query cause a data change |
| QOTOCL | QUERYOPENTOCLOSE | Query open to close days | |
| QUERYROWEXTERNALID | Query.RowExternalID (For 3rd party queries only) | ||
| CREATEDB | QUERYCREATEDBY | Query.CreatedBy | Query Created By |
| CREATED | QUERYCREATEDDT | Query.CreatedOn | Query Created Datetime |
| LASTCLOSEDDT | QUERYLASTCLOSEDDT | Query Last Closed Datetime | |
| ROWWRITEDT | Datetime the row is written to the file | ||
| ROWID | SDE: Internal Query ID / SFF: Unique ID for the row | ||
| Query.ClosedByCheck | |||
| LASTRUN | Last run of the SDE |
SYS_QT / QUERY_MSGS
The following table compares the SDE and SFF by available columns for the SYS_QT (query messages) dataset. The column order is not the exact order within each export. Note, in CDB query messages are available via Query Listings, which can be broken down by source or status.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| QRY.Status | Query Status | ||
| QRY.Age | Query Age | ||
| QRY.FormSource | Form Source | ||
| STUDYID | Study Label | Study Label | |
| STUDYNAME | Study Name | ||
| COUNTRY | Country Name | ||
| SITECOUNTRY | Site.Country | 3-Country Code Abbreviation in SFF | |
| SITENUM | SITENUM | Study Site Number | |
| Site.Name | Site Name | ||
| Event.Name | Event Name | ||
| Event.Date | Event Date | ||
| SUBJID | SUBJID | Subject ID | |
| Subject.Status | Subject Status | ||
| QUERYID | QUERYNAME | QRY.Name | Query Name |
| QUERYID | QRY.ID | Query ID | |
| STATUS | QUERYMSGSTATUS | QRYMSG.Status | Query Message Status |
| QRY.FormName | Form Name | ||
| QRY.ItemName | Item Name | ||
| RESTRICTED | RESTRICTEDMSG | Restricted Query Message | |
| QTEXT | QUERYMSG | QRYMSG.Text | Query Message |
| QTEXTBASE | Query Message in the Base Language | ||
| QTEXTENG | Query Message in English | ||
| TEXTDT | QUERYMSGDT | QRYMSG.CreatedBy | Query Message Datetime |
| TEXTBY | QUERYMSGBY | QRYMSG.CreatedDate | Query Message By |
| QUERYTEAM | QUERYTEAM | Query Team | |
| QUICKACT | QRYMSG.Action | Quick Action | |
| QUICKACTTYPE | QRYMSG.ActionType | Quick Action Type (for Quick Queries) | |
| SRCTYPE | Source Type (for Quick Queries) | ||
| SRCSYS | QRYMSG.OriginSystem | Source System Name | |
| SRCUSER | QRYMSG.OriginUser | Source User | |
| SRCID | QRYMSG.OriginID | Source ID | |
| QRY.RowExternalID | Row External ID | ||
| ROWWRITEDT | Datetime the row is written to the file | ||
| GUID | ROWID | SDE: Internal Query Message ID / SFF: Unique ID for the row | |
| LASTRUN | Last run of the SDE |
SYS_PD
The following table compares the SDE, SFF, and CDB Raw Export by available columns for the SYS_PD dataset. The column order is not the exact order within each export. The Protocol Deviations (PDs) operational file is only visible in the SDE/SFF folder if the PDs have been configured for the study within EDC.
| SDE | SFF | CDB Raw Export | Description |
|---|---|---|---|
| STUDYID | Study Label | ||
| STUDYNAME | Study.Name | Study Name | |
| COUNTRY | Site.CountryName | Country Name | |
| SITECOUNTRY | 3-Country Code Abbreviation in SFF | ||
| SITENUM | SITENUM | Site.Number | Study Site Number |
| SUBJID | SUBJID | Subject.Name | Subject ID |
| PDID | PDNAME | PD.ID | PD Identifier/Name |
| PDTYPE | PD Type | ||
| PDSUM | PDSUM | PD.Summary | PD Summary |
| PDDAT | PDDT | PD.DeviationDate | PD Date of Deviation |
| PDDATID | PDDTID | PD.IdentifiedDate | PD Date Identified |
| PDCAT | PDCAT | PD.CategoryName | PD Category |
| PD.CategoryLabel | PD Category Label | ||
| PDSUBCAT | PDSUBCAT | PD.SubcategoryName | PD Subcategory |
| PD.SubcategoryLabel | PD Subcategory Label | ||
| PDSEV | PDSEV | PD.SeverityName | PD Severity |
| PD.SeverityLabel | PD Severity Label | ||
| PDDESC | PDDESC | PD.Description | PD Description |
| PDSTATUS | PDSTATUS | PD.Status | Protocol Deviation Status |
| PDRES | PDRES | PD.Resolution | Protocol Deviation Resolution |
| PDRULE | PDRULE | PD.Rule | PD Rule |
| RESTRICTED | PDRESTRICTED | PD Restricted | |
| EGROUP | EventGroup.Label | Event Group Definition Label | |
| EventGroup.RepeatLabel | Event Group Definition Repeat Label | ||
| EGROUPDEF | EGROUPNAME | EventGroup.Name | Event Group Definition Name |
| EGROUPEID | EventGroup.ExternalID | Event Group Definition External ID | |
| EVENT | Event.Label | Event Definition Label | |
| EVENTDEF | EVENTNAME | Event.Name | Event Definition Name |
| EVENTEID | Event.ExternalID | Event Definition External ID | |
| ESEQ | EGSEQ | EventGroup.SeqNbr | Event Group Sequence Number |
| FORM | Form.Label | Form Definition Label | |
| FORMDEF | FORMNAME | Form.Name | Form Definition Name |
| FORMEID | Form.ExternalID | Form Definition External ID | |
| FSEQ | FSEQ | Form.SeqNbr | Form Sequence Number |
| IGROUP | ItemGroup.Label | Item Group Definition Label | |
| IGROUPDEF | IGROUPNAME | ItemGroup.Name | Item Group Definition Name |
| IGROUPEID | ItemGroup.ExternalID | Item Group Definition External ID | |
| IGSEQ | IGSEQ | ItemGroup.SeqNbr | Item Group Sequence Number |
| ITEM | Item.Label | Item Definition Label | |
| ITEMDEF | ITEMNAME | Item.Name | Item Definition Name |
| ITEMEID | Item.ExternalID | Item Definition External ID | |
| CREATEDT | PDCREATEDDT | PD.CreatedDate | Created Datetime |
| CREATEDB | PDCREATEDBY | PD.CreatedBy | Created By |
| LASTMODDT | PDLASTMODDT | PD.LastModifiedDate | Last Modified Datetime |
| LASTMODB | PDLASTMODBY | PD.LastModifiedBy | Last Modified By |
| USERMODDT | USERMODDT | PD.UserModifiedDate | User Modified Datetime |
| USERMODB | USERMODBY | PD.UserModifiedDate | User Modified By |
| LASTREAS | LASTREASON | PD.ChangeReason | Last Change Reason |
| INACBYSYS | INACTBYSYS | PD.InactivatedBySystem | Inactivated By System |
| LASTINACDT | LASTINACTDT | PD.LastInactivatedDate | Last Inactivated Date |
| FGUID | Internal Form ID | ||
| PDGUID | SYSID | PD.PID | Internal PD ID |
| LASTRUN | Last run of the SDE | ||
| SOURCE | Source of the PD (i.e., CDB, external) | ||
| ORIGINSYS | PD Origination System | ||
| ORIGINID | PD Origination ID | ||
| ORIGINNAME | PD Origination Name | ||
| ROWWRITEDT | Datetime the row is written to the file | ||
| ROWID | Unique ID for the row |
Reference Data
Reference data, unique to the SFF, helps perform lookups and serves as supplemental data to the clinical data. The following files in the SFF are considered reference data:
| File Name | SFF Availability | Description |
|---|---|---|
| LABELS | Full SFF only | Labels are exported in this file. SDE has labels in the CSV files and in the definitions folder if the Include Study Design option is selected. |
| OVERRIDE_LABELS | Full SFF only | Display Override Labels if configured in EDC Studio. |
| LOCAL_LAB_UNITS | Full and Incremental SFF | List of Units defined as part of the EDC Local Labs Module. |
| LOCAL_LAB_CODELISTS | Full and Incremental SFF | List of Codelists defined as part of the EDC Local Labs Module. |
| DELETES | Incremental SFF only | Tracks deleted data at the row level. |
Study Design Data
The SFF primarily includes study design data in the manifest.json file, within the Study Design code block, which contains the latest study design version’s information.
Study design data is included in various export formats:
- The full SFF contains label information in the LABELS CSV file (see Reference Data).
- The SDE includes study design information in the Definitions folder when a user selects the Include Study Design option. This folder contains CSV files for study design objects across all casebook versions.
- The CDB Raw Export has limited study design information in its manifest.json file, such as the data type, item label, code, and decode values if the column refers to a codelist item definition.
- The Casebook Design Export (CDE) contains comprehensive information about the study’s design properties in schedule order and in JSON format.
Shared Property Mappings
We display mappings between SDE, SFF, CDB Raw Export, and CDE shared properties. Note that SFF provides study design information in multiple places, but stores it mainly in the manifest file.
Unless specified otherwise, the mappings below refer to columns or properties from the respective outputs.
| SDE | SFF | CDB Raw Export | CDE | Description |
|---|---|---|---|---|
| manifest file: study | Study.Name | study\_name | Study Name | |
| STUDYID | LABELS.csv file | study\_label | Study Label | |
| EGROUP | LABELS.csv file | eventgroup\_def → label | Event Group Definition Label | |
| EGROUPDEF | EGROUPNAME column or manifest file: study\_design → eventgroups → name |
EventGroup.Name | eventgroup\_def → name | Event Group Definition Name |
| EGROUPEID | manifest file: study\_design → eventgroups → external\_id | eventgroup\_def → external\_id | Event Group Definition External ID | |
| EVENT | LABELS.csv file | event\_def → label | Event Definition Label | |
| EVENTDEF | EVENTNAME column or manifest file: study\_design → events → name |
Event.Name | event\_def → name | Event Definition Name |
| EVENTEID | manifest file: study\_design → events → external\_id | event\_def → external\_id | Event Definition External ID | |
| FORM | LABELS.csv file | form\_def → label | Form Definition Label | |
| FORMDEF | FORMNAME column or manifest file: study\_design → forms → name |
Form.Name | form\_def → name | Form Definition Name |
| FORMEID | manifest file: study\_design → forms → external\_id | form\_def → external\_id | Form Definition External ID | |
| IGROUP | LABELS.csv file | ItemGroup.Label | itemgroup\_def → label | Item Group Definition Label |
| IGROUPDEF | IGROUPNAME column or manifest file: study\_design → itemgroups → name |
ItemGroup.Name | itemgroup\_def → name | Item Group Definition Name |
| IGROUPEID | manifest file: study\_design → itemgroups → external\_id | ItemGroup.ExternalID | itemgroup\_def → external\_id | Item Group Definition External ID |
| ITEM | LABELS.csv file | Item.Label | item\_def → label | Item Definition Label |
| ITEMDEF | ITEMNAME column or manifest file: clinical\_data → columns → lookup\_name |
Item.Name | item\_def → name | Item Definition Name |
| ITEMEID | manifest file: clinical\_data → columns → external\_id | Item.ExternalID | item\_def → external\_id | Item Definition External ID |