A few months back I sat on a video call, screen-sharing, while a client’s QuickBooks account quietly filled up with duplicate invoices. 3,412 of them, created in about ninety minutes. Nobody clicked anything wrong. A webhook timed out, the sync tool retried it, and because nothing in the pipeline checked whether those invoices already existed, the thing just kept politely making fresh copies. Their bookkeeper spotted it before we did. Great Tuesday.
That mess is why I’m writing this. Most small business ETL pipeline tutorials assume you have a data team, a warehouse budget, or at least one person who enjoys writing SQL. This is the version for everyone else: the owner-operators and one-person IT departments who need app A talking to app B without duplicates, crashes, or API keys leaking somewhere they shouldn’t.
Quick context on where I’m coming from: I build automations for small and mid-sized businesses for a living, and I’ve written before about why pipelines break when nobody’s watching. Think of this post as the build-it-right companion to that one. Five steps, a bit of scar tissue, and one whiteboard drawing.
Step 1: ETL or ELT? Decide with your budget, not a conference deck
Quick definitions so we’re on the same page. ETL means you transform data before it lands in the destination: clean it, reshape it, deduplicate it, then load. ELT means you dump raw data into a warehouse first and do the transforming afterwards, usually with a tool like dbt.
My honest take, and I’ll probably get argued with over this: most small businesses need neither, at least not in the textbook sense. Moving invoices from your shop into your accounting tool, or form submissions into a CRM, is app-to-app sync with a little transformation in the middle. Make, n8n, and Zapier all handle it, and if you’re unsure which one fits your setup, I put together a detailed comparison of Zapier, Make, and n8n that’s less polite than the vendor pages.
So when does real ELT earn its keep? Mostly when you’re pulling from three or more sources to build reports, or when your transformation logic changes every month. If that’s you, a tiny BigQuery setup costs pennies per month at small volumes. The real cost is the learning curve: dbt will eat a weekend even if you already write SQL, and several if you don’t.
For a lean budget, my default is simple. Start with ETL-style syncs on an automation platform, and graduate to a warehouse only when your reports start hurting. You’ll know when that is.
Step 2: Map your data flow before you open any tool
Draw it. Boxes and arrows, on paper or a whiteboard, whatever’s closest. This step feels insultingly basic, and it saves more pain than any tool choice ever will, because the duplicate nightmare almost always lives in the flow itself.
It took us ten minutes to find the root cause of the 3,412-invoice disaster once we drew the picture. The shop’s webhook could fire twice per order, once on “created” and once on “paid”, and both triggers fed the same “create invoice” step in Make. Two doors, one record, no check. Nothing was misconfigured. The flow was just wrong, and nobody had ever looked at it end to end.
Three rules for the drawing
One source of truth per field. Decide where each piece of data is officially born. If the CRM and the billing tool both believe they own “company name”, you’ve built a loop that’s just waiting for a busy week to go off.
One-way sync by default. Two-way sync is where duplicates breed. App A pushes to app B, B never pushes back, and if B really must speak, it speaks about different fields than A does.
Check before you write. Look up the invoice number, contact email, or order ID before inserting anything. Some APIs offer upsert endpoints that handle this for you. Where they don’t, you build the check yourself, and if you want the full mechanics, I wrote a step-by-step walkthrough of using idempotency keys to prevent duplicate writes.
One more thing worth deciding early: your dedupe rules. Duplicates will slip through eventually, and when one does, you want “which record wins?” answered in a doc somewhere, not improvised at 9pm in a panic.
Step 3: Version control, or how I broke a live pipeline in forty seconds
Two winters ago I broke a client’s pipeline in under a minute. The setup ran on n8n, pushing deal updates into Pipedrive. I wanted to tweak one filter, so I opened the live workflow, moved a couple of nodes, and hit Execute to “just test the new branch”. Turns out n8n executes the whole workflow, on live data, which I kind of knew in theory and apparently not in practice. 214 deals got statuses they should never have had. Cleanup took two days, and a customer emailed support before my dashboard flagged anything. That detail still stings.
What I do now is unglamorous, and it works. Every workflow gets a duplicate named “dev” before I change anything. Every change gets exported as a dated JSON file into a backups folder, so there’s always a Monday version and a Thursday version. If something breaks, I roll back in about a minute instead of rebuilding from memory at midnight.
Real developers use Git for this, and I mostly don’t. A data engineer friend looked at my folder of timestamped exports and called it “version control with extra steps”. He’s probably right. I’ve started using proper repos for the bigger builds, but for small client projects the folder system has saved me more often than it’s failed me, and I’m not in any hurry to fix something that isn’t obviously broken.
One rule I’d tattoo on my own hand: never test on live data. Staging copy, sandbox account, ten fake records. Every “quick test” I’ve regretted happened on production.
Step 4: Lock the doors, because your API keys are the crown jewels
Security sounds dramatic. In practice it’s four boring habits, and together they prevent pretty much every small business data mess I’ve seen up close.
Scope every key. Each credential should do exactly what the pipeline needs and nothing more. Read-only where you can get away with it, and never the admin key just because it was first in the dropdown. I once watched a buggy step nearly wipe a mailbox because the pipeline held full admin rights for a task that only ever needed read access.
Store keys properly. Platform credential vaults or environment variables. Never hardcoded inside an exported workflow file, because those exports end up in Slack channels and email attachments far more often than anyone admits.
Mask what you log. Error logs don’t need customer email addresses in them. Log the record ID, not the person.
Rotate and delete. Someone leaves, a tool gets retired, a trial lapses: kill the key that day. I keep a quarterly calendar reminder to walk through every connected app and remove what’s stale.
One modern hazard worth naming: don’t paste customer data into a chatbot to debug a transformation. It’s tempting at 10pm. Don’t.
I’ve come to treat all of this as part of basic workflow reliability rather than a separate security chore, mostly because broken pipelines and leaked keys share the same root cause: shortcuts taken under time pressure.
Step 5: Test with ten records, then earn the right to scale
Small first, always. Sandbox accounts, fake records with “TEST-” prefixes, a handful of real ones from last week. Run the pipeline in shadow mode for a while, meaning it writes its output to a Google Sheet instead of the live CRM, then compare the two. A week of shadow mode catches more than a month of code review, in my experience.
My favorite cautionary tale here is my own. I once kicked off a backfill of 22,000 historical orders at 11pm, confident because the first hundred had worked fine. The API rate limit gave out around row 9,400. Half-finished imports are worse than no imports, and that one cost me a weekend.
Before scaling, poke at the ugly questions. Does the trigger fire exactly once per record? What happens when a retry lands? What if the source returns an empty result, or one malformed record sits in the middle of the batch? If you can’t answer those, the pipeline isn’t ready.
Then scale in steps. Ten records, then a hundred, then the full backfill, scheduled for a quiet hour, with a hand on the off switch.
Wrapping up
That’s the whole approach, and I know it isn’t flashy. A drawn flow, a check before every write, dated backups, narrowly scoped keys, tiny tests. Boring on purpose. The clients I never get emergency calls about are the ones who did all five steps, and the ones who skipped the drawing are the reason this list is taped above my desk.
Anyway, if you’re stuck on where to start, start with the drawing. Everything gets easier once the flow exists on paper and you can see the two doors before they let the same record in twice.
Go draw the boxes. That’s it from me.
Damian
