Microsoft Data Analyst Interview Preparation Guide - Mid Level

Approach: create a normalized email column (trim, lowercase, remove display names/quotes, strip +aliases, fix common separators), validate/filter malformed rows, then deduplicate on the normalized value.

Example Power Query (M) steps and functions:

1. Trim whitespace and remove surrounding quotes/display names

• Add Column -> Custom:

Normalized1 = Text.Trim([Email])

• Remove quotes, angle brackets or display name patterns:

Normalized2 = Text.Replace(Text.Replace(Normalized1, """", ""), "<", "")
Normalized2 = Text.Replace(Normalized2, ">", "")

2. Lowercase

Normalized3 = Text.Lower(Normalized2)

3. Split local and domain; remove +aliases in local part

Parts = Text.Split(Normalized3, "@"),
Local = if List.Count(Parts)=2 then Parts{0} else "",
Domain = if List.Count(Parts)=2 then Parts{1} else "",
LocalNoAlias = Text.BeforeDelimiter(Local, "+", Local)  // returns whole local if no +
NormalizedEmail = if Domain<>"" then LocalNoAlias & "@" & Domain else null

4. Fix common malformed characters (spaces, commas instead of @)

NormalizedEmail = Text.Replace(NormalizedEmail, " ", "")
NormalizedEmail = if Text.Contains(NormalizedEmail, ",@") then Text.Replace(NormalizedEmail, ",", ".") else NormalizedEmail

5. Basic validation flag

IsValid = (NormalizedEmail <> null) and Text.Contains(NormalizedEmail, "@") and Text.Contains(Text.BeforeDelimiter(NormalizedEmail,"@"), ".") = false and Text.Length(Text.BeforeDelimiter(NormalizedEmail,"@"))>0 and Text.Length(Text.AfterDelimiter(NormalizedEmail,"@"))>2

(Adjust domain-dot check: ensure domain contains a dot: Text.Contains(Text.AfterDelimiter(...), "."))

6. Filter out or review malformed rows

• Filter where IsValid = true OR move invalids to QA table

7. Remove duplicates

• Use Home -> Remove Rows -> Remove Duplicates on the NormalizedEmail column (Table.Distinct in M)

Notes / rationale:

• Trim + Lowercase ensures case-insensitive de-duplication.
• Removing +aliases (Text.BeforeDelimiter on "+") collapses mailbox tags common in Gmail.
• Splitting local/domain prevents removing + signs in domain or mangling.
• Validate rather than auto-fix risky cases — send malformed rows for review.
• For enterprise domains where + is significant, make alias removal configurable.

Direct answer

Teaching a skill you don't have natural talent for means separating what you know intuitively from what's actually teachable. You diagnose the real gap first, build an explicit, decomposed framework for the skill (even though you perform it by feel), and validate progress by watching the person apply it independently, not by how confident the coaching sessions felt.

Approach to teaching outside your natural strength

Diagnose before prescribing. "Struggles with X" is rarely one problem. Watch or review their actual attempt and separate the layers: is it a knowledge gap (they don't know the structure), a delivery gap (they know the structure but execution is shaky), or a confidence gap (they know it and can do it, but freeze under real stakes). Each needs a different intervention.

Decompose your own tacit skill into explicit steps. If you're good at something without having consciously learned it as a framework, you have to reverse-engineer your own process before you can teach it. Skipping this step and just saying "do what feels right" doesn't transfer anything.

Practice at graduated, increasing stakes. Start with low-stakes reps where mistakes are cheap and recoverable, then move toward the real, higher-stakes version. Jumping straight to the real thing conflates skill-building with performance evaluation in the person's head, which raises anxiety and slows learning.

Give feedback on the mechanism, not just the outcome. "That worked" or "that didn't work" is much less useful than pointing at which specific move in their approach caused the result.

Worked example

Situation: someone you're mentoring is excellent at the core technical work but has a real gap in a skill that doesn't come naturally to you either, say, communicating findings clearly to people outside the immediate team. Their material was always technically sound, but reviews ran long and the point often got lost.

Task: help them close that gap over a defined stretch, without pretending you have natural talent for it yourself.

Action: you watched a recording of one of their sessions together and separated content problems (no clear headline, too much detail up front) from delivery problems (pace, not anticipating pushback). You gave them a simple structure to practice against: state the conclusion first, then the supporting evidence, then the recommendation. You ran a couple of low-stakes rehearsals where you played a skeptical stakeholder, then let them run the real session solo.

Result: over a few sessions, their reviews needed fewer clarifying follow-up questions from the room, and the structure started showing up unprompted in written material too, not just live presentations. The real signal wasn't how the coaching sessions felt: it was watching them handle a session you weren't part of and hearing secondhand that it landed cleanly.

Trade-offs and pitfalls

A common junior-mentor mistake is trying to transfer your own tacit competence directly ("just do what I do") instead of decomposing it. That fails specifically because the skill you're teaching is one you never consciously learned as steps.

Another mistake: avoiding coaching on gaps you don't personally excel at, on the theory you're not qualified. You don't need to be naturally gifted at a skill to teach its structure. You need to be willing to build the explicit framework, which sometimes non-naturals do better than naturals, because they had to learn it deliberately themselves.

The real trade-off is time. Teaching a skill outside your own strength takes longer to prepare for, because you can't rely on instinct in the room. That prep time is where the actual coaching value gets built.

Direct answer

Decide how much exploration time is enough by matching it to the cost of being wrong, not to a fixed rule: a low-stakes, easily-reversible dashboard warrants a light pass, while a decision that's expensive to undo (a pricing change, a model that will run in production) warrants deeper scrutiny before shipping. Communicate the trade-off explicitly in terms stakeholders care about (risk and speed), not in terms of process for its own sake.

Framing the trade-off for stakeholders

Rather than treating "explore thoroughly" and "ship fast" as opposites, frame it as a calibrated bet: propose a minimum viable check (the handful of things that would catch the most likely and most costly failure modes) and commit to a timeline for it, rather than either skipping exploration entirely or insisting on an open-ended deep dive. Being explicit about WHAT could go wrong if you skip a check, and how likely and how costly that failure would be, turns "I need more time" from a vague request into a concrete, weighable trade-off a stakeholder can actually reason about.

Worked example

A stakeholder wants a new dashboard KPI live by end of day. Rather than a blanket "I need a full day to be thorough," propose: "I can ship a first version in two hours after checking the three things most likely to make this KPI wrong (missing data in the source table, a duplicate-row issue I've seen in this data before, and whether the definition matches what you actually mean by this metric). If those come back clean, we ship today with a clear note on what wasn't checked; if any come back dirty, I'll flag it before you present the number." That's a concrete, time-boxed commitment stakeholders can actually evaluate, rather than an open-ended request for more time.

Trade-offs and pitfalls

The failure mode on one side is a rushed number that turns out wrong in front of an executive; the failure mode on the other side is exploration that never actually converges because "just one more check" is always available. Naming an explicit, time-boxed set of checks up front is what prevents both.

Direct answer. Expand each view definition in place, substituting it for a plain subquery, until you're looking at the actual raw tables and joins underneath every layer, then run EXPLAIN against that fully-expanded query rather than the outer view alone, since the outer query's own plan will already reflect the full expansion but reading the SQL text itself, layer by layer, is what tells you where in that stack the expensive work actually lives.

Structured elaboration. A chain of views (a view built on another view, built on another) is transparent to the query planner, which sees straight through to the underlying tables regardless of how many logical layers you've stacked, but it's NOT transparent to a human reading the SQL, who sees only the outermost view's definition unless they deliberately expand each layer. Most databases let you inspect a view's definition directly (a system catalog or an equivalent introspection command); walk that chain, substituting each view reference for its actual underlying query, until you have one fully-expanded query with no view references left, and THAT is the query whose real execution plan you should be reading, cross-referenced against the expanded SQL so you know which line of text a given expensive plan node actually corresponds to.

Worked example. A "customer_summary" view built on top of an "active_customers" view, itself built on top of a raw customers table joined to a status lookup, might look innocuous when you only read the outermost customer_summary definition; expanding both layers might reveal that the underlying join involves an unindexed column, or a function-wrapped predicate, buried two layers down where nobody reading only the top-level report query would think to look.

Trade-offs and pitfalls. It's tempting to optimize the outer, visible query (adding an index that seems related to what the report is doing) without actually finding where the real cost lives inside the view chain; that often produces no improvement at all, since the fix has to target the actual underlying tables and predicates the expensive operator touches, which view-layer abstraction can easily hide from a reader who never expands past the top level.

Federated queries let you read external S3 data directly from the warehouse's query engine without a separate ingestion step, which is attractive for ad-hoc, one-off, or exploratory analysis, but it trades ingestion effort for ongoing query-time cost and weaker governance every time that external data is touched.

Performance trade-offs

A federated query typically has no warehouse-native statistics, indexing, or clustering benefits for the external data (the warehouse cannot apply the same optimizations it would to its own managed tables), and pays the read latency of the external storage system directly on every query, so repeated queries against the same external dataset are each paying that cost fresh, unlike an ingested copy that only pays the transfer cost once.

Cost trade-offs

Federation avoids the storage duplication and ingestion-pipeline cost of copying the data in, which is a real saving for data queried rarely or only once. But for data queried repeatedly, the cumulative federated-query cost (repeated external reads, no benefit from the warehouse's caching or materialized-view mechanisms the way an ingested table would get) can exceed the one-time cost of ingesting it, especially once you account for how much cheaper a warehouse's own storage and compute usually are per query compared to a federated read across a network boundary.

Governance trade-offs

Federated data generally sits outside the warehouse's own access-control, lineage, and data-quality tooling, since it is not truly "in" the warehouse, which makes it harder to apply consistent row-level security, audit who queried what, or trust that the external source has not silently changed shape or been deleted out from under a running query.

Criteria for choosing

Federation is appropriate for genuinely one-off or rarely-repeated exploratory queries against data that is not expected to become a regular part of the analytics workload, where the ingestion pipeline's setup cost would not be recouped by future use. Ingest first when the data will be queried repeatedly (the cumulative federated cost will likely exceed one-time ingestion), when it needs to participate in the warehouse's governance and access-control model consistently with the rest of your analytics data, or when query performance against it matters enough that the warehouse's own optimizations (partitioning, clustering, materialized views) are worth having.

Trade-offs and pitfalls

The common mistake is defaulting to federation for convenience and letting a dataset that was meant to be a one-off exploration quietly become a repeatedly-queried, business-critical source without anyone deciding to formally ingest it; put a lightweight review trigger in place (a usage-frequency threshold, or a periodic audit of federated-query volume by source) so a dataset that has organically become a repeat dependency gets promoted to a properly ingested and governed table rather than staying an ungoverned federated read indefinitely.

GROUP BY 1, 2 (positional) is terser but fragile under refactoring; repeating the full expression is explicit but verbose and error-prone to keep in sync; a CTE (Common Table Expression) with a named alias is usually the most maintainable middle ground.

Structured elaboration

-- Positional: fragile if SELECT list columns get reordered
SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2;
-- Repeated expression: verbose, and easy to accidentally diverge from the SELECT list
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM orders GROUP BY region, DATE_TRUNC('month', order_date);
-- CTE with alias: compute once, group by the readable name
WITH base AS (
  SELECT region, DATE_TRUNC('month', order_date) AS month, amount FROM orders
)
SELECT region, month, SUM(amount) FROM base GROUP BY region, month;

Positional GROUP BY is standard SQL and works everywhere, but if someone later reorders or adds a column to the SELECT list, the grouping silently changes to match different columns, a genuinely dangerous refactoring hazard. Repeating the expression avoids that but means the transformation logic is duplicated and can drift if only one copy is edited. A CTE materializes the computed expression once under a name, so both the SELECT list and the GROUP BY reference the same alias with no duplication.

Worked example

Given orders(region, order_date, amount) with rows (US, 2024-01-05, 100), (US, 2024-01-20, 50), (EU, 2024-01-10, 30), (US, 2024-02-01, 40): the original query, SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2, correctly returns three rows: EU/January 30, US/January 150, US/February 40.

Now suppose a teammate inserts customer_segment as a new first column in the SELECT list (every row here happens to be segment 'B2C') and forgets to update the GROUP BY: SELECT customer_segment, region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2. The GROUP BY clause still says 1, 2, but those ordinals now point at customer_segment and region, not region and the month expression. Because the third column (the month) is no longer part of the grouping, engines diverge sharply on what happens: strict engines like PostgreSQL raise an error (every non-aggregated SELECT column must appear in GROUP BY), while others (SQLite, and MySQL outside strict mode) silently pick an arbitrary month value per group and merge everything else. On the latter, the result silently collapses to two rows, B2C/EU 30 and B2C/US 190, with the January 150 and February 40 for US silently summed together into one bucket, no error and no warning that two different months got merged.

Trade-offs and pitfalls

For a one-off, throwaway query, positional GROUP BY is fine and common in ad hoc analysis. For anything that will live in a dashboard, a scheduled report, or a codebase, prefer the CTE-with-alias pattern: it reads clearly and is safe under later edits to the SELECT list.

Direct answer. Join order matters because the size of each intermediate result depends heavily on which tables are combined first; a cost-based optimizer searches over candidate orderings and picks the one with the lowest estimated total cost, while a purely heuristic or rule-based approach applies a fixed strategy (like "always join in the order written" or "always start from the most selective single-table filter") without weighing actual estimated costs against each other.

Structured elaboration. When you join more than two tables, the database has freedom (for inner joins) to combine them in different orders, and each ordering can produce very different intermediate result sizes at each step, since a poorly-chosen order can produce a huge intermediate table only to filter it down later, while a good order filters aggressively first and keeps every intermediate result small. A cost-based optimizer estimates the size and cost of each candidate ordering, using table and column statistics, and picks the cheapest; because the number of possible orderings grows factorially with the number of tables, most optimizers use heuristics or dynamic programming to prune the search space rather than exhaustively trying every ordering once you're past a handful of tables. A purely heuristic or rule-based approach skips this cost comparison and applies a fixed strategy, which is simpler and faster to plan but can pick a badly suboptimal order when the fixed rule doesn't match the actual data distribution.

Worked example. Joining orders (100 million rows) to customers (10 million rows) to a promotions lookup table (200 rows), where the query also filters promotions.code = 'SUMMER25': an order that joins orders to customers first produces an intermediate result on the order of 100 million rows before ever touching the highly-selective promotions filter; reordering to join promotions (already filtered down to a handful of rows) against orders first, then against customers, keeps every intermediate result small from the very first step. A cost-based optimizer, seeing the promotions filter's high selectivity in its statistics, would generally choose the second order on its own; a purely rule-based system following "join in the order the tables are listed" would only get lucky if the query happened to already be written that way.

Trade-offs and pitfalls. Cost-based join ordering depends entirely on the accuracy of the underlying statistics; with stale or missing statistics, a cost-based optimizer can pick an order just as bad as a naive rule-based one, which is why join-order problems and statistics problems are so often the same root cause wearing different symptoms.

Attribute each purchase's revenue by joining it to the buyer's first and last marketing touchpoint, then aggregate by channel under each model separately: a UNION ALL keeps first-touch and last-touch as two labeled result sets rather than blending them into one number. First-touch credits whichever channel started the relationship; last-touch credits whichever channel closed it. Neither model is more correct on its own: a stakeholder who owns awareness and top-of-funnel spend wants first-touch, while a stakeholder optimizing bottom-of-funnel channels (retargeting, paid search bidding) wants last-touch.

Approach

1. Rank a user's touchpoints by time, once ascending (to find the first touch) and once descending (to find the last touch), using ROW_NUMBER() partitioned by user.
2. Take the rn = 1 row from each ranking as that user's first-touch and last-touch channel.
3. LEFT JOIN purchases to each of those two lookups (not INNER JOIN), so a purchase from a user with zero recorded touchpoints still appears, labeled "unknown," instead of silently disappearing from the total.
4. UNION ALL the two attributed sets, tagging each with an attribution_model label, then GROUP BY model and channel.

Handling ties. If two touchpoints share the exact same touch_time for a user (duplicate event logging, same-second clickstream events), ordering by touch_time alone leaves ROW_NUMBER() unstable: which row lands on rn = 1 can differ between runs. Add a deterministic tiebreaker to the ORDER BY: a touchpoint primary key or ingestion sequence, not another text column like channel, since channel names just sort alphabetically and have nothing to do with which touchpoint actually happened first. The worked example below gives touchpoints a touchpoint_id surrogate key for exactly this purpose and includes a genuine cross-channel tie (user 6) to show it resolving deterministically.

When a stakeholder prefers which model. First-touch fits brand and awareness marketing ("which channel introduces us to buyers"); last-touch fits performance marketing and channels billed on last-click, like paid search bidding ("which channel closes the sale"). Neither handles split credit across a multi-touch path; a stakeholder who needs that is really asking for a fractional or time-decay model, which requires accumulating credit across the whole path, not just the min/max touch.

Worked example

Seed data and query (SQLite):

CREATE TABLE touchpoints (
  touchpoint_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  channel     TEXT,
  touch_time  TEXT
);

CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  purchase_time TEXT,
  revenue     NUMERIC
);

INSERT INTO touchpoints (touchpoint_id, user_id, channel, touch_time) VALUES
  (1, 1, 'google',  '2026-01-01'),
  (2, 1, 'email',   '2026-01-03'),
  (3, 1, 'organic', '2026-01-05'),
  (4, 2, 'social',  '2026-01-02'),
  (5, 3, 'email',   '2026-01-01'),
  (6, 3, 'email',   '2026-01-01'),
  (7, 6, 'zeta',    '2026-01-01'),
  (8, 6, 'alpha',   '2026-01-01');

INSERT INTO purchases VALUES
  (101, 1, '2026-01-06', 100),
  (102, 2, '2026-01-03', 50),
  (103, 3, '2026-01-02', 75),
  (104, 4, '2026-01-02', 30),
  (105, 6, '2026-01-02', 60);

WITH ranked AS (
  SELECT
    user_id, channel, touch_time, touchpoint_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC,  touchpoint_id ASC) AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC, touchpoint_id ASC) AS rn_last
  FROM touchpoints
),
first_touch AS (SELECT user_id, channel FROM ranked WHERE rn_first = 1),
last_touch  AS (SELECT user_id, channel FROM ranked WHERE rn_last  = 1),
attributed AS (
  SELECT p.user_id, p.revenue, COALESCE(ft.channel, 'unknown') AS channel, 'first_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN first_touch ft ON p.user_id = ft.user_id
  UNION ALL
  SELECT p.user_id, p.revenue, COALESCE(lt.channel, 'unknown') AS channel, 'last_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN last_touch lt ON p.user_id = lt.user_id
)
SELECT attribution_model, channel, SUM(revenue) AS total_revenue
FROM attributed
GROUP BY attribution_model, channel
ORDER BY attribution_model, total_revenue DESC;

Result:

┌───────────────────┬─────────┬───────────────┐
│ attribution_model │ channel │ total_revenue │
├───────────────────┼─────────┼───────────────┤
│ first_touch       │ google  │ 100           │
│ first_touch       │ email   │ 75            │
│ first_touch       │ zeta    │ 60            │
│ first_touch       │ social  │ 50            │
│ first_touch       │ unknown │ 30            │
│ last_touch        │ organic │ 100           │
│ last_touch        │ email   │ 75            │
│ last_touch        │ zeta    │ 60            │
│ last_touch        │ social  │ 50            │
│ last_touch        │ unknown │ 30            │
└───────────────────┴─────────┴───────────────┘

User 1's three touches (google, then email, then organic) split their $100 purchase: google gets credit under first-touch, organic gets credit under last-touch. User 2 and user 3 have only one touch each, so both models agree for them. User 4 purchased with no logged touchpoint at all: both models correctly bucket that $30 as "unknown" rather than dropping it (an INNER JOIN would have silently dropped it) or guessing a channel.

User 6 is the real tiebreak case: two touchpoints logged the same second, in different channels ('zeta', touchpoint_id = 7, and 'alpha', touchpoint_id = 8). Alphabetically 'alpha' sorts before 'zeta', so a channel-based tiebreaker would hand the win to 'alpha'; but touchpoint_id is what actually orders them, and touchpoint 7 ('zeta') was ingested first, so 'zeta' wins the tie under both rankings here, exactly the deterministic-by-ingestion-order behavior the tiebreaker is supposed to produce, and the opposite of what alphabetizing on channel would have given.

Trade-offs & pitfalls

• Complexity: two window-function sorts over touchpoints (O(n log n) each) plus a UNION ALL that doubles the row count of purchases. For a very large purchases table, consider whether both breakdowns are actually needed in one query or would be cheaper as two simpler queries.
• Edge cases: users with zero touchpoints (handled by LEFT JOIN + COALESCE, not INNER JOIN); duplicate/tied touch_time values, resolved deterministically here by touchpoint_id, not by channel (demonstrated with user 6 above), since without a real tiebreaker "first touch" would silently change between runs; a touchpoint logged after the purchase itself, since this simple model doesn't check touch_time against purchase_time (add WHERE touch_time <= purchase_time before ranking if that ordering matters to the business).
• Common wrong turn: writing two separate queries, one per model, and never combining them, so the interviewer has to ask for a single result set. Also common: using INNER JOIN instead of LEFT JOIN, which silently drops purchases from untouched users and understates total attributed revenue without any error or warning.

Direct answer: MATERIALIZED tells Postgres to compute a common table expression (CTE, a named WITH-clause subquery) exactly once and store its result for the rest of the statement; NOT MATERIALIZED tells it to treat the CTE like a plain derived-table subquery, inlining its definition into the surrounding query so the planner can push predicates and joins across the CTE boundary. Since Postgres 12, if you don't specify either, the planner picks for you using a documented default: a non-recursive, side-effect-free CTE referenced exactly once is inlined (as if NOT MATERIALIZED); a CTE referenced more than once, or containing a volatile expression, defaults to materializing. Before Postgres 12, there was no choice: every CTE always materialized, acting as what's called an optimization fence, a boundary the planner cannot see or optimize across.

Structured elaboration

MATERIALIZED helps when re-evaluating the CTE would be wasteful:

WITH customer_totals AS MATERIALIZED (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
SELECT a.customer_id, a.total, b.total AS total_again
FROM customer_totals a JOIN customer_totals b ON a.customer_id = b.customer_id;

customer_totals does real aggregation work and is referenced twice; forcing MATERIALIZED guarantees Postgres computes the GROUP BY once and reuses the result for both references, rather than potentially re-running the aggregation per reference.

NOT MATERIALIZED helps when a predicate should reach into the CTE:

WITH recent_orders AS NOT MATERIALIZED (
  SELECT * FROM orders
)
SELECT * FROM recent_orders WHERE created_at >= '2026-06-01';

With NOT MATERIALIZED, this is logically identical to writing created_at >= '2026-06-01' directly against orders; the planner can push the filter down and, if an index exists on created_at, use it, instead of first materializing the entire orders table and filtering afterward.

A case where adding a WITH clause slowed a query down on older Postgres: on Postgres 11 or earlier, wrapping a selective filter in a CTE was enough to lose an index scan that the equivalent inline subquery would have kept, purely because the CTE was always an optimization fence:

-- Pre-12: this always fully materializes large_table before the filter runs
WITH expensive AS (
  SELECT * FROM large_table WHERE complicated_expr(...)
)
SELECT * FROM expensive WHERE small_col = 5;

The fix on old versions was to rewrite the CTE as an inline derived-table subquery (which was never fenced the same way) or, on 12+, add NOT MATERIALIZED explicitly.

The "same CTE referenced three times" claim, tested directly and found to be engine-dependent: the intuitive claim is that forcing NOT MATERIALIZED on a CTE containing RANDOM(), referenced three times in the same statement, makes each reference re-evaluate RANDOM() independently, giving three different values per row. Tested on PostgreSQL 16.14 (docker postgres:16), this is false. Whether the CTE is left at its default, forced MATERIALIZED, or forced NOT MATERIALIZED, all three references return the identical value per row, confirmed both by comparing the output and by EXPLAIN VERBOSE, which shows a single CTE r node computing RANDOM() once, feeding three separate CTE Scan reads rather than three independent scans. A second test isolates why: a two-times-referenced CTE wrapping an expensive but deterministic (IMMUTABLE) function, forced NOT MATERIALIZED, is genuinely inlined (the function is called once per reference, confirmed with a RAISE NOTICE inside the function and with EXPLAIN showing two separate Seq Scan nodes). Swap that same test to a VOLATILE function and the call count drops back to one call per row instead of two, and both references return matching values, the materialized pattern again. PostgreSQL is declining the NOT MATERIALIZED request specifically because the CTE is both multiply-referenced and volatile: honoring it would change how many times a non-deterministic expression runs per output row, a semantic change the planner will not make on a hint alone. The keyword is a request the planner can override for that correctness reason, not an unconditional instruction.

DuckDB (1.4.4, verified) is where the original claim actually holds. It accepts the same MATERIALIZED / NOT MATERIALIZED syntax and defaults to materializing a CTE, matching Postgres 12+, but it does not apply Postgres's volatile-function override: WITH r AS NOT MATERIALIZED (SELECT id, RANDOM() AS v FROM base), referenced three times in a self-join, returns three genuinely different values per row on DuckDB, while the same query left at its default (or forced MATERIALIZED) returns one value reused three times. So the demonstration in this section is real, just on DuckDB rather than PostgreSQL: someone who tests NOT MATERIALIZED against a volatile, multiply-referenced CTE on DuckDB and then assumes the same override applies on Postgres will be surprised the values stop diverging there.

Cross-engine behavior (this is where the canonical framing of "other engines that historically always materialize" needs a correction): pre-12 Postgres is the standard textbook example of an engine that always materializes CTEs, but it is not representative of engines generally. The other major engines split into two different camps, neither of which matches "always materializes":

The practical consequence: a query written and tuned on Postgres 12+ or Oracle, relying on the "referenced 3 times, therefore free, therefore materialized by default" behavior, silently regresses on Snowflake or BigQuery, which sit at the opposite end of the spectrum from pre-12 Postgres and never give you that default for free. The only portable fix there is the one construct that's unambiguous everywhere: materialize explicitly into a real temp table if you need guaranteed single evaluation across engines. A second, narrower consequence sits inside the Postgres/DuckDB pair specifically: the same NOT MATERIALIZED keyword, valid syntax on both, does not mean the same thing on both once a volatile function and multiple references are both present (see the RANDOM() case above), so a query tuned and verified on one of the two is not automatically safe to assume behaves identically on the other.

Trade-offs & pitfalls

• MATERIALIZED/NOT MATERIALIZED originate as Postgres 12+ syntax. DuckDB (1.4.4, verified) also accepts and implements them, so "other engines reject them outright" is not a safe assumption; what actually differs between the two is the override semantics for a volatile, multiply-referenced CTE, not whether the syntax parses. Oracle, SQL Server, Snowflake, and BigQuery do not offer this exact keyword pair, so cross-engine SQL targeting those still needs either engine detection or a rewrite into an explicit temp table.
• Forcing NOT MATERIALIZED on a multiply-referenced CTE wrapping an expensive but deterministic computation really does multiply the work on Postgres: verified directly, a two-times-referenced CTE around a costly IMMUTABLE function, forced NOT MATERIALIZED, calls that function once per reference instead of once total, doubling the cost even though the returned values still match (the function is deterministic, so this is a performance pitfall, not a correctness one). The safe default is to trust the planner unless EXPLAIN shows it materializing something expensive that's actually referenced once, or inlining and duplicating something expensive that's referenced many times.
• A CTE wrapping a volatile function (e.g. RANDOM(), NOW(), a sequence-consuming call) referenced multiple times is exactly the case PostgreSQL protects against by overriding NOT MATERIALIZED back to a single evaluation (verified above), so on Postgres it will not silently give two branches of a report different values for what looks like the same row. That protection is Postgres-specific: on DuckDB (verified above), or with two genuinely separate CTEs, or hand-duplicated inline subqueries, that each independently call the same volatile expression, the totals really can diverge with nothing in the query text obviously wrong, since at that point it is not a materialization decision at all, it is two independent evaluations of a non-deterministic function.
• CTE dependency order and name scoping: within one WITH clause, each CTE can reference the ones defined earlier in the same clause, but not ones defined later or itself (outside of an explicit RECURSIVE CTE), and a CTE name shadows any real table of the same name for the remainder of that statement, a subtle bug source if a CTE is accidentally named the same as a real table it's derived from.

Direct answer
A bar chart's length encodes magnitude, so its axis must start at zero or the visual comparison between bars is distorted; a line chart encodes trend via slope and position, so a non-zero (but clearly labeled) baseline is much less misleading and is sometimes appropriate to make a meaningful change visible.

Structured elaboration

• Bar charts: because length is the encoding channel, and length is judged relative to the baseline, starting at any value other than zero exaggerates the apparent difference between bars. A bar that is visually "twice as tall" should represent a value twice as large; a truncated baseline breaks that guarantee.
• Line charts: the reader interprets slope and relative position, not the absolute area under the line, so a zoomed-in (non-zero-start) y-axis can legitimately help the reader see a real, meaningful change in a metric that moves in a narrow range (e.g. a conversion rate moving from 3.0% to 3.3%).
• When a truncated line-chart axis is acceptable: when the range is clearly labeled (so the reader isn't misled about scale) and the metric's natural range is narrow enough that a zero-based axis would flatten a real signal into an invisible line.
• When zero-based is required regardless of chart type: any time the chart's purpose is to let the viewer compare two values by their visual size (a bar chart, or a filled-area chart), not just their trend.

Worked example
A conversion-rate line chart moving from 3.0% to 3.3% on a 0-100% axis would look flat; zoomed to a 2.5-3.5% range with the axis range clearly labeled, the same real change becomes visible without misleading the viewer about the metric's absolute scale.

Trade-offs and pitfalls
The risk with a zoomed line-chart axis is that a reader who glances without reading the axis labels can still overestimate the magnitude of the change; always label the axis range explicitly and consider a small inset or annotation stating the absolute change.