Hi @SuganADF ,
The core issue is that SFTP as a source doesn't support query pushdown there's no way to push the date filter to the SFTP side, so the full 14M rows are always read before filtering. This explains both the inconsistency (timeouts on large reads) and the slow performance.
Here are the practical approaches depending on your situation:
Option 1 Files are split by date on SFTP (recommended)
If your SFTP has daily/weekly files in a folder structure like /data/YYYY/MM/DD/, use a wildcard file path in Copy Activity to only read the last 8 days:
/data/2026/04/*/*.csv ← dynamically generated using @adddays(utcnow(),-8)
This avoids loading the full file entirely.
Option 2 Single large file, staging approach
Load the full CSV into a Lakehouse table first via Copy Activity (faster than Dataflow for raw ingestion), then query with a Notebook or Warehouse:
df = spark.read.format("delta").load("Tables/staging_csv")
filtered = df.filter(df["DateColumn"] >= date_8_days_ago)
filtered.write.format("delta").mode("overwrite").save("Tables/output")
This decouples the slow SFTP read from the filter logic and is much more reliable.
Option 3 Optimize Dataflow Gen2
If you must keep Dataflow, enable incremental refresh on the sink and make sure the filter uses a folding-compatible expression (some expressions break query folding and force a full scan). Check the "Query folding indicators" in Power Query editor.
For 14M+ rows from SFTP, Options 1 or 2 will be significantly more stable than filtering inside Dataflow.
I hope you'll find this response helpful.
