DEV Community: Neha Christina

SQL Query Execution Order: Why Your Query Doesn't Run Top-to-Bottom

Neha Christina — Sun, 11 Oct 2026 14:46:33 +0000

If you've ever written a query like this and gotten a baffling error, this post is for you:

SELECT
    customer_id,
    SUM(amount) AS total
FROM orders
WHERE total > 100
GROUP BY customer_id;

ERROR: column "total" does not exist

Your first reaction is probably "but I just defined total right there in the SELECT." Totally reasonable reaction — and totally wrong assumption about how SQL works under the hood.

SQL reads like English, top to bottom: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. But that's the order you write it in, not the order the database executes it in. Once you internalize the real execution order, a whole category of beginner SQL errors stops being mysterious.

The order you write vs. the order SQL runs

Here's the logical processing order most SQL engines (Postgres, MySQL, SQL Server, Snowflake, etc.) actually follow:

FROM / JOIN — figure out which tables you're working with and build the initial row set
WHERE — filter individual rows, before any grouping happens
GROUP BY — collapse the remaining rows into groups
HAVING — filter the groups (this is where aggregates like SUM, COUNT, AVG become usable)
SELECT — compute the actual output columns, including aliases
DISTINCT — remove duplicate rows from the result
ORDER BY — sort the final result set
LIMIT / OFFSET — cut the result down to the rows requested

Notice SELECT is step 5, not step 1. That single fact is the root cause of most of the "why doesn't this work" confusion below.

Why WHERE can't see your alias (or your aggregates)

Go back to the broken query:

SELECT
    customer_id,
    SUM(amount) AS total
FROM orders
WHERE total > 100          -- ❌ fails
GROUP BY customer_id;

By the logical order above, WHERE (step 2) runs before SELECT (step 5). At the point WHERE executes, the alias total simply doesn't exist yet — it hasn't been computed. WHERE also runs before GROUP BY (step 3), so it has no concept of aggregated values at all. It only ever sees one raw row at a time.

This is also why you can't write WHERE SUM(amount) > 100 — there's no group to sum yet when WHERE runs.

The fix: filter the group with HAVING

If you want to filter on an aggregated value, you need HAVING, which runs after GROUP BY:

SELECT
    customer_id,
    SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;   -- ✅ works

HAVING operates on groups, not individual rows, so it has access to aggregate functions. Note that most databases require you to repeat the aggregate expression in HAVING rather than reference the SELECT alias — because, again, SELECT hasn't run yet at that point in the logical order. (Some engines, including Snowflake, are lenient about allowing the alias in HAVING as an extension — but relying on standard behavior will keep your SQL portable.)

The rule of thumb:

Filtering raw rows, before grouping → WHERE
Filtering aggregated groups, after grouping → HAVING

So why does ORDER BY get to use the alias?

Here's the part that trips people up next: if aliases don't exist until SELECT runs, why does this work fine?

SELECT
    customer_id,
    SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC;        -- ✅ works

Because ORDER BY is step 7 — it runs after SELECT (step 5). By the time sorting happens, the column alias total has already been computed and is sitting right there in the result set, ready to be referenced. Same underlying rule, opposite outcome, because of where each clause sits in the execution order.

A quick note on GROUP BY and aliases: standard SQL technically doesn't guarantee alias support in GROUP BY either, since it also runs before SELECT. Several engines (Snowflake included) allow it as a convenience, but don't assume every database will.

Does SELECT * change any of this?

No — SELECT * is still step 5 in the logical order, it just means "give me every column that exists at that point" instead of a specific list. The execution order itself doesn't change.

Putting it together: the full flow

Here's a slightly bigger example that touches every clause, with the logical order annotated:

SELECT                              -- 5. compute columns/aliases
    customer_id,
    SUM(amount) AS total
FROM orders                         -- 1. start with this table
JOIN customers USING (customer_id)  -- 1. combine rows
WHERE order_date >= '2026-01-01'    -- 2. filter raw rows
GROUP BY customer_id                -- 3. collapse into groups
HAVING SUM(amount) > 100            -- 4. filter groups
ORDER BY total DESC                 -- 7. sort final result
LIMIT 10;                           -- 8. trim to top 10

Even though you type SELECT first and LIMIT last, the database works through this roughly as: build the joined rows → keep only matching rows → group them → keep only matching groups → compute the output → sort it → trim it.

Cheat sheet

Need to filter individual rows before grouping? Use WHERE.
Need to filter aggregated groups? Use HAVING.
Want to reference a SELECT alias? Safe in ORDER BY (and often GROUP BY, engine-dependent) — never safe in WHERE.
Written order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Wrapping up

This is one of those SQL fundamentals that feels obvious in hindsight but trips up almost every junior dev at least once — usually right before an interview or a production deploy. Once the logical order clicks, errors like "column does not exist" or "aggregate functions are not allowed in WHERE" stop feeling random and start making total sense.

If this was useful, I post SQL, Python, and Snowflake breakdowns like this regularly over on Instagram @techqueen.codes — come say hi.

dbt Macros & Jinja Explained (With Real Examples)

Neha Christina — Sun, 11 Oct 2026 07:26:09 +0000

If you've worked in a dbt project for more than a few weeks, you've probably hit this moment: you need the same small piece of SQL logic — a unit conversion, a status mapping, a date bucket — in five or six different models. You copy it. You paste it. You move on.

Then someone finds a bug in that logic, and now you're grepping the whole repo for every place you pasted it, hoping you don't miss one.

This is exactly the problem dbt macros exist to solve. This post walks through what a macro actually is, how Jinja fits in, a working example you can copy into your own project, and the one gotcha that trips up almost every junior dbt dev the first time a macro breaks.

What a macro actually is

A macro is a reusable piece of SQL, written using Jinja — a templating language originally built for Python web frameworks, which dbt uses as its templating layer on top of plain SQL.

The important mental model: a macro is not a database function. Snowflake (or whatever warehouse you're on) never sees your macro definition, and it never evaluates any Jinja. Instead, dbt reads your .sql files, finds the Jinja templating in them (macro calls, ref(), source(), control-flow tags, etc.), and renders that Jinja into plain SQL before anything is sent to the warehouse. By the time your query reaches Snowflake, every {{ }} and {% %} tag is gone — it's just a SELECT statement.

That's the whole trick: dbt macros let you template SQL, not extend SQL.

Defining a macro

Macros live in your project's macros/ directory, in .sql files. A macro definition looks like this:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100)::float
{% endmacro %}

That's a macro called cents_to_dollars that takes one argument, column_name, and returns the SQL expression to convert a cents value to a float dollar amount. Nothing runs yet — this is just a definition, the same way defining a Python function doesn't execute its body.

Calling a macro

You call a macro from any model using the same double-curly-brace syntax you already use for ref():

-- models/marts/fct_orders.sql
select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_dollars
from {{ ref('stg_orders') }}

When dbt compiles this model, it swaps {{ cents_to_dollars('amount_cents') }} for the macro's rendered body. The compiled SQL that actually hits Snowflake looks like:

select
    order_id,
    (amount_cents / 100)::float as amount_dollars
from analytics.staging.stg_orders

Now imagine you need that same conversion in fct_refunds, fct_payouts, and rpt_revenue. Instead of pasting the three-line cast into each one, you call {{ cents_to_dollars('some_column') }} in each model. If the rounding logic ever needs to change — say, finance wants you to round instead of truncate — you change it in one file, and every model that calls the macro picks up the fix the next time it runs.

Jinja gives you real control flow

Because macros are Jinja, you're not limited to simple text substitution. You get {% if %} conditionals, {% for %} loops, and {% set %} for variables — none of which exist in plain SQL.

A common use case is generating repetitive CASE WHEN columns from a list:

-- macros/pivot_payment_methods.sql
{% macro payment_method_columns() %}
    {% for method in ['bank_transfer', 'credit_card', 'gift_card'] %}
    sum(case when payment_method = '{{ method }}' then amount end) as {{ method }}_amount{{ "," if not loop.last }}
    {% endfor %}
{% endmacro %}

Called inside a select, this loop generates one sum(case when...) column per payment method, so you're not hand-writing (and hand-maintaining) three nearly-identical lines every time a new payment method shows up.

You also don't need to write every macro yourself. The dbt-utils package — installed via packages.yml and dbt deps — ships a library of prebuilt macros for common jobs: dbt_utils.date_trunc(), dbt_utils.pivot(), dbt_utils.generate_surrogate_key(), and more. Before writing a macro from scratch, it's worth checking whether dbt-utils already has it.

The gotcha: macros fail at compile time, not run time

Here's the thing that catches almost everyone the first time: macros are resolved before any SQL reaches the warehouse. If there's a typo in a macro call, a missing argument, or a Jinja syntax error, you will not get a Snowflake error. You'll get a Jinja compilation error, and those read very differently — they're about rendering, not about SQL semantics, which can be confusing if you're expecting a database error message.

The fix is a command most juniors don't reach for soon enough: dbt compile.

dbt compile --select fct_orders

This renders all the Jinja in your project and writes the fully-resolved SQL to target/compiled/<project_name>/models/.../fct_orders.sql, without running anything against the warehouse. When a macro-heavy model breaks, open that compiled file first. You'll see exactly what SQL dbt generated — which immediately tells you whether the problem is in your macro logic (fix the Jinja) or in the resulting SQL itself (fix the query, and now you can debug it like normal SQL).

Making dbt compile a reflex before dbt run whenever a macro is involved will save you a lot of confused debugging sessions.

Quick recap

A macro is reusable SQL written with Jinja templating, defined once in your macros/ folder
You call it from any model with {{ macro_name(args) }} — the same pattern as ref()
dbt renders all Jinja into plain SQL at compile time, before anything touches the warehouse
Jinja gives you {% if %}, {% for %}, and {% set %} — real logic that plain SQL doesn't have
dbt_utils ships prebuilt macros for common tasks — check there before writing your own
When something breaks, run dbt compile first to see the generated SQL before you start debugging

Once this clicks, you'll start noticing copy-pasted SQL everywhere in your project — and that's usually your cue that it belongs in a macro.

Part of an ongoing series on practical SQL, Python, and Snowflake skills for junior data engineers. Follow along on Instagram @techqueen.codes for daily breakdowns like this one.

SQL Indexes Explained (For People Who Just Got Yelled At By a Slow Dashboard)

Neha Christina — Sat, 10 Oct 2026 08:17:10 +0000

At some point in every junior developer's career, a query that ran fine on your laptop turns into a query that times out in production. You didn't change the logic. You didn't change the data model. The only thing that changed is the data got big.

Nine times out of ten, the fix is an index. This post is the explanation I wish someone had given me instead of "just add an index, it'll be fine."

The problem: full table scans

Say you've got an orders table with 50 million rows, and you run this:

SELECT *
FROM orders
WHERE customer_id = 48213;

Without an index on customer_id, the database has no way to know where rows for customer 48213 live. So it does the only thing it can do: it reads every single row in the table, checks whether customer_id matches, and keeps the ones that do. That's called a full table scan.

On a 500-row table, a full scan is instant and nobody notices. On a 50-million-row table, it can mean seconds or minutes instead of milliseconds — which is exactly the kind of thing that turns into a Slack message asking why the dashboard is frozen.

What an index actually is

An index is a separate data structure — most commonly a B-tree — that stores a sorted copy of one or more columns, along with a pointer back to where the full row lives on disk.

The usual analogy is a phone book. If you want everyone with the last name "Smith," you don't read the book cover to cover — you flip straight to the S section because the names are sorted. An index gives your database the same shortcut: instead of scanning every row, it can jump straight to the matching entries and then go fetch just those rows.

Once you add an index:

CREATE INDEX idx_orders_customer
ON orders (customer_id);

the same SELECT query above no longer needs a full scan. The database checks the index, finds exactly which rows have customer_id = 48213, and reads only those. In Postgres and MySQL, this happens automatically — the query planner picks up the new index the next time that column is used in a WHERE, JOIN, or ORDER BY clause, no code changes needed on your end.

Clustered vs. non-clustered indexes

In most row-oriented relational databases (Postgres, MySQL, SQL Server), you'll run into two flavors:

Clustered index — this determines the physical order the table's rows are stored in on disk. Because the data can only be physically sorted one way, a table can have exactly one clustered index. It's usually built on the primary key.

Non-clustered index — a separate structure, stored apart from the table, that holds a sorted copy of the indexed column(s) plus a pointer back to the actual row. A table can have several non-clustered indexes, which is what lets you optimize for more than one type of lookup.

A practical way to think about it: the clustered index is the order the book's pages are physically bound in. A non-clustered index is more like the index at the back of the book — a separate list that tells you which page to flip to.

Indexes are not free

It's tempting, once you learn this trick, to start indexing everything. Don't. Every index comes with real costs:

Writes get slower. Every INSERT, UPDATE, or DELETE has to update not just the table, but every index on that table too. Five indexes means five extra structures to maintain on every write.
Storage goes up. An index is a real, separate structure taking up real disk space — sometimes a significant fraction of the table's own size.
Low-cardinality columns rarely benefit. An index on a boolean column (like is_active) usually doesn't help much, because there are only two possible values — the database still ends up reading a large chunk of the table either way.

A reasonable rule of thumb: index the columns you actually filter, join, or sort on frequently in real queries — not every column "just in case." If you're not sure, look at your slow query logs or EXPLAIN output before adding an index, not after.

The Snowflake exception

This is the part that trips people up if they learned SQL on Postgres or MySQL and then moved to Snowflake (which is the stack this blog mostly covers): Snowflake does not have traditional indexes. There's no CREATE INDEX statement in Snowflake at all.

Instead, Snowflake automatically breaks every table into micro-partitions — contiguous chunks of roughly 50–500MB of uncompressed data — and stores metadata about the min/max values in each one. When you run a query with a filter, Snowflake uses that metadata to skip micro-partitions that can't possibly contain matching rows, a process called pruning. You get a lot of the same benefit as an index (skip the data you don't need) without creating or maintaining anything yourself.

For very large tables where Snowflake's natural micro-partitioning isn't lining up well with how you query the data, you can define a clustering key to influence how the data gets physically organized — but that's a different mechanism solving a similar problem, not an index. (We covered clustering keys in an earlier post if you want the deeper dive.)

The short version: learn indexes for Postgres/MySQL and interviews — plenty of systems still use them, and understanding the concept makes you a better engineer regardless of platform. Just don't go looking for CREATE INDEX in a Snowflake worksheet.

Recap

No index means a full table scan on every query that filters on that column.
An index is a sorted structure that points back to the real rows — it trades a bit of write overhead for much faster reads.
Clustered indexes sort the table itself (one per table); non-clustered indexes are separate lookup structures (you can have several).
Indexes aren't free — they slow down writes and cost storage, so index what you actually query on, not everything.
Snowflake skips traditional indexing entirely in favor of automatic micro-partitioning, with clustering keys as the manual lever for huge tables.

If you found this useful, I post daily breakdowns like this on @techqueen.codes on Instagram — SQL, Python, and Snowflake, explained for junior developers. Drop a comment with the slowest query you've ever had to debug; I might turn it into the next post.

GROUP BY & Aggregate Functions Explained (the WHERE vs HAVING mistake almost everyone makes)

Neha Christina — Mon, 28 Sep 2026 09:25:48 +0000

If you've written more than a handful of SQL queries, you've used GROUP BY. But there's a difference between writing a GROUP BY that happens to run and actually understanding what it's doing — and that gap is exactly where a lot of junior developers get tripped up in interviews and in code review.

This post walks through GROUP BY and the five core aggregate functions from the ground up, then covers the mistake that catches almost everyone at least once: mixing up WHERE and HAVING.

The problem GROUP BY solves

Say you've got an orders table with one row per order:

order_id | region | product_category | sales | order_date
---------+--------+-------------------+-------+------------
1        | West   | Electronics       | 120   | 2026-01-03
2        | East   | Furniture         | 340   | 2026-01-04
3        | West   | Electronics       | 90    | 2026-01-05
...

If this table has 40,000 rows, nobody wants to read all 40,000 of them. What people actually ask for is a summary: total sales per region, average order value per customer, number of orders per day. That's the entire point of GROUP BY — it collapses many rows into one row per group, so an aggregate function has something meaningful to compute.

The five aggregate functions you'll use constantly

COUNT(*)      -- how many rows
SUM(amount)   -- total of a column
AVG(amount)   -- mean of a column
MIN(amount)   -- smallest value
MAX(amount)   -- largest value

Each of these takes many rows as input and returns a single value. Used without GROUP BY, an aggregate function collapses the entire result set down to one row:

SELECT COUNT(*) AS total_orders
FROM orders;

That returns exactly one row — the total order count across the whole table. Add GROUP BY, and instead of collapsing everything into one row, SQL collapses the rows within each group into one row per group.

Writing your first GROUP BY

SELECT region,
       SUM(sales) AS total_sales
FROM orders
GROUP BY region;

Here's what happens under the hood: SQL scans the table and buckets every row by its region value. Then, separately for each bucket, it runs SUM(sales). The result has one row per distinct region — not one row per order.

This is the mental model that matters: GROUP BY defines the buckets, aggregate functions summarize what's inside each bucket.

WHERE vs HAVING — the mistake that catches everyone

This is the part that trips up almost every junior developer at least once, so it's worth slowing down for.

SELECT region,
       SUM(sales) AS total_sales
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region
HAVING SUM(sales) > 10000;

WHERE filters individual rows, and it runs before grouping happens. In the query above, any order placed before January 1st is thrown out before the grouping and summing even start.
HAVING filters entire groups, and it runs after aggregation. Here, it drops any region whose total sales didn't clear $10,000 — but only after SUM(sales) has already been calculated for every region.

Here's the mistake: try writing WHERE SUM(sales) > 10000 instead of using HAVING, and most databases — including Snowflake and Postgres — will throw an error. The reason is order of operations: at the point WHERE is evaluated, grouping hasn't happened yet, so there's no per-group SUM(sales) for WHERE to check against. The aggregate simply doesn't exist yet at that stage of query execution. HAVING exists specifically because SQL needed a filter clause that runs after aggregation.

A useful rule of thumb: if the condition references a raw column (order_date, status, customer_id), it goes in WHERE. If it references an aggregate function (SUM(...), COUNT(...), AVG(...)), it goes in HAVING.

Grouping by more than one column

You're not limited to grouping by a single column:

SELECT region,
       product_category,
       COUNT(*) AS orders,
       SUM(sales) AS total_sales
FROM orders
GROUP BY region, product_category;

This groups by every unique combination of region and product_category. "West / Electronics" and "West / Furniture" become two separate rows in the output, even though they share the same region — because the grouping key is the pair of columns together, not each column independently.

The gotcha that breaks queries

This is the error message you'll eventually run into if you haven't already:

-- Will not run in Snowflake, Postgres, or most standards-compliant databases
SELECT region, customer_name, SUM(sales)
FROM orders
GROUP BY region;

The problem: customer_name appears in the SELECT list, but it's neither wrapped in an aggregate function nor included in GROUP BY. Once you group by region, each output row represents many orders from potentially many different customers — so which customer_name should SQL display for that row? There's no single correct answer, so Snowflake and Postgres refuse to run the query at all.

The fix is either to aggregate the column or add it to the GROUP BY list:

-- Aggregate it
SELECT region, COUNT(DISTINCT customer_name) AS unique_customers, SUM(sales)
FROM orders
GROUP BY region;

-- Or group by it
SELECT region, customer_name, SUM(sales)
FROM orders
GROUP BY region, customer_name;

One thing worth knowing if you work across different database engines: MySQL has historically been more lenient here and will sometimes let a non-aggregated, non-grouped column through, silently picking an arbitrary value for it per group. That's rarely what you actually want, and it's part of why Snowflake and Postgres's stricter behavior is the safer default to reason about — it forces you to be explicit rather than getting a query that runs but returns a value you didn't intend.

Order of operations, all in one place

It helps to know the actual evaluation order of a query with all these clauses, since it's not the same as the order you type them in:

FROM — pick the source table(s)
WHERE — filter individual rows
GROUP BY — bucket the remaining rows into groups
Aggregate functions run — one value computed per group
HAVING — filter out entire groups based on the aggregated values
SELECT — choose which columns/expressions to return
ORDER BY — sort the final result

Keeping this order in your head is what makes WHERE vs HAVING make sense: WHERE runs at step 2, before groups exist, and HAVING runs at step 5, after they do.

A few practice queries to try

If you've got access to any table with a few thousand rows and at least one categorical column, try these:

Count of rows per category
Average of some numeric column per category
Categories where the average exceeds some threshold (this one requires HAVING)
Grouping by two columns instead of one
Combining a WHERE filter and a HAVING filter in the same query

Working through all five is usually enough to make the WHERE/HAVING distinction permanent.

Wrapping up

GROUP BY and aggregate functions are some of the most-used tools in SQL, and they're also one of the most common sources of small, confusing errors for people early in their SQL journey. The fix isn't memorizing syntax — it's holding onto the mental model: GROUP BY defines the buckets, aggregate functions summarize what's inside them, WHERE filters before that happens, and HAVING filters after.

If this was useful, I post SQL, Python, and Snowflake breakdowns like this regularly on Instagram @techqueen.codes — feel free to follow along there for the shorter, visual version of posts like this one.

Snowflake Zero-Copy Cloning Explained

Neha Christina — Sat, 26 Sep 2026 10:35:27 +0000

Zero-Copy Cloning in Snowflake: Full Environments Without the Storage Bill

If you've worked with a traditional database, you know the drill: someone needs a copy of a production table for testing, and now you're looking at a multi-hour copy job and a storage bill that just doubled. Snowflake has a feature that makes that entire problem disappear, and it's one of the most underrated tools in the platform: zero-copy cloning.

This post covers what cloning actually does, why it's nearly free, how to use it, and the gotchas that catch people the first time they rely on it.

The problem it solves

Say you have a 4TB orders table in production and your team wants a realistic dataset to test a migration against. In most databases, your options are:

Run a full INSERT INTO ... SELECT * and wait for it to move every row
Provision new storage and pay for a second full copy of the data
Settle for a stale, partial, or synthetic sample instead of the real thing

Multiply that by every engineer who wants their own sandbox, or every QA environment your team spins up, and the storage cost adds up fast — even though most of that "new" data is identical to what already exists.

What zero-copy cloning actually does

Snowflake stores table data as immutable micro-partitions — compressed, columnar chunks of data that, once written, never change. Table metadata (a layer that's stored separately from the raw data) just keeps a set of pointers to which micro-partitions make up that table's current state.

A clone works at the metadata layer, not the data layer. When you run:

CREATE TABLE orders_dev
CLONE orders_prod;

Snowflake creates a brand-new table object with its own metadata, but that metadata just points at the same micro-partitions the source table already has. No data is read, copied, or moved. That's why the operation completes in about a second regardless of whether the source table is 10MB or 40TB, and why it doesn't cost you additional storage the moment you run it.

Copy-on-write: why it stays cheap after you edit it

The clone isn't a read-only alias — you can insert into it, update it, delete from it, run DDL against it, all independently of the source. The reason this doesn't quietly turn into a full duplicate is Snowflake's copy-on-write model:

Unchanged data stays shared between the original and the clone — both tables' metadata point at the same underlying micro-partitions.
The moment you modify a row in the clone, Snowflake writes a new micro-partition for just that change and updates the clone's metadata to point at it. The original table's micro-partitions, and its metadata, are untouched.

In practice, this means a clone that sits mostly unmodified costs you close to nothing in storage, and a clone that diverges heavily from its source starts to accrue storage costs proportional to how much of it has actually changed — not proportional to its total size.

Cloning is not limited to tables

You can clone at any level of the object hierarchy:

-- clone a single table
CREATE TABLE orders_dev
CLONE orders_prod;

-- clone an entire schema (all tables, views, etc. inside it)
CREATE SCHEMA analytics_dev
CLONE analytics_prod;

-- clone an entire database
CREATE DATABASE app_qa
CLONE app_prod;

Cloning a schema or database recursively clones the objects inside it, using the same copy-on-write mechanics all the way down. This is what makes it realistic to give every engineer on a team their own full-size copy of a production database for local development, without your storage bill scaling linearly with headcount.

Combine it with Time Travel for point-in-time snapshots

Snowflake's Time Travel feature keeps historical versions of table data available for a retention period (1 day by default, configurable up to 90 days on Enterprise Edition and above). You can clone a table as it existed at a specific point in the past, or right before a specific statement ran:

-- clone as of a specific timestamp
CREATE TABLE orders_snapshot
CLONE orders
  AT (TIMESTAMP => '2026-09-25 14:00:00'::timestamp);

-- clone as it looked immediately before a specific statement
CREATE TABLE orders_safe
CLONE orders
  BEFORE (STATEMENT => '01af2b3c-0000-1234-0000-abcdef012345');

This combination is genuinely useful before running a risky backfill, migration, or schema change: take an instant snapshot first, run your change, and if something goes wrong you have an exact, queryable copy of the "before" state to compare against or restore from — without having taken a separate backup in advance.

Where this shows up in real day-to-day work

Per-engineer dev databases. Instead of everyone sharing one dev schema (and stepping on each other's test data), each engineer gets their own full clone of production to work against.
Pre-migration safety nets. Clone the table or schema right before a risky ALTER, backfill, or dbt model rebuild, so you have an instant rollback point.
QA and staging environments. Stand up a QA environment from current production data on demand, run your test suite, and drop it when you're done — the teardown is as cheap as the setup.
One-off experiments. Want to try a schema change or a new transformation logic without touching real data? Clone it, break it, throw it away.

The gotchas

Cloning is close to free and close to instant, but it isn't magic, and there are two things that regularly trip people up:

A clone is a snapshot, not a live sync. Once you run CREATE TABLE ... CLONE, the clone is a point-in-time copy. It does not stay updated as the source table changes afterward. If you need current production data, you have to clone again.

Grants aren't cloned by default. Permissions on the source object (who can select from it, insert into it, and so on) are not automatically carried over to the clone in most configurations — you'll need to explicitly grant access on the new object, or your teammates will hit a permissions wall the moment they try to query their shiny new clone.

It's also worth knowing that Time Travel data itself has a retention window — if you're relying on AT or BEFORE clauses to clone historical states, that history is only available as far back as your table's Time Travel retention period allows.

Wrapping up

Zero-copy cloning is one of those Snowflake features that sounds like a marketing bullet point until you actually need it — and then it quietly saves your team hours of copy jobs and a meaningful chunk of your storage bill. If you're setting up dev environments, running risky schema changes, or just tired of asking for a "small" copy of a production table, this is the tool for the job.

If you found this useful, I write daily SQL, Python, and Snowflake breakdowns for junior data engineers over at @techqueen.codes on Instagram — come say hi.

What is dbt and Why is it in Every Data Engineering Job Listing?

Neha Christina — Thu, 17 Sep 2026 14:30:07 +0000

If you've been in data for more than five minutes recently, you've heard of dbt.

It shows up in job listings constantly. Data engineers rave about it. But nobody ever explains what it actually does in plain English.

This post fixes that.

What is dbt?

dbt stands for data build tool. It transforms your raw data into clean, analysis-ready tables — inside your data warehouse.

Here's the analogy that makes it click:

Your data warehouse (Snowflake, BigQuery, Redshift) is the kitchen. Raw data is the ingredients. dbt is the recipe book — it tells the kitchen exactly how to transform those raw ingredients into clean, ready-to-eat tables.

Three things make dbt different from just writing SQL yourself:

You write SQL — dbt handles the rest. No new language to learn. If you know SELECT, you can write dbt models.

Built-in testing. Data quality checks that run automatically. Bad data gets caught before it reaches dashboards.

Auto-generated documentation. dbt builds a full documentation site from your code and YAML descriptions, so your whole team knows what every table means.

The Problem dbt Solves

Before dbt, data teams had a messy set of problems.

SQL everywhere with no structure. Analysts copy-pasted the same transformation logic into 10 different dashboards. When the logic changed, you had to update it in 10 places.

No way to test your data. You only found out your data was wrong when a stakeholder spotted something odd in a report.

Zero documentation. Nobody knew what the revenue column actually meant or where it came from. Institutional knowledge lived in people's heads.

No version control. SQL lived inside BI tools, not in Git. Changes weren't tracked. You couldn't review them, roll them back, or collaborate on them properly.

dbt was built specifically to solve all four of these problems.

How dbt Works

A dbt model is just a .sql file. You write a SELECT statement, save it, and dbt turns it into a table or view in your warehouse.

-- models/customers.sql
SELECT
  customer_id,
  COUNT(orders.id) AS total_orders
FROM {{ ref('raw_customers') }}
LEFT JOIN {{ ref('raw_orders') }} USING (customer_id)
GROUP BY 1

The {{ ref() }} syntax is how dbt knows which models depend on each other. It builds a dependency graph automatically — so if raw_customers needs to be built before customers, dbt figures that out without you having to specify it.

Then you just run:

dbt run    # builds all your models
dbt test   # runs all your data tests

That's it. dbt compiles your SQL, runs it against your warehouse, and creates the tables.

5 dbt Terms You'll Hear Everywhere

Models — SQL files that define a transformation. Each one becomes a table or view in your warehouse.

Sources — Your raw tables, declared in YAML so dbt knows where the data comes from and can test it.

Tests — Checks like not_null, unique, accepted_values — run automatically with dbt test.

Docs — An auto-generated documentation site built from your models and YAML descriptions. Searchable, linkable, always up to date.

Lineage graph — A visual map showing how data flows through all your models. dbt builds this automatically from your ref() calls.

dbt Core vs dbt Cloud

There are two ways to use dbt:

dbt Core is free and open source. You run it from your terminal or CI/CD pipeline (GitLab, GitHub Actions, etc.). You get full control and all the core features. You manage the infrastructure yourself.

dbt Cloud is the managed platform from the company behind dbt. It adds a browser-based IDE, scheduling, CI/CD, and a polished UI. There's a free tier for individuals, with paid plans for teams.

For most engineers starting out, dbt Core is the right place to begin.

Should You Learn dbt?

Yes. Here's why:

It's in thousands of job listings for data engineer, analytics engineer, and data analyst roles right now.

If you know SQL, you already know 80% of dbt. The learning curve is much lower than people expect.

It makes your SQL reviewable, testable, and version controlled — exactly how software engineers treat their code.

It's used by Airbnb, GitLab, JetBlue, and thousands of data teams worldwide.

And dbt Core is completely free. You can start today.

How to Get Started

Install dbt Core:

pip install dbt-snowflake   # or dbt-bigquery, dbt-redshift

Then follow the official quickstart at docs.getdbt.com — it's one of the best pieces of technical documentation out there.

Does your team use dbt? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 🔥

CTEs vs Subqueries in SQL: Which One Should You Use?

Neha Christina — Thu, 02 Apr 2026 10:44:25 +0000

If you've written SQL for more than a week, you've hit this question.

Do I use a CTE or a subquery here?

Both can solve the same problem. Both produce the same result. So which one is actually better?

The honest answer: it depends. But there are clear rules for when to use each — and once you know them, you'll never second-guess yourself again.

What is a Subquery?

A subquery is a query nested inside another query. It runs inline, right where it's written.

SELECT name, salary
FROM employees
WHERE salary > (
  -- This is the subquery
  SELECT AVG(salary)
  FROM employees
);

The inner SELECT AVG(salary) runs first, produces a single value, and the outer query uses that value in its WHERE clause.

Subqueries can live in SELECT, FROM, WHERE, and HAVING clauses.

Pros: Quick to write for simple filters. No extra syntax needed.

Cons: Gets hard to read when nested more than one level deep. Can't reuse the result elsewhere in the same query.

What is a CTE?

A CTE (Common Table Expression) is a named temporary result set defined at the top of your query using WITH … AS (). You reference it by name below — just like a real table.

-- Define the CTE first
WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)

-- Now use it like a table
SELECT e.name, e.salary
FROM employees e
JOIN avg_salary a ON e.salary > a.avg_sal;

The CTE is defined once at the top and can be referenced multiple times in the main query.

Pros: Very readable — complex logic is broken into named steps. Can be referenced more than once within the same query.

Cons: Slightly more verbose. Not all databases support recursive CTEs.

Same Query, Two Ways

Here's the same problem — find employees earning above the average salary — written both ways:

Subquery version:

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

CTE version:

WITH avg_sal AS (
  SELECT AVG(salary) AS avg
  FROM employees
)
SELECT e.name, e.salary
FROM employees e
JOIN avg_sal a ON e.salary > a.avg;

Same result. Different readability. The CTE version makes the intent clearer — especially as queries get more complex.

When to Use a Subquery

Use a subquery when the logic is simple and short, you only need it in one place, and you're filtering in WHERE or HAVING. For quick one-liners, subqueries are perfectly fine.

When to Use a CTE

Use a CTE when you need to reference the result more than once, the query is complex and needs to be readable, or you're building step-by-step transformations where each step builds on the last.

CTEs are especially powerful when chained together:

WITH
step_one AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
),
step_two AS (
  SELECT dept, avg_sal
  FROM step_one
  WHERE avg_sal > 70000
)
SELECT * FROM step_two;

Each CTE becomes a building block. Much easier to debug than deeply nested subqueries.

Do CTEs Run Faster?

This is the question most people get wrong.

In most modern databases — including Snowflake and BigQuery — the query optimiser treats CTEs and subqueries almost identically. Neither is consistently faster.

The exception: older versions of Redshift and PostgreSQL sometimes materialise CTEs (compute them separately and store the result), which can make CTEs slower for complex queries. If you're on one of these, test both and measure.

The real rule: optimise for readability first. Only swap to subqueries for performance if you've actually measured a difference.

The Cheat Sheet

	Subquery	CTE
Syntax	Inline	`WITH … AS ()`
Readability	Gets messy	Very readable
Reuse in query?	No	Yes
Performance	Same	Same*
Recursive?	No	Yes
Best for	Simple filters	Complex logic

*Performance varies by database — always test.

Note on reusability: A CTE can be referenced multiple times within the single query it's defined in. It does not persist across queries — for that you'd need a temporary table or a view.

The Simple Decision Rule

If the logic fits in 2–3 lines and you only need it once → subquery.

If the logic is complex, reused, or needs to be understood by someone else → CTE.

When in doubt, use a CTE. Readability is worth more than brevity in team environments.

Which do YOU prefer — CTEs or subqueries? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

5 Python Built-ins You’re Not Using (But Should Be)

Neha Christina — Fri, 27 Mar 2026 18:36:43 +0000

You don’t need a library. You don’t need to pip install anything. These five tools are already sitting inside Python, waiting for you to use them.

Most junior developers don’t know they exist. Senior developers reach for them every single day.

Let’s fix that.

1. zip()

Loop over multiple lists at the same time.

How most beginners do it:

names = ['Alice', 'Bob', 'Carol']
scores = [95, 87, 92]

for i in range(len(names)):
    print(names[i], scores[i])

How you should do it:

for name, score in zip(names, scores):
    print(name, score)

zip() pairs up elements from two (or more) iterables and lets you loop over them together. It stops when the shortest one runs out.

You can also use it to combine lists into a dictionary:

name_score = dict(zip(names, scores))
# {'Alice': 95, 'Bob': 87, 'Carol': 92}

2. any() and all()

Check conditions across a list without writing a loop.

The verbose way:

scores = [85, 92, 78, 95]
has_pass = False
for s in scores:
    if s >= 80:
        has_pass = True
        break

The clean way:

has_pass = any(s >= 80 for s in scores)
all_pass = all(s >= 80 for s in scores)

any() returns True if at least one item meets the condition. all() returns True only if every item does. Both short-circuit — they stop checking as soon as the answer is known, which makes them fast.

3. enumerate()

Get the index AND value without range(len()).

The clunky version:

fruits = ['apple', 'banana', 'cherry']
for i in range(len(fruits)):
    print(i, fruits[i])

The Pythonic version:

for i, fruit in enumerate(fruits):
    print(i, fruit)

Want to start counting from 1 instead of 0?

for i, fruit in enumerate(fruits, start=1):
    print(i, fruit)

range(len()) is considered a code smell in Python. If you find yourself writing it, reach for enumerate() instead.

4. map()

Apply a function to every item in a list.

The loop version:

names = ['alice', 'bob', 'carol']
capitalised = []
for name in names:
    capitalised.append(name.upper())

With map():

capitalised = list(map(str.upper, names))

Or with a lambda for custom logic:

doubled = list(map(lambda x: x * 2, numbers))

map() is great for simple, single-function transformations. For more complex logic involving conditions or multiple steps, a list comprehension is usually more readable:

capitalised = [name.upper() for name in names]

Both are valid — pick whichever reads more clearly for your use case.

5. collections

Counter, defaultdict, deque — all built in.

The manual counting approach:

words = ['cat', 'dog', 'cat', 'bird', 'cat']
counts = {}
for word in words:
    if word in counts:
        counts[word] += 1
    else:
        counts[word] = 1

With Counter:

from collections import Counter

counts = Counter(words)
# Counter({'cat': 3, 'dog': 1, 'bird': 1})

top_2 = counts.most_common(2)
# [('cat', 3), ('dog', 1)]

collections also gives you:

defaultdict — like a regular dict, but never raises a KeyError. Automatically creates a default value for missing keys.

from collections import defaultdict
d = defaultdict(list)
d['missing'].append(1)  # No KeyError!

deque — a double-ended queue. Much faster than a list for appending or popping from both ends.

namedtuple — a tuple where you can access items by name instead of index.

The Cheat Sheet

Built-in	What it does	When to use it
`zip()`	Pairs up multiple iterables	Looping over two lists together
`any()`	True if at least one item passes	Checking if any condition is met
`all()`	True if every item passes	Validating a whole list
`enumerate()`	Gives index + value	When you need both in a loop
`map()`	Applies a function to each item	Simple transformations
`Counter`	Counts occurrences	Frequency analysis
`defaultdict`	Dict with default values	Avoiding KeyErrors

The Key Takeaway

None of these require installing anything. They’re all part of Python’s standard library, designed specifically for the problems you solve every day.

The difference between a junior and senior Python developer often isn’t the complex stuff — it’s knowing these small tools exist and reaching for them instead of writing loops from scratch.

Which one did you NOT know about before reading this? Comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💚

A Day in the Life of a Data Engineer (Real Talk, No Filter)

Neha Christina — Tue, 24 Mar 2026 11:15:14 +0000

If you've ever wondered what data engineers actually do all day — this one's for you.

Not the LinkedIn version. The real version.

I've been in tech for 10+ years and I still get asked "so what do you actually do?" at family dinners. This post is my answer.

9:00 AM — Standup & Morning Triage

First thing I do is not open Slack.

I open my monitoring dashboard.

Most pipeline failures happen overnight while nobody's watching. If something broke at 3am, I want to know before the business analyst in another timezone notices their dashboard is showing yesterday's data.

Once I've checked the alerts I jump into standup. Ours is 15 minutes max. What did I do yesterday, what am I doing today, what's blocking me. That's it.

Then I triage tickets. Some days I have three things to do. Some days I have fifteen. Prioritising is a skill they don't teach you in bootcamp but you learn fast.

9:30 AM — Pipeline Review

This is the unglamorous part of the job that nobody talks about.

Every morning I check whether last night's ETL jobs completed successfully. Did the data land in Snowflake? Are the row counts what we'd expect? Did any data quality checks fail?

If something broke, I investigate. This means reading logs, checking error messages, and tracing back through the pipeline to find where it fell over. Sometimes it's a network timeout. Sometimes it's a schema change upstream that nobody told us about. Sometimes it's my own code from two weeks ago coming back to bite me.

Most pipeline fires happen overnight. Mornings are for damage control before anyone notices.

11:00 AM — Deep Work (Headphones On)

This is my favourite part of the day.

I block this time aggressively. No meetings if I can help it. Headphones on. Do Not Disturb on. This is when actual building happens.

What does building look like?

Writing Python ETL scripts. Reading data from an API, transforming it, loading it into Snowflake. Handling edge cases. Writing tests so future-me doesn't break it accidentally.

Building SQL transformations. Using dbt to model data in the warehouse — turning raw event data into clean, business-friendly tables that analysts can actually use.

Debugging. Always debugging. Something that worked last week mysteriously doesn't work today. This is the job.

12:30 PM — Code Review

Before I eat lunch I try to clear my code review queue.

Code review is one of the most valuable things a senior engineer can do for a team. Reading someone else's PR carefully, leaving a thoughtful comment, catching a bug before it hits production — that's real impact.

I also get my own code reviewed. Fresh eyes catch things you're blind to after staring at the same function for two hours.

2:00 PM — Stakeholder Meetings

Yes, data engineers go to meetings. A lot of them.

The afternoon tends to be meeting-heavy. This might be a sync with the data analytics team to tell them a new dataset is ready. It might be a session with a product manager to understand the business requirements for a new pipeline. It might be a cross-team call about an upcoming data model change that'll affect three different teams.

The best data engineers I've worked with are great communicators. They can explain a technical concept to a non-technical stakeholder without talking down to them. That skill is worth as much as knowing Python.

3:30 PM — Documentation & Mentoring

Nobody loves writing documentation but everyone loves finding it when they need it.

I try to keep a rule: if I build it, I document it. What does it do, where does the data come from, what should you do if it breaks. Future me and my teammates will thank present me.

This slot also tends to be when junior engineers come to me with questions. Debugging sessions, code reviews, architecture questions. I was that junior engineer once. Paying it forward matters.

4:00 PM — Wrap Up

The last hour is for closing out.

I look at my ticket list. What did I actually complete today? What's moving to tomorrow? Are there any overnight jobs I need to leave notes about?

I clear my Slack backlog — not the other way around. Slack is not a real-time obligation. I check it a few times a day, not constantly.

4:30 PM — Learning Time (Protected)

This is the 30 minutes most people skip and then wonder why they feel stuck.

I keep this time for growth. Reading a technical blog post. Watching a conference talk. Experimenting with a new Snowflake feature. Trying out a new Python library.

The data engineering landscape changes fast. If you're not learning you're falling behind.

The Tech Stack

Here's what I actually use every day:

Snowflake — cloud data warehouse, where most of our data lives and gets queried.

Python — ETL scripts, data transformation, automation, anything that needs logic.

GitLab — CI/CD pipelines, version control and deployments. Every pipeline change is tracked, reviewed and deployed through GitLab. (You'll see Apache Airflow in a lot of job listings for this role — GitLab is what we use at my company, but Airflow is worth learning too.)

Git / GitHub — version control for everything. Every change tracked, every deployment reviewed.

dbt — transforms raw data inside Snowflake using SQL. Makes data modelling collaborative and testable.

Slack — where everything actually happens. For better or worse.

The Honest Truth Nobody Tells You

60% of the job is debugging pipelines that worked yesterday. The other 40% is writing the pipelines that will break tomorrow.

Communication skills matter as much as coding skills. You can write the most elegant Python in the world but if you can't explain your data model to a product manager, you'll be invisible.

You'll never stop learning. The tools change every year. That's frustrating and exciting at the same time.

Boring days are good days. A day where nothing breaks, all pipelines run, and you shipped one clean PR is a successful day. Don't let anyone tell you otherwise.

Want to Become a Data Engineer?

Start with SQL. Then learn Python. Then learn one cloud data warehouse — BigQuery has the most generous free tier if you want to experiment without spending money.

The path is clearer than it looks from the outside.

Drop a 🙋 in the comments if you're on your way in — I'd love to know where you're starting from 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

Snowflake vs Redshift vs BigQuery: Which One Should You Use?

Neha Christina — Sat, 21 Mar 2026 03:44:27 +0000

If you’ve ever Googled “which cloud data warehouse should I use” and ended up more confused than when you started — this post is for you.

Snowflake, Redshift, and BigQuery are the three biggest names in cloud data warehousing right now. They all do similar things. They all use SQL. They all run in the cloud. So how do you choose?

The answer depends on your situation. Let’s break it down.

What They All Have in Common

Before the differences, here’s what all three share:

They store massive amounts of data — we’re talking billions of rows — and let you query it with standard SQL. They all run entirely in the cloud, so there are no servers to buy or maintain. And they’re all used by major companies at scale.

The differences come down to three things: cost model, ecosystem, and who they’re built for.

❄️ Snowflake

The cloud-agnostic powerhouse.

Snowflake is unique because it runs on AWS, Azure, AND Google Cloud. No other major data warehouse does this. That means if your company uses multiple clouds — or might switch clouds in the future — Snowflake gives you flexibility nobody else does.

What makes Snowflake stand out:

Separation of storage and compute. This is Snowflake’s key architectural insight. Storage and compute are completely independent, so you can scale each one separately and only pay for what you actually use.

Multi-cluster warehouses. 100 analysts running queries at the same time? Each gets their own compute cluster. No one slows anyone else down.

Best-in-class data sharing. You can share live data with partners or other teams without copying it. This is genuinely impressive and something the other two struggle to match.

Time Travel. Accidentally deleted a table? You can recover it — up to 90 days back depending on your plan. This has saved countless data teams from disaster.

Zero-Copy Cloning. Clone an entire database instantly with no extra storage cost. Perfect for test environments.

Pricing: Credit-based, per second of compute. Easy to control once you understand it, but can surprise you if you leave warehouses running.

Best for: Teams that work across multiple clouds, organisations that need to share data externally, and companies that want maximum flexibility.

🔴 Redshift

The AWS native. Powerful if you’re already there.

Amazon Redshift is AWS’s data warehouse, and if your company is already deep in the AWS ecosystem, it’s a natural fit. The integration with S3, Lambda, Glue, Kinesis, and the rest of the AWS stack is seamless in a way that Snowflake and BigQuery simply can’t replicate.

What makes Redshift stand out:

Deep AWS integration. If you’re already using S3 for storage, IAM for access control, or Kinesis for streaming — Redshift plugs in with minimal friction.

Redshift Serverless. The newer serverless option means you don’t have to manage clusters anymore. It scales automatically and you pay only for what you use.

Strong performance for large structured datasets. Redshift uses columnar storage and is highly optimised for analytical queries across large volumes of structured data.

Enterprise security. Deep IAM integration, VPC support, encryption at rest and in transit. If your company has strict security requirements, Redshift ticks all the boxes.

Pricing: Traditionally priced per hour for provisioned clusters, which can get expensive at scale. Redshift Serverless is more flexible but costs can still add up. Reserved instances help significantly if you have predictable workloads.

Best for: Companies already deeply invested in AWS who want tight integration with the rest of their AWS infrastructure.

🔵 BigQuery

Google’s beast. Serverless and stupidly fast.

BigQuery is Google Cloud’s data warehouse, and it’s genuinely impressive. It’s the most truly serverless of the three — there’s no infrastructure to think about whatsoever. You just query data and Google handles everything else.

What makes BigQuery stand out:

Truly serverless. No clusters, no warehouses, no infrastructure decisions. Just write SQL and run it.

Generous free tier. 10GB of storage and 1TB of query processing free every month. This makes it the best option for learning or for small projects where you don’t want to spend anything.

BigQuery ML. You can train and run machine learning models using SQL — no Python required. This is genuinely unique and incredibly powerful for teams that want ML without a dedicated data science team.

Looker Studio integration. Connects directly to Google’s BI and visualisation tools with zero setup.

Pricing: Pay per query based on data scanned. This sounds great until someone writes a poorly optimised query on a huge table and gets a surprise bill. Always use LIMIT and partition your tables.

Best for: GCP-native teams, data science and ML workloads, anyone who wants to start for free, and teams already using Google Workspace or Looker.

The Cheat Sheet

	❄️ Snowflake	🔴 Redshift	🔵 BigQuery
Cloud	Any (AWS/Azure/GCP)	AWS only	GCP only
Pricing	Per second	Per hour	Per query
Free tier	30-day trial	2 months	Always free (1TB/mo)
Setup	Easy	Medium	Very easy
ML built-in	No	Limited	Yes
Data sharing	Best in class	Limited	Good

Which One Should You Pick?

Pick Snowflake if you work across multiple clouds, your team needs to share data externally, or you want maximum long-term flexibility.

Pick Redshift if your company is already all-in on AWS and you need tight integration with the rest of the AWS ecosystem.

Pick BigQuery if you’re on GCP, you want built-in ML capabilities, or you want to start completely free.

The Honest Take

If you’re just starting out and want to learn, BigQuery is the easiest entry point — free tier, no setup, and you can have your first query running in minutes.

If you’re job hunting in data engineering, Snowflake is the one showing up most in job listings right now. It’s worth learning regardless of which one your company uses.

If you’re at an AWS-heavy company, Redshift is likely already in your stack — and getting good at it will make you immediately useful to your team.

Which one does your company use? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

What is Snowflake? A Beginner's Guide to the Cloud Data Warehouse Everyone's Talking About

Neha Christina — Wed, 11 Mar 2026 15:31:16 +0000

If you've seen "Snowflake" in job listings and had no idea what it meant — you're not alone.

It shows up everywhere in data engineering, data analytics, and even business intelligence roles. But nobody ever explains what it actually is in plain English.

Until now.

What is Snowflake?

Snowflake is a cloud-based data warehouse.

That's the technical answer. Here's the human one:

Think of a regular database like a filing cabinet in your office. It works fine when you have a few thousand files. But what happens when you have billions of files, hundreds of people searching at the same time, and the cabinet needs to grow overnight?

It breaks.

Snowflake is the solution. It's a giant, intelligent filing warehouse that lives in the cloud — it can grow instantly, never slows down under pressure, and lets hundreds of people query it simultaneously without fighting over resources.

How is it Different from a Regular Database?

Traditional databases like MySQL or PostgreSQL were designed for a different era. They work great for small-to-medium workloads, but they hit a wall when data gets big.

Here's what happens with traditional databases at scale:

They get slow. Query a table with billions of rows and you might be waiting minutes — or hours.

They're hard to scale. Need more capacity? You have to physically buy and set up new servers. That takes time and money.

They struggle with many users. The more people running queries at once, the slower it gets for everyone.

They're expensive to maintain. You need a dedicated database administrator just to keep things running smoothly.

Snowflake was built from the ground up to solve all four of these problems at once.

How Snowflake Solves It

Massively parallel processing. When you run a query, Snowflake doesn't run it on one machine. It splits the work across thousands of servers simultaneously. A query that would take 10 minutes on a traditional database can take seconds.

Auto-scaling. Need more compute power? Snowflake spins up additional capacity in seconds — automatically. When you're done, it scales back down. You only pay for what you use.

Multi-cluster warehouses. Snowflake can run multiple compute clusters at the same time. 100 analysts running queries simultaneously? Each gets their own resources. No one slows anyone else down.

Separation of storage and compute. This is the key architectural insight that makes everything else possible. In a traditional database, storage and compute are tightly coupled. In Snowflake, they're completely separate — which means you can scale each one independently.

5 Snowflake Terms You'll Hear Everywhere

Once you start working with Snowflake, these five terms come up constantly.

Virtual Warehouse — This is the compute engine. When you run a query, a virtual warehouse does the actual processing. You can have multiple virtual warehouses for different teams or workloads, and they don't interfere with each other.

Database & Schema — Just like in any SQL database, you organize your data into databases and schemas (think of them as folders and subfolders). Your tables live inside schemas.

Time Travel — If someone accidentally deletes a table or overwrites data, you can travel back in time and recover it — up to 90 days back on some plans. This has saved many data teams from disaster.

Zero-Copy Cloning — You can clone an entire database, schema, or table instantly with no additional storage cost. Snowflake doesn't duplicate the actual data; it just creates a pointer to the same underlying data. Perfect for creating test environments.

Snowpipe — Snowflake's continuous data ingestion service. Instead of loading data in batches, Snowpipe automatically loads new data into Snowflake as soon as it arrives in your cloud storage.

Should You Learn Snowflake?

Short answer: yes.

If you're working in data — or want to work in data — Snowflake is one of the most valuable skills you can add to your toolkit right now.

Here's why:

It's in thousands of job listings. Search for data engineer, data analyst, or analytics engineer on any job board and Snowflake shows up constantly.

It's growing fast. Snowflake is now used by over 8,000 companies including Netflix, Adobe, Capital One, DoorDash, and Pfizer.

Your SQL already works. If you know SELECT, WHERE, JOIN, and GROUP BY — you already know how to query Snowflake. The learning curve is much lower than people expect.

It commands higher salaries. Roles that list Snowflake as a requirement consistently pay more than equivalent roles that don't.

How to Get Started

The best way to start is free:

Go to app.snowflake.com and sign up for a 30-day free trial — no credit card required
Create a virtual warehouse (just click through the setup wizard)
Load a sample dataset (Snowflake provides several built-in ones)
Start querying with SQL you already know

Within an hour you'll have run your first Snowflake query and the whole thing will feel far less intimidating.

What's Next?

In a future post I'll cover how Snowflake compares to other cloud data warehouses like BigQuery and Redshift — and when to use each one.

For now: Snowflake is not as scary as it sounds, and if you already know SQL, you're more than halfway there.

Which Snowflake feature surprised you most? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

SQL Window Functions Don't Have to Be Scary 🪟

Neha Christina — Mon, 09 Mar 2026 11:01:18 +0000

Introduction

If you've ever seen OVER (PARTITION BY ... ORDER BY ...) in a SQL query and quietly closed the tab — this post is for you.

Window functions are one of those topics that look intimidating at first but become incredibly powerful once they click. And once you learn them, you'll wonder how you ever wrote SQL without them.

By the end of this post you'll understand 5 essential window functions, when to use each one, and exactly how they differ from each other.

Let's go.

What Even Is a Window Function?

A regular aggregate function like SUM() or COUNT() collapses your rows into a single result. Window functions do calculations across rows — without collapsing them.

-- Regular aggregate: collapses all rows into one
SELECT dept, SUM(salary) FROM employees GROUP BY dept;

-- Window function: keeps all rows, adds a calculated column
SELECT name, dept, salary,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

The key difference: GROUP BY gives you one row per group. Window functions give you all your rows back, plus a new calculated column alongside them.

The syntax always follows this pattern:

function_name() OVER (
  PARTITION BY column   -- optional: defines the group
  ORDER BY column       -- optional: defines row order within the group
)

Function #1: ROW_NUMBER()

Assigns a unique sequential number to each row within a partition. No ties — every row gets a different number.

SELECT
  name, dept, salary,
  ROW_NUMBER() OVER (
    PARTITION BY dept
    ORDER BY salary DESC
  ) AS row_num
FROM employees;

Result:

name	dept	salary	row_num
Alice	Eng	90k	1
Bob	Eng	85k	2
Carol	HR	70k	1
Dave	HR	65k	2

Even if Alice and Bob had the same salary, they'd still get different row numbers.

When to use it:

Get the most recent record per user (filter where row_num = 1)
Deduplicate data
Paginate query results

Function #2: RANK()

Like ROW_NUMBER, but ties get the SAME rank — and the next rank skips.

SELECT
  name, score,
  RANK() OVER (
    ORDER BY score DESC
  ) AS rank
FROM scores;

Result:

name	score	rank
Alice	95	1
Bob	95	1
Carol	88	3
Dave	75	4

Notice rank 2 is missing. Alice and Bob both scored 95 so they share rank 1 — and the next rank jumps to 3.

When to use it:

Ranking students by exam score
Ranking products by sales volume
Any scenario where ties are meaningful and gaps are acceptable

Function #3: DENSE_RANK()

Like RANK, but NO gaps after ties.

SELECT
  name, score,
  DENSE_RANK() OVER (
    ORDER BY score DESC
  ) AS dense_rank
FROM scores;

Result:

name	score	dense_rank
Alice	95	1
Bob	95	1
Carol	88	2
Dave	75	3

Same data — but now Carol gets rank 2, not rank 3.

RANK vs DENSE_RANK — the simple rule:

Use RANK() when gaps make sense (sports — if two people tie for gold, no one gets silver)
Use DENSE_RANK() when gaps look wrong (medal tables, internal employee levels)

Function #4: LAG()

Lets you look at the value from the PREVIOUS row.

SELECT
  month, revenue,
  LAG(revenue, 1) OVER (
    ORDER BY month
  ) AS prev_revenue
FROM sales;

Result:

month	revenue	prev_revenue
Jan	10k	NULL
Feb	12k	10k
Mar	11k	12k
Apr	15k	11k

January has NULL for prev_revenue because there's no row before it.

When to use it:

Month-over-month or year-over-year comparisons
Calculating the change between consecutive rows
Detecting gaps or jumps in a time series

Function #5: LEAD()

Like LAG — but looks at the NEXT row instead.

SELECT
  month, revenue,
  LEAD(revenue, 1) OVER (
    ORDER BY month
  ) AS next_revenue
FROM sales;

Result:

month	revenue	next_revenue
Jan	10k	12k
Feb	12k	11k
Mar	11k	15k
Apr	15k	NULL

April has NULL because there's no row after it.

The Cheat Sheet

Function	Ties	Gaps	Best For
`ROW_NUMBER()`	Different numbers	N/A	Deduplication, pagination, latest record per group
`RANK()`	Same rank	Yes	Sports rankings, exams
`DENSE_RANK()`	Same rank	No	Leaderboards, levels, medal tables
`LAG()`	N/A	N/A	Compare to previous row
`LEAD()`	N/A	N/A	Compare to next row

One Thing to Remember

All window functions follow the same structure:

function() OVER (
  PARTITION BY ...   -- your "group by" (optional)
  ORDER BY ...       -- row order within the group (optional)
)

PARTITION BY is like GROUP BY — but it doesn't collapse your rows. ORDER BY inside OVER() controls how rows are ordered within each window, independently of the query's own ORDER BY.

What's Next?

Once you're comfortable with these 5, explore:

SUM(), AVG(), COUNT() as window functions
NTILE() for splitting rows into buckets
Frame clauses like ROWS BETWEEN for rolling calculations

Which window function confused you the most before reading this? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙