BI Connector¶
The BI Connector is a PostgreSQL-compatible interface to data in Tinybird.
All Data Sources and published Pipes created in Tinybird are 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 on top of Tinybird.
The BI Connector is not enabled by default. Contact Tinybird support (support@tinybird.co) to discuss if your use case is supported.
Best practices¶
1. 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 optimize.
2. Use Materialized Views¶
Tinybird allows you to build Pipes that materialize the result as a Materialized View. Materialized Views appear in the BI Connector as tables.
Rather than querying the raw data via the BI Connector, it's recommended to 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.
3. 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.
4. 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.
5. Avoid column mappings¶
Avoid using column mappings. Filtering by a mapped column can mean that the index (sorting key) is not being used.
6. Avoid JOINs¶
We recommend that you do not do JOINs over the BI Connector where possible. Instead, do any JOINs inside a Pipe and materialize the result as a Materialized View, then query the already-JOINed data.
7. 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 optimizations.
Limitations¶
Some settings can be adjusted on request. Contact Tinybird support (support@tinybird.co) to discuss if we can support your use case.
- 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 might run much, much more slowly. Avoid JOINs over the BI Connector.
- In some cases, Postgres might rewrite the query so that filters could end up not using sorting keys. Sorting keys allow you to run faster queries, and therefore those resulting queries could be slower and more expensive since they would 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.
Next steps¶
- Read up on Materialized View and getting the most from your data.
- Understand how to use and query Tinybird's Service Data Sources.