Build a lambda architecture in Tinybird¶

Use this alternative processing pattern when the typical Tinybird flow doesn't fit.

The typical flow is Data Source --> incremental transformation through Materialized Views --> API Endpoint publication. When Materialized Views don't fit your processing requirements, use Copy Pipes to create intermediate Data Sources that keep your Endpoints performant.

The ideal Tinybird flow¶

You ingest data (usually streamed in, but can also be in batch), transform it using SQL, and serve the results of the queries via parameterizable Endpoints. Tinybird provides freshness, low latency, and high concurrency: Your data is ready to be queried as soon as it arrives.

Sometimes, transforming the data at query time isn't ideal. Some operations - doing aggregations, or extracting fields from JSON - are better if done at ingest time, then you can query that prepared data. Materialized Views are perfect for this kind of situation. They're triggered at ingest time and create intermediate tables (data sources in Tinybird lingo) to keep your Endpoints performance super efficient.

The best practice for this approach is usually having a Materialized View (MV) per use case:

If your use case fits in these first two paragraphs, stop reading. No need to over-engineer it.

When the ideal flow isn't enough¶

There are some cases where you may need intermediate Data Sources (tables) and Materialized Views don't fit.

• Most common: Things like Window Functions where you need to check the whole table to make calculations.
• Fairly common: Needing an Aggregation MV over a deduplication table (ReplacingMergeTree).
• Scenarios where Materialized Views fit but aren't super efficient (hey uniqState).
• And lastly, one of the hardest problems in syncing OLTP and OLAP databases: Change data capture (CDC).

For example, consider the Aggregation Materialized Views over deduplication DS scenario.

Deduplication in Tinybird happens asynchronously, during merges, which you can't force in Tinybird. That's why you always have to add FINAL or the -Merge combinator when querying.

Materialized Views only see the block of data being processed. When materializing an aggregation, the Materialized View processes any new row, whether it has a new or duplicate ID. That's why this pattern fails.

Solution: Use an alternative architecture with Copy Pipes¶

Tinybird has another kind of Pipe that helps here: Copy Pipes.

At a high level, they're a helpful INSERT INTO SELECT, and they can be set to execute following a cron expression. You write your query, and (either on a recurring basis or on demand), the Copy Pipe appends the result in a different table.

So, in this example, you can have a clean, deduplicated snapshot of your data, with the correct Sorting Keys, and can use it to materialize:

Avoid loss of freshness¶

"But if you recreate the snapshot every hour, day, or another interval, aren't you losing freshness?" Yes. That's when the lambda architecture comes into play:

Combine the already-prepared data with the same operations over the fresh data being ingested at that moment. This provides higher performance despite complex logic over both fresh and old data.

Next steps¶

• Read more about Copy Pipes.
• Read more about Materialized Views.