May 2025 Summaries

Supabase, a managed PostgreSQL database, is well-suited for transactional applications but faces challenges when used for real-time analytics due to its row-oriented nature. While simple analytics can be implemented directly within Supabase for smaller datasets, this approach can lead to performance issues as data volume and complexity increase. Optimizations such as indexing and partitioning can enhance performance, but they may not fully overcome the limitations for large-scale analytics. To address this, Supabase users can utilize read replicas to offload analytical queries or consider integrating with specialized analytics solutions like Tinybird. Tinybird, built on the ClickHouse database, offers significant performance advantages for analytical workloads, supporting large datasets and complex queries with low latency. It provides a user-friendly environment for developers, enabling seamless integration with Supabase through methods like PostgreSQL table functions, Change Data Capture, and direct event streaming. These approaches allow users to maintain transactional workloads in Supabase while leveraging Tinybird for high-performance analytics, offering a balanced solution for applications requiring both operational and analytical capabilities.

The text outlines the process of enhancing a real-time IoT analytics pipeline, initially built using Kafka and Tinybird, by implementing a CI/CD pipeline with GitHub Actions to achieve a production-ready analytics backend. It emphasizes the necessity of CI/CD in data projects for managing complexities such as schema changes and collaboration, ensuring automated testing and standardized deployment to avoid manual errors. The guide details setting up GitHub repository workflows, customizing CI/CD pipelines, and deploying to Tinybird Cloud, highlighting the benefits of continuous integration and deployment for maintaining and iterating on analytics APIs. Additionally, it explains setting repository secrets, handling errors, and testing new features while managing streaming data efficiently, showcasing Tinybird's capabilities in managing real-time data flow and deployments.

Combining Redpanda, Apache Iceberg, and Tinybird creates a robust architecture for real-time analytics applications by integrating event streaming with durable data storage and analytics capabilities. Redpanda serves as a modern, Kafka-compatible event streaming platform that allows efficient event sourcing and storage, while Iceberg provides a versioned, durable data lake with schema evolution capabilities. Tinybird offers seamless, high-performance analytics APIs that can process both historical data from Iceberg and real-time data from Redpanda, enabling low-latency applications. This architecture addresses the limitations of traditional analytics systems, such as the slow response times of data warehouses, the lack of historical data retention in Kafka, and the real-time limitations of data lakes, by offering a horizontally scalable, developer-friendly solution with simple local setup and cloud deployment options. The process includes setting up Redpanda and Tinybird locally, connecting them for real-time streaming, and deploying analytics APIs to production, significantly enhancing developer productivity and minimizing infrastructure overhead.

Postgres is a robust OLTP database, but its real-time query performance on large datasets is limited, which is where Tinybird excels. This guide explores how to adapt Postgres queries to run efficiently on Tinybird, drawing inspiration from Haki Benita's work on SQL for data analysis. It covers the adaptation of common table expressions, demonstrating that while Postgres CTEs are versatile, they can be substituted with Tinybird's subqueries and Pipes for similar functionality. Despite Tinybird's limitations with certain SQL functions like generate_series, equivalent operations can be performed using its unique functions such as arrayJoin and range. The guide also explains how to perform tasks like joining data, random sampling, descriptive statistics, and linear regression on Tinybird, emphasizing its specialized capabilities for handling real-time analytics on large datasets. Additionally, it highlights Tinybird's tools for managing time-series data, handling missing values, and performing binning and histograms, showcasing its optimization for scalable, real-time data analysis.

Tinybird has introduced experimental support for Apache Iceberg, a high-performance open table format for large-scale analytics datasets, in a private beta. To test its capabilities, the GitHub Archive dataset was used to analyze real-time GitHub activity, illustrating that while Apache Iceberg is effective for data warehouse-like loads, it struggles with real-time analytics due to high latency and complexity in sorting and partitioning. As a solution, a hybrid architecture was developed where Iceberg remains the source of truth, and Tinybird handles data synchronization, transformation, and serving via fast HTTP endpoints. This approach leverages copy pipes to synchronize data from Iceberg, materialized views for real-time data transformation, and endpoint pipes to expose aggregated data as APIs, enabling scalable, low-latency access. The setup demonstrates a scalable, real-time, and cost-efficient system that integrates well with developers' workflows by maintaining Iceberg as the durable source of truth while providing interactive speed and automatic API generation through Tinybird. Future plans include handling schema evolution dynamically, merging historic and real-time events, and exploring an event-sourcing architecture with Kafka and Iceberg.

Building an analytics system for IoT devices requires an integration of streaming capabilities and an analytics backend, exemplified by combining Apache Kafka and Tinybird. Kafka, known for its high-throughput event streaming capabilities, efficiently handles data from numerous IoT devices, while Tinybird transforms these streams into real-time APIs with sub-second response times. This integration allows for the creation of real-time data APIs that can be seamlessly incorporated into applications, providing a comprehensive solution for tasks such as monitoring water meters in utility companies. By utilizing this combined approach, developers can work with streaming data, persist it in the Tinybird database, and create consumable APIs without the complexity of managing multiple tools. The process involves setting up connections between Kafka and Tinybird, defining data schemas, and deploying the system locally before moving to a cloud production environment. The architecture not only simplifies data handling but also enables real-time insights via high-availability APIs, minimizing infrastructure maintenance. Moreover, the system is designed to be testable and version-controlled, ensuring robust and scalable deployments from development to production.

A recent benchmark comparing the performance of 19 large language models and one human in generating analytical SQL queries has sparked a variety of feedback from online communities. Key criticisms included the flawed scoring method that penalized models for generating correct but structurally different SQL, the low complexity of benchmark queries that did not reflect real-world analytical challenges, and the unfair penalization of certain SQL function choices that prioritized exactness over performance. The benchmark used a controlled dataset to eliminate variables, but this also simplified the task compared to real-world scenarios where schemas are often ambiguous. Suggestions for improvement included incorporating a second grading pass based on result equivalence, introducing more complex and realistic queries, and allowing for community-sourced benchmarks to simulate more challenging data environments. The benchmark emphasized efficiency, considering both execution speed and data scanned, but this was not always clearly communicated. Moving forward, the team plans to refine the benchmark by integrating community feedback, enhancing scoring methodologies, and considering real-world complexities to better evaluate the capabilities of language models in writing SQL.

Data engineering is a specialized field within the data world where engineers build and maintain the infrastructure needed for efficient and reliable data flow, focusing on tasks such as extracting, transforming, and loading (ETL/ELT) data from various sources to central systems like data warehouses. Unlike developers, data engineers handle more data than code, focusing on designing database schemas optimized for analytics, orchestrating complex data workflows with tools like Apache Airflow, and ensuring data quality and governance at scale. They increasingly adopt software engineering best practices such as version control, testing, and continuous integration/continuous deployment (CI/CD) to enhance the reliability and efficiency of data pipeline changes. Monitoring and real-time data processing are also emphasized, although they are less common in data engineering compared to software development, and the integration of observability and faster feedback loops can significantly boost productivity. Data engineering tools like Tinybird incorporate many best practices, offering solutions for real-time analytics and efficient data management.

A customer recently observed a significant reduction in their query latency, which was attributed to the automatic optimizations performed by Query Booster, a feature designed to enhance database performance without manual intervention. Query Booster works by continuously monitoring query patterns, analyzing query plans, and adjusting data source schemas to optimize sorting keys, thereby reducing the need for full table scans and enhancing efficiency. It addresses common issues associated with slow database queries, such as increased infrastructure costs, decreased user satisfaction, and wasted engineering resources, by autonomously fine-tuning database schemas based on real-time usage patterns. Through automatic optimization, it creates temporary schemas that are validated for performance improvements and removed if they become redundant. This approach allows databases to adapt dynamically to evolving query patterns, ensuring optimal performance without requiring database expertise from users, and has demonstrated substantial improvements in query times, reduced CPU usage, and eliminated the need for configuration or maintenance.

Exploratory Data Analysis (EDA) is crucial for understanding and ensuring the integrity of data before diving into complex analyses, similar to checking a deck of cards before a game. Traditionally, EDA involved labor-intensive SQL processes for examining data quality and structure, such as counting null values and checking distributions. Databricks streamlines this with its Data Profiles feature, providing an accessible dashboard that displays essential column-level statistics. Tinybird, in response to user feedback, introduced Tinybird Forward and Explorations, which replace the previous SQL-heavy Playgrounds with a chat-style prompt interface that automatically generates queries to profile data. This new feature enhances speed, visibility, and dynamic maintainability, allowing users to easily verify data quality, detect outliers, and modify queries across projects. As Tinybird continues to develop Explorations, it has the potential to include features like histograms, automatic outlier detection, and LLM-powered summaries, offering a more comprehensive data profiling tool.

In a comprehensive evaluation of 19 large language models (LLMs) alongside a human engineer, a benchmark was established to assess the models' ability to generate analytical SQL queries for a 200 million-row dataset derived from GitHub events. This benchmark, which uses 50 analytical questions inspired by ClickHouse, measures success rates, efficiency, query latency, and other performance metrics compared to human-generated queries. The dataset is stored in Tinybird, allowing for interactive performance measurement. Key findings indicate that while LLMs are competent in producing SQL queries, they often struggle with generating queries that are both accurate and efficient, particularly as prompt complexity increases. Human engineers still outperform LLMs in efficiency, especially in real-world analytics scenarios where semantic correctness and optimized resource usage are crucial. The study highlights the ongoing challenge of adapting LLMs for precise data logic, emphasizing the need for human oversight and the importance of SQL proficiency. The benchmark serves as a tool for continuous evaluation, guiding improvements in LLM-generated SQL for interactive analytics experiences, with the results publicly accessible and open to contributions for further model testing.

Explorations is a feature within Tinybird designed to streamline data analysis by providing a notebook-style interface for exploring data through tables and charts, complemented by an AI chat interface that translates natural language questions into SQL queries. This tool emerged from the need to efficiently understand data shapes and query them without the tedium of manual SQL coding, offering a conversational analytics approach and interactive visualizations. By leveraging a custom system prompt for a language model, Explorations integrates contextual data, such as schemas and data samples, to facilitate intuitive querying and analysis, akin to the MCP server but with added UI integration. The feature has been in beta and is now publicly available, enabling users to explore data effectively by simply asking questions and receiving immediate SQL-based responses, as demonstrated through code contribution analysis with data from GitLab.

Tinybird has introduced a new feature called Explorations, a conversational UI that simplifies data interaction by allowing users to ask natural-language questions instead of writing complex SQL queries. This feature translates user queries into contextualized SQL commands, displaying results as tables and charts, and aims to streamline analytics workflows by integrating traditional Tinybird interfaces into a unified system. The underlying technology includes Next.js for streaming responses, Vercel AI SDK for chat state management, and Vertex AI for model selection and fallback options. Developing this conversational interface involved overcoming challenges with large language models (LLMs) through techniques like function calling, LLM chaining, and context management. The workflow starts with an LLM acting as an orchestrator that selects and utilizes various tools to complete tasks, such as executing queries and generating visualizations, with a limit on tool invocations to ensure efficiency. The feature aims to make data exploration more accessible and less time-consuming for users, offering a practical example of its application in analyzing product usage data. Users can try Explorations through Tinybird's cloud platform or CLI, with free sign-up options available for new users.