building a data mart without ETL: how we replaced Spark and Airflow with pure ClickHouse

How a single Go binary and a handful of materialized views replaced what would traditionally require Kafka, Spark, Airflow, and a team of data engineers.

The Complexity Tax Nobody Budgets For

We've all seen the "Modern Data Stack" architecture diagrams. Kafka for ingestion. Spark for transformation. Airflow for orchestration. A staging database for intermediate state. dbt for modeling. A separate OLAP engine for analytics. Prometheus and Grafana to monitor the whole thing.

Six services. Three databases. A YAML configuration that rivals War and Peace. And you haven't written a single line of business logic yet.

We lived this. We're building a sports data analytics platform — ingesting match results, league standings, player statistics, betting odds, and predictions from external REST APIs. The data needed to land in a clean, queryable star-schema data mart. The "right" thing to do was obvious: stand up Kafka, wire in Spark, orchestrate with Airflow, model with dbt.

Instead, we asked a dangerous question: What if the database could do all of this by itself?

Turns out, it can. We replaced the entire pipeline with ClickHouse materialized views and a single Go binary. No Kafka. No Spark. No Airflow. No dbt. Here's how — and more importantly, where it breaks.

The Architecture: One INSERT Triggers Everything

External REST APIs (JSON)
        |
  Go Collector (single binary)
        |
  INSERT INTO raw_data
        |  <-- This single INSERT triggers everything
        |
  Materialized Views fire automatically
        |
  +-----------+-----------+-----------+
  |           |           |           |
Dimensions  Facts     Aggregates  Views

The Go collector hits REST APIs, grabs JSON, and inserts it into one table. That's the last thing our application code does. ClickHouse handles parsing, typing, splitting into star schema, pre-computing aggregates — all of it. The entire ETL pipeline is SQL definitions living inside the database.

Why We Chose Star Schema (And Argued About It)

The first internal argument was predictable. ClickHouse is columnar — famous for scanning wide, flat tables at absurd speed. Why bother with a star schema?

We tried the flat approach first. It lasted three weeks.

A team changed its logo. Now we had two versions of the truth across hundreds of thousands of rows. We added a second data source — predictions from a different endpoint — and suddenly team names were duplicated across both tables with slightly different formatting. Storage grew. Queries filtered on strings. Debugging became guesswork.

Star schema fixed all of it:

The join cost objection came up immediately. But star schema joins are fact-to-dimension — the dimension tables are hundreds of rows, loaded entirely into memory as hash tables. We consistently see sub-second queries on millions of fact rows.

And here's the trick: the star schema is invisible to consumers. Layer 4 compatibility views present a flat, denormalized interface. Applications get the simplicity of a wide table. We get the storage efficiency and change management of normalization. Both sides of the tradeoff, neither downside.

Layer 0: The Raw Zone — Our Insurance Policy

Every API response, regardless of endpoint, lands in one table as raw JSON:

CREATE TABLE raw_data (
    id           String,
    source       LowCardinality(String),
    endpoint     LowCardinality(String),
    api_timestamp DateTime,
    ingestion_timestamp DateTime DEFAULT now(),
    season       String,
    data         String,
    checksum     String,
    metadata     Map(String, String)
) ENGINE = MergeTree()
PARTITION BY (source, toYYYYMM(api_timestamp))
ORDER BY (source, endpoint, season, id);

No parsing at write time. No schema validation. Just store it.

This table has saved us twice. Once when a materialized view had a JSON path typo that silently dropped a field for two weeks. Once when the API changed its response format without notice. Both times, we fixed the MV, ran a backfill from raw data, and the mart was whole again in minutes.

The raw zone is not optional. It's the safety net that makes the entire approach viable.

The Key Insight: ClickHouse MVs Are Not What You Think

If you've used materialized views in PostgreSQL, forget everything. In ClickHouse, a materialized view is a trigger, not a cached query. It fires on every INSERT to the source table and writes the transformed result into a completely separate target table.

INSERT INTO raw_data  -->  MV fires  -->  INSERT INTO mart.fact_matches
                      -->  MV fires  -->  INSERT INTO mart.dim_teams
                      -->  MV fires  -->  INSERT INTO mart.dim_leagues

Your application inserts raw JSON. ClickHouse parses it, casts types, splits it into dimensions and facts, and writes everything to the mart — automatically. No orchestrator. No job scheduler. No DAG. The database is the pipeline.

One thing caught us off guard: MVs fire sequentially within the INSERT pipeline. They're not background jobs — they add latency to the INSERT itself. With our 10 MVs, the overhead is negligible. Past 15-20, you'll want to monitor insert latency and consider splitting source tables by endpoint.

Layer 1: Dimensions — Reference Data That Builds Itself

In a traditional warehouse, populating dimension tables is a separate ETL job with its own schedule, its own failure modes, and its own on-call alerts.

We don't have that job. Every match record from the API already contains team names, league info, and venue details embedded in the JSON. The dimension MVs just extract them as a side effect of ingesting facts.

The dimension tables use ReplacingMergeTree(ingestion_timestamp) — duplicate inserts resolve automatically, keeping only the latest version after compaction. By the time fact tables are populated, every foreign key already has a matching dimension record. Zero orchestration. Zero dependency management.

Layer 2: Facts — JSON to Star Schema in One Statement

Each fact MV transforms a specific endpoint's JSON into a typed, keyed fact table. The patterns that matter:

Handling nested JSON. Not all responses are flat. League standings arrive as deeply nested arrays — groups containing teams containing statistics. ClickHouse handles this natively: JSONExtractArrayRaw to grab the array, arrayJoin to explode it, then extract fields from each element. Three CTEs turn a nested document into flat rows. No Python. No Spark. Pure SQL.

Layer 3: Aggregates — Pre-Computed Analytics

Some queries can't afford to scan millions of rows. Dashboards. API responses. Real-time lookups. For these, aggregate materialized views sit on top of fact tables using ClickHouse's State / Merge functions.

These aren't traditional aggregation queries that re-scan history. They store partial aggregation states as compact binary blobs, incrementally updated on every insert. New match result comes in? The state functions merge new data into existing aggregates without touching a single historical row.

Head-to-head records between rivals. League season summaries. Team form over the last 10 matches. All pre-computed. All point lookups.

Layer 4: Compatibility Views — The Flat Interface

Standard SQL views (not materialized) join facts with dimensions and present a clean, human-readable query API:

SELECT * FROM v_fixtures WHERE league_name = 'Premier League'

Applications get team names, venue names, and scores. They have no idea there are integer IDs, JSON parsing, or a five-layer MV chain underneath. The complexity is fully encapsulated in the database.

For latency-sensitive paths, ClickHouse Dictionary tables replace joins entirely — dimensions are loaded into memory at startup and queried with dictGet(). Pure in-memory lookup, zero join overhead.

Choosing the Right Engine

Each table engine is a deliberate choice, not a default:

ReplacingMergeTree is the workhorse. It gives us update semantics in an append-only system — each version is a new row, background merges keep only the highest version within a partition, and FINAL guarantees the latest version at read time. No UPDATE statements. No row-level locking. Just INSERT.

One gotcha we hit early: SummingMergeTree sums all non-key numeric columns. Status codes, enums, flags — if it's an integer and it's not in the ORDER BY, it gets summed during merges. We learned this the hard way when a status field silently accumulated to nonsensical values.

The Economics: Why This Actually Matters

Technical elegance is nice. Saving $20,000-40,000 a year is nicer.

The traditional stack — Kafka, Spark, Airflow, staging DB, dbt, OLAP engine, monitoring for each — runs $1,500-3,500/month minimum. And that's before you hire someone who knows how to operate all six of them.

Our stack: one ClickHouse instance and a Go binary on the same machine.

But the dollar savings aren't the real story. The real savings are operational:

We don't have a data platform on-call rotation. When something goes wrong — and it does — one person reads the ClickHouse logs, finds the SQL issue, and fixes it. Mean time to recovery: minutes.

To be fair: if you genuinely need Kafka's durability guarantees at scale, Spark's distributed compute, or Airflow's complex dependency management, the traditional stack earns its cost. The question is whether you're at that scale yet. Most teams adopt the complex stack because it's what "real" data engineering looks like, not because their data volume demands it.

Where It Breaks: Honest Tradeoffs

This approach is not magic. Here's where it has bitten us.

MVs Only See New Data

This catches everyone. A materialized view only processes data that arrives after it's created. It does not retroactively process existing rows.

We created a new MV for prediction data, queried the target table, and stared at zero rows for ten confused minutes before realizing: the MV was waiting for the next INSERT. Six months of historical data sitting in the raw zone, completely invisible to it.

The fix is a manual backfill — INSERT INTO ... SELECT FROM raw_data with the same transformation logic as the MV. Every MV needs a companion backfill script. Both must stay in sync. We version-control them side by side.

ClickHouse offers POPULATE for atomic create-and-backfill, but data inserted during population can fall into a gap. For tables with continuous writes, create without POPULATE and backfill separately.

MV Failures Are Silent — And That's Dangerous

This one cost us two weeks of partial data.

By default (materialized_views_ignore_errors = 1), a failing MV silently drops records. The raw INSERT succeeds. The application sees no error. The mart table simply never gets the row. No log entry. No alert. The data vanishes.

We didn't notice until an analyst asked why prediction counts had dropped. Thirty days of partial data in the mart, caused by an API format change that broke one JSON path in one MV.

Strict mode (materialized_views_ignore_errors = 0) trades silent drops for a different problem: a failing MV causes the INSERT to error, but the raw data may already be written, so retries create duplicates.

We defend against this with three daily checks: row-count comparisons between raw and mart tables, zero-ID scans for parsing failures (match_id = 0 means the JSON path returned empty), and freshness monitoring for endpoints that stop producing data. The raw zone always has everything — gaps in the mart are fixable with a backfill. But you have to know the gap exists first.

Schema Evolution Is a Three-Step Dance

Adding a field means: alter the target table, drop and recreate the MV with the new extraction logic, then backfill historical data. Between recreate and backfill, the new column is NULL for all historical rows.

It's not complex, but it's manual and it requires downtime awareness. We keep MV definitions and backfill scripts in the same directory, deployed together.

Other Scars

When This Approach Fits

It works well when:

It doesn't fit when:

Watch for outgrowth signals: INSERT latency climbing as MV count grows, FINAL queries slowing on hundreds of millions of rows, backfills taking hours instead of minutes. The migration path is smoother than you'd expect — the raw zone survives intact and can feed any future pipeline. You're not locked in. You're just not paying for complexity you don't need yet.

Try It Yourself

The entire pattern fits in a few lines:

-- Raw table: stores unstructured JSON
CREATE TABLE raw_events (data String, ts DateTime DEFAULT now())
ENGINE = MergeTree() ORDER BY ts;

-- Target table: clean, typed schema
CREATE TABLE events (
    event_id UInt32, event_type String, user_id UInt32, ts DateTime
) ENGINE = ReplacingMergeTree() ORDER BY (event_type, event_id);

-- The "ETL pipeline": one materialized view
CREATE MATERIALIZED VIEW mv_events TO events AS
SELECT
    toUInt32OrZero(JSONExtractString(data, 'id'))  AS event_id,
    JSONExtractString(data, 'type')                 AS event_type,
    toUInt32OrZero(JSONExtractString(data, 'user')) AS user_id,
    ts
FROM raw_events;

-- Insert raw JSON — the MV fires automatically
INSERT INTO raw_events (data) VALUES
  ('{"id": "1", "type": "click", "user": "42"}'),
  ('{"id": "2", "type": "purchase", "user": "42"}');

-- Query the mart — already transformed
SELECT * FROM events;

That's it. Scale by adding more MVs, more target tables, and aggregate views for dashboards. The mechanism never changes: insert raw data, let the database do the rest.

Final Thoughts

The data engineering world has a complexity addiction. We reach for distributed systems before we've outgrown a single node. We add orchestration layers before we have jobs to orchestrate. We build pipelines before we know what questions we need to answer.

ClickHouse materialized views offer a different path. Not a better path for everyone — but for small-to-medium platforms, a dramatically simpler one. We didn't set out to be clever. We set out to ship fast with what we had.

The simplest thing that could possibly work turned out to be SQL all the way down.

Sometimes the best architecture is the one you don't have to operate.