Subscriber and Data Management26% of the exam
SQL query segmentation across data extensions
When a SQL Query activity beats a data filter, which SQL the activity supports (joins and nested queries yes; CTEs, temporary tables and variables no), how to aggregate with GROUP BY and HAVING, draw a random sample with TOP n PERCENT and NEWID(), and reach shared data extensions with ENT. and the data extension name.
Given a customer's business requirements, configure segmentation tools to model subscribers and data.
~5 min read · 6 practice questions ·
What the exam expects
- Choose a SQL Query activity over a data filter when a segment needs more than one data extension.
- Recognize supported and unsupported SQL in the activity, and rewrite unsupported constructs.
- Write aggregate and random-sample segments with GROUP BY, HAVING, TOP n PERCENT and NEWID().
- Reference shared and parent-account data extensions correctly in an Enterprise 2.0 account.
- Key terms: SQL Query activity (Automation Studio activity that runs a SELECT and writes to a target data extension); target data extension (where results land; can't be a source of the same query); nested query (a subquery inside another statement); ENT. prefix (reaches shared or enterprise parent data extensions).
Key facts
Query versus filter
- Detail
- Queries evaluate more than one data extension at a time
Filter activity result
- Detail
- Refreshes each time the activity runs
Target data extension
- Detail
- Created before the query activity
Nested queries
- Detail
- Supported
CTEs, temp tables, variables
- Detail
- Unsupported
NOLOCK
- Detail
- Unnecessary; queries run READ UNCOMMITTED
Random share
- Detail
- TOP n PERCENT with ORDER BY newid()
Shared or parent data extension
- Detail
- ENT. prefix plus the data extension name, not the external key
- Which SQL Server version is the activity based on?
- Why is adding WITH (NOLOCK) to a query pointless?
- When will the activity run a statement at all?
- Which procedural keywords besides variables does the reference list as unsupported?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the Marketing Cloud Engagement Specialist pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 31 lessons of the Marketing Cloud Engagement Specialist guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Practice exams purchase adds inline bank questions and topic practice (6 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Choose guide-only, practice-only, or save $4 with both