DEV Community: IT-Craftsman

5 SQL window-function traps that show up in data engineering interviews (with runnable DuckDB examples)

IT-Craftsman — Mon, 12 Oct 2026 18:21:49 +0000

Window functions are the most common SQL topic in data engineering interviews, and most candidates know the syntax. The follow-up questions are where people stumble. Here are five traps, each with a tiny example you can paste into DuckDB (DuckDB CLI: curl https://install.duckdb.org | sh or brew install duckdb, then run duckdb; or Python: pip install duckdb, then duckdb.sql("<query>").show()).

Setup:

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1, 1, DATE '2026-01-01', 10),
  (2, 1, DATE '2026-01-01',  5),
  (3, 1, DATE '2026-01-02', 20),
  (4, 1, DATE '2026-01-12',  7)
) t(order_id, customer_id, order_date, amount);

1. The default frame is RANGE, not ROWS

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS default_frame,
       SUM(amount) OVER (ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM orders ORDER BY order_id;

order_id	order_date	amount	default_frame	rows_frame
1	2026-01-01	10	15	10
2	2026-01-01	5	15	15
3	2026-01-02	20	35	35
4	2026-01-12	7	42	42

With an ORDER BY and no frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: rows with the same order_date are peers and are all included. Always write the frame.

2. ROW_NUMBER without a tie-breaker is non-deterministic

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) must number orders 1 and 2 (same date) in some order, and nothing says which one gets number 1. Reruns can return different rows. Add a unique tie-breaker:

SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id
                           ORDER BY order_date, order_id) = 1;

If ties are meaningful ("top 3 including ties"), use RANK or DENSE_RANK instead.

3. LAST_VALUE returns the current row

SELECT order_id,
       LAST_VALUE(amount) OVER (ORDER BY order_date, order_id) AS looks_wrong,
       LAST_VALUE(amount) OVER (ORDER BY order_date, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS actual_last
FROM orders ORDER BY order_id;

looks_wrong is just each row's own amount (10, 5, 20, 7), because the default frame ends at the current row. actual_last is 7 everywhere.

4. WHERE runs before window functions

"Show the running total, but only display January 2nd onwards":

-- wrong: rows before Jan 2 are removed BEFORE the sum is computed
SELECT order_id,
       SUM(amount) OVER (ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders WHERE order_date >= DATE '2026-01-02';      -- 20, 27

-- right: compute first, filter after
SELECT * FROM (
  SELECT order_id, order_date,
         SUM(amount) OVER (ORDER BY order_date, order_id
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
  FROM orders
) WHERE order_date >= DATE '2026-01-02';                  -- 35, 42

Logical order: FROM → WHERE → GROUP BY → HAVING → window → QUALIFY → SELECT → ORDER BY.

5. "Last 7 rows" is not "last 7 days"

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts rows. On sparse data (no orders between Jan 2 and Jan 12) that window reaches back weeks. Use a time-based frame where your engine supports it:

SELECT order_id, order_date,
       SUM(amount) OVER (ORDER BY order_date
            RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW) AS last_7_days
FROM orders ORDER BY order_id;     -- 15, 15, 35, 7

or build a date spine (one row per day, COALESCE missing days to 0) and then use ROWS. BigQuery, for example, only allows numeric RANGE offsets, so you would order by UNIX_DATE(order_date) there.

6. Bonus: a test fixture that catches a missing PARTITION BY

(Thanks to Emily Woods for suggesting this in the comments.) With a single customer, a missing PARTITION BY customer_id is invisible: every row is in the same partition anyway. Add a second customer with an order on the same date and the wrong query fails visibly:

INSERT INTO orders VALUES (5, 2, DATE '2026-01-01', 100);

SELECT order_id, customer_id,
       SUM(amount) OVER (ORDER BY order_date, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS no_partition,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS per_customer
FROM orders ORDER BY order_id;

order_id	customer_id	no_partition	per_customer
1	1	10	10
2	1	15	15
3	1	135	35
4	1	142	42
5	2	115	100

Customer 2's 100 bleeds into customer 1's running total. That's a nice way to turn these snippets into a small regression set: one fixture per trap, each designed so the wrong query visibly fails. (Run this after the other examples; it changes the table.)

The likely follow-up questions in an interview: "How does this scale in Spark?" (a window with ORDER BY sorts each partition; one huge customer causes skew) and "How would you do the 'latest record per key' incrementally?" (deduplicate the batch, then MERGE).

These come from the Data Engineering Interview Kit 2026, which has 80 questions with trade-offs, common mistakes and follow-ups, plus a SQL practice set whose solutions are all executed in DuckDB. Free sample (20 questions): https://payhip.com/b/18A5n

About me: I work as an independent data engineer and DevOps freelancer (IT-Craftsman) and have delivered several data engineering and DevOps projects for large companies. I also work for a technology consulting firm, where I conduct hiring interviews for data engineers and DevOps engineers.

All SQL in this post was executed in DuckDB.

Airflow 3 quietly changed what your cron schedule means

IT-Craftsman — Mon, 12 Oct 2026 18:18:23 +0000

If you upgrade a DAG like this from Airflow 2 to Airflow 3 without changing a line, it can start processing a different day than before:

@dag(schedule="0 0 * * *", start_date=pendulum.datetime(2026, 10, 1, tz="UTC"))
def daily_orders():
    @task
    def extract(ds=None):
        load_orders_for(ds)   # which day is ds?
    extract()

Nothing is broken. The default timetable changed. Here is what is going on and how to make your DAGs independent of it.

Airflow 2: a run covers an interval and starts after it ends

In Airflow 2, [scheduler] create_cron_data_intervals defaults to True, so a bare cron string becomes a CronDataIntervalTimetable. Each run covers a data interval and is started after the interval ends:

Run	logical_date	data interval	actually starts
"Oct 1 run"	2026-10-01 00:00	[Oct 1 00:00, Oct 2 00:00)	shortly after Oct 2 00:00

logical_date (and therefore ds) equals data_interval_start: the day being processed. This confused a generation of engineers ("why does the Oct 1 run start on Oct 2?"), but it made "process yesterday" DAGs work naturally.

Airflow 3: a run is triggered at the cron time

In Airflow 3 the default is create_cron_data_intervals = False, so a bare cron string becomes a CronTriggerTimetable. The run starts at the cron time, logical_date is the trigger time, and data_interval_start and data_interval_end are normally the same moment unless you configure an interval.

Run	logical_date	data interval	actually starts
run at Oct 2 00:00	2026-10-02 00:00	Oct 2 00:00, Oct 2 00:00	Oct 2 00:00

So the run that starts at midnight on October 2nd has ds = 2026-10-02 in Airflow 3, but processed 2026-10-01 in Airflow 2. If your task does WHERE order_date = '{{ ds }}', it now loads the current (incomplete) day.

The official upgrade guide calls this out: if you rely on data_interval_start/data_interval_end or on ds/ts, either set create_cron_data_intervals=True before upgrading or pin an explicit timetable. (Another change while you are there: catchup now defaults to False.)

The fix: stop depending on the default

Pin the timetable in the DAG and give it an explicit interval. Then the DAG behaves the same regardless of the global config:

from datetime import timedelta
import pendulum
from airflow.sdk import dag, task
from airflow.timetables.trigger import CronTriggerTimetable

@dag(
    schedule=CronTriggerTimetable("0 0 * * *", timezone="UTC", interval=timedelta(days=1)),
    start_date=pendulum.datetime(2026, 10, 1, tz="UTC"),
    catchup=False,
)
def daily_orders():
    @task
    def extract(data_interval_start=None, data_interval_end=None):
        # run at 2026-10-02 00:00 gets [2026-10-01 00:00, 2026-10-02 00:00)
        load_orders(window_start=data_interval_start, window_end=data_interval_end)
    extract()

daily_orders()

Note: even with interval=, logical_date (and ds) is the trigger time (Oct 2), so template on data_interval_start/data_interval_end, not on ds.

And make the task read the window it is given, never "now":

# bad: depends on when the task happens to run (retries, backfills, catchup all break)
load_orders(window_start=date.today() - timedelta(days=1))

# good: depends only on the run
load_orders(window_start=data_interval_start, window_end=data_interval_end)

A checklist before you upgrade

grep your DAGs for ds, ds_nodash, ts, logical_date, execution_date (removed in Airflow 3), data_interval_start.
For each DAG with a bare cron string, decide: interval semantics or trigger semantics? Pin it explicitly.
Check manual triggers: Airflow 3's upgrade guide warns not to assume a manually triggered run's data_interval equals the logical_date you typed in.
Make tasks idempotent (delete-and-replace the run's window, or MERGE by key), so a run that processed the wrong day can simply be rerun.
Add a test that imports every DAG and asserts the timetable class you expect.

This is a condensed version of one question from the Data Engineering Interview Kit 2026. Interviewers love it because it tests whether you understand why idempotent, interval-based tasks matter. The free sample with 20 full questions (answers, trade-offs, common mistakes, follow-ups) is here: https://payhip.com/b/18A5n

Facts checked against the Airflow 3 documentation ("Upgrading to Airflow 3", "Timetables").

The easiest way to monitor complex Kubernetes Deployments (using Harbor as an example)

IT-Craftsman — Thu, 01 Oct 2026 12:30:00 +0000

If you’ve ever deployed Harbor Container Registry on Kubernetes, you know it’s an incredibly powerful tool. But you also know that it has a lot of moving parts. A standard Harbor deployment consists of multiple Deployments and StatefulSets: harbor-core, harbor-jobservice, harbor-portal, registry, trivy, redis, and a database.

When everything is running smoothly, Harbor is a joy to use. But what happens when the harbor-jobservice quietly crashes? Your images stop being scanned for vulnerabilities, replication policies fail, and you might not notice until a developer complains hours later.

You need a way to monitor pod availability.

The industry standard is to deploy Prometheus, set up Grafana, write PromQL queries, configure Alertmanager, and maintain all of this infrastructure. If you just want to answer the simple question: "Are my Harbor pods actually running?", this feels like massive overkill.

I wanted a simpler solution. I didn't want to install more heavy monitoring agents inside my cluster just to check if my pods were alive.

That’s why I used AliveSignal to monitor the entire Harbor namespace using a zero-agent, heartbeat-based approach. The best part? It took me exactly 60 seconds. Here is how you can do it too.

The Push vs. Pull Paradigm

Traditional monitoring systems (like Prometheus) use a Pull model. A central server scrapes metrics from your pods. This requires your pods to expose metric endpoints and often requires a dedicated agent running on your cluster nodes.

AliveSignal flips this into a Push model (Heartbeat monitoring). Instead of constantly asking the cluster "Are you okay?", we run a tiny, lightweight, concurrent CronJob inside the cluster that simply pings AliveSignal every minute: "I'm okay!".

If AliveSignal doesn't receive the ping, it alerts you immediately via Email or Webhook.

Step-by-Step: Automating Harbor Health Checks

Usually, setting up individual heartbeat monitors for 7 different deployments is tedious. But AliveSignal has a feature called Kubernetes Auto-Discovery that makes this feel like magic.

Here is the exact workflow.

Step 1: Export your Harbor Namespace

First, we tell Kubernetes to give us the current state of all Deployments and StatefulSets in the Harbor namespace, formatted as JSON.

Run this command in your terminal:

kubectl get deploy,sts -n harbor -o json > harbor.json

(Make sure to replace -n harbor with your actual namespace if it differs).

Step 2: Upload to AliveSignal

Log into your AliveSignal Dashboard. Click on "K8s Auto-Discovery" and upload the harbor.json file you just generated.

Instantly, AliveSignal parses the JSON and detects every single component of your Harbor registry. It automatically creates a separate monitor for harbor-core, harbor-jobservice, trivy, and so on. You don't have to type a single monitor name.

Step 3: Apply the Zero-Agent Manifest

Once the monitors are created, AliveSignal generates a custom Kubernetes manifest file for you: monitoring.yaml.

This file contains a lightweight Kubernetes CronJob. It doesn't require any special permissions (no heavy RBAC, no ClusterRoles) and doesn't run as a DaemonSet. It simply uses curl to concurrently ping the unique URLs for each of your newly created monitors based on pod availability.

Download the file and apply it to your cluster:

kubectl apply -f monitoring.yaml -n harbor
serviceaccount/dms-monitor-sa created
role.rbac.authorization.k8s.io/dms-monitor-role created
rolebinding.rbac.authorization.k8s.io/dms-monitor-rolebinding created
configmap/dms-monitor-config created
cronjob.batch/dms-monitor-cronjob created

That’s it. You are done.

The Result

Within 5 minutes, your AliveSignal dashboard will light up green. You now have deep visibility into your Harbor infrastructure without having installed a single heavy agent.

If the Kubernetes scheduler fails to start your trivy scanner pod, or if your harbor-core pod gets stuck in a CrashLoopBackOff, the internal CronJob will detect the missing pod, stop sending the ping for that specific component, and AliveSignal will alert you before your users notice.

Bonus: Instant Public Status Pages

Because you are running an infrastructure tool that your whole engineering team relies on, transparency is key.

With AliveSignal, you can take those newly created Harbor monitors and instantly publish them to a Public Status Page. Out-of-the-box, it supports custom domains and Dark Mode. You can link this Status Page in your internal developer documentation, so your team always knows if the Container Registry is healthy.

Conclusion

Monitoring doesn't always have to mean maintaining complex metric stacks. For many critical workloads, simple heartbeat monitoring is not just easier to set up—it's more reliable, because it exists completely outside of the infrastructure it is monitoring.

If you want to secure your Harbor Registry, Apache Airflow, or any other complex Kubernetes workload in under a minute, give AliveSignal a try. It’s built for developers who want peace of mind, without the maintenance overhead.