Tool & Technology Reference: Every Box on the Canvas

This is a reference chapter. You do not read it once from top to bottom. You open it when you must pick a tool.

The pipeline builder has a canvas. Each box on it is one kind of tool: ingestion, storage, processing, orchestration or serving.

Open source means the code is public and free to use. Vendor means a company sells the product.

What you will find

1. Five questions that shorten a list of tools.
2. One section for each box on the canvas, with real tools from AWS, Google Cloud, Azure and open source.
3. Six shapes a bill can take, with made-up numbers.
4. Five example stacks. A stack is the set of tools one team uses together.

You already know the ideas behind these boxes. Data Sources & Ingestion Patterns (chapter 5) covered how data comes in. Storage Layers & File Formats (chapter 23) covered where it rests.

Orchestration & Scheduling (chapter 10) covered what runs the steps. Applied Pipeline Design: How To Architect Any Pipeline (chapter 28) taught how to design one. This chapter only names the tools.

Do not compare every tool with every other tool. First answer five questions about your team and your need. Each answer removes some tools from the list.

The table shows the five questions, what each one removes, and an answer for the shop. The five-minute need comes from chapter 28. The other answers are made up:

The answers use tools such as a warehouse and dbt. The next sections explain each tool.

A vendor-run tool is a managed tool. A tool you run on your own machines is self-hosted.

Managed has a bigger invoice. Self-hosted takes more of your engineers’ time, and time costs money too.

Try to add at most one new tool in each project. If two new tools fail together, you cannot tell which one broke.

Ingestion tools get data out of its source and into your system. They read databases, apps and event streams. Databases such as PostgreSQL, MySQL, Amazon RDS, Google Cloud SQL, Spanner and Azure SQL Database are common sources.

A cluster is a group of machines that work together. To replay is to read old events again.

This table lists the main jobs and real tools for each. One row can list several tools, because many tools do the same job:

CDC means reading the changes a database writes in its own log. Change Data Capture (CDC), chapter 6, taught it.

The hardest choice here is a queue or a log. A queue gives each message to one reader, then forgets it. A log keeps events, so many readers can read them, and replay them.

Take three click events from the shop website. A dashboard job and a fraud job both need all three. A third job starts a month later and needs the old ones too:

The two jobs share one queue, so each message goes to only one of them. To give each job every message, send copies through SNS to one queue for each job. Old events still cannot be replayed.

A log keeps events only for a set time. Kinesis Data Streams keeps them 24 hours by default, and you can raise that. Kafka keeps them for the time you configure.

Storage is where data rests after ingestion. Chapter 23 explained the layers. Here are real tools for each layer.

Object storage holds files cheaply. A table format adds table rules on top of the files. Apache Iceberg, Delta Lake and Apache Hudi are table formats.

A query engine reads the files and runs SQL on them. A warehouse bundles storage and a query engine in one product.

A lakehouse is object storage plus a table format plus a query engine.

This table lists the jobs and tools:

Here is the shop’s orders table in one of these layers. The same query works on Athena, Trino, BigQuery or Snowflake, because it is plain SQL:

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC, status;

Warehouses bill in different ways. Auto-suspend stops a warehouse after a quiet time. Serverless means the vendor manages the machines, and you pay for use.

A provisioned cluster is a group of machines that you size and start yourself.

Capacity is a fixed amount of compute that you rent by the hour. A dedicated pool is a set of machines kept ready for your queries.

This table shows what each one charges for:

Processing tools change data: clean it, join it and add it up. Transformation Fundamentals (chapter 8) taught the jobs. These are the tools.

Ask first: would SQL in the warehouse do the work? If yes, use SQL. dbt is a tool that runs your SQL files inside the warehouse, in the right order.

The shop wants revenue for each customer. It is one SQL file, and no cluster:

SELECT c.name,
       SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.name
ORDER BY revenue DESC;

A tool such as Spark is for work that SQL cannot do, or data too big for one machine. Spark and Flink spread the work over many machines.

This table lists each kind of work and real tools. State means what a stream engine remembers between events:

Stream Processing (chapter 14) taught how a stream engine keeps state. Scaling & Performance (chapter 25) taught how to tune a big job.

An orchestrator runs the steps in order, retries failures and shows what happened. Chapter 10 taught the ideas.

A backfill re-runs a job for past days. An environment is one running copy of Airflow, with its own machines. This table lists real tools:

With only a few jobs that do not depend on each other, cron can be enough. You need an orchestrator when jobs depend on each other and you need retries, backfills and a view of what failed.

Other tools guard the data. Data Quality & Validation (chapter 20) and Schema Evolution & Data Contracts (chapter 22) taught why. This table lists the tools:

Serving is how people and programs use the data. It also covers the tools that watch the platform. Data Observability & Monitoring (chapter 21) taught the ideas.

A BI tool turns tables into charts and dashboards. BI means business intelligence. This table shows how each tool charges today:

A semantic layer is one place where a metric is defined once. It stops three dashboards from showing three different revenue numbers. Looker and Power BI have one built in.

Per-user pricing grows with the number of people, not with the data. A hundred people who look once a month can cost more than the warehouse. Check the price per user for occasional viewers.

This table lists the other serving and monitoring jobs:

Most tools bill in one or more of six shapes. Two tools with different shapes cannot be compared by unit price. Work out what your own workload would cost under each shape.

The table lists the six shapes. Each one has a behavior that makes the bill grow:

These prices are made up to show the shape of each bill. Real prices differ by tool and change often. Here 1 TB (terabyte) is 1,000 GB (gigabytes):

Each row shows the same work done two ways. The costly way is not wrong. It does the thing the shape punishes.

Check your tool first. F64 is a large Power BI capacity size. Tableau, and Power BI below F64, need a paid seat for each person who gets the email.

Four more things change a bill. Check them for every tool:

• Stored data: a fee for each GB you keep each month. Object storage such as Amazon S3 bills mostly this way.
• Floor cost: what the tool bills when nothing runs. A managed Airflow environment and a provisioned cluster have one.
• Commitment discount: you promise a fixed amount of use for a lower price. It helps only when your load is steady.
• Egress: a fee when data leaves a cloud or moves between regions (cloud data centres in different places). It grows large when you copy much data.

A stack is easier to judge than a single tool. These five go from tiny to large, with no prices, because prices change.

The table lists the five stacks. Each one is one way to move the shop’s orders to a report:

These are not steps everyone must climb. Many small teams can stay on stack 1 or 2.

In stack 4, streaming is added next to batch and does not replace it. Lambda, Kappa & Streaming-First (chapter 16) shows designs where streaming does replace batch. There, Lambda is a design, not AWS Lambda.

Move up only when a real need forces it. These needs are examples:

• The data grows past what the warehouse can process at a fair cost.
• A reader needs data in seconds, not minutes. Check that number first.
• A rule says where data may live or who may read it. Security, Governance & Compliance (chapter 27) covers this.

Every piece you add is one more thing someone must keep healthy. That cost does not appear on the invoice, and it lasts as long as the platform.

• Answer five questions about your team and need before you name a tool.
• Each box has real tools on AWS, Google Cloud, Azure and open source. Many do the same job.
• Choose a log, not a queue, when a second reader is likely.
• Try SQL in the warehouse before you reach for Spark.
• Name the billing shape of a tool before you compare two tools.
• Judge a stack by how many pieces someone must run. Start small.

Here are five scenarios. Each box could be one of several tools. Build each design on the canvas with the chapter closed, and the review names the requirement you missed.