As engineers we refactor and abstract our code regularly, this is part of process to improve our code quality and scale up our codebase. In my previous job, we refactor our code religiously - “Leave the codebase a better place than you found it” so we said, that was the main reason to help us build big, complex system with just a handful of engineers!
When we started Holistics, we naturally asked - how can we enable the same refactoring approach for data/SQL analysts?
Writing Reusable SQL Components with Query Templates
Today, we want to share with you Query Templates, an easy way for Holistics customers to write reusable SQL components! Query Templates allow you to abstract a snippet of SQL code into a dynamic template to be used across multiple SQL reports.
Whenever you find yourself writing similar snippets of SQL code over and over again, make it a query template!
When your report runs, the query template will automatically be replace with the actual template’s content (with dynamic variables!). This is just like programming language’s functions inside SQL itself.
Benefits
1- It saves you time writing repeated code and makes your SQL a lot easier to read
When you find yourself repeatedly writing long queries or common snippets of SQLs, turn that into a query template
2- It helps you and your teams centralize business logics into modular components
As your application evolves, your schema and business logic changes regularly. When this happens, instead of modifying 20 different reports, now you just need to modify at one place.
And when a new analyst comes onboard, s/he can just read the query templates to understand the different business logics baked into your application.
Conclusion
SQL is a powerful language when working with data, but it is built mainly for individual, adhoc executions, and not yet suitable for complex, organization-wide systems. Query Templates is one among the many things Holistics provide to help bridge this gap.
Holistics provides an online interface for data analysts to create automated reports/dashboards to share with your business users through dynamic SQL queries.
Building a data reporting platform for your company? Give us a try!
