2 posts tagged with "Clickhouse"

Why Your Data Kitchen Needs Separate Stations​

The case for splitting Analytics from Operations

Introduction: How I Learned to Separate My Kitchens​

After 30 years as a Data Architect, I’ve seen the same scenario play out countless times. Companies, particularly those in the SaaS space, come to me because they’re struggling to scale their analytics. They’ve built impressive systems, but as their data needs grow, so do their problems. Often, the root cause is the same, they’re trying to analyze data directly from their operational stores.

Imagine trying to build detailed reports for hundreds of clients, each with their own unique needs and schedules, all while relying on the same operational system that’s supposed to keep your business running smoothly. It’s a recipe for disaster. Whether they’re using a relational SQL store or a memory-hungry NoSQL solution, these companies are burning resources and slowing down operations.

What’s more, the market often pushes the idea that copying data is inherently bad and that accessing data directly from wherever it resides is faster and more efficient. But from my experience, this approach can lead to bloated systems, sluggish performance, and skyrocketing costs. The truth is, copying data isn’t bad, as long as it’s done thoughtfully, with a focus on directionality, master versus reference data, and lifecycle management.

An Analogy:​

Imagine you're running a restaurant kitchen. You have chefs preparing food for hungry customers, and every second counts. But what if, in the middle of dinner service, a food critic walks in and asks for detailed recipes, nutritional information, and a history of every dish you've ever served?

If the same chefs handling customer orders had to stop and dig through old recipe books, calculate nutrition facts, and compile a history of dishes, the kitchen would grind to a halt. Orders would get delayed, customers would be unhappy, and the overall dining experience would suffer.

This is what happens when you don't separate analytics from your operational data store.

The Operational Data Store (ODS): The Cooking Station In our kitchen analogy, the Operational Data Store (ODS) is like the cooking station. It's where all the action happens, data is created, updated, and used in real-time, just like how chefs are constantly chopping, sautéing, and plating dishes for customers. The ODS is optimized for speed and efficiency, designed to handle high volumes of transactions quickly and reliably.

Analytics: The Food Critic's Desk Analytics, on the other hand, is like the food critic's desk. It's where deeper insights are drawn, trends are analyzed, and strategic decisions are made. This process requires access to large amounts of historical data, complex calculations, and the ability to look at the data from various angles, similar to how a critic might assess a dish from taste, presentation, and nutritional value perspectives.

Why They Should Be Separate​

If you were to try and handle both cooking and food critics' demands from the same station, you'd overwhelm your chefs, slow down service, and ultimately hurt your restaurant’s performance. The same thing happens in your data architecture if you try to process analytics directly from your ODS:

Performance Hit: Just like chefs can’t keep up with orders if they’re constantly interrupted, your operational systems slow down when bogged down by complex queries.

Data Contamination: Mixing operational and analytical processes can lead to inconsistencies in data. It’s like a chef accidentally mixing salt instead of sugar, small errors can have big impacts.

Scalability Issues: As your restaurant (or business) grows, the demands on both your ODS and analytics will increase. Keeping them separate allows each to scale according to its own needs.

The Solution: A Separate Analytics Kitchen​

In a well-run restaurant, there’s a separate space where food critics are entertained. They have access to all the data they need, but they don’t interfere with the daily operations. Similarly, in data architecture, we separate the analytics environment from the operational data store.

Data Warehouses: These are specialized kitchens designed for analytics. They store historical data in a specific way to support complex queries, and are optimized for analysis without affecting operational performance.

ETL Processes and Data Pipelines: Just like how the best ingredients are carefully selected and prepared before they reach the kitchen, data is extracted, transformed, and loaded (ETL) from the ODS into the data warehouse. This ensures that the data used for analysis is clean, consistent, and ready for deep dives.

Conclusion: Keep Your Kitchens Separate!​

By keeping your operational and analytical processes separate, you ensure that both run smoothly, customers get their food on time, and critics get the detailed information they need without disrupting the flow. In the world of data architecture, this means faster operations, more accurate insights, and a more scalable and resilient system.

And on that note... I think I'm in the mood to cook. Maybe some shrimp in a roasted red-pepper and white-wine cream sauce on delicious home made pasta... I like quality in my cooking, almost as much as I like it in my data!

About TrillaBit​

TrillaBit is an analytics and business intelligence (BI) software company founded in 2022 and headquartered in Toronto, Canada. The company provides a no-code, search-driven, low-cost analytics cloud platform tailored for B2B SaaS providers. TrillaBit's platform, Quick Intelligence, delivers fast, easy, and secure access to data, allowing product owners and business users to create dashboards and visualizations without developer assistance.

The TrillaBit platform is a hosted, highly dynamic, meta-data engine that points to client data stores and automatically generates queries based on configuring tokens in search (AKA Search-driven analytics). The platform creates smart visualizations then allows users to refine the results. This ease of use and exploration allows end users to quickly dive into the data and create their own dashboards to derive further insights instantly. All without the need to rely on developers.

Quick Intelligence is designed to handle complex security scenarios, including multi-tenant environments, and supports a wide range of data sources. TrillaBit emphasizes data monetization through visualization, making data easily accessible and actionable for its users. The platform is scalable and affordable, designed to meet the needs of both small and large enterprises. For more information about TrillaBit, please visit: www.TrillaBit.com or [email protected]

ß TrillaBit Quick Intelligence is a robust SaaS platform for reporting and business intelligence, utilizing the power of ClickHouse for fast scalable results. Today's reporting tools are simply not dynamic enough for users to ask new questions and get results back immediately. Not without having to go through a timely and costly development life-cycle.

They also commonly depend on expensive expertise to implement, maintain, and run the supporting systems. When development teams want to spend time on new and exciting creations, they're often pulled back into a business user's new question. They are then forced to build out the new query and KPI, QA it, deploy it, so that the business user can finally see it. Once they see the results they have even more questions which keeps this vicious and costly cycle going.

Working with ClickHouse​

ClickHouse wasn't TrillaBit's first love. Solr originally caught TrillaBit's eye. Why not?! TrillaBit is a search-driven analytics platform, so why not use a search-driven data backend. Solr is capable of some levels of data aggregation, the models are dynamic and the indexing is ideal for search purposes. However, TrillaBit soon ran into a number of challenges. Solr, being a key-value store is more suited to search than it is to high volume aggregation or data compression for performance. Its query language isn’t as broad or established as SQL. It doesn’t handle joins well and is not ideal for managing data. TrillaBit experienced far too much pain managing and getting Solr to perform at scale. So when TrillaBit’s eyes began to wander, ClickHouse showed the most potential as an alternative.
TrillaBit quickly found a new favourite. ClickHouse has a huge number of built-in functions, supports data clustering and is built for both data management and analytics. It handles joins and materialized views with ease. The different table engines [ReplacingMergeTree, AggregatingMergeTree, MergeTree, S3 table engine] all help with different data management use cases for different client needs. The community version is Free and helped TrillaBit get started at a minimal cost As TrillaBit grows, ClickHouse is able to keep pace with the ClickHouse Cloud. Helping even data experts like TrillaBit scale and manage their clusters.

Exploring your ClickHouse data with Quick Intelligence​

TrillaBit is solving the BI Assembly line problem in a cost-effective way. The Quick Intelligence platform allows users to ask a question in a search bar and get immediate visual answers. Example Question: Total Sales by Sales Rep Last Month Utilizing ClickHouse because of its incredible performance at scale, it finds the data and instantly graphs it for you. Once you visualize the data you can easily drill down into the area of interest to uncover further insights and expose record level detail at any point. A metadata driven system allows business users to explore data in their own way, asking new questions and getting immediate answers in seconds.

Save and Share​

When users find something interesting and valuable in their data, they often want to save and share with others, either inside or outside the tool. There are many ways to do this. Creating dashboards on the fly and sharing them with individuals or groups is one way. With Quick Intelligence, this is as simple as pinning visualizations to a dashboard or creating a new one in seconds.

Users can also export their KPIs as images for PowerPoint presentations, word or email. You can also drill right down to the underlying raw data and export it to Excel to share with a colleague.

To Embed or not to Embed​

Companies that want to use this functionality as their own have the option to embed Quick Intelligence into their own product. They can skin it to look like their own brand or to look like any of their client’s brands at the account level. Other companies who want to use this internally are able to have all of this functionality in a standalone UI. Additionally Standalone and embedded are available in a single implementation. For the best of both worlds.

Security and scale​

TrillaBit Quick Intelligence utilizes ABAC policy control. It allows for multi-tenant within multi-tenant capabilities and can secure data for many departments. A large part of the backend scalability comes from the efficient performance of ClickHouse. Whether it's YOUR ClickHouse environment, the ClickHouse cloud or have TrillaBit manage everything, the product is versatile and able to handle several configurations. TrillaBit scales to IoT and network level traffic speed and size of data, trillions of rows. Providing real time analytics.

Getting Started with TrillaBit on ClickHouse​

TrillaBit is an enterprise grade platform. If you have ClickHouse already, TrillaBit can connect to it and you’ll be up and running in no time! TrillaBit is metadata driven, so the only thing required is the data. If you’re looking to run your own data warehouse in ClickHouse and have TrillaBit run on that, just let TrillaBit know. They’ll work with ClickHouse and guide you through the whole process. If you want to be completely hands-off, TrillaBit can handle the end-to-end process for you. Your business users or clients will be able to just start exploring on their own and gathering insights. sPlease feel free to reach out: [email protected]