SQL Logic-Storing vs Results-Storing — Zach Wilson

Summary

Zach Wilson (DataExpert.io founder, ex-Meta/Netflix/Airbnb) posted a clean taxonomy of SQL storage options organized by a single useful axis: does this store logic or results?

Original tweet: x.com/@EcZachly (176 likes, 129 bookmarks — March 14, 2024)

The Framework

Logic-storing (re-executes every time it’s called):

• Subquery — lives only for the duration of the containing query
• CTE — same query lifetime as a subquery, just more readable
• View — persists until dropped, but re-runs on every access

Results-storing (executes once, caches the output):

• Temp table — session lifetime
• Materialized view — persists until dropped or refreshed
• Staging table — persists until dropped

The key insight: a CTE called five times in one query runs five times. Most practitioners know this intuitively but have never seen it stated as a formal distinction.

Why This Is the Sanity Check Thesis

The founder flagged the framing: “explaining something people get an intuitive feel for but may not have formal training in.” That is the exact center of gravity for Sanity Check — taking practitioner intuition and giving it a name, a structure, and a mental model worth keeping.

Data engineers who’ve been burned by a slow CTE chain already know this matters. This tweet gives them the vocabulary to explain why to someone who hasn’t been burned yet.

Vault Connections

• Directly extends the DEDP framework: DEDP 6.2 — Dynamic Query Design Pattern covers this territory from the patterns angle; this tweet is the practitioner shorthand version
• Related SQL patterns layer: SQL Patterns — Ergest Xheblati
• Snowflake context: materialized views and temp tables behave differently in Snowflake’s compute/storage separation model — worth a dedicated Data Dots card

Data Dots Candidate

Strong candidate for a Data Dots card: “Logic vs Results — What Does Your SQL Actually Store?” The taxonomy is atomic, the visual could be a two-column chart with lifetimes as the y-axis, and the insight lands in one glance.

Newsletter Angle

Frame it as the question practitioners get wrong silently: “You’ve been running this CTE five times in one query. Here’s what that costs you.”