marketing-cloud-sql-queries

# Marketing Cloud SQL Queries

This skill activates when a practitioner needs to write, debug, or optimize a SQL Query Activity in Salesforce Marketing Cloud Automation Studio. It covers T-SQL dialect rules, system data view access, performance constraints, and the SELECT INTO output model — and explicitly excludes SOQL, standard relational SQL databases, and Data Cloud SQL.

---

## Before Starting

Gather this context before working on anything in this domain:

- Confirm the target is a Marketing Cloud Data Extension (DE), not a CRM object. SQL Query Activities output ONLY to a DE via SELECT INTO — there are no result sets, cursors, or direct API responses.
- Ask whether the Query Activity action is set to **Overwrite** (default — truncates target before insert) or **Update** (upserts based on Primary Key). Choosing the wrong action overwrites subscriber data silently.
- Determine the date range. System data views (_Sent, _Open, _Click, _Bounce, _Subscribers, _Job) retain only approximately 6 months of data. Queries spanning longer ranges return incomplete results without error.

---

## Core Concepts

### T-SQL Dialect, Not Standard SQL

Marketing Cloud SQL Query Activities use a T-SQL-like dialect, not ANSI SQL, MySQL, or PostgreSQL. Key differences that cause real failures:

- Date functions: use `GETDATE()`, `DATEADD()`, `DATEDIFF()`, `CONVERT()` — never `NOW()`, `SYSDATE()`, or `DATE_SUB()`
- No stored procedures, no temp tables (`#tmp`), no cursors, no CTEs (`WITH` clauses are not supported)
- Window functions (ROW_NUMBER OVER, RANK OVER) are NOT supported
- `IS NULL` / `IS NOT NULL` are required for null checks — `= NULL` always evaluates false
- `DISTINCT`, `GROUP BY`, `HAVING`, and `TOP` are supported

### SELECT INTO Is the Only Output Mechanism

Every query must end with a SELECT INTO targeting a DE:

```sql
SELECT
    s.SubscriberKey,
    s.EmailAddress,
    s.EventDate
INTO target_DE_name
FROM _Sent s
WHERE s.EventDate >= DATEADD(DAY, -30, GETDATE())
```

There is no `INSERT INTO ... SELECT` syntax. The target DE must already exist. Column names in the SELECT list must match DE field names exactly (case-insensitive but name-sensitive). If a column is not in the SELECT list, it receives NULL or its default value.

### System Data Views and Their Retention Window

Six system data views are queryable only from a SQL Query Activity (not via API):

| View | Content | Retention |
|---|---|---|
| `_Sent` | Send events per job/subscriber | ~6 months |
| `_Open` | Open tracking events | ~6 months |
| `_Click` | Click tracking events | ~6 months |
| `_Bounce` | Bounce events | ~6 months |
| `_Subscribers` | Subscriber list status | Current state |
| `_Job` | Email send job metadata | ~6 months |

Joins between system data views must use `JobID` + `SubscriberKey` as the composite key. Joining on `EmailAddress` alone causes fan-out because one email address can have multiple SubscriberKey values across lists.

---

## Common Patterns

### Pattern 1: Segment Engaged Subscribers from System Data Views

**When to use:** Building a re-engagement or suppression segment based on recent open or click activity.

**How it works:** Scope the date range to 90 days or less to stay well within the 6-month retention window and avoid full-table scans. Join `_Open` to a subscriber DE on `SubscriberKey` to enrich output.

```sql
SELECT DISTINCT
    o.SubscriberKey,
    o.EmailAddress,
    MAX(o.EventDate) AS LastOpenDate
INTO Engaged_Last_90_Days
FROM _Open o
WHERE o.EventDate >= DATEADD(DAY, -90, GETDATE())
GROUP BY o.SubscriberKey, o.EmailAddress
```

**Why not the alternative:** Using `SELECT *` or omitting the date WHERE clause causes a full scan of all 6 months of open data. On high-volume accounts this reliably hits the 30-minute timeout.

### Pattern 2: Upsert Subscriber Attributes from a Source DE

**When to use:** Syncing computed values (e.g., loyalty tier, predicted churn score) into a master profile DE without destroying existing rows.

**How it works:** Set the Query Activity action to **Update** and ensure the target DE has a Primary Key field that matches the join key. Use a straightforward SELECT INTO — the platform handles the upsert logic.

```sql
SELECT
    s.SubscriberKey,
    s.LoyaltyTier,
    s.UpdatedDate
INTO Master_Profile_DE
FROM Source_Tier_DE s
WHERE s.UpdatedDate >= DATEADD(DAY, -1, GETDATE())
```

**Why not the alternative:** Using **Overwrite** action on a master profile DE wipes all rows not present in the current query result, including subscribers who did not interact during the query window.

---

## Decision Guidance

| Situation | Recommended Approach | Reason |
|---|---|---|
| Query must append new rows without removing existing ones | Set Query Activity action to **Update**; ensure target DE has Primary Key | Overwrite (default) truncates the target before inserting |
| Date range exceeds 6 months | Split into multiple queries per 6-month window and union via intermediate DEs | System data views do not retain data beyond ~6 months |
| Query frequently times out | Add `WHERE EventDate >= DATEADD(DAY, -N, GETDATE())` using indexed EventDate | Non-date-bounded queries do full scans |
| Need to deduplicate output rows | Use `SELECT DISTINCT` or `GROUP BY` with `MAX()` / `MIN()` aggregates | Window functions like `ROW_NUMBER() OVER` are not supported |
| Need to test a query before automation | Use Query Studio (SF Labs AppExchange app) against live data | Automation Studio has no interactive result preview |
| Joining system data view to subscriber DE | Use `JobID + SubscriberKey` as composite join key | Joining on `EmailAddress` alone causes fan-out on multi-list accounts |

---

## Recommended Workflow

Step-by-step instructions for an AI agent or practitioner working on this task:

1. **Clarify the output target and action setting.** Confirm the target DE name exists, identify its Primary Key field, and determine whether the Query Activity action should be Overwrite (full refresh) or Update (upsert/append).
2. **Identify source data views and date bounds.** Determine which system data views or DEs are involved. If using system data views, establish a date range no longer than 6 months; prefer 30–90 days for performance.
3. **Draft the SELECT INTO statement.** Use T-SQL date functions (`GETDATE()`, `DATEADD()`). Ensure all WHERE conditions reference indexed or Primary Key columns. Avoid `SELECT *` — enumerate only needed columns.
4. **Validate join keys.** If joining a system data view to a DE, use `JobID + SubscriberKey` as the composite key. If joining DEs to each other, use the DE Primary Key field.
5. **Test in Query Studio before embedding in Automation.** Run the query in Query Studio (SF Labs AppExchange) to verify result shape, row count, and absence of timeout. Confirm the output matches expected DE column names.
6. **Embed in Automation Studio and set the action.** Add the Query Activity to the automation, set Overwrite or Update explicitly (do not rely on the default), and schedule or trigger appropriately.
7. **Monitor the first run.** Confirm the target DE row count matches expectation. Check Automation Studio activity history for errors or timeouts. If run time exceeds 20 minutes, narrow the date range further.

---

## Review Checklist

Run through these before marking work in this area complete:

- [ ] SELECT INTO references a DE that already exists with matching column names
- [ ] Query Activity action (Overwrite vs Update) is explicitly set and correct for the use case
- [ ] WHERE clause scopes date range to 6 months or less for any system data view source
- [ ] All WHERE and JOIN conditions reference indexed columns or Primary Key fields
- [ ] T-SQL date functions used throughout — no `NOW()`, `SYSDATE()`, or `DATE_SUB()`
- [ ] No stored procedures, temp tables, CTEs (`WITH`), or window functions in query
- [ ] NULL checks use `IS NULL` / `IS NOT NULL`, not `= NULL`
- [ ] Query tested in Query Studio before embedding in Automation

---

## Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. **Overwrite Is the Default — Silently Wipes Target DE** — If the Query Activity action is not explicitly set to Update, it defaults to Overwrite, which truncates the entire target DE before inserting results. A query that returns zero rows (e.g., due to a typo in a date filter) will silently empty the target DE.
2. **30-Minute Hard Timeout with No Partial Commit** — Queries that exceed 30 minutes are killed with no partial results written to the target DE. The Automation activity shows an error, but the target DE state depends on whether Overwrite had already truncated it. Scope date ranges tightly to prevent this.
3. **System Data Views Are Read-Only and Not Visible in DE Navigator** — System data views cannot be browsed in Contact Builder or All Subscribers. They appear only in SQL Query Activity context. Attempting to import, export, or modify them through other tools fails silently or with a generic error.

---

## Output Artifacts

| Artifact | Description |
|---|---|
| SQL Query Activity statement | A complete T-SQL SELECT INTO statement ready to paste into Automation Studio Query Activity |
| Query Activity configuration note | Specifies target DE name, Primary Key field, and action setting (Overwrite/Update) |
| Query Studio test checklist | Steps to validate query result shape and row count before automation embedding |

---

## Related Skills

- admin/marketing-cloud-engagement-setup — Use when the underlying MC org, business unit, or Automation Studio configuration needs to be validated before query work begins
- data/data-extension-design — Use when the target or source DE structure needs to be designed or reviewed as part of the query project
- data/bulk-api-and-large-data-loads — Use when data movement is between SFMC and the Salesforce CRM org rather than within SFMC itself