HubSpot + SFTP and CSV vendor feeds integration

Plenty of the systems a business depends on have no API at all. You find them across industries, among legacy ERPs, payroll and HR platforms, carriers and 3PLs, point of sale back offices, and custodians and fund platforms. They publish scheduled reports instead: a handful of CSV files dropped on an SFTP server overnight, named by report and date. The data is complete and the cadence is reliable. There is no endpoint to call and no event to subscribe to.

Sales and service teams working in HubSpot feel this as a missing half of the relationship. HubSpot knows the prospect, the meetings and the paperwork. It does not know what the customer bought, holds or owes, what changed since the last file, or which order an email thread relates to. So whoever owns the account opens the source system, or asks operations for an export, and HubSpot becomes the place where notes live rather than where the relationship lives. No workflow can fire on data HubSpot never sees.

Loading the feed changes what HubSpot is for. Once orders, invoices, shipments or holdings sit as records against the contact, you can list the accounts with an overdue invoice, route a review to the right owner, or read engagement activity beside the money it relates to. The work is unglamorous and almost entirely about discipline. The same files, parsed the same way every night, with something watching that speaks up when one does not arrive.

Files rather than objects are the unit of delivery, so the mapping starts one level up. Each file gets a shape: which columns identify the record, which columns carry values, and what the file means when a row disappears from it. A missing row in a snapshot file, such as balances or stock levels, usually means the record closed. A missing row in a contact file usually means the vendor sent a delta. Getting that distinction wrong deletes real records.

The object model underneath is conventional. Customers become contacts, the parent account becomes a company, product detail fills the product library, and activity arrives as engagements. Anything that is an order, a transaction or a snapshot becomes a custom object associated to the contact. HubSpot’s custom object API accepts a batch upsert against a unique identifier property, which is exactly what a nightly file needs. The vendor’s account number goes in that property, and the same file loaded twice produces the same records instead of a second set. Nothing keys on a name, because two files from one vendor will spell the same customer two ways.

The shape is a managed transfer service in front of object storage, with a state machine doing the work. AWS Transfer Family gives each vendor a real SFTP endpoint, reached with the client and key they already use, writing straight into a storage prefix. A new object raises an event, and that event starts a Step Functions execution.

Three functions run in order inside it. An extractor reads the file, parses it, and moves it into a processing prefix so a retry cannot pick it up twice. A validator checks the headers, remaps the vendor’s field names onto HubSpot property names, and applies the rules that decide whether a row is usable. A loader batches the validated rows into HubSpot keyed on the source identifier, then makes a separate pass for associations. On success the file moves to a processed prefix, and on failure to a failed one, so the prefix a file sits in is the run’s state. Step Functions carries retry and catch around each step, and a failed execution raises an alert rather than ending quietly.

What decides here is who owns the file’s shape, and how much judgement the load needs. If the file is small, stable and lands in a single standard object, a file-aware integration platform can watch the folder and map the columns with no code. That covers more cases than engineers like to admit, and it is cheaper when it fits.

That platform stops fitting once the load needs judgement. Several files that must land in a fixed order, custom objects with associations back to a contact, rows that are valid alone and wrong together, or a vendor that renames a header without telling anyone: each turns a mapping tool into a maintenance job. Where the data is money, the pipeline you own usually wins, because you need the run history as much as the load.

Most incidents trace back to arrival, column drift, re-delivery or the load itself.

1. Scope. We collect one real sample of every file, agree what each one means when a row is absent, and confirm which vendor field is the stable identifier.
2. Architect. We fix the object model before any code: which rows are contacts, which become custom objects, and how associations run back to the customer record.
3. Build. The extractor, validator and loader, with the field mapping held outside the code so a vendor’s rename is a configuration change. A local harness runs the whole pipeline against fixtures with nothing sent anywhere.
4. Validate. Loads run against a HubSpot sandbox first, behind a fail-closed portal allowlist, so a misconfigured token cannot write to the wrong portal. Production cutover is a token swap.
5. Operate. Arrival monitoring per feed, a failed prefix that is also the replay queue, and a reconciliation count that compares rows delivered against records written.