SFMC — now branded Marketing Cloud Engagement — is Salesforce's B2C, high-volume, subscriber-centric platform for email, SMS, push, ads and journey orchestration. It descends from ExactTarget (acquired 2013), so it runs on its own stack: its own data model, its own SQL flavour, AMPscript and SSJS for scripting, and its own REST and SOAP APIs. It is not built on the core CRM platform — integration to Sales or Service Cloud goes through Marketing Cloud Connect.

🛠️ Open the App Switcher (waffle, top-left) in your GAP org and say one sentence of purpose for every Studio and Builder you see.

Studios execute on channels: Email Studio for email, Mobile Studio (MobileConnect SMS, MobilePush, GroupConnect), Advertising Studio for ad audiences, Web Studio/CloudPages for landing pages. Builders define cross-channel infrastructure: Content Builder for assets, Journey Builder for orchestration, Automation Studio for scheduled batch work, Contact Builder for the contact model and DEs, Analytics Builder for reporting, plus Einstein as the AI layer. My shorthand: Builders define the pieces, Studios put them to work on a channel.

🛠️ Draw the two-column Studios-vs-Builders map on paper from memory, then verify it against your org's App Switcher.

No — it's the back-office batch engine, the odd one out in the naming. It runs scheduled or file-drop-triggered workflows chaining activities: SQL Query, Import, File Transfer, Data Extract, Script and send-related steps. Journey Builder handles one-to-one, event-driven orchestration; Automation Studio handles bulk data movement and segmentation on a schedule. In practice most real data work — nightly loads, rollups, audience builds — lives here.

🛠️ Open Automation Studio ▸ Overview in your org and narrate one production automation, naming each activity type in sequence.

Four to know. Einstein Send Time Optimization picks each contact's best send hour from roughly 90 days of engagement — you drop an STO activity before a journey send. Einstein Engagement Scoring buckets subscribers into personas — Loyalists, Window Shoppers, Selective Subscribers, Dormant — with predicted open, click and unsubscribe likelihood. Einstein Content Selection picks the best asset per contact at open time. Einstein Copy Insights scores subject-line language. Senior caveat: all are engagement-trained, so Apple MPP-inflated opens degrade the open-based models.

🛠️ Open the Einstein Engagement Scoring dashboard via the App Switcher and note the four persona buckets and their share of your audience.

A tenant is your account's slice of the multi-tenant Marketing Cloud infrastructure; each tenant lives on a stack — an instance like s7 or s50. When a panel asks 'what stack are you on', that's what they mean. Critically, API endpoints are tenant-specific: https://MC<tenant-subdomain>.rest.marketingcloudapis.com, with matching .soap. and .auth. hosts. The legacy generic exacttargetapis.com endpoints are retired — always take the endpoint from your Installed Package.

🛠️ Open Setup ▸ Apps ▸ Installed Packages and read your tenant subdomain off an API Integration component's REST/SOAP endpoints.

A Business Unit is a logical partition of the Enterprise account controlling four things: data segregation, brand identity — From-name, From-address, sender profiles — user access, and sending reputation. Each BU has its own local DEs, content, and unsubscribe scope, while a parent can share assets down. At GAP we effectively ran a BU per brand — Gap, Old Navy, Banana Republic, Athleta — so brands stay isolated while sharing common infrastructure from the parent.

🛠️ Click the BU dropdown at top-right of your org and name what changes as you switch — data, content, sender identity, tracking.

Enterprise 2.0 is the modern top-level account hierarchy: an Enterprise tenant with a parent, top-level Business Unit and child BUs beneath it. Its defining capability is parent-to-child sharing — Shared Data Extensions, shared Content Builder assets and templates, shared publication and suppression lists — where children reference one live copy rather than receiving duplicates. Legacy hierarchies like Enterprise 1.0, Lock and Publish, and Agency editions exist historically, but new orgs are provisioned as Enterprise 2.0.

🛠️ Open the Shared Data Extensions folder in a child BU and confirm a parent DE appears as a reference, not a local copy.

Shareable from parent to children: Shared Data Extensions, shared Content Builder assets and templates, and shared publication and suppression lists — always referenced, one live copy, one CustomerKey. Siloed per BU: local DEs and content, Sender and Delivery Profiles, Send Classifications, From-identity, sending IP and reputation, tracking data, and role assignments. Users exist at account level but access is granted per BU. Rule of thumb: data and content can cascade; brand identity, reputation and compliance scope never do.

🛠️ List your org's shared assets from the parent BU and label each one 'reference' — then name three things you know stay per-BU.

No — a shared DE is referenced, not copied. There's one physical table with one CustomerKey living in the parent; every child reads that live object, so a parent edit or data change reflects everywhere instantly. But the moment a child sends using it, the send job, tracking rows and attribution belong to the child's context — data views populate in the sending BU, and reputation stays per-BU. Definition in the parent, execution in the child.

🛠️ Update one row in a parent shared DE and immediately view it from a child BU to prove there is a single live copy.

Every Business Unit has a MID — its numeric Member ID. In the SOAP API you act on a specific BU by passing the MID inside the ClientID element; with REST enhanced packages you request a token scoped to that MID by passing account_id in the token request. That's MID context switching — one integration operating across BUs by changing context. The enterprise itself has an EID; MIDs identify the children under it.

🛠️ Open Setup ▸ Company Settings ▸ Account Settings and read off your BU's MID, then compare it to the parent EID.

One child BU per brand — Gap, Old Navy, Banana Republic, Athleta — under a parent governance BU. The parent holds shared assets: enterprise suppression lists, master templates, shared reference DEs. Each brand BU owns its sender identity, its dedicated IP and domain via its Sender Authentication Package, its unsubscribe scope, and its user access. That gives brand isolation with central governance: one physical person can exist in multiple brand BUs, resolved at enterprise level by a single Contact Key.

🛠️ Sketch the Enterprise ▸ Parent BU ▸ four brand BUs tree and annotate what lives at each level — shared assets up, sender identity down.

At enterprise level it's one person, resolvable to a single Contact Key. But each brand sends from its own BU, so they're separately suppressible per BU: a Gap unsubscribe is a BU-level opt-out and Old Navy is untouched. I'd deliberately choose BU-level subscription management so a brand opt-out never cascades across the portfolio — only a master, All-Subscribers-level unsubscribe should suppress every brand. And a true 'delete me everywhere' request is a Contact Delete, not an opt-out.

🛠️ Frame: tell it as the cross-brand story — one Contact Key at enterprise level, independent per-BU suppression, and name which unsubscribe level cascades.

Users exist once at account level but are granted access and roles per Business Unit — someone can be Administrator in Old Navy and a read-only Analyst in Gap; effective access is user times BU times role. Standard roles ship out of the box — Administrator, Content Creator, Analyst, Data Manager, channel managers — and you compose custom roles for least privilege. An Enterprise admin manages the whole account: creating BUs, cross-BU users, sharing, sender authentication. A BU admin is scoped to their own BU.

🛠️ Open Setup ▸ Users, pick a user, and review their Business Unit assignments and per-BU roles.

The BU dropdown. Data is BU-scoped, so the most common cause is standing in the wrong Business Unit — the DE exists, just not where they're looking. I check which BU it was created in, whether it's local or in the Shared Data Extensions folder, then folder permissions on their role. Only after ruling out scope do I consider real deletion — remembering that a DE retention policy can silently remove rows or the entire DE on schedule.

🛠️ Switch BUs via the top-right dropdown and re-run the search before escalating any 'missing data' report.

The parent is the governance layer: enterprise suppression lists, shared reference and audience DEs, master templates and shared content blocks, plus account-wide setup — SAP domains, users, Installed Packages, contact deletion configuration. Children hold execution: local audience DEs, brand content, sender and delivery profiles, sends and their tracking. The principle: anything two or more brands must agree on lives at the top and is shared down; anything carrying brand identity or reputation lives in the child.

🛠️ Audit your parent BU's Shared Data Extensions folder and confirm nothing brand-specific has crept upward.

A Contact is the person in the Contact model — the cross-channel single view across email, SMS and push — identified by Contact Key and visible in Contact Builder's All Contacts. A Subscriber is that same person specifically in the email channel, identified by Subscriber Key and living in Email Studio's All Subscribers. Historically Subscriber came first, in the ExactTarget era, and Contact was layered on for multi-channel. Best practice keeps both keys the same stable value so the two views line up as one identity.

🛠️ Open Contact Builder ▸ All Contacts and Email Studio ▸ Subscribers ▸ All Subscribers side by side and compare the same person's record.

Subscriber Key is the business-chosen unique identifier in Email Studio — the dedup and identity value for email. Contact Key is the same concept in the Contact model, spanning all channels. They should be the same value: a stable, system-owned business ID like a customer or loyalty ID, never the email address. The key is the join spine tying together All Subscribers, every sendable DE's send relationship, the SubscriberKey column across data views, and Journey Builder contact entry.

🛠️ Trace one customer's key from a sendable DE row to their All Subscribers record and confirm what your org actually keys on.

Three reasons. One person can own multiple addresses, so you mint duplicates. Addresses change, so you lose tracking history and continuity the moment someone updates their email. And every duplicate inflates the billable contact count. With a stable customer or loyalty ID as the key, email becomes just a changeable attribute on one persistent record — identity survives an address change, history stays attached, dedup stays clean.

🛠️ Frame: give the three-beat answer — duplicates, churned history, billing — then land the fix: stable business ID with email as an attribute.

Two you own: Subscriber Key — business-chosen, the Email Studio identity and dedup value — and Contact Key — business-chosen, the cross-channel identity in the Contact model. Two are system-generated: Subscriber ID, the internal numeric primary key of the subscriber record that surfaces in data views, and Contact ID, its Contact-model counterpart. The one rule preventing most pain: Subscriber Key and Contact Key must be the same value, or email identity and cross-channel identity split into duplicate, billable contacts.

🛠️ Draw the 2x2 — model (email versus contact) by setter (you versus system) — and fill in the four names from memory.

No — Subscriber and Contact Keys are case-insensitive: ABC123 and abc123 resolve to the same record. That's precisely why the platform rejects a raw 15-character Salesforce ID — those are case-sensitive by design — with a CaseSensitiveSalesforceID validation error, forcing the 18-character case-safe ID instead. It matters because two 15-character IDs differing only in case would silently merge into one contact and corrupt identity and tracking.

🛠️ Convert any 15-character Salesforce ID to its 18-character form (a CASESAFEID(Id) formula field in CRM) before it ever maps to a key.

Not in normal operations — treat it as effectively immutable. There's no in-place edit: writing the same person under a new key just creates a duplicate identity and orphans all prior tracking, list membership and status. Re-pointing an org's keys — say moving from email-as-key to loyalty ID — is a formal, Salesforce-assisted Subscriber Key migration that re-associates historical engagement and can involve downtime. So the honest answer: you don't change keys, you migrate them — rarely and deliberately.

🛠️ Frame: say 'effectively immutable' first, then name the formal migration path — the panel is testing whether you'd casually re-key production.

All Subscribers, in Email Studio, is the email-channel roster: every subscriber ever sent to or imported, each with a status — Active, Bounced, Held or Unsubscribed. All Contacts, in Contact Builder, is the cross-channel roster: the full Contact-model population including SMS-only, push-only, and records created via CloudPages or API with no email channel at all. So every Subscriber is a Contact, but not every Contact is a Subscriber — and it's the contact population your contract bills against.

🛠️ Compare the counts in Contact Builder ▸ All Contacts and Email Studio ▸ All Subscribers and explain the gap between the two numbers.

Contact count. The contract licenses a number of contacts, and every contact across all Business Units counts toward the allocation: bounced ones, held ones, channel-less ones, orphaned test records — everything in All Contacts. Exceeding it means overage charges. That's why data hygiene is a financial lever, not just deliverability: purging dead records, deleting orphans, and avoiding email-as-key directly cut spend. Super Messages are the other consumable, but contact count is the headline metric at a large retailer.

🛠️ Pull the total in Contact Builder ▸ All Contacts and compare it against your contracted allocation before proposing any cost work.

Contacts created through non-email paths — CloudPages form submits, MobilePush SDK registrations, API writes, journey entries — that never became email subscribers. They're invisible in Email Studio because no subscriber record exists, but fully present and billable in All Contacts. At retail scale they accumulate silently: test submissions, bot traffic, abandoned registrations. I audit by comparing All Contacts against All Subscribers and the channel rosters, then schedule Contact Delete for confirmed dead records to reclaim billable slots.

🛠️ Run the audit: identify All Contacts records absent from All Subscribers and every mobile list, and rank the sources creating them.

Cost tracks contact count, so I attack the count. Purge hard-bounced and long-dormant records on a defined policy; delete orphaned and test contacts via Contact Delete — that actually frees slots, unlike unsubscribing; fix email-as-key so address changes stop minting duplicates; and keep one identity per person by aligning Contact Key and Subscriber Key. Then govern intake with validated keys at every entry point. Net effect: a smaller billable population, better deliverability, cleaner attribution.

🛠️ Frame: lead with 'billing equals contact count', then give the levers in order — purge, delete orphans, fix keys, govern intake.

You delete by Contact Key from Contact Builder ▸ All Contacts, or via API and automation for bulk — after enabling it in Contacts Configuration. A suppression period then applies: default two days, configurable zero to thirty. During it the contact receives nothing and the key can't be re-uploaded; set zero to queue deletion immediately. Processing is asynchronous and deprioritized behind sends, imports and queries, so it's never instant. The delete is hard — no recovery — and frees a slot against the contracted contact count.

🛠️ Open Contact Builder ▸ Contacts Configuration and read your org's current suppression-period setting before quoting any erasure SLA.

Every record on the All Subscribers roster carries one of four statuses. Active — mailable. Bounced — delivery failed and the record is on the bounce path. Held — deemed undeliverable; the platform has stopped attempting. Unsubscribed — opted out and suppressed regardless of audience. The senior point: status is evaluated at send time and always overrides list or DE membership — being in my audience DE is necessary but never sufficient. Compliance and deliverability state live on the roster, not in my campaign tables.

🛠️ Open Email Studio ▸ Subscribers ▸ All Subscribers, filter by each status, and read one record's properties for its bounce and unsub history.

A hard bounce — a permanent failure like a nonexistent address — marks the record Bounced immediately. Soft bounces — full mailbox, transient server issues — are retried for roughly 72 hours before counting as a bounce. The record goes Held after three consecutive sends bounce with the first and last at least fifteen days apart; from Held, the platform stops attempting delivery entirely. Held and Bounced records still occupy billable contact slots — bounce hygiene is cost hygiene too.

🛠️ Open a Bounced subscriber's properties in All Subscribers and read the bounce count and dates driving the status.

That being in my audience DE never guarantees delivery. At send time the platform resolves every row against All Subscribers and applies master state first: Unsubscribed, Bounced and Held records are dropped no matter which list or DE they sit in, and master or BU-level unsubscribes beat any list-level opt-in. My sendable DE proposes an audience; the roster disposes. It's also the first place I look when delivered counts come up short of audience counts.

🛠️ Reconcile one send: compare the audience DE row count to the delivered count and attribute the gap to statuses and suppressions before calling it a defect.

Three levels. List or publication-list level: opted out of one topic — say Promotions — while still receiving others. Business Unit level: opted out of one brand; other BUs are unaffected. All Subscribers, master level: globally suppressed across the entire enterprise account. Which one applies is governed by subscription management — effectively which list the unsubscribe is processed against. At a multi-brand org I default to BU-level so one brand's opt-out never bleeds across the portfolio.

🛠️ Map your org's unsubscribe links: identify which publication list each email footer processes against, per BU.

A publication list represents a mail topic or category — Promotions, Newsletter, Order Updates — used to track opt-outs per topic rather than globally. You associate sends with a publication list; when someone unsubscribes, the opt-out records against that list, so they stop getting that category while staying Active for everything else. That's the machinery behind a retail preference center: granular consent per program, fewer total-loss unsubscribes, and clean per-topic compliance reporting.

🛠️ Open Email Studio ▸ Subscribers ▸ Publication Lists and map each list to the send programs actually using it.

Three suppression mechanisms with different natures. A publication-list unsubscribe is the subscriber's stated preference — it changes their list-level state and persists. A suppression list is an operational never-send I control — keys or addresses excluded at send time without touching their All Subscribers status. An exclusion script is AMPscript evaluated at send time for dynamic, per-send logic — say excluding LoyaltyTier == 'Inactive' — with no state change at all. Preference, operational block, dynamic filter — and each reports differently for compliance.

🛠️ Frame: answer as 'preference versus operational block versus dynamic logic', then name each one's status effect — persistent, none, none.

Every account ships with system defaults: the Profile Center, where subscribers edit their profile attributes, and the Subscription Center, listing the publication lists they can opt in or out of — the default unsubscribe link routes through these. They're brandable per BU, or you replace them with a custom CloudPages preference center that writes preferences yourself. The senior point: whatever you build must still process the opt-out at the correct scope — list, BU or master — within compliance timelines.

🛠️ Send yourself a proof, click the unsubscribe link, and note which publication lists the default subscription center exposes.

Lists are the legacy model: flat subscriber rosters with a rigid, account-wide profile and preference attribute schema — fine for small simple programs, but slow at scale and impossible to relate. Data Extensions are relational tables I define: my columns, types, primary keys and retention — holding audiences or any reference data, relatable in Data Designer, queryable with SQL, and far faster at volume. At GAP everything ran on DEs; the practical uses left for lists are small programs and the publication and suppression machinery.

🛠️ Frame: concede where lists survive — simple programs, subscription tracking — then land 'DEs are the modern default' on schema, scale and relationships.

I frame it as two true storage types plus variants. Storage: Standard — a plain table for reference data like products or barcodes — and Sendable — carrying a send relationship mapping a field to Subscriber Key so you can email it. The variants describe creation or sharing: Shared, a parent-BU object referenced by children; Filtered, generated by filtering one source DE and static by default; Synchronized, a read-only mirror of CRM objects via Marketing Cloud Connect. A 'Salesforce Data Extension' is MC Connect's journey staging table — a separate special case people conflate.

🛠️ Frame: group them as 'two storage types, three variants, one special case' — panels reward the taxonomy more than the flat list.

Sendable means the DE carries a send relationship: one field — typically SubscriberKey — is mapped to Subscriber Key, and a column is designated as the email address. That mapping tells the platform who each row is, so it can dedup against All Subscribers, honor unsubscribes and suppressions, and write tracking keyed to the person. Non-sendable DEs are reference tables — my barcode and offer tables — that emails look up into at render time but never send to.

🛠️ Open any audience DE's properties and read its send relationship aloud — which field relates to Subscriber Key and which column is the email.

They're static by default — the gotcha most people miss. A Filtered DE is generated by applying point-and-click criteria to a single source DE, and after creation it reflects that moment in time; it only updates when refreshed manually or via an automated refresh. It also can't join across DEs or aggregate. So it's fine for a genuinely one-off, single-source segment a marketer self-serves; anything relational, recurring, or that must stay current belongs in a scheduled SQL Query Activity instead.

🛠️ Demonstrate the staleness: add a qualifying row to the source DE and show the Filtered DE count doesn't move until you refresh it.

Two different Marketing Cloud Connect artifacts. A Synchronized DE is a continuous, read-only mirror of a CRM object — Contact, Lead, custom objects — created through Synchronized Data Sources in Contact Builder; it's prefixed, non-sendable, and you build sendable DEs off it. A Salesforce Data Extension is the staging DE MC Connect auto-creates when a journey uses a Salesforce Data Event entry — it holds the records the event fires in. Mirror versus journey staging; conflating them is a classic interview trap.

🛠️ Open Contact Builder ▸ Data Sources ▸ Synchronized and identify which CRM objects and fields your org mirrors today.

Text — up to 4000 characters. Number — integer only, roughly plus-minus 2.1 billion, no decimals. Decimal — fractional, precision up to 38 and scale up to 17; use it for money. Then Date, Boolean, Phone, Locale, and EmailAddress — the validated type required behind a sendable DE's email mapping. The classic trap: importing 19.99 into a Number field fails because Number can't hold decimals — monetary fields must be Decimal from day one, since changing a populated field's type later is painful.

🛠️ Audit one production DE's schema and flag any price or amount column typed as Number or Text instead of Decimal.

Per-DE retention has three modes: delete records only — rows purge on a rolling period or fixed date, DE remains; delete all records and the data extension; or delete the entire data extension outright. Options include the period unit and 'reset retention on import', which restarts the rolling clock on every load when on. Two senior warnings: these deletes are hard — no recycle bin — and retention on a DE feeding an active journey or triggered send can purge rows mid-flight and break personalization. It's entirely separate from data-view retention.

🛠️ Review retention on every DE feeding a live journey and confirm each window exceeds the journey's longest path.

Data Designer is where the contact data model is defined: you link Data Extensions to the contact through Attribute Groups — logical clusters like Loyalty, Purchases, Demographics — joined on Contact Key with declared cardinality, one-to-one or one-to-many. That model is what Journey Builder decision splits and entry sources traverse to reach related attributes. For send-time personalization, developers usually bypass traversal and use AMPscript lookups for row-level control — so I keep the designed model lean and purposeful, not a map of every DE.

🛠️ Open Contact Builder ▸ Data Designer and walk one attribute group: name the linkage field, the cardinality, and which journey consumes it.

A Population is the foundational DE in Data Designer that defines the universe of contacts — the table that says who exists — with attribute groups hanging off it on Contact Key. Cardinality is the senior nuance: one-to-one relationships bind cleanly in Journey Builder, one related row per contact. One-to-many — a contact with many orders — can't be referenced as if singular; you either bind to the modeled cardinality carefully or pre-flatten to one row per contact before entry. Mis-modeling 1:M as 1:1 yields wrong or empty personalization.

🛠️ Identify your org's Population in Data Designer and verify each attribute group's declared cardinality against the actual data.

Read-only system tables holding tracking and roster data, prefixed with an underscore — _Subscribers, _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Complaint, _Job, _ListSubscribers, _BusinessUnitUnsubscribes and more. They're invisible in the DE list and queryable only through a SQL Query Activity in Automation Studio — no UI browse, no direct REST retrieve. They're the raw event layer beneath the Tracking UI, keyed by SubscriberKey and JobID, and the foundation of any serious engagement, RCA or suppression build.

🛠️ List the data views you've actually queried at GAP with one production use case each — panels probe real usage, not the catalog.

_Job is send-job metadata — one row per send job: JobID, email name, subject, send classification, scheduled time. _Sent is event grain — one row per subscriber per send: SubscriberKey, JobID, ListID, BatchID, EventDate. JobID is the join key between them. More broadly: roster views like _Subscribers and _ListSubscribers hold current state, while event views — _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Complaint — hold history. State answers 'who and what status'; events answer 'what happened and when'.

🛠️ Frame: describe the two grains — job-level metadata versus per-subscriber events — and name JobID as the key connecting them.

Two separate policies — conflating them is a credibility risk. Data views retain roughly six months, about 180 days, of events — that caps what SQL against _Open or _Click can ever see. The broader engagement data behind Email Studio Tracking and Analytics Builder reports is retained 730 days — two years — effective June 16, 2025, after Salesforce first announced a 180-day cut and then revised upward. So: reports two years, data views still about six months — anything longer means persisting events to your own DE.

🛠️ Frame: give both numbers with the June 2025 date — 'reports 730 days, data views roughly 180 days, different policies' — before the panel can conflate them.

Apple Mail Privacy Protection, since iOS 15 in 2021: Apple's proxy pre-fetches images, firing machine 'opens' for Apple Mail users whether or not a human opened. _Open — which holds unique and repeat opens — is inflated, so open-based engagement segmentation is broken: a 'sent but never opened' suppression set now mixes genuine non-openers with Apple users, and open-trained models drift. I shift decisions to clicks and conversions — A/B winners on click-through, re-engagement on click recency — and treat opens as directional only.

🛠️ Audit any non-opener suppression or re-engagement logic in your org and re-cut it on click and conversion recency instead of opens.

Engagement is the classic ExactTarget-lineage platform I work on — its own stack, DEs, AMPscript, Journey Builder. Growth, launched February 2024, and Advanced, launched late 2024, are a newer marketing-automation line built natively on the core Salesforce platform and Data Cloud — Flow-based, Data-Cloud-segmented, aimed at SMB and mid-market; Advanced adds capabilities like Path Experiment testing. They're different products, not versions of each other. If a panel asks 'have you used the new Marketing Cloud', I clarify which one they mean.

🛠️ Frame: say 'Engagement equals ExactTarget lineage; Growth and Advanced are core-platform-native on Data Cloud' — then state plainly which one your experience covers.

Data Cloud — formerly CDP and Genie, now marketed under the Data 360 branding — is Salesforce's customer data platform on the core platform: it ingests data from many sources, unifies it into single profiles via identity resolution, computes segments and calculated insights, and activates them to channels — including triggering Engagement journeys through the API entry event. The positioning line: Data Cloud is the unification and segmentation layer; Engagement is the execution layer. They integrate; they're not the same product, and Data Cloud isn't 'the new SFMC'.

🛠️ Frame: position it in one line — 'Data Cloud unifies and segments; Engagement executes' — and name journey activation as the integration point.

Marketing Cloud Account Engagement — Pardot — is the B2B marketing-automation product built on the core Salesforce platform: Lead- and Contact-centric, sales-aligned nurture, scoring and grading, lower volume. Marketing Cloud Engagement is B2C: high-volume, Subscriber- and Contact-Key-centric, on its own stack, with cross-channel journeys. The one-line disambiguator: Account Engagement nurtures leads for a sales team; Engagement runs consumer-scale lifecycle marketing. Panels ask this to verify you know which Marketing Cloud your certification and experience actually cover.

🛠️ Frame: answer B2B-versus-B2C first, then platform stack — core versus own — then identity model: Lead/Contact versus Subscriber Key.

App switcher ▸ Audience Builder ▸ Contact Builder ▸ Data Extensions ▸ Create, choose Standard Data Extension. Properties: Name, External Key — blank auto-generates a GUID — folder Location, tick Is Sendable and Is Testable for a send audience. Next screen is Data Retention Policy — Off for a persistent master. Then Fields: name, data type, length, Nullable, Primary Key — PK on SubscriberKey, plus an EmailAddress-type field. Finally the Send Relationship: map SubscriberKey to Subscriber Key, then Complete.

🛠️ Open Contact Builder ▸ Data Extensions ▸ Create ▸ Standard and walk all four wizard steps end-to-end in a sandbox.

It's the DE's stable unique handle — CustomerKey in the SOAP API. Leave it blank and the UI generates a 36-character GUID; I set readable keys on anything integrations touch. The REST data events endpoint addresses rows via key:{externalKey}, and SOAP or WSProxy calls use CustomerKey. Renaming a DE is safe; changing its External Key silently breaks every integration pointed at it. Keep it to 36 characters or fewer — longer keys can degrade downstream processes.

🛠️ Create a DE with a custom External Key, then hit the REST rowset endpoint with key:{yourKey} to see the binding.

Eight types: Text, Number, Date, Boolean, EmailAddress, Phone, Decimal, Locale. Text takes a length — 254 by convention for emails, up to 4000 in Contact Builder; huge or unbounded text fields hurt performance and can't be primary keys. Decimal needs explicit precision and scale. Dates are stored in system time — CST, UTC-6, no daylight saving. A sendable DE needs one EmailAddress-type field to carry the send address.

🛠️ Create one field of each of the eight types on a scratch DE and import a value that violates each to read the rejections.

Lists are the legacy model: keyed on email address, flat profile attributes, practical to roughly 500K subscribers, no relationships. Data Extensions are relational tables — any key you choose, effectively unlimited scale, and they're what imports, SQL, Data Designer and Journey Builder all work against. I use DEs for everything serious; lists survive only for tiny static audiences and as Publication Lists, which handle per-publication opt-outs. All Subscribers still sits behind both as the master subscriber table.

🛠️ Open Email Studio ▸ Subscribers ▸ Lists beside Data Extensions and compare the property screens side by side.

Standard — an empty table you define field by field. Filtered — a rule-based subset of a source DE, drag-and-drop criteria, no SQL. Random — a percentage or fixed-count sample from a source, good for test cells. From a Template — pre-built field sets like the Salesforce templates or the TriggeredSendDataExtension. In an Enterprise account you also create Shared variants under Shared Items so every Business Unit can see them.

🛠️ Create one Filtered and one Random DE off the same source, change the source, and compare what each shows afterwards.

Open the DE ▸ Records tab to spot-check rows and the record count. For anything real I use Query Studio — a COUNT(*) or a TOP sample — because the Records view is paginated with only basic filtering. After an import I read the results — inserted, updated, errored — and pull the error file if rows rejected. Before a send, Preview & Test against the DE proves personalisation resolves row by row.

🛠️ Open any DE ▸ Records tab, then run a count on the same DE in Query Studio and reconcile the two numbers.

Two things — tick Is Sendable in Properties, then define the Send Relationship: this DE field relates to Subscribers on Subscriber Key. I map a stable ID like SubscriberKey or CustomerID, and the DE needs an EmailAddress-type field to send with. That relationship is how the send engine resolves each row to a contact, honours unsubscribe status, and writes tracking back. Without it, the DE can't be picked as a send or test-send audience.

🛠️ Edit an existing DE ▸ tick Is Sendable ▸ map SubscriberKey to Subscriber Key ▸ Save, then confirm it appears in a Guided Send.

Same identity, two vocabularies. Subscriber Key is the Email Studio identifier on All Subscribers; Contact Key is the Contact Builder and Journey Builder name for the same value on the contact record. One person, one key, across channels — email, SMS and push data all hang off it. The model only works if it's a stable business ID: mixing formats, like email in one feed and CRM ID in another, splinters one human into multiple contacts.

🛠️ Search one person by Contact Key in Contact Builder ▸ All Contacts, then find the same identity in Email Studio's All Subscribers.

Because emails change and get shared. If EmailAddress is the identity, someone updating their email becomes a brand-new subscriber — history splits — and a family sharing an inbox collapses into one record. Keying on a stable CustomerID keeps one shopper as one contact across email changes, dedupes tracking, and keeps unsubscribe state attached to the person. At GAP our Master_Audience is keyed on the customer ID as SubscriberKey for exactly this reason.

🛠️ Import the same person twice — once email-keyed, once ID-keyed — into a sandbox and watch the subscriber count diverge.

It marks a sendable DE as available for test sends — the DE then shows up in the Test Send audience picker, not just live sends. The checkbox is dependent: it stays disabled until Is Sendable is on. I tick it on seed and QA DEs so the team proofs real rendering against real rows. It has zero effect on production sends; it only gates visibility under test data extensions.

🛠️ Tick Is Testable on a seed DE and run Preview & Test against it from Content Builder.

Open the DE ▸ Edit Properties ▸ tick Is Sendable, then complete the Send Relationship — choose the send field and map the key field to Subscriber Key — and Save. If the sendable options are greyed out, the usual cause is no EmailAddress-type field on the DE: add one, reopen properties. And remember the asymmetry — you can make a DE sendable later, but once the relationship is saved you can't change which field it maps.

🛠️ Take a non-sendable DE, add an EmailAddress-type field, then reopen Properties and watch Send Relationship unlock.

It enforces uniqueness on that field and turns imports into upserts. With a PK, Add and Update matches on the key — existing keys update in place, new keys insert — so re-running the same file never duplicates rows. Without one, every import appends and duplicates pile up silently, meaning double sends. It's also what makes AMPscript UpsertData behave as a clean insert-or-update. I tick it on the stable ID and untick Nullable.

🛠️ Import the same CSV twice into a PK'd DE on Add and Update and confirm the row count doesn't move.

Nullable means the field may be empty. Untick it and it's required — an import row with a blank there is rejected to the error file, and a query writing NULL into it fails. Primary keys are always non-nullable. My rule: keys and EmailAddress required; personalisation fields nullable, so a missing first name doesn't reject the whole row — I handle the blank with a default greeting in AMPscript instead.

🛠️ Untick Nullable on a field, import a file containing blanks, and read the error file it generates.

Yes — tick Primary Key on multiple fields and uniqueness is enforced on the combination. Classic uses: an order-lines DE keyed on OrderID plus SKU, or a preferences DE keyed on SubscriberKey plus ChannelType. Add and Update then matches on all key columns together. Keep composites short and stable — and keep send audiences one row per subscriber; composite keys belong on relational and lookup DEs, not the audience itself.

🛠️ Build a two-field composite-PK DE and exercise it with UpsertData("DE",2,...) from a CloudPage.

The ones that bite: Text length caps at 4000 characters in Contact Builder; External Keys should stay within 36 characters; Decimal needs explicit precision and scale; retention policies max out at 730 days; a Contact Delete batch takes up to about a million keys. There's no hard row cap on a DE, but wide unindexed tables slow every import and query — so I keep the sendable DE narrow and push cold attributes into related DEs joined on the key.

🛠️ Create a Text field with length 10 and import an 11-character value to see the length rejection first-hand.

Open the DE ▸ Records tab ▸ Import. Choose the source — upload From File for a one-off, or a file already sitting on the Enhanced FTP. Set the delimiter, pick the Update Type — Add and Update is my default — map file columns to DE fields by header or ordinal, set the notification address, review, Finish. It runs asynchronously; the results give inserted, updated and errored counts.

🛠️ Open a DE ▸ Records ▸ Import, map by header, choose Add and Update, and read the results notification end to end.

Overwrite truncates the DE and loads fresh — afterwards the table is exactly the file. Add and Update is an upsert on the Primary Key: matching keys update in place, new keys insert, nothing is deleted. Add Only — append — inserts new rows only; with a PK, existing keys reject as errors, without one everything inserts. There's also Update Only, which touches existing keys and ignores new ones. Add and Update is the production default.

🛠️ Run the same file through Overwrite, Add and Update, and Add Only into three copies of a DE and diff the resulting counts.

Overwrite truncates first, then loads. If the load dies midway — corrupt file, FTP hiccup, encoding issue — the live audience is left empty or partial, and the evening send mails nobody or a fragment. So I never point Overwrite at a live send DE: land into a staging DE, validate row counts, then move staging into the target with an Update-mode step. Overwrite is fine on staging tables where the file is the source of truth.

🛠️ Build an automation with Import ▸ Verification Activity alerting on zero rows ▸ Send, then feed it a bad file and watch it halt.

Append — Add Only — just inserts: load the same file twice into a key-less DE and you physically double the rows, and the same person gets the send twice. Row-level dedupe comes from the Primary Key plus Add and Update: same key, one row, updated in place; only genuinely new keys insert. Overwrite sidesteps the question by truncating and reloading. So PK plus Add and Update is the dedupe mechanism; append without a key is what creates the mess.

🛠️ Import a key-less CSV twice on Add Only, count the duplicates, then rebuild the DE with a PK and repeat.

Two levers, and the panel is testing whether I conflate them. Row level: Primary Key plus Add and Update keeps one row per key inside the DE. Subscriber level: the send relationship must map a stable, unique SubscriberKey — never EmailAddress — so the same human resolves to one subscriber on every send and never splits when their email changes. PK for row dedupe; stable SubscriberKey in the send relationship for identity. You need both, or duplicates appear somewhere.

🛠️ Say the two-lever answer out loud until automatic: PK for row dedupe, stable SubscriberKey in the send relationship for identity.

The source system drops a file on the Enhanced FTP — usually the Import folder. An Import File Activity inside a scheduled or file-drop automation picks it up by filename pattern, with date substitution like %%Year%%%%Month%%%%Day%% for daily files, maps columns, applies the update type, and writes the DE. I chain it: trigger, Import, Verification, then the transform and send steps, with failure notifications going to a team inbox, never one person.

🛠️ Create an Import File Activity using %%Year%%%%Month%%%%Day%% in the filename pattern against the Enhanced FTP Import folder.

Read the results — records in file, inserted, updated, errored — then pull the error file, which lists each rejected row with the failing column and reason: blank required field, length exceeded, bad email or date format, PK duplicate on Add Only. Then I fix the pattern, not the rows: if the source extract changed, the spec gets corrected upstream; if it's genuine data quality, I add a staging DE and a cleansing step before the target load.

🛠️ Open Automation Studio's run log after a deliberately broken import and walk the error file row by row.

A rule-based subset of one source DE — drag fields into criteria with AND/OR groups, no SQL. Contact Builder ▸ Data Extensions ▸ Create ▸ Filtered Data Extension ▸ pick the source ▸ build the rules ▸ name and save; it materialises the matching rows. The key nuance: the definition is live but the data is a snapshot — it only updates when refreshed, manually or via automation. Single source only; the moment you need a join, it's a query instead.

🛠️ Create a Filtered DE off your master with two AND criteria, change the source, then refresh and watch the count move.

Filtered DE when it's a single source, simple attribute rules, and a marketer should be able to tweak criteria without a developer — it's self-documenting in the UI and inherits sendability. A SQL activity when I need joins, aggregation, dedupe logic, or the segment feeds an automation chain. My rule of thumb: one table plus AND/OR equals filter; anything relational equals query into a standard target DE. I give panels the boundary, not a religious preference.

🛠️ Rebuild one of your filtered DEs as a query-activity target and compare maintainability and refresh time.

It pulls a random sample from a source DE — a percentage or a fixed count, and you can carve several mutually exclusive splits in one pass for test cells. Create ▸ Random Data Extension ▸ pick the source ▸ define the splits. It's a static snapshot: new source rows never flow in, and re-running re-samples. I use it for hold-out groups and manual test cells when the native A/B feature doesn't fit the design.

🛠️ Create a Random DE at 10% of a source, add rows to the source, and verify the sample never changes.

In Enterprise 2.0, DEs created under Shared Items ▸ Shared Data Extensions are visible across Business Units instead of local to one. Sharing is by folder location — you can't flip a local DE to shared; you create it in the shared folder, typically from the parent BU, or rebuild it there. It's the standard pattern for one master suppression or audience DE governing every brand, with access controlled so children consume without corrupting it.

🛠️ Create a DE inside Shared Items ▸ Shared Data Extensions at the parent, then reference it from a child BU with the ENT. prefix.

Read-only mirrors of Sales or Service Cloud objects, brought in through Marketing Cloud Connect. Setup is Contact Builder ▸ Data Sources ▸ Synchronized: pick the object — Contact, Lead, custom — select the fields, set the poll rate from every 15 minutes up to hourly or daily, and it lands as Object_Salesforce. You can't write to them or send to them directly; you query them into standard DEs, or use Salesforce Sends for CRM audiences.

🛠️ Open Contact Builder ▸ Data Sources ▸ Synchronized and check one object's field selection, sync rate and last-run status.

A Synchronized DE is the raw, read-only mirror of a CRM object via MC Connect. A Salesforce Data Extension is the sendable audience used for Salesforce Sends — created when you target a Salesforce report or campaign from Email Studio — with Subscriber Key set to the 18-character Contact or Lead ID and tracking written back to the CRM. Mirror for data; Salesforce DE for actually mailing CRM audiences with closed-loop tracking.

🛠️ Run a Salesforce Send to a small CRM report in a sandbox and inspect the Salesforce DE it generates.

All Contacts to browse contacts and run deletions; Data Designer to model attribute groups and relationships; Data Extensions to create and manage tables; Data Sources for where data originates, including Synchronized sources from CRM; and Contacts Configuration for settings like enabling Contact Delete. Conceptually it's where the contact record lives — one Contact Key with channel addresses and linked DEs — whereas Email Studio is the send-centric view of the same world.

🛠️ Open each Contact Builder tab in order and say aloud what each one governs before your next panel.

Data Designer is the canvas in Contact Builder where you link Data Extensions to the contact model, organised into Attribute Groups — say Orders or Preferences. You create a group, drag in a DE, and define the relationship: which DE field joins to Contact Key or to another DE's field, plus the cardinality. Once linked, those attributes become available to Journey Builder decision splits, entry filters and personalisation. Unlinked, a DE is just a table; linked, it's contact data.

🛠️ Link a DE to Contact Key in a new Attribute Group, then locate its fields under Contact Data in a journey decision split.

Two choices per link. One-to-one for profile-style data — exactly one row per contact, like a demographics DE joined on Contact Key. One-to-many for behavioural or transactional data — one contact, many order rows, joined on the customer ID. Get it right, because segmentation and journeys treat them differently: one-to-one yields clean scalar attributes; one-to-many yields row sets where any matching row can satisfy a filter, which surprises people.

🛠️ Set a deliberately wrong one-to-one link on a multi-row DE and watch the attribute values turn nondeterministic.

All Subscribers is Email Studio's master list — one row per Subscriber Key with an email status: Active, Held, Unsubscribed or Bounced; I remember it as A-HUB. All Contacts in Contact Builder is the cross-channel superset — anyone with a Contact Key, reachable by email, SMS or push. Every email send checks All Subscribers status regardless of which DE you target — which is why an Unsubscribed person never mails even though their DE row looks perfectly clean.

🛠️ Open All Subscribers, filter by status, and inspect one Held and one Unsubscribed record's properties.

Two parts. Enable once, as an admin at the parent BU: Contact Builder ▸ Contacts Configuration ▸ turn on Contact Delete, and under Manage Settings set the Suppression Period — default 2 days, 0 for immediate. Then to run it: Contact Builder ▸ All Contacts ▸ trash icon ▸ choose the source — usually a Data Extension holding the contact keys, or a list or filtered set, up to about a million keys per request — and confirm Delete. It suppresses first, then hard-deletes after the window, asynchronously.

🛠️ Enable Contact Delete in a sandbox via Contacts Configuration, then erase two test contacts from a keys DE via All Contacts ▸ trash icon.

Deletion is a pipeline, not an event. First, suppress: the contact is immediately hidden and blocked everywhere — no sends, no journeys, invisible in the UI — for the Suppression Period, which defaults to 2 days and is configurable in Contacts Configuration, with 0 meaning immediately eligible. Then the queued job identifies all related data. Finally, hard delete: contact and related data permanently erased. The window is a deliberate safety buffer before the irreversible step.

🛠️ Set the Suppression Period to 0 in Manage Settings on a sandbox and time one test contact's full disappearance.

Three different removes. Unsubscribe changes status only — the subscriber flips to Unsubscribed in All Subscribers; the data stays and sends suppress. Deleting a DE row removes the person from that one audience table — the contact record and every other DE still hold them. Contact Delete is account-wide erasure of the contact and related data across every Business Unit — suppress-then-delete, irreversible. Only the third satisfies a GDPR erasure request; the first satisfies an opt-out.

🛠️ Whiteboard the three removes with exactly what survives after each — status, row, contact record — until it's reflexive.

Account-wide, always. The delete applies to every Business Unit sharing the account — you cannot erase a contact from just one BU with this tool; it's all or nothing across the ClientID. It's also asynchronous and irreversible. And after completion I verify leftovers: synchronized DEs, triggered-send logs and child-BU query outputs can still hold copies that need their own cleanup pass.

🛠️ Verify after a sandbox Contact Delete that the key is gone from every BU's All Contacts view, not just the one you ran it in.

Contact count drives licensing, so I run governed hygiene: define deletable criteria with the business — no engagement in N months, in no active audience, no legal hold — stage those keys into a DeleteKeys DE, then batch Contact Delete via the trash flow or the REST listReference endpoint. The suppression period applies and it's queued, so I plan it off-cycle. Duplicate contacts from historical key mistakes are usually the other big win.

🛠️ Frame: present contact hygiene as a governed programme — business-approved criteria, a staged keys DE, an off-cycle queued delete — not an engineering cleanup.

Set at creation step two or later via Properties, with three scopes: delete Individual Records after a period — row by row, aging from when each row was added; delete All Records but keep the DE shell; or delete All Records and Data Extension itself. Periods run up to a 730-day maximum, and all-records policies can Reset On Import so each load restarts the clock. Aged-out data is permanently deleted — no recycle bin, no recovery.

🛠️ Create a scratch DE with an Individual Records policy, load rows, and revisit Properties ▸ Data Retention to watch the countdown behaviour.

First click: DE ▸ Properties ▸ Data Retention — an aggressive policy is the usual culprit, often inherited from a copied DE or wizard defaults nobody reviewed. Confirm the scope and period, and whether Reset On Import applies. Then the import history for a failed or Overwrite load, then any queries writing to it. My standing rule: retention Off on persistent master DEs, and explicit, documented policies on staging and log DEs only.

🛠️ Check DE ▸ Properties ▸ Data Retention as your first move whenever rows vanish, before blaming imports or queries.

Both shape who receives, differently. A Publication List categorises sends — Promotional versus Transactional per brand — and records subscribe/unsubscribe per publication, so opting out of offers doesn't kill order confirmations; triggered sends require one. A Suppression List is a do-not-contact filter applied at the send: matching subscribers are excluded with no status change — competitors, complainers, legal holds. Publication is opt-out bookkeeping the subscriber controls; suppression is a silent exclusion you control.

🛠️ Create a Promotional publication list, attach it to a send, unsubscribe a seed, and confirm the seed still receives a Transactional send.

In a SQL Query Activity — Automation Studio ▸ Activities ▸ Create Activity ▸ SQL Query. There is no console that prints rows: the query is SELECT-only and results must land in a pre-built target Data Extension via a data action of Overwrite, Update, or Append. For ad-hoc previews I use Query Studio; Email Studio ▸ Interactions ▸ Query is the legacy entry point I only mention as legacy.

🛠️ Open Automation Studio ▸ Activities ▸ Create Activity ▸ SQL Query, paste a SELECT, pick the target DE, set Overwrite, click Validate, then Save.

Overwrite truncates the target DE first, then inserts the result — the default for refreshing a snapshot audience. Update requires a Primary Key on the target and is really an upsert: it updates rows whose PK matches and inserts the ones that don't; it never deletes. Append just adds every result row — duplicates possible, but it's how you accumulate history into a permanent DE.

🛠️ Open a SQL Query Activity, click through the three Data Action radio buttons, and say out loud what each one does to the target DE.

No — that's the classic misconception. Update needs a Primary Key on the target, then it updates rows whose PK matches AND inserts rows whose PK doesn't — a true upsert. It never deletes, so subscribers who fall out of the source stay behind as stale rows. If churn matters, pair it with a cleanup pass or rebuild with Overwrite behind a row-count guard.

🛠️ Run an Update-mode query twice against a PK'd test DE — once with a matching key, once with a new one — and watch it update the first and insert the second.

Validate parses the SQL for syntax and confirms every returned column maps by name and compatible type to a column in the target DE — that's it. It doesn't execute the query or prove your logic. Since the DE is never auto-created from the SELECT, alias every computed column with AS so names line up. A query can validate clean and still return zero rows or time out at runtime.

🛠️ Alias a column to a name that doesn't exist in the target DE, hit Validate, and read the mapping error it throws.

30 minutes, and it's a hard, non-extendable limit — Salesforce won't raise it on request; longer queries are killed. I design around it: filter by date first so joins work on small sets, join on indexed keys like the PK or SubscriberID, keep predicates SARGable, and split heavy transforms into chained Query Activities writing to staging DEs inside one automation. Anything that consistently blows 30 minutes gets pre-aggregated nightly.

🛠️ Split one heavy query into two chained SQL Query steps in an automation, with an intermediate staging DE between them.

No. SFMC SQL is a read-only SELECT subset of SQL Server T-SQL — no INSERT, UPDATE, DELETE, or MERGE, and no DDL. Sources, including the system data views, are never touched. The only write in the whole mechanism is the activity's data action — Overwrite, Update, or Append — against the target DE you picked in the editor.

🛠️ Paste a query starting with UPDATE into the activity editor, watch Validate reject it, and restate that the data action is the only write.

Query Studio — the modern ad-hoc tool. It runs a SELECT against any DE or data view and shows results instantly; it's SELECT-only, forces you to name columns (no SELECT *), and auto-saves each run as a timestamped temp DE under QueryStudioResults, kept roughly 24 hours. The fallback is a throwaway Query Activity into a scratch DE with Run Once.

🛠️ Open Query Studio, run a named-column SELECT against _Open for the last 7 days, and find the auto-saved result DE under QueryStudioResults.

No — you pre-create it, typically Email Studio ▸ Subscribers ▸ Data Extensions ▸ Create, with column names and data types matching exactly what the SELECT returns. Validate enforces that mapping, which is why I alias every derived column with AS. Query Studio can spin a DE out of results for you, but a production Query Activity always writes into a target that already exists.

🛠️ Create the target DE first with columns matching your SELECT aliases, then build the Query Activity and confirm Validate passes clean.

Overwrite truncates the target before inserting, so a query that errored or legitimately returned zero rows — bad join, source not yet loaded, date-window edge — leaves the DE empty, and the next send goes to nobody, or for an exclusion DE, suppresses nobody. Prevention: a Verification Activity with a minimum row-count threshold that stops the automation, or stage into a holding DE and guarded-copy into the live audience only when staging has rows.

🛠️ Add a Verification Activity between the audience query and the send step, set it to stop the automation when the record count is below N, and wire the alert email.

No DML — INSERT, UPDATE, DELETE, MERGE — and no DDL. No stored procedures, no variables (DECLARE @x), no temp tables (#temp), no EXEC, no cursors, no query hints like NOLOCK, and no execution-plan visibility. Plus the 30-minute hard timeout. It's a SELECT-only subset of SQL Server, and the write side is handled entirely by the activity's data action.

🛠️ Frame: recite the banned list in one breath — 'no DML, no DDL, no procs, variables, temp tables, cursors, or hints; SELECT-only with a 30-minute cap.'

Two things. Every value, including date windows, becomes an inline expression — DATEADD(day, -30, GETDATE()) written straight into the WHERE, never @cutoff — and there's no way to pass a runtime parameter into a Query Activity. And multi-pass logic becomes chained Query Activities writing to intermediate staging DEs inside one automation — the staging-DE pattern is the canonical substitute for temp tables and variables.

🛠️ Rewrite one of your parameterized SQL Server queries as pure inline expressions, then split its second pass into a chained staging-DE step.

Yes, both. WITH CTEs work and are the readable in-query substitute for staging, and window functions — ROW_NUMBER, RANK, DENSE_RANK with OVER (PARTITION BY ... ORDER BY ...) — are fully supported; ROW_NUMBER drives the standard dedup pattern. What you don't get around them is temp tables or variables, and the whole statement still lives under the 30-minute cap.

🛠️ Rewrite your engagement rollup with a WITH CTE for the aggregation stage and compute open rate in the outer SELECT.

Treat them as unsafe in the general case — SFMC's engine is historically picky: scalar subqueries in the SELECT list or general correlated WHERE subqueries can fail or crawl. I rewrite them as JOINs, derived tables, or CTEs. The one correlated form that works reliably is EXISTS / NOT EXISTS correlated on the join key — which is exactly the recommended anti-join for suppression.

🛠️ Take a SELECT-list scalar subquery and rewrite it as a LEFT JOIN onto a pre-aggregated derived table grouped by the key.

Central Standard Time — and SFMC system time never observes Daylight Saving. So GETDATE() is not IST, not the subscriber's zone, and a 'last 24 hours' window drifts an hour against wall clock during DST. If a cutoff genuinely matters — say a market-local midnight — I shift explicitly with DATEADD hour offsets written inline, because there are no variables to hold an offset.

🛠️ Run SELECT GETDATE() in Query Studio and compare it with your local clock to fix the CST-no-DST offset in your head.

CONVERT with a style code at any real volume — CONVERT(varchar, OrderDate, 23) gives yyyy-mm-dd, style 112 gives yyyymmdd, 101 gives mm/dd/yyyy. FORMAT works in SFMC but it's CLR-backed, slow, and non-deterministic, so it belongs only in low-volume display formatting. In a segmentation query over millions of rows, FORMAT is a self-inflicted step toward the 30-minute timeout.

🛠️ Replace any FORMAT call in your queries with CONVERT(varchar, col, 23) or style 112 and memorize those two codes.

ROW_NUMBER in a derived table: inner query does SELECT *, ROW_NUMBER() OVER (PARTITION BY SubscriberKey ORDER BY PurchaseDate DESC) AS rn FROM Orders, outer query selects from it WHERE rn = 1. PARTITION BY restarts numbering per subscriber, ORDER BY DESC makes the newest row number one, and the outer filter keeps exactly one winner per key. I'd add a unique tiebreaker to the ORDER BY for determinism.

🛠️ Write this dedup cold on paper in under 90 seconds, narrating PARTITION BY, ORDER BY, and the outer rn = 1 as you go.

Because of SQL's logical processing order. WHERE is evaluated before the SELECT phase, and window functions are computed in the SELECT phase — so at the moment WHERE runs, rn doesn't exist yet. The engine rejects a window function in WHERE. You must compute ROW_NUMBER in a subquery or CTE, then filter rn = 1 in the outer query. That wrapper isn't style; it's forced by the evaluation order.

🛠️ Say the logical order out loud — FROM, JOIN/ON, WHERE, GROUP BY, HAVING, SELECT with window functions, DISTINCT, ORDER BY, TOP.

ROW_NUMBER always gives one winner per partition — 1, 2, 3 with no shared ranks — so it's the dedup tool; on ties the winner is arbitrary unless you tie-break. RANK gives ties the same rank and skips the next — 1, 1, 3 — so RANK() = 1 keeps every row tied for the top, like all orders tied for a customer's max value. DENSE_RANK is 1, 1, 2 — no gaps — for top-N-distinct-values logic.

🛠️ Frame: answer with the ask — 'one record per subscriber means ROW_NUMBER; all records tied for the max means RANK = 1; top N distinct values means DENSE_RANK.'

The winner is non-deterministic — with ORDER BY PurchaseDate DESC alone, SFMC may pick a different row each run, so the audience silently changes between reruns and results aren't reproducible. Fix: add stable secondary sort keys ending in a unique column — ORDER BY PurchaseDate DESC, OrderTotal DESC, _CustomObjectKey DESC. The unique trailing key guarantees exactly one deterministic winner every time.

🛠️ Append a unique trailing sort key such as _CustomObjectKey DESC to every ROW_NUMBER ORDER BY you write from now on.

GROUP BY collapses rows and only lets you keep aggregates — MAX(PurchaseDate) can't tell you which OrderID or OrderTotal belonged to that winning date, and adding those columns to GROUP BY changes the grain. ROW_NUMBER numbers rows without collapsing them, so rn = 1 keeps the entire winning record intact — every column of the latest row, ready to write to the target DE.

🛠️ Frame: lead with 'GROUP BY loses the non-aggregated columns; ROW_NUMBER preserves the whole winning row' before writing anything.

Three. NOT IN with a subquery — a landmine unless you add an IS NOT NULL guard inside. LEFT JOIN B ... WHERE b.key IS NULL — the classic anti-join shape. And NOT EXISTS (SELECT 1 FROM B WHERE b.key = a.key) — my default: NULL-proof, short-circuits on first match, doesn't fan out if B has duplicates, and the engine optimizes it as a true anti-join at scale.

🛠️ Write the same suppression three ways against a scratch DE — NOT IN, LEFT JOIN IS NULL, NOT EXISTS — and confirm identical row counts.

Three-valued logic. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and any comparison to NULL evaluates to UNKNOWN, not FALSE. An AND chain containing UNKNOWN can never be TRUE, so the predicate is never true for any row — the query silently returns zero rows. One NULL SubscriberKey in the suppression DE and your entire audience vanishes.

🛠️ Insert one NULL key into a scratch suppression DE, run a NOT IN query against it, and watch the result drop to zero rows.

That's join fan-out: the join key isn't unique on one side, so each left row multiplies by its matches. DISTINCT hides the symptom, masks the broken join, and can still leave wrong aggregates. The real fix is making the many side one-row-per-key first — ROW_NUMBER dedup or a GROUP BY pre-aggregation in a derived table — then joining. I reach for DISTINCT only when the duplication is expected and meaningful.

🛠️ Diagnose a fan-out by running SELECT key, COUNT(*) ... GROUP BY key HAVING COUNT(*) > 1 on each side of the join before blaming the join itself.

Base from the audience DE, then two NOT EXISTS anti-joins: WHERE NOT EXISTS (SELECT 1 FROM _Bounce b WHERE b.SubscriberKey = a.SubscriberKey AND b.BounceCategory = 'Hard bounce') AND NOT EXISTS (SELECT 1 FROM _Unsubscribe u WHERE u.SubscriberKey = a.SubscriberKey). Note the stored value is 'Hard bounce' with a lowercase b — I match the stored casing rather than trusting collation. NOT EXISTS keeps it NULL-proof at scale.

🛠️ Write this cold, then extend it with a third NOT EXISTS against _Complaint to also drop spam complainers.

_Subscribers — the roster: SubscriberKey, SubscriberID, EmailAddress, Status, DateJoined; persists indefinitely. The event views — _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Complaint — one row per event, roughly 6-month retention. _Job — send metadata: JobID, EmailName, EmailSubject, SchedTime; the join that names campaigns. Plus _ListSubscribers and _BusinessUnitUnsubscribes for list and BU-level state.

🛠️ Run SELECT TOP 10 with named columns from _Sent, _Open, _Job, and _Subscribers in Query Studio to internalize each schema.

Trap question — _Open has no EmailAddress column, so SELECT EmailAddress FROM _Open is wrong; event views only carry keys and event fields. Correct: SELECT DISTINCT sub.SubscriberKey, sub.EmailAddress FROM _Open o JOIN _Subscribers sub ON o.SubscriberID = sub.SubscriberID WHERE o.EventDate >= DATEADD(day, -30, GETDATE()). DISTINCT because one person opens many times; the join to _Subscribers is the only way to the address.

🛠️ State 'event views carry no EmailAddress' out loud before writing, then write the _Subscribers join on SubscriberID from memory.

SubscriberKey is the ID you chose — at GAP, effectively the customer/loyalty ID — the All Subscribers key and how your own DEs join. SubscriberID is a system-assigned internal integer, indexed on the system views. ContactKey is the Contact Builder / Journey Builder identity; for email sends it usually equals SubscriberKey, but it's conceptually the contact-model key. EmailAddress is just an attribute in _Subscribers — never an identifier I join on.

🛠️ Frame: answer in three beats — 'my chosen key, the system's indexed integer, and the contact-model key' — then say which you'd join on and why.

The send-metadata view — one row per send job: JobID, EmailName, EmailSubject, FromName, FromEmail, SchedTime, DeliveredTime, SendClassification. Event views only know the JobID integer, so joining _Job on JobID is the only way to label or filter engagement by campaign — 'unique opens by campaign' is _Open JOIN _Job ON JobID, COUNT(DISTINCT SubscriberKey), GROUP BY EmailName.

🛠️ Write the campaign-level rollup — _Open joined to _Job on JobID, COUNT(DISTINCT SubscriberKey) grouped by EmailName — in Query Studio.

Anchor on _Sent, anti-join _Open: SELECT DISTINCT s.SubscriberKey FROM _Sent s WHERE s.EventDate >= DATEADD(day, -90, GETDATE()) AND NOT EXISTS (SELECT 1 FROM _Open o WHERE o.SubscriberKey = s.SubscriberKey AND o.JobID = s.JobID). The JobID condition is load-bearing — it scopes the open to that exact send. The equivalent LEFT JOIN ... IS NULL form works too, with the same two-part ON.

🛠️ Write this in 90 seconds and say 'JobID scopes the open to the same send' unprompted as you type the ON clause.

It flags the subscriber's first event of that job — first open of the send, first click, first bounce. So unique versus total opens per campaign is one pass over _Open: SUM(CASE WHEN IsUnique = 1 THEN 1 ELSE 0 END) for uniques against COUNT(*) for totals, grouped by JobID. The gap between them is your re-open volume — and a hint of Apple MPP inflation.

🛠️ Run the unique-vs-total query grouped by JobID for the last 30 days and read the re-open gap per campaign.

The standard event columns — SubscriberKey, SubscriberID, JobID, ListID, BatchID, EventDate, IsUnique — plus bounce detail: BounceCategory, BounceType, and SMTP code and reason fields. The documented BounceCategory set: 'Block bounce', 'Hard bounce', 'Soft bounce', 'Technical bounce', and 'Unknown'. Suppression logic keys on 'Hard bounce' — exact stored casing, lowercase b.

🛠️ Query SELECT BounceCategory, COUNT(*) FROM _Bounce ... GROUP BY BounceCategory for the last 90 days and read your real category mix.

Prefix the view with Ent. — SELECT SubscriberKey, EventDate FROM Ent._Open reads opens across all BUs in an Enterprise 2.0 account, where the unprefixed _Open shows only the local BU's rows. Same columns, same retention, same SARGability rules — only the scope changes. Essential for global suppression and cross-brand engagement in a multi-brand org like GAP's four banners.

🛠️ Run the same 7-day _Open count twice from a child BU — once bare, once with the Ent. prefix — and compare the row counts.

_Sent is the deliverable base — one row per subscriber per job actually dispatched. Anchoring there and LEFT JOINing the event views keeps non-engagers in the result with zeros — and non-openers are usually the entire point of the exercise. Anchoring on _Open silently drops them; anchoring on the audience DE counts people the send never reached. Rates also need _Sent as the denominator.

🛠️ Frame: open with 'start from _Sent as the denominator, LEFT JOIN events onto it' before writing any engagement rollup.

It's the All Subscribers roster, not an event log — SubscriberKey, SubscriberID, EmailAddress, Status, DateJoined, DateUnsubscribed, BounceCount. And no — it persists indefinitely: the ~6-month retention applies to the event views, not the roster. That's why sunset queries drive from _Subscribers — full population, never expires, and Status lets you scope to Active before applying engagement anti-joins.

🛠️ Query SELECT Status, COUNT(*) FROM _Subscribers GROUP BY Status and memorize your BU's status distribution.

Two classes. Event views — _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Complaint — retain roughly six months, 180 days, rolling; older rows silently stop being queryable. Roster and state views — _Subscribers, _ListSubscribers, _BusinessUnitUnsubscribes — persist indefinitely. Mislabeling which class expires is a senior red flag, so I say it precisely: the events expire, the roster doesn't, and long windows need a persisted rollup.

🛠️ Frame: rehearse the one-liner — 'event views roll off at ~180 days; roster views never do; long windows run off a persisted rollup.'

Two different stores. Tracking and Analytics Builder standard reports read a reporting store retained around 730 days; SQL Query Activities read the data views, retained around 180 days. Same sends, different windows — so a marketer quoting the report and a developer quoting _Open can both be right. The architectural consequence: any long-window logic in SQL must run off a persisted rollup, never the raw views.

🛠️ Frame: keep the contrast crisp — '730 days is the reports UI; 180 days is what my SQL sees in the data views.'

The persistence pattern: a nightly automation whose query reads yesterday's delta from the views and appends it into a permanent DE I own. Normalize opens and clicks into one schema — SELECT SubscriberKey, JobID, 'Open' AS EventType, EventDate FROM _Open WHERE EventDate >= DATEADD(day, -1, GETDATE()) then UNION ALL the _Click arm — data action Append, or Update on a PK of SubscriberKey + JobID + EventType for idempotency. The rollup then spans years.

🛠️ Build the nightly rollup automation — the UNION ALL delta query appending into Engagement_Log — and schedule it before you ever need the history.

The naive query — NOT EXISTS against _Open and _Click for 12 months — silently lies: the views only hold ~6 months, so the older half of the window is simply gone. The real build: persist events nightly into an Engagement_Log rollup (or enable Send Logging), then drive from _Subscribers — WHERE Status = 'Active' AND NOT EXISTS (SELECT 1 FROM Engagement_Log e WHERE e.SubscriberKey = s.SubscriberKey AND e.EventDate >= DATEADD(month, -12, GETDATE())).

🛠️ Say the trap first in the room — 'a 12-month window against raw views only sees six' — then present the rollup-driven query.

Anchor on _Sent for the last six months, LEFT JOIN _Open and _Click on SubscriberKey AND JobID, then GROUP BY s.SubscriberKey with COUNT(DISTINCT s.JobID) AS Sends, COUNT(DISTINCT o.JobID) AS Opens, COUNT(DISTINCT c.JobID) AS Clicks, and MAX(o.EventDate) AS LastOpen. LEFT keeps the zero-engagement people — they're the segment you're hunting — and COUNT ignoring NULLs scores them 0 correctly.

🛠️ Write this rollup as a WITH CTE and add CASE-guarded OpenRate and ClickToOpenRate columns in the outer SELECT.

COUNT(*) counts every row, NULLs included. COUNT(col) counts rows where col is non-NULL — which is why LEFT-JOINed non-engagers correctly score zero. COUNT(DISTINCT col) counts distinct non-NULL values — distinct campaigns, unique people. In engagement SQL the choice IS the metric: COUNT(*) on _Open is total opens; COUNT(DISTINCT SubscriberKey) is unique openers; COUNT(DISTINCT JobID) is campaigns engaged.

🛠️ Run all three COUNT variants over the same LEFT-JOINed result and explain each number aloud before checking your reasoning.

Apple Mail Privacy Protection, since iOS 15 in 2021 — Apple proxies and prefetches images, firing an _Open event whether or not a human ever looked. Opens are inflated and untrustworthy as an engagement signal. I shift measurement and policy to clicks and conversions: click-based sunset logic, click-weighted engagement scores, and I treat the unique-opens-to-clicks gap as a health metric rather than celebrating opens.

🛠️ Rewrite your sunset query's NOT EXISTS test from _Open to _Click and present it as the MPP-resistant variant.

SELECT *, CASE WHEN ABS(CHECKSUM(SubscriberKey)) % 2 = 0 THEN 'A' ELSE 'B' END AS Variant FROM Audience. CHECKSUM hashes the key to an integer — the same key always hashes the same, so assignment is deterministic across reruns. ABS is mandatory because CHECKSUM can return negatives and T-SQL modulo keeps the dividend's sign. The split is approximately even at large N; I validate with a GROUP BY Variant count.

🛠️ Write the CHECKSUM split, run it, then run SELECT Variant, COUNT(*) FROM target GROUP BY Variant to confirm the balance.

ABS(CHECKSUM(SubscriberKey)) % 20 yields buckets 0–19, each about 5%. Map with a CASE: bucket 0 to Holdout_A, bucket 1 to Holdout_B, ELSE Treatment — the remaining 18 buckets, 90%. Because it's keyed on SubscriberKey, every customer lands in the same cell on every rerun — a stable holdout, which is what makes multi-send readouts clean. Then validate the actual proportions with a GROUP BY Cell count.

🛠️ Write the % 20 CASE mapping cold, then verify the 90/5/5 landed with SELECT Cell, COUNT(*) FROM target GROUP BY Cell.

INNER keeps only the overlap — use it when the relationship is required, like audience members who are also loyalty members. LEFT keeps all of the left circle plus matches, NULL-padding where the right side is missing — enrichment, and the anti-join shape. FULL OUTER keeps everything from both sides, matched where possible — reconciliation, when losing rows from either side is unacceptable. On a whiteboard I confirm what 'everything' means before writing.

🛠️ Sketch the three Venn shadings from memory and label each with its one-line use case in under a minute.

SARGable — Search-ARGument-able — means the predicate lets the engine seek the index: the column stays bare on one side of the comparison. WHERE CONVERT(date, EventDate) = ... wraps the column and forces a full scan; the rewrite is a range — EventDate >= DATEADD(day, -1, GETDATE()) AND EventDate < GETDATE(). With no hints or plans in SFMC, the levers are: filter early, join on PK or SubscriberID, SARGable predicates, and pre-aggregated rollups.

🛠️ Hunt one function-wrapped date column in your own queries and rewrite it as a bare-column range against a computed boundary.

AMPscript is SFMC's proprietary scripting language for emails, CloudPages, landing pages, SMS and push. It executes server-side, once per subscriber, at send or render time — the recipient only ever sees rendered HTML. I use it for personalization, conditional content, DE lookups and write-backs. It's interpreted top-to-bottom on every render with no compilation, so the cost model is dominated by data-layer round trips, not string or math work.

🛠️ Open Content Builder ▸ your email ▸ drop an HTML block and type the %%[ ]%% logic at the top — there is no dedicated AMPscript tile.

Three delimiters. %%[ ... ]%% is a logic block — VAR, SET, lookups, branching, no direct output. %%= ... =%% is inline output, like %%=v(@name)=%%. %%FieldName%% is a personalization string resolved from the send context. There is also a rarely used tag form, <script runat='server' language='ampscript'>, functionally equivalent to a block — but in real assets you always write %%[ ]%%.

🛠️ Write the greeting skeleton blind: declare with VAR, set with AttributeValue('FirstName'), guard with EMPTY(), output with %%=v(@greeting)=%%.

Variables start with @, are declared with VAR (optional but good practice) and assigned with SET — the only assignment keyword. Names and functions are case-insensitive, there are no semicolons, and comments are /* */. Output a variable inline with %%=v(@x)=%%; from inside a block use Output() or OutputLine() without closing the fence.

🛠️ Declare every variable in one VAR @a, @b, @c line at the top of the first block — it reads like a manifest in code review.

The trap: AMPscript has no + or & operator at all. Strings concatenate only with Concat(); arithmetic only with Add, Subtract, Multiply, Divide, Mod. Yet && and || do work as aliases for AND/OR, and both = and == test equality — that inconsistency is exactly why people assume 'a' & 'b' works. It doesn't; it fails at render.

🛠️ Grep your own snippets for & and + before any live-coding round — it is the most common bug reviewers spot instantly.

IF condition THEN ... ELSEIF ... ELSE ... ENDIF — THEN and the closing ENDIF are mandatory; chain as many ELSEIF branches as needed. Comparison operators: == (or a single =), !=, >, <, >=, <=, combined with AND, OR, NOT. I write == for clarity even though = is legal, and I remember string comparison is case-insensitive.

🛠️ Code the account-tier branch aloud: read AttributeValue('AccountTier'), branch Platinum/Gold/default, assign a ContentBlockByKey per branch.

FOR @i = 1 TO @count DO ... NEXT @i — the counter auto-increments by one each pass; there is no manual increment and the name after NEXT must match the counter. FOR @i = @count DOWNTO 1 DO iterates in reverse. Rowsets are 1-indexed, so the canonical loop is FOR @i = 1 TO RowCount(@rows) with Row(@rows, @i) inside.

🛠️ Write a four-row loop against a rowset and deliberately pop out to HTML between ]%% and %%[ on each iteration.

Lookup('DE', 'ReturnCol', 'MatchCol', @value) returns one value from a reference DE — add more column/value pairs and they are ANDed. No match returns an empty string, never an error. Matching is case-insensitive, and for speed the match column should be the DE's primary key or an indexed field — a seek instead of a scan. I use it for single facts: an advisor name, a store region.

🛠️ Write Lookup('Store_Master', 'Region', 'StoreID', @storeId) and name each argument out loud — DE, return column, match column, match value.

The value from an unspecified row — it is not deterministically the first physical row or the latest insert. So if I actually need 'the first' or 'the most recent', I never rely on Lookup: I call LookupOrderedRows with an explicit sort like 'OrderDate DESC', take Row(@rows, 1), and read the field from that.

🛠️ Replace any Lookup that assumes recency with LookupOrderedRows(DE, 1, 'SortCol DESC', matchCol, @val) plus Row(@rows, 1).

SET @rows = LookupRows('Order_Items', 'OrderID', @orderId) returns whole rows — note there is no return-column argument. Then SET @count = RowCount(@rows), guard IF @count > 0, loop FOR @i = 1 TO @count, pull SET @row = Row(@rows, @i), read columns with Field(@row, 'SKU'), and pop out of the block to emit each <tr>. Mnemonic: Count, Row, Field.

🛠️ Write the full LookupRows → RowCount → Row → Field loop blind on paper until the fence-out-fence-in around the HTML is automatic.

LookupOrderedRows('Products', 5, 'Price DESC', 'Category', 'Shoes') — DE, max rows, an ORDER BY-style sort string, then match pairs. numRows below 1, including 0, means 'all matches' — still capped at 2,000. Multi-column sort works: 'Score DESC, OrderDate ASC'. The sort is applied in the data layer and the cap is applied after sorting, so a bounded top-N by score is fully reliable.

🛠️ Pass an explicit numRows matching what the design shows — never 0 — and say why out loud: the silent 2,000 cap.

Every Lookup*Rows function — LookupRows, LookupOrderedRows, and their CS variants — is hard-capped at 2,000 rows regardless of numRows. Truncation is silent: no error, no warning. The classic trap question: 'a customer matches 5,000 rows, what does LookupOrderedRows(DE, 0, ...) return?' — 2,000, not 5,000. DataExtensionRowCount('DE') still reports the true total, which is how you prove rows were dropped.

🛠️ Rehearse the trap answer verbatim: '2,000, not 5,000 — the cap applies after the sort, and DataExtensionRowCount shows the real count.'

Three, in order of preference. One: pre-aggregate upstream — a SQL Query Activity builds a per-customer summary DE, one row per customer with totals and last-order, and the render does a single Lookup. Two: page past the cap in SSJS with WSProxy Retrieve and continuation when you genuinely need every row. Three: design match sets to stay under 2,000 by narrowing keys. Retail use cases almost always want the summary DE.

🛠️ Build the pattern once: Automation Studio ▸ SQL Query Activity ▸ GROUP BY CustomerID into Orders_Summary ▸ Overwrite ▸ then a single-row Lookup at render.

LookupCS, LookupRowsCS, LookupOrderedRowsCS are the case-sensitive variants — the default functions match values case-insensitively. I reach for CS when the match value's case is meaningful: coupon or token strings where 'abc123' and 'ABC123' are different records. Everything else carries over unchanged, including the 2,000-row cap and the argument shapes.

🛠️ Name the three CS functions unprompted, then state that the 2,000 cap and argument order are identical to the standard versions.

Row(@rowset, n) pulls the nth row — 1-indexed. RowCount(@rowset) counts rows. Field(@row, 'Col', boolException) reads a column, and the third argument is the senior detail: it defaults to true, so a missing column throws a runtime error. Pass 0 to suppress that and get an empty string back instead — essential for columns that don't exist in every environment, like a dev DE that drifted from prod.

🛠️ Add , 0 to every Field() read of an optional column and pair it with IIF(EMPTY(@x), 'default', @x).

Four signals, and you must guard the one you actually have. RowCount(@rows) == 0 — the lookup matched nothing. Field(@row, 'Col') returning empty — the row exists but the column is blank. Lookup returning empty — ambiguous: no match, or matched a blank. And IsNull — the field was never populated at all, which is distinct from explicitly set to empty string.

🛠️ State which signal you are guarding before writing any IF — 'no rows', 'blank field' and 'no match' are three different bugs.

Four render-time write functions. InsertDE('DE', 'col', val, ...) inserts. UpdateDE('DE', numKeys, keyCol, keyVal, col, val, ...) updates matches. UpsertDE — same shape — updates if the key exists, else inserts; it needs the DE's primary key. DeleteDE('DE', 'key', val) removes matches. In an email they execute per subscriber at render, return nothing usable, and there is no rollback — which is why I guard them by context.

🛠️ Write UpsertDE('Preferences', 1, 'SubscriberKey', @sk, 'UpdatedDate', Now()) and annotate which pair is key versus data.

numKeys is the second argument of UpdateDE/UpsertDE: how many of the following name/value pairs are match keys. It must equal the DE's real primary-key columns. Get it wrong and nothing errors — too few keys and you duplicate rows; misaligned keys and you overwrite the wrong rows. Silent data corruption, discovered weeks later in reporting, is the failure mode.

🛠️ Open the target DE's properties, count the primary-key columns, and read your numKeys against them before every deploy.

Same operation, different context and return. UpsertDE is the email-context function: it returns nothing usable. UpsertData is the CloudPage, landing-page and SMS context function: it executes inline and returns the number of rows affected, so you can branch and show 'Saved'. The split runs through the family — InsertData/InsertDE likewise — and they are related but not identical: argument shapes and return semantics differ, so don't call them aliases. Reads — Lookup, LookupRows — work in both contexts.

🛠️ Say the one-liner: '*Data returns rows affected and suits pages; *DE returns nothing and suits sends' — then write both calls.

Write-backs re-fire on every render, not just the send: View As Web Page, previews and Forward-to-a-Friend all re-execute the AMPscript, so an unguarded UpsertDE writes phantom rows and skews any last-interaction timestamp. The guard is the _messagecontext system variable: if it's VAWP, PREVIEW or FTAF, skip the write; only the ELSE branch — a genuine SEND — performs the UpsertDE.

🛠️ Wrap every render-time write in IF _messagecontext == 'VAWP' OR _messagecontext == 'PREVIEW' OR _messagecontext == 'FTAF' THEN /* skip */ ELSE UpsertDE(...) ENDIF.

Ten documented values. Know cold: SEND for a normal send, VAWP when the same email is opened as a web page, PREVIEW for UI test renders. The rest: FTAF forward-to-a-friend, SITE for CloudPages, LANDINGPAGE, SOCIAL, VALIDATION at send-time validation, LINKRESOLUTION, and SMS. It's the switchboard for guarding side effects by render context.

🛠️ Recite SEND, VAWP, PREVIEW, FTAF, SITE unprompted, and state that a CloudPage render reports SITE.

It is not transactional — there is no rollback. If InsertDE succeeds and a later line in the same render errors, that inserted row stays, leaving half-written state; the only lever is RaiseError's fifth parameter, boolPreserveDataExt, which controls whether prior writes are retained when you skip a subscriber. The architectural conclusion: keep the email render read-mostly and push state changes to CloudPages or Automation — contexts you control.

🛠️ State the design rule in one line: 'reads in the render, writes in pages and automations — the render can't roll back and re-fires on VAWP.'

ContentBlockById(12345) uses the numeric ID — breaks on BU migration because IDs are per-instance. ContentBlockByName takes the full folder path — breaks when someone moves or renames a folder, since the path is the identity. ContentBlockByKey('customer-key') is the portable, preferred choice; but the key must be deployed in that BU or it errors at render. One canonical block per component: a legal update lands once and propagates to every email.

🛠️ Right-click the block in Content Builder ▸ Properties, copy the Customer Key, and reference only that key in code.

Capture, then fall back. SET @legal = ContentBlockByKey('legal-en', @null, 'legal-block', false) — the trailing false tells the function not to throw when the block is missing; it returns empty instead, and the third argument names an impression region for tracking. Then output %%=IIF(EMPTY(@legal), ContentBlockByKey('legal-default'), @legal)=%%. The send never breaks because one key wasn't deployed to the BU.

🛠️ Convert every direct %%=ContentBlockByKey(...)=%% on business-critical blocks to the capture-inspect-fallback pattern.

TreatAsContent(@str) re-invokes the AMPscript parser on a string so embedded AMPscript and HTML actually execute — the use case is dynamic copy stored in a DE that contains code. Without it, the stored %%=...=%% renders as literal text. It is not cached, and it costs a second interpreter pass per call, so keep stored snippets small. And it must only ever run on trusted, internally-authored content.

🛠️ Wrap any DE-sourced string that contains AMPscript in TreatAsContent() at output, and test with a row that actually holds code.

TreatAsContentArea(key, @str) does what TreatAsContent does but caches the parsed result by key within the render. The bite: cache-key collision. Reuse one key for different content in the same render and the second call silently returns the first call's cached output — a maddening bug. Build keys unique per logical content with Concat('promo-copy-', @promoId) — Concat, because there is no & operator.

🛠️ Audit every TreatAsContentArea call for a hard-coded key and rebuild each key with Concat plus the content's ID.

TreatAsContent on untrusted input — it's code injection, RCE-class. The function re-runs the parser, so attacker-supplied AMPscript in a RequestParameter or form value executes with the render's privileges: they can call Lookup to read DEs, HTTPGet to exfiltrate, UpsertDE to corrupt. Rule: only ever re-parse trusted, internally-authored content, and whitelist every request parameter — expected set, type, length — before it touches anything.

🛠️ Say 'never TreatAsContent(RequestParameter(...))' first, then name the whitelist-validate-tokenize trio for every CloudPage input.

Three sources that look interchangeable. AttributeValue('Col') resolves from the subscriber/sendable send context, returns empty instead of erroring when absent, and accepts a computed name — AttributeValue(Concat('Pref_', @category)) — which enables data-driven personalization. %%Field%% is a literal personalization token: it errors if the field is absent and cannot be dynamic. v(@var) outputs a script variable — a separate namespace entirely, nothing to do with data fields.

🛠️ Answer 'where does this value come from?' by naming the source first — send context, literal token, or variable — before touching syntax.

For a send, a %%Field%% or AttributeValue reference resolves from the send's data-source row first — the sendable DE row for a DE send, the subscriber's profile attributes for a list send — then subscriber/profile attributes at account level. On a name collision, the send data-source row wins. Practical consequence: when a value 'isn't showing up', confirm which send type you're in and which source actually holds that column.

🛠️ Check the send relationship on the sendable DE — which column maps to Subscriber Key — before debugging any personalization miss.

There is no native switch in AMPscript — CASE WHEN is SQL, not AMPscript. The 'three-track function' interviewers mean is IIF(condition, trueValue, falseValue): the inline ternary — one condition, two outcomes. It's perfect for defaults, like IIF(EMPTY(@lang), 'en', @lang), and inherently two-way. For a many-way choice you don't chain ten IIFs; you go data-driven with a Lookup or a computed ContentBlockByKey name.

🛠️ Write the default-language line SET @lang = IIF(EMPTY(@lang), 'en', @lang) and immediately state that ten branches want a lookup, not IIF.

Never a ten-branch IF. Read the language once — SET @lang = AttributeValue('Language'), default it with IIF(EMPTY(@lang), 'en', @lang) — then go data-driven. Option A: a copy DE keyed on LangCode plus content key — Lookup('Copy_By_Lang', 'Text', 'LangCode', @lang, 'Key', 'hero_headline') — with a second English-fallback lookup when the row is missing. Option B: per-language content blocks pulled with ContentBlockByKey(Concat('hero-', @lang)). Adding language eleven is a row or a block, not a code change — and handle RTL with a dir attribute driven by IIF.

🛠️ Build the Copy_By_Lang DE with composite key (LangCode, Key), then write the two-step lookup with English fallback blind.

RaiseError(message, boolSkipCurrentOnly, apiErrorCode, apiErrorNumber, boolPreserveDataExt). The second parameter is the one to get right: true skips only the current subscriber and the send continues for everyone else; false — the default — aborts the entire job. The third and fourth are user-defined string and numeric codes surfaced in error reporting, not field names. For a missing required token I raise with true: one bad record shouldn't kill a million-recipient send.

🛠️ Write the guard blind: IF EMPTY(@requiredToken) THEN RaiseError('Missing token', true, 'ERR_TOKEN') ENDIF and name parameter two out loud.

Base64Encode/Base64Decode for reversible transport encoding; MD5, SHA1, SHA256 for one-way hashes; GUID() for unique identifiers. Uses I actually ship: deterministic A/B bucketing — hash the subscriber key, BaseConvert hex to decimal, Mod 2; per-record GUID tokens stored on the DE as URL-safe identity; hashed emails for privacy-safe matching. Key distinction to say out loud: hashing is one-way, encryption is reversible — they solve different problems.

🛠️ Write the stable split Mod(BaseConvert(Substring(MD5(@sk), 1, 6), 16, 10), 2) and explain each step inside-out.

In a triggered send, AMPscript executes per message in real time as each entry event fires — not in a batch render. Same functions, different blast radius: RaiseError with skip-current true drops just that one message; false can stall or abort the triggered send definition itself, which for order confirmations means the transactional stream stops. And attributes can resolve from the API event payload versus the TSD's sendable DE — mixing those up is the classic 'why is the order total blank?' incident.

🛠️ Default to RaiseError(msg, true, ...) in transactional sends and say why: skip the one message, never halt the stream.

The working set: Now() for current system time, DateParse to turn a string into a real date you can do math on, DateAdd(@d, 6, 'H') to shift, DateDiff(@start, @end, 'D') for whole-unit differences — intervals D, H, M, Y — DatePart to extract components, and Format(@d, 'MMMM d, yyyy') for display. A countdown is just DateDiff(Now(), @end, 'H') with copy branching on the value: ended, under 24 hours, days left.

🛠️ Write the countdown block blind: parse the deadline, DateDiff in hours, branch ended / under-24h / days-left with Format for the date.

Now() returns Central Standard Time — UTC minus 6 — with no daylight saving observed, all year. It is never CDT, so during summer SFMC system time sits one hour behind actual US Central wall-clock. That's the source of off-by-one-hour bugs in countdown timers and 'expires at midnight' logic. Conversion to true UTC is therefore a constant DateAdd(@now, 6, 'H') — no seasonal branch needed, which is the one mercy.

🛠️ Say the sentence exactly: 'system time is Central Standard, UTC−6, year-round, no DST — an hour behind Central wall clock in summer.'

SystemDateToLocalDate(@dt) converts system time to the account or Business Unit timezone — and that's all. It does not know the subscriber's timezone. For genuine per-subscriber local times you store a UTC offset — or a timezone resolved upstream — on the DE, then do it manually: DateAdd(@nowSys, 6, 'H') to true UTC, then DateAdd(@nowUtc, @offsetHrs, 'H') to the subscriber. LocalDateToSystemDate reverses the account-level conversion.

🛠️ Add a UtcOffsetHours column to the sendable DE and write the two-step CST → UTC → subscriber-local conversion once as a reusable snippet.

Core set: Concat — the only concatenation — Substring(@s, start, length) which is 1-indexed, not 0, Length, IndexOf, Replace and ReplaceList (both literal, not regex), Trim, Uppercase, Lowercase, ProperCase, Char, StringToHex, RegExMatch. My hygiene pattern for names is ProperCase(Trim(@name)) — fix casing, strip stray whitespace — applied before any greeting renders.

🛠️ Write Substring(@phone, 1, 3) for an area code and call out the 1-indexing before anyone asks.

FormatCurrency(value, culture, decimals, symbol) — FormatCurrency(Multiply(@price, @qty), 'en-US', 2, '$') yields '$59.98'. FormatNumber(@points, 'N0') adds thousands separators with no decimals. Format is the general one for dates and .NET format strings. The culture argument is the i18n hook: pass the subscriber's locale — 'de-DE', 'fr-FR' — from the DE and separators, symbols and date shapes localise themselves.

🛠️ Format the loyalty banner as Concat(@tier, ' — ', FormatNumber(@points, 'N0'), ' points') and verify 1234 renders as 1,234.

RegExMatch(input, pattern, group) runs a .NET-flavoured regex and returns a captured group — RegExMatch(@full, '(\\w+)', 1) extracts the first word; note the doubled backslash to escape inside the string literal. The limitation to volunteer: there is no native regex replace. Replace and ReplaceList are literal. So extraction is fine in pure AMPscript; substitution means chained literal Replaces or dropping to SSJS.

🛠️ Extract the first word with RegExMatch and a capture group, then state plainly that regex replace requires SSJS.

No arithmetic operators — Add, Subtract, Multiply, Divide, Mod, plus Random. Mod is the workhorse: Mod(@id, 2) gives a clean two-way split, and hashing first — Mod(BaseConvert(Substring(MD5(@sk), 1, 6), 16, 10), 2) — makes the bucket deterministic per subscriber. Composite math nests inside-out: FormatCurrency(Multiply(@price, @qty), 'en-US', 2, '$').

🛠️ Compute an order line total using only Multiply and FormatCurrency, narrating the inside-out evaluation.

Empty(x) is true for both null and empty string — the everyday guard. IsNull(x) is true only for an actual null: a field never populated is null, but one explicitly set to empty string is not. IsNullDefault(x, default) is the concise null-coalesce. Choosing the wrong one is a quiet senior tell — 'never populated' and 'populated with blank' are different business facts, especially in preference data.

🛠️ Pick the guard aloud before coding: 'anything here?' → Empty; 'never set?' → IsNull; 'default a null' → IsNullDefault.

EncryptSymmetric/DecryptSymmetric do reversible AES encryption for tokenizing identifiers in URLs — the secure preference-centre pattern. The arguments reference named keys, salts and IVs stored in Key Management — Setup ▸ Data Management ▸ Key Management — with @null placeholders for the unused inline alternatives, so no secret ever sits in code. Encrypt the subscriber key into an opaque token, pass it via CloudPagesURL, DecryptSymmetric it on the page. Portability flag: this family is not supported in Marketing Cloud Next.

🛠️ Create a named AES key in Setup ▸ Data Management ▸ Key Management, then write the encrypt-link-decrypt round trip end to end.

CloudPagesURL(pageId, 'name', value, ...) builds a signed link to a CloudPage and passes name/value pairs as encrypted query parameters — tamper-evident, so editing the URL invalidates it. On the page, RequestParameter('name') reads and auto-decrypts them. The rule I enforce: never raw PII in a URL — pass the subscriber key or a token, then Lookup everything else server-side on the page.

🛠️ Write the pair blind: CloudPagesURL(1234, 'sk', _subscriberkey) in the email href, RequestParameter('sk') plus a Lookup on the page.

Two different functions, two contexts. In an email, wrap variable-held links as %%=RedirectTo(@url)=%% inside the href — that routes the click through SFMC link tracking; hard-coded URLs get wrapped automatically, but a URL held in a variable needs RedirectTo or the click won't be tracked. On a CloudPage, Redirect(@url) performs a server-side redirect of the visitor to another URL.

🛠️ Sweep templates for href='%%=v(@url)=%%' and replace each with %%=RedirectTo(@url)=%% so dynamic links track.

QueryParameter('p') reads only the URL query string. RequestParameter('p') reads both GET and POST values — and, critically, it's the one that auto-decrypts parameters passed via CloudPagesURL. So for anything arriving from an email link built with CloudPagesURL, or from a submitted form, use RequestParameter. My default is RequestParameter everywhere on pages unless I specifically need to distinguish the source.

🛠️ Read every CloudPagesURL-passed value with RequestParameter, and say why: QueryParameter won't decrypt it.

With Marketing Cloud Connect wired up, AMPscript gets three CRM functions. CreateSalesforceObject('Object', numPairs, 'Field', value, ...) inserts a record and returns the new Id. RetrieveSalesforceObjects('Object', 'Id,Email,Field__c', 'Email', '=', @email) reads matching records into a rowset — comma-separated field list, then a field/operator/value filter. UpdateSingleSalesforceObject('Object', @id, 'Field', value, ...) updates one record by Id, returning 1 or 0. Each is a live API round-trip to the connected org.

🛠️ Write the retrieve-then-update pair for a Contact opt-in flip, always returning Id in the retrieve because the update needs it.

Roughly one second per call — each CRM function is a live synchronous API round-trip to Salesforce. That's fine on a CloudPage or preference centre serving one visitor at a time. In a high-volume send it's catastrophic: a million recipients times a second each blows the send window entirely. Bulk CRM writes belong in Journey Builder's Salesforce activities — Update Contact, Object activities — or batch API and data loads, never in the email render.

🛠️ Give the arithmetic out loud — '1M recipients × ~1s per call = the send window is gone' — then name Journey Builder Salesforce activities as the bulk path.

UpdateSingleSalesforceObject('Contact', @recordId, 'Newsletter_OptIn__c', 'true') — object, record Id, then field/value pairs. It updates exactly one record, addressed by Id, and returns 1 on success, 0 on failure — so you branch on the return to confirm or show a retry. Getting the Id means a RetrieveSalesforceObjects first; CreateSalesforceObject differs by returning the new record's Id instead.

🛠️ Branch on the return every time: IF @ok == 1 show saved, ELSE show retry and log the failure to a DE.

An email send has a send context: a subscriber plus the sendable DE row, so AttributeValue and %%Field%% resolve automatically. A CloudPage has no send context — _messagecontext reports SITE — so identity must arrive via URL parameters, read with RequestParameter, and every data point comes from explicit Lookups. VAWP sits between: it re-renders the send context later, against current data, so what displays can differ from what was sent.

🛠️ State the context before writing a line: 'send context — attributes resolve' versus 'page context — params in, lookups out.'

AMPscript is interpreted top-to-bottom per render — no compilation, nothing cached between subscribers. The dominant cost is data-layer round trips — every Lookup is a query — and external calls; string and math work is noise. So I manage a lookup budget per render: one LookupRows beats N Lookups, match columns are PKs or indexed, and anything heavy — joins, aggregates, API data — moves upstream into SQL Query Activities so the render mostly reads.

🛠️ Count the lookups in any email you're shown and say the number out loud — then propose how many it could be.

A Lookup inside a FOR loop — N data-layer queries per subscriber per render. Multiply by audience: 500k subscribers with a 10-iteration loop is five million queries in one send. The fix: fetch once with LookupRows into memory and iterate with Row/Field; or better, pre-join upstream in a SQL Query Activity so the render needs one seek. This exact collapse is where render-time reductions come from.

🛠️ Refactor one nested-lookup loop live: hoist the Lookup out, replace it with a single LookupRows, iterate in memory.

HTTPGet(url, boolContinueOnError, intEmptyContentHandling, @status) makes one call per unique URL per send and caches the response for every subscriber hitting the same URL. Personalize the query string and every URL becomes unique — cache defeated, thousands of synchronous calls at render, latency and timeout risk. Operational limits: ports 80/443 only, no basic-auth-in-URL. The by-reference status returns 0 success, -1 URL not found, -2 HTTP error, -3 empty content.

🛠️ Handle all four status codes in an ELSEIF ladder, falling back to a Price_Cache DE lookup on failure.

Yes — BuildRowsetFromJSON(@json, '$.products[*]', 1), added Summer '23, parses a JSON string and turns the selected array into a rowset, so the same RowCount/Row/Field loop you use on DE lookups iterates an API response without SSJS. JSONPath supports dot and bracket notation but no filter expressions; the third argument true returns an empty rowset on parse failure instead of erroring. Sibling: BuildRowsetFromString splits a delimited string into loopable rows.

🛠️ Fetch JSON with HTTPGet, build the rowset with the $.products[*] path, and render a three-item loop without touching SSJS.

No try/catch is the single biggest structural limitation. An unhandled runtime error blanks the affected region or fails that subscriber; on a CloudPage it can surface as an error 500. So defence is mandatory: guard every lookup with RowCount, every field with EMPTY or Field(..., 0), supply IIF/IsNullDefault defaults and fallback content blocks, guard side effects by _messagecontext, and use RaiseError skip-current to isolate a bad subscriber. Anything genuinely fallible moves to SSJS, which has real exception handling.

🛠️ Deliver the closing line: 'no try/catch, no rollback — so I code defensively and isolate failures with RaiseError rather than letting one record kill a send.'

Blank personalization is 90% data or context, not code. My path: open Preview and Test and set 'Preview using' to the sendable DE with a subscriber I know has the value — still blank means data source, not syntax. Check the exact field name and casing against the DE column. Confirm the field actually lives in the sendable DE or journey entry data — a reference-only field needs a Lookup. Drop a temporary %%=v(@first)=%% debug output, and for lookups verify the DE name and filter value — whitespace, leading zeros.

🛠️ Open the email ▸ Preview and Test ▸ set 'Preview using' to the sendable DE ▸ step through both a populated and an empty subscriber.

AMPscript for lightweight inline personalization and lookups in email — it is lighter and faster at render for simple ops. SSJS when I need real programming constructs: JSON handling, complex loops, try/catch, or calling the SOAP API via WSProxy on CloudPages and Script Activities. They interoperate — I set an AMPscript variable from SSJS with Variable.SetValue('@x', v) and render it with %%=v(@x)=%% — so I use each for its strength.

🛠️ Build a CloudPage where SSJS does a WSProxy retrieve, hands the result to @result via Variable.SetValue, and AMPscript renders it inline.

The Platform library is always available: low-level functions mirroring AMPscript, like Platform.Function.Lookup, Platform.Response.Write, Platform.Request.GetQueryStringParameter, and Platform.Variable.SetValue. The Core library is the object-oriented layer — DataExtension, Subscriber, Folder, TriggeredSend, plus Script.Util — and must be loaded explicitly with Platform.Load('Core','1.1.1') at the top of the <script runat=server> block before you touch any of its objects.

🛠️ Open a test CloudPage, call DataExtension.Init once without Platform.Load and once with it, and note the error difference.

SSJS runs on a Salesforce-customized Mozilla Rhino engine built to the ECMAScript-3 spec. Some ES5 surface — Array.forEach, map, native JSON, Object.keys — technically exists but is unreliable and inconsistent across SFMC contexts. So I treat it as ES3 in practice: classic for loops with an index, var only, string concatenation instead of template literals, and the Platform JSON functions ParseJSON/Stringify instead of native JSON.

🛠️ Rewrite an items.forEach snippet as a classic for (var i = 0; i < items.length; i++) loop and run it in a test CloudPage.

They share one variable space on a page or email. From SSJS, Variable.GetValue('@subscriberKey') reads an AMPscript variable and Variable.SetValue('@result', myValue) writes one back for AMPscript to render with %%=v(@result)=%%. The leading @ is part of the name and must be in the string. The namespaced forms Platform.Variable.GetValue/SetValue are identical — both read and write the same variable space, so either is correct.

🛠️ Declare @brand in an AMPscript block, read it in SSJS with Variable.GetValue, uppercase it, write it back, and render it with %%=v(@brand)=%%.

CloudPage: has Request/Response, can Write() to the page, takes query-string input, and can make outbound HTTP. Email content: render-time only — no Request/Response, no reliable outbound HTTP; use it for personalization logic, not I/O. Script Activity: batch context under the automation's system user — Write() output is discarded, there is no console, an uncaught error fails the step, and there is a hard 30-minute runtime limit.

🛠️ List the three contexts on paper and, for each, write what breaks: Write target, Request object, HTTP, error behaviour, runtime cap.

After Platform.Load('Core','1.1.1'), DataExtension.Init('Customer_Master') — by Name or CustomerKey — returns a handle; it does not hit the database yet. Then de.Rows.Lookup(['SubscriberKey'],['12345']) reads rows with positional match columns and values, ANDed. de.Rows.Add({col: val}) inserts, de.Rows.Update({Status:'Updated'}, ['SubscriberKey'], ['12345']) updates matches, and de.Rows.Retrieve(filter) takes a {Property, SimpleOperator, Value} filter object for non-equals reads. Everything wrapped in try/catch.

🛠️ On a test CloudPage, Init a sandbox DE, Add a row, Lookup it back, Update it, then Retrieve with a SimpleOperator: 'greaterThan' filter.

Platform.Function.Lookup(de, returnCol, matchCol, val) returns one scalar. LookupRows returns a rowset but is case-insensitive with no sort control. LookupRowsCS is the case-sensitive variant for matching codes and tokens. And LookupOrderedRows(de, rowCount, 'JoinDate desc', matchCol, val) adds the two powers LookupRows lacks: a row cap and ordering — that is how you answer 'give me the most recent order'. Writes are UpsertDE, InsertDE, UpdateDE, DeleteDE with positional column and value arrays.

🛠️ Write a one-liner using LookupOrderedRows to fetch a subscriber's 5 most recent orders sorted by OrderDate desc.

I justify the choice by scale and scope. Platform.Function.UpsertDE for simple single-row, AMPscript-parity writes — easiest, one row at a time, in-BU only. Core's DataExtension.Init(de).Rows.Add/Update for object-style in-session work, a good default for CloudPage forms. And WSProxy createItem('DataExtensionObject', ...) with SaveOptions UpdateAdd for bulk upserts, cross-BU writes, and fine-grained result checking. Saying 'I just use UpsertDE for everything' is the wrong answer at lead level.

🛠️ Draw the three-row decision table — UpsertDE / Core Rows / WSProxy DataExtensionObject — with one 'best for' phrase each, from memory.

WSProxy is a lightweight SSJS wrapper around the same SFMC SOAP API, executed in-session — new Script.Util.WSProxy() after loading Core, no URL, no credentials. It does not bypass SOAP processing; the win is removing the external HTTPS round-trip, the OAuth token exchange on every call, and the JSON-to-object marshaling a REST client pays, while reusing the session's existing auth context. It is not free: retrieves still page at 2,500 rows and still consume request time.

🛠️ Say the three removed costs out loud — network hop, token exchange, marshaling — then write var prox = new Script.Util.WSProxy(); from memory.

var prox = new Script.Util.WSProxy(); then var res = prox.retrieve('DataExtension', ['Name','CustomerKey','CategoryID']);. Signature is retrieve(type, cols, filter, options). SOAP makes you name every property you want back — there is no SELECT *. The response is an envelope, not the data: { Results, HasMoreRows, RequestID, OverallStatus } — the actual array is on res.Results, which I loop with a classic indexed for. No filter means the first 2,500 objects the session can see.

🛠️ Write the retrieve of all DEs with Name, CustomerKey, CategoryID on a test CloudPage and print res.Results.length plus res.HasMoreRows.

A simple filter is one object with exactly three keys — { Property:'CategoryID', SimpleOperator:'equals', Value:12345 } — passed as the third argument to retrieve. The operators to memorise: equals, notEquals, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, isNull, isNotNull, between with a two-element array Value, IN with an array Value, and like with % wildcards. Text comparisons are case-sensitive; the Value's type should match the column.

🛠️ Write three filters from memory — an equals on CategoryID, a like 'Promo%' on Name, and an IN with an array of three keys.

With a complex filter — a different shape with exactly three keys: LeftOperand, LogicalOperator, RightOperand. Each operand is itself a simple filter, like { LeftOperand:{Property:'CategoryID', SimpleOperator:'equals', Value:12345}, LogicalOperator:'AND', RightOperand:{Property:'Name', SimpleOperator:'like', Value:'Promo%'} }. LogicalOperator is 'AND' or 'OR'. And because each operand can itself be another complex filter, you nest them to build arbitrary trees like (A AND B) OR C.

🛠️ Write the (CategoryID equals 12345 AND Name like 'Promo%') OR Name like 'Sale%' filter by nesting one complex object inside another.

With the dynamic row type: prox.retrieve('DataExtensionObject[' + deKey + ']', ['SubscriberKey','Status']) — the DE identifier is concatenated inside the brackets, so the type string is built at runtime. That is what makes a generic DE-lookup page possible. The requested columns must exist on that DE, results land on .Results, and I store the type string in a variable once because getNextBatch requires the identical string byte-for-byte when paging.

🛠️ Build the type string as var TYPE = 'DataExtensionObject[' + deKey + ']'; and run a retrieve against a sandbox DE using a key from a query-string parameter.

Both actually work — DataExtensionObject[My DE] and DataExtensionObject[my_de_key] are equally valid, and saying the Name fails is wrong. But I prefer the CustomerKey every time: it is immutable, while a Name can be renamed out from under your code; it is unambiguous, since Names can collide across BUs and shared DEs; and it is required for shared and parent-BU access. So it is a robustness choice, not a correctness one.

🛠️ Open Email Studio ▸ Subscribers ▸ Data Extensions, click a DE's properties, and copy its External Key — that is the CustomerKey your code should reference.

A SOAP retrieve returns at most 2,500 rows per call, and a BatchSize set higher is silently ignored. The response carries HasMoreRows and a RequestID continuation token. The canonical pattern is a do/while driven by HasMoreRows: first pass calls prox.retrieve(TYPE, COLS), every later pass calls prox.getNextBatch(TYPE, reqID) with the saved token, concatenating each batch's Results into an accumulator until HasMoreRows is false. Type string identical both times.

🛠️ Write the do/while from memory — res = (reqID == null) ? prox.retrieve(TYPE, COLS) : prox.getNextBatch(TYPE, reqID); — against a sandbox DE with over 2,500 rows.

Same result, two call shapes. getNextBatch(type, reqID) is a dedicated method taking the identical type string plus the prior RequestID. The alternative keeps one consistent signature: a normal retrieve(TYPE, COLS, null, { ContinueRequest: reqID }) where the prior RequestID rides in the options object and the filter slot is null, because the original filter is remembered server-side with the token. Same HasMoreRows/RequestID bookkeeping either way — knowing both means no interviewer phrasing blindsides you.

🛠️ Rewrite your paging loop so the continuation branch uses prox.retrieve(TYPE, COLS, null, { ContinueRequest: reqID }) instead of getNextBatch, and confirm identical totals.

I check two levels, because a call can 'return' while rows failed. First the envelope: resp.Status and resp.OverallStatus must both be 'OK' — a non-OK with partial Results means some rows failed silently. Then per-row: each element of resp.Results carries a StatusMessage with the human-readable reason. If either level fails, I log it and fail loudly with Platform.Function.RaiseError so the automation step actually errors instead of reporting a false success.