Building a Data Stack on a Budget: An Affordable Guide to Data Management Sujeet Pillai January 17, 2023

Database management

A data stack is a combination of various tools and technologies that work together to manage, store, and analyze data. It typically consists of a data storage engine, an ingestion tool, an analytics engine, and BI visualization tools. Data stacks have become quite central to an organization’s operations and growth in recent years.

Data management is an essential part of any organization, and the way data is managed has evolved over the years. Data lakes and data warehouses were once only affordable by larger organizations. However, this has changed with the open-source data stack ecosystem’s growth. The open-source data stack ecosystem has grown significantly in recent years, providing powerful alternatives for every layer of the stack. This has pushed the envelope for data stacks and reduced entry barriers for organizations to adopt a data stack.

One of the main reasons why data stacks have become more accessible is the availability of open-source alternatives. Open-source alternatives are available for every layer of the data stack that packs a serious punch in capability. These alternatives are often just as good, if not better, than their commercial counterparts. They also tend to be more flexible and customizable, which is essential for organizations that must tailor their data stack to their specific needs.

Another reason why data stacks have become more accessible is the availability of cheap cloud resources. Cloud providers such as Amazon Web Services, Google Cloud, and Microsoft Azure provide low-cost options for organizations to set up and run their data stacks. This has enabled even smaller organizations to afford a data stack.

Organizations need to consider this framework over a patchwork of point-to-point integrations seriously. A patchwork of point-to-point integrations is often a result of an ad-hoc approach to data management. This approach is not only difficult to manage but also limits the organization’s ability to gain insights from its data. On the other hand, a data stack framework provides a more structured approach to data management, making it easier to manage and providing the organization with the ability to gain insights from their data.

An Affordable Data Stack

Affordable data stacks that organizations can consider are as follows:

Storage Engine: Clickhouse

Clickhouse is a column-oriented database management system that can handle large data loads and has great query performance. It runs on commodity hardware and can be self-hosted using Docker. Clickhouse is designed to process large amounts of data, and its columnar storage model enhances its query performance.

Ingestion Engine: Airbyte

Airbyte is an open-source data integration platform that automates the ingestion of data sources and can be monitored and managed from a UI. It can also be self-hosted using Docker and has the ability to use Clickhouse as a sink. Airbyte automates the ingestion of data sources, making it easy to bring data into the data stack.

Analytics Engine: DBT

DBT is a powerful analytics engine that helps organize data models and processing. It’s built on SQL with jinja templating superpowers, making it accessible to many more people. DBT is a hero in the data lakes space, helping enterprises organize their data models and processing. When building out an analytics process in DBT, it’s quite helpful to use a conceptual framework to organize your models. I found this blog excellent to be an excellent starting point, providing great insights. 

Visualization Engine: Metabase

Metabase is a powerful visualization tool that makes it easy for organizations to gain insights from their data. It has lots of visualizations that cover most bases. The SQL query builder or ‘question wizard’ in Metabase is quite powerful for non-SQL experts to get answers from their data. It also has a self-hostable open-source version and can easily be set up in Docker.

Infrastructure

For infrastructure, we recommend using Amazon Web Services. This stack can be deployed on a single m5.large instance for smaller-scale data and scaled up to a cluster configuration for larger data sets. Additionally, the different components of the stack can be separated into different servers for scaling. For example, if many Metabase users are accessing the data, it may be necessary to move Metabase onto its own server. Similarly, if ingestions are large, it’s best to move Airbyte to its server. And if storage and queries are large, it’s best to move Clickhouse into a cluster formation. This way, a company can ensure its system can handle more data as needed.

Production considerations

When it comes to taking the data stack to production, there are a lot of other considerations. Organizations should ensure reliable, fault-tolerant backups and provide security and role-based access. They should also build DBT models to cater to multiple use cases and normalize data values across sources. Other considerations may include monitoring and alerting, performance tuning, and disaster recovery planning.

Reliable, fault-tolerant backups are crucial to ensure that data is not lost in the event of a disaster. Organizations should have a well-defined backup and recovery plan in place. This should include regular backups, offsite storage of backups, and testing of backups to ensure they can be restored in an emergency.

Security and role-based access are also crucial implications. Organizations should ensure that only authorized personnel have access to sensitive data. This can be achieved by setting up role-based access controls, which ensure that only users with the necessary permissions can access sensitive data.

Further, organizations should ensure that their data is accurate, consistent, and reliable. This can be achieved by building DBT models that cater to multiple use cases and normalizing data values across data sources.

Finally, monitoring and alerting, performance tuning, and disaster recovery planning are also crucial. Organizations should ensure that their data stack is performing at optimal levels and that they are alerted to any issues that arise. Performance tuning is necessary to ensure that the data stack performs optimally. Disaster recovery planning is crucial to ensure that data can be recovered in the event of a disaster.

Conclusion

In conclusion, data stacks have become increasingly affordable and accessible for organizations of all sizes. The open-source data stack ecosystem has grown significantly, providing powerful alternatives for every layer of the stack. Designing DBT models to cater to multiple scenarios and standardizing data values across various sources are crucial. A data stack framework provides a more structured approach to data management, making it easier to manage and providing the organization with the ability to gain insights from their data.

Deploying a data lake to production with all these elements is a non-trivial technical exercise. If you do not have this expertise in-house, consider using the services of a consulting organization with expertise in this area, like Incentius. Drop us an email at info@incentius.com, and we’d be happy to help

Data Visualization using MS Excel Sumeet Shah March 31, 2021

 

The ability to analyze data is a powerful skill that helps you make better decisions. When it comes to choosing a tool, there are several options; however, the first one you think of is Microsoft Excel. Why? It is a tool that is deep-rooted in almost every business and our day-to-day life. The extent of our dependence on it is such that “Export to Excel” is the most used option amongst BI communities. In terms of usage, there are over 750M Excel users worldwide, and literally, any function in any organization/industry can benefit through the use of Microsoft Excel. Additionally, the ability to learn and quickly put it into practice gives you another reason to adopt it.

“I never guess, it is a shocking habit—destructive to the logical faculty” – Sherlock Holmes

Today, we live in an information-rich and time-poor world. Businesses have transformed from being intuitive to data-driven engines. Leaders crave data because they are aware of its availability. However, what they are looking for is not data; it is information and knowledge extracted from it. The big problem, though, isn’t data or too much of it. It’s the lack of time; there just isn’t enough of it to analyze the tons of data you have at your disposal. So, if your organization isn’t using BI tools like Tableau or PowerBI and plan to use Microsoft Excel as a tool for data analyses, this might be of your interest. 

Above is a quick piece of analysis done on the recently concluded India vs England Cricket Test Series. A simple export of scorecards for all the test matches and the use of basic excel functions like IF, SUM, RANK, VLOOKUP, and OFFSET have been used to transform the data into a structured format. There are no VBA macros used anywhere in the analysis, and the visualizations have been created by basic chart options available as a part of the default excel package. 

 

If you are an organization looking to analyze your data and are struggling with it, feel free to reach out to us at Incentius.