Home / Data Platform / Streaming & CDC

Data PlatformHow-toIntermediate

Transformation: from nightly SFTP file drops to event-driven ingestion

Replace cron jobs that poll SFTP folders once a night with ingestion that reacts to each file as it lands. Use managed SFTP into object storage, storage events, Auto Loader or Snowpipe, file validation and a quarantine path.

Deepak Bhardwaj3 min read

Applies toAzure Blob/ADLS SFTP, AWS Transfer Family, Event Grid / S3 events, Databricks Auto Loader, Snowflake Snowpipe, ADF storage event triggers

Before and after

Before After
Partners upload to an SFTP VM Partners upload to managed SFTP backed by object storage
Cron at 02:00 copies and loads everything Each file triggers ingestion within minutes
Late file = missed day Late file is processed whenever it arrives
Bad file breaks the whole run Bad file goes to quarantine; others load

1. Managed SFTP endpoint

Azure — SFTP on a storage account (hierarchical namespace required) with local users per partner:

az storage account update -g rg-ingest -n stpartners --enable-sftp true
az storage account local-user create -g rg-ingest --account-name stpartners -n partnerA \
  --home-directory landing/partnerA --permission-scope permissions=cw service=blob resource-name=landing \
  --has-ssh-key true --ssh-authorized-key key="ssh-rsa AAAA..."

AWS — Transfer Family SFTP server with an S3 backend and per-user home directories.

Give partners write-only access to their own folder.

2. React to new files

Platform Mechanism
Databricks Auto Loader with file notification mode (Event Grid / SQS set up for you)
Snowflake Snowpipe with auto-ingest (Event Grid / S3 event notifications)
ADF / Fabric Storage event trigger on BlobCreated

Databricks Auto Loader example:

(spark.readStream.format("cloudFiles")
   .option("cloudFiles.format", "csv")
   .option("cloudFiles.useNotifications", "true")
   .option("cloudFiles.schemaLocation", "/Volumes/main/ingest/_schemas/partnerA")
   .option("header", "true")
   .load("abfss://landing@stpartners.dfs.core.windows.net/partnerA/")
 .writeStream
   .option("checkpointLocation", "/Volumes/main/ingest/_checkpoints/partnerA")
   .trigger(availableNow=True)          # or processingTime="5 minutes" for continuous
   .toTable("main.bronze.partnerA_orders"))

Snowpipe example:

CREATE PIPE raw.partner_a_pipe AUTO_INGEST = TRUE INTEGRATION = 'NOTIF_INT_PARTNERS' AS
COPY INTO raw.partner_a_orders FROM @partner_stage/partnerA/ FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

3. Validate every file

  • Naming convention and expected extension.
  • Header/schema check; row count vs. a control file or trailer record.
  • Duplicate file detection (hash or filename registry).

Route failures to a quarantine/ path and notify the partner contact automatically, rather than failing the pipeline.

4. Handle "all files for the day" logic

Some downstream processing really needs a complete set. Keep ingestion per file, and run the downstream step when a completeness rule is met (all expected partners arrived, or a cut-off time passed) — not when cron fires.

5. Decommission the SFTP VM

Migrate partners one at a time: create their user, share the new host and key requirements, run both endpoints in parallel for a week, then disable the old account.

Tip

Keep the raw files (immutable, with lifecycle to cool/archive tiers). They're your replay source when a transformation bug is found later.

Checklist

  • Managed SFTP with per-partner users and folders
  • Event-driven ingestion with checkpoints
  • Validation and quarantine with notifications
  • Completeness rules for downstream jobs
  • Partners migrated; old SFTP VM retired

Related fixes