Ad-hoc Analytics Requiring 100+ Lines of Complex SQL? No-code Tools to the Rescue

When repeatable reports are produced, SQL is used to combine the data into the right data model, and the query is continuously reused when the report is generated.  Hence, the data to produce the results is transformed into the right format to be consumed by users.

However, for most ad hoc analysis requests, the data is often not in the right format and can come from multiple tables.  This leads to either (a) analysts needing to write complicated queries that require complex SQL coding or (b) a request to the data engineering team to model and transform the data for the analysis.

SQL experts are proud of their coding skills, SQL knowledge, and level of detail they can get to.  They consider it a challenge and a validation of their unique skills.  Their SQL knowledge is certainly a strong validation of the value they bring to their organization.

But, in the case of ad-hoc analytics, a more important capability is that the analyst produces the RIGHT answer, QUICKLY.  The true value add is their knowledge of the data and how to produce the result, not necessarily their ability to write complex SQL.

In this LinkedIn post , the author, an analyst at Freddie Mac, talks about having to write very complex SQL to solve an ad-hoc analytics request which consisted of:

• 100+ lines of code
• Multiple CASE WHEN statements (one even had 15 cases all with AND conditions)
• A large sub query💀 (also full of several CASE WHEN statements)

According to the author:

“The query was written by the powers of coffee, brute force, and sheer determination.  It is by far the most inefficient, clunkiest, held-together-by-a-thread query I have ever written.”

The author also claimed she “didn’t know a more efficient way to accomplish what I was hoping to.”

Using a no-code data modeling and transformation tool would have been a more efficient way for the author to accomplish their goal.  With a no-code tool:

• You don’t need to write 100+ lines of complex SQL code,
• CASE WHEN statements are all done via wizards (like in Excel),
• Subqueries are automatically generated based on the visual model – you don’t need to embed them in the query,
• You don’t need to experiment with the different pieces of the query – the no-code tool will give you visual representations of the results as you add different pieces to the query,
• You will get a complete visual representation of the data as you go and at the end, so you KNOW if your query is correct,
• All the data stays in the cloud data warehouse where it can be properly governed and managed, and
• All the compute to perform the query and transformations is performed in and optimized for the cloud data warehouse, making it extremely cost-efficient.

  When it comes to data engineering, SQL is often king because data engineers like to write SQL.  And the majority of data analysts are often SQL experts as well.  But why bother if you don’t have to go through the time to write complex SQL?  It is time-consuming and potentially error-prone, regardless of your level of SQL expertise.

The other aspect of Jess’ project was that “it was an ad-hoc request.”  She argues in this case that efficiency wasn’t an issue.  And that is true unless the answer she produced was important and needed to become repeatable.  Many ad-hoc requests often do become repeatable.  And no code tools will ensure the query is correct, efficient, and repeatable.

Also, while efficiency may not have been a concern, time most likely was.  Most ad-hoc requests have urgency and require a very fast turnaround time.  No-code tools would allow the data model to be built rapidly and answers delivered within hours or even minutes, without even moving the data out of your cloud data warehouse.

Would no-code data modeling and transformation tools make your ad-hoc analytics faster and more efficient?  Learn more by visiting the Datameer website , or get started immediately with a free trial of our SaaS platform.