The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need for expensive operations like DISTINCT and limiting result sets by using INNER, rather than OUTER joins.

The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need for expensive operations like DISTINCT and limiting result sets by using INNER, rather than OUTER joins.

Without having your data to hand or knowing too much about the business rules you're looking to apply, it's a little hard to write hypothetical SQL, here, but here's an example of breaking down your automation.

1. Overwrite ContactsWithProducts (PK = ContactId,ProductCode)

SELECT 
    a.ContactId AS ContactId,
    p2.Id AS ProductCode    
FROM
    Product2_Salesforce p2 INNER JOIN 
    Asset_Salesforce a
        ON  p2.Id = a.Product2Id

2. Overwrite DataLayerContacts (No PK)

SELECT 
    c.Id AS ContactId,
    cdl.AccountId 
FROM
    Contact_Salesforce c INNER JOIN 
    [Customer Data Layer_v2] cdl
        ON  c.AccountId = cdl.AccountId

3. Overwrite MyResultsTable (No PK)

SELECT 
    dlc.ContactId,
    STUFF(
        (
        SELECT  ',' + cwp.ProductCode
        FROM    ContactsWithProducts cwp
        WHERE   cwp.ContactId = dlc.ContactId
        FOR XML PATH('')
        )
    ,1,1,'') AS ProductCodes
FROM
    DataLayerContacts dlc