SQL is the common language of analytics. Every warehouse - BigQuery, Snowflake, Redshift, Databricks SQL - speaks a dialect of it, dbt compiles to it, Spark and Flink expose it, and BI tools generate it. A data engineer writes more SQL than any other language, and the difference between a query that scans a partition and one that scans a petabyte is usually a few clauses, not a different tool.

Declarative, set-based, and optimizer-driven

You describe the result you want; the engine's optimizer decides how to get it - which join order, which index, whether to broadcast a small table or shuffle both. This is why two queries that return identical rows can differ 100x in cost, and why reading the EXPLAIN plan matters more than micro-optimizing syntax. The mental shift from procedural code is to stop thinking in loops and think in sets: a join is a relationship, not a nested iteration.

Window functions are the analytics workhorse

Window functions compute across a set of rows related to the current row without collapsing them the way GROUP BY does. They are how you write running totals, rankings, period-over-period change, and deduplication - the bulk of real analytics. This example keeps only the latest row per user, a pattern that appears in nearly every pipeline.

SELECT user_id, event_type, created_at
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY created_at DESC
           ) AS rn
    FROM events
) ranked
WHERE rn = 1;

The clauses that decide your bill

On columnar warehouses, cost is driven by how much data the engine has to read. Partition pruning (a WHERE on the partition key), selecting only the columns you need instead of SELECT *, and filtering before joining all cut scanned bytes. The table below lists the levers that matter most.

Lever	Effect
Filter on partition column	Engine skips whole partitions - biggest single win
Select specific columns	Columnar stores read only those columns from disk
Filter before join	Smaller inputs, less shuffle
Clustering / sort keys	Skips blocks within a partition
Avoid SELECT DISTINCT as a fix	Hides a real join-cardinality bug

Dialects diverge at the edges

Core SQL is portable - joins, aggregation, window functions look the same everywhere. The differences show up in date functions, array and struct handling, QUALIFY (Snowflake, BigQuery), and DDL. dbt smooths some of this with macros and adapters, but you still target one warehouse's dialect at a time. Knowing where your engine departs from ANSI SQL prevents the silent surprises, like integer division or implicit time-zone conversion.

Frequently asked questions

Is SQL enough on its own for data engineering? For transformation inside the warehouse, increasingly yes - dbt plus SQL covers a large share of modeling work. You still need a host language (usually Python) for orchestration, ingestion, and anything stateful, but SQL does the heavy analytical lifting.

CTEs or subqueries? Prefer CTEs (WITH) for readability - they name each step and read top to bottom. On most modern engines they are inlined by the optimizer with no performance penalty; on a few older ones a CTE can act as an optimization fence, so check the plan if a query is slow.

Why is my query scanning the whole table? Usually a missing filter on the partition or clustering column, a function wrapped around that column in the WHERE clause (which defeats pruning), or an implicit type cast. Read the EXPLAIN output and check the bytes-scanned estimate.

How do I dedupe rows in SQL? Use ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) and keep rn = 1, rather than SELECT DISTINCT. The window approach lets you choose which row to keep and surfaces the real cardinality instead of masking it.