14. ETL pipelines¶
Every data platform has a language for the wiring. Data Factory wires activities in JSON, PySpark in a DataFrame API, ksqlDB in SQL over topics, Kafka Streams in Java, dbt in SQL models with a dependency graph around them. Each describes the same four things: where data comes from, what is done to it, where it goes, and when. They differ in where a mistake is found: in a validator before deployment, in a cluster at run time, or in a dashboard the next morning.
HQL's claim is that the wiring is a program like any other in this book. A
pipeline is one .hql file. What it reads and writes are endpoint ids that
configuration binds to systems. Its types are checked before anything
connects. And the same text runs over a subject that never ends and over the
archive of what the subject once carried.
Part I was about the steps: what each one takes, what it gives, and the type that joins them. This part is about the pipeline the steps form, and the systems a pipeline connects. This chapter is the frame: what a pipeline is, when it is checked, what a step may and may not do, and what stays outside the file. The five after it follow one shop's orders through every kind of system a pipeline reaches, and chapters 19 and 20 are the shop's two sides, the transactions as they happen and the history they leave.
A pipeline language¶
An HQL program is values flowing through typed steps. | is the operator
that forms a step: the value on its left becomes the input of the
operation on its right, and the result is the input of the next. A step
invokes exactly one operation, filter, sort, map, or one
presentation, json, markdown:
[3, 1, 2] | filter(n => n > 1) | sort | map(n => n * 10)
[20, 30]
A dot selects a field or calls a function on a value. s.trim() is a
receiver call, and b.title a field read. Inside a pipeline step, these
expressions compute the values that its operation uses:
["Dune ", " Emma"] | map(s => s.trim().upper())
["DUNE", "EMMA"]
The pipes describe the logical steps and their data dependencies. The dots express computations within those steps. A planner uses that checked program to choose physical execution stages; several steps may share a stage. The companion Logical steps and physical plans follows this distinction through local, parallel and SQL execution.
Three phases¶
A program is parsed, then checked, then executed, and a diagnostic is headed
by the phase that wrote it: parse failure and check error before anything
runs, execute failure after (chapter 12). Checking resolves every
name, import and module, infers and checks every type, and verifies every
trait implementation; a program the checker rejects has no meaning, and no
step of it executes. hql check runs the first two phases alone and prints
the type the third would produce:
[3, 1, 2] | filter(n => n > 1) | sort | map(n => n * 10)
List<Int>
Execution is a strategy a backend chooses. localhost, the one backend
the current release has, evaluates the checked program in process. Finite
collections pass through each step in turn; streams produce values on
demand. A future backend that compiles a program or runs it over many
machines receives the same checked program. For the work it supports, it
must compute the value localhost computes, at the same type, with the
same failure where localhost fails. Unsupported work is a planning
refusal before execution. Backend selection belongs to configuration,
and hql config reports it.
Logical steps and effects¶
A step is a logical unit of computation, established before planning. Its operation defines the transformation from input to result. Physical planning chooses how to perform that transformation, subject to its data dependencies, shape and effects.
An effectful operation interacts with state outside the evaluation:
source reads, target and sink write, and a logger's info emits a
record. Planning preserves the order of effects that the program orders and
the scope of each transaction. Repeating an effect requires a delivery or
transaction guarantee that permits the repeat. A backend that cannot honor
the program's contract refuses it before execution.
A pure step may be rewritten as its shape and dependencies permit, while
preserving results, sequence order and failures. A step that processes each
value alone, filter, map, may run over separate parts of the input; a
step such as sort, group, sum or size needs coordination to produce
its complete result. The reading list of chapter 21 has three steps:
filter tests each card, sort orders the retained cards, and map turns
each card into its title. MapReduce is one possible execution strategy
for suitable steps: work on parts, then combine the results. The planner
chooses traversals and intermediate storage while preserving the pipeline's
result. collect(n) and emit change
the shape of what flows, batches of values or their members, and tee
branches a pipeline without ending it; each of them is a step like any other,
and the type says what the next step may ask.
The command line¶
The smallest pipeline is a shell pipe. hql '<program>' reads standard input
with source(stdin, Json), writes its result as jq or awk would, and
names no input or output of its own: input is source or tap in the
program, output is target or sink in the program, or the result, presented
by --to. The command-line page of chapter 9 shows
each flag; one program there reads a log as it is written, appends its errors
to a file and prints every message, with no orchestration around it. The
shell connects the processes, and each hql process evaluates its program
with the localhost backend.
Streaming and batching¶
A platform's second vocabulary is about how a pipeline runs. A batch job reads an input that is all there, computes over the whole of it, and writes a result; it ends. A streaming job reads values as they arrive, computes over each, and writes as it goes; it does not end. The same transformation is often wanted both ways, and most platforms have two engines, two APIs or two deployment modes for it.
In HQL the difference is the type of the input, and the four steps of
chapter 9 carry it. source gives a Seq, which ends;
tap gives a Stream, which may not. target writes one completed value;
sink writes each value as it arrives. A transformation written over a
Seq is a batch job, and over a Stream the same text is a streaming job,
and the checker says which steps each allows. The revenue of the shop's
orders, over a table that ends:
type Event {
id : Int
kind : String
customer : String
amount : Decimal
at : String
}
orders : Seq<Event> = source("ledger")
orders | filter(o => o.kind == "order_placed") | map(o => o.amount) | sum
185.74
and over a topic that does not:
type Event {
id : Int
kind : String
customer : String
amount : Decimal
at : String
}
events : Stream<Event> = tap("order_events")
events | filter(o => o.kind == "order_placed") | map(o => o.amount) | sum
check error[type-mismatch]: `sum` needs a finite input, and Stream<Decimal> may not end; bound it first with `take(n)`, or batch it with `collect(n)`
--> <expression>:10:71
|
10 | events | filter(o => o.kind == "order_placed") | map(o => o.amount) | sum
| ^^^
A sum over a stream has no moment to be complete, so it is refused before
the program runs, and the error names the two ways out. take(n) bounds the
stream and makes a batch of its first n values. collect(n) keeps the
stream and makes it a stream of batches, which is the micro-batch of
Spark Structured Streaming: inside each batch every whole-input step
applies, and the result is one value per batch, written as each closes:
events : Stream<Event> = tap("events")
events
| collect(4)
| map(batch => batch | filter(o => o.kind == "order_placed") | map(o => o.amount) | sum)
| sink(stdout, Json)
170.49
15.25
tap over the events file gives the six orders as a stream that ends after
the last, so this program is the streaming job run against recorded input.
That is the other half of the rule: an input that ends can be read either
way, source as a batch or tap as a stream, and a program written for the
stream is tested over a file and replayed over an archive with no change
but the id it reads, which chapter 19 relies on.
The two vocabularies are independent. Streaming and batching say how a pipeline runs, one value at a time or all of them at once. OLTP and OLAP, chapters 19 and 20, say what the data is for, the transactions as they happen or the history they leave. A transactional pipeline is usually streaming and an analytical one usually batch, and the exceptions are everyday: an analytical table filled as events arrive, a transactional table backfilled from an archive. The types let a program say which it is without the platform having a word for the combination.
The shop¶
A small shop sells to three customers, ada, grace and linus. Each thing that happens to an order is an event: placed, shipped, or a payment that failed. On the first day the events are one JSON file, and the shop's whole data platform is a pipeline over it:
source("events")
| filter(e => e.kind == "order_placed")
| map(e => e.customer)
| distinct
["ada", "grace", "linus"]
source("events") names no file. The shop's .hql/config.toml, in
examples/pipelines/shop/,
declares events as a json endpoint, and the program names the id. That
one indirection is what lets the rest of Part II happen without the program
changing shape: as the shop grows, the events move into a table, then onto
a topic, then onto a subject a stream keeps, then into a lakehouse, and at
each move the configuration names the new system and the pipeline keeps its
steps.
| Chapter | The shop's events are in | Read by | Written by |
|---|---|---|---|
| 15. Endpoints | anywhere: the model every system shares | source, tap |
target, sink |
| 16. Tables and topics | a SQLite table, a PostgreSQL table, a Kafka topic | source for a table, tap for a topic |
target, sink |
| 17. Subjects and the lakehouse | NATS subjects, a JetStream stream, Delta tables | tap for a subject, source for a table |
sink, target |
| 18. APIs, webhooks and stores | a REST API, a webhook, an object store, as design | source, tap |
target, sink |
| 19. OLTP | the transactions as they happen, all of the above in one file | tap |
sink |
| 20. OLAP | the history they leave: bronze, silver and gold tables | source |
target |
Every system in chapters 16, 17, 19 and 20 is in the current release. The
programs that reach a server are checked in this book, against the shop's
configuration and with no connection made, and run against real servers in
the hql-demo repository, whose output
is the output the book shows. A checked program is shown with what
hql check prints, the type its result would have, and a pipeline that
ends in target or sink has the type Step: its result is nothing,
because its work is the writing.
A job is one run of a program's plan. An .hql file defines the program;
each invocation starts another run, which may include several pipelines.
Its steps describe transformations, reads and writes. Configuration names
the environment variables that supply credentials and selects the backend.
The schedule determines when the program runs. hql check reads the
configuration without connecting to endpoints, so a deployment can check
the program before running it.
A job's own settings, the customer a report is for or the day it covers,
come from the invocation that runs it. hql --arg customer=ada run
report.hql passes one, and the program reads it with env.arg from
import io.env, as text, with a default where it is not passed
(environment and arguments):
import io.env
type Event {
id : Int
kind : String
customer : String
amount : Decimal
at : String
}
customer = env.arg("customer") | unwrap_or("grace")
orders : Seq<Event> = source("ledger")
orders
| filter(o => o.kind == "order_placed" and o.customer == customer)
| map(o => o.amount)
| sum
135.75
Partitions, schedules and backends¶
The program can declare logical partitioning. Scheduling and backend selection are configuration around it.
A partition is the logical unit of ownership. partition(by = e =>
e.customer) is in the current release: it states that the orders of one
customer belong together, keeps every value and its type, and on
localhost changes nothing else. It names no worker and no Kafka partition.
A backend reads it as the statement that a per-customer group may run on
separate parts without the parts having to meet.
Design only
The rest of this section describes functionality that is not in the current release. The configuration examples illustrate design direction; names and keys may change before it ships.
A schedule is configuration beside the endpoints, [jobs.report] run =
"report.hql" with an at, or a trigger that is itself a source: a subject
whose messages each start a run. The program declares no schedule; when it runs is configuration.
A backend supplies an execution strategy for the checked program. Future backends could use parallel workers in one process, translate a supported computation into SQL, or distribute it across a cluster. Each must preserve the result, types, sequence order, failures and effects of local evaluation. The companion Logical steps and physical plans explains the planning handoff, including Spark SQL and CTEs on Databricks.
[execution]
backend = "spark"
This illustrates future execution configuration. The spark endpoint kind
in chapter 17 already identifies a table the program reads and writes.
Backend selection would additionally choose where pipeline computation
runs, while keeping the checked program's meaning.
What each of them wires¶
| Sources and sinks | Transformations | Where a mistake is found | |
|---|---|---|---|
| Data Factory | linked services and datasets, in JSON | activities, each a box | at validation of the JSON, or in the run's monitor |
| PySpark | readers and writers, in code | DataFrame operations | when the job runs, at the operation that fails |
| ksqlDB | topics as streams and tables | SQL over them | when the statement is submitted, or in the records it skips |
| dbt | models over a warehouse | SQL, with tests after | when the model builds, or when its tests run |
| HQL | ids bound in .hql/config.toml |
the steps of this book | when the file is checked, before anything connects |
The columns are the same four things. What HQL adds is a type for each arrow: stream against table, finite against live, a declared record against a configured boundary, a contract against what a producer actually sent, each checked where the file is written, so that the file that passed is the file that runs.
What is next¶
Chapter 15 looks at what "events" names: an endpoint,
the one model behind a file, a table, a topic and a webhook.