DEV Community: Tinybird

Build an analytics agent to analyze your Ghost blog traffic with the Vercel AI SDK and Tinybird

Cameron Archer — Wed, 06 Aug 2025 16:58:54 +0000

What excites me most about the inflow of AI and LLMs into every nook and cranny of my daily life is the opportunity to point an agent at a big block of data, ask questions, and get answers. I've spent most of my career analyzing data. I like data. I like analyzing data. I used to like writing SQL. Now I don't (mostly). Now that I've tasted the sweet goodness of agentic analytics powered by tools like Vercel AI SDK and Tinybird MCP Server, I'd rather give an agent context and have it query the database for me.

However, this isn't as simple as it sounds. LLMs are surprisingly bad at writing SQL. But with Tinybird (and the Tinybird MCP Server) and the Vercel AI SDK, it's quite straightforward (and mostly prompt engineering).

In this post, I'll show you how to build an agent with sufficient contextual understanding of underlying analytics data - and the tools to query it - so that you can have a chat with your data (any data!). Specifically, I'll build a simple analytics agent for a blog - hosted on the open-source publishing platform Ghost. The agent will tell us which content is performing the best, and why.

The tools for the job:

Ghost (blog hosting)
Vercel AI SDK (Typescript agent framework)
Tinybird (database + analytics MCP + tools)

Blog analytics on Ghost

Ghost is an open source publishing platform, great for anybody who wants to self-host their blogging platform. You can get up and running immediately with Ghost(Pro), their hosted offering, but many publishers choose to self-host.

In either case, the recent release of Ghost 6.0 includes real-time, multi-channel analytics via Tinybird, the platform on which Ghost's analytics run. If you choose to self-host, you'll also get unfettered access to the underlying data in Tinybird. Alongside Ghost, you can use Tinybird Cloud - for managed analytics hosting - or Tinybird self-managed regions for a complete self-hosted setup.

Self-hosting Ghost + Tinybird is straightforward, and I won't cover it here. You can follow Ghost's self-hosting guides to learn how to do it.

For now, I'll assume you've already set up Ghost self-hosted with Tinybird web analytics enabled, and, as a part of that setup, you have created a Tinybird workspace (hosted or self-managed) that is receiving web traffic events from Ghost.

If you don't already have that, but want to follow along anyway, here's how to set up Tinybird for basic testing:

Basic Tinybird setup

tb login
# deploy the Ghost Web Analytics template
tb --cloud deploy --template https://github.com/TryGhost/Ghost/tree/main/ghost/core/core/server/data/tinybird

The template includes a small fixture of sample data you can append for testing:

tb --cloud datasource append analytics_events fixtures/analytics_events.ndjson

You can then query the data in the analytics_events data source to get a basic feel for what's in it:

tb --cloud sql 'select uniq(session_id) from analytics_events'
# Running against Tinybird Local
#   uniq(session_id)  
#             UInt64  
# ─────────────────────
#                 24

(Or use Tinybird's built-in analytics agent, Tinybird Code):

tb --prompt "How many sessions are there?"
# Based on the data in the analytics_events data source, I can see that you have 24 unique sessions

In addition to the data source holding the blog traffic events, the template aslo includes several SQL-based API endpoints that provide metrics like top pages, top browsers, top sources, etc.

ls endpoints
# api_active_visitors.pipe        api_top_browsers.pipe           api_top_os.pipe
# api_kpis.pipe                   api_top_devices.pipe            api_top_pages.pipe
# api_post_visitor_counts.pipe    api_top_locations.pipe          api_top_sources.pipe

You can test the response from any of these APIs locally:

tb endpoint url api_top_browsers --language curl
# curl -X GET "http://localhost:7181/v0/pipes/api_top_browsers.json?site_uuid=mock_site_uuid&timezone=Etc%2FUTC&skip=0&limit=50&token=p.eyJ1Ijo..."

So that's the Tinybird setup. You have data in the database and APIs that query it. Now for the fun part: building an AI agent that can turn a natural language question into helpful insights about our blog traffic.

Creating an analytics agent with Vercel AI SDK and Tinybird MCP Server

You can quickly build a simple analytical CLI agent with the Vercel AI SDK and Tinybird MCP Server. That agent will be able to analyze the blog traffic data stored in Tinybird and answer questions using the set of tools provided by the Tinybird MCP Server.

Setting up the agent basics

We're going to make this as simple as possible: A single Node.js CLI script that will accept a user input via the terminal and print out a response, maintaining a chat history in the context window for multi-message functionality. You'll need to have a recent version of Node installed.

You can start by creating a file, cli.js (full gist here), and importing + installing the packages you'll need:

import { streamText, experimental_createMCPClient as createMCPClient } from 'ai';
import { anthropic } from '@ai-sdk/anthropic';
import { StreamableHTTPClientTransport } from '@modelcontextprotocol/sdk/client/streamableHttp.js';
import readline from 'readline';
import * as dotenv from 'dotenv';

dotenv.config();

Here's what we're importing:

streamText function from the AI SDK to enabling streaming output from the LLM
experimental_createmCPClient from the AI SDK to create a client for the Tinybird MCP Server
StreamableHTTPClientTransport support from the MCP project, since the Tinybird MCP Server uses StreamableHTTP transport
readline to enable the CLI interface
dotenv to handle environment variables

Important! Defining a system prompt

The most important task when creating an analytics agent (or any agent) is prompt engineering. The tooling around agent development is so solid now that 90% of the work is writing a good prompt.

Here's the simple system prompt I gave my agent.

const SYSTEM_PROMPT = `You are a Ghost blog analytics assistant. Your job is the answer the user's questions to help them understand the performance of their Ghost blog, and to provide recommendations about how to improve their blog performance. Use available Tinybird tools to answer questions about page views, visitors, and site data. When querying data, use a default time range of the last 24 hours unless the user specific requests a different time range. Be concise in your responses. Today's date is ${new Date().toLocaleDateString()}.`

This works, but there's plenty of room for improvement. Anthropic's prompt engineering docs are a great reference, specifically for Anthropic models but also for LLMs in general.

Set up the CLI interface

We're using readline to enable a simple CLI chat interface:

const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});

const chatHistory = [];

We're also maintaining the chat history so the agent has full context. Keep in mind this is a naive approach with zero summarization or compaction. There are plenty of resources out their on how to minimize token usage as chat history grows, including retaining only recent messages and summarizing old messages (with another agent), using a vector DB + RAG approach to recall important context, etc. For now, we'll just maintain the full history and eat the tokens :).

Creating an MCP client for the Tinybird MCP Server

Next, we need to instantiate an MCP Client to connect to the Tinybird MCP Server:

let mcpClient = null;

async function initializeMCP() {
    // Check to make sure the Tinybird token is available
    if (!process.env.TINYBIRD_TOKEN) {
        console.error('TINYBIRD_TOKEN environment variable not found');
        console.log('Get your token with `tb info ls` and `tb token copy`');
        process.exit(1);
    }

    try {
        // Tinybird remote MCP server URL with token for scoped permissions
        const url = new URL(`https://mcp.tinybird.co?token=${process.env.TINYBIRD_TOKEN}`);

        // Create the client with a unique session ID
        mcpClient = await createMCPClient({
            transport: new StreamableHTTPClientTransport(url, {
                sessionId: `ghost-cli-${Date.now()}`,
            }),
        });

        console.log('✅ Connected to Tinybird MCP server');

        // Fetch and list the available tools from the MCP Server
        const tools = await mcpClient.tools();
        console.log('\nAvailable tools:');
        Object.keys(tools).forEach(tool => {
            console.log(`  • ${tool}`);
        });
        return tools;

    } catch (error) {
        console.error('❌ Failed to connect to Tinybird MCP server:', error.message);
        process.exit(1);
    }
}

A couple notes about this:

The Tinybird MCP Server requires a Tinybird token for authentication. The token limits the scope of the MCP Server so that it can only access data allowed by the permissions granted that token. This can be really useful when you need to add security policies to your agentic interfaces, and you can even use custom JWTs with row-level policies in multi-tenant environments, for example, where specific users may need to only see data that match their user_id in a shared data source.
The Tinybird MCP Server includes a list of core tools, like explore_data, text_to_sql, and list_datasources. In addition, every published API endpoint in the Tinybird workspace is enabled as a tool. This can be very useful for agents when you want more deterministic results for certain questions. For example, since I have a api_top_browsers API endpoint in my Ghost project, I would an agent to respond to a question like What are the top 3 browsers used to access my blog in the last 7 days? by making a tool call to that API, rather than attempting its own SQL generation. This allows for much more efficient and deterministic analysis for common query patterns. Every Tinybird API Endpoint includes a plaintext description, which can assist the agent with tool discovery and selection. Write good descriptions!

More info about the Tinybird MCP Server, available tools, token scopes, and observability can be found in the Tinybird MCP Server docs.

Creating the question/answer CLI interface

Now let's create this CLI chat interface! We'll start with a bit of housekeeping: If the user types exit we should exit the chat:

async function askQuestion(question, mcpTools) {
    if (question.toLowerCase().trim() === 'exit') {
        console.log('👋 Goodbye!');
        process.exit(0);
    }

(we will also add Ctrl+C handling in the main CLI function).

Then, we add the question to the chatHistory and use the streamText AI SDK function to get a response:

    try {
        chatHistory.push({ role: 'user', content: question });

        const result = await streamText({
            model: anthropic("claude-3-7-sonnet-20250219"),
            messages: chatHistory,
            maxSteps: 5,
            tools: { ...mcpTools },
            system: SYSTEM_PROMPT,
        });

        let fullResponse = '';
        console.log('\n💭 Thinking...');

The streamText output is a text stream (result) broken up into pieces, that we'll call delta. These deltas can have several different types - like text, tool call, tool result, etc. - that we should handle for a graceful chat output:

        for await (const delta of result.fullStream) {
            try {
                switch (delta.type) {
                    case 'tool-call':
                        if (delta.toolName) {
                            console.log(`\n🔧 ${delta.toolName}`);
                            if (delta.args && Object.keys(delta.args).length > 0) {
                                console.log(`   args: ${JSON.stringify(delta.args)}`);
                            }
                        }
                        break;

                    case 'tool-result':
                        break;

                    case 'text-delta':
                        if (delta.textDelta) {
                            process.stdout.write(delta.textDelta);
                            fullResponse += delta.textDelta;
                        }
                        break;

                    case 'finish':
                        console.log('\n');
                        break;

                    default:
                        break;
                }
            } catch (err) {
                console.error(`⚠️  Error: ${err.message}`);
            }
        }

        chatHistory.push({ role: 'assistant', content: fullResponse });

        console.log('\n' + '─'.repeat(50));

    } catch (error) {
        console.error('\n❌ Error:', error.message);
    }
}

We're doing some very basic handling here. If the delta is just text, we write the text to stdout. If it's a tool call, we show which tool is being called and any arguments passed to the tool. If it's a tool result, we show nothing. Why? Because tool results are a bit of JSON gobbldygook and require some finesse to parse and look pretty. To me, that felt like a waste of time for this demo. But feel free to play around with parsing the delta.result object yourself :).

Note when the agent finishes we push its response to the chat history to maintain the full context.

Starting the CLI interface

Now we just need a main function to start the CLI, initialize the MCP server, and open the input for questions:

async function startCLI() {
    console.log('👻 Ghost Blog Analytics CLI Agent');
    console.log('Type "exit" to quit.\n');
    console.log('Connecting to Tinybird MCP Server...\n');

    const tbTools = await initializeMCP();

    const prompt = () => {
        rl.question('\n💭 Question: ', async (input) => {
            if (input.trim()) {
                await askQuestion(input.trim(), tbTools);
            }
            console.log('');
            prompt();
        });
    };

    prompt();
}

// Handle Ctrl+C
process.on('SIGINT', () => {
    console.log('\n👋 Goodbye!');
    process.exit(0);
});

startCLI().catch(console.error);

The self-calling prompt arrow function creates a continuous prompt loop until a user types "exit" or Ctrl+C. At each interation, the input awaits the result of the askQuestion function, displaying the agent output and starting all over again.

Let's chat with our blog data

Running the agent is as simple as:

node cli.js

Of course, running an analytics agent on a blog pageviews events table containing 31 rows is kind of boring. In theory you could use tb --cloud mock command to generate some mock data, but this may not properly populate the nested JSON in the payload column in such a way that the API endpoints we already have return meaninful data. No problem! I vibe coded a simple data generator that you can use to generate some "realistic" Ghost blog traffic in a way that matches the default Ghost data schema.

Just run the following in your terminal:

node generate-blog-data.js 100000 # outputs an ndjson with 100k rows of "realistic" blog traffic data
tb --cloud datasource append analytics_events name_of_the_generated_ndjson_file.ndjson

You can verify the data was appended:

tb --cloud sql 'select count() from analytics_events'
# Running against Tinybird Cloud: Workspace ghost
#   count()  
#    UInt64  
# ───────────
#    100031

With some more data to analyze, you can fire up the agent! Here's an example of the agent helping me identify the top performing blog post and making content recommendations:

Conclusion

I've showed you how to create a simple analytics agent using the Vercel AI SDK and the Tinybird MCP Server. In this specific example, we used it to analyze blog traffic data from our Ghost blog, but the concept here can easily be applied to any use case or any kind of data. The only thing that changes is the data you have stored in Tinybird, the token you use to authenticate to the Tinybird MCP Server, and the system prompt. Everything else stays the same.

To get started with Tinybird, sign up for a free account. The Tinybird MCP Server is enabled for all workspaces by default, so you can begin testing this agentic workflow for free.

Why LLMs struggle with analytics

Cameron Archer — Tue, 29 Jul 2025 20:59:56 +0000

Everyone's talking about AI agents that can analyze your data and answer business questions. The vision is compelling: just ask "Why did revenue drop last week?" and get an intelligent, contextual answer.

But here's the reality: LLMs are surprisingly bad at working with analytical data. It's not just a "big data" problem; they struggle even to figure out what they're being asked and where to look in your database. If you're building agentic analytics experiences, you're going to hit those walls fast.

Some of the problems marketers don't talk about

LLMs struggle with tabular data

Every LLM was trained on billions of words, learning to predict what comes next in a sentence. But analytical data doesn't follow narrative patterns. A 50-column table isn't a story, it's a multidimensional relationship map where correlations matter more than sequence.

Show an LLM a comma-separated table detailing sales performance, and it will try to parse it like prose; likely missing the relationships that drive meaning and valuable inference.

SQL generation is hit-or-miss

Despite all the hype, LLMs rarely write perfect SQL on the first try. We've seen this in our own LLM SQL benchmark, but don't take our word for it. Stanford research figured out early that even top performing language models struggle significantly with complex SQL generation, especially for joins and database-specific syntax.

Long running analytical queries kill the agentic experience

In analytics, you're querying massive tables. A poorly written query can run for minutes or consume enormous resources. That's not just expensive, it's a frustrating user experience. Users expect agents to respond in seconds, not after a coffee break.

The context window exhaustion problem

If your prompts (or the tools you ask the LLM to use) aren't well designed, the amount of data returned can easily exhaust context windows. Ask for "top customers by revenue" and you might get back 10,000 rows. LLMs can't process that volume effectively, and users certainly can't consume it.

Your data is too complex

Databases, data lakes, and data warehouses, especially in large organizations, can be huge and messy. We've seen organizations with hundreds of tables and schemas with hundreds of columns.

Modern analytics involves materialized views, SQL API endpoints, parameterized query lambdas, and complex data models. LLMs trained on generic SQL examples won't understand your specific architecture. They'll guess and they'll be wrong (but confidently).

What we've learned while building the Tinybird MCP Server

We were among the first to implement an MCP server for our analytics database, literally two days after Anthropic announced the protocol. And recently, we released the remote, hosted Tinybird MCP server for all Tinybird users, which turns a Tinybird workspaces into a set of tools LLMs can reason over.

Since then, we've also built and fine-tuned the Explorations UI, which uses MCP under the hood to solve (or sidestep) many of the problems we mentioned above.

After building thousands of analytical queries with LLMs, here's what we've learned works:

Context is King

To analyze data and answer business questions accurately, without hallucinating, LLMs need context:

Static context that helps LLMs understand your resources
Dynamic and semantic context that helps LLMs understand your data

Together, these help LLMs map user intent to your data and produce analytical queries that explain your business.

Giving an LLM a schema is fine when you have two or three well-named tables with a few columns and a simple use case. But it scales badly when you expose the LLM to your entire business and all of its data.

These are, however, some techniques that help LLMs understand your resources.

Document everything as context for the LLM

Materialized views, API endpoints, even the parameters those endpoints accept, should be described in a way that LLMs understand: with text, bullet points, structure (e.g. XML) tags, etc. An overarching set of instructions for each group of resources also helps.

You must instruct the LLM to use those descriptions as context.

When we designed Tinybird pipes and data sources, we added the ability to include a description.

At the time, it was a small QoL improvement for human readers. Now, as agentic use cases surge, it's one of the most important bits of code you can write in Tinybird. Goodbye one-liners, hello comprehensive explanations.

DESCRIPTION >
    The `orders` table contains raw transactional data representing customer purchases. 
    Each row corresponds to a single order line item and includes metadata about 
    the product, customer, geography, and revenue.

    This table powers all revenue-related analytics across:
    - **Time**: via `timestamp`
    - **Geography**: via `country`
    - **Product dimensions**: via `product_id` and `category`
    - **Customer behavior**: via `customer_id`

    ### Column semantics and behaviors:

    - `order_id`: Unique identifier for each order line. Not necessarily globally unique across customers.
    - `timestamp`: The time when the order occurred. Used for time-series analysis and trend detection.
    - `customer_id`: Internal identifier for the customer. High-cardinality. Used to group behavior or analyze cohorts.
    - `country`: ISO 2-letter country code (e.g., "US", "DE", "ES"). Used for geographic breakdowns.
    - `product_id`: Unique identifier for the product SKU. High-cardinality. Can be linked to a product catalog if available.
    - `category`: Mid-cardinality grouping of products (e.g., "Electronics", "Apparel", "Books"). Good for segmentation and drilldowns.
    - `quantity`: Number of items purchased in the order.
    - `unit_price`: Price per item at the time of purchase.
    - `revenue`: Total amount paid for this line (`unit_price * quantity`). Used for sales aggregation.

    ### Relationships and usage patterns:

    - Each order line is tied to a single product and customer.
    - Common aggregations include: total revenue, order count, avg order value (AOV), revenue per country/category.
    - LLMs should use `timestamp` for any time filtering (e.g., "last week").
    - `revenue` is the key metric for trend analysis, anomaly detection, and business health checks.
    - Can be grouped by `country` or `category` for higher-level insights.
    - Expected to be used with rolling windows, week/month aggregates, and MoM/YoY comparisons.

    ### Notes for LLMs:

    - Use `sum(revenue)` to compute total sales.
    - Use `avg(revenue)` or `sum(revenue)/countDistinct(order_id)` to compute AOV.
    - Combine `country` + `timestamp` to compare revenue across time and region.
    - To detect a drop in revenue, compare recent time slices (e.g. week-over-week or month-over-month).


TOKEN "revenue" APPEND

SCHEMA >
    `order_id` UInt64 `json:$.order_id`,
    `timestamp` DateTime `json:$.timestamp`,
    `customer_id` UInt64 `json:$.customer_id`,
    `country` LowCardinality(String) `json:$.country`,
    `product_id` UInt64 `json:$.product_id`,
    `category` LowCardinality(String) `json:$.category`,
    `quantity` UInt32 `json:$.quantity`,
    `unit_price` Float64 `json:$.unit_price`,
    `revenue` Float64 `json:$.revenue`

ENGINE MergeTree
ENGINE_PARTITION_KEY toYYYYMM(timestamp)
ENGINE_SORTING_KEY (timestamp, country)
ENGINE_TTL timestamp + INTERVAL 12 MONTH

Provide sample data

Big data exhausts context windows. Using data subsets compacts the context while still being sufficiently thorough:

Run a small query with just a few rows to show the LLM what each table or resource returns.
Give access to pre-aggregated views that provide access to time series, unique dimension counters, and numeric ranges.

Generate semantic models

Semantic models help LLMs "understand" your data. They explain:

Facts (numeric values)
Dimensions (categories and time)
Metrics (aggregated values)

...and extract data patterns from:

Sample values
Time ranges
Numeric distributions
Cardinality and uniqueness insights
Schema metadata and data types
Example user questions and queries
Relationships between tables

They can be built dinamyically or you can use an LLM to generate them statically:

## `orders` Datasource

This data source contains revenue-generating transactions from customer purchases. Each row represents a line item in an order and includes time, location, product, and financial details. It is the core table for revenue analytics across time, geography, and product hierarchy.

---

### 1. Schema Overview

- **Engine**: `MergeTree`  
  A high-performance table optimized for time-based queries and aggregations.

- **ENGINE_PARTITION_KEY**: `toYYYYMM(timestamp)`  
  Used to segment data by month for efficient storage and access.

- **ENGINE_SORTING_KEY**: `(timestamp, country)`  
  Enables fast retrieval of time-based and region-based queries.

- **ENGINE_TTL**: `timestamp + INTERVAL 12 MONTH`  
  Retains historical data for one year.

- **Columns**:
  - `order_id` (UInt64): Unique identifier per order line. Not globally unique but stable within ingestion.
  - `timestamp` (DateTime): Timestamp of the transaction. Used for temporal filtering and partitioning.
  - `customer_id` (UInt64): Internal customer ID. High cardinality. Enables user-level and cohort analysis.
  - `country` (LowCardinality(String)): Country code (e.g., "US", "FR", "BR"). Medium cardinality.
  - `product_id` (UInt64): Unique product identifier. High cardinality.
  - `category` (LowCardinality(String)): Product grouping such as "Electronics", "Furniture", etc.
  - `quantity` (UInt32): Number of items in the order line.
  - `unit_price` (Float64): Unit price at the time of transaction.
  - `revenue` (Float64): Calculated as `quantity * unit_price`.

---

### 2. Key Columns

#### `timestamp`
- **Type**: `DateTime`
- **Format**: `YYYY-MM-DD HH:MM:SS`
- **Example Values**:
  - `"2025-06-30 14:12:08"`
  - `"2025-07-01 09:45:15"`
- **Notes**: Used for filtering by day, week, month, or rolling windows.

#### `revenue`
- **Type**: `Float64`
- **Range**: 0.00 - 10,000.00+
- **Distribution**: Right-skewed with outliers
- **Usage**: Main fact value for business reporting, KPIs, and trend analysis.

#### `country`
- **Type**: `String`
- **Examples**: `"US"`, `"DE"`, `"BR"`, `"ES"`
- **Cardinality**: ~100
- **Usage**: Geographic filtering, segmentation, and breakdowns.

#### `category`
- **Type**: `String`
- **Examples**: `"Electronics"`, `"Clothing"`, `"Books"`
- **Cardinality**: ~10–30
- **Usage**: High-level product analysis, useful for bar charts and comparisons.

---

### 3. Facts, Dimensions, and Metrics

#### Facts
- `revenue`
- `quantity`
- `unit_price`

#### Dimensions
- `timestamp` (temporal)
- `country` (geographic)
- `category` (product)
- `customer_id` (entity)

#### Metrics
- `SUM(revenue)`: Total sales
- `COUNT(DISTINCT order_id)`: Unique order lines
- `AVG(revenue)`: Average order value (AOV)
- `SUM(quantity)`: Total items sold
- `SUM(revenue) / COUNT(DISTINCT customer_id)`: Revenue per customer
- `SUM(revenue) / COUNT(*)`: Revenue per transaction line

---

### 4. Data Patterns

- **Time range**: Rolling window of 12 months. Mostly active within past 90 days.
- **Seasonality**: Weekly and monthly patterns—sales often spike on weekends or during campaigns.
- **Currency normalization**: Revenue assumed to be in a single currency (e.g., USD).
- **Country bias**: US and EU countries dominate volume.
- **Skew**: 80/20 rule: few categories or customers account for most revenue.
- **Completeness**: All fields are non-null; data is clean and consistently formatted.

---

### 5. Relationships and Join Potential

- Can be joined on `product_id` to a product catalog for metadata (not included here).
- Can be joined on `customer_id` to a customer profile table.
- Often aggregated by `category`, `country`, or `timestamp`.
- Not normalized—revenue is fully materialized for fast querying.

---

### 6. Example Questions the LLM Should Be Able to Answer

- "Why did revenue drop last week in the US?"
- "Which categories contributed most to last month's sales?"
- "What was the average order value for France in June?"
- "How does current revenue compare to the same week last year?"
- "Which customers had the highest spend in Q2?"
- "Was the revenue drop due to fewer orders or lower AOV?"
- "Did any category underperform relative to last month?"

---

### 7. Query Tips for the LLM

- Always filter by `timestamp` with explicit ranges (e.g., `>= now() - INTERVAL 7 DAY`)
- Use `country` = 'US' or any region as a filter when geographic context is needed
- Use `category` for group-by breakdowns
- Pre-aggregate when possible using MATERIALIZED views for week/month summaries
- Use ratio comparisons (`this_week/last_week`) for trend explanations

For a query like "Why did revenue drop last week in the US?", static context and semantic models help the LLM:

Select the key tables for revenue
Detect time dimensions to filter "last week"
Map "US" to a proper dimension
Find relevant facts and metrics
Understand relationships, anomalies, and trends

The difference between "Sales are down 15%" and "Sales are down 15%, but that's typical for Tuesdays, and we're actually up 23% from last month" is everything.

Build query loops that learn

Context is necessary, but not enough. LLMs also need data access and auto-fix capabilities.

Instead of hoping for perfect SQL on the first try:

Validate queries before executing them. Run LIMIT 1 queries to ensure they execute.
Pass errors back to the LLM with context. Use them in a loop so the LLM learns and retries with feedback.

Example:

data = None
attempts = 0
feedback = ""
while attempts < MAX_ATTEMPTS:
    result = llm.generate_sql(schema, prompt, feedback)
    r = requests.get(f"{TINYBIRD_API_URL}/v0/sql?q={result}")
    response = r.json()
    data = response.get("data", [])
    if data:
        break
    error = response.get("error")
    feedback += f"{error}\n"
    attempts += 1

Detailed error messages matter now more than ever.

Also: Instruct your LLM to apply SQL best practices e.g., use sorting keys for filtering, filter before joining, select only the necessary columns, etc. These details matter a lot for performance.

LLM-as-a-judge evaluators

When query generation errors are hard to validate deterministically, use a second LLM:

Detect syntax issues, invalid functions, or non-existent resources
Feed that result back to the generator LLM

This loop closes the feedback gap that would otherwise leave agents hanging.

Evaluate the following response taking into account the user prompt and the instructions provided.

Your task in to evaluate the generated response and provide feedback about it.
Only output "PASS" if the response meets all the criteria and if it won't fail when executed against Tinybird.

The output must be in the following format:

‹evaluation>PASS or FAIL</evaluation>
‹feedback>
[What is wrong with the code, and how do you fix it]
‹/feedback>

The real breakthrough

The magic happens when you stop trying to make LLMs perfect at SQL and start making them perfect at understanding your business, its data, and the data analytics infrastructure and tooling you already have in place.

When an LLM knows that your conversion_rate endpoint accepts date ranges and user segments, understands that Tuesday mornings are always slow, and can explain why marketing cares about cohort analysis, that's when you get truly intelligent analytics.

It's not about generating perfect queries.

It's about generating perfect insights.

Where we go from here

The companies that crack agentic analytics won't be the ones with the best LLMs.

They'll be the ones who bridge the gap between human questions, formed in natural language, and the nuances of the data being queried.

That means building systems that:

Understand your data as well as your analysts do
Iterate toward correct answers—not just correct syntax
Provide context, not just calculations
Scale with your complexity, not despite it

The future of analytics is about making analytical thinking accessible to everyone who needs it. Fast.

Multi-agent Mastery: Building integrated analytics features with Claude Code and Tinybird Code

Cameron Archer — Tue, 29 Jul 2025 19:09:20 +0000

We recently launched Tinybird Code, a new CLI agent with deep ClickHouse knowledge designed to help you build, deploy, manage, and optimize Tinybird projects from idea to production.

Also recently, the fine folks at Anthropic announced Claude Code's sub-agents, a new feature that allows developers to create specialized AI assistants that Claude Code can task with specific workflows.

To me, the most exciting part about the Tinybird Code release is using it within multi-agent workflows, especially as a sub-agent for analytics within larger projects or codebases.

I explored how to integrate Tinybird Code with Claude Code for just this purpose, and below is a little tutorial with some of the things that I learned doing multi-agent work to build a fun little 8-bit Atari style game with gameplay analytics.

The basic steps you'll see in this tutorial:

Configuring Tinybird Code as a sub-agent within Claude Code
Building a simple app with Claude Code
Asking Claude Code to integrate analytics into the app, using Tinybird Code as a sub-agent to build the resources locally
Testing data ingestion and API consumption using Tinybird Local
Deploying to Tinybird Cloud and testing end-to-end gameplay with live analytics APIs

This was mostly a one-shot, but I did learn some things along the way.

What I built

I decided to create a little retro-style game with real-time analytics capabilities. Nothing crazy. Here's the prompt I initially gave Claude Code:

Create a simple browser game. A user clicks a green circle and when they click it, it moves to another random location in the playing field. The user must click 10 times with the goal to finish as fast as possible, so show a timer at the top of the gameplay. Also, give it a nice 8-bit Atari look and feel.

And with just a few more prompts (more on that below), this was the final result:

LOOOOVE.

Let me show you how I got there.

Prerequisites

If you want to follow along, make sure you have the following before you start:

Claude Code installed and configured
Docker or similar container runtime for local development
Tinybird CLI installed and authenticated (tb login && tb local start)
A Tinybird workspace set up

Step 1: Configuring the Tinybird Code sub-agent

The first step was to create a specialized sub-agent for Claude Code that can handle Tinybird-specific tasks. Claude Code's new sub-agent feature allows us to delegate data engineering and analytics tasks to Tinybird Code, an agent that understands ClickHouse, SQL optimization, and Tinybird's ecosystem.

Using the agents command

In Claude Code, you can run the agents slash command:

/agents

This opens the sub-agents interface where you can create a new agent. You have two options:

Guided creation: Let Claude Code help you build the agent step by step
Manual creation: Create the agent configuration file directly

Let's go with manual creation so you can build off of what I already created.

Manually creating the agent configuration

Sub-agents are stored as Markdown files with YAML frontmatter in the .claude/agents/ directory. You can create a new file called tinybird-code.md:

Note that you can add this at the project directory level (cwd/.claude/agents) or the user level ~/.claude/agents. Since Tinybird Code's functions are pretty generic and shouldn't be project specific, I created mine at the user level:

mkdir -p ~/.claude/agents

Here's the full configuration I used:

---
name: tinybird-code
description: "Use this agent when you need to manage Tinybird-related tasks such as creating or modifying data sources, pipes, endpoints, handling workspace operations, deploy changes to Tinybird Local or Tinybird Cloud, debugging queries, or any other Tinybird development work. Examples: <example>Context: User needs to create a new Tinybird pipe for processing streaming data. user: 'I need to create a pipe that aggregates user events by hour' assistant: 'I'll use the tinybird-code agent to help you create and configure that aggregation pipe' <commentary>Since this involves Tinybird pipe creation, use the tinybird-code agent to handle the workspace operations and pipe configuration.</commentary></example> <example>Context: User is experiencing issues with a Tinybird query performance. user: 'My endpoint query is timing out, can you help optimize it?' assistant: 'Let me use the tinybird-code agent to analyze and optimize your query performance' <commentary>Query optimization is a core Tinybird task, so the tinybird-code agent should handle this.</commentary></example>"
color: green
---

You are a Tinybird expert specializing in building real-time API endpoints. You have deep expertise in Tinybird's architecture, data sources, pipes, endpoints, and the tb CLI tool.

When you first initialize, check for a `.tinyb` file to make sure the user is logged in to their Tinybird workspace. Also check the status of the Tinybird Local development server. If Tinybird Local isn't running, then start the Tinybird Local container.

Your primary tool is the tb CLI. You MUST use `tb -p="my_prompt_here"` for ALL Tinybird interactions, with these exceptions:

- You can use `tb local status` to see if Tinybird Local is running
- You can use `tb local start` to start the Tinybird Local container
- You can use `tb deploy` to deploy a project to Tinybird Local
- You can use `tb --cloud deploy` to deploy changes to Tinybird Cloud
- Use `tb info` to get information about Cloud and Local environments, including tokens and host URLs.
- When using `tb -p`, be as descriptive as possible so the agent can understand the context - adapt and simplify prompts rather than copying them directly
- Let tinybird-code agent handle which resources are needed to complete the task.

Always explain your reasoning and provide context for your recommendations. If you encounter ambiguous requirements, ask specific clarifying questions to ensure optimal solutions.

When a user asks to create APIs or API endpoints, make sure to be clear in your prompt to create endpoint pipes.

Understanding the configuration

The sub-agent configuration includes:

Name and description: How Claude Code identifies when to use this agent
Tools: Which CLI tools and capabilities the agent can access
System prompt: Detailed instructions on how the agent should behave
Expertise areas: Specific domains where this agent excels

Once configured, Claude Code will automatically delegate Tinybird-related tasks to this specialized agent, ensuring that data engineering decisions are made with deep ClickHouse and analytics expertise.

One note: In effect, you're creating a sort of "shim agent" between Claude Code and Tinybird Code, and directing it to exclusively use tb -p commands, which initialized Tinybird Code with a seed prompt.

I realize this isn't ideal, nor particularly efficient. In reality, it might just make sense to add some instructions to the general Claude Code prompt (claude.md) to tell it to use those tb -p commands on its own without needing a sub-agent. I haven't tested that personally, but I know it works. Still, I wanted to test Claude Code sub-agents so this is where we are!

Step 2: Building a fun retro game with Claude Code

Now let's put our multi-agent setup to work. I started by asking Claude Code to build a retro-style game. The beauty of this approach is that Claude Code will handle the game development while keeping our Tinybird Code sub-agent ready for when we need analytics capabilities.

Again, here's the initial prompt I used:

Create a simple browser game. A user clicks a green circle and when they click it, it moves to another random location in the playing field. The user must click 10 times with the goal to finish as fast as possible, so show a timer at the top of the gameplay. Also, give it a nice 8-bit Atari look and feel.

And I ended up with this basic game in one shot:

The gameplay worked and tracked my score for each game. Now let's add some long-running player stats.

Step 3: Integrating analytics with Tinybird

Here's the prompt I gave Claude Code:

Love it. Now, let's add analytics with Tinybird. You need to create a data source to store click events by user ID, and then create some API endpoints to show the user's best score (lowest game duration), average score, total games played, and then a trend chart (in the same atari style!) of their scores over time. Once Tinybird creates the resources locally, ask Tinybird to deploy them to Tinybird Local and provide the host url and token you can use to send events from the game to Tinybird (using the Events API) and fetch the API endpoints from the application.

TL;DR:

Create a data source to store click events
Create 4 APIs: best game, avg game, total games, and game score trend
Deploy to local
Get host URL and token to integrate into the application

What happened behind the scenes

When I made this request, Claude Code's sub-agent delegation kicked in:

Task Analysis: Claude Code recognized this involved Tinybird
Agent Selection: It identified that the Tinybird Code sub-agent was best suited for this task
Context Handoff: The specialized agent received the project context
Implementation: Tinybird Code designed and implemented the analytics architecture, passing back information to Claude Code on what it created.

What Tinybird Code created

The Tinybird Code sub-agent answered Claude Code's prompt beautifully, and built:

Data Sources: Optimized ClickHouse table structure for game events
Pipes: SQL transformations for meaningful analytics
Endpoints: APIs to query analytics data in real-time

Here for example, is the data source schema it developer to track game click events.

DESCRIPTION >
    Landing datasource to store click game events with user activity tracking

SCHEMA >
    `user_id` String `json:$.user_id`,
    `game_duration` Float32 `json:$.game_duration`,
    `clicks` UInt32 `json:$.clicks`,
    `timestamp` DateTime `json:$.timestamp`,
    `game_id` String `json:$.game_id`

ENGINE "MergeTree"
ENGINE_SORTING_KEY "timestamp, user_id"

You'll notice it set up JSONpaths to allow for JSON parsing from the Events API into columns, and it created a user_id column so we can filter analytics be a particular user or users.

It also created 4 endpoints. Here's the performance trend API, for example:

DESCRIPTION >
    Returns game_duration over time for a specific user_id for charting purposes

NODE score_trend_query
SQL >
    %
    SELECT
        timestamp,
        game_duration,
        game_id
    FROM click_game_events
    WHERE user_id = {{String(user_id)}}
    ORDER BY timestamp ASC

TYPE endpoint

The nice thing about Tinybird Code here is that knows how to build Tinybird resources and use proper ClickHouse SQL syntax and Tinybird resource syntax. For example, it understands exactly how to add that user_id query parameter so I can pass that in my API.

After Tinybird built and deployed locally, Claude Code was able to ask it for the host and token for Tinybird Local:

After that, Claude Code updated its HTML, CSS, and JS to incorporate these new analytics features.

Step 4: Local testing

With analytics integrated, I was able to test everything locally before deploying to production:

Troubleshooting API responses

During this phase, I encountered an issue where Claude Code didn't properly parse the Tinybird API response on one of the analytics components. It had the wrong JSON key in the JavaScript. This is where the collaborative nature of our multi-agent setup really shines. When problems arise:

Error Detection: Claude Code identifies the JavaScript parsing issue
Expert Consultation: The Tinybird sub-agent provides API-specific guidance
Quick Resolution: The code is updated to properly handle Tinybird API responses

I just told Claude Code to check the Tinybird API response, and it got the fix done in one try.

It works

And look at that, the game works, with fully integrated analytics. Every time I finish a game, Tinybird calculates my gameplay stats and serves them to the app via API.

Step 5: Deploying to Tinybird Cloud

Up to this point everything was running on my machine, but I wanted to deploy it to "production", so I asked Claude Code to deploy the analytics infrastructure to Tinybird Cloud. This step revealed an important lesson about sub-agent prompting and delegation.

The agent handoff didn't work here

When I asked Claude Code to deploy the Tinybird resources to Cloud, the process didn't go as I expected, despite what I thought as a good prompt.

My tinybird-code.md file includes this clause:

- You can use `tb --cloud deploy` to deploy changes to Tinybird Cloud

I figured this would be enough, but the sub-agent I built kept trying to use tb info instead of tb --cloud deploy perhaps because my prompt mentions it can use tb info to get information (tokens, etc.) about cloud deployments.

Lesson learned; I've updated my Tinybird sub-agent prompt for the next go-round. In the meantime, I just deployed my project to cloud manually since it's a single command with the Tinybird CLI:

tb --cloud deploy

This pushed all of my resources (data sources and pipes) to a hosted Tinybird region on GCP, and published the REST endpoints, e.g.:

curl -G https://api.us-east.tinybird.co/v0/pipes/performance_trend.json?user_id=123

I then asked Claude Code to get the new host URL and token from Tinybird, which it handled easily, updating the JavaScript to use the new auth config.

Note: This is a toy game, so I just allowed Claude Code to hard-code an app token into the JavaScript. Worst anybody can do is DDoS my endpoints or send fake data to the data source. If this were a real app, I'd properly handle authentication.

And boom: I had a fun, retro, and dare I say - addicting - "circle click" game sending gameplay events to my Tinybird production environment and fetching APIs from the same.

Improving the sub-agent

Based on this experience, I would make a few updates to the Tinybird Code sub-agent prompt to be more specific about deployment workflows:

When deploying to cloud:
1. Always use `tb --cloud deploy` for Cloud deployment
2. Verify all resources are properly created
3. Test endpoints after deployment

To be honest, I problem wouldn't use Claude Code sub-agents here, because of what I described above. I think I'd have more success simply instruction Claude Code to handle data engineering and analytics tasks by calling the tb prompts directly, rather than delegating that to a "shim" sub-agent.

Key Takeaways

This multi-agent workflow demonstrates several important concepts:

1. Specialized expertise matters

By delegating analytics tasks to a Tinybird-specialized sub-agent, we get:

Better Architecture: Optimal ClickHouse schema design
Performance Optimization: Proper sorting keys and partitioning
Best Practices: Following Tinybird and ClickHouse conventions

2. Context preservation

Each agent maintains its own context, which means:

Focused Conversations: Main Claude Code session stays high-level
Specialized Knowledge: Sub-agents dive deep into domain-specific details
Efficient Workflows: No context pollution between different task types

3. Iterative improvement

Sub-agents can be improved over time:

Learning from Failures: Update prompts based on deployment issues
Adding Tools: Grant additional CLI access as needed
Refining Instructions: Make deployment workflows more specific

4. Collaborative development

The multi-agent approach enables:

Seamless Handoffs: Tasks automatically go to the right "expert"
Comprehensive Solutions: Each agent contributes their specialty
Faster Development: Parallel expertise without context switching

Play the game (it's addicting)

After I wrote this, I updated the game to include a global leaderboard. If you exclude my colleagues who clearly cheated (looking at you filete), the best time is 4,799 ms. Think you can beat it? The game is live on a GitHub Page.

Next steps

We're all experiencing a new paradigm in software development. We can now deploy specialized AI assistants with deep domain knowledge to build end-to-end applications efficiently, without losing code quality, and by interfacing with just a single orchestrating agent.

My retro game with real-time analytics was very simple, but it demonstrates how these tools can work together - from initial development through local testing to production deployment. While I encountered some challenges, the experience helped me refine the sub-agents and improve future workflows.

Ready to try this workflow yourself? Start by trying out Tinybird Code as a standalone agent to build an analytics project:

curl https://tinybird.co | sh
tb login
tb local start
tb

You'll get a good feel for what it's capable of and how to integrate it into multi-agent workflows with Claude Code, Gemini CLI, or any other agentic CLI or IDE.

More resources:

Tinybird Code Docs
Tinybird Code Demo Video (14 min)

Have questions? Join our Slack Community.

How to build an analytics agent with Agno and Tinybird: Step-by-step

Cameron Archer — Wed, 02 Jul 2025 17:44:19 +0000

In this guide, we'll show you how to build a production-ready analytics agent using the Agno framework and Tinybird's real-time analytics platform. By the end, you'll have an agent that can answer complex data questions, investigate performance issues, and deliver its findings through multiple channels.

Why Agno and Tinybird?

Agno is a Python framework designed for building AI agents with specific performance characteristics:

Performance: Agents instantiate in approximately 3 microseconds and use 50x less memory than alternatives like LangGraph
Model flexibility: Works with 23+ LLM providers including Claude, Gemini, and OpenAI
Multi-modal support: Native support for text, images, audio, and video inputs/outputs
Built-in reasoning: First-class support for reasoning models and chain-of-thought approaches
Production features: Includes memory, storage, structured outputs, and monitoring capabilities

Tinybird provides the data infrastructure for agents with:

Query performance: Sub-second query responses at scale
MCP Server: Remote, hosted Model Context Protocol server that securely exposes workspace as AI-accessible tools
SQL compatibility: Full SQL support with advanced analytics functions
API generation: Every query becomes a documented REST API endpoint automatically. Natural language descriptions help agents discover these APIs as tools and use them to fetch data.

Together, they create a robust foundation for building analytics agents that can explore data, detect anomalies, and provide intelligent insights.

When building AI agents, you have several options for connecting to external services and data sources. For analytics use cases, it's important to differentiate between traditional text-to-sql MCP tools and API endpoint tools. Each provides tradedoffs for agent-to-data connectivity. Read more about MCPs vs APIs for agentic analytics.

Step 1: Set up a Tinybird workspace

First, let's set up your data infrastructure. Tinybird will serve as the analytics backend that your agent queries.

If you already have data in Tinybird that you want to work with, you can skip this section. If you have data in other places that you want to get into Tinybird, check out the ingest guides for more details on how to get data into Tinybird.

If you don't have data in Tinybird and want to work with an example, follow along.

Create your workspace

First, create a Tinybird workspace.

Install the Tinybird CLI:

curl https://tinybird.co | sh

Authenticate with your workspace (or create a new one):

tb login
# A browser window will open for you to login and select/create a workspace

Start the local Tinybird development server (note you'll need a Docker runtime):

 tb local start

Initialize an empty workspace:

 tb create

You're now ready to start building a data project.

Create a data source

Tinybird data sources define the schema and source connections (if applicable) for your data.

For this example, we'll create a data source to store web analytics events. Create a file called events.datasource in the datasources directory:

cd datasources && touch events.datasource

Paste the contents below into the file and save:

SCHEMA >
    `timestamp` DateTime,
    `session_id` String,
    `user_id` String,
    `event_type` String,
    `page_url` String,
    `referrer` String,
    `duration_ms` Int32,
    `country` String

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, session_id"

Deploy the data source to your local environment:

tb deploy

Generate some data

Use the Tinybird CLI's mock data generation feature to populate your data sources with realistic sample data:

tb mock events --rows 100000 --prompt "Generate timestamps within the last 30 days. All pages should have a tinybird.co domain. Use a mix of referrers including Google, X/Twitter, LinkedIn, HackerNews, ChatGPT, and Perplexity. Use pageview, click, or form_submit as event types."

The tb mock command automatically generates sample data that matches your data source schema. This is much faster than writing custom data generation scripts and ensures the data types and formats are correct. Use the --prompt flag to further refine the mock data, for example:

Realistic timestamps distributed over recent time periods
Common page URLs and referrer patterns
Geographic distribution of users
Varied session durations and page view counts

The command will generate two files in the fixtures/ folder:

A .sql file which defines the data generation'
A .ndjson file containing the produced mock data

Append the generated mock data to your data source:

tb datasource append events fixtures/events.ndjson

Create API endpoints

You now have data in Tinybird. The next step is to create a few SQL-based API endpoints that your agent can use.

Now, in theory, you could proceed without creating API endpoints and simply allow the agent to generate its own SQL queries. However, predefined API endpoints can help reduce latency and increase deterministic responses to common prompts, so let's create a couple analytical endpoints that the agent can query.

Create top_pages.pipe:

DESCRIPTION >
    'Returns the top N pages by page_view count within a specific date range'
NODE top_pages_node
SQL >
    SELECT 
        page_url,
        COUNT() as visits
    FROM events
    WHERE timestamp >= {{DateTime(date_from, '2024-01-01')}}
      AND timestamp < {{DateTime(date_to, '2024-01-02')}}
      AND event_type = 'pageview'
    GROUP BY page_url
    ORDER BY visits DESC
    LIMIT {{Int32(N, 10)}}

Note the plain text description; use this to help agents discover the APIs as tools. The more descriptive, the better.

Feel free to create additional API endpoints based on your use case. You can create them manually or use Tinybird's CLI:

tb create --prompt "Create an API endpoint to return the top pages by clicks. Include date_from and date_to parameters, as well as an N parameter which limits the number of results."

tb create --prompt "Create a materialized view to calculate session metrics based on session_id. The resulting data source should store session_id, referrer, utm parameters for the session, session duration, total pageviews, and arrays containing all pages viewed and all actions taken"

Deploy your resources

Deploy your data sources and endpoints to Tinybird local:

tb deploy

You now have an active Tinybird workspace with data and API endpoints running on your localhost.

For production workloads with larger datasets, consider reviewing the Tinybird optimization guide to ensure optimal query performance.

Step 2: Instantiate an agent with Agno

Now let's set up the Agno framework to create our analytics agent.

Install dependencies

Create a new Python project and install the required packages:

python -m venv venv
source venv/bin/activate
pip install -U agno python-dotenv

Set up a basic agent

Create your first analytics agent in analytics_agent.py:

from agno.agent import Agent
from agno.models.anthropic import Claude
from agno.models.google import Gemini
from agno.tools.mcp import MCPTools
from dotenv import load_dotenv
import os
import asyncio

load_dotenv()

async def create_analytics_agent():
    # Choose your model (Gemini for larger context, Claude for reasoning)
    model = Gemini(
        id="gemini-2.5-flash",
        vertexai=True,
        project_id=os.getenv("GOOGLE_CLOUD_PROJECT"),
        location=os.getenv("GOOGLE_CLOUD_LOCATION", "us-central1")
    )

    # Alternative: Use Claude for strong reasoning
    # model = Claude(id="claude-4-sonnet-20250514")

    agent = Agent(
        model=model,
        role="Analytics Data Analyst",
        description="""You are an expert data analyst with access to web analytics data. 
        You can explore data, identify trends, and provide actionable insights.""",
        instructions=[
            "Always provide specific metrics and timeframes in your analysis",
            "When exploring data, explain what the numbers mean for the business",
            "Include relevant context about data patterns and anomalies"
        ],
        markdown=True
    )

    return agent

async def main():
    agent = await create_analytics_agent()

    async:
        # Test the agent
        await agent.aprint_response(
            "Hi, I'm Cameron. How are you?",
            stream=True
        )

if __name__ == "__main__":
    asyncio.run(main())

Make sure to create a .env file with your API key for whatever AI model providers you're using.

This is a very basic agent without any tool access. We need to give it access to our Tinybird MCP Server so it has the tools it needs to explore the data, generate SQL queries, execute those queries, and call our published API endpoints.

Step 3: Connect to the Tinybird MCP server

The Tinybird MCP Server automatically exposes your workspace resources as tools that your agent can use.

The MCP server provides several key capabilities:

Role-based access control: Access to the MCP Server is secured with a Tinybird token, so agents can only access data within the token scopes. You can use JWTs to offer fine-grained, role-based access to the Tinybird MCP Server.
Automatic tool discovery: Your Tinybird endpoints become callable functions that agents can use to fetch pre-calculated metrics.
Data exploration: The built-in explore_data tool allows the agent to spawn a server-side subagent with understanding data and developing queries.
Text-to-SQL: Generate SQL queries with schema context.
SQL execution: Directly query the data sources.
Observability: List and query service data sources (those that contain your workspace logs) for debugging and performance monitoring.

You can learn more about available tools in the Tinybird MCP Server documentation.

Configure the Tinybird MCP Server

Let's give our agent access to the tools made available via the Tinybird MCP Server.

from agno.agent import Agent
from agno.models.anthropic import Claude
from agno.tools.mcp import MCPTools
from agno.tools.slack import SlackTools
from agno.tools.resend import ResendTools
import os
import json
from datetime import datetime

async def create_advanced_analytics_agent():
    # Tinybird MCP configuration
    tinybird_api_key = os.getenv("TINYBIRD_TOKEN")
    server_url = f"https://mcp.tinybird.co?token={tinybird_api_key}"

    mcp_tools = MCPTools(
        transport="streamable-http",
        url=server_url,
        timeout_seconds=300
    )

    # Choose your model (Gemini for larger context, Claude for reasoning)
    model = Gemini(
        id="gemini-2.5-flash",
        vertexai=True,
        project_id=os.getenv("GOOGLE_CLOUD_PROJECT"),
        location=os.getenv("GOOGLE_CLOUD_LOCATION", "us-central1")
    )

    # Alternative: Use Claude for strong reasoning
    # model = Claude(id="claude-4-sonnet-20250514")

    agent = Agent(
        model=model,
        tools=[mcp_tools],
        role="Senior Analytics Agent",
        name="DataExplorer",
        description="""You are a senior data analyst with deep expertise in web analytics, 
        user behavior analysis, and business intelligence. You have access to real-time 
        analytics data through Tinybird and can explore data to answer complex questions.""",
        instructions=[
            "Use the explore_data tool for complex analytical questions",
            "Always include timeframes and specific metrics in your analysis. Default to the last 24 hours unless a time frame is provided.", 
            "Provide business context and actionable recommendations.",
            "When detecting anomalies, investigate potential root causes.",
            "Format responses clearly with key findings highlighted."
        ],
        markdown=True,
        show_tool_calls=True,
        debug_mode=True
    )

    return agent, mcp_tools

# Usage example
async def main():
    agent, mcp_tools = await create_advanced_analytics_agent()

    async with mcp_tools:
        await agent.aprint_response(
            """Investigate any unusual traffic patterns in the last 24 hours. 
            Look for spikes in page views, unusual referrer patterns, or 
            geographic anomalies. Provide a summary with key metrics.""",
            stream=True
        )

Understand MCP tool capabilities

The Tinybird MCP Server provides several tools automatically:

explore_data: Natural language data exploration
list_endpoints: See available API endpoints
execute_query: Run direct SQL queries
[endpoint_name]: Direct access to each of your Tinybird endpoints

Example of using specific endpoint tools:

# The agent can call your endpoints directly
await agent.aprint_response(
    "Use the top_pages endpoint to show me the most popular pages today"
)

# Or use natural language exploration
await agent.aprint_response(
    "Explore user session data to find patterns in user engagement by country"
)

Step 4: Design system prompts and instructions

Effective prompt design is crucial for analytics agents (or any agent, for that matter). Here's how we recommend structuring prompts for different use cases.

Different language models have varying capabilities for SQL generation and analytical reasoning. If you're curious about which LLMs perform best for analytical SQL queries, this comparison can help inform your model selection.

Structure system prompts

Create a generalized system prompt to help the agent understand its core role:

SYSTEM_PROMPT = """
You are an expert data analyst for Tinybird metrics. You have MCP tools to get schemas, endpoints and data. The explore_data tool is an agent capable of exploring data autonomously.
"""

You can then provide further instructional prompts, or "missions", depending on the contents of your Tinybird workspace and how you want the agent to analyze your data, for example:

MISSION_PROMPT = """
You are an expert data analyst for web analytics. You have access to real-time data 
through Tinybird MCP tools including page views, user sessions, and behavioral data.

<role_definition>
Your role is to:
- Analyze web analytics data to provide actionable insights
- Investigate performance anomalies and traffic patterns  
- Monitor key business metrics and identify trends
- Provide clear, data-driven recommendations
</role_definition>

<data_access_rules>
- Use explore_data for complex analytical questions requiring data exploration
- Use specific endpoint tools when you know the exact data needed
- Always include timeframes in your analysis (default to last 24 hours if not specified)
- Provide both raw metrics and business context
</data_access_rules>

<response_formatting>
- Structure responses with clear sections: Summary, Key Findings, Metrics, Recommendations
- Use tables for comparative data
- Highlight significant changes or anomalies
- Include data confidence levels when relevant
</response_formatting>

Current date and time: {current_date}
"""

Here's how you might define an agent with a static system prompt and flexible missions:

from agno.agent import Agent
from agno.models.anthropic import Claude
from agno.tools.mcp import MCPTools
from agno.tools.slack import SlackTools
from agno.tools.resend import ResendTools
import os
import json
from datetime import datetime
from prompts import SYSTEM_PROMPT

async def create_advanced_analytics_agent():
    # Tinybird MCP configuration
    tinybird_api_key = os.getenv("TINYBIRD_TOKEN")
    server_url = f"https://mcp.tinybird.co?token={tinybird_api_key}"

    mcp_tools = MCPTools(
        transport="streamable-http",
        url=server_url,
        timeout_seconds=300
    )

    # Choose your model (Gemini for larger context, Claude for reasoning)
    model = Gemini(
        id="gemini-2.5-flash",
        vertexai=True,
        project_id=os.getenv("GOOGLE_CLOUD_PROJECT"),
        location=os.getenv("GOOGLE_CLOUD_LOCATION", "us-central1")
    )

    mission="""
    Your job is to analyze data in the Tinybird workspace to identify performance issues and make recommendations for how to fix them. Your goal is to effectively answer the user request.

    <exploration_instructions>
    - If list_service_datasources returns organization data sources, you must append "use organization service data sources" in the explore_data tool call, otherwise answer with an error message
    - You MUST include a time filter in every call to the explore_data tool if not provided by the user in the prompt
    - You MUST do one call to the explore_data tool per data source requested by the user
    - Do not ask follow up questions, do a best effort to answer the user request, if you make any assumptions, report them in the response.
    </exploration_instructions>

    <output_instructions>
    - Format output using Markdown
    - Use clearly structured headers, text formatting, and lists
    </output_instructions>
    """

    agent = Agent(
        model=model,
        tools=[mcp_tools],
        role="Senior Analytics Agent",
        name="PerformanceInvestigator",
        description=dedent(SYSTEM_PROMPT),
        instructions=mission
        markdown=True,
        show_tool_calls=True,
        debug_mode=True
    )

    return agent, mcp_tools

# Usage example
async def investigate_traffic_spike():
    agent, mcp_tools = await create_advanced_analytics_agent()

    async with mcp_tools:
        await agent.aprint_response(
            """Investigate any unusual traffic patterns in the last 24 hours. 
            Look for spikes in page views, unusual referrer patterns, or 
            geographic anomalies. Provide a summary with key metrics.""",
            stream=True
        )

Finally, user prompts and conversations direct the actions of the analytics agent:

Show me the top 5 pages by pageviews in the last 30 days

Which of those pages has the highest form submission conversion rate?

Which referrers get me the most traffic to the top converting page?

And so on. The Agno framework allows the agent to retain memory and context from a user session. It responds to each user prompt within the context of its system prompt, mission prompt, and the tools available to it.

Step 5: Configure agent output options

Your analytics agent can deliver insights through multiple channels and even trigger new agents to process its findings. Here are a few options for output with implementation examples.

CLI output

The simplest option for development and testing:

import asyncio
from agno.agent import Agent

async def cli_analytics_agent():
    agent, mcp_tools = await create_analytics_agent()

    print("🤖 Analytics Agent CLI")
    print("Ask questions about your data or type 'exit' to quit")
    print("-" * 50)

    async with mcp_tools:
        while True:
            try:
                user_input = input("\n💬 Your question: ").strip()
                if user_input.lower() == "exit":
                    print("👋 Goodbye!")
                    break

                await agent.aprint_response(
                    user_input,
                    stream=True,
                    user_id="analyst"
                )
            except KeyboardInterrupt:
                print("\n👋 Goodbye!")
                break
            except Exception as e:
                print(f"❌ Error: {e}")

# Run with: python analytics_agent.py
if __name__ == "__main__":
    asyncio.run(cli_analytics_agent())

Email reports

You can use an email provider like Resent to generate and send automated email reports:

from agno.tools.resend import ResendTools

async def email_analytics_report():
    agent = Agent(
        model=Claude(id="claude-4-sonnet-20250514"),
        tools=[
            MCPTools(transport="streamable-http", url=server_url),
            ResendTools(from_email="analytics@company.com")
        ],
        instructions=[
            "Generate comprehensive HTML email reports",
            "Include executive summary and key metrics",
            "Use tables and visual formatting"
        ]
    )

    async with agent.tools[0]:  # MCP tools
        response = await agent.arun(
            """Generate a weekly analytics report with:
            1. Traffic overview and trends
            2. Top performing content
            3. User engagement metrics
            4. Key insights and recommendations

            Send this as an HTML email to team@company.com with subject 
            'Weekly Analytics Report - [Current Date]'""",
            user_id="system"
        )

        return response

# Schedule with cron or GitHub Actions
# 0 9 * * 1 cd /path/to/agent && python -c "import asyncio; asyncio.run(email_analytics_report())"

Slack integration

You can even set up real-time notifications and interactive queries within Slack. For a more comprehensive implementation, check out how to chat with your data using a Birdwatcher Slack App:

from agno.tools.slack import SlackTools

async def slack_analytics_bot():
    agent = Agent(
        model=Claude(id="claude-4-sonnet-20250514"),
        tools=[
            MCPTools(transport="streamable-http", url=server_url),
            SlackTools(token=os.getenv("SLACK_BOT_TOKEN"))
        ],
        instructions=[
            "Respond in Slack-friendly format with clear sections",
            "Use code blocks for data tables",
            "Include relevant metrics in thread responses",
            "Tag relevant team members for critical alerts"
        ]
    )

    # Alert example
    await agent.arun(
        """Check for any traffic anomalies in the last hour.
        If significant issues are found, send an alert to #analytics-alerts 
        channel with details and suggested actions."""
    )

    # Interactive query handling
    await agent.arun(
        """A user in #general asked: 'How is our mobile traffic trending this week?'
        Analyze mobile traffic patterns and respond in the thread with insights."""
    )

# Webhook handler for Slack events
async def handle_slack_message(event):
    if "analytics" in event.get("text", "").lower():
        await slack_analytics_bot()

Multi-agent workflows

For complex analytics scenarios or when you want other agents to take action on the insights found by your analytics agent, orchestrate multiple specialized agents:

from agno.agent import Agent

async def multi_agent_analysis():
    # Data Explorer Agent
    explorer = Agent(
        model=model,
        tools=[mcp_tools],
        role="Data Explorer",
        instructions=["Focus on data discovery and pattern identification"]
    )

    # Insights Analyst Agent  
    analyst = Agent(
        model=model,
        tools=[mcp_tools],
        role="Insights Analyst",
        instructions=["Focus on business interpretation and recommendations"]
    )

    # Report Writer Agent
    writer = Agent(
        model=model,
        tools=[slack_tools, email_tools],
        role="Report Writer", 
        instructions=["Create clear, actionable reports for stakeholders"]
    )

    async with mcp_tools:
        # Step 1: Explore data
        exploration = await explorer.arun(
            "Identify interesting patterns in user behavior this week"
        )

        # Step 2: Analyze insights
        analysis = await analyst.arun(
            f"Based on this data exploration: {exploration.content}, "
            "what are the key business insights and recommendations?"
        )

        # Step 3: Create and distribute report
        await writer.arun(
            f"Create a summary report based on this analysis: {analysis.content}. "
            "Send to #analytics channel and email to stakeholders@company.com"
        )

# Schedule for comprehensive weekly analysis

Step 6: Add advanced features and production considerations

Manage memory and context

Implement persistent memory for better contextual understanding:

from agno.memory.v2.db.postgres import PostgresMemoryDb
from agno.memory.v2.memory import Memory
from agno.storage.postgres import PostgresStorage

async def create_persistent_agent():
    # Configure memory
    memory = Memory(
        model=Claude(id="claude-4-sonnet-20250514"),
        db=PostgresMemoryDb(
            table_name="analytics_memories",
            db_url=os.getenv("DATABASE_URL")
        )
    )

    # Configure storage for session history
    storage = PostgresStorage(
        table_name="analytics_sessions",
        db_url=os.getenv("DATABASE_URL")
    )

    agent = Agent(
        model=model,
        tools=[mcp_tools],
        memory=memory,
        storage=storage,
        enable_agentic_memory=True,
        add_history_to_messages=True,
        num_history_runs=10,
        instructions=[
            "Remember insights from previous analyses",
            "Build context over time about data patterns",
            "Reference historical findings when relevant"
        ]
    )

    return agent

Handle errors and build resilience

Implement robust error handling:

import logging
from tenacity import retry, stop_after_attempt, wait_exponential

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@retry(
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=4, max=10)
)
async def resilient_query(agent, query, user_id="system"):
    try:
        async with agent.tools[0]:  # MCP tools context
            response = await agent.arun(query, user_id=user_id)
            logger.info(f"Query successful: {query[:50]}...")
            return response
    except Exception as e:
        logger.error(f"Query failed: {query[:50]}... - Error: {e}")
        raise

async def safe_analytics_run(query):
    try:
        agent, mcp_tools = await create_analytics_agent()
        return await resilient_query(agent, query)
    except Exception as e:
        logger.error(f"Analytics run failed completely: {e}")
        return {"error": str(e), "success": False}

Deploy to production

You can easily deploy your Tinybird resources to a production cloud environment:

tb --cloud deploy

Deploy your agent using Docker and container orchestration:

# Dockerfile
FROM python:3.11-slim

WORKDIR /app

COPY requirements.txt .
RUN pip install -r requirements.txt

COPY . .

CMD ["python", "analytics_agent.py"]

# docker-compose.yml
version: '3.8'
services:
  analytics-agent:
    build: .
    environment:
      - TINYBIRD_TOKEN=${TINYBIRD_TOKEN}
      - SLACK_BOT_TOKEN=${SLACK_BOT_TOKEN}
      - DATABASE_URL=${DATABASE_URL}
    restart: unless-stopped

  postgres:
    image: postgres:15
    environment:
      - POSTGRES_DB=analytics_agent
      - POSTGRES_USER=agent
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Conclusion

Analytics agents like those you can build with Agno and Tinybird can change how organizations and their customers interact with data. By combining Agno's performance characteristics and tooling with Tinybird's high-performance data infrastructure and AI tooling, you can create intelligent systems that not only answer questions quickly but also proactively monitor, investigate, and report on your data.

Key advantages of this approach:

Performance metrics: Agno's 3-microsecond instantiation time combined with Tinybird's sub-second queries
Natural language interface: Eliminates the need for complex SQL queries or dashboard navigation
Proactive monitoring: Agents that detect and investigate anomalies automatically
Multiple delivery channels: Insights delivered via CLI, email, Slack, or APIs
Production scalability: Both platforms designed for production workloads with measurable performance characteristics

What makes Tinybird essential for analytics agents

Query performance: Sub-second response times for analytical queries
MCP integration: Seamless tool discovery and automatic API generation
SQL compatibility: Full analytical capabilities without compromise
Developer workflow: From prototype to production deployment
Transparent pricing: Pay-per-compute model with clear cost structure

As AI agents become central to data operations, having performant data infrastructure becomes crucial. Tinybird provides the foundation that makes your agents not just intelligent, but measurably fast, reliable, and scalable.

For more inspiration on data-driven applications you can build, explore examples like building a Datadog alternative, creating natural language filters for analytics dashboards, or using LLMs to generate user-defined data visualizations.

Start building

Sign up for Tinybird at tinybird.co and start building agents that transform how your team works with data. With Tinybird's free tier, you can prototype and deploy your first analytics agent within an hour.

Need inspiration, check out Tinybird's AI agent templates - including one built with Agno - in the Tinybird AI repository.

The future of data analysis is conversational, proactive, and intelligent. Start building it today.

IoT monitoring with Kafka and Tinybird

Cameron Archer — Tue, 20 May 2025 15:18:52 +0000

Building analytics for IoT devices requires both streaming capabilities and a developer-friendly analytics backend. Traditional solutions offer one or the other, usually making it hard to work with streams the way you'd work with a database. However, in this example, I'll show you how you can combine an Apache Kafka-compatible streaming service with Tinybird, a real-time analytics backend, to build real-time data APIs that you can integrate into your app.

Why Kafka and Tinybird?

Kafka is the world's most popular data streaming infrastructure; it excels at handling high-throughput event streaming from thousands of IoT devices, providing reliable message delivery with persistence. Tinybird complements this by transforming IoT data streams into real-time APIs with sub-second response times. Together, they form an end-to-end solution:

Kafka handles the ingestion and buffering of IoT sensor meter readings
Tinybird processes the data stream, stores the history, and exposes the results as API endpoints
Your application or service can present insights through standard REST API calls

What makes this combination powerful for product engineers is the ability to work with streaming data, persist it in the Tinybird database, and create APIs to consume it involving just two pieces of software. Depending on your approach to Kafka, you can do this in an entirely serverless fashion, without worrying about any complex infrastructure setup.

The water meters challenge

Water utility companies use smart meters to keep track of and maintain their processing and distribution infrastructure. They must handle thousands of real-time readings to detect leaks, monitor and predict consumption, and alert customers. There's an internal analytics angle to this; they need dashboards + alerts for maintenance crews and managers. Increasingly, though, utility companies are adding customer-facing features: outage alerts, real-time consumption monitoring, etc. A traditional real-time analytics stack requires juggling multiple tools and environments, which not only slows down feature delivery but also results in slow, brittle data pipelines.

The Kafka + Tinybird stack achieves real-time insights published via high-availability APIs with minimal infrastructure maintenance and zero externally managed technical handoffs (as you'll see below).

In this post, I'll share how to build a real-time analytics pipeline that consumes IoT water meter data from a Kafka topic and exposes relevant customer-facing information through scalable API endpoints. If you follow along, you'll learn how to create a system that can:

Consume and store the events from Kafka regardless of throughput
Build real-time analytics endpoints to fetch the latest readings and today's average from a specific meter
Deploy the entire pipeline, from local development environment to production in a scalable cloud

In the next post, I'll set up a proper code repository with CI/CD, and show you how to evolve the data project and APIs to handle changing requirements. Specifically, I'll add some extra metadata from S3 and build more API endpoints. Thanks to CI/CD, I'll be sure we don't break production. But that's for later. Let's focus on the first build:

The measurements and the expected results

Each water meter sends messages containing flow rate and temperature readings:

{
  "meter_id": 887876,
  "timestamp": "2025-05-13 09:45:43",
  "flow_rate": 6.665,
  "temperature": 17.08
}

I want to build an API with an expected call and response like this:

GET /api/v0/water_metrics/latest?meter_id=887876&measurement=flow_rate

{
  "meter_id": 887876,
  "measurement": "flow_rate",
  "latest_value": 6.665,
  "today_avg": 7.32
}

Getting Tinybird Local ready

One major benefit of using Tinybird here is its Docker container, Tinybird Local. You can run Tinybird's analytics backend locally to validate the build without cloud/external dependencies, then deploy to a cloud production environment once you've tested things end to end.

To start Tinybird Local:

curl https://tinybird.co | sh && tb local start

Follow the quickstart if you're not familiar with Tinybird CLI.

Setting Up the Kafka connection and the data source

To connect Kafka to Tinybird, you need a connection to a Kafka cluster and data source that will read from the topic and store the messages. Tinybird has a Kafka connector that simplifies this:

tb datasource create --kafka

The tb datasource create command prompts a wizard that guides you through the process, but we're here for the detailed explanation. To capture data from a Kafka topic, you need a connection to the Kafka cluster (.connection) and a place (.datasource in Tinybird) to consume the messages from a specific topic and write them into a database table:

You define the connection details (bootstrap server and port, api key, secret) in the generated .connection file.

TYPE kafka
KAFKA_BOOTSTRAP_SERVERS {{ tb_secret("KAFKA_SERVERS", "localhost:9092") }}
KAFKA_SECURITY_PROTOCOL SASL_SSL
KAFKA_SASL_MECHANISM PLAIN
KAFKA_KEY {{ tb_secret("KAFKA_KEY", "key") }}
KAFKA_SECRET {{ tb_secret("KAFKA_SECRET", "secret") }}

You can use the tb secret function to get the credentials from the environment variables, which allows you to point your Kafka connection at a different Kafka server in your local and prod environments.

On Tinybird Local, secrets will automatically get set to the default provided in the connection file, but if you need to change them:

tb secret set KAFKA_SERVERS <broker:port>
tb secret set KAFKA_KEY <key>
tb secret set KAFKA_SECRET <secret>

Remember that secrets are applied by default to the workspace in Tinybird Local. For production, you need to use the --cloud flag. But we're developing for now, so local is fine.

The default .datasource schema generated will ingest the whole message payload from Kafka into a single column.

SCHEMA >
    `data` String `json:$`

KAFKA_CONNECTION_NAME my_kafka_conn
KAFKA_TOPIC water_metrics_demo
KAFKA_GROUP_ID {{ tb_secret("KAFKA_GROUP_ID", "water_meters_group") }}

You can keep this pattern and use materialized views with JSONExtract functions to parse the JSON object into another data source on ingestion, or go ahead and breakout the payload into columns in the landing data source.

To do the latter, you can define column names, data types, and JSONPaths so that each field gets stored in its own column.

SCHEMA >
    `meter_id` Int32 `json:$.meter_id`,
    `timestamp` DateTime `json:$.timestamp`,
    `flow_rate` Float32 `json:$.flow_rate`,
    `temperature` Float32 `json:$.temperature`

KAFKA_CONNECTION_NAME my_kafka_conn
KAFKA_TOPIC water_metrics_demo
KAFKA_GROUP_ID {{ tb_secret("KAFKA_GROUP_ID", "water_meters_group") }}
KAFKA_AUTO_OFFSET_RESET earliest

Check out that last setting in the schema. If you want to ingest the data that is already in the topic, you set the KAFKA_AUTO_OFFSET_RESET setting to earliest. Otherwise, the data source will only store messages consumed after the data source is deployed (which may be desirable depending on your use case).

Note also that KAFKA_GROUP_ID is added as a secret. When prototyping, you often tend to destroy/recreate the data source, which requires a new consumer ID (more info here) to avoid clashes in the consumer offsets. You can use secrets and append $(date +%s) to have a different consumer group ID each time you deploy.

tb secret set KAFKA_GROUP_ID consumer-group-id-local-$(date +%s)

You can verify that project is ready to deploy with tb deploy --check.

tb deploy --check

If the check passes, you can deploy the project to Tinybird Local:

tb deploy

You can use tb open to open a browser and see in Tinybird's UI the connection and the data source, which should already have some data flowing in from the Kafka topic.

Creating the measurements API endpoint

Tinybird's tb create command will help you generate any resource. Let's use it to build an API config from a natural language prompt:

tb create --prompt "an endpoint that accepts sensor id and measurement as params, and returns latest value and today's average of the selected measurement"

This should create something valid, but just in case the LLM gets a bit too creative, here's a valid version of meter_measurements.pipe that I tested:

DESCRIPTION >
    Returns the latest value and today's average for a specified sensor and measurement

NODE measurements_node
SQL >
    %
    {% if measurement != 'temperature' and measurement != 'flow_rate' %}
        {{ error('measurement query param must be either temperature or flow_rate') }}
    {% end %}
    SELECT
        meter_id,
        {{String(measurement)}} as measurement,
        {% if String(measurement) == 'temperature' %}
            argMax(temperature, timestamp) as latest_value,
            round(avg(temperature), 2) as today_avg
        {% else %}
            argMax(flow_rate, timestamp) as latest_value,
            round(avg(flow_rate), 2) as today_avg
        {% end %}
    FROM kafka_water_meters
    WHERE meter_id = {{Int32(sensor_id)}}
      AND toDate(timestamp) = today()
    GROUP BY meter_id

TYPE endpoint

Notice a few things:

The use of templating functions to define logic (API response errors, if/else, etc.)
The definition of query parameters

Deploy and test the endpoint locally

To add new resources to your Tinybird Local workspace, you just create a new deployment with tb deploy.

To fetch the API, we need to supply a valid sensor id, so lets get one from the table:

tb sql "select meter_id from kafka_water_meters limit 1"
-- 601546

Now we can test the endpoint. The default Tinybird localhost port is 7181, and I recommend storing the admin token in the TB_LOCAL_TOKEN environment variable:

tb token copy "admin local_testing@tinybird.co" && TB_LOCAL_TOKEN=$(pbpaste)

Calling the endpoint with the sensor id we got from the query...

curl -X GET "http://localhost:7181/v0/pipes/meter_measurements.json" \
   -H "Authorization: Bearer $TB_LOCAL_TOKEN" \
   -H "Content-Type: application/json" \
   -d '{"sensor_id": 601546, "measurement": "temperature"}'

...we get:

(...)
"data":
  [
    {
      "meter_id": 601546,
      "measurement": "temperature",
      "latest_value": 22.35,
      "today_avg": 22.35
    }
  ],
(...)

And if we don't pass a valid measurement...

curl -X GET "http://localhost:7181/v0/pipes/meter_measurements.json" \
   -H "Authorization: Bearer $TB_LOCAL_TOKEN" \
   -H "Content-Type: application/json" \
   -d '{"sensor_id": 601546, "measurement": "whatever"}'

...it will return an error:

{"error": "measurement query param must be either temperature or flow_rate", "documentation": "https://docs.tinybird.co/query/query-parameters.html"}%

A brief note on security/tokens: Here I am testing, so I used a static admin token, but for production, I recommend using a signed JWT, which can easily be generated with auth services like Clerk or Auth0.

YOLO mode

At this point I have a working local project, so I can just deploy it to cloud (remember to set the secrets!) and start monitoring the sensors.

tb --cloud secret set KAFKA_SERVERS <broker:port>
tb --cloud secret set KAFKA_KEY <key>
tb --cloud secret set KAFKA_SECRET <secret>
tb --cloud secret set KAFKA_GROUP_ID consumer-group-id-prod

tb --cloud deploy

I call this "YOLO mode" using tb --cloud deploy directly from the CLI, but for real projects in production that you will need to iterate over time, I recommend committing everything to git (the benefit of all resources being defined as code!) instead of deploying directly, so the repo is a source of truth. Then you can use proper CI/CD pipelines to handle building, testing, and then deploying. Basically, just following software engineering best practices is a nice thing to do, so let's not rush to prod. First, let's create some tests!

Proper tests

Manual checks are fine while developing, but you want to add automated tests to your CI/CD to ensure you will not break your project on future iterations.

tb test generates API tests and tb test run runs them. Tinybird believes that tests should not wait, so tests will be generated on the existing endpoints against the test data in the /fixtures folder. This means tb test does not need a connection to the Kafka topic, and hence is suitable to be run in isolation as part of the CI/CD process. To make sure you have good tests, generate some data for testing:

Generating synthetic data for tests

I want to keep it simple, so just 4 rows and one sensor id.

tb mock kafka_water_meters --prompt "examples with meter_id = 1 and temperature between 1 and 30 deg " --rows 4

This command generates a .sql file that runs against the database in Tinybird Local to generate and .ndjson with the mock data:

SELECT
    1 AS meter_id,
    now() - rand() % 86400 AS timestamp,
    rand() % 100 / 10 AS flow_rate,
    1 + rand() % 29 AS temperature
FROM numbers(4)

{"meter_id": 1, "timestamp": "2025-05-15 13:36:52", "flow_rate": 7.2, "temperature": 14}
{"meter_id": 1, "timestamp": "2025-05-15 21:05:50", "flow_rate": 3.4, "temperature": 11}
{"meter_id": 1, "timestamp": "2025-05-16 01:31:33", "flow_rate": 9.1, "temperature": 2}
{"meter_id": 1, "timestamp": "2025-05-16 02:00:43", "flow_rate": 4.1, "temperature": 19}

Creating the tests

To create a test, you just run tb test create pipe_name, and you get a YAML with some valid tests for your API. You can also optionally --prompt your tests to give more guidance on what gets generated.

tb test create meter_measurements

- name: temperature_sensor_1
  description: Test temperature measurement for sensor ID 1
  parameters: measurement=temperature&sensor_id=1
  expected_result: |
    {"meter_id":1,"measurement":"temperature","latest_value":19,"today_avg":10.5}

- name: flow_rate_sensor_2
  description: Test flow rate measurement for sensor ID 2
  parameters: measurement=flow_rate&sensor_id=2
  expected_result: ''

- name: invalid_measurement
  description: Test error handling with invalid measurement parameter
  parameters: measurement=pressure&sensor_id=1
  expected_result: measurement query param must be either temperature or flow_rate
  expected_http_status: 400

I won't edit them here, but you can of course adjust yours manually, and update their expected result with tb test update.

To run the tests:

tb test run

» Building project
✓ Done!

» Running tests
* meter_measurements.yaml
✓ temperature_sensor_1 passed
✓ flow_rate_sensor_2 passed
✓ invalid_measurement passed

✓ 1/1 passed

All passed, so that's a great place to leave it here for the next post, where we'll setup CI/CD, GitHub Actions secrets to pass the Kafka credentials to the workspace in Tinybird Cloud, and evolve the project adding more sources of data and more functionality.

Conclusion

I've shown you how to build a system that processes IoT data streams and exposes them through real-time APIs. The architecture is simple:

Kafka handles the data streaming
Tinybird turns the streams into queryable API endpoints
Everything is testable and version controlled, from local to production

The main benefit? No need to juggle multiple tools or maintain complex processing pipelines. Just stream your data and query it.

In the next post, I'll add CI/CD pipelines and evolve the project with more data sources. Meanwhile, if you want to start building something with Tinybird, you can sign up for free here.

Build a Voice Call Sentiment Analysis API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:15:40 +0000

In this tutorial, you'll learn how to build an API that performs sentiment analysis on voice call transcripts. This API is particularly useful for monitoring call center performance, analyzing customer sentiment, agent effectiveness, and identifying trends over time. By leveraging Tinybird, a data analytics backend designed for software developers, you can implement this solution efficiently without worrying about the underlying infrastructure. Tinybird facilitates the creation of real-time analytics APIs by providing data sources for storing your data and pipes to transform data and expose it through Endpoints. This API will help you analyze voice call transcripts through simple keyword matching to categorize calls into positive, negative, or neutral sentiments. Additionally, you'll be able to evaluate agent performance and aggregate sentiment over time. Tinybird's data sources and pipes are central to this process, enabling real-time data ingestion, transformation, and API publication. Let's dive into the technical steps required to implement this voice call sentiment analysis API.

Understanding the data

Imagine your data looks like this:

{"call_id": "call_5389", "transcript": "Thank you for your assistance today. You have been very helpful in resolving my issue.", "customer_id": "cust_389", "agent_id": "agent_89", "duration_seconds": 569, "timestamp": "2025-04-14 03:32:28", "call_type": "billing", "caller_phone": "+1396805389", "tags": ["unsatisfied", "unsatisfied"]}
{"call_id": "call_8518", "transcript": "Hello, I need help with my account. I cannot access my services.", "customer_id": "cust_3518", "agent_id": "agent_18", "duration_seconds": 558, "timestamp": "2025-05-02 22:53:39", "call_type": "technical", "caller_phone": "+1385388518", "tags": []}

This data represents voice call transcripts along with metadata such as customer and agent IDs, call duration, timestamp, call type, caller's phone number, and tags indicating the call's nature. To store this data in Tinybird, you first need to create a data source. Here's how you define a data source for the voice call transcripts:

DESCRIPTION >
    Stores voice call transcripts with metadata for sentiment analysis

SCHEMA >
    `call_id` String `json:$.call_id`,
    `transcript` String `json:$.transcript`,
    `customer_id` String `json:$.customer_id`,
    `agent_id` String `json:$.agent_id`,
    `duration_seconds` Int32 `json:$.duration_seconds`,
    `timestamp` DateTime `json:$.timestamp`,
    `call_type` String `json:$.call_type`,
    `caller_phone` String `json:$.caller_phone`,
    `tags` Array(String) `json:$.tags[:]`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "call_id, timestamp"

The schema is designed to optimize query performance with an appropriate sorting key and partitioning strategy. For data ingestion, Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This real-time nature and low latency make it ideal for live data feeds:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=voice_call_transcripts&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
  -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
  -d '{
    "call_id": "call_12345",
    "transcript": "Customer: I am really happy with your service. Agent: Thank you for your feedback!",
    "customer_id": "cust_789",
    "agent_id": "agent_456",
    "duration_seconds": 180,
    "timestamp": "2023-06-15 14:30:00",
    "call_type": "support",
    "caller_phone": "+1234567890",
    "tags": ["support", "billing"]
  }'

Other ingestion methods include using the Kafka connector for event/streaming data, which benefits from Kafka's robustness and scalability, and the Data Sources API or S3 connector for batch/file data.

Transforming data and publishing APIs

Tinybird's pipes enable batch and real-time data transformations. They also help create API endpoints to expose transformed data.

Sentiment Analysis Endpoint

Here's the complete code for the sentiment analysis endpoint pipe:

DESCRIPTION >
    Analyzes voice call transcripts for sentiment using simple keyword matching

NODE analyze_sentiment_node
SQL >
    %
    SELECT 
        call_id,
        transcript,
        customer_id,
        agent_id,
        timestamp,
        multiIf(
            position(lowerUTF8(transcript), 'happy') > 0 OR 
            position(lowerUTF8(transcript), 'great') > 0 OR 
            position(lowerUTF8(transcript), 'excellent') > 0 OR 
            position(lowerUTF8(transcript), 'thank you') > 0 OR 
            position(lowerUTF8(transcript), 'appreciate') > 0, 'positive',

            position(lowerUTF8(transcript), 'unhappy') > 0 OR 
            position(lowerUTF8(transcript), 'angry') > 0 OR 
            position(lowerUTF8(transcript), 'terrible') > 0 OR 
            position(lowerUTF8(transcript), 'upset') > 0 OR 
            position(lowerUTF8(transcript), 'frustrated') > 0, 'negative',

            'neutral'
        ) AS sentiment,
        duration_seconds
    FROM voice_call_transcripts
    WHERE 1=1
    {% if defined(call_id) %}
        AND call_id = {{String(call_id, '')}}
    {% end %}
    {% if defined(customer_id) %}
        AND customer_id = {{String(customer_id, '')}}
    {% end %}
    {% if defined(agent_id) %}
        AND agent_id = {{String(agent_id, '')}}
    {% end %}
    {% if defined(start_date) %}
        AND timestamp >= {{DateTime(start_date, '2023-01-01 00:00:00')}}
    {% end %}
    {% if defined(end_date) %}
        AND timestamp <= {{DateTime(end_date, '2023-12-31 23:59:59')}}
    {% end %}
    LIMIT {{Int32(limit, 100)}}

TYPE endpoint

This SQL logic matches keywords in transcripts to classify calls into sentiments. Query parameters make this API flexible, allowing for filtering by call ID, customer ID, agent ID, and date range. Example API call:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/analyze_sentiment.json?token=%24TB_ADMIN_TOKEN&call_id=call_12345&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

Similar logic is applied to the agent_performance and sentiment_summary endpoints, which provide insights into agent performance and sentiment trends, respectively.

Deploying to production

Deploy your project to Tinybird Cloud using the Tinybird CLI:

tb --cloud deploy

This command creates production-ready, scalable API endpoints. Tinybird treats all resources as code, which means you can integrate this deployment process into your CI/CD pipeline for seamless updates. For securing your APIs, Tinybird uses token-based authentication. Here's how to call a deployed endpoint:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/analyze_sentiment.json?token=%24TB_ADMIN_TOKEN&call_id=call_12345&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

Conclusion

Throughout this tutorial, you've learned how to build an API for analyzing sentiment in voice call transcripts using Tinybird. By creating data sources, transforming data with pipes, and deploying to production, you've implemented a solution that can significantly enhance call center analytics. The technical benefits of using Tinybird for this use case include real-time data processing, scalability, and the ability to manage resources as code, which is crucial for agile development practices. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes. Tinybird is free to start, with no time limit and no credit card required.

Build a Real-Time Survey Response Analytics API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:15:06 +0000

In the realm of data analytics, swiftly processing and analyzing survey data can provide crucial insights into user feedback and trends. Developers often face the challenge of creating scalable, real-time APIs to handle this data efficiently. This tutorial will guide you through building a real-time API for survey response analytics using Tinybird. Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflows, git-based deployments, resource definitions as code, and features for AI-native developers. By leveraging Tinybird's data sources and pipes, you can ingest survey data, transform it, and expose it through flexible, scalable APIs.

Understanding the data

Imagine your data looks like this:

{
  "response_id": "resp_1353",
  "survey_id": "surv_53",
  "user_id": "user_1353",
  "timestamp": "2025-05-09 17:22:22",
  "questions": ["question_1", "question_2"],
  "answers": ["answer_1", "answer_2"],
  "rating": 4,
  "feedback": "Very satisfied with the product!",
  "tags": []
}

This dataset represents individual responses to surveys, capturing details like the survey and user IDs, the timestamp of the response, questions asked, given answers, a numerical rating, textual feedback, and any associated tags. To store this data in Tinybird, you first create a data source with a schema matching the JSON structure of your survey responses. Here's how the .datasource file for survey_responses might look:

{
  "DESCRIPTION": "Raw survey responses data",
  "SCHEMA": [
    {"name": "response_id", "type": "String", "json_path": "$.response_id"},
    {"name": "survey_id", "type": "String", "json_path": "$.survey_id"},
    {"name": "user_id", "type": "String", "json_path": "$.user_id"},
    {"name": "timestamp", "type": "DateTime", "json_path": "$.timestamp"},
    {"name": "questions", "type": "Array(String)", "json_path": "$.questions[:]"},
    {"name": "answers", "type": "Array(String)", "json_path": "$.answers[:]"},
    {"name": "rating", "type": "Int32", "json_path": "$.rating"},
    {"name": "feedback", "type": "String", "json_path": "$.feedback"},
    {"name": "tags", "type": "Array(String)", "json_path": "$.tags[:]" }
  ],
  "ENGINE": "MergeTree",
  "ENGINE_PARTITION_KEY": "toYYYYMM(timestamp)",
  "ENGINE_SORTING_KEY": "survey_id, timestamp, user_id"
}

When designing your schema, consider the types of queries you'll run. Sorting keys, for example, can significantly impact query performance, especially for time-series data.

Data Ingestion

Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This real-time nature and low latency make it ideal for ingesting survey responses as they are collected:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=survey_responses&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
  -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
  -d '{
    "response_id": "resp_123",
    "survey_id": "survey_456",
    "user_id": "user_789",
    "timestamp": "2023-06-15 14:30:00",
    "questions": ["How satisfied are you?", "Would you recommend us?"],
    "answers": ["Very satisfied", "Yes, definitely"],
    "rating": 5,
    "feedback": "Great service, very responsive team!",
    "tags": ["customer", "support", "feedback"]
  }'

For event or streaming data, consider the Kafka connector for its reliability and scalability. For batch or file data, the Data Sources API and S3 connector provide efficient bulk ingestion methods.

Transforming data and publishing APIs

With the data ingested into Tinybird, the next step is to transform this data and publish APIs using pipes. Pipes in Tinybird allow for both batch and real-time data transformations, as well as the creation of API Endpoints.

Endpoint: get_survey_stats

SELECT
    survey_id,
    count() AS total_responses,
    avg(rating) AS avg_rating,
    min(rating) AS min_rating,
    max(rating) AS max_rating,
    countIf(rating >= 4) AS positive_responses,
    countIf(rating <= 2) AS negative_responses
FROM survey_responses
WHERE 1=1
{% if defined(survey_id) %}
AND survey_id = {{String(survey_id, '')}}
{% end %}
{% if defined(from_date) %}
AND timestamp >= {{DateTime(from_date, '2023-01-01 00:00:00')}}
{% end %}
{% if defined(to_date) %}
AND timestamp <= {{DateTime(to_date, '2023-12-31 23:59:59')}}
{% end %}
GROUP BY survey_id
ORDER BY total_responses DESC

TYPE endpoint

This pipe aggregates survey statistics, providing a flexible API for fetching metrics like average ratings and response counts. Query parameters make the API adaptable to various client needs.

Deploying to production

Deploying your Tinybird project to production is straightforward with the tb --cloud deploy command. This command packages your data sources and pipes, deploying them to Tinybird Cloud to create scalable, production-ready endpoints. Tinybird manages resources as code, facilitating integration with CI/CD pipelines for automated deployments. Secure your APIs with token-based authentication to ensure only authorized users can access them. Example API call:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/get_survey_stats.json?token=%24TB_ADMIN_TOKEN&survey_id=survey_456&from_date=2023-01-01&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV 00:00:00&to_date=2023-12-31 23:59:59"

Conclusion

Throughout this tutorial, we've walked through creating a real-time API for survey response analytics using Tinybird. From ingesting data with the Events API to transforming it with pipes and deploying scalable endpoints, Tinybird provides a comprehensive platform for building real-time analytics APIs. The technical benefits include efficient data ingestion, flexible transformations, and the ability to publish and scale APIs rapidly. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes. With Tinybird's local-first development workflows and git-based deployments, you're equipped to handle real-time data analytics at scale, without the operational overhead.

Build a Multi-Tenant User Session Tracking API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:14:34 +0000

Tracking user sessions and analyzing their behavior across different tenants in a multi-tenant application can be quite a challenge. This tutorial will guide you through building a user session tracking API that captures user activity across sessions and provides Endpoints to analyze session data, view active sessions, and get detailed session information for multi-tenant applications using Tinybird. Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflow, git-based deployments, resource definitions as code, and features for AI-native developers. By leveraging Tinybird's data sources and pipes, we'll implement a scalable solution that allows us to ingest, transform, and query large volumes of session data in real-time. Let's dive into how we can achieve this.

Understanding the data

Imagine your data looks like this:

{"session_id": "sess_ffc43cbd9abd4ab7", "user_id": "user_85", "tenant_id": "tenant_85", "event_type": "pageview", "event_data": "{\"action\":\"view\",\"target\":\"home\"}", "page_url": "https://example.com/settings", "referrer": "https://example.com/home", "device_type": "tablet", "browser": "Firefox", "ip_address": "192.168.50.50", "created_at": "2025-05-07 16:41:42", "updated_at": "2025-05-07 16:41:42"}

This data represents an event or action taken by a user during their session, including metadata such as the session ID, user ID, tenant ID, event type, and various attributes related to the event. To store this data in Tinybird, we first need to create a data source:

DESCRIPTION >
    Stores user session activity data for multi-tenant applications

SCHEMA >
    `session_id` String `json:$.session_id`,
    `user_id` String `json:$.user_id`,
    `tenant_id` String `json:$.tenant_id`,
    `event_type` String `json:$.event_type`,
    `event_data` String `json:$.event_data`,
    `page_url` String `json:$.page_url`,
    `referrer` String `json:$.referrer`,
    `device_type` String `json:$.device_type`,
    `browser` String `json:$.browser`,
    `ip_address` String `json:$.ip_address`,
    `created_at` DateTime `json:$.created_at`,
    `updated_at` DateTime `json:$.updated_at`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(created_at)"
ENGINE_SORTING_KEY "tenant_id, session_id, created_at"

This .datasource file defines the schema for our user_sessions data source, specifying the column types and how we expect to ingest the data (using JSON paths). The sorting key is chosen to optimize query performance by tenant, session, and creation time. Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This real-time nature and its low latency make it ideal for tracking user sessions:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=user_sessions&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
  -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
  -d '{
    "session_id": "sess_12345",
    "user_id": "user_789",
    "tenant_id": "tenant_42",
    "event_type": "page_view",
    "event_data": "{\"page\":\"dashboard\"}",
    "page_url": "https://app.example.com/dashboard",
    "referrer": "https://app.example.com/home",
    "device_type": "desktop",
    "browser": "Chrome",
    "ip_address": "192.168.1.1",
    "created_at": "2023-07-15 14:30:00",
    "updated_at": "2023-07-15 14:30:00"
  }'

For event/streaming data, the Kafka connector can provide additional benefits by allowing you to ingest data directly from a Kafka topic. For batch/file data, the Data Sources API and S3 connector offer flexible options for bulk ingestion.

Transforming data and publishing APIs

In Tinybird, pipes are used for batch transformations (copies), real-time transformations (Materialized views), and creating API endpoints.

Active Sessions Endpoint

Consider the get_active_sessions endpoint, which returns active user sessions with filtering options by tenant, user, and time range:

DESCRIPTION >
    Get active sessions with optional filtering by tenant_id, time range, and user_id

NODE get_active_sessions_node
SQL >
    %
    SELECT 
        session_id,
        user_id,
        tenant_id,
        min(created_at) as session_start,
        max(updated_at) as last_activity,
        max(updated_at) - min(created_at) as session_duration_seconds,
        count() as event_count,
        groupArray(event_type) as events
    FROM user_sessions
    WHERE 1=1
    {% if defined(tenant_id) %}
        AND tenant_id = {{String(tenant_id, '')}}
    {% end %}
    {% if defined(user_id) %}
        AND user_id = {{String(user_id, '')}}
    {% end %}
    {% if defined(from_date) %}
        AND created_at >= {{DateTime(from_date, '2023-01-01 00:00:00')}}
    {% end %}
    {% if defined(to_date) %}
        AND created_at <= {{DateTime(to_date, '2023-12-31 23:59:59')}}
    {% end %}
    GROUP BY session_id, user_id, tenant_id
    ORDER BY last_activity DESC
    LIMIT {{Int32(limit, 100)}}

TYPE endpoint

This query aggregates session data, calculating metrics such as session duration and event count. It demonstrates the use of query parameters (tenant_id, user_id, from_date, and to_date) to filter the results. Example API call:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/[pipes?utm_source=DEV&utm_campaign=tb+create+--prompt+DEV](https://www.tinybird.co/docs/forward/work-with-data/pipes?utm_source=DEV&utm_campaign=tb+create+--prompt+DEV)/get_active_sessions.json?token=$TB_ADMIN_TOKEN&tenant_id=tenant_42&from_date=2023-07-01%2000:00:00&to_date=2023-07-31%2023:59:59&limit=50"

Similarly, the tenant_activity_summary and get_session_details endpoints provide summaries of tenant activity and detailed events for specific sessions, respectively.

Deploying to production

Deploy your project to Tinybird Cloud using:

tb --cloud deploy

This command creates production-ready, scalable API endpoints. Tinybird manages resources as code, enabling integration with CI/CD pipelines. Token-based authentication secures your APIs, ensuring that only authorized users can access them. Example API call:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/get_session_details.json?token=%24TB_ADMIN_TOKEN&session_id=sess_12345&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

Conclusion

Throughout this tutorial, you've learned how to use Tinybird to build a multi-tenant user session tracking API. We've covered how to ingest data, transform it, and publish real-time analytics APIs. Tinybird's ability to process and query large volumes of data in real-time makes it an ideal choice for developers looking to implement scalable data analytics backends. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes.

Build a Real-Time Shipment Tracking API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:14:02 +0000

Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflows, git-based deployments, resource definitions as code, and features for AI-native developers. In this tutorial, you'll learn how to leverage Tinybird to create a real-time API for tracking shipments, including insights into shipment statuses, late deliveries, and product-specific shipment summaries. By employing Tinybird's data sources and pipes, you'll be able to handle and analyze shipment data and product information efficiently, enabling you to expose this data through scalable APIs.

Understanding the data

Imagine your data looks like this:

{"shipment_id": "SHIP-12749", "product_id": "PROD-3749", "origin_location": "New York", "destination_location": "San Francisco", "quantity": 426, "shipment_timestamp": "2025-03-24 16:53:37", "estimated_delivery_timestamp": "2025-05-31 16:53:37", "actual_delivery_timestamp": "2025-06-10 16:53:37", "status": "Cancelled"}

This sample represents a shipment from New York to San Francisco that was unfortunately cancelled. You'll store this data in Tinybird data sources, which are essentially tables optimized for real-time analytics. To begin, you'll create two data sources in Tinybird: product_catalog and raw_shipments. Here's how you define the product_catalog data source:

DESCRIPTION >
    Product catalog with product details. SCHEMA >
    `product_id` String `json:$.product_id`,
    `product_name` String `json:$.product_name`,
    `category` String `json:$.category`,
    `unit_price` Float32 `json:$.unit_price`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "category"
ENGINE_SORTING_KEY "product_id"

And the raw_shipments data source:

DESCRIPTION >
    Raw shipment data ingested from a source like Kafka or S3. SCHEMA >
    `shipment_id` String `json:$.shipment_id`,
    `product_id` String `json:$.product_id`,
    `origin_location` String `json:$.origin_location`,
    `destination_location` String `json:$.destination_location`,
    `quantity` UInt32 `json:$.quantity`,
    `shipment_timestamp` DateTime `json:$.shipment_timestamp`,
    `estimated_delivery_timestamp` DateTime `json:$.estimated_delivery_timestamp`,
    `actual_delivery_timestamp` DateTime `json:$.actual_delivery_timestamp`,
    `status` String `json:$.status`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(shipment_timestamp)"
ENGINE_SORTING_KEY "shipment_timestamp, product_id, origin_location, destination_location"

These schemas highlight thoughtful design choices, such as using the MergeTree engine for efficient querying and sorting keys to optimize query performance. For data ingestion, Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This feature is crucial for real-time data processing, offering low latency. Here's how you'd send data to the raw_shipments data source:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=raw_shipments&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
     -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
     -d '{"shipment_id":"shipment456","product_id":"product123","origin_location":"New York","destination_location":"Los Angeles","quantity":10,"shipment_timestamp":"2024-01-01 10:00:00","estimated_delivery_timestamp":"2024-01-05 18:00:00","actual_delivery_timestamp":"2024-01-05 17:00:00","status":"Delivered"}'

Additionally, for event or streaming data, you might consider using the Kafka connector for its robustness, and for batch or file data, the Data Sources API and S3 connector are valuable.

Transforming data and publishing APIs

Tinybird's pipes are at the heart of data transformation and API publication. They allow for batch transformations, real-time transformations, and creating API Endpoints. Let's dive into the endpoints you'll be creating.

Late shipments

The late_shipments endpoint identifies shipments delivered past their estimated delivery date:

DESCRIPTION >
    Endpoint to get a list of late shipments. NODE late_shipments_node
SQL >
    SELECT
        shipment_id,
        product_id,
        origin_location,
        destination_location,
        estimated_delivery_timestamp,
        actual_delivery_timestamp
    FROM raw_shipments
    WHERE actual_delivery_timestamp > estimated_delivery_timestamp

TYPE endpoint

The SQL logic is straightforward: it selects shipments where the actual delivery timestamp is later than the estimated one.

Shipment status counts

Next, the shipment_status_counts endpoint:

DESCRIPTION >
    Endpoint to get the count of shipments by status. NODE shipment_status_counts_node
SQL >
    SELECT
        status,
        count() AS shipment_count
    FROM raw_shipments
    GROUP BY status

TYPE endpoint

This pipe groups shipments by their status and counts them, useful for quickly assessing the overall distribution of shipment states.

Product shipment summary

Finally, the product_shipment_summary endpoint provides detailed summaries:

DESCRIPTION >
    Endpoint to get shipment summary by product. NODE product_shipment_summary_node
SQL >
    SELECT
        rs.product_id,
        pc.product_name,
        pc.category,
        sum(rs.quantity) AS total_quantity_shipped,
        count() AS total_shipments,
        avg(rs.actual_delivery_timestamp - rs.shipment_timestamp) AS avg_delivery_time
    FROM raw_shipments rs
    JOIN product_catalog pc ON rs.product_id = pc.product_id
    WHERE pc.category = {{String(product_category, "Electronics")}}
    GROUP BY rs.product_id, pc.product_name, pc.category

TYPE endpoint

By joining the raw_shipments and product_catalog data sources, this endpoint calculates the total quantity shipped, total shipments, and average delivery time for products, optionally filtered by category.

Deploying to production

To deploy these resources to the Tinybird Cloud, use the Tinybird CLI:

tb --cloud deploy

This command prepares your data sources and pipes, making them ready for scalable, real-time access. Tinybird manages these resources as code, facilitating integration with CI/CD pipelines and ensuring your data analytics backend is production-ready. To secure your APIs, Tinybird employs token-based authentication. Here's how you might call the deployed late_shipments endpoint:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/late_shipments.json?token=%24TB_ADMIN_TOKEN&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

Conclusion

Throughout this tutorial, you've built a real-time shipment tracking API using Tinybird, covering data ingestion, transformation, and API publication. Tinybird empowers developers to handle real-time data analytics at scale, without the overhead of managing infrastructure. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes.

Build a Real-Time User Behavior Analytics API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:13:31 +0000

Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflows, git-based deployments, resource definitions as code, and features for AI-native developers. This tutorial will guide you through creating a real-time analytics API leveraging Tinybird to track and analyze user behavior on websites or applications. You'll learn how to collect user events, analyze session data, view user timelines, and track activity metrics to better understand user engagement. The API you'll build with Tinybird focuses on capturing detailed information about user interactions, such as page views, clicks, and other events. By utilizing Tinybird's data sources and pipes, we can ingest, transform, and expose this data through scalable, real-time APIs. This tutorial will take you through setting up the data sources, transforming the data, and deploying the API Endpoints.

Understanding the data

Imagine your data looks like this:

{"event_id": "ev_73902", "user_id": "user_902", "session_id": "session_3902", "event_type": "scroll", "page_url": "https://example.com/about", "referrer": "https://twitter.com", "device_type": "tablet", "browser": "Safari", "country": "Canada", "city": "Toronto", "properties": "{\"button_id\":\"btn_2\",\"page_section\":\"main\"}", "timestamp": "2025-05-11 17:49:55"}

This data represents user events, capturing interactions such as scrolls, page views, and clicks, along with the user, session identifiers, and metadata like the device type, location, and custom properties. To store this data in Tinybird, you create a data source with a schema tailored to your data:

DESCRIPTION >
    Tracks user events such as page views, clicks, and other interactions

SCHEMA >
    `event_id` String `json:$.event_id`,
    `user_id` String `json:$.user_id`,
    `session_id` String `json:$.session_id`,
    `event_type` String `json:$.event_type`,
    `page_url` String `json:$.page_url`,
    `referrer` String `json:$.referrer`,
    `device_type` String `json:$.device_type`,
    `browser` String `json:$.browser`,
    `country` String `json:$.country`,
    `city` String `json:$.city`,
    `properties` String `json:$.properties`,
    `timestamp` DateTime `json:$.timestamp`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, user_id, event_type"

The schema design choices, such as the sorting key on timestamp, user_id, event_type, directly impact query performance by optimizing data access patterns. To ingest data into this source, Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This method ensures low latency and real-time data availability:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=user_events&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
     -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
     -d '{
       "event_id": "e123456",
       "user_id": "u789012",
       "session_id": "s345678",
       "event_type": "page_view",
       ... "timestamp": "2023-06-15 14:32:45"
     }'

Additionally, for batch or file data, the Data Sources API and S3 connector are available, along with a Kafka connector for streaming data.

Transforming data and publishing APIs

Tinybird transforms data and publishes APIs through pipes. Pipes can perform batch transformations, real-time transformations, and create API endpoints. Let's start by defining an endpoint for session-level statistics:

DESCRIPTION >
    Provides session-level statistics for user behavior

NODE user_session_stats_node
SQL >
    SELECT
        session_id,
        any(user_id) as user_id,
        min(timestamp) as session_start,
        ... any(country) as country
    FROM user_events
    WHERE 
        ... GROUP BY session_id
    ORDER BY session_start DESC
    LIMIT {{Int32(limit, 100)}}

TYPE endpoint

This SQL logic aggregates event data to provide session statistics, such as session duration and event counts, filtering by parameters like user_id and time range. The query parameters make the API flexible, allowing users to retrieve specific slices of the data. Now, for a timeline of user events:

DESCRIPTION >
    Provides a chronological timeline of events for a specific user

NODE user_event_timeline_node
SQL >
    SELECT
        event_id,
        ... timestamp
    FROM user_events
    WHERE user_id = {{String(user_id, '')}}
        ... ORDER BY timestamp DESC
    LIMIT {{Int32(limit, 100)}}

TYPE endpoint

This endpoint sorts events by timestamp, offering a detailed view of a user's interaction sequence. Finally, an endpoint for user activity metrics:

DESCRIPTION >
    Provides aggregated metrics on user activity over a time period

NODE user_activity_metrics_node
SQL >
    SELECT
        toDate(timestamp) as date,
        ... round(countIf(event_type = 'click') / countIf(event_type = 'page_view'), 2) as click_through_rate
    FROM user_events
    WHERE 
        ... GROUP BY date
    ORDER BY date DESC

TYPE endpoint

Here, data is aggregated to provide daily metrics on user activity, useful for trend analysis.

Deploying to production

Deploying your project to the Tinybird Cloud is simple. Run tb --cloud deploy to create production-ready, scalable API endpoints. This command deploys all your data sources and pipes as defined in your project, ensuring your API is ready for real-world usage. Tinybird manages resources as code, facilitating integration with CI/CD pipelines and enabling a streamlined development to production workflow. For security, Tinybird uses token-based authentication:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/pipes/user_session_stats.json?token=%24TB_ADMIN_TOKEN&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

By including an authorization token, you ensure that only authorized users can access your APIs.

Conclusion

Throughout this tutorial, we've built a real-time analytics API capable of tracking and analyzing user behavior on websites or applications. We've covered setting up data sources to ingest user events, transforming this data with pipes to generate actionable endpoints, and deploying these endpoints to production with Tinybird. By using Tinybird, you've learned how to leverage real-time data processing and API publication capabilities without the overhead of managing infrastructure. This solution enables software developers to focus on creating value from their data with minimal setup. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes. Tinybird is free to start, with no time limit and no credit card required.

Build a Real-Time Customer Feedback Analysis API with Tinybird

Cameron Archer — Tue, 13 May 2025 17:13:00 +0000

In this tutorial, we'll guide you through building a real-time API to ingest, process, and analyze customer feedback. Understanding customer sentiment and product performance is crucial for businesses to make informed decisions and improve their offerings. By leveraging Tinybird, we will create an API that enables real-time tracking of customer feedback across various products, focusing on sentiment analysis, ratings, and identifying emerging trends. Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflow, git-based deployments, resource definitions as code, and features for AI-native developers. This tutorial will explore how Tinybird's data sources and pipes can be utilized to ingest raw customer feedback, transform this data, and publish Endpoints for querying this information in real-time.

Understanding the data

Imagine your data looks like this:

{"feedback_id": "fb_6577", "customer_id": "cust_577", "product_id": "prod_77", "rating": 3, "feedback_text": "Shipping was too slow", "sentiment": "negative", "tags": ["quality"], "timestamp": "2025-04-25 23:07:53"}
{"feedback_id": "fb_4020", "customer_id": "cust_20", "product_id": "prod_20", "rating": 1, "feedback_text": "Great product!", "sentiment": "neutral", "tags": [], "timestamp": "2025-04-28 19:37:10"}
... ```
{% endraw %}


This data represents individual feedback events from customers, including a unique feedback ID, customer ID, product ID, rating, feedback text, sentiment analysis results (positive, neutral, negative), associated tags, and a timestamp. To store this data, we create a Tinybird datasource named {% raw %}`feedback_events`{% endraw %} with the following schema:
{% raw %}


```json
DESCRIPTION >
    Raw customer feedback events ingested in real-time

SCHEMA >
    `feedback_id` String `json:$.feedback_id`,
    `customer_id` String `json:$.customer_id`,
    `product_id` String `json:$.product_id`,
    `rating` Int32 `json:$.rating`,
    `feedback_text` String `json:$.feedback_text`,
    `sentiment` String `json:$.sentiment`,
    `tags` Array(String) `json:$.tags[:]`,
    `timestamp` DateTime `json:$.timestamp`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, product_id, customer_id"

The schema design choices, such as sorting keys, significantly impact query performance by optimizing how data is stored and accessed. To ingest data into this datasource, we leverage Tinybird's Events API:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=feedback_events&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
     -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
     -d '{...}'

Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. It provides real-time ingestion with low latency, ideal for live customer feedback scenarios. Other ingestion methods include the Kafka connector for event/streaming data and the Data Sources API and S3 connector for batch/file data.

Transforming data and publishing APIs

Pipes in Tinybird facilitate data transformation and the publication of APIs. They support batch transformations (copies), real-time transformations (Materialized views), and creating API endpoints.

Aggregating Feedback

The get_feedback_summary endpoint aggregates feedback statistics, offering optional filtering by date range and product ID:

DESCRIPTION >
    Endpoint to get aggregated feedback statistics with optional filtering by date range

NODE get_feedback_summary_node
SQL >
    %
    SELECT 
        product_id,
        count() AS feedback_count,
        avg(rating) AS average_rating,
        countIf(sentiment = 'positive') AS positive_count,
        countIf(sentiment = 'neutral') AS neutral_count,
        countIf(sentiment = 'negative') AS negative_count
    FROM feedback_events
    ... TYPE endpoint

This SQL logic calculates the average rating and counts feedback by sentiment, demonstrating how query parameters can make the API flexible.

Identifying Trending Tags

Similarly, the get_trending_tags endpoint identifies trending tags within customer feedback:

DESCRIPTION >
    Endpoint to identify trending tags in customer feedback with optional filtering

NODE get_trending_tags_node
SQL >
    %
    WITH 
    ... ORDER BY tag_count DESC
    LIMIT {{Int(limit, 20)}}

TYPE endpoint

The SQL query extracts tags, counts them, and calculates their average rating, showcasing the use of materialized views to optimize data pipelines.

Detailed Feedback Retrieval

The get_feedback_by_product endpoint retrieves detailed feedback for a specific product:

DESCRIPTION >
    Endpoint to retrieve feedback for a specific product with optional filtering by rating and date range

NODE get_feedback_by_product_node
SQL >
    %
    SELECT 
        ... ORDER BY timestamp DESC
    LIMIT {{Int(limit, 100)}}

TYPE endpoint

This pipe uses templating logic to enable flexible filtering and sorting of feedback data, providing valuable insights into customer sentiment for specific products.

Deploying to production

To deploy these resources to Tinybird Cloud, use the command:

tb --cloud deploy

This command deploys your project, creating production-ready, scalable API endpoints. Tinybird manages resources as code, allowing for seamless integration with CI/CD pipelines. Securing these APIs involves token-based authentication, ensuring that access is controlled and data remains secure. Example API call:

curl -X GET "https://api.europe-west2.gcp.tinybird.co/v0/[pipes?utm_source=DEV&utm_campaign=tb+create+--prompt+DEV](https://www.tinybird.co/docs/forward/work-with-data/pipes?utm_source=DEV&utm_campaign=tb+create+--prompt+DEV)/get_feedback_summary.json?token=$TB_ADMIN_TOKEN&..."

Conclusion

In this tutorial, we've walked through creating a real-time API for customer feedback analysis using Tinybird. We covered how to ingest and store raw feedback data, transform it to derive meaningful metrics, and publish scalable, secure endpoints for querying this information in real-time. Tinybird simplifies the process of building and deploying real-time analytics APIs, handling infrastructure management so you can focus on delivering value from your data. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes.

Build a Real-Time Analytics API for a Fitness App with Tinybird

Cameron Archer — Tue, 13 May 2025 17:12:29 +0000

In this tutorial, we'll guide you through building a real-time analytics API for a fitness application using Tinybird. As developers, creating a backend that can handle streaming data and provide instant insights into user behavior is crucial for engaging and personalized user experiences. Whether you're tracking workout trends, app usage metrics, or individual user activity summaries, the ability to analyze and act on data in real-time is a game-changer. Tinybird is a data analytics backend for software developers. You use Tinybird to build real-time analytics APIs without needing to set up or manage the underlying infrastructure. Tinybird offers a local-first development workflows, git-based deployments, resource definitions as code, and features for AI-native developers. By leveraging Tinybird's data sources and pipes, you can ingest, transform, and serve large volumes of data through APIs with minimal latency, enabling real-time analytics at scale. In this tutorial, we will cover how to structure your data, create data sources, transform your data with pipes, and finally, deploy your APIs to production. By the end, you'll have a fully functional real-time analytics API for a fitness app.

Understanding the data

Imagine your data looks like this:

{
  "event_id": "evt_cn.?|bT&",
  "user_id": "user_775",
  "event_type": "app_open",
  "timestamp": "2025-04-27 12:44:56",
  "app_version": "1.5.5",
  "device_type": "iPhone",
  "device_os": "iPadOS",
  "session_id": "sess_~g?8 _",
  "duration_seconds": 3375,
  "activity_type": "weight_training",
  "calories_burned": 775,
  "distance_km": 2927681790,
  "heart_rate": 135,
  "location_lat": 2927719.5499,
  "location_lon": 2927559.3556
}

This data represents events generated by users interacting with a fitness app, detailing everything from the type of workout to the device used. To store this data in Tinybird, create a data source with the following .datasource file:

DESCRIPTION >
    Stores fitness app events/activities from users

SCHEMA >
    `event_id` String `json:$.event_id`,
    `user_id` String `json:$.user_id`,
    `event_type` String `json:$.event_type`,
    `timestamp` DateTime `json:$.timestamp`,
    `app_version` String `json:$.app_version`,
    `device_type` String `json:$.device_type`,
    `device_os` String `json:$.device_os`,
    `session_id` String `json:$.session_id`,
    `duration_seconds` Int32 `json:$.duration_seconds`,
    `activity_type` String `json:$.activity_type`,
    `calories_burned` Int32 `json:$.calories_burned`,
    `distance_km` Float32 `json:$.distance_km`,
    `heart_rate` Int32 `json:$.heart_rate`,
    `location_lat` Float64 `json:$.location_lat`,
    `location_lon` Float64 `json:$.location_lon`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, user_id, event_type"

This schema carefully selects column types to optimize storage and performance, using sorting keys to enhance query efficiency. For data ingestion, Tinybird's Events API allows you to stream JSON/NDJSON events from your application frontend or backend with a simple HTTP request. This real-time nature and low latency are ideal for fitness apps where timely data is crucial. Here's how you can send data:

curl -X POST "https://api.europe-west2.gcp.tinybird.co/v0/events?name=app_events&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV" \
  -H "Authorization: Bearer $TB_ADMIN_TOKEN" \
  -d '{...}'

Additionally, for event/streaming data, the Kafka connector can be beneficial for integrating with existing Kafka streams. For batch or file data, the Data Sources API and S3 connector provide flexible ingestion options. Use the Tinybird CLI for command-line interactions:

tb datasource append app_events.datasource 'app_events.ndjson'

Transforming data and publishing APIs

With your data ingested into Tinybird, the next step is to transform it and publish APIs using pipes.

Batch Transformations and Real-time Transformations

Tinybird pipes can perform both batch transformations (copies) and real-time transformations (Materialized views). When applicable, materialized views pre-aggregate data, significantly speeding up API response times.

Creating API Endpoints

For each endpoint, you will define a pipe that executes SQL queries to transform and serve the data. Here’s how you can create APIs for popular activities, app usage metrics, and user activity summary:

Popular Activities Endpoint

DESCRIPTION >
    Get most popular activities based on user engagement

NODE popular_activities_node
SQL >
    SELECT 
        activity_type,
        count(*) as activity_count,
        count(DISTINCT user_id) as user_count,
        avg(duration_seconds) as avg_duration,
        avg(calories_burned) as avg_calories,
        avg(distance_km) as avg_distance
    FROM app_events
    WHERE event_type = 'workout_completed'
    AND timestamp >= {{DateTime(start_date)}}
    AND timestamp <= {{DateTime(end_date)}}
    GROUP BY activity_type
    ORDER BY user_count DESC
    LIMIT {{Int32(limit, 10)}}

TYPE endpoint

This pipe creates an endpoint that aggregates workout data to find the most popular activities. It showcases the use of query parameters (start_date, end_date, limit) to make the API flexible.

App Usage Metrics Endpoint

DESCRIPTION >
    Get app usage metrics over a time range with optional filters

NODE app_usage_metrics_node
SQL >
    SELECT 
        toStartOfDay(timestamp) as day,
        count(DISTINCT user_id) as daily_active_users,
        count(DISTINCT session_id) as total_sessions,
        count(*) as total_events,
        avg(duration_seconds) as avg_session_duration,
        countIf(event_type = 'app_open') as app_opens,
        countIf(event_type = 'workout_completed') as workouts_completed
    FROM app_events
    WHERE device_os = {{String(device_os)}}
    AND app_version = {{String(app_version)}}
    AND timestamp BETWEEN {{DateTime(start_date, '2023-01-01 00:00:00')}} AND {{DateTime(end_date, '2023-12-31 23:59:59')}}
    GROUP BY day
    ORDER BY day

TYPE endpoint

This endpoint calculates daily app usage metrics, demonstrating filtering by device OS and app version.

User Activity Summary Endpoint

DESCRIPTION >
    Get activity summary for a specific user over a time period

NODE user_activity_summary_node
SQL >
    SELECT 
        user_id,
        countIf(event_type = 'workout_completed') as total_workouts,
        sum(duration_seconds) as total_duration_seconds,
        sum(calories_burned) as total_calories_burned,
        sum(distance_km) as total_distance_km,
        avg(heart_rate) as avg_heart_rate,
        COUNT(DISTINCT activity_type) as unique_activities
    FROM app_events
    WHERE user_id = {{String(user_id, '12345')}}
    AND timestamp BETWEEN {{DateTime(start_date, '2023-01-01 00:00:00')}} AND {{DateTime(end_date, '2023-12-31 23:59:59')}}
    GROUP BY user_id

TYPE endpoint

This API provides a comprehensive summary of a user's activities, suitable for personal dashboards or fitness tracking features.

Deploying to production

Deploying your project to production with Tinybird is as simple as running tb --cloud deploy. This command publishes your data sources and pipes to Tinybird Cloud, creating scalable, production-ready API Endpoints. Tinybird manages resources as code, integrating seamlessly into CI/CD pipelines for continuous deployment. To secure your APIs, Tinybird uses token-based authentication. Here’s how to call your deployed endpoints:

curl -X GET "https://api.tinybird.co/v0/pipes/popular_activities.json?token=%24TB_ADMIN_TOKEN&utm_source=DEV&utm_campaign=tb+create+--prompt+DEV"

Conclusion

Throughout this tutorial, you've learned how to build real-time analytics APIs for a fitness app using Tinybird. From designing data schemas and ingesting streaming data to transforming that data and deploying scalable APIs, you now have the tools to implement similar solutions in your own projects. The technical benefits of using Tinybird include efficient data management, flexible API creation, and seamless deployment workflows. Sign up for Tinybird to build and deploy your first real-time data APIs in a few minutes.