0

Is it better to create a query with filtering within the query to populate a data extension - or - do you create a query and then create a filtered extension based on your parameters?

My use case is this:

I have a population of 3.8MM. I need to create a combined data extension based on that large [Users] data extension and a data extension called [Stores] which represents store owners (that are also users). I'm using an Inner Join on the Contact_Key(subscriber key).

From there I need to filter that list of stores based on whether they are open, active, and within a certain range for a close date. Currently, running an automation for a filter activity after a query activity. Is this best practice? Or would it be more scalable to just run queries with filtering logic written within the query?

1

If the only scenario you have, is the one described above, i would personally:

  • Create Data Relationship (seems you have this?)
  • Create filtered DE's

If it is because you need to combine data from both tables into the final data extension, i would skip the filter and just handle it in the query, in order to minimise the amount of steps needed to maintain / achieve the end result.

If you are experiencing performance issues, i would assume that queries are the faster option, and you might be able to split out data in "intermediate" or "staging" DE so that you can go from maybe 3.8 million to 2.0 million before performing final operations.

1

Personally I prefer the query-only approach. This allows the end user to then apply their own filter on that final Data Extension should they need to segment further. If you are leaving them with a filtered Data Extension they are a bit stuck with that. You could argue it's a bit more work to initially to set up, but if you are comfortable with queries it shouldn't be a show stopper.

From a system performance or best practice perspective others may have different opinions.

1

I'd do it all with Query Activities, which are way more flexible when joining related data extensions.

Queries also require less configuration steps if you want to add them to an Automation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.