Analytics & BI Tools · Chapter 14 of 18
The preparation layer: Alteryx and what competes with it
Why visual data preparation gets bought, what it is genuinely best at, and the lifecycle every workflow follows if nobody intervenes.
Data preparation tools occupy an awkward and important position: they are bought by analysts, they solve a real problem, and five years later they are running business-critical processes that no engineering team knows exists. Both halves of that sentence are true and most writing on the subject only acknowledges one.
Why it gets bought, which is a real reason
An analyst needs to join three files, clean them, pivot one, and produce a summary — every week. Their options are a spreadsheet that will not scale, learning enough SQL and scheduling to do it properly, or waiting for a data engineering team with a six-week backlog.
A visual preparation tool gives them a fourth option: build it in two hours, see the data at every step, and run it themselves. That is a genuine capability gap being filled, and the person filling it is right to. Engineers who dismiss these tools usually have not sat in the seat where the alternative is a six-week wait.
- The visual canvas is genuinely useful for exploratory transformation, because you can inspect the data after each step. Debugging a long SQL statement means reasoning about what it does; debugging a canvas means looking at the rows.
- Spatial, statistical and fuzzy-matching operations are where these tools have a real technical edge. Fuzzy matching in particular — reconciling customer names across systems with typos and abbreviations — is genuinely hard to write from scratch and is a single node here.
- Desktop file handling. A great deal of real business data arrives as spreadsheets from people who will never put it in a database, and tools built around a warehouse handle that badly.
The lifecycle, and the month that matters
The honest framing to use in a design review
The problem is not that analysts build these. The problem is the ungoverned transition from personal tool to business dependency.
Nobody decides that the month-end close will depend on one person's laptop workflow. It happens by accumulation, one small dependency at a time, and by the time it is obvious the original author has changed team.
So the intervention is not a policy against the tool. It is a trigger: when a workflow feeds something more than one team relies on, or feeds a regulated or financial process, it gets a named owner, a runbook, version control, and a test that fails when an upstream column changes.
The failure mode to avoid is the engineering department's instinct, which is to ban the tool and offer a backlog instead. That does not remove the workflows; it moves them into spreadsheets, where they are less visible and worse.
What competes with it now
The competitive picture changed substantially, and it is worth being precise because the alternatives sit at different layers rather than being straight substitutes.
| Alternative | What it replaces | What it does not |
|---|---|---|
| Warehouse-native transformation (a modelling framework in SQL) | The transformation logic, with version control, tests, lineage and CI included | Desktop file handling, spatial and fuzzy operations, and the visual debugging loop |
| The BI tool's own prep (query editors, dataflows) | Light shaping close to the report | Anything heavy, and anything another tool needs to reuse |
| Managed ELT plus SQL | The ingestion half entirely, which is often most of the pain | The transformation logic itself |
| Cloud-native pipeline tools | Scheduled orchestration at scale | Ad-hoc exploration by a non-engineer |
| Notebooks | Everything, for someone who can write code | Being usable by the analyst who cannot |
| Open-source visual tools | The licence cost, with a similar model | The commercial support and the scheduling infrastructure |
The pattern across that table: the transformation logic is the part that has genuinely moved, toward version-controlled SQL that runs in the warehouse, because that brings testing, lineage, code review and CI — all the things the visual canvas lacks. What has not moved is the analyst-facing exploratory loop and the messy-file problem, and that is where the visual tools remain genuinely better rather than merely entrenched.
The push-down question, which decides performance
The single most important technical question about any preparation tool: does it push work down to the source, or does it pull data out and process it locally?
- Pulling data out means the tool's own engine does the work, on one machine, over data that had to cross the network first. That is fine at a million rows and hopeless at a hundred million, and it is the reason a workflow that ran in four minutes last year now takes two hours.
- Pushing down means the tool compiles your steps into SQL the warehouse executes. Vastly faster at scale, and it constrains what you can express — some operations have no SQL equivalent and force a fall back to local processing, usually without telling you.
- The practical test: take a workflow and watch the source. If the warehouse does the work, you are pushed down. If the tool downloads gigabytes and the warehouse goes idle, you are not — and the fix is usually to reorder the steps so that filters and aggregations happen before whatever forced the fallback.
A rule of thumb worth carrying
If the output feeds a dashboard more than one team uses, the logic belongs in version control. Not because visual tools are bad, but because that output is now infrastructure, and infrastructure needs the things version control brings: a history, a reviewer, a test, and a way to know what broke.
Everything below that line — genuinely personal analysis, exploration, one-off reconciliation — should be left completely alone. Governing it is how you make analysts stop telling you what they are doing.
Check yourself