6 SQL Concepts Every Data Engineer Should Know
Many years ago, I was having a conversation with a friend who was looking to break into data engineering.
At the time, I was a recent graduate and had just landed my first data engineering role.
I confidently told him:
“SQL is easy. It shouldn’t take long to learn.”
Five years of working with data later, I can wholeheartedly disagree with my younger self. 😂
SQL is relatively easy to start learning. Writing a SELECT statement, filtering rows with WHERE, joining two tables, and calculating an AVG() isn't particularly difficult.
But becoming genuinely good at SQL is a different story.
As you work with larger datasets and more complicated business requirements, you quickly run into problems that basic SQL doesn’t solve elegantly: preserving row-level detail while calculating aggregates, breaking complex transformations into manageable steps, dynamically generating queries, working with database metadata, and implementing reusable business logic.
SQL is a declarative language, and its syntax is generally easier to pick up than languages such as Python or C#. But that doesn’t mean it is easy to master.
Here are six SQL features and concepts that I think anyone serious about data engineering should learn.
1. Window Functions
Writing SQL statements to conduct simple data analysis on simple data is easy enough using regular SQL queries without window functions. However, it soon becomes apparent that using aggregate functions like AVG() will change the granularity of your analysis, and so a different approach is required.
A common example is finding each employee’s salary along with the average salary for their department. A regular aggregate query with GROUP BY produces one row per department, so it loses the individual employee rows:
SELECT
department_id,
AVG(salary) AS department_avg_salary
FROM employees
GROUP BY department_id;The key difference is that GROUP BY changes the granularity of the result, while a window function performs a calculation across related rows without collapsing them.
That cannot directly return every employee and compare their salary with the department average.
A window function keeps the individual rows while calculating the grouped value:
SELECT
employee_id,
employee_name,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS department_avg_salary
FROM employees;A really good resource that explains Window Functions in-detail is DataWithBaraa.
2. Common Table Expressions (CTEs)
A Common Table Expression (CTE) allows you to define a named intermediate result that can then be referenced by the main query. It allows you to break a complicated query into logical steps by creating intermediate queries and transforming a given table in each step to achieve the desired result.
Similar to window functions, CTEs are commonly used in real-world scenarios where more complex analysis is required. One can use CTEs to create intermediate queries which can often simplify complex analysis by using a multi-step approach.
CTEs begins with the WITH statement and defines an intermediate result and then queries it as if it were a temporary table.
WITH employee_totals AS (
SELECT
employee_id,
SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
),
top_employees AS (
SELECT
employee_id,
total_sales
FROM employee_totals
WHERE total_sales > 100000
)
SELECT
e.employee_name,
t.total_sales
FROM top_employees t
JOIN employees e
ON e.employee_id = t.employee_id;Although this query could possibly be solved using nested SQL statements or another approach, CTEs make the code easily readable & easy to debug.
CTEs become particularly useful when building data transformations because each step can represent a logical stage of the transformation.
This tutorial by DataWithBaraa does a great job explaining CTEs in-detail.
3. Stored Procedures & Database Automation
Creating individual SQL queries are limited in functionality because they must be run manually. Stored procedures package SQL logic into a reusable database object that can accept parameters and perform multiple operations. They can be scheduled to run automatically using external triggers or built-in triggers from your RDMS (Relational Database Managements System).
A stored procedure is essentially a stored program that can be explicitly invoked, potentially by an application, scheduler, orchestration tool, or another database process.
CREATE OR ALTER PROCEDURE dbo.usp_LoadDailySales
@LoadDate DATE
AS
BEGIN
INSERT INTO fact_sales (
sale_date,
customer_id,
amount
)
SELECT
sale_date,
customer_id,
amount
FROM staging_sales
WHERE sale_date = @LoadDate;
END;Stored procedures are used to create automated & dynamic SQL statements that can perform a variety of tasks, including transformations, logging, data-quality checks, and many others.
A stored procedure doesn’t run automatically simply because it exists. It needs to be invoked by an application, orchestration tool, scheduler, or another database process.
A trigger generally executes in response to a database event such as:
INSERTUPDATEDELETE- sometimes DDL events
A scheduler executes something based on time or another scheduled condition.
Stored procedures can be combined with Triggers & schedulers to automate their usage.
In many modern analytics engineering environments, transformation logic that historically lived in stored procedures has increasingly moved into tools such as dbt. This can make transformations easier to test, version, document, and deploy through Git-based workflows.
Ironically, both Snowflake and Databricks have stored procedures in their data platform. Perhaps its because many legacy systems still rely on this important functionality.
4. User Defined Functions (UDFs)
A User-Defined Function is a custom database function that accepts inputs, performs logic, and returns a value or a result set.
Unlike a stored procedure, a UDF is usually designed to be used inside SQL expressions such as SELECT, WHERE, or JOIN.
CREATE FUNCTION dbo.CalculateTax
(
@Amount DECIMAL(10, 2),
@TaxRate DECIMAL(5, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
RETURN @Amount * @TaxRate / 100;
END;There are 2 different types of UDFs:
(1) Scalar Functions — Returns one value
(2) Table-valued Functions — Returns a table that can participate in a query.
SELECT
order_id,
total_amount,
dbo.CalculateTax(total_amount, 8.25) AS tax_amount
FROM orders;UDFs can be extremely useful, but they should be used carefully. Depending on the database engine and function type, calling a function row-by-row can introduce significant performance overhead.
Get Blosher Brar’s stories in your inbox
Join Medium for free to get updates from this writer.
I don’t use UDFs nearly as often as window functions or CTEs, but understanding them becomes useful when you inherit an existing warehouse or work with database-heavy applications.
5. System Catalogs & Metadata
Most RDMS and even modern data platforms like Databricks & Snowflake have system tables where platform Metadata is stored. This includes information about all database objects like column names, column data types, etc.
- What tables, views, procedures, and functions exist
Each data platform has a slightly different name for storing its metadata. For example:
- SQL Server → system catalog views /
sys.* - PostgreSQL → system catalogs /
information_schema - Snowflake →
INFORMATION_SCHEMA - Databricks →
information_schema
Being able to query these tables is helpful because it lets you inspect and manage the structure, configuration, and security of a database through metadata.
Instead of manually inspecting dozens or hundreds of tables, we can query metadata programmatically to discover information such as
- What tables, views, procedures, and functions exist
- Which columns each table contains
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY
TABLE_SCHEMA,
TABLE_NAME,
ORDINAL_POSITION;This concept becomes particularly powerful in data engineering when building metadata-driven pipelines.
6. Procedural Logic & Dynamic SQL
SQL is so much more powerful that just a query language, and learning to create dynamic SQL statements and implementing conditional logic provides significant functionalities.
Control flow is an important programming concept and it can be implemented using IF statement.
DECLARE @LoadType VARCHAR(20) = 'Incremental';
IF @LoadType = 'Full'
BEGIN
TRUNCATE TABLE fact_sales;
INSERT INTO fact_sales
SELECT *
FROM stg_sales;
END
ELSE
BEGIN
INSERT INTO fact_sales
SELECT s.*
FROM stg_sales AS s
WHERE s.last_modified_date > (
SELECT MAX(last_modified_date)
FROM fact_sales
);
END;WHILE statements — Repeats statements while a condition is true.
DECLARE @RowsProcessed INT = 1;
WHILE @RowsProcessed > 0
BEGIN
DELETE TOP (100)
FROM stg_sales
WHERE sale_date < '2020-01-01';
SET @RowsProcessed = @@ROWCOUNT;
PRINT CONCAT('Rows deleted: ', @RowsProcessed);
END;Dynamic SQL statements are useful when the structure of the query must change dynamically.
Common use cases include:
- Selecting from a table whose name is provided at runtime
- Choosing a column for sorting
- Adding optional filters
- Generating SQL for multiple similarly structured warehouse tables
- Creating or altering tables dynamically
- Building pivot queries when the columns are not known in advance
For example, suppose a report should filter by region only when a region is provided:
DECLARE @Region VARCHAR(50) = 'West';
DECLARE @MinAmount DECIMAL(10, 2) = 1000;
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'
SELECT
order_id,
customer_id,
region,
total_amount
FROM dbo.fact_sales
WHERE total_amount >= @MinAmount';
IF @Region IS NOT NULL
BEGIN
SET @Sql += N'
AND region = @Region';
END;
EXEC sys.sp_executesql
@Sql,
N'@Region VARCHAR(50), @MinAmount DECIMAL(10, 2)',
@Region = @Region,
@MinAmount = @MinAmount;The resulting query changes depending on whether @Region is NULL, but the values are passed as parameters.
Dynamic SQL should be used carefully. Building SQL statements by concatenating untrusted user input can introduce SQL injection vulnerabilities. Parameterized dynamic SQL, such as sp_executesql, is significantly safer. Dynamic SQL can also be harder to test, debug, and maintain than static SQL, so it is generally best used when the query structure genuinely needs to change at runtime.
What Should You Learn First?
If you’re relatively new to SQL, I wouldn’t try to learn all six concepts at once. I’d prioritize them roughly like this:
Beginner → Intermediate
- CTEs
- Window functions
- System catalogs / metadata
- UDFs
Intermediate → Advanced
- Stored procedures
- Procedural SQL & dynamic SQL
And before any of these, make sure you’re comfortable with:
SELECT
WHERE
GROUP BY
HAVING
JOIN
CASE
UNION
Subqueries
AggregationsConclusion
When I first told my friend that SQL was easy, I was confusing learning SQL syntax with mastering SQL.
Writing a basic query is easy.
Understanding how to manipulate data at different levels of granularity, break complex transformations into logical steps, work with metadata, build reusable database logic, and safely generate SQL dynamically is where things become much more interesting.
And these six concepts are only the beginning.
If you’re starting a career in data engineering, don’t worry about memorizing every SQL function or becoming an expert in every database feature. Focus on understanding why these tools exist, when to use them, and the trade-offs involved.
That’s when SQL starts becoming less about writing queries and more about solving problems with data.
Hope you enjoyed reading my article and feel free to reach out to me on LinkedIn to connect and learn more about everything data & technology. Please follow me if you are interested in learning more about the modern data stack and associated tools.
