Real-Time Dashboard with Tinybird

Tinybird has achieved substantial increase in analytical power over ksqlDB and comparable products, due to its introduction of ClickHouse database. At Infinite Lambda, we evaluated Tinybird over some complex joins operation over 200 million of rows, and found the latency to be well within 30 seconds.

Tinybird offers data lineage similar to Snowflake Dynamic Tables/ Databricks Delta Live Tables. However, as it takes an Apache Kafka setup, this is a highly complex solution that comes with steep requirements in terms of skills. The latter can potentially be mitigated by opting for Confluent Cloud.

Data source, pipes and materialised views

Let’s start with the basics.

Once you have set up Kafka with Debezium, Tinybird can natively read from the Kafka and process the Change Data Capture (CDC) stream in real time.

A data source is created from each topic, which itself originated from each table. You can then refer to the data source in any other queries.

Queries which carry transform, and are themselves not data sources, are called pipes. There are only two layers of objects — data source and pipes — which makes Tinybird a flatter architecture than the medallion structure that we tend to find in a data warehouse.

You can publish all pipes as API endpoints, and have them exposed in Swagger. When an API call is made, the underlying queries are performed. This is a good practice for data that is seldom used.

In other cases, you can use materialised views for publishing the pipes. Here, the transform is being computed in real time and materialised so that API calls can be served quickly. There are also business Intelligence connectors available to hook up with solutions like Cube.js.

Tinybird’s edge: ClickHouse under the hood

The key differentiator in Tinybird’s architecture lies in its adoption of ClickHouse as the core engine.

This is important for three main reasons.

First, it enables high-performance analytics. The columnar storage allows for efficient aggregation and filtering, which is crucial for real-time queries over large datasets. The vectorised execution processes data in batches, reducing input/output and boosting query speeds.

Second, it makes complex joins perfectly feasible. There is rich SQL Support, as Tinybird leverages ClickHouse’s advanced SQL capabilities to enable joins, window functions, and subqueries that are often considered more suitable for data warehousing solutions. Moreover, in terms of scalability, it supports millions to billions of rows while maintaining near-real-time query speeds.

Finally, the latency is low, even at 200 million rows. This allows for efficient query planning — our tests demonstrated that queries with multiple joins could still complete within just a few seconds. The incremental ingestion when streaming data into Tinybird means near-instantaneous availability for your queries.

Data lineage and workflow capabilities

In addition to raw query performance, modern data engineering solutions call for robust data lineage and workflow management. Tinybird meets these requirements by offering data lineage similar to Snowflake and Databricks, as well as APIs for real-time data access.

The visual workflows provide a clear depiction of data flows from ingestion to transformation to final outputs. You can also easily schedule tasks, while dependent pipelines are automatically updated when the upstream data changes.

The API-first approach has a direct advantage of exposing data transformations as APIs, enabling quick integration into real-time dashboards and applications.

Kafka requirements: challenges and mitigations

Despite its strengths, Tinybird often runs on top of Apache Kafka for ingesting real-time data streams. This introduces some challenges.

First, it takes deep expertise operating Kafka clusters and managing topics. It also comes with infrastructural complexity, as self-managed Kafka clusters demand monitoring, scaling, and maintenance, raising the bar on operational overhead.

The potential solution comes in the form of Confluent Cloud, as outsourcing Kafka to Confluent Cloud can drastically lower the complexity. Confluent’s managed service handles scaling, updates, and resource allocation to ease the burden on internal teams.

Alternatively, if your use case does not strictly depend on Kafka’s real-time capabilities, you might explore Tinybird’s other connectors or ingestion APIs to try to reduce complexity.

SQL limitations in complex joins

As part of the ClickHouse limitation in complex joins, if a condition refers to columns from different tables, at this point only the equality operator (=) is supported.

In our case, this entails rewriting some of our inner joins to cross joins, moving the comparative operators (e.g. >, <) to WHERE conditions. Such CROSS JOINS would be inefficient if the tables of both sides of the join are big.

However, we recognise that in the typical analytics use cases, equality operators will suffice. Furthermore, if you put filters in the number of rows being joined, even a CROSS JOIN can result in an efficient query.

For example, this Snowflake query is not allowed in Tinybird:

This CROSS JOIN version, on the other hand, is going to work with Tinybird:

The above query will be successful because the calendar table is relatively small. If the tables are big on both sides of the joins, always aim to filter the rows first.

Performance evaluation: complex joins on 200 million rows

Let’s now look at a real-work project example that served as the basis of our observations.

Setup and methodology

• Dataset: 200 million rows drawn from simulated e-commerce transactions;
• Joins: Included multi-table joins with dimension data on products, customers, and geolocation;
• Queries: Focused on aggregations, such as total sales by region, monthly revenue, combined with filtering and sorting operations.

Optimisation

We have worked on joining two streams with over a hundred million rows. There are certain ways to optimise such workloads to ensure it fits into the memory. For example, we will rewrite the following joins from this:

...into this, where the memory footprint in ClickHouse can be significantly reduced:

On Snowflake, this would not make much of a difference. However, due to the internal workings of ClickHouse treating the sort key, on Tinybird you will get low latency and high concurrency.

Let’s have a closer look:

• Query latency: Across multiple tests, end-to-end latency remained within just 30 seconds, which was noteworthy given the number of rows and join complexity. Moreover, the latency is mainly related to Debezium, as Tinybird only contributes to 3 seconds approximately;
• Concurrency: Tinybird’s underlying ClickHouse engine handled concurrent queries efficiently, showing minimal performance degradation as additional user sessions were introduced.

Recommendations for enterprises

If you are considering using Tinybird at enterprise level, here is how to maximise the benefits:

1. Assess real-time vs. batch needs: If you require sub-second or near-second real-time analytics and have a Kafka pipeline, Tinybird can offer a significant performance advantage;
2. Consider managed services: If running Kafka in-house is too complex, using Confluent Cloud mitigates many operational challenges while retaining real-time ingestion benefits;
3. Optimise data model: Simplify join conditions and data structures to align with ClickHouse best practices. Well-partitioned, denormalised schemas often yield the lowest latencies;
4. Leverage data lineage features: Adopt Tinybird’s workflow and lineage tools to ensure clear visibility into data transformations, simplifying audits and compliance checks.

Further reading

• Reducing Latency with Snowflake Dynamic Tables and Minimal Rewrite of Existing dbt Models
• Real-Time Analytics: The Updates You Need
• On Using the Join Clause
• On Using Materialised Views

Visit the Infinite Lambda Blog for fresh technological insights.

Check out our case studies to see how we implement data and AI technology to solve modern business challenges.