So, you’re staring down a new Salesforce Marketing Cloud (SFMC) project. Your client, bless their heart, hands over a monster Excel file—40 columns, probably more—and the classic directive: “Just import this.” Your team, likely bright-eyed and bushy-tailed, churns out a Data Extension (DE) that’s a spitting image of that spreadsheet. Fast forward two weeks, and it’s a dumpster fire: emails won’t send, relationships with other DEs are tangled messes, and some critical field just refuses to import because the data type is, you guessed it, wrong.
Look, I’ve been covering this Silicon Valley circus for two decades, and this little dance is as old as dirt. It’s the same old song and dance, just with different buzzwords. The problem isn’t the import; it’s the utter lack of foresight. The real fix? Four decisions, made before you even think about clicking that ‘create’ button.
The ‘Sendable’ Trap
First up, the biggie: Is this DE going to send emails? If the answer is even a sniff of ‘yes,’ you must tick the ‘Is Sendable’ box in its properties. And here’s the kicker: it needs an EmailAddress field and a Subscriber Key relationship. Don’t get this wrong. Trying to tack on ‘Is Sendable’ after the fact? Annoying. Trying to wrangle a Subscriber Key relationship on an existing DE packed with data? That’s a special kind of hell you don’t want to visit.
Then there’s the ‘Lookup DE’. Think of these as your reference libraries—sales rep lists, product catalogs, store locations. Stuff AMPscript happily sips from at send time. These aren’t sendable, and they don’t need an EmailAddress field. Simple enough, right? Except most folks forget to decide before they start building.
The ‘Primary Key’ Predicament
This is where things get really dicey. Your Primary Key is the unique fingerprint for each record. It has to be unique. Absolutely, positively, unequivocally unique. For sendable DEs, ditch the EmailAddress—people change emails, and one person might have half a dozen. Use a system ID, like CustomerID or MemberID. For lookup DEs, sure, use the natural ID of the thing you’re looking up—SalesRepID, ProductID. But mess this up, and you’ll spend your days chasing ghosts.
Nullable No-Nos and Data Type Disasters
Next, let’s talk about what’s required. ‘Non-nullable’ means a field must have a value. If it’s blank on import, the whole darn row gets rejected. EmailAddress and Primary Key? They should almost always be non-nullable. Your birthday or phone number fields? Those are often nullable. Why? Because source data isn’t always perfect. Surprise! Importing a mountain of rows with missing required fields and then scratching your head when only half the data shows up? Classic first-week mistake. Happens to the best of us, which is why we’re talking about it.
And the data types themselves! Oh, the places you’ll go wrong.
- Text: For names, mixed alphanumeric IDs, addresses. Basically, anything that’s not purely a number or date.
- Number: For integer IDs, point balances. Simple, clean.
- EmailAddress: SFMC’s got a special magic wand for this one, and it validates. Use it for your primary email.
- formatDate: For birthdays, expiry dates. But watch out – the format must match on import.
- Boolean: For flags like
IsVIP,HasPurchased. Yes/No, True/False. - Decimal: For monetary amounts, anything with a fractional component. Handy for prices.
I’ve seen import failures galore because someone tried to shove ORD-00123 into a Number field. The letters, people! The letters break it. Use Text when there’s any doubt.
The “Where’s My DE?” Mystery
This one’s a doozy for beginners. You go to send an email, and your shiny new DE just isn’t showing up in the audience picker. Usually? You forgot to tick ‘Is Sendable’ and configure the ‘Send Relationship’ in its properties. Thirty seconds of forethought at creation time saves an hour of head-scratching later.
The Identity Crisis: Email vs. Subscriber Key
Here’s a subtle but pernicious problem. A customer updates their email. Your import runs. Instead of updating the existing record, SFMC—if you’ve set it up wrong—adds a new row with the new email. Suddenly, one person is two records. Your audience counts are lying to you. Use a stable system ID as your Subscriber Key. Email is an attribute, a changeable characteristic. A Subscriber Key is your actual identity within the system. Don’t conflate the two.
The Dual Email Dilemma
SFMC plays a bit of a diva here: it only allows one field of type EmailAddress in a single DE. So, if you have your primary EmailAddress and a SecondaryEmail field, and you naively set them both to EmailAddress type, your sends from that DE will likely fail. Completely. Nobody gets the email. One of them has to be plain Text.
The Truncation Tribulation
Setting FullName to 50 characters might seem safe. Until you get a customer with a name longer than that. Then what? Data integrity is silently eroding, or worse, you’re rejecting perfectly good customers. SFMC can actually process imports faster if field lengths are accurately sized. Don’t aim for the median; aim for the longest realistic value. Better to allocate a bit more space than to lose data or customers.
The ‘Create from Existing’ Shortcut
When you need a DE that’s basically a clone of another one—say, a Customer_Segment_VIP DE that shares most fields with your Customer_Master DE—don’t start from scratch. In Content Builder, use ‘Create from Existing’. It copies the entire schema: field names, types, lengths, nullable flags. You just tweak the fields that need to be different. Way faster, way less error-prone. (And for the record, ‘Create from Template’ is for SFMC’s predefined schemas, not your own custom DEs. Different tools for different jobs, folks).
So, to recap: Four decisions before you click ‘Create’. Purpose (sendable/lookup)? Primary Key choice? Nullable fields? Data types? Thirty seconds of actual thought saves you days of debugging. When you inherit a project littered with broken DEs, nine times out of ten, the root cause is one of these four being fundamentally wrong from the get-go. It’s not rocket science; it’s just engineering.
