Why Senior Data Engineers Write SQL Differently

Every experienced data engineer has opened a SQL file, stared at a wall of nested subqueries, and wondered, "Who wrote this?"

Then they check the Git history.

It was them. Six months ago.

Two engineers are asked to pull a list of high-value repeat customers for the marketing team. Both write a query. Both get the right answer. One query gets merged in a single review pass. The other comes back with three comments, two of which are just "what does this do?"

Same result set. Same database. Completely different outcomes. The difference has nothing to do with whether the SQL is correct, because it is, in both cases. It has to do with whether the query is easy for someone else to read, understand, and change when business requirements inevitably evolve, or whether even the original author can make sense of it six months later.

That is the gap this article is about.

We will use one running example throughout: a PostgreSQL e-commerce schema with customers, orders, order_items, and products tables, and a single business question: find repeat customers who have spent over $500 in the last 90 days, excluding refunded orders. We'll start with a query that works, then refactor it into one that's easier to understand, review, debug, and maintain.

Working SQL vs. Maintainable SQL

Here is a version that works.

SELECT *
FROM customers c
WHERE c.customer_id IN (
  SELECT o.customer_id
  FROM orders o
  WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
    AND o.status != 'refunded'
  GROUP BY o.customer_id
  HAVING COUNT(*) > 1
     AND SUM(
       (SELECT SUM(oi.quantity * oi.unit_price)
        FROM order_items oi
        WHERE oi.order_id = o.order_id)
     ) > 500
);

Run it, and it returns the correct customers. Put it into production, and three things will eventually go wrong. Someone adds a column to customers and the SELECT * silently pulls in data nobody asked for. Someone needs to know why the threshold is 90 days and $500, and there is no comment to tell them. Someone needs to debug why a known repeat customer is missing from the results, and the only way to check is to mentally execute a query with a subquery nested inside a HAVING clause nested inside an IN clause.

This query works today. It may even work tomorrow. What it doesn't do is make tomorrow's change easy.

Maintainable SQL is written with future readers in mind. It assumes schemas evolve, business rules change, teammates rotate, and production issues eventually happen. Senior engineers optimize for that reality. Their goal isn't simply to write SQL that returns the right rows—they write SQL that someone else can confidently modify without introducing new bugs.

Good SQL doesn't minimize the number of lines. It minimizes the amount of information the reader has to keep in their head.

Structure SQL as a Sequence of Transformations

The query optimizer cares about execution plans; your teammates care about readability. Modern database query optimizers can often transform different SQL formulations into similar execution plans by flattening subqueries, reordering joins, and applying other optimizations. When performance is comparable, the version that's easier for humans to understand is usually the better engineering choice.

Here is the same logic, restructured.

-- Repeat customer definition: more than one qualifying order
-- in the trailing 90 days, excluding refunds.
WITH recent_orders AS (
    SELECT order_id, customer_id, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
      AND status != 'refunded'
),

order_totals AS (
    SELECT
        ro.order_id,
        ro.customer_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM recent_orders ro
    JOIN order_items oi ON oi.order_id = ro.order_id
    GROUP BY ro.order_id, ro.customer_id
),

customer_summary AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(order_total) AS total_spend
    FROM order_totals
    GROUP BY customer_id
),

high_value_repeat_customers AS (
    SELECT customer_id, order_count, total_spend
    FROM customer_summary
    WHERE order_count > 1
      AND total_spend > 500
)

SELECT
    c.email,
    hvrc.order_count,
    hvrc.total_spend
FROM high_value_repeat_customers hvrc
JOIN customers c ON c.customer_id = hvrc.customer_id
ORDER BY hvrc.total_spend DESC;

Nothing about the output changed. Everything about the experience of reading it did. A few things are doing the work here, and they generalize well beyond this one query.

The nested subquery is gone. The original query asked the reader to hold three levels of logic in their head at once: which orders qualify, what each order is worth, and which customers clear the bar. Splitting that into sequential CTEs means each block answers exactly one question, and the next block builds on a clean answer instead of a buried calculation.

Each CTE has a name that describes what it produces, not what it technically is. recent_orders, order_totals, customer_summary, and high_value_repeat_customers tell you the shape of the data at each stage without opening the block. Compare that to cte1, t, or temp_data, which tell you nothing and force a re-read every time they appear later in the query.

The transformation is broken into logical stages that mirror how a person would actually reason through the problem by hand: filter the orders, total the orders, summarize by customer, then apply the business threshold. That sequence is also the order someone would naturally debug in.

Which leads to the real payoff: easier debugging. If a customer who should qualify is missing, you do not have to mentally execute the whole query. You run SELECT * FROM recent_orders WHERE customer_id = 1042 and check whether their orders show up. Then order_totals. Then customer_summary. Each CTE is a checkpoint you can inspect in isolation, which is exactly why this structure is easier to maintain than a single dense expression.

Select Only the Columns You Need

I once reviewed a query that produced the correct report for over a year. Then a new column was added to the source table. Because the query used SELECT *, a downstream export silently changed shape and broke another team's pipeline. Nothing was "wrong" with the SQL. It simply wasn't written with future change in mind. Nobody intentionally changed the business logic, yet downstream systems still break because the query's contract was never explicit.

The same is happening here. Look back at the original query and notice what SELECT * FROM customers actually returns. Every column on that table, including ones that have nothing to do with the question being asked, and ones that might not exist yet.

-- Before: pulls every column on customers, including ones
-- nobody asked for and some that may not even be safe to expose.
SELECT *
FROM customers c
WHERE c.customer_id IN (...)

-- After: explicit about exactly what's needed downstream.
SELECT
    c.customer_id,
    c.email,
    c.signup_date
FROM customers c
WHERE c.customer_id IN (...)

The explicit version costs three extra lines and buys real protection. If someone adds an marketing_preferences or gdpr_deletion_requested column to customers next quarter, the SELECT * version starts leaking it into every downstream dashboard and export that consumes this query, without anyone deciding that should happen. The explicit version is unaffected, because it only ever returns what it was written to return.

There is also a performance and cost angle. Most modern cloud data warehouses, such as Snowflake, BigQuery, Redshift, and Databricks, store data in a columnar format, allowing the query engine to read only the columns required by a query. When you use SELECT *, the execution engine has to read every selected column, including ones you don't actually need. On wide tables, that can mean scanning large JSON documents, audit fields, or other bulky columns that downstream consumers never use. Selecting only the columns you need not only makes the intent of the query clearer, but can also reduce I/O, lower query costs, and improve performance in column-oriented systems.

There is a reviewability cost too. A reviewer looking at SELECT * has to go check the table definition to know what they are actually approving. A reviewer looking at an explicit column list can see the entire contract of the query in the query itself.

Validate Results Before Shipping

A query that runs without an error is not the same thing as a query that is correct, and senior engineers tend to have been burned by that distinction at least once. Before this query is delivered to the marketing team, it is worth a few minutes of sanity checking.

Check that the row count is plausible.

SELECT COUNT(*) FROM high_value_repeat_customers;

If the customer base is 40,000 people and this query returns 38,000 of them as "high-value repeat customers," the threshold logic is almost certainly wrong somewhere, even though the query executed cleanly.

Check for nulls in fields the downstream team will rely on, like email. A join that silently drops or nulls out a field is invisible until someone tries to use the data.

Check the aggregate against something independently known. If finance reports total revenue for the last 90 days as roughly $1.2M, and summing total_spend across all customers in this result set comes out to $4M, something is double-counting, most likely a join fanning out rows somewhere upstream of order_totals.

Here is a quick checklist to mentally walk through before sharing a query:

• Does the row count look reasonable?
• Did any joins accidentally multiply rows?
• Are unexpected NULL values appearing?
• Do aggregates roughly match a trusted source?
• Have obvious edge cases been tested?

These checks may not guarantee correctness, but they dramatically reduce the chance of discovering problems after someone has already acted on the results.

Make Intent Obvious

A query that only the author can parse is not really finished, because someone else is going to have to read it during code review, during an incident, or during onboarding. Formatting consistency does a lot of the work here: consistent capitalization for keywords, consistent indentation for joins and conditions, and CTEs that are visually separated so the eye can find the boundaries between stages at a glance. None of these changes what the query does. It changes how quickly a reviewer can understand the query. That is the real cost being paid every time SQL is reviewed.

Instead of compressing multiple joins, filters, and conditions into dense blocks of SQL, keep related logic together, align joins consistently, and format predicates predictably. Reviewers should be able to scan the query and immediately identify filtering, aggregation, and business rules without mentally reformatting the code first.

Good formatting doesn't make SQL execute faster. It makes people understand it faster.

Comments matter, but only in the right place. A comment explaining what a GROUP BY does is noise, because the SQL already says that. A comment explaining why orders are filtered to the last 90 days, or why refunded orders are excluded, is signal, because that logic comes from a business decision the code itself cannot express. The CTE names in the example above already do a lot of that explanatory work for free. Comments should fill in what names cannot carry.

Make Assumptions Explicit

Every business query is full of decisions that are invisible in the result set. Why 90 days and not 60. Why a refunded order does not count toward spend even though it briefly existed as a completed order. Why "repeat" means more than one order rather than two or more, which is the same threshold expressed differently but easy to get wrong if someone has to guess at it later.

Senior engineers do not just write queries, they encode these assumptions so the next reader does not have to reverse-engineer them from the logic. That can be a comment block at the top of the query, like the one above the recent_orders CTE. It can be a CTE name that states the assumption directly, the way high_value_repeat_customers tells you the threshold logic lives in that block before you even read it. What it cannot be is silence, because silence forces the next person to either guess or go find the original author and ask.

This matters more as a query gets reused. The first time someone copies this logic into a new dashboard, they will copy the assumptions along with it, whether those assumptions are documented or not. Making them explicit is the only way to make sure they get copied correctly.

Write SQL for the Next Engineer

None of the techniques in this article are particularly advanced. Naming CTEs well, avoiding SELECT *, validating results, or breaking complex logic into smaller steps won't make you the smartest SQL developer in the room.

What they do is make your SQL easier for the next engineer to understand, review, debug, and safely change.

Junior engineers often optimize for making the query run. Senior engineers optimize for making the next change easy. They write SQL that explains itself, surfaces assumptions, and reduces the amount of information another engineer has to keep in their head.

SQL is read far more often than it is written. Optimize for the reader first, and maintainability naturally follows.