ServicesSalesforce consultingArchitectureImplementationOrg health checkDevelopmentIntegrations and APIsData migrationTesting and QAManaged supportSpecialist supportMarketing Cloud auditDeliverability
SolutionsMarketing Cloud EngagementMarketing Cloud NextSalesforce + BrazeAccount EngagementData Cloud (Data 360)Identity resolutionInsights and segmentsAgentforce and AIMCP integrationsSales and Service CloudReal-time personalisationIntegration solutionsEnterprise AI integrationBy industry
ArchitectureSalesforce CRMData 360Salesforce + BrazeAgentforceCustomer 360API-led integrationEvent-drivenMCP host, client and serverIdentity and consentMCP securityData governanceCI/CD and releasesOur public MCP server
ResourcesEngineering journalDecision guidesSolution blueprintsSalesforce productsProjects and achievementsSales ROI calculatorWhere we workChecklists and templatesPlanning toolsReadiness assessmentGlossaryAbout GeekMidstOur approachOur expertiseContact
CompanyAbout GeekMidstOur approachDelivery methodologyTrust and evidenceProjectsAchievementsBook a consultationContactWhere we workSecurity and data
Book a Consultation

Data Extension Design: Keys, Sends, Retention

How to choose primary keys, configure sendable and testable data extensions, pick field types and set retention so Marketing Cloud data stays reliable as it grows.

Type
Article
Topic
Marketing Cloud
Reading time
5 min
Updated
Author
GeekMidst Team
Reviewed by
Site owner
Contractor returning a laptop and security key to an IT engineer at an asset desk

Data extensions are cheap to create and expensive to fix. A wrong primary key turns every update into a duplicate, a sendable relationship on the wrong field sends to the wrong person, and a missing retention policy leaves years of personal data nobody needs. Three decisions matter most, and they are all easier to get right on the day you create the data extension than afterwards.

Checklist of data extension design decisions: grain, primary key, required key fields, sendable relationship, field types, retention and documentation
Decide grain, key, sendability and retention on the day you create a data extension, because each is hard to change once it holds data. View full size

Before you start

The examples use an orders model: Customers_DE (one row per customer), Orders_DE (one row per order line) and a sending data extension built from them. Names and fields are samples. Before creating anything in production, write the design in a data dictionary so others can review it.

Primary keys: what they actually do

A primary key in a data extension enforces uniqueness on one or more fields. It is also what imports and Query Activities use to decide whether an incoming row updates an existing one:

  • Imports set to "Add and Update" or "Update Only" match on the primary key.
  • Query Activities with the Update data action require a primary key and upsert on it. Append inserts rows and fails if a key would be duplicated. Overwrite clears the data extension and reloads it.
  • Lookups in AMPscript do not need a primary key, but lookups on key fields tend to behave more predictably at volume.

Guidelines that hold up in practice:

  • Choose the key from the grain of the data. One row per customer: SubscriberKey. One row per order line: OrderNumber plus LineNumber as a composite key.
  • Avoid email address as a key. People change addresses, and the same address can belong to more than one customer record.
  • Key fields cannot be nullable, so the source must always supply them. Size text keys for the longest real value with headroom.
  • Decide the key before the data extension holds data. Changing keys on a populated data extension is restricted, and the usual fix is a new data extension and a migration.

Example design: Orders_DE

FieldTypePrimary keyNullableNotes
OrderNumberText (20)YesNoFrom the commerce platform
LineNumberNumberYesNoComposite key with OrderNumber
SubscriberKeyText (50)NoNoRelates to Customers_DE
OrderDateDateNoNoStore one agreed time zone
ProductSKUText (40)NoNoJoins to a product catalogue
LineValueDecimal (12,2)NoNoUse Decimal for money, never Text
IsFirstOrderBooleanNoYesDerived by a nightly query

Sendable and testable data extensions

A data extension is sendable when you tick "Is Sendable" and define the send relationship: a field in the data extension related to Subscriber Key in All Subscribers. Marketing Cloud uses that relationship to apply subscription status, so the field must contain the same stable identifier you use as subscriber key everywhere else, typically the CRM Contact or Lead ID when Marketing Cloud Connect is in use.

Things to get right:

  • Include an EmailAddress field of type EmailAddress. It is the address the send uses for that row.
  • Relate the send relationship to a stable ID field, not to email address, unless your whole account genuinely uses email as subscriber key.
  • Keep sending data extensions at one row per subscriber. If you need several products per person, look them up at send time from a related data extension rather than duplicating the subscriber.
  • Mark a data extension testable when you want to use it for previews and test sends with realistic rows. Keep test data extensions separate from production audiences.

Not every data extension should be sendable. Reference data (product catalogues, store lists) and log tables are better left non-sendable so nobody can accidentally select them as an audience.

Field types

Pick the narrowest correct type: Text, Number, Decimal, Date, Boolean, EmailAddress, Phone or Locale. Dates stored as Text cannot be compared reliably in SQL or in journey filters, and Booleans stored as "Yes" strings make decision splits fragile. Text fields have a length; set it deliberately rather than leaving long defaults on every column.

Retention policies

Data retention on a data extension can delete individual records after a period, delete all records after a period, or delete the whole data extension and its records. A period can be counted from creation or set to a fixed date, and there is an option to reset the period on each import, which suits staging tables that are refreshed regularly.

Set retention at creation where you can, and check in your account whether it can be changed later. Retention deletes data permanently, so test on a copy first. Retention also does not remove the contact itself or their tracking history; that is a separate process covered in Contact deletion and data retention.

Four data extension patterns and their defaults

sql
/* Build a sendable audience: one row per subscriber, latest order only */
SELECT
    c.SubscriberKey,
    c.EmailAddress,
    c.FirstName,
    o.OrderNumber AS LastOrderNumber,
    o.OrderDate   AS LastOrderDate
FROM Customers_DE c
INNER JOIN (
    SELECT SubscriberKey, OrderNumber, OrderDate,
           ROW_NUMBER() OVER (PARTITION BY SubscriberKey
                              ORDER BY OrderDate DESC, OrderNumber DESC) AS rn
    FROM Orders_DE
) o ON o.SubscriberKey = c.SubscriberKey AND o.rn = 1
WHERE c.EmailAddress IS NOT NULL
Target: a sendable DE keyed on SubscriberKey, Overwrite action. Collapsing order lines to one row stops primary key violations.

Common mistakes and how to spot them

  • "Violation of PRIMARY KEY constraint" in a query. The query returns more than one row per key. Count rows per key in the source and deduplicate.
  • Sends reaching unexpected people. The send relationship points at a field that does not hold subscriber keys. Check a few rows against All Subscribers.
  • Journey filters failing on dates. The date is stored as Text. Convert it in a query into a proper Date field.
  • Data extensions nobody can explain. Add a description and a folder structure, and record the owner and purpose in your data dictionary.

Checklist

  • Grain written down and primary key chosen from it
  • Key fields required and sized correctly
  • Sendable only where needed, related to the stable subscriber key
  • EmailAddress field typed as EmailAddress
  • Field types chosen deliberately, with dates as Date and money as Decimal
  • Retention set and tested on a copy
  • Description, folder and owner recorded

Untangling an inherited data model?

If your account has hundreds of data extensions and nobody is sure which ones matter, we can review the model and propose a cleaner structure with a migration plan.

Request a data model review →

Workflow diagram

Explore how the pieces fit together. Select a step to read what happens there.

Workflow

Contact and subscriber data relationships

Open full size
Contact and subscriber data relationshipsA record arrives from CRM, an import or the API and is given a stable contact key. It is stored in a data extension keyed on that contact key, marked sendable by relating the key to Subscriber Key, and linked to the contact in an attribute group. At send time the All Subscribers status decides whether the email is sent or suppressed.Contact and subscriber data relationshipsSource systemsContact BuilderSend timeSubscriberYesNoRecord arrives: CRM, file or API. Contact data reaches Marketing Cloud from CRM synchronisation, file imports or API calls. Every route must use the same identifier for the same person.Record arrivesCRM, file or APIContact key assigned: Stable ID, never email. The contact key, which is also the subscriber key for email, should be a stable ID such as the CRM Contact or Lead ID. Email as a key creates duplicates when it changes.Contact keyassignedStable ID, never emailData extension: Primary key = contact key. Each data extension row carries the contact key in a primary key field. Use the same data type and length for the key everywhere it is stored.Data extensionPrimary key = contactkeyMarked sendable: Field relates to Subscriber Key. A sendable data extension has a send relationship that maps its key field to Subscriber Key, plus an email address field for the email channel.Marked sendableField relates toSubscriber KeyAttribute group: Linked in Data Designer. In Data Designer, attribute groups link data extensions to the contact through the key, one-to-one or one-to-many, so journeys can use the fields in splits.Attribute groupLinked in DataDesignerAll Subscribers: Status checked. At send time the subscriber status in All Subscribers is checked. New keys are added on first send, which is where a mismatched key becomes a duplicate.All SubscribersStatus checkedStatus active?. Only active subscribers receive commercial email. Unsubscribed, held and bounced statuses block the send even when the data extension row is present.Status active?Email sent: Tracked by key. The email is sent and tracking is stored against the subscriber key, so engagement links back to the same contact and CRM record.Email sentTracked by keySuppressed: Not sent. The subscriber is skipped for this send. Their status, not the data extension, decides this, so status must stay in step with CRM consent.SuppressedNot sentStartProcess stepDataSystem or platformDecisionEnd or outcomeOriginal GeekMidst explanatory diagram, not an official Salesforce reference.

Select any step in the diagram, or use Step through, to see what happens at that point and why.

Text version: every step in order
  1. Record arrives Source systemsCRM, file or API

    Contact data reaches Marketing Cloud from CRM synchronisation, file imports or API calls. Every route must use the same identifier for the same person.

    Next: Contact key assigned

  2. Contact key assigned Contact BuilderStable ID, never email

    The contact key, which is also the subscriber key for email, should be a stable ID such as the CRM Contact or Lead ID. Email as a key creates duplicates when it changes.

    Next: Data extension

  3. Data extension Contact BuilderPrimary key = contact key

    Each data extension row carries the contact key in a primary key field. Use the same data type and length for the key everywhere it is stored.

    Next: Marked sendable

  4. Marked sendable Contact BuilderField relates to Subscriber Key

    A sendable data extension has a send relationship that maps its key field to Subscriber Key, plus an email address field for the email channel.

    Next: Attribute group

  5. Attribute group Contact BuilderLinked in Data Designer

    In Data Designer, attribute groups link data extensions to the contact through the key, one-to-one or one-to-many, so journeys can use the fields in splits.

    Next: All Subscribers

  6. All Subscribers Send timeStatus checked

    At send time the subscriber status in All Subscribers is checked. New keys are added on first send, which is where a mismatched key becomes a duplicate.

    Next: Status active?

  7. Status active? Send time

    Only active subscribers receive commercial email. Unsubscribed, held and bounced statuses block the send even when the data extension row is present.

    Next: Yes: Email sent; No: Suppressed

  8. Email sent SubscriberTracked by key

    The email is sent and tracking is stored against the subscriber key, so engagement links back to the same contact and CRM record.

    End of the flow.

  9. Suppressed Send timeNot sent

    The subscriber is skipped for this send. Their status, not the data extension, decides this, so status must stay in step with CRM consent.

    End of the flow.

A record arrives from CRM, an import or the API and is given a stable contact key. It is stored in a data extension keyed on that contact key, marked sendable by relating the key to Subscriber Key, and linked to the contact in an attribute group. At send time the All Subscribers status decides whether the email is sent or suppressed. Original GeekMidst explanatory diagram; not an official Salesforce reference architecture.

Tell us what you are trying to fix.

Share your Salesforce setup, goals and constraints. A practitioner reads every message and replies with questions, first thoughts or an honest view of whether you need outside help at all.

Talk to a Salesforce Expert
Book a Consultation