DEV Community

Cover image for SQL Query Activity in SFMC: Joins, Archives, and Segmentation
SapotaCorp
SapotaCorp

Posted on • Originally published at sapotacorp.vn

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

#webdev

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()
Enter fullscreen mode Exit fullscreen mode

Paired with a daily Schedule Starting Source:

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

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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)