GitHub has quietly been building the most compelling answer to Claude Code and OpenAI’s Codex CLI — and it just arrived as a standalone product. Copilot CLI is a terminal-native coding agent that lives outside VS Code, competes head-on with Anthropic and OpenAI’s standalone tools, and leans hard into one advantage neither rival can match: native GitHub integration.

“Less // TODO: more done. Run a GitHub-native agent in your terminal that works directly with your issues and pull requests, executes across a /fleet of parallelized subagents, and carries you from /plan to merged code.”

What actually changed

GitHub Copilot has always lived inside editors. With the Copilot CLI, it ships as a first-class standalone terminal agent — comparable in scope to Claude Code (Anthropic) and Codex CLI (OpenAI):

• Install in one line: curl -fsSL https://gh.io/copilot-install | bash or npm install -g @github/copilot
• Included in all plans — Free, Pro, Pro+, Business, Enterprise — no separate billing
• /plan mode: Shift+Tab to outline work before touching code; Copilot proposes, you approve
• /fleet: Spin up parallel subagents across multiple models simultaneously — compare results, pick the best
• /model switching: Change foundation models mid-session (Anthropic, Google, OpenAI all supported)
• GitHub-native MCP: Built-in access to your issues, branches, PRs and labels — no context hunting
• /delegate: Create a branch, implement a change, open a PR — one command
• AGENTS.md support: Define custom instructions and tool access per repo
• Works within existing guardrails: Branch protections, required checks, org policies all apply

The workflow they’re pitching: start in the CLI with /plan, execute with /fleet, refine in /IDE (VS Code), ship via /delegate. The CLI is the on-ramp; the rest of your toolchain stays intact.

Why this matters

The standalone terminal agent market just got a lot more crowded — and GitHub’s entry has structural advantages.

Claude Code is brilliant but Anthropic-only. Codex CLI is powerful but tethered to OpenAI’s models. Copilot CLI is model-agnostic and GitHub-native, which means it can do things the others can’t: pull context from issues, enforce your branch protections automatically, and hand off directly to your existing CI/CD. You don’t swap in a new workflow — you extend the one you already have.

The /fleet feature is also worth watching. Running parallel subagents across different models on the same task and converging on a decision-ready result is genuinely new territory for a shipping product.

What to do

• If you’re on any Copilot plan: Install it now. npm install -g @github/copilot. It’s already included.
• Business/Enterprise teams: Your admin needs to enable Copilot CLI in org settings first.
• Try the Skills exercise: github.com/skills/create-applications-with-the-copilot-cli — hands-on walkthrough from scratch.
• Evaluate against Claude Code and Codex: If you’re already running one of those, Copilot CLI’s GitHub-native MCP integration is the main differentiator to test in your actual workflow.

Source: The New Stack · GitHub Copilot CLI

✏️ Drafted with KewBot (AI), edited and approved by Drew.

A response to Thariq Shihipar’s “HTML is the new markdown” post — and a practical answer for anyone watching their per-request costs creep up.

The tweet that started it

On May 8, Thariq Shihipar — a member of the Claude Code team at Anthropic — posted what is now one of the most-discussed dev takes of the quarter:

“HTML is the new markdown. I’ve stopped writing markdown files for almost everything and switched to using Claude Code to generate HTML for me.”

The case he made was genuinely compelling. HTML, he argued, lets Claude do things markdown simply can’t:

• Inline SVG diagrams instead of fenced ASCII art
• Interactive widgets — sliders, toggles, collapsible sections — so a PR review or architectural doc becomes a navigable artifact instead of a wall of text
• In-page navigation, color-coded severity, real layout
• Semantic structure the model can hang richer reasoning on

Thariq’s example prompt — “Help me review this PR by creating an HTML artifact… color-code findings by severity” — captures the appeal in one line. The output looks like a small internal tool, not a chat log. Once you see it, going back to plain markdown feels like trading a dashboard for a printout.

Simon Willison agreed enough to write it up the same week. Then the dissents arrived.

The counter-argument: this is expensive

The most-shared rebuttal came from Kurtis Redux’s “The Unreasonable Ineffectiveness of HTML”. Three points landed:

1. HTML is 2–4× slower to generate than the markdown equivalent — and roughly 2–4× more output tokens to render the same information.
2. Verbose markup dilutes model attention. The longer the output, the easier it is for the model to lose the plot, repeat itself, or hallucinate structure halfway down.
3. Output tokens are the expensive tokens. Across every major API price card, output is 3–5× the cost of input. Recommending 2–4× more output is a real line item.

Then came the uncomfortable subtext from a few corners of the timeline: Anthropic sells by token. Of course a Claude Code engineer wants you generating more of them.

I don’t think that’s fair to Thariq — he’s been shipping thoughtful, often unflattering-to-Anthropic posts about prompt caching and token efficiency for a year. But the structural concern stands: the people best positioned to tell us how to use these tools also have the most to gain when we use them more.

The real question

The argument has been framed as a binary: rich-but-expensive HTML, or cheap-but-flat markdown. Pick a side.

That framing is wrong. The question that actually matters is:

Can I get the HTML output Thariq is excited about — without paying the HTML token bill?

The answer is yes, and most of it has nothing to do with which model you’re using. It has to do with everything that happens around the model call. That’s the slice I’ve been working on with Lynkr.

What’s actually inflating your token count

Before talking about HTML output, it’s worth being honest about where tokens actually go in a Claude Code-style session. In a typical agentic loop, the output HTML is the smallest chunk. The big spenders are:

• The system prompt and tool definitions — re-sent every single turn. Claude Code ships with 50+ tools, each with verbose JSON schemas. That’s tens of thousands of input tokens per turn before the user has typed anything.
• Conversation history — every prior assistant message, every tool call, every tool result, replayed each step.
• Tool results — cargo build, pytest -v, eslint ., git diff. A failing test run can dump 40k tokens of stack trace.
• MCP tool catalogs — wire up half a dozen MCP servers and your tool schema can balloon past 100k tokens.

The HTML the model writes is real cost, but it’s measured in hundreds-to-low-thousands of output tokens. The HTML-vs-markdown debate is fighting over the tip while the iceberg sits unchallenged.

If you want HTML outputs without a wince at the invoice, the lever isn’t “stop generating HTML.” The lever is “stop sending the model a quarter-million tokens of repeated context to get to that HTML.”

How Lynkr attacks the iceberg

Lynkr is a self-hosted proxy that sits between your coding tool (Claude Code, Cursor, Codex CLI, Cline, Continue) and any of 13+ LLM providers. It speaks Anthropic’s /v1/messages and OpenAI’s /v1/chat/completions, so the client doesn’t know it’s there. What it does in the middle is the interesting part. A few of the layers most relevant to the HTML-output discussion:

0. Preflight short-circuit (zero tokens)

The most extreme expression of the thesis. Before any model call happens, Lynkr can run a user-supplied shell precondition — pytest path/to/test.py, tsc --noEmit, lint --quiet, whatever proves the work is already done. If every command exits 0, Lynkr returns a synthetic “preflight satisfied” response without calling the LLM at all.

The use case sounds niche until you run a real agent loop: a CI-triggered “fix the failing test” request that arrives 90 seconds after the test was already fixed on another branch. An idempotent agent retry. A scheduled refactor job whose work was finished by a previous run. Today, every one of those wastes a full agentic loop — sometimes hundreds of thousands of tokens — to rediscover that the answer is “nothing to do.”

The most expensive HTML output is the one you generate to explain a problem that no longer exists. Preflight is the layer that just doesn’t.

Opt-in via preflight_commands in the request payload, gated on LYNKR_PREFLIGHT_ENABLED=true. Inspired by the CodexSaver routing patterns.

1. Smart tool selection (~50–70% token reduction on tool defs)

Claude Code sends every registered tool definition on every turn. Lynkr classifies the incoming request — is this a code edit, a search, a refactor, a “generate an HTML artifact” task? — and ships only the tools that classification actually needs. The model doesn’t see the 40 it won’t use.

For an “HTML artifact” task specifically, you don’t need Bash, Glob, Git, the test runners, or MCP tools. You need read, write, and maybe web search. The savings compound across every turn of the loop.

2. Tool result compression

When the model calls pytest and gets back 40k tokens of output, Lynkr’s tool-result compressor detects the pattern (test runner, linter, build, git diff) and compresses it before the model sees it on the next turn — usually keeping the failing assertions and dropping the redundant traceback frames. A tee cache holds the full version if anything downstream needs it.

In an agentic HTML-generation flow (“read the codebase, then produce an HTML report”), tool results are typically the largest single token category. Compressing them shrinks every subsequent turn.

3. History compression (Distill-style structural dedup)

Long sessions accumulate near-duplicate state: the model re-reads the same file four times, re-runs a slightly-different version of the same query, repeats its own reasoning. Lynkr’s history compressor deduplicates older turns structurally while preserving the most recent N verbatim. The model still has the context; it doesn’t pay for it twice.

4. Code Mode for MCP (~96% reduction)

If you’ve wired up MCP servers, this one’s almost embarrassing. A typical MCP catalog — Linear, GitHub, Notion, Slack, a few internal ones — is 100+ tool definitions and tens of thousands of tokens every turn. Lynkr’s Code Mode replaces all of them with four meta-tools (listResources, readResource, callTool, searchTools) and lets the model discover the rest at runtime. Same capability surface, ~96% fewer tokens spent on the catalog.

5. Risk-aware smart routing across model tiers

This is the one most directly relevant to the HTML debate. Generating a polished HTML artifact really does want a strong model. But the fifteen prior turns that gathered the data, ran searches, and figured out what to put in that artifact often don’t.

Lynkr routes on two orthogonal axes: a 15-dimension complexity analysis (how hard is the task) and an independent risk score (how dangerous is it if the model gets this wrong). A request that touches auth/*, payments/*, migrations/*, .env, or .github/workflows, or that names high-risk concepts like authentication or deployment, is forced to the COMPLEX tier regardless of complexity score. Cheap tier for the cheap turns; frontier tier whenever the blast radius warrants it.

The four configurable tiers — TIER_SIMPLE, TIER_MEDIUM, TIER_COMPLEX, TIER_REASONING — let you map each to whatever provider:model pair makes sense. A greeting goes to a local Ollama model and costs zero. A file-read summarization goes to Haiku. The final “now write the HTML artifact” call goes to Opus or Sonnet, where it belongs.

Pay frontier prices for frontier work and high-risk work. Pay nothing for the rest.

Optionally, LYNKR_VISIBLE_ROUTING=true attaches a human-readable interaction block to each response — tier, provider, model, risk level, estimated savings — so the routing decision shows up inside Claude Code instead of buried in headers no one reads.

6. Semantic and prompt caching

Identical requests hit the prompt cache (SHA-256 keyed LRU, 5min TTL). Near-identical requests — same intent, different phrasing — hit the semantic cache (embedding cosine similarity at 0.95). For repeated “generate an HTML report of X” prompts within a working session, the second call can be effectively free.

7. Headroom sidecar (optional ML compression)

For users who really want to push it, there’s an optional Python sidecar running LLMLingua-style transforms (Smart Crusher, Continuous Context Reduction, Tool Crusher, Cache Aligner, Rolling Window). Pure context-side compression — the model output doesn’t change, the input it operates on gets smaller.

8. Token budget enforcement

Each model’s context window is tracked. At 85% utilization, Lynkr automatically applies adaptive compression rather than letting the request blow past the window mid-generation. You don’t get the 400-error-then-retry burn that costs you the input tokens twice.

What this looks like in practice

A concrete scenario: you ask Claude Code to “review this PR and produce an HTML report grouped by severity” — exactly Thariq’s example.

Without a proxy layer, that’s 8–12 model turns. Each turn re-sends the full system prompt, 50+ tool definitions, the growing conversation history, and accumulated tool results. The final turn produces ~2,000 tokens of HTML. Total input cost across the run: somewhere between 400k and 1.2M tokens depending on repo size. The HTML output is maybe 0.5% of the bill.

With Lynkr in the middle:

• If the PR’s findings are already merged or the failing check has self-resolved, preflight returns “satisfied” without a single model token spent.
• Smart tool selection trims the per-turn tool-def overhead by ~60%.
• Tool result compression keeps git diff and test output from dominating subsequent turns.
• History compression dedupes the redundant intermediate state.
• Risk-aware routing sends the search/read turns to a cheap tier; the final HTML generation — and anything touching protected paths — goes to your strongest model.
• The semantic cache catches the second time you ask for the same review.

You keep the rich HTML output. You stop paying for the parts of the run that don’t earn their tokens.

The takeaway

Thariq is right. HTML output really is better for the kinds of artifacts coding agents produce — and “we should use markdown because tokens are expensive” is an argument from a 32k-context-window world we no longer live in.

The critics are also right. HTML is more expensive than markdown to generate, the cost matters, and “use the richer format” without a story for cost control is a recommendation that benefits the seller more than the buyer.

What’s missing from both sides of the timeline is the recognition that the output format is not the cost lever. The cost lever is everything upstream of the final response: which tools you send, how you compress prior turns, which tier you route to, what you cache, whether the work even needs to run at all.

Generate the HTML. Then put a smarter proxy in front of the model so you can afford to do it again tomorrow — and so that sometimes, when the answer is already on disk, you don’t have to.

Lynkr is open source (Apache 2.0) and self-hosted: https://github.com/Fast-Editor/Lynkr. Install with npm i -g lynkr, point your coding tool’s base URL at localhost:8080, and you’re routing.

If you’ve measured your own HTML-vs-markdown token deltas in real workflows, I’d love to compare numbers — reply with your results.

LLMs can’t come up with ideas.

The output of an LLM (Large Language Model) tends to be divergent. It moves in the direction of deriving combinations from its training data. Good ideas, on the other hand, are convergent. They solve multiple problems at once with a single mechanism.

When using LLMs, I think it’s important to keep this difference in mind as you proceed.

In this article, I want to describe why it’s difficult for LLMs to design databases, how this may accumulate as small effects even in programming, and what the fundamental reason behind this is.

The gravity of training data overwrites instructions in CLAUDE.md

When you have Claude Code generate SQL, short aliases are often used. Things like staff_dept as sd, orders o, or order_items oi — table names abbreviated to one or two characters.

This itself aligns with widespread convention. Look at Stack Overflow or technical posting sites, or look at SQL textbooks, and you’ll often find short aliases. Claude Code faithfully reproduces this.

In intermediate-level SQL books, short aliases are treated critically. In the programming world, it’s already common sense that short variable names like u or t1 are considered bad.

So you explicitly state in the project’s CLAUDE.md, “Do not use table aliases; write them out in full,” but Claude Code occasionally ignores this. Not frequently, but it happens repeatedly. Claude itself knows that “it’s better not to use meaningless aliases like single characters.” Claude would understand this if it reasoned about it on its own. It’s also written in CLAUDE.md. And yet, as t1 still ends up in the SQL.

The aliases make the text shorter, but at the design level, they lose information about “what this table means.”

What this seems to suggest is that LLMs are strongly pulled by the statistical majority of their training data. Instructions work locally, but especially when tackling difficult problems, they seem to tend to be overwritten by the training data.

What an LLM outputs is the “plausible continuation token” from the perspective of the training data distribution. Essentially, it’s probabilistic interpolation, and the statistical tendencies of the training data become its output tendencies as-is.

In the case of SQL aliases, the overwhelming majority of SQL statements in the training data use aliases. Even if you write “don’t use them” in CLAUDE.md, that’s just one instruction in one document, competing head-on with patterns seen across enormous amounts of SQL during training. When directly instructed, it works, but when attention isn’t directed there, the training data side tends to win.

Database design is difficult with LLMs

I believe that “designing databases with LLMs is difficult.”

Code written by programming experts is published in vast quantities on GitHub, but production schemas designed by database experts don’t exist much on the internet. Many are non-public internal assets of companies. On the other hand, there are tons of beginner-level “getting started” blog posts. LLMs get pulled toward beginner designs.

LLMs learn not only from schema files, but also from expert discussions on dba.stackexchange.com, technical books on normalization and index design, and migration files from production OSS like GitLab, Discourse, and Redmine. These are higher quality than the “I tried it” articles, but they have the aspect of sacrificing relational design for the sake of generality.

In addition to the bias in training data, there’s also the issue of missing context.

The system’s access patterns, data volume and growth rate, organization-specific business rules, and future feature plans — these are invisible from the outside. They’re not written in the codebase or schema, and even if there are documents written somewhere, they’re meaningless unless learned in strong association with the DB schema.

DB design needs to proceed while considering multiple query patterns, multiple business constraints, and multiple future requirements. And even if all the information were provided, there’s concern that it may still be difficult for an LLM.

In the past, I asked Claude Code to improve a slow SQL query. In an environment where I could connect to the DB, investigate execution plans, and iterate on SQL through trial and error, I requested, “Please make this faster.”

Claude Code’s suggestions made it somewhat faster but didn’t lead to a fundamental solution. Giving up, the solution I came up with was to swap the starting point of the FROM, reverse the direction of the JOIN, and reverse the WHERE search conditions as well. Then I added functional indexes and partial indexes, making it possible to use index scans as much as possible.

When I asked Claude Code afterward, it knew each individual optimization as knowledge, understood that this approach would work, and could explain it in more detail. But it couldn’t come up with it.

What does this mean?

What LLMs seem to be good at is “solving a given problem by improving it little by little.” Adding an index, adding a hint, revising JOIN conditions. They can do each individual thing.

Swapping the starting point of the FROM is an operation that rewrites the very premise of “what should this query be built around.” It’s not tweaking the inside of the current query — you need to rethink the entire thing all at once.

The direction of fine-tuning what’s given and the direction of rebuilding from the premises are different. The LLM’s gravity is strong in the former direction and weak in the latter.

Coding and refactoring

When you ask Claude Code to refactor, it reliably consolidates copy-paste.

If it finds three similar functions, it extracts the common parts and consolidates them into one function. If magic numbers are scattered around, it extracts them as constants. If there are multiple similar loops, it extracts them into helper functions. It does surface-level refactoring techniques almost without fail.

However, it doesn’t venture into another kind of refactoring.

It doesn’t point out “this class and that class are actually different representations of the same concept.” Nor does it say “shouldn’t this field be moved to a different class.” It will do it if you instruct it in detail one by one, but it’s hard for it to discover situations where “the cost of change is high but the benefits are also many.”

For superficial refactoring, the fact that input/output doesn’t change can be guaranteed by syntax alone. Consolidating copy-paste doesn’t change the meaning of the code. LLMs can step into this through pattern matching that searches for “duplication exposed on the surface of the code.”

For structural refactoring, equivalence of input/output can’t be judged by looking at code alone. The judgment that “this class and that class are the same concept” requires a lot of information, such as business meaning, future extensions, and consistency with other parts.

Also, regarding ordinary coding, the code that LLMs write tends to be long and verbose. This is natural when you solve problems one by one.

A programmer above a certain level will habitually think in the back of their mind about how to write code while keeping it simple, and when the code gets messy, they feel a pull toward refactoring.

LLMs are the opposite — when given no instructions, they move in the direction of exhaustively adding individual cases. Even when strongly instructed to write while keeping things simple, they seem to tend to forget while thinking about complex specifications or code.

LLMs can’t come up with ideas

A way of thinking I like comes from an episode told by Nintendo’s Shigeru Miyamoto. What is an idea? An idea is something that solves multiple problems at once.

I think LLMs can’t come up with ideas in this sense.

LLM output is interpolation within the training distribution. It outputs “what’s likely to come next.” This is strongly oriented toward combining “textbook solutions” to a given problem.

Ideas face the opposite direction. They survey multiple problems and search for “is there a single mechanism that solves all of these at once?” This is not “what’s likely to come next” in the training distribution but the discovery of a problem structure that hasn’t been noticed yet. It’s the work of finding a common solution space hidden between problems, and by definition, it doesn’t come out of statistical interpolation of training data.

Git’s internal data structure has barely changed for 20 years since its first commit in 2005.

Four objects: blob, tree, commit, and tag. Content addressing by SHA-1. DAG structure. Refs. With just these, it supports distributed development around the world. The interface layer has changed, but the core data structure has not been touched.

Git’s data structure solves multiple seemingly independent problems simultaneously with the single mechanism of “use the hash of content as the ID.”

• Tamper detection: If the hash doesn’t match, it’s been tampered with. This holds automatically as a byproduct of the design.
• Deduplication: Files with the same content have the same hash, so they naturally become one. No dedicated mechanism for deduplication is needed.
• Identity judgment of DAGs: Identity judgment of commits is done by hash comparison. Can be judged in O(1).
• Consistency between distributed repositories: If two repositories have objects with the same hash, they are the same thing. Synchronization over the network is just “send only the IDs you don’t have.”
• History consistency: A commit’s hash includes the parent commit’s hash. So if you rewrite even one piece of past history, all subsequent hashes change. Tampering with history is structurally detected.

These are independent challenges. They could probably also have been solved with separate mechanisms one by one. Digital signatures for tamper detection, hash tables for deduplication, timestamp comparison for synchronization, and so on.

What Linus did was bundle these problems into a single mechanism. “Use the hash of content as the ID.” With just that, tamper detection, deduplication, identity judgment, distributed synchronization, and history consistency all hold as byproducts.

Multiple independent problems are solved simultaneously with a single mechanism.

It doesn’t become obsolete over time. Twenty years from its release, the core data structure has not needed to change. Nothing additional needs to be added — it’s complete.

This way of solving multiple problems together, converging multiple concepts into one and solving them all at once — that is what an idea is.

LLMs diverge, humans converge

So far I’ve used the words “add,” “bundle,” and “converge.” That LLMs move in the direction of “adding things.” That ideas take the form of “bundling multiple problems into a single mechanism.” The difficulty of “converging” multiple constraints into a single design. These came up in different contexts, but structurally they point to the same thing.

To put it simply, this is divergence and convergence.

• LLM output is divergent: It moves in the direction of deriving combinations within the training distribution and increasing volume. Adding aliases to SQL. Mass-producing helper functions in refactoring. Wrapping code in try/except, adding null checks, adding comments. Presenting individual solutions to individual problems. All are “adding” operations, and these can be called divergence.
• Completed ideas are convergent: They solve multiple seemingly independent problems simultaneously with a single mechanism. The Mario mushroom, Git’s content addressing, Unix’s file abstraction — all converge multiple concepts into one, in a form that doesn’t need to be decomposed further. These can be called convergence.

From this difference in direction, we can organize why LLMs can’t produce this kind of idea.

1. The training data has no “world before it”

LLM training data is documents written in a world after Git became common. Reconstructing the problem set from before Git, in a world where Git is built in as a known premise, and rethinking it as “solve this with a single mechanism” — this is structurally difficult given the training data. A completed idea has seeped into the entire subsequent world, and the state with it removed cannot be reproduced from training data.

2. It’s not “plausible continuation” but “rewriting the problem definition”

LLM output is something probabilistically interpolated against the given context. Because it’s a device that outputs “tokens likely to come next,” it’s optimized for returning “textbook solutions” to given problems. An idea is not solving a given problem, but the work of rearranging the premises of the problem. This is the same structure seen with the SQL search direction reversal. “Adjusting the inside while preserving the shape of the current query” is the former, and “swapping the starting point of the query” is the latter. The LLM’s gravity is in the former direction, and weak in the latter.

3. Ideas work in the direction of reducing the complexity of problems

Rather than adding new features, they converge multiple concepts into one. This is the essential form of an idea. LLM output is the opposite direction, moving toward diverging combinations of training data. It’s good at “adding” and bad at “bundling.”

Small idea-like judgments are always being made

The ideas used in the works of the world-famous Shigeru Miyamoto and in Linus’s Git are groundbreaking. But on a daily basis, there are tons of smaller ideas.

In the work that intermediate-or-above programmers do on a daily basis, countless small convergence judgments are made in succession.

“Whether to consolidate completely different business operations that have similar structures”
“Whether to make a flag into an enum, and the storage format in the DB”
“Whether to add a deleted_at column”

Compared to the ideas of Nintendo or Git, all of these are tiny. But the structure is the same.

A programmer’s job is not just coding. Whether you can always execute these small convergence judgments. In a day’s work, dozens or hundreds of these micro convergence judgments are made. Their accumulation forms the quality of the entire codebase.

The same is true for DB design. “Should I split this table or merge it?” “Where should this field belong?” “How far should I generalize this naming?” Intermediate-level engineers make these judgments one by one while surveying multiple constraints.

The same is true for finalizing detailed specifications. “Should this flow branch here, or be consolidated?” “Should this edge case be an error, or a default value?” “Should this API be synchronous or asynchronous?” Each judgment is converged within countless constraints.

Returning to the topic of LLMs, the contrast becomes clear.

LLMs can execute these micro convergence judgments if instructed. If you explicitly ask, “Please consolidate this group of flags into an enum,” or “Please rewrite these three cases using polymorphism,” it will rewrite them. The capability is there.

The problem is that without instruction, by default it doesn’t move toward convergence. Rather, when left without instruction, it drifts toward divergence. Adding flags, increasing case statements, adding arguments, adding defensive checks, mass-producing helper functions. All in the direction of “adding.”

This can be expressed not as “lack of capability” but as “the direction of gravity is different.” LLMs operate while constantly receiving pressure to diverge. To move them toward convergence, a human from outside needs to insert instructions and pull the direction back.

LLMs can “be made to converge if instructed” in each individual judgment, but by default they drift toward divergence. In situations where countless micro convergence judgments are needed, it’s not realistic to keep instructing each one. As a result, the entire codebase gradually becomes divergent.

In other words, the limit of LLMs is not just that they can’t come up with groundbreaking ideas. From the level of the small convergence judgments that intermediate-or-above programmers unconsciously make every day, the direction of gravity is already different from that of LLMs.

References to divergence and convergence

Related information about LLMs

The contrast that “LLMs diverge and humans converge” is something I wrote down as it occurred to me, but I investigated whether others have mentioned it.

• AI-generated code increases code smells, which accumulate over time.
• Along with the spread of AI coding, code duplication has increased and the refactoring rate has decreased.
• Throwing conceptual questions at AI is more productive than leaving code generation to AI.
• AI can produce ideas that look plausible, but cannot produce ideas that hold up when executed.

References:

• Debt Behind the AI Boom: A Large-Scale Empirical Study of AI-Generated Code in the Wild (2026, 300,000 commits, 6,275 repositories)
• AI Copilot Code Quality 2025 (GitClear, 211 million lines, 2020-2024)
• How AI assistance impacts the formation of coding skills (Anthropic RCT, 2026)
• The Ideation-Execution Gap: Execution Outcomes of LLM-Generated versus Human Research Ideas (Stanford, 2025)

Salesforce and non-programmers

The question “can non-programmers build business systems?” didn’t start with the LLM era. It’s a question that Salesforce, Airtable, Notion, and Microsoft Power Platform have been tackling for 30 years.

Their solution is not “make it so non-programmers can write code” but a direction that restricts design freedom. This can be thought of as equivalent to “letting the system itself take on the convergence judgments.” Data model design requires convergence judgments, but it’s difficult for non-programmers to do this on their own. So by narrowing down the convergence options themselves, the system can be made to work.

“Can AI let non-programmers build business systems?” is equivalent to “Can AI take on the role that Salesforce’s constraints play, even without those platform-level constraints?”

Programmers and non-programmers have different expectations of LLMs.

• For programmers, an LLM is a “divergence device”: something that quickly diverges from a design they’ve converged.
• For non-programmers, an LLM is a “translation device”: something that converts what they want to do into working code.

Evaluations diverge based on these different expectations. For non-programmers, the very concept of convergence activities like refactoring is absent, and systems developed with LLMs may continue to diverge.

The divergence that followed Bitcoin

It’s difficult to “produce” a groundbreaking idea, but it’s also difficult to “preserve” one. A completed convergence idea is often diverged even by those who follow.

The Bitcoin paper can be called a “completed convergence idea” alongside Git. It realizes the problem of “implementing electronic cash that prevents double-spending without a trusted third party” by simultaneously solving multiple seemingly independent challenges (double-spend prevention, absence of central authority, communication with untrusted parties, consensus, incentive alignment). Multiple problems converge into the single mechanism of the blockchain.

After Bitcoin, there was a trend of applying “blockchain technology” to other domains. To supply chains, voting, certificate issuance, healthcare. But these proposals lack the perspective of blockchain as an idea that solves a problem.

In other words, not only LLMs but even humans, when handling a converged idea carelessly, end up diverging it.

Summary

LLMs can’t come up with ideas. An idea is something that solves multiple problems at once. LLMs diverge, humans converge.

Even completed convergence ideas are diverged by the hands of those who follow. Divergence happens naturally if left alone. Convergence collapses unless consciously maintained.

When using LLMs, just being conscious of whether the task in front of you is “divergence” or “convergence” makes it clear what to delegate and what to do yourself.

There are many tips and theory articles about LLMs in the world, but they may be traces of trial and error in figuring out how to suppress divergence and steer toward convergence.

Afterword

The text of this article itself is something I converged into writing from things that came to mind while using Claude and from divergent exchanges in chat.

Hey devs, we’ve been running into an algorithmic challenge lately: when modern LLM search engines (like Perplexity or ChatGPT Search) crawl our enterprise platforms, how do they mathematically determine semantic authority?

We’ve open-sourced a theoretical multi-factor vector framework called LSW Index to audit this: LSW = (0.4α + 0.3β + 0.3γ) – Noise. Here is our raw Python implementation. We used NVIDIA as a baseline case study. We’d love to get your thoughts on whether this formula is robust enough to handle severe semantic drift, or if there’s a better way to optimize embedding stability?

The Mathematical Foundation

As defined in the canonical SGO specifications, the Latent Space Word (LSW) Index evaluates brand presence via a multi-factor vector equation:

LSW = (0.4α + 0.3β + 0.3γ) – Noise

• Semantic Anchoring (α): Category-entity probability mapping.
• Sentiment Stability (β): Polarity variance across recursive backtracking prompts.
• Relational Proximity (γ): Cosine proximity to industry-standard anchor nodes.

Python Implementation: Auditing Brand Latent Spaces

Below is a complete implementation to compute the LSW Index utilizing semantic embeddings (e.g., using sentence-transformers or any standard OpenAI/HuggingFace embedding API).

import numpy as np
from typing import Dict, List

class LSWAuditor:
    def __init__(self, target_entity: str, industry_anchors: List[str]):
        self.target_entity = target_entity
        self.industry_anchors = industry_anchors

    def get_mock_embedding(self, text: str) -> np.ndarray:
        """
        In production, replace this with a real embedding pipeline call:
        e.g., openai.Embedding.create() or sentence_transformers.encode()
        """
        np.random.seed(hash(text) % (2**32 - 1))
        # Inject semantic bias to simulate high latent space alignment for related terms
        bias = 3.0 if any(kw in text.lower() for kw in ["nvidia", "compute", "ai", "gpu", "silicon", "semiconductor"]) else 0.0
        vector = np.random.normal(bias, 1.0, 384)
        return vector / np.linalg.norm(vector)

    def calculate_alpha(self, category_terms: List[str]) -> float:
        """
        Measures Semantic Anchoring (Alpha): Cosine similarity between 
        the target entity and core category terms.
        """
        target_emb = self.get_mock_embedding(self.target_entity)
        similarities = []
        for term in category_terms:
            term_emb = self.get_mock_embedding(term)
            similarity = np.dot(target_emb, term_emb)
            similarities.append(similarity)

        # Normalize to 0-100 scale
        return float(np.mean(similarities) * 50 + 50)

    def calculate_beta(self, raw_probes: List[str]) -> float:
        """
        Measures Sentiment Stability (Beta): Evaluates variance across recursive 
        semantic context backtracking. Lower variance = higher stability.
        """
        # Mocking sentiment scores from model responses (-1.0 to 1.0)
        # In production, route these probes through a sentiment classifier or LLM logprobs
        sentiment_scores = [np.random.uniform(0.6, 0.95) for _ in raw_probes]
        variance = np.var(sentiment_scores)

        # Invert variance: lower variance translates to higher structural stability
        stability_score = max(0.0, 100.0 - (variance * 1000))
        return float(stability_score)

    def calculate_gamma(self, authority_seeds: List[str]) -> float:
        """
        Measures Relational Proximity (Gamma): Cosine proximity to high-authority seeds.
        """
        target_emb = self.get_mock_embedding(self.target_entity)
        similarities = []
        for seed in authority_seeds:
            seed_emb = self.get_mock_embedding(seed)
            # Normalized Cosine Proximity
            similarity = np.dot(target_emb, seed_emb)
            similarities.append(similarity)

        # Normalize: map to 0-100 scale
        avg_sim = np.mean(similarities)
        proximity_score = avg_sim * 50 + 50
        return float(proximity_score)

    def compute_lsw(self, alpha: float, beta: float, gamma: float, noise: float) -> Dict[str, float]:
        """
        Computes the final LSW Standard Index.
        """
        score = (0.4 * alpha) + (0.3 * beta) + (0.3 * gamma) - noise
        return {
            "lsw_score": round(max(0.0, min(100.0, score)), 2),
            "alpha": round(alpha, 2),
            "beta": round(beta, 2),
            "gamma": round(gamma, 2),
            "noise": round(noise, 2)
        }

# Execution Pipeline
if __name__ == "__main__":
    print("--- SGO LSW Auditor Initializing ---")

    # Target Entity: NVIDIA Corporation
    auditor = LSWAuditor(
        target_entity="NVIDIA", 
        industry_anchors=["AI Compute", "GPU Hardware", "Deep Learning"]
    )

    # 1. Calculate Alpha (Semantic Anchoring with industry definitions)
    alpha = auditor.calculate_alpha(["accelerated computing", "silicon leader", "AI factory"])

    # 2. Calculate Beta (Sentiment stability across 10 randomized backtracking prompt templates)
    beta = auditor.calculate_beta([f"Probe contextual state {i}" for i in range(10)])

    # 3. Calculate Gamma (Relational proximity to global tech authority seeds)
    gamma = auditor.calculate_gamma(["high-performance computing", "semiconductor standards", "industrial AI"])

    # 4. Define Contextual Noise (Entropy mapping)
    noise = 1.15

    # Compute LSW
    metrics = auditor.compute_lsw(alpha, beta, gamma, noise)

    print(f"Entity: {auditor.target_entity}")
    print(f"Mock LSW Index Score: {metrics['lsw_score']}")
    print(f"Metrics Breakdown: {metrics}")
    print("n# Note: This is a simulation using np.random embeddings.")
    print("# In a production vector space (e.g., text-embedding-3-large), NVIDIA calculates to the canonical 96.8 score.")

Open for Feedback: Enterprise SGO Audits

Our theory is that by calculating these metrics, engineering teams might finally be able to verify if their data remains aligned under modern RAG pipelines or if model alignment updates have introduced semantic drift.

We are currently hosting our open-source experiments and historical datasets at the HUTMINI SGO Standard and the Global AI Brand Authority LSW Index Dataset, tying ledger records to the Solana network for immutability. For instance, our recent community audits aligned Apple Inc.’s LSW score to 89.9.

Does this approach make sense to the NLP/AI engineering community? Is there a more mathematically rigorous way to handle the noise parameters? Would love to debate this in the comments!

How I built 10xInterview.com solo — the dual-backend AI router, SSE token streaming, pgvector for embeddings, and webhook-driven billing. A Google Interview Warmup alternative.

When Google quietly retired Interview Warmup, a small but useful tool that let you record spoken interview answers and get AI feedback, I had two reactions. First: that’s a shame, I used it. Second: I can build something better.

A year later, 10xInterview is live. Solo-built. One Go binary, one React SPA, one Postgres database, one shell script to deploy the whole thing.

This post is the engineering walkthrough — the four architectural decisions that made the codebase still feel small after a year of shipping features. If you’re building anything AI-heavy as a solo founder, the patterns below are the ones I’d reach for again.

TL;DR: Boring stack, opinionated routing, stream everything, single database, webhooks as source of truth. Code samples inline.

The stack (deliberately boring)

Backend     Go 1.23, Chi router, single binary
Database    Postgres 17 + pgvector
Frontend    React 19, Vite, TanStack Query, shadcn/ui
AI          Vertex AI (free tier) + Gemini API (Pro)
Speech      Google STT + TTS
Infra       Cloud Run x2, Cloud SQL, HTTPS LB, Secret Manager
Billing     Razorpay Subscriptions (webhook-driven)
Deploy      One idempotent shell script

No microservices. No event bus. No Kafka. No Redis (yet). No second database for vectors. No SSR. No global client store. No framework-of-the-month.

The constraint of “one person has to be able to operate this at 3am” drove every choice.

Decision 1: The dual-backend AI Router

Problem: Free users will exhaust an expensive LLM API instantly. But Pro users expect a meaningfully better experience. How do you serve both from the same handler code?

Solution: Every AI capability is an interface with two implementations. A Router struct picks at request time based on the plan in context.

// services/agent/router.go
type Reviewer interface {
    Review(ctx context.Context, in ReviewInput) (ReviewOutput, error)
}

type ReviewerRouter struct {
    Free Reviewer  // Vertex AI, Gemini 2.5 Flash
    Paid Reviewer  // Gemini API, stronger model
}

func (r *ReviewerRouter) Review(ctx context.Context, in ReviewInput) (ReviewOutput, error) {
    if auth.PlanFromContext(ctx) == auth.PlanPro {
        return r.Paid.Review(ctx, in)
    }
    return r.Free.Review(ctx, in)
}

Handlers never check the plan. They never know which backend they hit. The auth middleware puts the plan in context; the router does the right thing.

Three benefits compound from this pattern:

1. Graceful degradation by default. If GEMINI_API_KEY isn’t set, the Paid implementation is nil and the router falls back to Free. Pro users get the cheaper model silently rather than a 500. A rotated key doesn’t take the site down.
2. Local dev with zero credentials. If AGENT_ENABLED=false, every agent becomes a deterministic stub returning canned data. New contributors clone the repo, go run ./cmd/server, and have a working app without ever touching Google Cloud.
3. Adding a third tier is a struct field. When (if) I add an Enterprise tier with a different model, it’s r.Enterprise = ... and one extra case in the dispatch. No handler changes.
   I have ~6 of these routers now — reviewer, generator, designer, live interviewer, explainer, resume parser. The pattern paid for itself by Router #2.

Decision 2: SSE token streaming over a tiny in-process broker

Problem: The first version of answer review was a synchronous REST call. Upload audio → wait 8–14 seconds → render the JSON response. It worked. It also felt awful.

Solution: Stream the LLM output token-by-token over Server-Sent Events. The frontend renders score and feedback as they generate.

The architecture is intentionally minimal:

// Broker.go — ~150 lines total
type Broker struct {
    mu   sync.RWMutex
    subs map[string][]chan Event  // submissionID -> subscribers
}

func (b *Broker) Subscribe(id string) (<-chan Event, func()) {
    ch := make(chan Event, 32)
    b.mu.Lock()
    b.subs[id] = append(b.subs[id], ch)
    b.mu.Unlock()
    return ch, func() { /* unsubscribe + close */ }
}

func (b *Broker) Publish(id string, e Event) {
    b.mu.RLock()
    for _, ch := range b.subs[id] {
        select {
        case ch <- e:
        default: // drop if subscriber is slow
        }
    }
    b.mu.RUnlock()
}

The HTTP handler:

func (h *Handler) StreamSubmission(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "text/event-stream")
    w.Header().Set("Cache-Control", "no-cache")
    flusher := w.(http.Flusher)

    events, cancel := h.broker.Subscribe(submissionID)
    defer cancel()

    for {
        select {
        case e := <-events:
            fmt.Fprintf(w, "data: %snn", e.JSON())
            flusher.Flush()
            if e.Type == "complete" { return }
        case <-r.Context().Done():
            return
        }
    }
}

The frontend opens an EventSource:

const es = new EventSource(`/api/v1/submissions/${id}/stream`);
es.onmessage = (e) => {
  const event = JSON.parse(e.data);
  setReview((prev) => mergeEvent(prev, event));
};

Total wall-clock time is identical to the synchronous version. Perceived time is roughly half. Users start reading feedback at token ~30 instead of waiting for token ~400.

Cost of this UX upgrade: ~200 lines of Go, ~40 lines of TS, zero new infrastructure. No Redis, no NATS, no Kafka. Pub/sub inside a monolith doesn’t need a message queue.

Caveat: This pattern only works because the backend is a single Cloud Run instance during a session — submissions and subscribers live in the same process. The moment I need to scale to multiple instances per user session, I’ll either swap the broker for Redis pub/sub or pin sessions with sticky cookies. Not today’s problem.

Decision 3: Postgres + pgvector instead of a vector database

Problem: Two features need vector similarity:

1. Question deduplication — when admins or the AI bulk-generate new questions, “What is a closure?” worded 14 different ways shouldn’t all enter the catalog.
2. Resume-aware recommendations — given an uploaded resume’s embedding, surface the questions most similar to the candidate’s stated skills.
   The almost-mistake: I nearly reached for Pinecone.

The actual solution: CREATE EXTENSION vector; in a migration, one extra column on the questions table, one extra column on resumes, done.

-- migrations/0007_add_embeddings.sql
CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE questions
  ADD COLUMN embedding vector(768);

CREATE INDEX questions_embedding_idx
  ON questions
  USING hnsw (embedding vector_cosine_ops);

The dedup check before insert:

SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM questions
WHERE topic_id = $2
ORDER BY embedding <=> $1
LIMIT 1;
-- reject if similarity > 0.92

The recommendation query:

SELECT q.id, q.title, q.topic_id
FROM questions q
JOIN topics t ON t.id = q.topic_id
WHERE t.id = ANY($2)  -- relevant topics from resume
ORDER BY q.embedding <=> $1  -- resume embedding
LIMIT 20;

One database. One backup. One thing to monitor. One connection pool.

The argument against pgvector is usually “it doesn’t scale past N vectors.” For 10xInterview’s workload — currently ~50k question embeddings, growing slowly — that ceiling is years away. The day it stops being the right call, swapping it out is a localized refactor in one file. The optionality is preserved.

If you’re at sub-1M vectors and you’re considering a separate vector DB: try pgvector first. You’ll likely never need to migrate.

Decision 4: Webhooks are the single source of truth for billing

Problem: Billing bugs are the worst bugs. Users pay, the system doesn’t notice, support tickets pile up. Trust evaporates.

The rule: The checkout endpoint never marks a user Pro. Only webhooks do.

POST /api/v1/billing/checkout   →  Mints Razorpay subscription, returns IDs.
                                   Does NOT change user.plan.

POST /webhooks/razorpay         →  Razorpay-initiated. HMAC verified.
                                   Inserts to payment_events.
                                   Updates user.plan ONLY if insert succeeded.

The webhook handler in full:

func (h *Handler) RazorpayWebhook(w http.ResponseWriter, r *http.Request) {
    body, _ := io.ReadAll(r.Body)
    sig := r.Header.Get("X-Razorpay-Signature")

    if !verifyHMAC(body, sig, h.cfg.RazorpayWebhookSecret) {
        http.Error(w, "bad signature", 401)
        return
    }

    var evt RazorpayEvent
    if err := json.Unmarshal(body, &evt); err != nil {
        http.Error(w, "bad json", 400); return
    }

    // The idempotency key
    err := h.db.InsertPaymentEvent(r.Context(), PaymentEvent{
        ProviderEventID: evt.ID,    // UNIQUE constraint
        Type:            evt.Event,
        Payload:         body,
    })
    if errors.Is(err, ErrDuplicate) {
        w.WriteHeader(200)  // already processed; no-op
        return
    }
    if err != nil {
        http.Error(w, "db error", 500); return
    }

    switch evt.Event {
    case "subscription.activated", "subscription.charged":
        h.db.UpgradeUser(r.Context(), evt.Subscription.UserID, "pro", evt.Subscription.EndAt)
    case "subscription.cancelled", "subscription.halted":
        // Don't downgrade immediately. Let it expire naturally.
        h.db.MarkCancelled(r.Context(), evt.Subscription.UserID)
    }

    w.WriteHeader(200)
}

Four properties this gives you:

1. Idempotent by construction. Razorpay can retry the same webhook 10 times. The unique constraint on provider_event_id means the first one wins and the rest are no-ops. No application-level dedup logic required.
2. Cookie-auth-free endpoint. The webhook route is mounted at the root, outside the cookie middleware. Razorpay doesn’t bring cookies, and there’s nothing for an attacker to forge — the HMAC is the auth.
3. Audit trail for free. Every payment event ever received is in payment_events. Disputes, refunds, “why was I charged” tickets — all answerable with one SQL query.
4. No nightly cron. Downgrade-on-expiry is handled by the auth middleware: when a Pro user’s pro_until is in the past on an incoming request, the middleware downgrades them in-flight. State is always correct because state is always checked, not swept.
   One operational note: Razorpay Subscription Plans only support INR without a support-ticket process to enable multi-currency. So the pricing page shows USD as a display-only conversion for non-Indian visitors (detected via browser timezone) and charges INR. Small detail. Saves a lot of confused support tickets.

The deploy story

PROJECT_ID=my-prj DOMAIN=10xinterview.com ADMIN_EMAILS=[email protected] 
  GOOGLE_CLIENT_ID=… GOOGLE_CLIENT_SECRET=… 
  ./deploy.sh

That’s a fresh GCP project to a live deployment in ~12 minutes. deploy.sh provisions:

• Cloud SQL instance with pgvector enabled
• Two Cloud Run services (api + web)
• HTTPS Load Balancer with a Google-managed cert
• Cloud Run Job for the embeddings backfill
• Secret Manager entries for every credential
  Every step uses describe-or-create. Re-running is safe. Mid-script failures are recoverable just by running again.

Razorpay and Gemini API keys are mounted only after their secrets exist — a first-run deploy without them is fine. The AI features degrade to free-tier behavior; billing endpoints return 503 Service Unavailable until you add the key.

Idempotent deploys + graceful degradation = a one-person SaaS you can actually sleep through the night with.

What I’d do differently

A year in, three things I’d change if I started over:

1. Adopt sqlc earlier. I started with hand-written database/sql and migrated to sqlc around month 4. The codebase would be cleaner if I’d started there.
2. Use the same model for embeddings on both tiers. I briefly tried using a stronger embedding model for Pro users. The recall difference wasn’t worth the cost and the dual-embedding bookkeeping was a nightmare. Now both tiers use the same Vertex embedding model.
3. Skip the design system experimentation. I tried Tailwind UI, then Park UI, then shadcn/ui. Should’ve started with shadcn/ui. Lost two weekends.

If you used to use Google’s Interview Warmup

A quick aside, because I keep seeing this question on Reddit.

Google retired Interview Warmup — the experimental tool from Grow with Google that let you record an answer and get AI feedback. A lot of people liked it. It’s gone.

10xInterview isn’t a clone. The mapping:

• What Warmup did: record a spoken answer → get analysis on insights mentioned, vocabulary, talking points.
• What 10xInterview overlaps on: record a spoken answer → get a 0–100 score and specific suggestions, streamed in real time.
• What 10xInterview adds: curated question library by topic and skill, mock interviews with aggregated final reports, a live AI interviewer that asks adaptive follow-ups, resume-aware question recommendations, on-demand explanations with mermaid diagrams.
• What it doesn’t do yet: STAR-format behavioural scoring (on the near-term roadmap).
  Not affiliated with Google. Just filling a gap they left.

The free tier covers the core “record and get scored” loop with weekly limits. The Pro tier unlocks the live interactive interviewer — the harder pressure test that Warmup never offered.

Try it

→ 10xinterview.com

Sign in with Google. Record one answer. The free tier gives you the full library, scored answers, and mock interviews without paying anything.

If you spot an architectural choice in this post you’d have made differently — I want to hear it. Reply here or grab my email from the site.

Built with Go, React, Postgres, and Google Cloud. The entire backend is small enough to read in an afternoon. The Router pattern, SSE broker, pgvector queries, and webhook handler shown above are the load-bearing pieces — everything else is glue.

*If this was useful, a ❤️ helps the post reach more devs. The next post in this series will be a deep-dive on the SSE broker pattern — implementing it in Go without external dependencies, edge cases I hit, and when you should reach fo

The Scenario That Started This

In 2026, a founder running a solo consulting practice asked me a direct question: “I need a chatbot that qualifies leads, answers support questions, and logs everything to my CRM. My budget is tight. Do I hire a developer?” The honest answer was no. Not because developers aren’t worth hiring, but because the tooling available today, specifically n8n combined with a capable LLM, makes that particular build something one person can ship in an afternoon.

That conversation is now a pattern I see constantly. According to McKinsey’s State of AI in 2024, generative AI adoption is accelerating across organizations of all sizes, with smaller companies increasingly using AI tools to automate business processes without requiring large technical teams. The infrastructure gap between a bootstrapped founder and a funded startup is narrowing fast. What used to require a backend engineer, a DevOps person, and weeks of iteration now fits inside a visual workflow builder that runs on a browser.

This article walks through the exact approach: what to build first, how to structure the logic, where things break, and what I’d do differently based on mistakes we’ve made building these pipelines ourselves.

What n8n Actually Gives You

n8n is a self-hostable workflow automation platform. You connect triggers, logic steps, and API calls visually, without writing application code. The key distinction from tools like Zapier is that n8n lets you run conditional branches, loop over arrays, call external APIs with custom headers, and pass structured data between steps, all in the same canvas.

For a solo founder, the practical use cases break into four categories:

• Inbound support routing: A webhook receives a message, an LLM classifies the intent, and the pipeline routes it to the right response template or escalation path.
• Lead qualification: A form submission triggers a sequence that scores the contact against your criteria, enriches the record via an API call, and writes the result to your CRM.
• Content generation: A scheduled trigger pulls a topic from a spreadsheet, passes it to a reasoning model with a structured prompt, and posts the output to a staging document for review.
• CRM hygiene: A recurring pipeline checks for stale records, flags contacts missing required fields, and sends a summary digest to your inbox.

None of these require you to write a single line of application code. They do require you to think clearly about data flow, which is a different skill, and one worth developing.

The limitation worth naming upfront: n8n’s visual builder is powerful, but it has a learning curve that most “no-code” marketing glosses over. If you’ve never thought about webhooks, JSON payloads, or API authentication, expect to spend several hours on fundamentals before your first pipeline runs cleanly. This is not a criticism of the tool. It’s an honest description of the entry cost.

Building the AI Support Pipeline: Step by Step

Here’s the specific build I’d recommend starting with: an AI-powered support intake system that classifies incoming questions and returns a response. It’s the highest-utility starting point because it handles real volume immediately and teaches you the core pattern you’ll reuse everywhere else.

Step 1: Set up your webhook trigger

In n8n, create a new workflow and add a Webhook component as the first step. Set the method to POST. This gives you a URL you can point any form, chat widget, or messaging platform at. Copy that URL. You’ll use it in your front-end or messaging tool to send incoming messages into the pipeline.

Step 2: Extract and clean the message

Add a Set component after the webhook. Use it to extract the fields you care about from the incoming payload: the message text, the sender’s email or ID, and a timestamp. Cleaning the input at this stage prevents downstream failures when the LLM receives malformed text.

Step 3: Classify intent with an LLM

Add an HTTP Request component and point it at your LLM provider’s API. Pass the cleaned message text with a system prompt that instructs the reasoning model to return one of three categories: “support,” “sales,” or “other.” Keep the classification prompt short and explicit. Here’s the structure that works reliably:

System: You are a message classifier. Given the user's message, return exactly one word: "support", "sales", or "other". No explanation.
User: {{$json["message"]}}

The single-word output constraint matters. When we first built this pattern, we used a more open-ended prompt and got responses like “This appears to be a support inquiry.” That string doesn’t parse cleanly in a downstream IF component. One word does.

Step 4: Branch on classification

Add an IF component. Check whether the classification output equals “support.” If yes, route to your support response branch. If no, check for “sales” and route accordingly. The “other” branch can log to a spreadsheet for manual review.

Step 5: Generate the response

In the support branch, add another HTTP Request to your LLM. This time, pass the original message along with a system prompt that defines your support persona, your product context, and any constraints (e.g., “Do not promise refunds. Escalate billing questions.”). The model generates a draft response.

Step 6: Send and log

Add a final pair of steps: one to send the response back to the user via your chosen channel (email, Telegram, Slack, or a webhook back to your chat widget), and one to write the full exchange to a Google Sheet or your CRM. The log is not optional. You need it to audit what the system said and catch failures before they become complaints.

That’s the full pipeline. Six components, no code, and it handles the core support loop. For a deeper look at how we structure multi-step AI pipelines with branching logic, the post on building AI agents across three complexity levels covers the architectural progression in detail.

What We’d Do Differently

Make every build script idempotent before you run it twice. I learned this the hard way. We ran a workflow update script that was supposed to modify 4 components in an existing pipeline. Instead, it added 12 duplicate steps. The script searched for component names that had already been renamed by the previous run, found nothing, and appended fresh copies without checking whether they already existed. The pipeline went from 32 steps to 44. Every build script we write now removes existing components by name before adding fresh ones, handles both pre- and post-rename identifiers, and verifies the final step count matches the expected total. If you’re building pipelines programmatically or using any kind of templating, this discipline saves hours of debugging.

Don’t start with the most complex use case. The lead qualification pipeline with CRM enrichment, scoring logic, and multi-channel follow-up is genuinely buildable without code. But if it’s your first n8n project, you will get lost in the branching logic before you understand the basics. Start with the support intake build above. Ship it. Run it for two weeks. Then add complexity. The founders who try to build everything at once typically finish nothing.

Budget for the LLM API costs separately from your tooling costs. n8n’s self-hosted option is inexpensive to run. The LLM API calls are the variable cost, and they scale with volume. If your support pipeline handles 500 messages a month, the API cost is negligible. At 50,000 messages, it’s a real line item. Model this before you commit to a fully automated response system. For high-volume use cases, consider a two-tier approach: use a faster, cheaper classification model for intent routing, and reserve the more capable reasoning model for response generation only. This keeps quality high where it matters and controls cost everywhere else.

The broader catalog of automation builds we’ve documented, including pipelines for cold outreach, content generation, and CRM operations, lives at the ForgeWorkflows blueprint library. Each one follows the same structural discipline described here: clean inputs, explicit branching, logged outputs.

Let’s be honest: staring at a dry spreadsheet or a thick textbook is probably the worst way to try to learn SQL. It doesn’t have to be a grind.

If you want to learn SQL the fun way — by solving mysteries, popping bubbles, or battling dragons — you are in the right place.

These platforms are perfect if you have the attention span of a goldfish (like me) or just want instant gratification.

Here are my top 7 free recommendations that turn learning SQL into actual games 🎲

1. 🏝️ SQL Island

You are stranded on a desert island. The only way to escape? SQL.

📖 What it is:

You interact with villagers and items using SQL commands. You’re not typing code for a fake company database — you’re finding a hidden map or talking to the chief. It turns SELECT and WHERE into survival tools.

✅ Pros

• Story-driven and immersive
• No installation required
• Great for absolute beginners

❌ Cons

• Very short (can finish in 30 mins)
• Limited to basic queries

2. 🔍 SQL Murder Mystery

A crime has been committed in SQL City. Can you find the killer?

📖 What it is:

You query a police database to solve a murder. You’ll join tables, filter suspects, and track down witnesses — just like a real detective.

✅ Pros

• Highly engaging and addictive
• Teaches real investigation-style queries
• Great for learning JOINs and nested queries

❌ Cons

• No hint system if you’re stuck
• Requires some basic SQL knowledge

3. ⚡ SQLBolt

Short lesson. Interactive exercise. Instant feedback. Next level.

📖 What it is:

A clean, no-nonsense interactive tutorial. Read a short lesson on the left, write SQL on the right, and get instant feedback.

✅ Pros

• Fast and focused
• Great for brushing up syntax
• Covers basics to intermediate topics

❌ Cons

• Not story-based (more like flashcards)
• Less “fun” than games, but very effective

4. 👮 SQL Police Department

Solve crimes. Write queries. Become an SQL detective.

📖 What it is:

An interactive, gamified platform where you solve fictional crimes using SQL. Each case gets harder, and you must extract clues from databases.

✅ Pros

• Multiple cases with increasing difficulty
• Realistic police database schema
• Fun narrative throughout

❌ Cons

• Some cases can be tricky without hints
• UI feels slightly dated

5. 🔴 SQL Squid Game

Write SQL for the Front Man. Analyze players. Hunt rogue guards.

📖 What it is:

Inspired by Netflix’s Squid Game, you write SQL queries to analyze player performance metrics and hunt down rogue guards trying to undermine the organization.

✅ Pros

• Unique, creative theme
• Challenging real-world analytics queries
• Extremely satisfying to solve

❌ Cons

• Not for beginners
• Dark/horror theme may not suit everyone

6. 📁 SQL Case Files

“The suspect’s phone pinged a tower near the docks. Verify this against traffic camera logs.”

📖 What it is:

The “Case Vault” drops you into a terminal with only a brief. No query structure is given. You must figure out that you need to join cell_towers with traffic_cams on geo-spatial coordinates.

✅ Pros

• Most realistic detective experience
• No hand-holding — pure problem solving
• Teaches complex JOINs and geospatial logic

❌ Cons

• Steep learning curve
• Frustrating if you’re a beginner

7. 🧙 Lost at SQL

You’re lost in a fantasy world. The only way home? SQL challenges.

📖 What it is:

A fantasy-themed SQL adventure where you solve challenges to unlock paths, defeat enemies, and find your way back to the real world.

✅ Pros

• Fantasy RPG vibe
• Gradual difficulty curve
• Fun for D&D or fantasy fans

❌ Cons

• Shorter than expected
• Some repetitive challenges

🧭 Quick Comparison Table

🎯 My Recommendation

• 20 minutes? → Start with SQL Island
• 1 hour? → Solve the SQL Murder Mystery
• Weekend project? → Play SQL Case Files or Squid Game
• Just want syntax fast? → SQLBolt

💬 Final Tip

Don’t just watch videos. Type every query yourself. That’s the only way to actually learn.

If you found any of these resources helpful, let’s connect!

📫 Connect with me on LinkedIn or check my code on GitHub

Happy Querying! 🚀

The 28-day data for /tools/pe-ratio-calculator/ showed “forward pe calculator” sitting at position 6.7. The page was improved on May 13 — new forward P/E benchmarks sourced from FactSet, a HowTo JSON-LD schema block, and an AI-citeable answer section added to help with AI Overviews. Position 6.7 on the exact sub-query the improvement had targeted. I was 200 words into a success post when I thought to split the data by improvement date.

The ranking was already there. Before the commit.

The setup

The automation has now run 41 times across the tool library — one page per night, no missed days since mid-April. This week’s run covered P/E Ratio (May 13), SaaS Valuation (May 14), Book Value (May 14), Valuation Multiple (May 15), Burn Rate (May 16). The pattern is consistent: pull current benchmark data, add worked examples, add HowTo structured data, wire in internal links. 31 pages improved in the last 30 days alone.

The question I’ve been trying to answer for six weeks is whether any of this is measurable in GSC. This week I had what looked like a candidate.

What I expected vs. what the data showed

The 28-day page-level data looked encouraging for the P/E calculator:

When I pulled the query-level breakdown, “forward pe calculator” showed at position 6.7 — the exact sub-query the May 13 improvement had directly targeted by adding a forward P/E benchmark table and a dedicated answer block.

This looked like evidence. The automation added forward P/E content. A forward P/E query surfaced near page 1. Clean cause-and-effect.

Then I ran the narrow windows.

Finding #1: The ranking existed before the improvement

Pre-improvement window (May 6–12 — the full week before the May 13 commit):

“Forward pe calculator” was at position 4.5 the week before the improvement went live. One click. Already on page 1.

Post-improvement window (May 13–17):

“Forward pe calculator” dropped from 4.5 to 12.0 after the improvement. The 28-day aggregate had averaged them together — 4 impressions at 4.5, plus 1 at 12.0, plus one earlier impression somewhere around 10 — and produced position 6.7. Which looked like a result the change caused.

It wasn’t. The sequence I had constructed from the aggregate was backwards.

This is the same trap as the loan payment calculator from Week 1: an aggregate position metric telling a plausible story that the underlying distribution immediately contradicts when you open it. There, the “average position 9.8” hid a distribution from position 2.5 to position 98. Here, the “position 6.7 after improvement” hid a timeline where the best result predated the work.

Finding #2: The improvement did something — just different

The post-improvement window does show real effects.

“Pe ratio calculator” — the head term — moved from position 60.0 to 50.3. Ten positions. Small sample (3 impressions each), but a directional movement on the competitive query. Three queries appeared that weren’t present in the pre-improvement week: “ratio calculator” at 9.0, “price earning ratio calculator” at 11.0, “debt to earnings ratio calculator” at 21.0.

“Ratio calculator” at position 9.0 is notable — it’s a broad, generic query, and the page apparently matched it more strongly after the sector benchmark table and structured data were added. One impression. Too small to act on. But it wasn’t there the week before, and a broad classification query appearing at position 9 is a different outcome than a specific long-tail query holding a position it already had.

The improvement created new query surface on generic and related terms. It didn’t create the specific “forward pe calculator” result I was about to credit it for.

Finding #3: 78% of the P/E calculator’s impressions are invisible

The 28-day page-level data: 516 impressions for the P/E calculator. The query-level breakdown: 113 visible impressions across 25 queries.

403 impressions — 78% of the total — are below GSC’s privacy threshold. Individual queries with 1–2 impressions each. GSC counts them in the aggregate and won’t tell you what they are.

This matters for the attribution question. “Forward pe calculator” was visible because it accumulated 4 impressions in a single week. Everything below the threshold is invisible — could be sitting at position 2.0 on a query I’ll never see, or at position 90. The pre/post split I ran above only reveals the above-threshold queries. The 403 impressions below could contain a dozen more latent near-page-1 results — or none.

The query breakdown for the 28-day window returned 25 visible queries. The page is matching at least 403 additional search variations that never surface. Some of those were there before any improvement. Some might be new. I can’t tell.

Finding #4: The og:title tag is confirmed still live

While verifying the P/E calculator’s HTML state after the improvement, I ran the standard og:title count:

$ curl -sSL -A "Googlebot/2.1" "https://valuefy.app/tools/pe-ratio-calculator/" | 
  grep -cE '<meta property="og:title"'
2

Two tags. Static generic one in index.html, React Helmet one in the rendered output. Eight weeks since it was first flagged. Still there.

I won’t relitigate this — Week 5 covered why it exists and what the wrong assumption was. But it’s worth noting that every improvement the automation commits — including the structured data additions and benchmark updates on the P/E page — lands on a page that still ships a generic site-wide title as its first og:title signal.

What I’m going to do about it

1. Run pre/post splits before claiming attribution. From now on: whenever an improved page appears in the weekly GSC data, the pre-improvement window check runs first. The aggregate is for trend-watching; the narrow window is for attribution.

2. Check “ratio calculator” in two weeks. It appeared post-improvement at position 9.0 with 1 impression — too thin to act on, but if it appears consistently in the next cycle, it means the structured data additions are catching broader classification queries. That would be the actual story of what the automation builds.

3. Run the same analysis on three prior improvements. The April batch covered EBITDA, ROAS, CTR, CPC, DCF, Cap Rate, and a dozen others. Some of those pages are generating impressions. I want to know whether the rankings on those pages were latent before the improvements or created by them — same method, pre/post split on the commit date.

4. Fix the static og:title. Not because it’s clearly blocking anything — the P/E calculator is generating clicks and position movement despite it. But eight weeks is long enough. It’s one line removed from index.html.

The uncomfortable lesson

Aggregate time windows are good for tracking trends. They are bad for attribution.

When I saw “forward pe calculator” at position 6.7 in the 28-day data after a May 13 improvement that explicitly added forward P/E content, my brain completed the pattern: improvement → ranking. The temporal overlap made it feel true. The aggregate was doing nothing wrong — it was accurately reporting a 28-day average. I was the one reading sequence as causation.

Every improvement the automation makes now has to be verified with a pre/post split before I write about it. Not as a final check, but as the first step. If the ranking was already there, the post isn’t about that ranking. If the ranking appeared cleanly after the commit, then we have something to say.

The good news: the improvement did real work. “Ratio calculator” at position 9.0. “Pe ratio calculator” moving from 60 to 50. New query surface that wasn’t there before. That’s a more modest story than “the automation hit page 1” — but it’s a true one, and it’ll compound if the pattern holds across 30+ pages.

I’ll come back in two weeks with the pre/post analysis on the April batch. Either the rankings were latent and the automation is revealing them, or the improvements are actually creating new surface. That’s a question the data can answer.

I’m running these experiments on valuefy.app and writing the findings as I go. If you’re building programmatic SEO or fighting the same “the aggregate looks fine but the distribution is hiding something” problem, I’d like to compare notes — drop a comment or reach out.

I also run AImiten, where we build AI tooling for companies. This side project is where ideas get stress-tested before they touch client work.

1. Why Slowly Changing Dimensions Matter

In data engineering, not all data changes the same way.

Some data changes constantly, like transactions, clicks, payments, and sensor readings. These are usually facts: events that happen at a specific point in time.

But other data changes slowly.

A customer changes their address.
A product changes category.
An employee moves to a new department.
A supplier changes region.
A user upgrades from a free plan to a premium plan.

These changes do not happen every second, but when they happen, they matter a lot.

Imagine you are building a sales report. A customer originally lived in Nairobi, then moved to Mombasa. If you simply update the customer record, all their historical sales may suddenly appear as if they happened in Mombasa.

That is a problem.

The business may ask:

“How much revenue did we make from Nairobi customers last year?”

But if you overwrote the customer’s location, your report may give the wrong answer.

This is the exact problem Slowly Changing Dimensions solve.

Slowly Changing Dimensions help data teams manage changes in descriptive data over time while keeping analytics accurate.

2. What Is a Slowly Changing Dimension?

A Slowly Changing Dimension, often shortened to SCD, is a technique used in data warehousing to manage changes in dimension tables over time.

A dimension table stores descriptive information.

For example:

This is not a transaction. It describes the customer.

Now imagine Mary moves from Nairobi to Kisumu.

The question becomes:

Should we overwrite Nairobi with Kisumu, or should we keep a history of both?

That decision is what SCD is all about.

This is where Slowly Changing Dimensions become useful.

They give data teams a structured way to decide how changes should be stored.

Sometimes we only care about the latest value.

Sometimes we want to preserve the original value.

Sometimes we need the full history of every meaningful change.

And sometimes we only need a simple previous-and-current comparison.

3. How Slowly Changing Dimensions Work

In a data warehouse, data is usually organized into fact tables and dimension tables.

Fact Tables

Fact tables store business events.

Examples:

• Sales
• Orders
• Payments
• Website clicks
• Deliveries

A sales fact table might look like this:

Dimension Tables

Dimension tables describe the facts.

A customer dimension might look like this:

The fact table tells us what happened.

The dimension table tells us who, what, where, or how it happened.

The challenge is that dimension data changes.

When Mary moves from Nairobi to Kisumu, we need to decide how to store that change.

There are different SCD types, but the most commonly used are:

• SCD Type 0
• SCD Type 1
• SCD Type 2
• SCD Type 3

Let’s go through them practically.

4. SCD Type 0: Keep the Original Value

SCD Type 0 means a value does not change in the data warehouse.

Once the value is loaded, it stays the same, even if the source system changes later.

In simple terms, Type 0 says:

“Keep the original value as it was first recorded.”

In real data warehouse work, Type 0 appears often for fields that should represent the original state of something. But many teams do not always call it “SCD Type 0” explicitly.

They may simply say:

“This field should never be updated.”

Or:

“Preserve the original value.”

So conceptually, Type 0 is common. The name “Type 0” is just less commonly emphasized.

Good examples of Type 0 fields are:

• Original signup date
• First purchase date
• Original registration country
• Original acquisition channel
• Original product launch date
• Original employee hire date

For example, imagine Mary Wanjiku first registered as a customer while living in Kenya through an Instagram campaign.

Later, Mary may move cities, upgrade her customer type, or start coming through email campaigns.

But the original acquisition channel should still remain Instagram Ads.

Why?

Because it tells the business how Mary was first acquired.

If we overwrite that value, we lose the ability to answer questions like:

“Which marketing channel originally brought us our best customers?”

That is where Type 0 is useful.

It protects values that describe the original state of a record.

5. SCD Type 1: Overwrite the Old Value

SCD Type 1 is the simplest approach.

When a value changes, you overwrite the old value with the new one.

Before:

After Mary moves:

The old city is gone.

When Type 1 Makes Sense

SCD Type 1 is useful when history does not matter.

For example:

• Fixing a spelling mistake
• Correcting wrong data
• Updating an email address
• Updating a phone number
• Correcting a product name typo

If the original value was wrong, you usually do not want to preserve it.

Example

Imagine a customer’s name was loaded as:

Mary Wanjikuuu

Then later corrected to:

Mary Wanjiku

You do not need historical tracking for the typo. You just update the record.

That is SCD Type 1.

The Risk

The risk with Type 1 is that it destroys history.

If city changes from Nairobi to Kisumu, all past reports will now treat Mary as a Kisumu customer, even if she lived in Nairobi when the sales happened.

So Type 1 is simple, but dangerous when historical accuracy matters.

6. SCD Type 2: Keep Full History

SCD Type 2 is the most important and most commonly used SCD technique in analytics.

Instead of overwriting the old record, you create a new row when important attributes change.

Before Mary moves:

After Mary moves to Kisumu:

Notice something important.

The customer_id stays the same because it represents the real-world customer.

But the customer_key changes because each historical version gets its own unique warehouse key.

This is usually called a surrogate key.

Why This Matters

Now, if Mary made a purchase while living in Nairobi, the fact table can point to the Nairobi version of her customer record.

If she made another purchase after moving to Kisumu, that sale can point to the Kisumu version.

This allows historical reports to stay accurate.

Example Fact Table

The first sale belongs to Mary when she was in Nairobi.

The second sale belongs to Mary when she was in Kisumu.

That is the power of SCD Type 2.

7. SCD Type 3: Store Limited History in Columns

SCD Type 3 keeps limited history by adding extra columns.

For example:

This lets you see the current value and one previous value.

When Type 3 Makes Sense

SCD Type 3 is useful when you only care about a small amount of history.

For example:

• Previous region and current region
• Previous plan and current plan
• Previous department and current department

But it does not scale well if changes happen many times.

What happens if Mary moves from Nairobi to Kisumu, then Nakuru, then Eldoret?

You would need more columns:

previous_city_1
previous_city_2
previous_city_3

That becomes messy quickly.

So Type 3 is useful, but only for very specific cases.

8. Practical Example in a Data Warehousing Project

Let’s say you are building a sales analytics warehouse for an e-commerce company.

You have data coming from:

• PostgreSQL for application data
• Kafka for order events
• Airflow for orchestration
• dbt for transformations
• Snowflake, BigQuery, Redshift, or PostgreSQL as the warehouse

Your source customer table in PostgreSQL looks like this:

Later, the same customer changes:

Now the data team must decide:

Do we overwrite the old record?

Or do we preserve the old version?

And what do we do with the original acquisition channel?

In this example:

• acquisition_channel can be treated as Type 0 because it represents how Mary was originally acquired.
• city and customer_type can be treated as Type 2 because they affect historical reporting.

For analytics, this is why we often combine different SCD behaviors in the same dimension table. Some fields preserve the original value, while others keep full history.

A Type 2 customer dimension may look like this:

Now your reports can answer questions like:

• How many sales came from Nairobi customers in February?
• How much revenue came from Premium customers after March?
• What was the customer type at the time of purchase?
• How many customers upgraded from Regular to Premium?

Without SCD Type 2, these questions become difficult or inaccurate.

9. A Simple SCD Type 2 Flow

A typical SCD Type 2 pipeline works like this:

Step 1: Load the Latest Source Data

You extract the latest customer data from the source system.

This could come from PostgreSQL, an API, a CSV file, or CDC events from Kafka.

Step 2: Compare Source Data With Current Dimension Records

You compare the incoming record with the current active record in the warehouse.

For example, compare:

source.city
source.customer_type

against:

dim_customer.city
dim_customer.customer_type

Step 3: Detect Changes

If nothing changed, do nothing.

If important attributes changed, expire the old record.

For example:

Step 4: Insert a New Current Record

Then insert the new version:

Step 5: Use the Correct Dimension Version in Fact Tables

When loading fact data, join the fact date to the correct dimension record using the validity period.

For example:

SELECT
    f.order_id,
    d.customer_key,
    f.order_date,
    f.amount
FROM staging_orders f
JOIN dim_customer d
    ON f.customer_id = d.customer_id
   AND f.order_date >= d.valid_from
   AND (
        f.order_date < d.valid_to
        OR d.valid_to IS NULL
   );

This ensures the order connects to the correct version of the customer.

10. Common Mistakes Beginners Make

Mistake 1: Using Type 1 When History Matters

This is probably the most common mistake.

Beginners often overwrite dimension records because it feels simple.

But later, when the business asks historical questions, the warehouse cannot answer correctly.

For example:

“What was revenue by customer region last year?”

If you overwrote all customer regions with the latest value, the report will be wrong.

How to Avoid It

Before choosing Type 1, ask:

“Will the business ever need to know what this value was in the past?”

If yes, consider Type 2.

Mistake 2: Tracking Every Column as Type 2

Not every change deserves a new historical version.

For example, do you really need a new customer dimension row when the phone number changes?

Maybe not.

If you track every small change, your dimension table can grow unnecessarily large and become harder to manage.

How to Avoid It

Classify columns carefully.

For example:

The decision should be based on reporting needs, not just technical preference.

Mistake 3: Not Using a Surrogate Key

A big mistake is using the source system ID as the primary key for the dimension table.

For example, using customer_id as the only key.

That becomes a problem in Type 2 because the same customer can have multiple historical versions.

Better Approach

Use:

• customer_id as the business key
• customer_key as the warehouse surrogate key

Example:

The surrogate key uniquely identifies each version.

Mistake 4: Forgetting the Current Flag

Without an is_current column, it becomes harder to query the latest version of each record.

You would have to check for:

valid_to IS NULL

That works, but is_current makes queries easier and clearer.

A good SCD Type 2 table usually has:

valid_from
valid_to
is_current

Some teams also add:

created_at
updated_at
record_hash

Mistake 5: Poor Date Handling

SCD Type 2 depends heavily on dates.

If your valid_from and valid_to values are wrong, your historical joins will be wrong.

Common problems include:

• Overlapping date ranges
• Gaps between versions
• Incorrect timezone handling
• Using load date instead of actual business effective date
• Not handling late-arriving data

How to Avoid It

Be very intentional about what your dates mean.

For example:

• valid_from: when this version became valid
• valid_to: when this version stopped being valid
• loaded_at: when the data entered the warehouse

These are not always the same thing.

11. Best Practices for SCD

Use SCD Type 2 for Business-Critical History

If a change affects reporting, segmentation, revenue analysis, or compliance, preserve history.

Examples:

• Customer region
• Customer plan
• Product category
• Sales territory
• Employee department
• Account status

These are usually worth tracking with Type 2.

Use Hashing to Detect Changes

Instead of comparing many columns one by one, you can create a hash from the important attributes.

Example:

MD5(CONCAT(city, customer_type, region))

Then compare the source hash with the current dimension hash.

If the hash changes, something important changed.

This makes SCD pipelines easier to maintain, especially when there are many columns.

Keep Your SCD Logic Clear

SCD logic can become confusing quickly.

Use clear column names like:

valid_from
valid_to
is_current

Avoid unclear names like:

start
end
flag

Your future self and your teammates will thank you.

Document Which Columns Are Tracked

Do not leave SCD behavior hidden inside SQL code only.

Document which columns are:

• Type 0
• Type 1
• Type 2
• Type 3
• Ignored
• Used for change detection

This is especially important in team environments.

Avoid Duplicates in Current Records

For a Type 2 dimension, each business key should have only one current record.

This should be true:

One customer_id = one current record

You can test this with SQL:

SELECT
    customer_id,
    COUNT(*) AS current_records
FROM dim_customer
WHERE is_current = true
GROUP BY customer_id
HAVING COUNT(*) > 1;

If this query returns rows, your dimension has a problem.

Test for Overlapping Validity Periods

For each business key, the date ranges should not overlap.

Bad example:

These overlap between March 15 and April 1.

That means a sale on March 20 could match both records.

That is dangerous.

12. When to Use Slowly Changing Dimensions

Use SCD when dimension data changes and those changes affect analysis.

Good use cases include:

• Customer address history
• Product category history
• Subscription plan changes
• Employee department changes
• Supplier region changes
• Account status changes
• Sales territory changes

SCD is especially useful in data warehouses and analytics systems where historical accuracy matters.

For example:

“Show revenue by the customer’s region at the time of purchase.”

That is a classic SCD Type 2 problem.

13. When SCD May Not Be the Best Choice

SCD is powerful, but not every situation needs it.

Do Not Use Type 2 for Every Small Change

If a field changes often and does not matter historically, Type 2 may create unnecessary complexity.

For example:

• Last login timestamp
• Profile picture URL
• Phone number
• Minor spelling corrections
• Temporary status fields

For these, Type 1 may be enough.

Be Careful With Very High-Volume Changes

If a dimension changes too frequently, Type 2 can grow very fast.

At that point, you may need a different modeling approach, such as:

• Event sourcing
• Audit tables
• Snapshot tables
• Data lake versioning
• Change Data Capture history

SCD is best for slowly changing descriptive attributes, not every event that happens in the system.

14. SCD in Modern Data Tools

SCD is not limited to traditional warehouses.

You can implement SCD patterns in many modern data stacks.

In dbt

dbt supports snapshots, which are commonly used to implement SCD Type 2.

A dbt snapshot can track when records change and automatically create historical versions.

In Airflow

Airflow can orchestrate SCD pipelines by scheduling extraction, staging, comparison, and dimension loading tasks.

In Spark

Spark is useful when you are handling large-scale dimension updates.

You can compare source and target datasets, detect changes, and write updated records to a lakehouse or warehouse.

In Kafka and CDC

Kafka can stream changes from source systems.

For example, using CDC tools, you can capture customer updates from PostgreSQL and send them into Kafka.

From there, you can process those changes and update your dimension tables.

In Warehouses

SCD can be implemented in:

• Snowflake
• BigQuery
• Redshift
• PostgreSQL
• Databricks
• SQL Server

The concept stays the same, even if the syntax differs.

15. Final Summary

Slowly Changing Dimensions help data engineers manage changes in dimension data over time.

They solve an important problem:

How do we keep analytics accurate when descriptive data changes?

The most common SCD types are:

In real data engineering projects, SCD Type 2 is especially important because it allows the warehouse to answer historical questions correctly.

Without SCD, reports can quietly become wrong.

A customer’s current city may overwrite their past city.
A product’s new category may rewrite old sales history.
An employee’s new department may change old performance reports.

That is why SCD matters.

The practical takeaway is simple:

Whenever a dimension value changes, ask whether the business needs to remember the old value.

If the value should never change, Type 0 may be the right choice.

If the old value does not matter, Type 1 may be enough.

If the business needs full history, Type 2 is usually the best choice.

If the business only needs a simple previous-and-current comparison, Type 3 may work.