Data Analytics & BI · Tools
A tool for every step of the pipeline.
From pulling data out of a system to the dashboard and the history behind it: the 13 tools I work with, the wider landscape around them, and how to choose.
One dataset, the whole stack
Follow one file through every tool.
The real Online Retail export, from the moment it is pulled out of the shop's system to the dashboard and the history behind it. Scroll, or click the steps.
01 · Collect
Python scripts
The map
28 tools, seven steps, one pipeline.
Solid cards are the 13 tools I work with; outlined ones are the wider landscape, shown so the choices make sense. Click any tool for what it is, what it looks like, and when it fits.
01 · Collect
Pull data out of apps, files, APIs and event streams.
02 · Store
Keep it in a database or warehouse that can be queried.
03 · Transform
Clean, join and model it into trusted tables.
04 · Orchestrate
Run every step in order, on schedule, with alerts.
05 · Analyse
Explore, test ideas, compute the numbers.
06 · Visualise
Dashboards and reports for the people who decide.
07 · Collaborate
Version, review and reproduce the work.
Swipe the map sideways to see every step.
A drawn illustration of working in it, not a screenshot. Numbers shown come from the Online Retail data.
Transform · my stack
dbt
Turns SQL SELECT statements into tested, documented, versioned tables.
Strong at
Models built in dependency order, with tests, docs and lineage.
Watch out
Batch only; another tool to learn and run.
Rule-of-thumb ratings (1–5), my judgement, not benchmarks.
I use it for
Building the star schemas and summary tables that dashboards read.
Same task, every tool
One question, eight ways to ask it.
“Orders per country per month (Sep–Nov 2011)”, answered in 8 tools. Some are typed, some are clicked; the answer is the same. Pick a tool to watch it run.
Result · orders
✓ same answer| country | Sep 2011 | Oct 2011 | Nov 2011 |
|---|---|---|---|
| United Kingdom | 1,631 | 1,789 | 2,481 |
| Germany | 41 | 56 | 64 |
| France | 45 | 33 | 64 |
Real result from the cleaned Online Retail data (distinct invoices). Colour on a log scale, so the smaller countries still show.
BI head-to-head
Power BI, Tableau, Looker Studio, Metabase, Superset.
The dashboard tools compared on what actually decides the choice. Switch tools on and off; hover to isolate one. Ratings are my rule-of-thumb judgement (1–5), not benchmarks.
| BI | Tb | ||||
|---|---|---|---|---|---|
| Free to start | best | best | best | ||
| Ease | best | best | |||
| Data modelling | best | ||||
| Visual freedom | best | ||||
| Sharing | best | ||||
| Governance | best |
Comparison matrix
Every tool, side by side.
Click a column to rank the tools by it. Darker means stronger. Same caveat: rule-of-thumb judgement, so read it as a starting point, not a score.
| Python scriptsCollect | 3 | 3 | 3 | 3 | 2 |
| AirbyteCollect | 4 | 4 | 4 | 3 | 4 |
| 5TFivetranCollect | 5 | 5 | 4 | 3 | 5 |
| Apache KafkaCollect | 5 | 2 | 3 | 5 | 4 |
| PostgreSQLStore | 3 | 3 | 4 | 3 | 4 |
| BigQueryStore | 5 | 4 | 4 | 4 | 5 |
| DuckDBStore | 4 | 5 | 2 | 1 | 2 |
| SnowflakeStore | 5 | 4 | 5 | 3 | 5 |
| MySQLStore | 3 | 4 | 3 | 3 | 3 |
| MongoDBStore | 4 | 4 | 3 | 4 | 3 |
| SQLSQLTransform | 5 | 4 | 4 | 3 | 4 |
| dbtdbtTransform | 5 | 3 | 5 | 2 | 5 |
| Apache SparkTransform | 5 | 2 | 3 | 4 | 3 |
| Apache AirflowOrchestrate | 5 | 2 | 4 | 2 | 4 |
| PrefectOrchestrate | 4 | 4 | 4 | 2 | 3 |
| Python · pandasAnalyse | 3 | 3 | 3 | 2 | 2 |
| JupyterAnalyse | 3 | 4 | 3 | 1 | 2 |
| XLExcelAnalyse | 1 | 5 | 3 | 1 | 2 |
| Google SheetsAnalyse | 1 | 5 | 5 | 2 | 2 |
| RAnalyse | 3 | 3 | 3 | 1 | 2 |
| BIPower BIVisualise | 4 | 3 | 4 | 3 | 5 |
| TbTableauVisualise | 4 | 3 | 4 | 3 | 4 |
| Looker StudioVisualise | 3 | 5 | 4 | 2 | 2 |
| MetabaseVisualise | 3 | 5 | 4 | 2 | 3 |
| Apache SupersetVisualise | 4 | 3 | 4 | 3 | 3 |
| StreamlitVisualise | 3 | 4 | 3 | 3 | 2 |
| GrafanaVisualise | 4 | 3 | 4 | 5 | 3 |
| Git & GitHubCollaborate | 5 | 3 | 5 | 1 | 5 |
Which tool, when
The best tool depends on the job. Build the stack for yours.
Pick a scenario or answer seven questions. You get a tool for every step with the reason, an alternative, the cost level and the skills to learn, plus what would change if your answers did.
Scenario: Startup with an app
1How much data?
2Where does it come from?
3How fresh must it be?
4Who reads the result?
5Budget?
6Team skills?
7Company ecosystem?
Suggested stack
Cost level
Free to start, pay as it grows
Skills to learn
From my stack
4 of 7 tools
Collect · Airbyte
Open-source connectors sync app databases and SaaS tools without writing each one.
or Python scripts
Store · BigQuery
Serverless, shared, with a free tier, and native to Google tools.
or Snowflake
Transform · dbt
Recurring work deserves tested, documented SQL models, so the numbers don't drift.
or SQL SQL
Orchestrate · Apache Airflow
Hourly or live data needs reliable scheduling, retries and alerts.
or Prefect
Analyse · SQL
Analyse in SQL, right where the data lives.
or Google Sheets
Visualise · Looker Studio
Free, easy to share, and native to Google data.
or Metabase
Collaborate · Git & GitHub
Every query, model and notebook in version control, reviewed and reversible.
What would change if…
Click one to try it. These are rules of thumb written down from experience, not a ranking.