Marketing Cloud Engagement | Definitions of CSV Files Output by Reports and Data Extract Activities

Data output by Reports and Data Extract Activities is in CSV format (text). The file itself does not retain database schema definitions such as Primary Keys, data types, or data lengths.

Although the source data resides in a database, the application retrieves and processes it before outputting it to CSV. Consequently, the output may not strictly reflect the database definition, and there may not always be a key that guarantees uniqueness.

Recommended Settings for Data Import

Based on the characteristics described above, consider the following settings when storing extracted data.

Importing into Data Extensions

To ensure a smooth import process, we recommend the following settings:

• Data Type: Select "Text" for all fields.

• Length: Specify a sufficiently large size.

• Required: Set all fields to "Nullable" (unchecked for Required).

• Primary Key: Set to "None".

Importing into External Databases

It is not necessary to replicate the Marketing Cloud database definition for the source data. Define fields flexibly according to your database requirements and consider performing data conversion during the import process if necessary. Note: Data types such as dates may require conversion to match the definitions of your destination database.

• Data Type: Variable-length string (e.g., VARCHAR).

• Primary Key: None.

Strategies for Adding/Updating Data Without a Primary Key

If a Data Extension does not have a Primary Key, "Add" and "Update" options are unavailable during imports or Query Activities, preventing differential updates. To achieve this, consider one of the following three approaches:

1. Consolidate files beforehand: Merge multiple CSV files from extracts or reports into a single file before importing it into the Data Extension.

2. Process data to create a Primary Key: Modify the CSV file to include a column with unique values and designate it as the Primary Key.

3. Assume a Primary Key based on existing data: Identify a combination of fields that should be unique based on data output history and set them as the Primary Key.

Note: If you choose option 3 and data violating the Primary Key constraint is generated, you must manually process the data to remove the violation.

Related Information: Primary Keys in Data Views

Similar to the content of this article, users often ask, "Which field is the Primary Key for Data Views?" Data Views do not have Primary Keys.

For more information, please refer to the following documentation:

Optimizing a SQL Query Activity