Why CTEs Can Hurt Query Performance on Amazon Redshift

·1 views

A developer discovered that Common Table Expressions (CTEs) behave differently on Amazon Redshift compared to modern platforms like Snowflake or Postgres. Unlike newer query engines, Redshift's older Postgres-derived planner can materialize CTEs as separate intermediate steps rather than inlining them, causing extra I/O and data redistribution across compute nodes. This became apparent during an ETL job on a large events table, where replacing a CTE with a subquery allowed the planner to push filters down earlier and significantly reduced query execution time. The author recommends using subqueries for simple, single-reference logic, reserving CTEs for complex multi-step readability, and considering temporary tables for reused logic. Developers are advised to always inspect query plans using EXPLAIN rather than assuming optimizer behavior is consistent across different warehouse platforms.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Why You Should Always Run a Control Before Explaining Any Data Improvement

A software developer shares two real-world cases where rushing to explain why a metric improved led to near-costly misdiagnoses. In the first case, an anomaly spotted with a feature turned ON appeared equally in a control group with it OFF, revealing the true cause was a timing offset between measurement paths, not the feature itself. In the second case, a retrained model appeared to outperform its predecessor until a cheap threshold tweak on the old model matched it on every metric, exposing the retraining as unnecessary. The core lesson is that a single observation cannot reveal mechanism — only a properly matched control arm, differing in just one variable, can separate a genuine improvement from confounding noise. The author argues that the instinct to immediately explain a positive result is precisely what leads to the most expensive mistakes in data-driven practice.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Why Your AI Accuracy Metric May Be Measuring the Wrong Thing

A developer building an AI translation system relied on a single pass/fail accuracy score combining three axes: correct meaning, correct language, and structural integrity. The system's scores kept improving, suggesting the model was performing well, but reader feedback revealed a blind spot: translations felt flat and lifeless despite being technically correct. The developer realized the metric never captured the quality that actually mattered to readers — emotional resonance and readability. This reflects Goodhart's Law, where optimizing for a proxy measure causes the true goal to be neglected. The episode highlights how defining 'accuracy' is a deliberate choice, and a poorly chosen definition can mask real shortcomings even as scores rise.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

AI Judge Scores Can Mislead: Measure Scoring Noise Before Trusting Improvements

A developer testing AI-based translation scoring discovered that the same content scored twice by the same AI judge produced results 6.1 points apart, despite no changes being made. This variance nearly matched a supposed 7.2-point improvement from adding contextual data, making the gain statistically meaningless. When the developer switched from absolute scoring to pairwise comparison, the context-based improvement vanished, with the win rate settling at an indistinguishable 53%. The incident highlights a critical flaw in using LLMs as quality judges without first measuring their inherent noise and bias. The key takeaway: any measured difference at or below a judge's natural spread should not be treated as a genuine improvement.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Monads in TypeScript Explained Using Promise, Array, and Maybe

A practical guide published on DEV Community breaks down monads in TypeScript by focusing on three core operations: wrapping a value with 'of', transforming it with 'map', and chaining it with 'chain'. The article argues that developers already use two monads daily — Promise and Array — since both support these same operations under familiar names like '.then' and '.flatMap'. Using a real-world example of nested null checks on a User object, the guide demonstrates how the Maybe monad from the '@oofp/core' library can replace verbose conditional logic with a clean, composable pipeline. Once any step in a Maybe chain produces a 'Nothing' value, all subsequent operations are automatically skipped, eliminating the need for manual null guards or early returns. The piece positions understanding this shared pattern as the key to working confidently with any monad in functional TypeScript libraries.

0 comments Read more at DEV Community