Data Extension functions — Marketing Cloud AMPscript reference | Cleon

The Data Extension functions are the most safety-critical surface in AMPscript. They read and write production data; a misaligned argument pair lands the wrong value in the wrong column with no error. The write functions are silent at every interesting failure (wrong PK → duplicates, wrong column name → write skipped, type mismatch → coercion). The read functions cap at 2000 rows without warning. And per-recipient lookups in an email body run once per recipient — a 50,000-row send runs 50,000 reads against your DE.

This page is the inventory + the patterns. The two gotchas page items that fire most often (LookupRows cap at 2000 and silent UpsertData duplicates) are covered in detail; the SSJS sibling debugging snippet walks through the same failure shape with the SQL queries that detect it after the fact.

Official syntax

Reads — single value

%%[
  /* Lookup — return one column value, or NULL when no match */
  SET @tier = Lookup("master_segments", "Tier", "SubscriberKey", _subscriberKey)

  /* Always default the result if it might miss */
  IF Empty(@tier) THEN
    SET @tier = "Standard"
  ENDIF
]%%

Reads — multiple rows

%%[
  /* LookupRows — capped at 2000 rows, silently */
  SET @rows = LookupRows("active_promos", "Region", "LATAM")
  SET @n = RowCount(@rows)

  /* Iterate — Row() is 1-based, like everything else */
  FOR @i = 1 TO @n DO
    SET @r = Row(@rows, @i)
    SET @id = Field(@r, "PromoId")
    SET @label = Field(@r, "Label")
  NEXT @i

  /* LookupOrderedRows — paged + ordered. 4th arg is row count
     (0 = all rows up to the 2000 cap), 5th is order column with
     "asc" / "desc" suffix */
  SET @latest = LookupOrderedRows("orders", 1, "OrderDate desc",
                                   "SubscriberKey", _subscriberKey)
  IF RowCount(@latest) > 0 THEN
    SET @lastOrder = Row(@latest, 1)
    SET @amount = Field(@lastOrder, "Amount")
  ENDIF

  /* LookupRowsCS — case-sensitive variant */
  SET @exact = LookupRowsCS("master_subs", "Status", "Active")
]%%

Writes

%%[
  /* InsertData — variadic pairs after the DE name.
     Always pair (columnName, value) — misalignment is silent. */
  InsertData(
    "de_log_writes",
    "RunId", @runId,
    "Step", "process",
    "Message", "completed",
    "Ts", Now()
  )

  /* UpdateData — pairs of (keyCol, keyVal) followed by pairs of
     (setCol, setVal). The argument count tells AMPscript which is
     which: first N pairs are keys, remaining pairs are values to set. */
  UpdateData(
    "master_subs",
    1,                                  /* number of key pairs */
    "SubscriberKey", @subKey,           /* key pair */
    "Status", "Inactive",               /* set pair */
    "UpdatedAt", Now()                  /* set pair */
  )

  /* UpsertData — same signature as UpdateData, but inserts if key
     doesn't match. Returns 1 on success, 0 on failure. Silent
     duplicates if destination PK is wrong — see gotcha #4. */
  SET @result = UpsertData(
    "master_subs",
    1,
    "SubscriberKey", @subKey,
    "Status", @status,
    "UpdatedAt", Now()
  )

  /* DeleteData — match-pair only, no set-pair */
  DeleteData("de_stg_temp", "RunId", @runId)
]%%

Atomic claim — `ClaimRow`

%%[
  /* ClaimRow — atomically read + flag the first matching row.
     Used for one-of-N giveaway / first-come-first-served patterns
     (gift cards, codes, limited offers). Atomic at the MC backend. */
  SET @row = ClaimRow(
    "de_gift_codes",
    "Code",                              /* return column */
    "IsClaimed",                         /* status column to flag */
    "true",                              /* value to write */
    "IsClaimed", "false",                /* filter: only unclaimed */
    "Region", "LATAM"                    /* additional filter */
  )

  IF NOT Empty(@row) THEN
    SET @giftCode = @row
  ELSE
    /* No codes left — handle gracefully */
    SET @giftCode = "SORRY-OUT-OF-STOCK"
  ENDIF
]%%

The supported set:

| Function | Purpose | Notes | |---|---|---| | Lookup(de, returnCol, filterCol, filterVal) | Read one column value | Returns NULL on no match — no error | | LookupRows(de, filterCol, filterVal) | Read all matching rows | Capped at 2000 silently | | LookupRowsCS(de, filterCol, filterVal) | Case-sensitive LookupRows | Same 2000 cap | | LookupOrderedRows(de, count, orderBy, filterCol, filterVal) | Read N rows in order | count=0 means all up to 2000; orderBy like "Date desc" | | LookupOrderedRowsCS(de, count, orderBy, filterCol, filterVal) | Case-sensitive LookupOrderedRows | Same cap | | Row(rowset, n) | Get nth row from LookupRows result | 1-based | | Field(row, columnName) | Get column from a row | Returns NULL on missing column — no error | | RowCount(rowset) | Count rows in a result | Top-level count, never exceeds 2000 | | InsertData(de, col1, val1, col2, val2, ...) | Insert one row | Pair-aligned args; misalignment is silent | | UpdateData(de, nKeys, key1, kval1, ..., set1, sval1, ...) | Update matching rows | Requires count of key pairs as 2nd arg | | UpsertData(de, nKeys, key1, kval1, ..., set1, sval1, ...) | Update or insert | Returns 1/0; silent duplicates if PK wrong | | DeleteData(de, col1, val1, ...) | Delete matching rows | Match-pairs only | | ClaimRow(de, returnCol, flagCol, flagVal, filterCol, filterVal, ...) | Atomic read + flag | Used for one-of-N giveaway patterns |

Reference:

What survives in production

`LookupRows` caps at 2000 rows — silently

Same shape as the SSJS Platform.Function.LookupRows cap at 2500. Email logic that iterates over the result misses everyone past row 2000 with no warning.

%%[
  SET @rows = LookupRows("active_promotions", "Region", "LATAM")
  SET @n = RowCount(@rows)
  /* @n is at most 2000 — assert and alert when it hits the cap */

  IF @n == 2000 THEN
    /* Either alert via a log DE write, or you've already pivoted to a
       pre-shaped staging DE upstream. The Cleon rule is the latter. */
    InsertData("de_log_alerts",
      "JobID", jobid,
      "Source", "AMPscript LookupRows",
      "Issue", "Hit 2000-row cap",
      "Ts", Now())
  ENDIF
]%%

The right defense is upstream: a SQL Activity writes a de_email_<purpose> DE with the rows already capped, ordered, and shaped for the email's needs. AMPscript reads that DE with a LookupRows call where 2000 isn't a constraint. See gotchas — #3.

`UpsertData` silently inserts duplicates when the destination PK is wrong

UpsertData is upsert in name only — the MC backend checks the destination DE's primary key. If the PK is missing or on the wrong column, every call becomes an insert and duplicates accumulate. The function returns 1 (success) either way.

%%[
  /* AT RISK — assumes destination DE has SubscriberKey as PK.
     If it doesn't, every script run adds a row instead of updating. */
  SET @result = UpsertData(
    "master_subs",
    1,
    "SubscriberKey", _subscriberKey,
    "Status", "Active",
    "UpdatedAt", Now()
  )
  /* @result = 1 → "success" — but the row may have been INSERTED
     when you intended UPDATE. Verify destination PK before going live. */
]%%

Defense is the same as the SSJS rule: verify the destination DE's primary key configuration in the MC UI before any UpsertData reaches production. The SSJS sibling debugging snippet walks through the SQL queries that detect the failure after the fact — same diagnostic applies to AMPscript-driven writes against the same DEs. See gotchas — #4.

Argument-pair misalignment is silent

InsertData / UpdateData / UpsertData take pairs of (columnName, value). Forgetting one element shifts every following pair by one position, and the writes land in the wrong columns. AMPscript doesn't validate the alignment.

%%[
  /* AT RISK — missing Message column name; "completed" lands in Step,
     Now() lands in Message, RunId becomes "RunId" literally */
  InsertData(
    "de_log_writes",
    "RunId", @runId,
    "Step",          /* ← missing value here */
    "completed",
    "Message",
    Now()
  )

  /* DURABLE — formatted vertically with pair-per-line aligns visually,
     and the next person reading the code spots a missing pair */
  InsertData(
    "de_log_writes",
    "RunId",    @runId,
    "Step",     "process",
    "Message",  "completed",
    "Ts",       Now()
  )
]%%

The Cleon convention: every InsertData / UpdateData / UpsertData call uses pair-per-line formatting with aligned columns. The visual structure is the validation — code review catches a missing value immediately.

`Row` and column access — 1-based, NULL on miss

%%[
  SET @rows = LookupRows("orders", "SubscriberKey", _subscriberKey)
  SET @n = RowCount(@rows)

  IF @n > 0 THEN
    /* 1-based — Row(@rows, 0) is undefined behavior; Row(@rows, 1) is the first */
    SET @first = Row(@rows, 1)
    SET @amount = Field(@first, "Amount")

    /* If the DE doesn't have an "Amount" column, @amount is NULL — silent */
    IF Empty(@amount) THEN
      SET @amount = 0
    ENDIF
  ENDIF
]%%

Field(row, "wrongColumnName") returns NULL with no error. Always default the result of a Field access if the column might not exist or might not have a value for this row. See gotchas — #2.

Per-recipient `LookupRows` at scale = N reads per send

%%[
  /* AT RISK — runs once per recipient. On a 50k send, that's 50,000
     reads against active_promos. The DE is hit hard during the send. */
  SET @promos = LookupRows("active_promos", "Region", @region)
  SET @n = RowCount(@promos)
]%%

For audiences over a few thousand, the per-recipient cost adds up — both in DE load and in send-time latency. The pattern: do a single SQL Activity upstream that joins audience × promos into a de_email_<send>_promos DE keyed by SubscriberKey. AMPscript reads that DE once per recipient with a thin Lookup (or no lookup at all if the columns are already on the sendable DE).

%%[
  /* DURABLE — sendable DE already has the joined data;
     no LookupRows needed at render time */
  SET @promoLabel = AttributeValue("PromoLabel")
  SET @promoCode  = AttributeValue("PromoCode")
]%%

The Cleon principle: AMPscript should be thin. The data work happens upstream; the email body interpolates.

`ClaimRow` is the only atomic write — use it for first-come-first-served

ClaimRow is rare in AMPscript catalogs but critical for one-of-N giveaway patterns (limited-supply gift codes, "the first 100 to click get X"). The MC backend handles the atomicity, so two recipients hitting the same code at the same moment don't get the same value.

%%[
  /* The supply DE has rows (Code, IsClaimed); the function finds the
     first row with IsClaimed=false matching any extra filters,
     sets IsClaimed=true, and returns the Code. */
  SET @giftCode = ClaimRow(
    "de_gift_codes",
    "Code",
    "IsClaimed",
    "true",
    "IsClaimed", "false",
    "Region", @region
  )

  IF Empty(@giftCode) THEN
    /* Supply exhausted */
    SET @giftCode = ""
  ENDIF
]%%

The non-obvious bit: the "atomic" guarantee only holds when the supply DE has a primary key on the Code column (or whatever the return-column is). Without PK, the function can hand out the same code twice. Same primary-key discipline as UpsertData. See gotchas — #4.

Write functions return `1`/`0` — log the result every time

Same pattern as the gotchas page item #10 for Cloud-write functions: AMPscript's DE-write functions return a numeric result, not an exception. A 0 return means the write didn't happen, the email body still renders, and you find out a week later when the log DE you were writing to is empty.

%%[
  SET @result = UpsertData(
    "de_log_engagement",
    1,
    "SubscriberKey", _subscriberKey,
    "LastEmailOpened", Now()
  )

  /* Log the outcome — a second write to a more reliable DE */
  InsertData(
    "de_log_writes",
    "JobID", jobid,
    "SubscriberKey", _subscriberKey,
    "Operation", "de_log_engagement.upsert",
    "Result", @result,
    "Ts", Now()
  )
]%%

If the second write is to the same DE family that's failing, the logging itself can fail silently — write the log to a different DE on a different schema (no exotic columns, no complex PK), so the log is more likely to land even when the primary write didn't.

Quick decision

Use Lookup when:

You need one column value for the current recipient. Always default the result with Empty().

Use LookupRows (or LookupOrderedRows) when:

You need multiple rows and you've verified the result will be under 2000. Otherwise, pre-shape upstream in SQL.

Use LookupOrderedRows when:

You need "the latest N" or "the top N by score". The 5th arg's "asc" / "desc" suffix is the cleanest ordering syntax.

Use Field(Row(@rs, n), "col") when:

Accessing a specific column in a specific row of a rowset. Default the result; column name typos return NULL silently.

Use InsertData when:

Writing a log row, a CloudPage form submission, an event row. Format args pair-per-line.

Use UpdateData when:

The destination row is guaranteed to exist by another upstream step. Otherwise use UpsertData.

Use UpsertData when:

Insert-or-update semantics are needed. Always verify destination PK first.

Use ClaimRow when:

One-of-N giveaway pattern. Supply DE must have PK on the return column. Atomic at the backend.

Pre-compute in SQL upstream when:

The personalization is the same shape for every recipient. One SQL pass beats N AMPscript reads.
The lookup involves multi-DE joins, aggregations, or row-count limits beyond 2000.
The write is part of an audience-build step. Use SQL Activity (INSERT INTO ... SELECT) instead of per-recipient AMPscript writes.

Basics — supported language surface
Validation functions — Empty() for defaulting Lookup results
MC AMPscript gotchas — see #2 (Lookup silent NULL), #3 (LookupRows 2000 cap), #4 (UpsertData silent duplicates), #10 (write functions silent failure pattern)
MC SSJS — Debugging UpsertData duplicates — the SQL diagnostic queries that detect silent-duplicate writes from any source, AMPscript or SSJS
MC SQL — INSERT INTO — the upstream pattern for shaping de_email_* DEs that thin out AMPscript reads
MC SQL Style Guide — the upstream discipline that prevents most of the failure modes on this page

More AMPscript reference pages incoming: Subscriber/Profile · Cloud-write · Encoding/Hashing · Style Guide.

Official syntax

Reads — single value

Reads — multiple rows

Writes

Atomic claim — ClaimRow

What survives in production

LookupRows caps at 2000 rows — silently

UpsertData silently inserts duplicates when the destination PK is wrong

Argument-pair misalignment is silent

Row and column access — 1-based, NULL on miss

Per-recipient LookupRows at scale = N reads per send

ClaimRow is the only atomic write — use it for first-come-first-served

Write functions return 1/0 — log the result every time