← SQL Foundations for Data Engineering

▶ Play from here · 8 min

Starts this lesson and continues through 0 more to the end of the course.

Redshift is not PostgreSQL

AWS's own warning

Redshift descends from PostgreSQL, uses a PostgreSQL wire protocol, and accepts a great deal of PostgreSQL syntax. Which is exactly why people get hurt by it.

AWS puts the warning in bold at the top of the page:

"Important. Do not assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical. Make sure to consult the Amazon Redshift Developer Guide SQL commands to understand the often subtle differences." — Unsupported PostgreSQL features (verified 2026-08-12)

"Often subtle differences" is doing a lot of work in that sentence. This lesson is about the ones that change results, not the ones that change syntax — because a syntax difference throws an error and a semantic difference ships to production.

The big one: constraints are declared, not enforced

This is the single most important thing in this lesson, and it is a favourite senior interview question.

The unsupported list includes constraints — unique, foreign key, primary key, check, exclusion. And then:

"Unique, primary key, and foreign key constraints are permitted, but they are informational only. They are not enforced by the system, but they are used by the query planner."

Read that twice. Three facts, and the third is the dangerous one:

1. You can declare a primary key. The CREATE TABLE succeeds.
2. Redshift will not enforce it. You can insert the same key a million times.
3. The query planner believes your declaration and optimises accordingly.

⚠️ Consequence: declaring a primary key that isn't actually unique can produce wrong results, not just slow ones. If the planner is told a column is unique, it may legitimately eliminate a de-duplication step or simplify a join, because you promised there were no duplicates. If there are duplicates, the optimisation is still valid given the promise — the promise was false.

This is a genuinely different failure mode from anything in PostgreSQL, where the constraint would have rejected the duplicate insert at write time.

What to do about it:

• Declare constraints anyway. They help the planner and they document intent.
• But treat them as documentation, and enforce uniqueness yourself — in the load process, with dedup logic (lesson 4), or with a post-load assertion.
• Assert after every load. The check from lesson 4 is the whole defence:

-- This must return zero rows. Run it as a pipeline step, not as a manual habit.
SELECT customer_id, COUNT(*) AS n
FROM dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

• Never declare a primary key you haven't proven. A false declaration is worse than no declaration, because it converts a slow query into a wrong one.

If an interviewer asks "does Redshift enforce primary keys?", the complete answer is: no — but the planner trusts them, so a false declaration can change your results, not just your performance. That last clause is what distinguishes a strong answer.

What else isn't there

Verbatim from the same page, the features Redshift does not support:

The two I'd flag hardest for day-to-day work are the NULLS clause (because it silently changes which row wins in a dedup) and the VALUES list (because it breaks the most natural way to write a small lookup inline, and the failure is at least loud).

No indexes — and why that's fine

New arrivals from PostgreSQL reliably ask where the indexes are. There aren't any, and that isn't a gap.

Redshift is a columnar, distributed, analytic store. Instead of B-tree indexes it uses:

• Sort keys — physical ordering of blocks, which lets it skip blocks entirely
• Zone maps — per-block min/max metadata, so an irrelevant block is never read
• Distribution style — where a row physically lives across slices, which determines whether a join needs network movement

That's the model, and it's a different set of decisions from indexing. Module RS1 covers choosing them. The relevant point here: do not try to reproduce OLTP index thinking in Redshift. The lever is data layout, not auxiliary structures.

⚠️ Python UDFs: end of support has passed

The page carries a banner:

"Amazon Redshift will no longer support the use of Python UDFs after June 30, 2026. We will start enforcing it in phases." — read 2026-08-12

That date is in the past. Enforcement is in progress now, in phases. If you have Python UDFs in Redshift:

1. Inventory them today — they will not keep working.
2. Read the AWS blog post for the migration options. I have not fetched that post for this lesson, so I am pointing you at it rather than summarising migration paths I haven't verified.
3. Treat this as a live deprecation with a passed deadline, not a future planning item.

This is exactly the class of change that a static training course gets wrong, which is why every lesson here carries a verification date.

Working across three dialects

If your platform is Redshift plus Athena/Trino plus Spark SQL — a very common AWS shape, and the one underneath this course's QuickSight track — you are writing in three dialects that agree on most syntax and disagree in the corners.

A working discipline:

1. Write to the intersection where you can. The wrapped-subquery dedup from lesson 4 works everywhere; QUALIFY doesn't.
2. Keep a written note of each divergence you hit, with the engine and the date. This lesson is that note for Redshift.
3. Never port a query between engines without re-running the correctness checks from lessons 2 and 4. Same SQL, different engine, different answer is entirely possible — and NULL ordering and type coercion are the usual culprits.
4. When you can't verify a behaviour from documentation, test it — a five-row table and two minutes beats an assumption that reaches production.

⚠️ I have deliberately not listed Athena/Trino and Spark SQL divergences here, because I did not fetch their documentation for this lesson and I am not going to reconstruct them from memory. Module DL0 covers Athena and PS0 covers Spark SQL, each against their own sources.

Check yourself

1. Does Redshift enforce a primary key? Give the complete answer.
2. Why can a false primary key declaration produce a wrong result rather than a slow one?
3. You need ORDER BY updated_at DESC NULLS LAST inside a window in Redshift. What now?
4. Where are the indexes?
5. You have Python UDFs in Redshift. What's the urgency, as of today?

Answers

1. No. Unique, primary key and foreign key constraints "are permitted, but they are informational only. They are not enforced by the system, but they are used by the query planner." The second clause is the part that matters.
2. Because the planner trusts the declaration. Told a column is unique, it may drop a de-duplication step or simplify a join. If duplicates exist, the plan is valid for the promise you made and wrong for the data you have.
3. That clause isn't supported — the NULLS clause in window functions is on the unsupported list. Control null ordering explicitly instead, e.g. by ordering on a coalesced expression or adding a leading term that sorts nulls where you want them. Verify the exact form on your cluster.
4. There aren't any, and that's by design. Redshift uses sort keys, zone maps and distribution style instead — data layout rather than auxiliary structures.
5. Immediate. End of support was 2026-06-30, that date has passed, and AWS said enforcement happens in phases. Inventory them now and read the AWS migration post.

Teaching this section

• Lead with the constraints question as a straw poll. "Does Redshift enforce primary keys?" Most rooms split. Then read the sentence about the query planner aloud and let it land — the room goes quiet, and it's the most memorable thirty seconds in the module.
• Demo: declare a primary key on a table, insert the same key twice, and select it back. Seeing the duplicate sitting there under a declared PK does more than any slide.
• Good discussion: "Which of our Redshift tables have declared primary keys, and have we ever checked them?" Then run the HAVING COUNT(*) > 1 check on two of them live.
• Watch for: people concluding "so don't declare constraints". That's the wrong lesson — declare them for the planner and for documentation, and enforce them yourself in the pipeline.
• Timing: 20 min presented, 15 discussion and hands-on.