So your Query Activity failed. What next?
SFMC Support can certainly tell you exactly what the error was, and now you can (sometimes) see some error details in the Activity tab of Automation Studio.
Here are a few things that I’ve found are common things that cause Query Activities to fail.
If your query results in duplicate rows not allowed by the primary key, then you’ll need to change your primary key or de-duplicate the rows that result from your query. Here’s my go-to method for deduplication. The innermost query sorts by
insertDate, groups by subscriber key and assigns a
ranking to each row. The outermost query selects only the oldest result per subscriber key.
If you have fields in your target Data Extension that are non-nullable, you need to ensure none of the columns in your query return a null value. You can utilize the
isnull() SQL function to handle that situation:
If any of your source data extension columns are larger than the target, then you can adjust the length of the target column or use the
left() SQL function to trim the value:
Probably the most frustrating error is the the 30 minute timeout. If your query won’t run in that amount of time, then it’ll error out. There are a bunch of different ways to address query timeouts. Here are some tips:
Easy fix for this one. Just re-select the target data extension in your Query Definition.
You can’t, for example, insert a string value of
$12.34 into a Decimal field. What I typically do in this scenario is to check the data types of the source data extension(s) and ensure that they match the target data extension column data types. If they don’t, then I’ll either recreate the target column with the correct data type or cast/convert/fix the source column value in the query.
If the custom preference center in your integrated Marketing Cloud account needs to update Salesforce object attributes, you're probably acquainted…