The Other 20 Percent: SQL Lessons That Actually Make You a Senior Developer

Learning to be a SQL developer seems relatively simple when you first get started. Understanding the SELECT, FROM, WHERE, ORDER BY, GROUP BY, and HAVING commands will get you eighty percent of the way there. Learn a few join types, sprinkle in some window functions, and you can fake it until you make it.

Udemy courses and LeetCode tests can help you sharpen your SQL skills and perform admirably in interviews. But there are tricks you only pick up by working alongside more seasoned engineers. Sometimes the difference between code that works and code that works long term is not obvious until a table grows to a hundred million rows, or a NULL value sneaks in where you did not expect one. These are the patterns that rarely show up in a textbook, but they will give you a real edge as you grow into the role.

Part 1: Comparing and Validating Data Between Tables

Finding Missing Records

Early on, I would write a query against one table and use NOT IN against a subquery to compare to the column in the second table.

SELECT *

FROM table1

WHERE column1 NOT IN (

    SELECT column1

    FROM table2

);

This works, but only if there are no NULL values in the second table's column. If even one NULL sneaks in, NOT IN silently returns zero rows for the entire query. The data quality is bad but the performance issue is much worse.  The optimizer falls back to a full table scan instead of using an index. The version below fixes both problems. It handles NULLs correctly and lets the execution plan use an index to seek the missing rows.  Think of this as going to the library to find a book.  If you use the card catalog, you can go directly to the aisle with the book.  Otherwise, you have to scan the entire library taking much more time.

SELECT t1.column1

, t1.column2

, t1.column3

FROM table1 t1

LEFT OUTER JOIN table2 t2

    ON t1.column1 = t2.column1

WHERE t2.column1 IS NULL;

Now let's look at one more pattern. Notice that this uses NOT EXISTS instead of NOT IN. This gives you the same NULL-safe, index-friendly result as the LEFT JOIN version above but without needing the anti-join filter. The optimizer can also stop checking table2 as soon as it finds one match, rather than evaluating the whole subquery, so it's a clean alternative to the LEFT JOIN pattern. SELECT t1.column1

, t1.column2

, t1.column3

FROM table1 t1 WHERE NOT EXISTS (

SELECT 1      

FROM table2 t2

WHERE t1.column1 = t2.column1

);

Comparing Table Totals: Sums vs. Hash Tables

At first glance, comparing rows by running a SUM and ordering them by a ROW_NUMBER looks like a reasonable way to check whether two tables match.

SELECT ROW_NUMBER() OVER (ORDER BY primary_key) AS sortkey

, SUM(column1 + column2 + column3) AS total

FROM table1

GROUP BY primary_key

EXCEPT

SELECT ROW_NUMBER() OVER (ORDER BY primary_key) AS sortkey

, SUM(column1 + column2 + column3) AS total

FROM table2

GROUP BY primary_key;

This has two problems, one subtle and one obvious. The subtle one is more serious.  ROW_NUMBER assigns numbers based on sort position, not by matching primary key values. If even one row is missing or added in the middle of either table, every row after it shifts position, and the query starts comparing unrelated rows to each other. The more obvious problem is that different combinations of numbers can add up to the same total, so two genuinely different rows can slip through undetected. Furthermore, summing several columns together gets expensive fast especially on wide tables.

A hash comparison avoids all of this.

SELECT primary_key AS sort_key,

       HASHBYTES('MD5', CONCAT(CAST(column1 AS VARCHAR(50)), '|',

                                CAST(column2 AS VARCHAR(50)), '|',

                                CAST(column3 AS VARCHAR(50)))) AS row_hash

INTO #HashTable1

FROM table1;

SELECT primary_key AS sort_key,

       HASHBYTES('MD5', CONCAT(CAST(column1 AS VARCHAR(50)), '|',

                                CAST(column2 AS VARCHAR(50)), '|',

                                CAST(column3 AS VARCHAR(50)))) AS row_hash

INTO #HashTable2

FROM table2;

-- Compare the two hash tables

SELECT

    COALESCE(h1.sort_key, h2.sort_key) AS key_col,

    CASE

        WHEN h1.sort_key IS NULL THEN 'MISSING FROM TABLE1'

        WHEN h2.sort_key IS NULL THEN 'MISSING FROM TABLE2'

        ELSE 'VALUES DIFFER'

    END AS difference_type

FROM #HashTable1 h1

FULL OUTER JOIN #HashTable2 h2 ON h1.sort_key = h2.sort_key

WHERE h1.row_hash IS NULL

   OR h2.row_hash IS NULL

   OR h1.row_hash <> h2.row_hash;

Converting each row to a single hashed value sidesteps the sum collision problem entirely, and comparing by primary key instead of row position avoids the cascading mismatch problem. The engine no longer needs to do arithmetic per row. It just compares two hash values. This option is both more accurate and more efficient for wide tables. Summing each row is like checking if two books are the same by counting their total number of pages. A hash table comparison is like checking the exact barcode of each book.

Removing Duplicates

The query below will remove all duplicate records. It works great on a small table with only a few hundred rows.

SELECT DISTINCT *

FROM table1;

However, DISTINCT across every column also requires a full shuffle to compare entire rows against each other. It still runs in parallel across your cluster, but the wide shuffle can erase much of the performance advantage you were counting on.

This next option is much better suited for larger tables. By assigning a row number within each group and keeping only the row numbered one, it ensures you keep exactly one row per key.

SELECT sort_key

, column1

FROM (

    SELECT ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) AS sort_key

    , column1

    FROM table1

) AS A

WHERE sort_key = 1;

This final pattern is best suited for manual testing and debugging, since it's quick to write and spot the exact duplicate data. It's a weaker choice for production pipelines because the join-back touches the table twice. First, it finds the duplicate keys, and then it must retrieve them. When duplicate keys are rare, that second pass is just a handful of cheap index seeks, but as duplication grows, so does the cost of pulling those rows back. The ROW_NUMBER pattern avoids this tradeoff entirely by filtering everything in a single table pass.

SELECT B.*

FROM (

    SELECT column1

    , COUNT(*) AS total

    FROM table1

    GROUP BY column1

    HAVING COUNT(*) > 1

) AS A

INNER JOIN table1 AS B

    ON A.column1 = B.column1;

Checking for Duplicates Without a Primary Key

We generally assume that if a table has a primary key, there are no duplicates. That assumption can be dangerous. I have seen the exact same record with the same timestamp, inadvertently inserted into a table twice. An identity column will still generate a unique primary key for that second row, even though it is a duplicate record.

Part 2: Writing Cleaner Code

COALESCE Instead of CASE WHEN

Once your query is correct, small readability wins start to matter. COALESCE is a good example. Both of the queries below return the same result.

SELECT t1.column1

, CASE WHEN t2.column2 IS NULL

       THEN 0

       ELSE t2.column2

  END AS column2

FROM table1 t1

LEFT OUTER JOIN table2 t2

    ON t1.column1 = t2.column1;

SELECT t1.column1

, COALESCE(t2.column2, 0) AS column2

FROM table1 t1

LEFT OUTER JOIN table2 t2

    ON t1.column1 = t2.column1;

COALESCE says in one line what the CASE statement takes five lines to say. For simple NULL substitution, it is easier to write, easier to read, and easier to maintain.  I use this a lot when I use a LEFT OUTER JOIN and want to replace the NULL with 0.  As discussed below, it keeps a missing dimension match from surfacing as NULL.

Part 3: Thinking Like a Data Architect

  

Slowly Changing Dimensions

A good way to handle slowly changing dimensions is to add a start_date and end_date to each row.   Then set the end_date to a high value like '9999-12-31'. Add an is_current flag and set it to 1 whenever the end_date is '9999-12-31'. Join your fact table on the key and the transaction date when you need historical accuracy, or filter on is_current = 1 for the latest record only. This preserves full type 2 history while letting you query current-state values as if the dimension were type 1.

Data Modeling Using Surrogate Keys

Even when a table already has its own natural primary key, it is good practice to build your own surrogate key. I always use integer values starting at 1. Integer keys index better than natural keys, and they hold up cleanly when you are merging data from multiple sources into one table. I also add a row with a surrogate key of 0 to every dimensional table to represent "unknown." That makes it easy for the reporting team to spot and trace missing records back to their source.  As discussed above, a statement like COALESCE(key,0) AS key ensures all values are populated.

Part 4: Building a Culture of Data Quality

Shift Left Logic

In a medallion architecture, testing often does not begin until the silver or gold layer. But every layer needs to be checked for accuracy, including the earliest one. In the bronze layer, start with something as simple as a row count comparison between your bronze tables and the source system. Catching a discrepancy there is far cheaper than tracing it back from a broken report two layers later.

The same discipline should carry through the rest of the pipeline. In the silver layer, testing shifts from completeness to correctness.  Check for duplicates on your business key, confirm row counts reconcile with bronze (after deduplication logic), and enforce referential integrity. In the gold layer, check the business logic. Reconcile key metrics against a trusted source.  This is a good time to create a hash table from a known source and compare it to your gold table.

Data Governance Early On

The old expression "garbage in, garbage out" carries a lot of weight in data engineering. The best defense is teaching your source system's users to constrain their data and enforce the proper data types before it ever reaches you. When that is not possible, I write a program to validate each data dump against my own requirements. If anything fails validation, I send it back to the source before it ever pollutes the data lake.

Closing Thought

None of these tricks show up in a textbook and none of them will show up on a certification exam either. They show up the first time a NULL value quietly breaks your report, or the first time a missing row turns into a hundred false positives. Learning the eighty percent that gets you through an interview is the easy part. The other twenty percent, the part that keeps your queries correct and your pipelines trustworthy at scale, is what actually makes you a senior developer.