Data Management22% of the exam
Query Activity SQL with data views and joins
What a SQL Query activity accepts and rejects, how it writes to its target data extension, how joins, UNION, CASE and GROUP BY shape the rows it returns, and which system data view holds what, for how long, in which time zone and at which business-unit level.
Given a scenario, apply SQL to produce the desired results.
~7 min read · 13 practice questions ·
What the exam expects
- Spot the statements, comments and constructs a SQL Query activity rejects, and rewrite them with supported SQL.
- Choose Append, Update or Overwrite for the target data extension.
- Predict the rows a JOIN, UNION, CASE, aggregate or GROUP BY query returns, including NULL handling.
- Reference data extensions correctly: names with spaces, names versus external keys, and the
ENT.prefix. - Pick the data view and column that hold the data, and adjust for its retention, time zone and business-unit scope.
- Key terms: SQL Query activity (the Automation Studio activity that runs a SELECT and writes the result to a data extension); target data extension (where results land); data action (Append, Update or Overwrite); system data view (a read-only, Salesforce-created table such as
_Sent); Enterprise 2.0 (a parent account with child business units).
Key facts
Statements
- Detail
- SELECT only, against data extensions or data views
Comments
- Detail
- Open-ended
--comments are unsupported
Isolation
- Detail
- READ UNCOMMITTED, equal to WITH (NOLOCK)
Timeout
- Detail
- 30 minutes
Known issue
- Detail
- A Decimal field used in a query doesn't produce decimal output
Primary key size
- Detail
- Don't set a data extension primary key over 1700 bytes
Overwrite
- Detail
- Deletes all existing target rows first
Engagement view dates
- Detail
- Central Standard Time, no Daylight Saving Time
_Job DeliveredTime- Detail
- NULL for any triggered send
_Subscribers- Detail
- Returns results at the Enterprise level only
Sources for this section (5)
- Why should you drag columns into the query editor instead of typing their names?
- When does a send that just finished show up in
_Sent? - Does a Unicode search string in a query need an N prefix?
- Why can a query that reaches past six months of a data view still be a concern?
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 Developer pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 25 lessons of the Marketing Cloud Engagement Developer guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Practice exams purchase adds inline bank questions and topic practice (13 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