Realtime Data Processing

At SolubleCode, we process a large amount of financial transaction data on a daily basis. To ensure that we provide our clients with accurate and timely insights, we rely on near real-time stream data processing. To achieve this, we use Tinybird and Upstash Kafka. In this blog, we will discuss how we use these technologies to process financial transaction data, and showcase code examples for analysis through Tinybird transformations.

What is Tinybird?

Tinybird is a real-time data processing platform that enables the processing of data in real-time. It is an easy-to-use platform that provides a range of features for processing and analyzing large volumes of data. Tinybird allows us to process and transform data in real-time, and can be used to build real-time dashboards, generate reports, and more.

What is Upstash Kafka?

Upstash Kafka is a cloud-based managed Kafka service. It provides a distributed messaging system for streaming data processing. With Upstash Kafka, we can create, read, and write data streams in real-time. It is a reliable, scalable, and durable messaging system that allows us to process data in near real-time.

How we use Tinybird and Upstash Kafka for stream data processing?

At Solublecode, we use Upstash Kafka to ingest and stream financial transaction data in real-time. We use the Kafka producer API to send financial transaction data to Kafka topics. Once the data is in Kafka, we use Tinybird to process and transform the data in real-time.

Here's are examples of how we use Tinybird to process financial transaction data:

SELECT 
    CAST(time AS datetime) AS transaction_time, 
    sum(amount) AS total_amount, 
    count(*) AS transaction_count, 
    avg(amount) AS average_amount,
    CASE
        WHEN amount >= 500 THEN 'Large Transaction'
        WHEN amount >= 100 AND amount < 500 THEN 'Medium Transaction'
        ELSE 'Small Transaction'
    END AS transaction_type
FROM 
    pesa-tools.transactions
GROUP BY 
    CAST(time AS datetime), 
    CASE
        WHEN amount >= 500 THEN 'Large Transaction'
        WHEN amount >= 100 AND amount < 500 THEN 'Medium Transaction'
        ELSE 'Small Transaction'
    END
 

In this example, we are processing financial transaction data to get a summary of transactions that occurred on a specific date. We are grouping the transactions by the transaction type (small, medium, or large), and calculating the total amount, transaction count, and average amount for each transaction type. We are also categorizing each transaction based on its amount.

In addition to processing and transforming data in real-time, Tinybird also allows for the publishing of transforms to an HTTP API. This allows the transformed data to be easily accessed and used by other applications and services.

To publish a Tinybird transform to an HTTP API, you can use the Tinybird API feature. The API feature allows you to create an HTTP endpoint that exposes your Tinybird transform as a REST API.

Here's an example of how you can use the Tinybird API feature to publish a transform to an HTTP API:

FROM pesa-tools.transactions
SELECT 
    CAST(time AS datetime) AS transaction_time, 
    count(*) AS transaction_count, 
    sum(CASE WHEN type = 'credit' THEN amount ELSE 0 END) AS total_credit_amount, 
    sum(CASE WHEN type = 'debit' THEN amount ELSE 0 END) AS total_debit_amount, 
    sum(CASE WHEN type = 'credit' THEN 1 ELSE -1 END * amount) AS net_amount
GROUP BY 
    CAST(time AS datetime)
API "transactions_summary" "v1" "This API returns a summary of financial transactions"

In this example, we are analyzing financial transaction data to get a summary of transactions that occurred on each day. We are grouping the transactions by the transaction time and calculating the total transaction count, total credit amount, total debit amount, and net amount (which is the difference between the total credit and total debit amounts).

Further, we are using the API statement to create an HTTP endpoint for the transform. The first parameter is the name of the API, the second parameter is the version, and the third parameter is a description of the API. Once the transform is published as an HTTP endpoint, other applications and services can easily access the transformed data through a REST API.

Once the data is processed and transformed, the resulting data can be used to generate real-time dashboards, reports, or for further analysis.

Conclusion:

Tinybird provides a powerful platform for real-time stream data processing and transformation, and the ability to publish transforms to an HTTP API makes it even more powerful. With Tinybird, you can easily process and transform financial transaction data in real-time and expose the results as a REST API for other applications and services to consume.