SQL Query Activity in SFMC: Joins, Archives, and Segmentation

A lot of production SFMC work lives inside the SQL Query Activity. It's the only Automation Studio tool that can:

• Join data across multiple Data Extensions.
• Aggregate (SUM, COUNT, DATEDIFF, etc.).
• Write the result into a new or existing DE.

Filter Activity can't do any of that. If the logic needs anything more than a single-attribute filter, SQL Query is the answer.

Here are the three patterns we reach for most.

Pattern 1: Archive the Send Log beyond 10 days

The system-maintained _SendLog DE retains records for only 10 days by default. Many clients need 12-24 months of send history for audit, deliverability analysis, or chargeback investigations.

Solution: a nightly SQL Query Activity that copies yesterday's records into an archive DE.

INSERT INTO [SendLog_Archive_DE]
SELECT *
FROM [_SendLog]
WHERE EventDate >= DATEADD(DAY, -1, GETDATE())
  AND EventDate < GETDATE()

Paired with a daily Schedule Starting Source:

Schedule (daily at 01:00)
  -> SQL Query Activity (copy yesterday's Send Log rows to archive)

This is also how you preserve other system DEs that have short retention - _Open, _Click, _Bounce.

Pattern 2: Join multiple DEs for a segmented send

A campaign targets "customers who purchased in the last 30 days AND are enrolled in the loyalty program." The data lives across three DEs: Customer_DE, Order_DE, Loyalty_DE.

Filter Activity can't join. SQL Query can:

SELECT
  c.CustomerID AS SubscriberKey,
  c.EmailAddress,
  c.FirstName,
  l.Tier AS LoyaltyTier,
  MAX(o.OrderDate) AS LastPurchaseDate
INTO [Campaign_Audience_DE]
FROM [Customer_DE] c
INNER JOIN [Order_DE] o ON o.CustomerID = c.CustomerID
INNER JOIN [Loyalty_DE] l ON l.CustomerID = c.CustomerID
WHERE o.OrderDate >= DATEADD(DAY, -30, GETDATE())
  AND l.Enrolled = 1
GROUP BY c.CustomerID, c.EmailAddress, c.FirstName, l.Tier

The output Campaign_Audience_DE becomes the sendable DE for the Journey or Send.

Pattern 3: Calculated fields for personalization

Before the send, pre-compute fields that AMPscript would otherwise have to derive at send time:

SELECT
  CustomerID AS SubscriberKey,
  EmailAddress,
  FirstName,
  CASE
    WHEN LoyaltyPoints >= 1000 THEN 'Gold'
    WHEN LoyaltyPoints >= 500  THEN 'Silver'
    ELSE                          'Standard'
  END AS Tier,
  DATEDIFF(DAY, GETDATE(), RenewalDate) AS DaysUntilRenewal
INTO [Send_Ready_DE]
FROM [Customer_DE]

Now %%Tier%% and %%DaysUntilRenewal%% are straight Personalization Strings instead of AMPscript logic. Simpler email templates, faster render, easier for marketing ops to read.

SQL Query Activity vs Filter Activity

Long-running confusion on teams new to SFMC:

	SQL Query Activity	Filter Activity
Syntax	SQL you write by hand	Point-and-click UI
Joins across DEs	Yes	No
Aggregations (SUM, COUNT)	Yes	No
Calculated fields	Yes	No
Output	Writes to a DE	Refreshes a Filtered Group or Filtered DE
Best for	Complex logic, cross-DE segmentation	Simple single-attribute segmentation

If the logic is "SELECT rows WHERE some_field = some_value" on a single DE, Filter Activity is the lighter option. Anything else - SQL Query.

Syntax gotchas worth knowing

• Square brackets around DE names: FROM [My_DE], not FROM My_DE. Required if the DE name has spaces or special characters.
• SQL Server dialect: SFMC SQL is T-SQL. DATEADD, GETDATE, ISNULL, square-bracket escapes. Not MySQL.
• INSERT INTO vs SELECT INTO: both work. INSERT INTO appends to an existing DE; SELECT INTO creates a new DE (only allowed when configured in the Activity).
• No schema prefix: DE names don't need a schema prefix like dbo..

Retention on the target DE

If you INSERT into a long-lived archive DE, set a Data Retention Policy on that DE. Otherwise it grows unbounded and becomes slow to query over time.

Takeaway

SQL Query Activity is the Swiss Army knife of Automation Studio. It covers archiving, cross-DE joins, calculated fields, and anything a Filter can't do. Spend an afternoon learning the T-SQL dialect and the escape rules - it pays back on every production SFMC engagement.

---

Architecting an SFMC data flow? Our Salesforce team writes SQL Query Activities and Data Extension joins on production Marketing Cloud builds. Get in touch ->

See our full platform services for the stack we cover.