Exporting SFMC Data to an External SFTP: The 3-Step Pattern

A banking client on our roster wanted nightly tracking exports pushed to their own SFTP so their analyst team could process opens and clicks in their data warehouse.

The first build ran clean. No errors, automation marked complete every night. A week later the client asked why no files had shown up. Investigation took 20 minutes: Data Extract Activity was writing the file to SFMC's own SFTP, but there was no step to push it to the client's SFTP. Silent gap.

Here's the 3-step pattern we use every time.

The canonical sequence

Schedule Starting Source (daily at 23:00)
  -> Step 1: SQL Query Activity      (read _Open data view, write to Export_DE)
  -> Step 2: Data Extract Activity   (export Export_DE to CSV on SFMC SFTP)
  -> Step 3: File Transfer Activity  (move CSV from SFMC SFTP to client's external SFTP)

Three Activities. Each does one thing. Skipping any one breaks the chain silently.

What each Activity actually does

SQL Query Activity

Reads from Data Extensions and Data Views (the system-owned DEs like _Open, _Click, _Sent, _Subscribers), optionally joins/filters/aggregates, and writes the result to a target DE of your choice.

Classic mistake here: exporting directly from a raw source DE. If the client wants "subscribers who opened email today" and the source Subscriber_DE holds all subscribers (millions), Data Extract straight from it produces a multi-million-row CSV instead of a few thousand. SQL Query first, Data Extract second.

/* write today's opens to Export_DE */
SELECT SubscriberKey, EventDate, JobID
FROM _Open
WHERE EventDate >= DATEADD(DAY, -1, GETDATE())
  AND EventDate < GETDATE()

Data Extract Activity

Takes a DE and writes it out as a file (CSV, TSV, or pipe-delimited) onto SFMC's own SFTP. Configuration lets you name the file, pick delimiter, include headers, and optionally zip it.

Data Extract is a one-purpose Activity: DE -> file on SFMC SFTP. That's where the file stops if you don't add the next step.

File Transfer Activity

Moves the file. From SFMC SFTP to an external SFTP, or from one folder to another inside SFMC SFTP. Also handles encryption - if the client wants files delivered encrypted, the File Transfer Activity can PGP-encrypt on the way out.

For an external SFTP destination, you configure the External File Location once in Setup > Data Management > Key Management + File Locations, then reference it in the File Transfer Activity.

Mistakes we've fixed on audit

Missing File Transfer

Described above. Data Extract Activity alone leaves the file on SFMC's SFTP. Many first-time setups assume Data Extract is the end; it isn't if the file needs to leave SFMC.

Skipping SQL Query, extracting raw source DE

Leads to multi-million row CSVs landing on the client's SFTP, usually timing out the upload or overwhelming the downstream ingestion. Always filter in SQL Query first.

Hardcoding credentials inside the File Transfer config

Store the external SFTP credentials in File Locations, not inline on each Activity. When the credential rotates, you update one place instead of hunting every Automation.

Not testing the encryption key rotation

If the client asks for encrypted files and you store their PGP public key in SFMC, test what happens when they rotate the key. Rotation is rare but routine - plan for it.

When the client wants encrypted output

Adds a File Transfer step:

Schedule
  -> SQL Query
  -> Data Extract
  -> File Transfer (PGP-encrypt the file)
  -> File Transfer (push encrypted file to external SFTP)

Two File Transfer Activities in a row: one to encrypt, one to push. You can't do both in one Activity - they're separate operations.

Verifying the pipeline

Before going live, run the full automation manually and confirm:

1. SQL Query wrote the expected row count to Export_DE.
2. Data Extract produced a file on SFMC SFTP with the expected filename and size.
3. File Transfer successfully delivered the file to the external destination.
4. The external team can read, decrypt (if encrypted), and parse the file.

Run this test for two consecutive days before declaring it production-ready. Flaky SFTP credentials often pass the first run and fail on the second.

Takeaway

Export pipelines in SFMC need three Activities, not two. SQL Query prepares the data, Data Extract writes the file, File Transfer moves it. The moment a file is supposed to land on a client's SFTP instead of SFMC's own, that third Activity is mandatory. Spend the extra 5 minutes configuring the External File Location and testing end to end - it pays back the first time the client asks why the files haven't been arriving.

---

Designing an SFMC data export pipeline? Our Salesforce team ships end-to-end Automation Studio setups including External File Locations and PGP encryption on production engagements. Get in touch ->

See our full platform services for the stack we cover.