September 2022 Summaries

Moving from batch to real-time data analysis can significantly alter business operations, as demonstrated through a collaboration between Tinybird and Galeo in the music industry. They developed a real-time data product using Kafka and Tinybird to simulate and analyze music streaming data, enabling stakeholders such as artists, record labels, and streaming platforms to access immediate insights into song performance. The system ingests and processes streaming data via a Kafka connector, transforming JSON objects into structured data, enriching it with additional song details, and aggregating it to facilitate rapid querying. API endpoints allow for detailed analytics, and a React application visualizes this data, providing artists with real-time information on their music's global performance and income. The initiative highlights the potential for real-time data tools to transform industry practices by allowing faster reaction times and more informed decision-making.

In ClickHouse, joining two tables and filtering by a column from the right-side table can be approached differently than in databases like Postgres, leading to significant performance improvements. Instead of performing a join when the result will always be filtered post-join, ClickHouse's columnar storage allows for direct filtering, which is highly efficient. By rewriting the query to utilize this feature, one can achieve faster results without creating a join table. Tinybird leverages ClickHouse to enable the creation of real-time API endpoints quickly, simplifying complex data operations, although it is currently in private beta and requires account creation for testing.

Tinybird's approach to managing analytics data projects emphasizes the importance of treating data as code, allowing it to be stored in version control systems akin to traditional software development. This methodology involves serializing resources like datasources and data transformation pipes into a simple, text-based format that is easily editable and compatible with existing tools such as Dockerfile, which many developers are familiar with. By enabling projects to be mirrored in repositories like GitHub, Tinybird facilitates the use of best practices like continuous integration, testing, and automated workflows, which are often challenging to implement in SaaS products that do not support such mirroring. This design allows for seamless integration with version control systems and code editors, providing flexibility in managing data pipelines, testing API endpoints, and deploying data workflows across different environments.

The blog post discusses using complex time-series models for anomaly detection in data, beyond simple statistical methods like z-scores. By employing Python libraries such as Prophet or statsmodels, users can predict control limits using pre-coded models without needing to build from scratch. The post provides an example using historic New York taxi trip data to forecast future data and generate alerts for anomalies. The process involves extracting time-series data, fitting a model, generating predictions, and creating a Data Source of these predictions in Tinybird. An alerts system is then set up using an API Endpoint to identify days with unexpected data, thus enabling real-time operational adjustments. This approach highlights the flexibility of integrating SQL-based analysis with advanced modeling techniques to enhance real-time data monitoring and anomaly detection.

Debugging ClickHouse® using Visual Studio Code involves building ClickHouse for debugging by adapting the build with specific flags, such as disabling jemalloc, and running ninja for the clickhouse-server and clickhouse-client, which takes less than an hour. To debug, one must install the C++ extension and gdb, create a new run configuration in the .vscode directory, and set breakpoints directly in the editor to run the code from within Visual Studio Code. After making changes to a cpp file, recompilation is straightforward, thanks to the compile_commands.json file in the build folder, which aids in calling ninja to regenerate the ClickHouse binary by recompiling only the dependencies.

Tinybird, while not a traditional log analysis tool, can effectively analyze log-like datasets such as NGINX logs by leveraging its SQL-based framework to publish queries as API endpoints and model data sources for enhanced speed. Although Tinybird only supports CSV files, its CLI tool can load space-separated files by guessing the delimiter, allowing users to transform and query data through a process called "piping." For scalability, especially with millions of daily log entries, Tinybird enables the creation of materialized views (MV) that provide faster real-time data processing by using SQL with state modifiers and an AggregatingMergeTree engine to aggregate columns efficiently. This process ensures that endpoints are significantly faster, as they utilize pre-aggregated data stored in MVs rather than recalculating with each request. The materialized views update automatically with new data, providing users with rapid and reliable real-time analytics, and can be applied to various use cases like tracking unique IP addresses by day or analyzing payload size percentiles per hour. A GitHub repository is available to help users reproduce these steps and start analyzing their logs with Tinybird.

In ClickHouse®, retrieving the type of a query, function, or aggregation is essential for debugging and issue identification, and while the toTypeName(x) function can provide this information, it requires executing the query, potentially leading to long response times and unnecessary duplicate results. A more efficient alternative is using the DESCRIBE command, which evaluates the query without execution and returns only one entry per column, resulting in significantly improved performance and a simpler solution.

Data ingestion challenges, such as missing data or improper formatting, can be cumbersome for users who prefer not to spend extensive time troubleshooting. To address this, Tinybird has developed the Data Operations Log, a feature that provides comprehensive visibility into every operation performed on data sources, including details on operation success, duration, and error specifics. This log is accessible via the UI or API, allowing users to query detailed information about data source operations, such as creations, appends, deletions, and more. By exposing the Operations Log as a service dataset, users can directly query it through the API, enabling advanced functionalities like alert systems and ad-hoc reporting tools. Additionally, users can create endpoints to visualize data ingestion metrics, such as elapsed time versus rows ingested, allowing for enhanced analysis and control over data operations. This feature aims to streamline the process of building robust analytical solutions, offering users the tools for better data management and insight generation.

Clickhouse® enhances data handling efficiency through its JOIN engines, which offer superior performance compared to regular tables when preparing data for joins. It supports the joinGet function, enabling retrieval of table values using a key, although this cannot be applied to dynamically created data. To achieve similar performance benefits with large datasets, the transform function can be used. This approach is illustrated with a practical example involving a sales table and an exchange_rate table, demonstrating how to efficiently calculate total sales in dollars per country. The text provides guidance on replicating these performance-optimized queries using provided CSV datasets and schemas.

Tinybird experienced a significant service disruption affecting a major customer due to a misconfiguration during an infrastructure update on December 19th. Despite rigorous testing in other environments, a critical oversight occurred when data path changes were not applied to the customer's production environment, leading to database queries failing as the servers used incorrect directories. The incident, which lasted 28 minutes, was resolved quickly by Tinybird's team, who immediately informed the affected customer and provided an incident report detailing the root cause and corrective measures. These measures included improving load balancer health checks and contributing a bug fix to ClickHouse®. While service disruptions are challenging and costly, Tinybird views them as opportunities for learning and system improvements.

In ClickHouse®, SELECT modifiers such as APPLY, EXCEPT, and REPLACE enable users to perform simple operations on multiple columns and modify their original values efficiently. The APPLY modifier allows functions to be applied to specified columns, while EXCEPT facilitates the selection of columns excluding those specified. REPLACE enables the application of functions to columns, replacing them with new values through alias expressions. These modifiers can be combined for more complex operations, and future releases, like ClickHouse® 21.10, promise enhancements such as incorporating lambda functions within an APPLY for greater flexibility and functionality.

ClickHouse® supports adding and subtracting time and date intervals using a syntax similar to that of other databases like Postgres, which includes the INTERVAL keyword. This feature allows users to manipulate dates with various intervals such as seconds, minutes, hours, days, weeks, months, quarters, and years without the need for parentheses, providing flexibility in constructing queries. Users can combine multiple intervals in a single query, and while the smallest supported interval is a second, there are workarounds to achieve finer precision. Furthermore, the functions now(), interval, and other interval types are case-insensitive, allowing for versatile use in both uppercase and lowercase formats.

ClickHouse® is known for its fast release cycle, introducing major updates around once a month, which allows for rapid innovation but can complicate the implementation of large features. To manage this, ClickHouse® often introduces new features as experimental, allowing them to be tested and refined before possibly becoming stable or being discarded. One such experimental feature is projections, introduced in version 21.6, which function similarly to materialized views but are automatically utilized by the database depending on the query. Projections offer efficiency by reducing disk usage significantly, though they require duplicating storage. They differ from Skipping Indexes, which are smaller and more limited but also automatically applied to queries. In comparison, materialized views are a stable feature that can be used with any table engine and allow for complex data enrichment, though they may complicate query writing and error recovery. Projections handle errors silently and are generated in the background, offering a less disruptive alternative when dealing with query failures.

A developer from Tinybird, inspired by Ed Hawkins's #ShowYourStripes graphic for global warming, created a project that visualizes the sentiment of their Twitter timeline by changing the background color and avatar expression based on sentiment analysis. By streaming tweets using Twitter's API and analyzing them with TextBlob, the developer transformed sentiment data into visual stripes on their Twitter banner, reflecting the mood over the past week. The project revealed fluctuations in sentiment, with a notable increase in negative sentiment due to recent global events, and highlighted increased polarization when switching from a chronological to a 'Top Tweets' timeline. The developer is also working on tracking emoji usage in tweets and has shared the project code on GitHub, alongside a starter kit for others to create dashboards using real-time Twitter data, leveraging Tinybird for scalable data processing.

Tinybird has introduced workspaces to enhance collaboration among developers and data engineers working on real-time analytics projects, acknowledging the common need for teamwork in large data initiatives. Users now have personal workspaces and can manage access to shared workspaces, allowing control over who can access specific Data Sources and Pipes. The user interface displays accessible workspaces, while the CLI tool prompts a workspace selector upon login. Public APIs maintain transparency as long as the appropriate Auth tokens are used. Inviting a user to a workspace grants them read/write permissions for all associated Data Sources and Pipes, with personal workspaces available for private testing. This development aims to streamline collaboration, and the company is eager to see how users will leverage this new feature.

Tinybird provides a solution for efficiently running analytical queries on large PostgreSQL databases that have grown too big for traditional methods. Instead of relying on costly and suboptimal approaches like fine-tuning indices, setting up replication, or running pre-aggregation queries, Tinybird offers the ability to run instant queries using its platform. By setting up a cron job, users can maintain an up-to-date replica of their transactions table and perform sub-second analytical queries without using PostgreSQL for the actual querying. Tinybird can handle data ingestion at high speeds, enabling users to access its UI for quick query execution or set up KPI dashboards through APIs. The platform demonstrates significant performance improvements, with queries running up to 200 times faster compared to PostgreSQL, making it a compelling option for applications struggling with large and complex data sets.

Filling gaps and resampling time series data in ClickHouse® can be efficiently handled using the WITH FILL modifier, which addresses the issue of missing rows due to periods without data, by inserting zero values to maintain constant intervals between data points. This technique works seamlessly with Date, DateTime, and numerical columns, with default steps of one second for DateTime and one day for Date columns. For users running older versions of ClickHouse®, an alternative method involves generating a complete time series for the desired interval and joining it with the original dataset to include previously missing rows. Additionally, the process can be streamlined using Tinybird, where queries can be split into nodes, allowing for more readable and manageable code, alongside the advantage of viewing intermediate subquery results in a notebook-like environment.

Tinybird, a platform focused on speed, has introduced improvements to increase visibility and management of asynchronous operations, known as jobs, which handle tasks like data ingestion and population of materialized views. These jobs are queued in a first-in, first-out (FIFO) order and may vary in processing time. Depending on the account type, the jobs queue can be isolated or shared. Recent updates include enhancements to the API, CLI, and UI that allow users to easily track job status, view details of jobs from the past 7 days, and manage their job queues effectively. Users can now answer questions about job completion and status with greater ease using these enhancements, and Tinybird encourages users to explore and provide feedback on these new features.

Tinybird recently enhanced its observability capabilities by adding new columns to its pipe_stats and pipe_stat_rt Service Data Sources and introducing a usage graph to API endpoint pages, tracking key metrics like request count and average latency. However, some customers requested more flexible observability visuals, leading to the creation of a solution that integrates Tinybird usage data into Datadog using vector.dev, a lightweight tool built by Datadog for building observability pipelines. The integration involves using Tinybird's API endpoints, vector.dev, and GitHub Actions to seamlessly route data from Tinybird to Datadog, allowing customers to track performance metrics in one place. This project demonstrates how quickly and effectively Tinybird can respond to customer needs, aligning with their product development approach of making the seemingly impossible possible, and highlights the company's commitment to improving observability.

In recent weeks, Tinybird Analytics has focused on improving the organization and usability of large applications by redesigning its User Interface (UI) for the Data Pipes screen, alongside making enhancements to its APIs. The new UI introduces a sidebar for better contextual information and easier navigation through Data Pipes and nodes, enabling users to quickly access recently used Data Sources and preview relevant data for query building. Additionally, the transformation nodes within a Data Pipe can now be collapsed for improved readability, with all node options accessible via a contextual menu. These changes have significantly enhanced the user experience for both client and internal data projects, and Tinybird Analytics encourages feedback from users, inviting those with CSVs or data analysis needs to join their waiting list for access.

Data analytics has traditionally involved lengthy processes and significant engineering resources to prototype, build, and deploy transformations, often relying on batch processing rather than real-time analysis. Tinybird addresses this challenge by offering a real-time analytics platform designed with developers in mind, enabling companies like Vercel and Keyrock to maximize their raw data for high-frequency decision-making and process automation. With the recent opening of access, Tinybird allows users to ingest and query real-time data to create scalable data products through a user-friendly interface or CLI at no initial cost. The platform supports developers with serverless APIs, version control, continuous integration, and schema management to facilitate seamless data handling. Tinybird's Build plan offers users up to 1000 requests per day per workspace for free, allowing unlimited team members and workspaces, along with guided product usage upon sign-up.

Kafka Summit 2022 is set to offer insights into the popular open-source streaming platform Kafka, especially for first-time attendees looking to understand its fundamentals. Kafka serves as an intermediary between systems, helping to avoid data loss and manage flow control by decoupling data producers and consumers, thus preventing system overloads during data surges. It also promotes data sharing across teams without conflict through its consumer group IDs, and its scalability is enhanced by simply increasing partitions and adding brokers and consumers. However, Kafka does introduce complexity and costs, as maintaining Kafka brokers can be challenging, and the platform's CLI tools and access control mechanisms can be cumbersome. The post encourages attendees to explore further resources and consider Kafka's advantages and drawbacks in the context of their specific needs, with an invitation to connect at the Summit for more personalized discussions.

Tinybird Analytics emphasizes resiliency and consistency in handling high-frequency or large-scale data updates by treating data as dynamic and ever-evolving. The platform ensures that transformation nodes connected to data sources are recalculated in real-time whenever data changes occur, maintaining seamless and immediate updates without delays. Recent enhancements to the Tinybird Analytics user interface include the ability to append or replace data in existing data sources, providing users with advanced import options and shortcuts for efficient data management. These features, along with real-time API performance, allow users to manage their data effortlessly, and Tinybird invites feedback and early access participation from companies and developers.

Tinybird, a company that extensively uses the open-source database Clickhouse, highlights the spectrum of involvement in open-source projects, from companies that control their projects like Mongo and Elastic to those contributing to community-managed efforts like the Linux kernel. While emphasizing the value of open-source software in advancing technology and benefiting society, as seen in examples like the COVID vaccine development and Wikipedia, Tinybird acknowledges its reliance on open-source technologies but expresses a desire to contribute more actively to these projects. This commitment is underscored by their decision to hire developers to work full-time on Clickhouse, aiming to enhance their understanding and improve the technology for broader benefit.

Inserting a NULL value into a LowCardinality(String) column in a database will result in it being stored as an empty string, demonstrating how the system internally handles such inputs. By using LowCardinality(Nullable(String)) as opposed to just Nullable(String), not only can one achieve the benefits associated with LowCardinality when the conditions are favorable, but it also enables ordering by that column. This illustrates the flexibility and advantages of using LowCardinality(Nullable(String)) in certain database operations.

Speed is emphasized as a crucial feature in data processing and analytics, as it not only enhances the efficiency of tasks but also fosters a sense of progress and motivation. The ability to iterate quickly reduces the cost and complexity of experimentation, leading to better insights and team motivation. Google exemplifies this by defining speed as a feature, where the immediacy of search promotes frequent usage and exploration. Tinybird adopts a similar philosophy, highlighting the importance of rapid data operations to uncover insights from large datasets. They demonstrate this with their Query API, capable of executing basic analytical operations on extensive data within milliseconds, showcasing their commitment to fast and efficient data processing.

Tinybird is expanding its leadership team and relocating its headquarters from Madrid to the United States as part of its strategic growth plan. The company has appointed Yotam Yemini as CRO and GM for the US, Marc Rosabrunetto as CFO, Felix Lopez as VP of Engineering, and Stephane Cornille as VP of Marketing to enhance its ability to deliver real-time data solutions and capitalize on the growing data-driven business environment. With a focus on accelerating revenue growth, improving engineering processes, and building a strong brand, these leaders bring extensive experience from high-growth SaaS companies and other industries. This expansion follows Tinybird's rapid growth, tripling in size since its $3 million seed round, and the company continues to seek additional talent for its offices in New York, London, and Madrid.

After migrating a client to the newest version of the Kafka connector, the support team provided insights into optimizing performance, particularly focusing on compression techniques and configuration parameters. Despite the optimizations, the client's Kafka cluster reading throughput remains a limiting factor, with significant improvements observed when using zstd compression at higher levels, achieving up to 19 million records per minute. Although higher compression levels increase CPU load, zstd maintains notable speed, demonstrated by a test on a laptop producing and compressing at millions of records per minute. The support team also noted issues with three specific partitions lagging under heavy loads, suggesting further investigation, and recommended utilizing Confluent's optimization guide for additional improvements.

DataMarket, a spin-off from WhiteBox, is Spain's largest privately operated data marketplace, providing ready-to-use, GDPR-compliant datasets across various sectors such as real estate and retail, designed to enhance internal databases, AI models, and real-time decision-making for its clients. Initially running on on-premise infrastructure, DataMarket faced challenges in scaling and maintaining data quality due to rapidly increasing data volumes generated by its web crawling core, which led to the need for cloud storage solutions and a more efficient infrastructure. The platform struggled with complex data access control and customization for clients, who often lacked the technical expertise to process data, necessitating bespoke data transformation and delivery through various formats like APIs and CSV files. To address these issues, DataMarket adopted Tinybird, a tool built on ClickHouse®, which simplified high-frequency data ingestion, user access control, and data transformation through its intuitive "Pipes" feature, allowing for seamless integration and scalability. This transition improved operational efficiency, reduced infrastructure complexity, and enhanced the overall customer experience by enabling real-time data processing and delivery, positioning DataMarket for continued growth.

Tinybird's approach to building data-intensive projects involves collaborating with data and engineering teams to discover innovative ways to develop, maintain, and scale these projects by adopting a DataOps culture. Similar to DevOps, which enhances productivity and collaboration in software development, DataOps is a set of practices and tools that fosters cooperation among data scientists, data engineers, infrastructure engineers, and developers, enabling them to work with data as if it were source code. This approach breaks down traditional silos in data teams, allowing for full autonomy, ownership, and accountability over the data product while facilitating faster iteration and delivery of high-quality solutions. By applying the principles of DataOps, teams can handle requirements, develop, deploy, and support data products efficiently with better performance metrics, latency control, and service level agreements.

Tinybird has achieved SOC 2 Type I compliance, marking a significant advancement in its mission to help developers create secure API endpoints for large volumes of analytical data. In response to demands from larger customers for enhanced authentication capabilities, Tinybird has integrated Enterprise Authentication using Auth0, allowing for Single Sign On (SSO) protocols like SAML and LDAP. While Tinybird previously offered SSO through social platforms such as Google, GitHub, and Microsoft, the switch to Auth0 provides a more comprehensive and secure authentication experience, including universal login across all Tinybird products and seamless enterprise connections. Despite these backend changes, the user interface and command-line interface (CLI) remain unchanged, ensuring a consistent user experience.

Tinybird aims to simplify the process of working with large datasets by enabling developers and data engineers to derive insights quickly and efficiently without the need for complex infrastructure setups. Many organizations struggle with democratizing data access and making data-driven decisions due to the complexity and cost of maintaining extensive data infrastructures in platforms like AWS or Google Cloud. Tinybird is developing a product that allows users to perform real-time queries on large datasets and convert them into API endpoints, facilitating ease of use and accessibility. The company is focused on gathering developer feedback through user testing from various industries to refine their product, with an emphasis on eliminating the hassle of traditional data management and empowering developers to build what they need swiftly.

Apache Kafka is widely regarded as the standard for capturing real-time event data due to its ability to separate data collection from processing, allowing users to address different use cases over time. However, challenges arise with consuming Kafka data, particularly due to the large volumes and evolving nature of data within topics. While managed solutions like Confluent can facilitate event pushing and querying with kSQL, creating a low-latency, high-concurrency data product for real-time insights remains complex. Tinybird addresses this challenge by providing tools to ingest Kafka streams into an analytical backend like ClickHouse®, enabling users to perform transformations, create materializations, and generate secure, dynamic API endpoints with ease. This integration allows for rapid development of analytical applications, greatly reducing the time and complexity traditionally required, and empowering developers to innovate and transform industries by leveraging streaming and historical data at scale.

The text discusses a method for materializing UNION ALL queries in ClickHouse® and Tinybird by creating two materialized views that write to the same table, utilizing the SimpleAggregateFunction data type. This approach, exemplified with Unsplash open data, circumvents the inability to directly materialize UNION ALL results by employing the AggregatingMergeTree engine. The process involves creating a destination table and two materialized views to store photo IDs, keywords, and collection details in a more efficient manner. The use of SimpleAggregateFunction allows for performance improvements and simpler queries compared to AggregateFunction, as it only stores the aggregate value rather than the full state, eliminating the need for -State and -Merge suffixes in queries. By structuring projects properly in Tinybird and using commands like tb push, the data can be joined and accessed rapidly, enhancing the speed of data retrieval by approximately six times compared to traditional methods.

AWS's Simple Notification Service (SNS) is a popular choice among Tinybird customers for pub/sub messaging due to its capability to distribute messages to multiple destinations, filter messages, and prevent data loss. Recognizing its importance, Tinybird has now enabled data ingestion from SNS, allowing users to connect their SNS topics to Tinybird through a straightforward process involving creating an SNS topic, defining a subscription with Tinybird's API endpoint, and publishing messages to the topic. This feature is currently available for all customers, and Tinybird is seeking feedback through their community Slack to refine the service further.

Faced with the challenge of hosting a real-time analytics API for a retail client during Black Friday, the team implemented a system using their Tinybird backend, incorporating Nginx, Varnish, and a Clickhouse cluster to manage and query large volumes of transactional data. The approach involved utilizing materialized views, splitting the main sales table into two—one for recent data and another for historical data—to efficiently handle upserts and maintain real-time capabilities. Despite some hiccups, including a minor downtime during peak traffic due to a script error, the system successfully managed to ingest over 650 billion rows and handled queries reading a total of over 12 trillion rows, achieving a median of 50 queries per second with peaks up to 300 QPS. The team optimized performance by focusing on reducing query times and byte scans, employing techniques like lightweight operations and strategic caching with Varnish, ultimately delivering an average API response time of 600ms.

Tinybird Analytics is set to showcase its capabilities at South Summit Madrid, an event renowned for facilitating connections between technology startups, investors, and corporates. The company will be featured at the Google for Startups stands, alongside other innovative startups such as IDOVEN, Rated Power, and Neuro Digital. Attendees will have the opportunity to experience Tinybird's real-time analytics, which can process billions of records and execute subsecond queries. Interested parties are encouraged to contact the company for more information.

When dealing with datasets containing null values, it's often necessary to fill these gaps with non-null values to maintain data integrity. While a constant value can be used as a substitute, a more dynamic approach involves filling nulls with the latest or next available value, a task made simple in libraries like Pandas through the fillna method, and similarly achievable in Postgres. ClickHouse® also supports this functionality using array functions, providing a detailed explanation of the process and the specific role of each array function within the subqueries used to handle such replacements.

As data accumulates rapidly in various applications, managing and extracting meaningful insights from billions of rows becomes a significant challenge, especially for real-time analytics. Traditional databases like Postgres or Mongo can handle large volumes of data but struggle with speed when processing complex queries, particularly under medium to high loads. Tinybird offers a solution by optimizing data management and query processes, leveraging Clickhouse® for statistical approximations and real-time pre-aggregations to enhance performance. By designing efficient data schemas, using pre-aggregated tables, denormalizing data, and applying smart indexing and partitioning strategies, Tinybird enables quick iteration and high query-per-second (QPS) rates with reduced latency. For instance, data rollups significantly reduce the query time by narrowing the data scope, maintaining high performance even with large datasets. Tinybird's approach allows for scalable and efficient data handling, minimizing the need for costly and inflexible pre-aggregations and ETLs, thereby supporting high-demand use cases and facilitating seamless integration into CI/CD workflows.

Tinybird has streamlined the process of creating API endpoints for large datasets, allowing users to connect their data, execute an SQL query, and generate a secure, real-time analytical API endpoint with minimal effort. The platform emphasizes ease of use and consumption, particularly for developers using these APIs, by offering a newly introduced API endpoints page. This page provides detailed statistics, usage logs, and documentation, including OpenAPI 3.0 links, which facilitate integration with tools like Postman or Swagger. It also offers code snippets in various programming languages to simplify integration into applications. The information used to build these API endpoint pages is available in OpenAPI 3.0 format, enabling users to develop their own documentation and easily manage multiple API endpoints with a single security token. Tinybird aims to improve the API development experience by making integration straightforward and efficient.

Tinybird Analytics has significantly enhanced its CSV import process, achieving nearly double the performance with the ability to ingest around 680,000 rows per second, even on the smallest paid account. In addition to these improvements, Tinybird has introduced SQL filters to its Auth Tokens, allowing users to define specific data access permissions. This feature enables the creation of Auth Tokens that provide access to only certain subsets of data based on specified criteria, such as filtering data by city or payment type without the need for backend code. This advancement allows for dynamic and secure data access within applications, as Auth Tokens can be created and modified at runtime, facilitating seamless integration with existing permission systems.

Usage-based pricing (UBP) is increasingly popular among organizations due to its direct impact on revenue, prompting many to consider whether to build their own systems or purchase existing SaaS solutions. While buying a UBP SaaS tool promises faster implementation and pre-built integrations, it may lack flexibility and result in dependency on support services. Conversely, building a custom solution offers greater control and potential alignment with a company's tech stack but requires significant resources and cross-functional collaboration. The emergence of real-time analytics platforms, such as Tinybird, presents an alternative by offering infrastructure abstractions similar to SaaS tools while allowing more control over business logic, particularly for those proficient in languages like SQL. Ultimately, the decision between building or buying hinges on a company's specific needs and its capacity to balance infrastructure management with business logic customization.

In 2021, the Ethereum blockchain experienced significant transaction volume, prompting the need for efficient data analysis methods to manage real-time analytics dashboards. The concept of rollups is introduced as a solution, allowing for incremental aggregation of transaction data to improve query performance and reduce computational load. By implementing rollups using Materialized Views in Tinybird, developers can create efficient data pipelines that handle large datasets by pre-aggregating time series metrics. This approach enables dynamic querying based on different time spans, enhancing the speed and efficiency of data presentation in dashboards. The example provided demonstrates how queries that utilize materialized rollups drastically reduce the number of rows scanned, resulting in faster query execution. The blog post also includes a visual demonstration using a UI built in Retool, illustrating how rollups can optimize dashboard performance and offering readers an opportunity to explore the methodology through a GitHub project.

In the journey of developing digital products, data management and architecture play crucial roles as organizations increasingly prioritize data aggregation and utilization. Initially, developers often choose the simplest database management systems to validate business models quickly, sometimes overlooking long-term performance and scalability. As businesses grow, challenges such as ensuring data integrity, handling database migrations, and maintaining high availability arise, often resolved by implementing transactions and constraints. The integration of third-party tools compounds these challenges, leading to potential data inconsistencies. To address analytics needs without compromising production databases, organizations can use BI tools, secondary databases, or real-time data replication. Documentation via data catalogs is vital for clarity but maintaining them is challenging. Data governance becomes crucial to prevent leaks, often from internal sources, by controlling and monitoring data access. Tinybird is positioned as a solution to facilitate real-time analytics, aiming to simplify and economize the implementation of robust data architectures for organizations.

Dustin Phillips, drummer for The Ataris, inspires a metaphor for understanding "data products" by likening them to songs, where individual musical tracks, akin to raw data sources, hold little value on their own but become valuable when combined and refined into a cohesive whole. Just as musicians record, mix, and master tracks to create a marketable song, data producers gather, clean, and transform raw data to produce a "data product" that can be accessed and used by data consumers through various channels. Productizing data, like creating a song, involves a significant initial investment but offers high returns when done well, making data discoverable and consistently usable. This approach contrasts with constantly building custom data pipelines, which can be costly and inefficient. The journey of refining data products mirrors the iterative process of musicians releasing remixes and remastered hits, emphasizing the continuous improvement needed to enhance quality, availability, and reliability.

In transactional databases, especially when ensuring data quality and compliance with regulations like GDPR, data deletion is a common requirement, and Tinybird addresses this need with a new API endpoint for selective data deletion. This feature allows developers to delete rows from a Tinybird datasource that meet specific conditions, which can be particularly useful in situations like reconciling real-time analyses or correcting errors from buggy applications. The deletion process involves submitting a POST request to the delete API endpoint, including the datasource name and a SQL expression filter, and requires an auth token with the DATASOURCES:CREATE scope to ensure data security. Despite the asynchronous nature of the operation, developers can monitor its progress and ensure that all data mutations and replicas are handled appropriately. Tinybird supports these operations by abstracting the complexities of data replication and management, enabling users to focus on their data engineering workflows and leverage real-time analytics without worrying about the underlying database intricacies.

Tinybird enables users to ingest data and run real-time analyses, providing scalable answers through analytical API endpoints that can be consumed as JSON documents or CSV files. These insights can be visualized using tools like Retool, which facilitates the creation of internal apps by leveraging REST APIs. The process for building such an app involves three steps: ingesting data into Tinybird, creating a Tinybird API endpoint, and developing a Retool app. Data can be ingested from various sources, including CSV files, remote URLs, or Kafka streams, and Tinybird's service data sources offer real-time statistics about API calls and operations. Users can create apps to monitor API endpoint performance by selecting parameters, querying endpoints, and charting results using Retool's drag-and-drop components. This approach allows for visually impactful presentation of operational data, and additional charts can be easily added to monitor other business metrics. Users are encouraged to share their creations and use cases with the Tinybird community.

After using Kafka for several months, a newcomer shares their experience, noting that while Kafka adds complexity to a tech stack, it offers significant benefits for handling high-load and complex data processing scenarios. Initially skeptical, they admit that setting up Kafka is more labor-intensive compared to simpler tools like Redis, but recognize that its complexity brings flexibility for scaling and managing data consumption. Kafka's strengths lie in its ability to coordinate and scale data processing effectively, although it requires a deep understanding of its components, such as partitions and brokers. Despite the challenges in setup and management, including error handling and configuration, Kafka proves invaluable for large-scale event ingestion, providing robust architectural patterns and saving the user from potentially difficult situations. The writer suggests alternative solutions like Redpanda for easier local setup and emphasizes that Kafka's advantages become apparent as system demands grow, despite the initial setup hurdles.

Static applications, which operate entirely within the browser, offer numerous advantages such as fast performance, ease of deployment, and scalability without requiring a full stack developer. They can be enhanced with frameworks like Next.js or Gatsby, although they often need to fetch data from various services, which can be streamlined by minimizing intermediaries to reduce latency and potential failure points. Tinybird is a service that facilitates real-time querying of large datasets, which is demonstrated in a static application that processes and analyzes over 50 million GitHub events from January 2020. The process involves converting JSON-formatted GitHub event data into CSV using jq and batching it with GNU parallel before uploading to Tinybird. A simple Vue application, or one built with other frameworks, displays aggregated data such as top users and repositories by fetching only the necessary information through Tinybird's API, utilizing SQL queries or predefined endpoints. Deployment is simplified using Zeit's platform, and while the GitHub data used is public, private data can be accessed securely with tokens. Despite concerns, sending SQL queries from a client application is secure as long as queries are limited by tokens to prevent unauthorized data manipulation. The application achieves average query times of around 300 milliseconds without intermediate caches or additional optimizations, and new accounts are being offered early access.

In April, a ClickHouse® meetup was held in Madrid, organized by Altinity, which frequently hosts such events in various cities to allow local developers to share their experiences with the technology. These meetups also feature discussions on the ClickHouse® roadmap by representatives from Yandex and Altinity. The event emphasized the enthusiasm of Altinity for ClickHouse®, which is central to Tinybird Analytics. Videos from the event, hosted at Google Campus Madrid, have been made available online. Additionally, there is an invitation for users facing slow query issues on MySQL or Postgres to contact Altinity for potential early access to Tinybird Analytics.

Traditional data warehouses are effective for data exploration and analysis but fall short in enabling real-time operational reactions, a gap that has become increasingly evident as companies seek to leverage data for immediate decision-making and competitive advantage. The rise of the cloud and software integration has accelerated digital transformation and the importance of data as a critical resource, yet even advanced companies face delays in accessing real-time insights, often relying on pre-aggregated data views that do not reflect the current state of their business. The shift towards operational analytics emphasizes not just extracting insights from data but also reacting to those insights instantaneously, a necessity highlighted by use cases like e-commerce logistics and car-sharing applications. However, achieving real-time operational analytics has been challenging due to the complexity of building data pipelines, infrastructure maintenance, and the limitations of existing data warehousing tools, which are not optimized for real-time processing or developer accessibility. Tinybird is presented as a solution that enables developers to achieve operational analytics at scale through secure and dynamic APIs, simplifying the process and allowing data engineers and developers to transform real-time data into business value with minimal effort.