Build a user-facing analytics dashboard with BigQuery and Tinybird¶

In this guide you'll learn how to take data from BigQuery and build a user-facing analytics dashboard using Tinybird, Next.js, and Tremor components. You'll end up with a dashboard and enough familiarity with Tremor to adjust the frontend & data visualization for your own projects in the future.

Google BigQuery is a serverless data warehouse, offering powerful online analytical processing (OLAP) computations over large data sets with a familiar SQL interface. Since its launch in 2010, it's been widely adopted by Google Cloud users to handle long-running analytics queries to support strategic decision-making through business intelligence (BI) visualizations.

Sometimes, however, you want to extend the functionality of your BigQuery data beyond business intelligence: For instance, real-time data visualizations that can be integrated into user-facing applications. As outlined in the Tinybird blog post on BigQuery dashboard options, you can build Looker Studio dashboards over BigQuery data, but they'll struggle to support user-facing applications that require high concurrency, fresh data, and low-latency API responses. Tinybird is the smart option for fast and real-time.

Let's get building!

Imagine you're a huge baseball fan. You want to build a real-time dashboard that aggregates up-to-the-moment accurate baseball stats from teams around the world, and gives you the scoop on all your favorite players. This tutorial explains how to build a really nice-looking prototype version.

In this tutorial, you'll learn how to:

1. Ingest your existing BigQuery data into Tinybird.
2. Process and transform that data with accessible SQL.
3. Publish the transformations as real-time APIs.
4. Use Tremor components in a Next.js app to build a clean, responsive, real-time dashboard that consumes those API Endpoints.

Prerequisites¶

To complete this tutorial, you'll need:

1. A free Tinybird account
2. A BigQuery account
3. Node.js >=20.11

This tutorial includes a Next.js app and Tremor components for frontend visualization, but you don't need working familiarity with TypeScript or JavaScript - just copy & paste the code snippets.

1. Create a Tinybird Workspace¶

Navigate to the Tinybird web UI (app.tinybird.co) and create an empty Tinybird Workspace (no starter kit) called bigquery_dashboard in your preferred region.

2. Connect your BigQuery dataset to Tinybird¶

To get your BigQuery data into Tinybird, you'll use the Tinybird BigQuery Connector.

Download this sample dataset that contains 20,000 rows of fake baseball stats. Upload it to your BigQuery project as a new CSV dataset.

Next, follow the steps in the documentation to authorize Tinybird to view your BigQuery tables, select the table you want to sync, and set a sync schedule. Call the Data Source baseball_game_stats.

Tinybird will copy the contents of your BigQuery table into a Tinybird Data Source and ensure the Data Source stays in sync with your BigQuery table.

3. Create some Pipes¶

In Tinybird, a Pipe is a transformation definition comprised of a series of SQL statements. You can build metrics through a series of short, composable Nodes of SQL. Think of Pipes as a way to build SQL queries without always needing to write common table expressions or subqueries, as these can be split out into reusable, independent Nodes.

For example, here's a simple single-Node Pipe definition that calculates the season batting average for each player:

SELECT
  player_name AS "Player Name",
  sum(stat_hits)/sum(stat_at_bats) AS "Batting Percentage"
FROM baseball_game_stats
GROUP BY "Player Name"
ORDER BY "Batting Percentage" DESC

Create your first Pipe from your newly-created BigQuery Data Source by selecting “Create Pipe” in the top right corner of the Tinybird UI. Paste in the SQL above and run the query. Rename the Pipe player_batting_percentages.

4. Extend Pipes with Query Parameters¶

Every good dashboard is interactive. You can make your Tinybird queries interactive using Tinybird's templating language to generate query parameters. In Tinybird, you add query parameters using {{<DateType>(<name>,<default_value>}}, defining the data type of the parameter, its name, and an optional default value.

For example, you can extend the SQL query in the previous step to dynamically change the number of results returned from the Pipe, by using a limit parameter and a default value of 10:

SELECT
  player_name AS "Player Name",
  sum(stat_hits)/sum(stat_at_bats) AS "Batting Percentage"
FROM baseball_game_stats
GROUP BY "Player Name"
ORDER BY "Batting Percentage" DESC
LIMIT {{UInt16(limit, 10, description="The number of results to display")}}

Replace the SQL in your Pipe with this code snippet. Run the query and rename the Node endpoint.

5. Publish your Pipes as APIs¶

The magic of Tinybird is that you can instantly publish your Pipes as fully-documented, scalable REST APIs instantly. From the Pipe definition in the Tinybird UI, select “Create API Endpoint” in the top right corner, select the endpoint Node.

Congratulations! You just ingested BigQuery data, transformed it, and published it as a Tinybird API Endpoint!

Create additional Pipes¶

Create these additional 5 Pipes (they can also be found in the project repository). Rename them as they are titled in each snippet, and call each Node endpoint. Read through the SQL to get a sense of what each query does, then run and publish each one as its own API Endpoint:

%
SELECT
    game_date AS "Game Date",
    sum(stat_hits)/sum(stat_at_bats) AS "Batting Percentage"
FROM baseball_game_stats
WHERE player_team = {{String(team_name, 'BOS', required=True)}}
GROUP BY "Game Date"
ORDER BY "Game Date" ASC

%
SELECT player_name AS name, sum({{ column(hit_type, 'stat_hits') }}) AS value
FROM baseball_game_stats
GROUP BY name
ORDER BY value DESC
LIMIT 7

%
SELECT game_opponent AS "Team", sum(stat_hits) / sum(stat_at_bats) AS "Opponent Batting Percentage"
FROM baseball_game_stats
GROUP BY "Team"
ORDER BY "Opponent Batting Percentage" ASC
LIMIT {{ UInt16(limit, 10) }}

%
SELECT
    player_name AS "Player Name",
    sum(stat_hits)/sum(stat_at_bats) AS "Batting Percentage"
FROM baseball_game_stats
GROUP BY "Player Name"
ORDER BY "Batting Percentage" DESC
LIMIT {{UInt16(limit, 10)}}

%
SELECT player_team AS "Team", sum(stat_hits) / sum(stat_at_bats) AS "Batting Percentage"
FROM baseball_game_stats
GROUP BY "Team"
ORDER BY "Batting Percentage" DESC
LIMIT {{ UInt16(limit, 10) }}

1 Data Source, 6 Pipes: Perfect. Onto the next step.

6. Create a Next.js app¶

In your terminal, create a project folder and inside it create your Next.js app, using all the default options:

mkdir bigquery-tinybird-dashboard
cd bigquery-tinybird-dashboard
npx create-next-app

Tinybird APIs are accessible via Tokens. In order to run your dashboard locally, you'll need to create a .env.local file at the root of your new project:

touch .env.local

And include the following:

NEXT_PUBLIC_TINYBIRD_HOST="YOUR TINYBIRD API HOST" # Your regional API host e.g. https://api.tinybird.co
NEXT_PUBLIC_TINYBIRD_TOKEN="YOUR SIGNING TOKEN" # Use your Admin Token as the signing token

7. Define your APIs in code¶

To support the dashboard components you're about to build, it's a great idea to create a helper file that contains all your Tinybird API references. In the project repo, that's called tinybird.js and it looks like this:

const playerBattingPercentagesURL = (host, token, limit) =>
    `https://${host}/v0/pipes/player_batting_percentages.json?limit=${limit}&token=${token}`

const teamBattingPercentagesURL = (host, token, limit) =>
    `https://${host}/v0/pipes/team_batting_percentages.json?limit=${limit}&token=${token}`

const opponentBattingPercentagesURL = (host, token, limit) =>
    `https://${host}/v0/pipes/opponent_batting_percentages.json?limit=${limit}&token=${token}`

const battingPercentageOverTimeURL = (host, token, team_name) =>
    `https://${host}/v0/pipes/batting_percentage_over_time.json?team_name=${team_name}&token=${token}`

const hitsByTypeURL = (host, token, hit_type) =>
    `https://${host}/v0/pipes/most_hits_by_type.json?hit_type=${hit_type}&token=${token}`

const fetchTinybirdUrl = async (fetchUrl, setData, setLatency) => {
    const data = await fetch(fetchUrl)
    const jsonData = await data.json();
    setData(jsonData.data);
    setLatency(jsonData.statistics.elapsed)
}

export {
    fetchTinybirdUrl,
    playerBattingPercentagesURL,
    teamBattingPercentagesURL,
    opponentBattingPercentagesURL,
    battingPercentageOverTimeURL,
    hitsByTypeURL
}

Inside /src/app, create a new subfolder called /services and paste the snippet into a new tinybird.js helper file.

8. Build your dashboard components¶

This tutorial uses the Tremor React library because it provides a clean UI out of the box with very little code. You could easily use ECharts or something similar if you prefer.

Add Tremor to your Next.js app¶

You're going to use Tremor to create a simple bar chart that displays the signature count for each organization. Tremor provides stylish React chart components that you can deploy easily and customize as needed.

Inside your app folder, install Tremor with the CLI:

npx @tremor/cli@latest init

Select Next as your framework and allow Tremor to overwrite your existing tailwind.config.js.

Create dashboard component files¶

Your final dashboard contains 3 Bar Charts, 1 Area Chart, and 1 Bar List. You'll use Tremor Cards to display these components, and each one will have an interactive input. In addition, you'll show the API response latency underneath the Chart (just so you can show off about how “real-timey” the dashboard is).

Here's the code for the Player Batting Averages component (playerBattingPercentages.js). It sets up the file, defines the limit parameters, then renders the Chart components:

"use-client";

import { Card, Title, Subtitle, BarChart, Text, NumberInput, Flex } from '@tremor/react'; // Tremor components
import React, { useState, useEffect } from 'react';
import {fetchTinybirdUrl, playerBattingPercentagesURL } from '../services/tinybird.js' // Tinybird API

// utilize useState/useEffect to get data from Tinybird APIs on change
const PlayerBattingPercentages = ({host, token}) => {
    const [player_batting_percentages, setData] = useState([{
        "Player Name": "",
        "Batting Percentage": 0,
    }]);

    // set latency from the API response
    const [latency, setLatency] = useState(0

    // set limit parameter when the component input is changed
    const [limit, setLimit] = useState(10);

    // format the numbers on the component
    const valueFormatter = (number) => `${new Intl.NumberFormat("us").format(number).toString()}`;

    // set the Tinybird API URL with query parameters
    let player_batting_percentages_url = playerBattingPercentagesURL(host, token, limit)


    useEffect(() => {
        fetchTinybirdUrl(player_batting_percentages_url, setData, setLatency)
    }, [player_batting_percentages_url]);

    // build the Tremor component
    return (
        <Card>
            <Flex>
                <div className="card-title">
                    <Title>Player Batting Percentages</Title>
                    <Subtitle>All Players</Subtitle>
                </div>
                <div className="chart-input">
                    <Text># of Results</Text>
                    <NumberInput
                        className="number-input"
                        defaultValue="10"
                        // pass a new “limit” parameter to Tinybird API on change
                        onValueChange={(value) => setLimit(value)}
                    />
                </div>
            </Flex>
            // Build the bar chart with the data received from the Tinybird API
            <BarChart
                data={player_batting_percentages}
                index="Player Name"
                categories={["Batting Percentage"]}
                className="mt-6"
                colors={["blue"]}
                valueFormatter={valueFormatter}
            />
            <Text>Latency: {latency*1000} ms</Text> // Add the latency metric
        </Card>
    );
};

export default PlayerBattingPercentages;

In the project repo, you'll find the 5 dashboard components you need, inside the src/app/components directory. Each one renders a dashboard component to display the data received by one of the Tinybird APIs.

It's time to build them out. For this tutorial, just recreate the same files in your app, pasting in the JavaScript (or downloading the files and dropping them in to your app directory). When building your own dashboard in future, use this as a template and build to fit your needs!

9. Compile components into a dashboard¶

Final step! Update your page.tsx file to render a nicely-organized dashboard with your 5 components: Replace the contents of page.tsx with this file.

The logic in this page gets your Tinybird Token from your local environment variables to be able to access the Tinybird APIs, then renders the 5 components you just built in a Tremor Grid.

To visualize your dashboard, run it locally with npm run dev and open http://localhost:3000. You'll see your complete real-time dashboard!

Notice the latencies in each dashboard component. This is the Tinybird API request latency. This is not using any sort of cache or query optimization; each request is directly querying the 20,000 rows in the table and returning a response. As you interact with the dashboard and change inputs, the APIs respond. In this case, that's happening in just a few milliseconds. Now that's a fast dashboard.

Optional: Expand your dashboard¶

You've got the basics: An active Workspace and Data Source, knowledge of how to build Pipes, and access to the Tremor docs. Build out some more Pipes, API Endpoints, and visualizations!

You can also spend some time optimizing your data project for faster responses and minimal data processing using fine-tuned indexes, Materialized Views, and more.

Next steps¶

• Investigate the GitHub repository for this project in more depth.
• Understand today's real-time analytics landscape with Tinybird's definitive guide.
• Learn how to implement multi-tenant security in your user-facing analytics.