In today’s data-driven world, having access to accurate and up-to-date reports is essential for making informed decisions. While many organizations rely on expensive business intelligence (BI) tools or data analytics platforms to generate and share reports, there is an alternative approach that’s both cost-effective and efficient.
In this post, we’ll explore how you can leverage plain text files and Git to create and automate zero-cost reporting.
The challenge of traditional reporting
Traditional reporting often involves using BI tools or analytics platforms. These solutions can be expensive, time-consuming to set up, and require continuous maintenance. With numerous options available, decision-making can be overwhelming, leading to analysis paralysis.
The power of plain text files
Plain text files have the advantage of being human-readable, but also machine-parsable. They provide a simple, lightweight, and easily shareable format for reporting. Results can be easily converted to better formats like PDF or HTML, making them accessible to a wider audience.
Utilizing Git for version control
Then, storing query and report files in a version control system like Git enables easy tracking of changes over time. This enables users to collaborate on queries, maintain a history of reports, compare different versions, and analyze evolving data and report structures effectively.
Automating reporting with Git and GitHub Actions
One of the powerful features of GitHub is GitHub Actions, which allows you to define workflows and automate various tasks. By creating workflows, you can automatically generate reports at specific intervals or in response to triggers like data updates. This automation eliminates the need for manual report generation, saving time and effort.
Get the O’Reilly Trino guide
SQL at Any Scale, on Any Storage, in Any Environment
Step-by-step guide to zero-cost reporting
- Start with Plain Text Files: Save your data queries and results in plain text files, such as .txt or .sql files. These files are easy to edit, maintain, and share.
- Version Control with Git: Store these files in a Git repository to keep track of changes and maintain a history of reports. Git allows you to collaborate with others seamlessly.
- Execute Queries from Files: Use your preferred SQL client to run queries directly from the query files. This ensures that your reports are always based on the latest data.
- Convert Results to Tables: Pipe the query results through a CSV to MD (Markdown) conversion tool to create Markdown tables. Markdown files are easily readable and can be converted to various formats.
- Automation with GitHub Actions: Define a GitHub Actions workflow to automatically run queries and generate reports. Set triggers for specific events, such as scheduled intervals or data updates, to ensure timely report generation.
- Commit and Push: GitHub Actions will automatically commit the updated report files back to the repository. This makes the reports readily accessible and allows for easy comparison between different versions.
See more examples and projects
To demonstrate the effectiveness of this approach, here are two notable projects:
- Trino CI/CD pipeline analysis: A real-world example showcasing how this method is used to analyze the Trino CI/CD pipeline.
- Evidence.dev — Business intelligence as code: Evidence is a project that takes the concept of zero-cost reporting further by embedding SQL queries inside Markdown files. This approach streamlines the process of generating dynamic reports.
With the power of plain text files, version control, and automation, you can now create a zero-cost reporting system that delivers up-to-date reports to your team effortlessly.
Whether you’re a small organization or a larger enterprise, this approach can save you both time and money while ensuring you have the insights you need to succeed. Happy reporting!
