Troubleshooting Queries in SFMC

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.

Primary key violation

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.

Inserting a null value into a non-nullable field

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:

Inserting a value too long for the field (truncation)

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:

Timeout

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:

  1. First and foremost, you need to reduce the number of row you’re querying. This can be altering your date range or adding additional criteria to target rows more specifically.
  2. Leverage the primary keys for speed.  While we don’t have any insight in to the indexes that SFMC behind the scenes, it’s a good bet the primary keys are indexes.  So the more utilize their values in the where-clause, the better.
  3. Don’t join multiple System Data Views in your query.  It’s a pain, but it’s a good practice to have a separate query for each activity (sends, opens, clicks, etc.).  Last resort is to make your own copies of the System Data Views with primary keys and refresh them on an interval.  The data extension versions will perform better.
  4. Make sure your where-clause conditionals are sargable to reduce the number of type-conversions and to optimize the selection:
  1. If it’s still timing out, leave a comment below with some details. I have some other tricks to share.

Target Data Extension has been deleted

Easy fix for this one. Just re-select the target data extension in your Query Definition.

Mis-match data types

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.

avatar
4 Comment threads
4 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
5 Comment authors
Chetan Anand SinghFitzAli ArshadAdam SpriggsJosh Recent comment authors
  Subscribe  
newest oldest
Notify of
Josh
Guest
Josh

are the 30 minute timeouts new in the past year? Or are they specific to queries triggered by an API call?

And is the timeout on an Automation Task or Activity?

6 months ago, I would start automations via the UI that ran for well over an hour with no issues, but now that I’m using the API, my automations are failing at 30 minutes run time +/- 1 minute. That said, these are different automations.

I’d love some clarity here if you have any thoughts!

Ali Arshad
Guest
Ali Arshad

An union between 2 DE’s, 1 with 17 million records the other with 45 million records and target data extension is being overwritten. Failing due to timeout, what to do.

Fitz
Guest
Fitz

Thanks for this! The primary key workaround is a lifesaver for moving SF contacts into my Customer Segment DEs!

Chetan Anand Singh
Guest
Chetan Anand Singh

Hi @Adam Spriggs…Can you help me with the below query as it is timing out:
We believe it is because of the DE’s we are inner joining to get the target audience. This DE has 21 million records. EXT_Nucleo_Comunicaciones_IBP_Previa IBP DE has 60k records and Account_Salesforce has 21 million records.

SELECT

CASE

WHEN IBP.NumIBP is null THEN IBP.CampoPersonalizable1

ELSE IBP.NumIBP

END AS NumIBP,

IBP.Nombre,

IBP.Apell1,

IBP.Apell2,

IBP.Email,

IBP.PIN,

IBP.Sexo,

IBP.Gerencia,

IBP.NivelTarj,

IBP.IdiomaPref,

IBP.IdiomaComu,

IBP.Colectivo,

IBP.CampaignType,

IBP.CampaignSubtype,

IBP.CampoPersonalizable1,

IBP.CampoPersonalizable2,

IBP.CampoPersonalizable3,

IBP.CampoPersonalizable4,

IBP.CampoPersonalizable5,

IBP.CampoPersonalizable6,

IBP.CampoPersonalizable7,

IBP.CampoPersonalizable8,

IBP.CampoPersonalizable9,

IBP.CampoPersonalizable10,

IBP.CampoPersonalizable11,

IBP.CampoPersonalizable12,

IBP.CampoPersonalizable13,

IBP.CampoPersonalizable14,

IBP.CampoPersonalizable15,

IBP.CampoPersonalizable16,

IBP.CampoPersonalizable17,

IBP.CampoPersonalizable18,

IBP.CampoAuxiliar1,

IBP.CampoAuxiliar2,

IBP.CampoAuxiliar3,

IBP.CampoAuxiliar4,

IBP.CampoAuxiliar5,

IBP.CampoAuxiliar6,

CASE

WHEN a.PersonContactId is null and IBP.NumIBP is null THEN IBP.CampoPersonalizable1

WHEN a.PersonContactId is null THEN IBP.NumIBP

ELSE a.PersonContactId

END AS ID18,

a.R2_ACC_NUM_ICAR_num_avios_total__c Saldo_Avios_in,

a.R2_ACC_DAT_ICAR_Date_aviso_expiration__c Saldo_Avios_dt

FROM EXT_Nucleo_Comunicaciones_IBP_Previa IBP

INNER JOIN Account_Salesforce a ON a.R1_ACC_TXT_Primary_Frecuent_Flyer_Card__c = ‘IB’ + IBP.NumIBP