Designing a Data Extension in SFMC: The Four Decisions First

Almost every new SFMC engagement starts the same way. The client sends a 40-column Excel file of customer data and says "import this into SFMC." The team creates a Data Extension that mirrors the Excel columns. Two weeks later the DE can't send email, can't relate to other DEs, and one field won't import because the data type is wrong.

The fix is four decisions made before the DE is created, not after.

Decision 1: What is this DE for?

• Sendable DE: will be used to send email. Tick "Is Sendable" in Properties. Must have a field of type EmailAddress and a Subscriber Key relationship.
• Lookup DE: stores reference data that AMPscript looks up at send time (sales rep list, product catalog, store locations). Not sendable, no EmailAddress required.

Pick before creating. Adding Is Sendable after the fact is possible but annoying; getting the Subscriber Key relationship right on an existing DE with data is error-prone.

Decision 2: What's the Primary Key?

Primary Key is the column that uniquely identifies each record. Rules:

• Must be unique across all records in the DE.
• For sendable DEs, use a system ID like CustomerID or MemberID. Don't use EmailAddress - one person can have multiple email addresses, and some people change addresses.
• For lookup DEs, use the natural ID of the entity (SalesRepID, ProductID).

Decision 3: Which fields are nullable?

• Non-nullable means the field is required. If an imported row is missing it, the row is rejected.
• EmailAddress and Primary Key should almost always be non-nullable.
• Demographic fields (address, phone, birthday) are often nullable because source data isn't always complete.

Importing a file full of rows with missing non-nullable fields and wondering why half the rows vanished is a standard first-week mistake.

Decision 4: Are the data types right?

Data typeUse forTextNames, mixed alphanumeric IDs, addressesNumberInteger IDs, point balancesEmailAddressMust be used for the email column - SFMC validates formatDateBirthdays, expiry dates (format must match on import)BooleanFlags like IsVIP, HasPurchasedDecimalMonetary amounts, anything with a fractional component

Classic import failure: OrderNumber values like ORD-00123 imported into a Number field. The letters break it. Use Text.

Four mistakes we still see on audits

Forgot to set Is Sendable

DE doesn't appear in the audience picker when you go to send. Edit Properties, tick Is Sendable, configure the Send Relationship. Avoidable if you decide at creation time.

Using EmailAddress as Primary Key

Customer updates their email. Import runs. The old row with the old email still exists. New row with the new email is added. One person is now two records. Audience counts lie.

Use a stable system ID. Email is an attribute, not an identity.

Two EmailAddress-type fields in one DE

SFMC does not allow two fields with data type EmailAddress in the same DE. If you have both EmailAddress and SecondaryEmail typed as EmailAddress, sends from this DE fail completely - no one gets the email.

One of the two must be typed as plain Text.

Field length too short

Setting FullName length to 50 characters seems reasonable until a customer with a longer name gets truncated or rejected. Two consequences:

1. Data integrity: you silently lose data.
2. Import speed: SFMC can process imports faster when field lengths are accurately sized.

Size fields to the longest realistic value, not the median.

The Create-from-Existing shortcut

When you need a new DE with a structure similar to an existing one (e.g. Customer_Segment_VIP with most fields from Customer_Master), use Create from Existing in Content Builder > Data Extensions. It copies the full schema: field names, types, lengths, nullable flags. You only add or remove the fields that need to differ.

Create from Template is for SFMC's preset schemas (Triggered Send DE, Tracking DEs, etc.), not your own DEs. Different tool.

Takeaway

Four decisions before you click Create: purpose (sendable/lookup), Primary Key, nullable fields, data types. Thirty seconds of thinking saves rebuild work later. When you inherit a project with half-broken DEs, the common root cause is always one of these four being wrong.

---

Starting an SFMC project with messy source data? Our Salesforce team designs Data Extensions and data models on production engagements. Get in touch ->

See our full platform services for the stack we cover.