Unable to select Filtered Data Extension as target in SQL query in Marketing Cloud

Question

I have created 2 filtered data extension from a shared data extension using filter. I want to automate those filters so that the DE's will be auto refreshed. I realized that automating filter activity creates a new DE rather than appending to existing one. I had to go for query automation but while selecting the target DE, I cannot see my filtered DE in the list. Is this standard functionality that query does not appends into filtered DE? If yes, what is alternate way to refresh the existing filtered DE without manually doing it?

Answer 1

Unfortunately you cannot target a filtered Data Extension with a query. These are able to be manipulated by your filter only. If you cannot utilize the filter to get what you need, you will need to create a new data extension to target with the query.

If your filter is appropriate/sufficient and you just want to automate it, you can utilize a filter activity inside of SFMC Automation Studio (as @RachidMamai suggested) to schedule this. This would require you to:

1. Save your filter inside the 'filter' section of Email Studio
2. Create a Filter Activity inside of Automation Studio for use in your automation, assigning the filter you created in step 1.
3. Create a target DE for your filter to land in (this is done automatically in the Filter Activity creation [not needed to be done manually], but you cannot use an already existing DE)

Another alternative is to create a script activity to utilize an undocumented REST API endpoint to refresh an existing filtered Data Extension.

POST /email/v1/filteredCustomObjects/{{filteredDEObjectID}}/refresh 
Host: {{yourSubDomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{AuthToken}}
Content-Type: application/json

Although this is a POST method, no payload is necessary to refresh this. You will just need to place the Object ID of your filtered Data Extension into the URL and then send it.

You can get the ObjectID through a few ways:

1. SOAP API call to get the DE info based on Name/Customerkey
2. Using The Salesforce Marketing Cloud - Developer tools extension for Chrome
3. Using the 'Network' tab on your developer tools. Then find the API call that was made when you opened the Object in the UI and copy the ObjectID from there.