Data extensions, are Salesforce Marketing Cloud name for database tables that users of marketing cloud can setup on the platform. SFMC uses under the hood a SQLServer database, whose SQL capabilities correspond to SQLServer 2005 version. Data Extensions are the objects onto which these SQL queries can be run.
Data Types
Text: is quite a straight forward data type. In SFMC, it has a maximum length of 4k characters. If you are trying to push a record with a text field than the length set in the data extension field, the Marketing Cloud API will reject the record and return the following error message:
'{"message":"Unable to save rows for data extension ID 123 ","errorcode":10006,"documentation":"","additionalErrors":[{"message":"sampltxt: The value for column [sampltxt] exceeds the column\'s maximum length.","errorcode":10000,"documentation":""}]}'
Email: The email type, is a specific field type in marketing cloud that goes through a few steps to ensure that an email is indeed “valid”. It performs three level of validation on emails: A syntax check, a mail server domain check and a blacklist check.
- Syntax: SFMC performs a syntax check on every insert into an email field, it rejects records with non-valid email syntax:
'{"message":"Unable to save rows for data extension ID 123","errorcode":10006,"documentation":"","additionalErrors":[{"message":"email: The value for column [email] is not a valid email address. Parse error [InvalidEmailAddress]","errorcode":10000,"documentation":""}]}'
Basic validation of email syntax is also something that is available in an AMPScript function.
- MxValidator:Checks that the domain name used in the email address has a valid domain server status and Mx record. This is a validation that needs to be performed using the SFMC API.
- List detective:The list detective contains a black-list exclusion of certain “bad” email addresses, these are “scrubbed” at the time of send.
Date Fields: SQLServer has some idiosyncrasy with respect to date-time format. The datetime format appears to be what is used in the backend for Salesforce Marketing Cloud data extension’s date field.
The range of acceptable values for a datetime value is in the following format YYYY-MM-DD hh:mm:ss[.nnn] from 1753–01–01 through 9999–12–31. Although according to Salesforce’s documentation “Valid date values begin after 12/31/1772 and before 01/01/10000”
One of the consequences of this long list of requirements is that when pushing data using a timezone, the minimum date that can be pushed to a Salesforce Marketing Cloud date field is 1753–01–01 CST.This converts to 1753–01–01T06:00:000Z due to the 6 hours offset between UTC and CST.
This specifically can be a hard to debug issues when an ORM converts the default minimum value in SQLServer database to a timezone different than CST.
Phone: Is meant to represent phone numbers, it accepts any text input and strips out non-numerical characters.
Numbers, Decimal and Boolean: Specific field types exist for numbers (Integers), Decimals, and Booleans values. There is no specific validation that occurs onto these fields besides a check for “truncations” for numbers/decimals and a check that the input is indeed a valid boolean field.
{"message":"Unable to save rows for data extension ID 123","errorcode":10006,"documentation":"","additionalErrors":[{"message":"boola: The value for column [boola] is not a valid boolean. Parse error [ExactTarget.Core.Validation.ValidationResult[]]","errorcode":10000,"documentation":""}]}
Locale: a combination of ISO language and country code, eg: EN-US, EN-GB
Data field properties
Beside the types, Salesforce Marketing Cloud also supports some configurations options for data fields, namely enabling a field to be part of a primary key and setting up a field to be nullable with or without default value.
Primary: Enabling primary keys within the data extension allows for upserts of records. This is done through a POST method using the Synchronous API or a PUT method using the Asynchronous API.
If using the Async API and using a POST request rather than a PUT to upsert data already in SFMC, Marketing Cloud will throw a violation or PK error.
Trying to push as a key a field that is not a primary key will result in the following exception:
{"message":"Field \'$\' exists but is not a primary key.","errorcode":10000,"documentation":""}
Nullable & default value: Marketing Cloud allows for nullable and default value fields. Not setting fields as nullable in a data extension without providing the data will result in the following error:
{"message":"Unable to save rows for data extension ID #####","errorcode":10006,"documentation":""}
Options
Sendable & Testable: As their name indicates, these options are made for sending and testing emails. Checking the sendable field is required if there is an intent to send communication directly using the data extension, likewise for testable and test sends.
Data Retention: One of the main data extension’s option is the data retention policy. There is quite a wide range of setting available, from deletion options to the way the retention period is handled.
Types of data extensions
Standard Data Extension: are the typical data store on Salesforce Marketing Cloud.
Filtered Data Extension: is essentially a filtered view on another data extension. The filtered data extensions subset data based on filtering conditions age > 18 for instance. This allows for the creation of targeted segments within the user base for management sends. Filtered data extensions can be refreshed to update their data based on any changes in the original data extension.
Random Data Extension: enable the split of subscribers contained in a data extensions across multiple other data extensions. This functionality is in particular useful for creating A/B and multivariate tests.
Shared Data Extension: are data extensions that can be shared across business units. They have specific permissions setting which allows to define to which business units, the data extension and its’ data can be shared.
