BI Connector Best Practices¶

Advanced

In this guide you'll learn the best practices & limitations of the BI Connector in Tinybird.

The BI Connector is a PostgreSQL-compatible interface to data in Tinybird.

All Data Sources and published Pipes created in Tinybird will be available as standard Postgres tables. You can query using standard Postgres syntax & use any tool that implements the Postgres protocol. This means you can connect many of your favorite tools to Tinybird, such as Tableau, Apache SuperSet or Grafana.

Tinybird is not designed to be a replacement for the Data Warehouse, nor used as a general purpose BI platform. However, there are use cases where it makes sense to use a BI tool over Tinybird. We encourage you to reach out to discuss your needs so we can help to guide you.

Best practices¶

Avoid queries on exposed Pipes¶

Both your Pipes & Data Sources are exposed in the BI Connector as tables. We recommend that you only query against Data Sources tables, and avoid using any Pipes tables. In general, queries on Data Sources tables are much faster & easier to optimise.

Use Materialized Views¶

Tinybird allows you to build Pipes that materialize the result as a Materialized View. Materialized Views will appear in the BI Connector as tables.

Rather than querying the raw data via the BI Connector, we recommend you build your transformations, filters, aggregations or joins in standard Tinybird Pipes to create a Materialized View. You can then use the BI Connector to read the pre-prepared Materialized View.

For example, if your dashboard shows events aggregated by day, create a Materialized View with pre-aggregated data by day, and have your BI chart read this.

Use dedicated Materialized Views for different charts¶

If you have multiple charts or widgets consuming data, try to use Materialized Views that are specifically built for that chart or widget. This helps to avoid additional transformation operations.

Use efficient sorting keys¶

Data stored in Tinybird is sorted using a sorting key. This helps to efficiently find data that is relevant to your query.

You should use the appropriate sorting key(s) for your data according to the needs of your dashboard.

For example, if your dashboard is filtering by time, you should have time in the table's sorting key.

Avoid column mappings¶

Avoid using column mappings. Filtering by a mapped column can mean that the index (sorting key) is not being used.

Avoid JOINs¶

We recommend that you do not do JOINs over the BI Connector where at all possible. Instead, do any JOINs inside a Pipe and materialize the result as a Materialized View, then query the already-JOINed data.

Monitor usage with bi_stats_rt¶

The bi_stats_rt table contains observability data about your usage of the BI Connector. This is a great way to get insights into your queries and inform your optimisations.

Limitations¶

• Query timeout is 10 seconds.
• Queries over the BI Connector are only allocated 1 CPU core per query (this only applies to the BI Connector, not to standard Tinybird Pipes)
• Some JOIN operations are run in Postgres and not ClickHouse, which means they can run much, much slower. Avoid JOINs over the BI Connector.
• Filters could not use sorting keys, that could make queries slower and more expensive, since they require full table scans.
• Querying large datasets can result in a timeout. Large generally means many millions of rows, but this is also affected by how many columns and how large the values are.
• A single query should not return more than 1GB of data at a time.
• The total amount of data returned by all queries executed within a 5-minute period should not exceed 6GB. This is to prevent overloading the system with too much data in a short period of time.