Filtering files using a SQL view or table

you already have everything that you need in the lookup. The simplest approach would be:

1. Lookup gets rows: (prefix, month_end_yyyymmdd)

2. Get-metadata gets all filenames (childItems)

3. Filter activity keeps only files where:
   filename startswith(prefix) AND filename contains(month_end)

In Fabric pipelines the filter condition looks like this:

And(
startswith(item().name, item().prefix),
contains(item().name, item().month_end_yyyymmdd)
)

If your lookup returns an array of objects, wrap the filter under a ForEach on the lookup output, but do NOT do multiple Get Metadata calls:

• 1 Get Metadata

• 1 ForEach over lookup records

• Inside: Filter on the same childItems list

This prevents repeated enumeration of the folder. You generate a single list of files and apply logical filtering per prefix/date combination.