Breaking It Down: Explaining SQL Query Differences to Your Manager

Recently, I witnessed a spirited discussion between a developer and a manager about the intricacies of SQL queries and their myriad types. This interaction served as a poignant reminder to all managers, especially those who have transitioned away from hands-on programming: it's entirely acceptable that your daily objectives no longer center around code. However, maintaining a robust understanding of programming concepts is crucial. Although you may be steering the ship from a strategic vantage point, it's essential to trust and rely on the technical prowess of your developers. Your architects and senior developers are equipped with the most current technical skills, crucial for navigating today's technological challenges.

Your architects and senior developers are equipped with the most current technical skills, crucial for navigating today's technological challenges.

Embracing a common language to discuss and understand the nuances of code will not only enhance collaboration but also ensure that, even if you're not directly involved in coding, you're not lost in translation either.

During this discussion, it became evident that there was a mix-up between what constitutes a dynamic versus an adaptive SQL query. This mix-up highlighted the importance of staying connected with the technical language and developments within our fields.

Let me tell you a story.

The Premise

Our story begins with Developer A, an adept programmer known for her meticulous approach to coding and problem-solving. A was tasked with a seemingly straightforward request: to write an SQL query that would use the output of a previous query as the parameters for the next one. For those familiar with the intricacies of SQL, you'd recognize that this scenario typically requires a dynamic SQL query—a query that is not entirely known until runtime, where you must construct SQL strings on the fly, inject parameters, and execute them based on live data.

The Misunderstanding

Enter Manager J. When A approached J with concerns about the complexity of dynamically generating SQL queries, J dismissed these concerns as overblown. “This is a trivial task. I’ve done it many times,” he asserted confidently, mistaking A's description for what he recalled as adaptive queries—queries that adjust based on predetermined conditions but do not entail the creation of entirely new query strings during runtime.

He insisted on a solution leveraging dynamic queries and BigQuery's capabilities. Yet, when he sent his proposed solution —useless btw—, A discovered J was actually referring to a particular type of query—what we call "adaptive"—rather than the truly dynamic queries he thought he was describing. This mix-up not only sparked our lively debate but also inspired me to write this blog entry.

My goal here is to spotlight the distinctions between these concepts clearly and comprehensively. Understanding these differences is crucial for developers, as it helps set clear boundaries and expectations when communicating technical strategies with management. By demystifying these terms, we can foster a deeper mutual understanding and more effective collaboration between managers and their technical teams. This, in turn, ensures that strategic directives and implementation details are perfectly aligned, optimizing both development processes and business outcomes.

Let’s explore each type of SQL query with an example to better understand their characteristics and usage.

0. Query

A SQL query is a request made to a database to perform a specific operation on the data it contains. SQL, which stands for Structured Query Language, is the standard language used for managing and manipulating databases. A SQL query can execute a wide range of tasks, including retrieving data, inserting new records, updating existing data, and deleting records from a database. Here’s a breakdown of the main types of SQL queries:

SELECT, INSERT, UPDATE, DELETE, DDL STATEMENTS.

1. Parameterized Query

Description: A parameterized query is static in its SQL structure but allows dynamic input for parameters, typically used for enhancing security and performance.

Example (Using Python & BigQuery)

from google.cloud import bigquery
client = bigquery.Client()

# Static query with parameterized input
query = """
SELECT state, COUNT(*) as total
FROM `bigquery-public-data.samples.natality`
WHERE year = @year
GROUP BY state
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("year", "INT64", 2005)
    ]
)

# Execute the query with parameters
query_job = client.query(query, job_config=job_config)

# Print results
for row in query_job:
    print(row)

2. Adaptive Query

Description: Adaptive queries use results from initial queries (or conditions) to determine what data to operate on in subsequent operations, within the same execution context.

Example (using BigQuery SQL):

WITH HighBirthYears AS (
  SELECT year
  FROM `bigquery-public-data.samples.natality`
  GROUP BY year
  HAVING COUNT(*) > 1000000
)
SELECT n.*
FROM `bigquery-public-data.samples.natality` n
JOIN HighBirthYears h ON n.year = h.year

3. Conditional SQL

Description: This involves using SQL's conditional logic to alter the execution plan based on the data being processed or external conditions.

Example (using BigQuery SQL):

SELECT state,
       CASE
           WHEN year < 2000 THEN '20th century'
           WHEN year >= 2000 THEN '21st century'
           ELSE 'Unknown'
       END AS century
FROM `bigquery-public-data.samples.natality`
WHERE state IS NOT NULL
ORDER BY state, century

4. Dynamic queries

Description: Dynamic SQL involves constructing and executing SQL statements based on runtime parameters. It can change entirely based on inputs and is often used in applications or with database scripting features.

"The dynamic SQL component of SQL allows programs to construct and submit queries at run time. In contrast, embedded SQL statements must be completely present at compile time; they are compiled by the embedded SQL preprocessor. Using dynamic SQL, programs can create SQL queries as strings at runtime (perhaps based on input from the user) and can either have them executed immediately or have them prepared for subsequent use. Preparing a dynamic SQL statement compiles it, and subsequent uses of the prepared statement use the compiled version."[1]

Example (using BigQuery SQL):

DECLARE table_info ARRAY<STRUCT<table_name STRING, column_name STRING>>;
DECLARE query_strings ARRAY<STRING>;
DECLARE idx INT64 DEFAULT 0;

-- Step 1: Fetch table and column names into an array
SET table_info = ARRAY(
  SELECT AS STRUCT table_name, column_name
  FROM `project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_schema = 'your_dataset'  
);

-- Step 2: Generate SQL query strings for each column in each table
WHILE idx < ARRAY_LENGTH(table_info) DO
  SET query_strings = ARRAY_CONCAT(
    query_strings,
    [FORMAT("SELECT '%s' AS table_name, '%s' AS column_name, %s AS value, COUNT(*) AS count FROM `project.your_dataset.%s` GROUP BY %s ORDER BY count DESC LIMIT 5;", 
            table_info[ORDINAL(idx + 1)].table_name, 
            table_info[ORDINAL(idx + 1)].column_name, 
            table_info[ORDINAL(idx + 1)].column_name, 
            table_info[ORDINAL(idx + 1)].table_name, 
            table_info[ORDINAL(idx + 1)].column_name)]
  );
  SET idx = idx + 1;
END WHILE;

-- Step 3: Output the list of queries
SELECT * FROM UNNEST(query_strings) AS query_string;

Through her detailed presentation, A was able to clearly demonstrate the complexities of her SQL query, effectively demystifying the technical misunderstanding with Manager J. Although J did not acknowledge his oversight —a common scenario workplace environments— this incident illuminated the broader issue of how to engage with management when disagreements arise. (The resolution of this particular case, and J's reaction, could be explored in a separate discussion focused on strategies for dealing with challenging managerial behaviors).

In conclusion.

Embracing a common language to discuss and understand the nuances of code is more than a necessity—it's a bridge to better collaboration and ensures that, even if we are not in the coding trenches, we are not lost in translation. Such understanding fosters a deeper mutual respect and enhances the collaborative spirit, enabling our teams to innovate effectively and meet project demands efficiently.

This blog entry serves as a reflection and a call to all my fellow managers to reengage with the technical elements of our projects, ensuring that we can lead our teams not just with managerial expertise but with informed technical insight.

Bibliography

[1] Silberschatz, A., Korth, H. F., & Sudarshan, S. (2006). Database system concepts (5th ed.). McGraw-Hill Education.