Vendor SFTP feeds into HubSpot custom objects on AWS

Both providers deliver scheduled CSV reports over SFTP. That is the interface the engagement had to build on, so the shape of the work was settled before it started.

The files are wide and they are not tidy. The three largest reports carry 46, 58 and 90 columns. Dates arrive in three formats. Currency arrives with a symbol and thousands separators. A single cell can hold several email addresses separated by a semicolon, a comma or a slash. Enumerated fields gain a new option whenever the provider adds one, with no notice. Some rows on the advisor report have no email address at all, which makes them unidentifiable rather than invalid.

The harder constraint was regulatory. This is financial services, and the client’s compliance lead set the real specification in a review: how long does our data sit on your side, what is the encryption standard on the transfer, who is allowed to upload, and how does an auditor see that the environment is being maintained. A pipeline that only moved data correctly would still have failed that review.

The two providers’ data also had to stay separated end to end, at rest and in flight, with no step that merges or compares across them.

Managed SFTP gives each provider its own upload-only account, with no ability to delete or modify anything already there. Each maps to its own inbound folder, so the two feeds never share a path.

A file landing raises an event that starts a workflow of four ordered steps. Extract parses the CSV, moves it into a processing folder so the same file cannot run twice, and writes the rows out as JSON. Validate does the real work, Load writes to HubSpot, and a fourth step moves the file to a processed folder.

Validation lives in configuration rather than in code. Each file type has a mapping of source field to HubSpot property, a type, and for enumerated fields the allowed values. The validator strips the byte order mark from the first header, matches header names case insensitively, remaps each field, then runs the validator for its type: emails split on any of the three separators, with the first going to the email property and the rest to an overflow field; currency strips the symbol and the commas; dates accept all three formats.

Two decisions there matter more than the rest. An enumerated value that is not in the allowed list becomes “Other”, and the original value is kept in a companion property, so a new option from a provider never stops a file. And a row that cannot be identified is skipped and counted rather than failed, while a row that is identifiable but wrong fails the batch, because a partly loaded file is worse than a rejected one.

Load runs in batches of 100. Contacts upsert on email with the advisor registration number as the fallback key. Four custom objects hold what a contact cannot: fund details, engagement activities, holdings and transactions, each associated back to the contact that owns it.

Go live was a sandbox to production cutover, ordered so that a mistake stayed recoverable. We took a full snapshot of the production account, then ran an automated schema comparison against it, which found five properties missing from one custom object. Those were created before any write.

Then the 137 files already processed in the sandbox were re-driven through the production pipeline in batches of 10, with record counts read back from the CRM after each batch. All 137 completed with no failures, producing 1,353 fund detail records, 178 engagement activities and 386 contacts. Contacts already in the portal that matched nothing incoming were untouched. A run takes about 30 seconds for the small activity report and 2 to 3 minutes for the larger detail report.

That go live covered the first provider’s two reports. The second provider’s three reports are built and tested, and wait on the provider before they run in production, so none of the counts above include them.

The compliance answers are now in the delivered documentation rather than folklore: a rolling one month retention, the named cipher suites, upload only accounts scoped to one folder each, and a monthly report covering patches, security status and risks found.

The pipeline is AWS serverless: managed SFTP, object storage with a folder tree per provider, an event rule, a state machine and three functions in Node.js and TypeScript.

The state machine has 21 states and only four do the work. The other 17 are error handling and endings: three build an error context, three move the file to failed, three choose a destination by provider, six publish the alert, and two are terminal. Retries are per step: one each on extract and validate, two with backoff on the load because a rate limit is worth waiting out, none on the move.

Nothing needs patching at the operating system level, which answered most of the maintenance question. The debt that exists is written down rather than buried: the runtime and the cloud SDK are both past end of life, each with a written upgrade path. There is no infrastructure as code, because the stack is built once and left running.

Five report files arrive by SFTP into per-provider inbound folders. A file landing raises an event, the workflow extracts, validates and loads it, and the file moves to a processed folder. The only branch is the failure path: any step failing after its retries moves the file to a failed folder and sends an alert routed by provider, and nothing retries on its own after that. Recovery is re-uploading a corrected file, which starts a clean run.

Direction is one way for every object. HubSpot is never read back into a vendor file, and no step compares one provider’s rows against the other’s, in storage or in memory. A row that cannot be keyed at all is counted and skipped rather than failed, so a file’s row count and its load count rarely match, and that difference is the number worth watching.

The mapping runs to 235 rows across the five files. The excerpt below is 12 of them, chosen to show a contact field, a custom object field, a required key and an allowed value list.