Why Senior Data Engineering Goes Beyond SQL

One of these questions I saw the other day was around a list of airplane routes with a table that looked like this:

The post then talked about how in a database A -> B and B -> A are different routes, but for a human they are the same. It then asked for a uniuqe list of routes, saying what a great showcase of self-joins, filters, window functions, and aggregations it was.

And while you certainly can do it that way, there's a much simpler way that has little to do with SQL and more with how a senior engineer or analyst would approach the problem: Thinking about why A -> B and B -> A are the same route. The answer here is not that one is the return trip for the other, but that the combination of items is the same. Both routes contain locations A and B. They are just stored in two columns.

The SQL Way

WITH base(start_location, end_location) AS (
  VALUES
  ('A','B'),('A','C'),('A','D'),
  ('B','A'),('B','C'),('B','D'),
  ('C','A'),('C','B'),('C','D'),
  ('D','A'),('D','B'),('D','C')
)
SELECT DISTINCT
	LEAST(a1.start_location, a2.start_location) AS r1,
	GREATEST(a1.start_location, a2.start_location) AS r2
FROM base a1
JOIN base a2 ON a1.start_location = a2.end_location

This will work perfectly fine and give you 6 results. But there is another way.

The Senior Engineer Way

Instead of self-joining and using functions like LEAST() and GREATEST() simply compute the unique number of combinations. In PostgreSQL that can look something like this:

WITH base(start_location, end_location) AS (
  VALUES
  ('A','B'),('A','C'),('A','D'),
  ('B','A'),('B','C'),('B','D'),
  ('C','A'),('C','B'),('C','D'),
  ('D','A'),('D','B'),('D','C')
)
SELECT DISTINCT
(
	SELECT array_agg(x ORDER BY x)
	FROM unnest(routes) AS x
) as sorted_routes
from
(
	SELECT
        ARRAY[start_location, end_location] AS routes
        FROM base
)

And boom, 12 routes shrink down to 6, but how? Well, the first step is simply to turn your starting_location and end_location into an array column.

That turns A -> B and B -> A into (A,B) and (B,A).

Then you simply sort the array items and SELECT DISTINCT the outcome. That way the two rows (A,B) and (B,A) become (A,B) and (A,B) and DISTINCT removes the duplicates.

Why This is Better (Opinion)

The more senior a data engineer becomes, the more their work focuses on solving problems in an easy to understand manner, so that it's easy to look at 6 months later or by a different person. It is about maintainability, rather than showy code.

Maintainability

Here's an example for that 6 months later followup: You migrated or merged systems and the location names are not quite as consistent as they used to be. There can be extra whitespace, case differences, and all the other issues that come from mixing two systems together. So you need to clean and standardize your location names.

In the fancy SQL part you'd have to plaster your cleaning functions (e.g. TRIM(LOWER(REGEXP_REPLACE(column, pattern, ''))))) into the LEAST / GREATEST functions, the ON part of the join, any sorting you might have to do, etc.

The array method is far less showy and fancy. It does not demonstrate your ability to memorize dozens of different SQL functions, but it lets you apply that cleaning function in only one place, the select array[]) part. Just that once and everything else works.

Transferability

The array method has another benefit over self joins: you can move it to other languages because sorting arrays is one of the most common operations in any programming language. You can do it easily in python dataframes, in JavaScript, in Go, heck even in bash.

Now your workflow is suddenly a lot more modular and flexible.

Conclusion

Dear hiring managers, HR screeners, when you look for a senior position, don't be so focused on the technical proficiencies of a candidate. Instead, think about whether you want to hire someone who is really good at SQL, or someone who will solve your problems.