DEV Community: 137Foundry

Idempotent Data Reconciliation - Production Patterns That Don't Create Noise

137Foundry — Wed, 13 May 2026 11:16:32 +0000

The first version of a data reconciliation system almost always has the same failure mode: it works correctly, then gets deployed to production, and immediately generates hundreds of duplicate alerts. The same discrepancy is reported every time the job runs. Operators learn to ignore the alert channel within a week. The system that was supposed to improve data reliability becomes noise.

The root cause is the same in almost every case: the comparison engine was built as a stateless script. Each run produces a fresh list of discrepancies with no awareness of what previous runs found. Without state management, every run is the first run.

Making a reconciliation system idempotent - ensuring that running it multiple times produces the same observable outcome as running it once - requires both deterministic comparison logic and persistent state that survives between runs. This piece covers the patterns that make that work in production.

Why Idempotency Is the Right Frame

Idempotency is a property typically discussed in the context of API calls and database writes: calling an endpoint or executing a write multiple times should have the same effect as calling it once. The same principle applies to reconciliation runs.

A reconciliation run is idempotent when: running it twice on unchanged source data produces zero new alerts, no duplicate discrepancy records, and the same state in the tracking store as running it once. The run reads, compares, and updates state - but only creates new signals where something actually changed.

This property requires two things. First, the comparison engine must produce deterministic output from the same input. The same two records compared twice should produce the same list of discrepancies. Second, the discrepancy tracker must distinguish between "new discrepancy" and "discrepancy we already know about."

The Stable Discrepancy Identifier

The mechanism that enables idempotent state management is a stable identifier for each discrepancy. This identifier must be deterministic from the discrepancy's properties and stable across runs.

A practical stable identifier is a hash of the record's comparison key, the field name, and optionally the values from each source. The comparison key ensures the identifier is unique to a specific record. The field name ensures it is unique to a specific field on that record. Including the values is optional but useful if you want different values for the same field on the same record to generate distinct discrepancy records.

import hashlib
import json

def discrepancy_id(comparison_key, field_name, value_a=None, value_b=None):
    """
    Generates a stable identifier for a discrepancy.
    Omit value_a and value_b to make the ID value-independent
    (same discrepancy regardless of the specific values).
    """
    payload = {
        "key": str(comparison_key),
        "field": field_name,
    }
    if value_a is not None or value_b is not None:
        payload["values"] = [str(value_a), str(value_b)]

    canonical = json.dumps(payload, sort_keys=True)
    return hashlib.sha256(canonical.encode()).hexdigest()[:16]

Using this identifier, the tracker upserts on each run. SQLAlchemy provides upsert support via its ORM and core layers for managing the discrepancy state table. Redis is useful as a deduplication cache for the current run's processed record set.: if a discrepancy with this identifier exists, update its last_seen timestamp. If it does not exist, create a new record and queue an alert.

The Discrepancy Lifecycle

A production discrepancy tracker manages four states:

Open: The discrepancy was detected and has not yet been resolved. Each subsequent run that still finds the discrepancy updates last_seen without creating a new alert.

Acknowledged: An operator has reviewed the discrepancy and flagged it as known. Acknowledged discrepancies do not generate repeat alerts even if they persist across runs. This is the key escape valve for cases where the discrepancy is a known acceptable difference (two systems intentionally representing the same entity differently) rather than an error.

Resolved: A run completed without finding the discrepancy. The tracker marks it resolved and can optionally send a resolution notification.

Escalated: The discrepancy has been open beyond a configured threshold and has been escalated to a higher-urgency channel or a human-review queue.

The state machine transitions:

New detection -> Open (alert fires once)
Open, found again -> Open (update last_seen, no new alert)
Open, not found -> Resolved (optional resolution notification)
Open, beyond threshold -> Escalated
Acknowledged, found again -> Acknowledged (no alert, no state change)
Acknowledged, not found -> Resolved

def process_discrepancy(tracker, disc_id, field_name, val_a, val_b):
    existing = tracker.get(disc_id)
    if existing is None:
        tracker.create(disc_id, field_name, val_a, val_b, status="open")
        alert(disc_id, field_name, val_a, val_b)
    elif existing["status"] == "open":
        tracker.update_last_seen(disc_id)
    elif existing["status"] == "acknowledged":
        pass  # known, no action needed

Handling Resolved Discrepancies

Detecting resolution requires comparing the set of open discrepancies against the set of discrepancies found in the current run. Any open discrepancy that does not appear in the current run's findings has resolved.

def resolve_cleared(tracker, current_run_ids):
    open_discrepancies = tracker.get_by_status("open")
    for disc in open_discrepancies:
        if disc["id"] not in current_run_ids:
            tracker.mark_resolved(disc["id"])

This logic must account for partial comparisons. If the current run only compared a subset of records (incremental mode), discrepancies for records not included in this run's comparison should not be marked resolved - they were simply not checked. The resolution check should only run against discrepancies whose records were included in the current run's comparison scope.

One practical approach: tag each discrepancy with the comparison scope (date range, record ID range, or full) at creation time, and only attempt resolution for discrepancies whose scope is covered by the current run.

Comparison Determinism

Idempotent alerting is only possible if the comparison engine itself is deterministic. The same two records compared twice must produce the same discrepancy output. Non-determinism typically enters through:

Floating-point comparison without a tolerance. Direct equality comparison of float values can produce inconsistent results when the same underlying value is represented with different floating-point precision across systems. Always use a tolerance threshold for float comparisons and enforce it consistently.

Timestamp handling without normalization. Comparing timestamps from two systems without normalizing to UTC and stripping subsecond precision that one system tracks and the other does not will produce spurious discrepancies on repeated comparisons of the same records.

Null handling inconsistency. If your comparison treats null-in-A vs value-in-B as a discrepancy in some contexts but not others, the same pair of records can produce different results on different runs depending on which code path evaluates them.

Document and enforce the comparison rules for each field type before writing the state management layer. Non-determinism in comparison logic makes the state management layer impossible to reason about.

The Value of Acknowledged State

The acknowledged state deserves emphasis because it is the pattern that makes reconciliation systems sustainable in environments where not every discrepancy is an error.

Two systems that represent the same customer entity will sometimes have legitimately different representations of certain fields. A billing system rounds currency amounts; the CRM does not. An analytics system uses a different timezone for date fields than the operational system. These are not errors - they are design decisions. But they will surface as discrepancies in any comparison.

Without acknowledged state, operators must either tune the comparison to exclude these fields (reducing coverage) or accept that those discrepancy alerts will fire on every run forever (producing noise). Acknowledged state gives you a third option: detect the discrepancy once, review it, and mark it as an accepted difference that should not generate alerts on subsequent runs but should still appear in audit reports.

The guide on data reconciliation system design at 137Foundry covers the full architecture including the discrepancy tracker schema and how the state machine integrates with the comparison engine and scheduling layer.

Production Deployment Checklist

Before deploying a reconciliation system to production:

Verify comparison logic is deterministic: run it twice on the same data and compare the output byte-for-byte
Test resolution detection with a controlled discrepancy that you clear between runs
Verify that acknowledged discrepancies do not generate alerts on re-detection
Test partial-scope runs to confirm they do not incorrectly resolve out-of-scope discrepancies
Use Prefect or a similar orchestration tool to handle scheduling, retry policies, and run health monitoring. Set up monitoring on the reconciliation job itself: how long each run takes, how many records were compared, how many discrepancies were found
Define alert thresholds for escalation before any real discrepancies appear

A reconciliation system that generates reliable, low-noise, actionable signals is worth significantly more than one that technically detects everything but has trained its operators to filter the channel. Idempotency is the engineering property that makes the difference between the two.

7 Free Tools for Data Pipeline Reconciliation and Cross-Source Validation

137Foundry — Wed, 13 May 2026 11:12:01 +0000

Building a data reconciliation system from scratch requires decisions at several layers: how to connect to sources, how to run comparisons, how to orchestrate runs, and how to alert on findings. Each of these layers has free and open-source tooling that is production-grade. Some tools address a single layer; others span multiple.

This list covers the tools worth evaluating at each layer, what they specifically do well for reconciliation use cases, and where they fit in a complete reconciliation architecture.

1. Great Expectations (Data Validation and Quality Gates)

Great Expectations is the most widely adopted open-source data validation framework for Python. You define "expectations" - assertions about what your data should look like - and run validation suites that check whether your data meets them.

For reconciliation, Great Expectations is most useful at the source validation layer: confirm that each source's data is internally consistent before attempting cross-source comparison. If source A's records have unexpected null rates or value distributions, that signal belongs in the comparison context.

It also supports cross-dataset expectations in newer versions, allowing assertions that reference values from a separate data source. This is not full reconciliation, but it covers a subset of comparison scenarios without requiring custom code.

Best for: Teams that want declarative data quality rules they can version-control and run as part of a CI/CD or pipeline step, alongside custom reconciliation logic.

Limitation: Does not manage discrepancy state or handle the lifecycle of identified issues. It reports findings but does not track them across runs.

2. dbt (Data Build Tool - Testing Layer)

dbt is primarily a SQL-based data transformation tool, but its testing framework is useful for reconciliation in warehouse environments. dbt tests support singular tests (custom SQL assertions) and generic tests (schema tests like not_null, unique, relationships).

The relationships test validates that a foreign key in one model resolves to a valid record in another model - a form of cross-source reconciliation within the same data warehouse. The dbt-audit-helper package adds comparison tests that compare two versions of the same model and surface field-level differences.

For teams already using dbt to transform data from multiple sources into a warehouse, the testing framework provides reconciliation coverage with minimal additional infrastructure.

Best for: Warehouse-centric teams that want reconciliation coverage expressed in SQL, tested alongside their transformation models.

Limitation: Works within a single database connection. Cross-system reconciliation (comparing a live API against a database) requires custom source connectors before dbt can handle the comparison.

3. Debezium (Change Data Capture)

Debezium is an open-source CDC (change data capture) platform that streams database changes - inserts, updates, deletes - from supported databases (PostgreSQL, MySQL, MongoDB, SQL Server) to downstream consumers via Apache Kafka.

For event-driven reconciliation architectures, Debezium is the source connector layer. When you need to trigger reconciliation checks in near-real-time as changes occur in a database, Debezium captures those changes and publishes them to a Kafka topic. The reconciliation system subscribes to the topic, waits for the propagation window, and checks the destination system.

Best for: Teams with relational database sources who want event-driven reconciliation without instrumenting application code to emit events manually.

Limitation: Requires a Kafka cluster (or Kafka-compatible service) as the transport layer. Adds operational complexity compared to simpler polling approaches.

4. Apache Kafka (Event Streaming for Event-Driven Reconciliation)

Apache Kafka is the most widely used distributed event streaming platform and the standard transport layer for event-driven reconciliation architectures.

In a reconciliation context, Kafka serves as the queue between change events (emitted by source systems or CDC tools) and the reconciliation consumer (which reads events and triggers comparisons). Kafka's consumer group model allows multiple reconciliation consumers to process events in parallel without duplicate processing.

Kafka's retention configuration is important for reconciliation: retaining events long enough that a consumer outage does not result in missed events during recovery.

Best for: Organizations building event-driven reconciliation where multiple source systems emit changes and the reconciliation layer needs to consume from all of them reliably.

Limitation: Kafka has non-trivial operational overhead. For teams without existing Kafka infrastructure, starting with scheduled batch reconciliation is simpler. Kafka makes sense when you need near-real-time detection and have the infrastructure to support it.

5. Prefect (Workflow Orchestration)

Prefect is a Python-based workflow orchestration framework. Its free tier (Prefect OSS + Prefect Cloud free) provides scheduling, run history, retry policies, and a monitoring UI for Python-based workflows.

For reconciliation, Prefect handles the scheduling and operational concerns: trigger runs on a schedule or in response to events, retry failed runs with configurable backoff, surface run health in a dashboard, and notify on run failures.

Prefect's flow and task model maps naturally to reconciliation: source extraction is a set of tasks, comparison is a task, discrepancy persistence is a task, and alerting is a task. Each can retry independently on failure.

Best for: Teams building reconciliation in Python who want scheduling, retry, and monitoring without deploying Apache Airflow's full infrastructure.

Limitation: The free tier has limitations on concurrent runs and cloud-managed infrastructure. Self-hosted Prefect requires running the orchestration server separately.

6. Pandas (In-Memory Comparison Engine)

Pandas is the standard Python library for tabular data manipulation. For reconciliation jobs that operate on data sets that fit comfortably in memory (up to several million rows depending on column count and available RAM), Pandas provides efficient merge and comparison operations that would otherwise require custom SQL or database joins.

The core pattern: load each source into a DataFrame, merge on the comparison key, and compare columns across the merged result. Pandas handles the row matching efficiently with DataFrame.merge() using how='outer' to surface unmatched rows from either source.

import pandas as pd

merged = pd.merge(df_a, df_b, on='customer_id', 
                  suffixes=('_a', '_b'), how='outer', indicator=True)

# Records in A only
only_in_a = merged[merged['_merge'] == 'left_only']

# Records in B only
only_in_b = merged[merged['_merge'] == 'right_only']

# Matched but disagreeing on email field
field_discrepancies = merged[
    (merged['_merge'] == 'both') & 
    (merged['email_a'] != merged['email_b'])
]

Best for: Reconciliation scripts operating on data sets that fit in memory, particularly for prototyping comparison logic before optimizing for scale.

Limitation: Memory-bound. For data sets with tens of millions of rows, database-side comparison or distributed processing (Apache Spark) is more appropriate.

7. Apache Spark (Large-Scale Distributed Comparison)

Apache Spark provides distributed in-memory data processing and is the appropriate tool when the data set to be reconciled does not fit in a single machine's memory, or when parallelizing the comparison across a cluster would reduce runtime from hours to minutes.

Spark's DataFrame API mirrors Pandas in most comparison patterns, so Pandas-based comparison logic can be ported to Spark with moderate effort. The key difference is that Spark partitions data across the cluster and processes partitions in parallel, making it suitable for comparing data sets with hundreds of millions of rows.

PySpark is the Python interface and is free. Deploying a Spark cluster requires infrastructure (Databricks, Amazon EMR, Google Dataproc, or self-managed) which has associated costs.

Best for: Financial institutions, large e-commerce platforms, or any organization where reconciliation involves data sets with hundreds of millions of records.

Limitation: Operational overhead of a Spark cluster is significant. Most teams should start with Pandas or database-side SQL comparison and move to Spark only when scale requires it.

Putting the Stack Together

A practical reconciliation stack for a mid-size team:

Extraction: custom connectors + Debezium for database sources
Comparison engine: Pandas for data sets under 10M rows
Orchestration: Prefect for scheduling, retry, and monitoring
Validation at source: Great Expectations for pre-comparison quality checks
Transport (event-driven layer): Kafka via Confluent free tier or self-managed

The guide on building an automated data reconciliation system at 137Foundry covers the comparison engine and discrepancy tracker architecture that sits between the extraction tools and the orchestration layer - the core logic that these tools plug into.

Start with the simplest stack that handles your data volume. Prefect + Pandas + a PostgreSQL discrepancy tracker is enough for most use cases. Add Kafka and Debezium when detection latency becomes an operational requirement, and move to Spark when Pandas starts to strain under the data volume.

How to Read Google Search Console Crawl Stats to Debug Indexing Problems

137Foundry — Tue, 12 May 2026 14:37:13 +0000

If a page on your site isn't showing up in Google Search, the first question is whether Googlebot has even tried to crawl it. The second question is whether the crawl attempt succeeded. Google Search Console's Crawl Stats report answers both questions, at least at the aggregate level, and it's one of the most underused diagnostic tools in technical SEO.

This guide walks through how to navigate to the report, what each section shows, how to read the data to identify specific problems, and what to do when you find them.

Step 1: Navigate to the Crawl Stats Report

Open Google Search Console and select your property. In the left sidebar, go to Settings. Under "Crawling," you'll find the Crawl Stats report. Click "Open Report" to view the full data.

The report shows data for the last 90 days. It updates daily, so changes you make to your site (new robots.txt rules, sitemap updates, redirect fixes) will start appearing in the data within a few days, though their full effect may take weeks to show.

Step 2: Read the Crawl Requests Graph

The top of the report shows a graph of total crawl requests per day. This is the first number to understand: how many pages is Googlebot crawling on your site each day, and is that number changing over time?

For most sites, the trend should be relatively stable, with spikes around major content publishing events and gradual increases as the site grows. Patterns that indicate problems:

Sharp drops in crawl volume often mean Googlebot was blocked -- by a robots.txt change, a server configuration error, or a DNS issue. If you see a cliff in the graph, check your server logs and robots.txt for changes around that date.

Sustained high crawl volume relative to page count often means Googlebot is crawling a large number of low-value URLs. If your site has 10,000 pages but Googlebot is making 50,000 requests per day, it's spending most of its time on URL variants, redirects, or URLs that aren't in your sitemap.

A declining trend over time can indicate that Googlebot is finding fewer new or updated pages and is reducing its crawl frequency. This is sometimes appropriate (a stable site that doesn't publish often), but can also indicate that content isn't being discovered because it's buried in the site structure.

Step 3: Analyze the Response Code Breakdown

Below the crawl volume graph, Crawl Stats shows a breakdown by response code. This is where the diagnostic detail lives.

2xx (Success): Pages that returned a successful response. The proportion of your total crawl requests that return 2xx should be high -- ideally above 90%. If it's significantly lower, other response codes are consuming crawl budget.

3xx (Redirects): Pages that returned a redirect. A small percentage of redirects is expected, but a high percentage indicates redirect chains. Every redirect response is a crawl request that didn't result in a content crawl. If 20% of your daily crawl requests return redirects, Googlebot is spending a fifth of its crawl budget just following pointers to other URLs.

4xx (Client Errors): Pages that returned 404 or similar errors. A small number of 404s is normal -- Googlebot follows external links to your site that may point to deleted pages. A large number suggests dead internal links or a sitemap that hasn't been cleaned up after page deletions.

5xx (Server Errors): Pages that returned server errors. Any sustained volume of 5xx responses is a problem. It means pages that should be accessible are failing, Googlebot is wasting crawl budget on them, and the errors may be reducing Googlebot's crawl rate for your domain.

Step 4: Check the Response Time Data

The report also shows average response time for crawled pages. Faster response times allow Googlebot to crawl more pages per session. Google's crawl budget documentation specifically notes that slow response times reduce crawl rate.

Watch for response time spikes that correlate with drops in crawl volume -- this is a direct signal that server performance is affecting how aggressively Googlebot crawls your site. Sustained response times above 500ms are worth investigating; anything above 1000ms is likely affecting your crawl rate.

Step 5: Review the File Type Breakdown

The file type section shows what types of resources Googlebot is crawling: HTML pages, images, JavaScript files, CSS files, and so on. For crawl budget purposes, you care most about HTML, but the breakdown tells you if Googlebot is spending requests on resource files.

If Googlebot is making thousands of requests for JavaScript or CSS files, this typically means those files aren't cached properly or are being changed frequently. Googlebot needs to periodically recrawl resource files to render JavaScript-heavy pages, so some of this is expected, but unusually high resource file crawl volume is worth checking.

Step 6: Connect Crawl Stats to Indexing Gaps

Crawl Stats tells you what Googlebot crawled. The Coverage report (under Indexing in the left sidebar of Google Search Console) tells you what pages are actually in Google's index.

Compare the two. If Crawl Stats shows high daily crawl volume but Coverage shows fewer indexed pages than expected, one of these things is happening:

Many of the crawled URLs are redirects, errors, or noindexed pages (which Googlebot crawls but doesn't index)
Googlebot is crawling a large number of URL variants and only indexing the canonical version
Pages are being crawled but failing Googlebot's quality threshold for indexing

Cross-reference Coverage's "Excluded" section with Crawl Stats response codes. If you see high redirect volume in Crawl Stats and a large "Excluded -- Redirect" count in Coverage, your redirect chains are definitively the problem.

Step 7: Look for Timing Patterns

Scroll down to the "How Googlebot crawled your site" section if available, or look at the daily data points in the main graph. For sites with regular content publishing schedules, there should be crawl spikes after publication days -- Googlebot notices new sitemaps and internal links and crawls more aggressively.

If you're publishing content but not seeing corresponding crawl spikes, it can mean Googlebot's crawl priority for your domain is low because of quality signals elsewhere on the site. Too much low-value crawl content trains Googlebot to be less aggressive about discovering new pages, because previous sessions didn't yield much indexable content.

Step 8: Diagnose Specific Problems

Based on what the data shows, here's how to connect Crawl Stats patterns to specific fixes:

Pattern	Likely Cause	Fix
High 3xx volume	Redirect chains, old URLs in sitemap	Update redirects to go direct; clean up sitemap
High 4xx volume	Dead internal links, deleted pages in sitemap	Audit internal links; remove 404s from sitemap
High crawl volume, low index count	Parameterized URLs, duplicate content	Add robots.txt rules; add canonical tags
Declining crawl volume	Crawl rate throttled due to slow server or low content quality	Improve response times; reduce low-value URL crawlable pool
Crawl spikes at wrong times	External links from high-traffic sources	Expected behavior, not a problem

Step 9: Track Changes Over Time

After making crawl budget fixes -- updating robots.txt, cleaning the sitemap, fixing redirect chains -- use Crawl Stats as your measurement tool. The changes won't be instant. Give it four to six weeks to see whether the response code ratios shift and whether total crawl volume adjusts.

The metric to focus on is crawl quality, not just crawl volume. Lower total crawl volume with a higher percentage of 2xx responses and faster response times means Googlebot is spending its budget more efficiently. That's the goal.

A deeper explanation of what causes crawl budget waste across parameterized URLs, redirect chains, thin content, and sitemap configuration -- and how to fix each category -- is covered in the comprehensive guide to crawl budget issues for complex web applications. The technical SEO team at 137Foundry uses this data-first diagnostic approach on every site audit, because Crawl Stats patterns almost always point toward the right fix before you've spent time on any manual investigation.

Additional context on crawl behavior is available through Screaming Frog SEO Spider, which simulates the crawl at the structural level and confirms patterns that Crawl Stats identifies only at the aggregate level.

7 Free Tools for Auditing Crawl Budget and Googlebot Coverage

137Foundry — Tue, 12 May 2026 14:37:12 +0000

Understanding how Googlebot is spending its time on your site requires more than a gut check. You need data: which URLs is it actually crawling, how often, and what responses is it getting? These seven free tools give you different angles on that question, and you'll typically need at least two or three of them working together to get a complete picture.

This list covers tools for passive monitoring (what does Google know right now), active crawling (simulate what Googlebot sees), and log-level analysis (what actually happened on the server). Different sites need different mixes depending on their size, platform, and specific crawl problems.

1. Google Search Console

Google Search Console is the starting point for any crawl audit. The Crawl Stats report (under Settings) shows how many requests Googlebot made per day, what response codes it received, and how response times trended over time. This data comes directly from Google's servers, so it reflects actual Googlebot behavior rather than a simulation.

The key metrics to track are: total daily crawl requests (compared to your total page count), the ratio of 3xx to 2xx responses (high 3xx volume indicates redirect chains), and average response time (which affects how aggressively Googlebot crawls your domain). The Coverage report shows which pages are indexed, which are excluded, and which are in an error state.

The limitation: Crawl Stats doesn't show you specific URLs. You know Googlebot made 5,000 requests yesterday and 30% were redirects, but you don't know which URLs triggered the redirects. That's where the next tools become useful.

2. Screaming Frog SEO Spider

Screaming Frog SEO Spider is a desktop crawler that simulates Googlebot's link-following behavior from a starting URL. The free tier crawls up to 500 URLs, which covers small sites completely and gives a useful sample for larger ones.

What makes it useful for crawl budget analysis: the internal link count per URL (shows which pages attract the most crawl attention), the redirect chain report (identifies multi-hop redirect paths that waste crawl requests), and the parameterized URL report (surfaces URLs with query strings that may be creating duplicate crawl targets).

The paid version removes the 500-URL cap and adds scheduled crawls and log file analysis, but the free version is enough to understand your site's structural crawl problems.

3. Ahrefs Webmaster Tools

Ahrefs offers a free Webmaster Tools tier that gives access to Site Audit for a connected domain. The crawler checks for crawlability issues including redirect chains, broken internal links, noindex pages receiving internal links, and pages with slow response times.

The free tier limits the crawl to a set number of pages and runs on a schedule, but for crawl budget diagnosis the audit data is valuable. The "indexability" report shows pages that Googlebot can access but that have signals preventing them from being indexed, which is useful for finding the noindex vs. indexed-anyway mismatches that indicate crawl budget waste.

4. Sitebulb

Sitebulb is a website auditing tool with a free trial period that includes its full crawl analysis capabilities. It's particularly good at visualizing site structure and crawl paths. The "crawl map" view shows the internal link hierarchy as a visual tree, which makes it easy to spot deeply-nested content or orphaned sections.

Sitebulb's "crawl budget efficiency" report specifically breaks down which URL categories are consuming the most crawl requests and flags patterns like parameterized URL variants and redirect chains. For sites where understanding the structural cause of crawl waste is the priority, it's one of the more useful tools in this list.

5. Semrush

Semrush includes a Site Audit tool in its free tier with limited monthly crawls. The crawl data covers issues relevant to crawl budget: redirect chains, pages blocked by robots.txt, non-indexable pages that are receiving internal links, and sitemap errors.

The free tier caps the number of pages per crawl, so it works better as a diagnostic sample on large sites than as a comprehensive audit. The value Semrush adds relative to the other tools here is that it shows external backlink data alongside crawl data, which helps identify whether pages with crawl problems also have incoming external links (a situation where removing the page from the sitemap requires more care).

6. Bing Webmaster Tools

Bing Webmaster Tools is often overlooked but has one capability that Google Search Console lacks: it lets you download a CSV of the specific URLs Bing crawled recently. This is useful as a cross-reference for your Googlebot log data, since Bingbot and Googlebot tend to have similar crawl patterns. If Bingbot is hitting a lot of parameterized URLs, Googlebot probably is too.

The Crawl Information report shows discovered URLs, crawl depth, and response codes. It also surfaces issues like pages that are slow to respond and pages blocked by robots.txt. Free with a verified site property, no usage limits.

7. Python + requests for Log File Analysis

Server access log analysis is the only way to see exactly which URLs Googlebot requested at the server level, not just what Google reports it crawled. Every request Googlebot makes appears in your web server logs with its user agent string (Googlebot/2.1). Parsing those logs reveals patterns that no third-party tool can show you.

The Python requests library isn't a log parser itself, but Python with standard library modules (like re and collections) is the fastest way to build a lightweight log analysis script. A basic script that filters log entries by user agent, counts requests per URL pattern, and outputs the top 50 most-requested URLs takes less than 30 lines and surfaces where Googlebot is spending its time immediately.

For sites hosted on platforms that don't expose access logs, this option isn't available -- but for self-hosted applications or sites with direct server access, it's the most complete data source on this list.

How to Use These Tools Together

The practical workflow for a crawl budget audit looks like this: start with Google Search Console to understand the scale and category of the problem (too many redirects? too many 404s? unusually high total request volume?). Then use Screaming Frog or Sitebulb to crawl the site and identify the structural URL patterns generating the waste. If you have log access, parse the logs to confirm which URLs Googlebot is actually hitting and compare that to what the crawler simulation predicted.

Ahrefs and Semrush add backlink context that's useful when deciding whether a problematic page should be removed from the sitemap entirely or just cleaned up. Bing Webmaster Tools provides a sanity check and fills in some data gaps that Google doesn't expose.

For a detailed walkthrough of what to do once you've identified the specific crawl budget problems -- parameterized URLs, redirect chains, thin content, and sitemap issues -- the guide on fixing crawl budget issues for large web applications covers each category of fix in practical terms. The 137Foundry technical SEO team uses this same toolset when auditing client sites, often discovering that the most impactful crawl budget fixes are in places the site owner wasn't expecting.

The key is not to rely on any single tool. Each one shows a partial picture, and the most useful insights come from comparing what different data sources reveal about the same underlying crawl behavior.

7 Free Tools for Building and Testing a Web Typography System

137Foundry — Mon, 11 May 2026 11:06:12 +0000

Building a typography system requires several distinct decisions: choosing a type scale ratio, selecting typefaces for different roles, implementing fluid sizing, checking readability and contrast, and generating the CSS. There is no single tool that handles all of this, but there are several excellent free tools that cover specific steps cleanly.

This is a working list of the tools used regularly in front-end and design system work, with notes on what each does well and where it fits in the typography system workflow.

Photo by Aldrich on Pexels

1. Utopia.fyi - Fluid Type and Space Generator

What it does: Generates complete fluid type scales and spacing scales using CSS clamp() values. Input your minimum and maximum viewport widths, base font size range, and scale ratio, and it outputs the full CSS custom properties block.

Best for: The foundation step of any fluid type system. Instead of calculating each clamp() value manually, Utopia generates the complete scale in seconds. It also generates a matching fluid space scale, so type and spacing scale in proportion.

Output format: Ready-to-paste CSS custom properties. The generated code follows well-established naming conventions and is compatible with any CSS architecture.

Free tier: Fully free, no account required.

Link: utopia.fyi

2. Type Scale (type-scale.com) - Modular Scale Visualizer

What it does: Shows a live preview of a modular type scale across any ratio (Minor Second, Major Third, Perfect Fourth, Golden Ratio, and others). Accepts a base font size and ratio, then displays the full step ladder.

Best for: The early decision about which ratio to use. The visual preview makes it immediately clear how "dramatic" or "subtle" different ratios will feel in practice. The Major Third (1.25) versus Perfect Fourth (1.333) comparison is often the most useful - close enough to seem similar, different enough to create a noticeably different visual hierarchy.

Output format: Shows the pixel values for each scale step. Copy the numbers to use in your own CSS implementation or hand them to Utopia for fluid generation.

Free tier: Fully free, no account required.

Link: type-scale.com

3. Modular Scale (modularscale.com) - Advanced Scale Calculator

What it does: The original modular scale tool by Tim Brown and Scott Kellum. Supports multiple base values (useful for scales with more complex harmonic relationships), custom ratios, and outputs scales with notation showing the mathematical relationship between each step.

Best for: Type systems that require more than one base frequency or non-standard ratios. Also useful for teams that want to document the mathematical rationale behind their scale choices.

Output format: Scale values with ratio notation. Less turn-key than Type Scale but more expressive for complex systems.

Free tier: Fully free.

Link: modularscale.com

4. Wakamaifondue - Variable Font Inspector

What it does: Drag any font file onto the browser window and Wakamaifondue shows you all axes the variable font supports, their ranges, named instances, features (ligatures, tabular figures, old-style numbers), and a live preview where you can adjust all axes interactively.

Best for: Understanding what a variable font actually offers before committing to it. Not all variable fonts expose the same axes. Some only vary weight. Others include optical size, width, and slant. Wakamaifondue shows you exactly what is available.

Output format: CSS code for any combination of axis values you set in the live preview.

Free tier: Fully free, runs in the browser.

Link: wakamaifondue.com

5. Google Fonts - Font Discovery and Pairing

What it does: Hosts over 1,400 free, open-source web fonts with previews at adjustable sizes, weight comparisons, and usage data. The knowledge base section includes detailed articles on typography principles, font selection by use case, and type pairing theory.

Best for: Finding fonts that meet specific role requirements (legible body text, distinctive headings, monospace for code). The filter options for classification, number of styles, and character support are useful for narrowing from 1,400+ options to a manageable shortlist.

Output format: CSS @import or tags for web loading. Also available for self-hosting via the downloadable zip files.

Free tier: All fonts are free, including commercial use.

Link: fonts.google.com

6. WebAIM Contrast Checker - Accessibility Compliance

What it does: Takes foreground and background color values (hex, RGB, or HSL) and returns the contrast ratio, pass/fail status against WCAG AA and AAA for both normal and large text, and a suggestion for the minimum change needed to pass if the current combination fails.

Best for: Typography color decisions - specifically, whether a specific font color on a specific background meets accessibility requirements. Any text element that serves informational content needs at least 4.5:1 contrast for body text (WCAG AA) or 3:1 for large text (18pt+ or 14pt bold).

Output format: Contrast ratio and pass/fail. Instant, no setup required.

Free tier: Fully free.

Link: webaim.org/resources/contrastchecker/

7. Fonts In Use - Real-World Font Examples

What it does: A catalog of typographic choices made in real published work, with photographs and detailed notes on which fonts appear in which roles, why they were chosen, and how they are used in combination.

Best for: Font evaluation in actual reading contexts rather than type specimen previews. Searching "sans-serif body" or "web interface" returns examples of fonts used in comparable contexts to your project, which is far more useful for decision-making than looking at a font specimen that shows it at 72pt.

Free tier: Fully free, browsable without an account.

Link: fontsinuse.com

How These Tools Fit Together

A typical typography system workflow using these tools:

Step 1 - Establish the scale: Use Utopia.fyi to generate a fluid type scale for your target viewport range. Preview alternative ratios in type-scale.com first if you are undecided.

Step 2 - Select typefaces: Browse Google Fonts filtered to your role requirements. Validate candidates at body text size by previewing at 16px in actual paragraph text. Check Fonts In Use for real-world examples of shortlisted fonts.

Step 3 - Inspect variable font support: If using variable fonts, load them into Wakamaifondue to understand available axes and generate the CSS for your implementation.

Step 4 - Verify contrast: Run each text color/background color pairing through WebAIM Contrast Checker and fix any that fail AA.

Step 5 - Implement and test: Write the CSS using the Utopia-generated clamp() values, apply the custom properties across all text elements, and verify at 375px, 768px, and 1280px viewports.

The full guide on web typography system design at https://137foundry.com covers the conceptual decisions behind each step - which scale ratio fits which design context, how to pair fonts by functional role, and how to implement the system with CSS custom properties for long-term maintainability.

For front-end development projects where typography system implementation is part of the scope, 137Foundry web development services handle the full design-to-CSS pipeline, from scale generation through component-level integration and accessibility review.

Revisiting this toolset periodically is worth doing as the ecosystem evolves. Google Fonts has continued expanding its variable font catalog and filtering options since 2022. WebAIM updates their contrast checker guidance alongside WCAG standard revisions. Both Utopia.fyi and Wakamaifondue have added features since their initial releases. Checking each tool for updates before starting a new project takes two minutes and occasionally surfaces options that were not available the last time you used it.

How to Build a Fluid Type Scale with CSS clamp() - A Complete Implementation Guide

137Foundry — Mon, 11 May 2026 11:06:08 +0000

Fixed font sizes require media queries at each breakpoint to adjust type. Fluid font sizes scale continuously with the viewport width, requiring no breakpoints at all. CSS clamp() is the mechanism that makes this work without JavaScript or complex responsive frameworks.

This guide covers the math behind fluid type, the CSS implementation patterns, and how to generate a complete type scale that scales smoothly from mobile to wide desktop viewports.

Photo by Karub ‎ on Pexels

How CSS clamp() Works

clamp() accepts three values: minimum, preferred, and maximum. The browser renders the preferred value when it fits between the min and max, uses the minimum when the preferred goes below it, and uses the maximum when the preferred goes above it.

font-size: clamp(1rem, 2.5vw, 1.5rem);

This sets font-size to:

At least 1rem (minimum)
At most 1.5rem (maximum)
Exactly 2.5vw when that value is between 1rem and 1.5rem

The challenge is selecting the preferred value expression - the 2.5vw part - to produce the right behavior across your target viewport range.

Calculating the Fluid Formula

The goal: a font size that is exactly min-size at your minimum viewport width and exactly max-size at your maximum viewport width, scaling linearly between them.

Formula derivation:

You want: font-size = m * vw + b

Where m is the slope and b is the y-intercept.

Given two points:

At viewport width v1: font-size = f1
At viewport width v2: font-size = f2

The slope in viewport units:

m = (f2 - f1) / (v2 - v1)

The y-intercept (in rem, where 1vw = viewport_width/100):

b = f1 - m * v1

Concrete example:

Target: 16px at 375px viewport, 20px at 1200px viewport.

Convert to rem (assuming 16px root font size):

f1 = 1rem at v1 = 375px
f2 = 1.25rem at v2 = 1200px

Calculate slope:

m = (1.25 - 1) / (1200 - 375) = 0.25 / 825 = 0.000303 rem/px

Convert to vw units (multiply by 100):

m_vw = 0.0303

Calculate intercept:

b = 1rem - 0.000303 * 375 = 1rem - 0.1136rem = 0.8864rem

Resulting clamp():

font-size: clamp(1rem, 0.8864rem + 3.03vw, 1.25rem);

Verify:

At 375px: 0.8864rem + 3.03 * (375/100) * (1rem/16px) - this simplifies to exactly 1rem. Correct.
At 1200px: simplifies to 1.25rem. Correct.

A Complete Type Scale Implementation

Here is a full type scale using this formula, targeting 375px as the minimum viewport and 1280px as the maximum:

:root {
  /*
    Type scale using clamp() for fluid sizing
    Min viewport: 375px | Max viewport: 1280px
    Ratio: Major Third (1.25) at max size
  */

  /* XS: 11px -> 12.8px */
  --text-xs: clamp(0.6875rem, 0.648rem + 0.208vw, 0.8rem);

  /* SM: 13px -> 14.4px */
  --text-sm: clamp(0.8125rem, 0.784rem + 0.152vw, 0.9rem);

  /* Base: 16px -> 18px */
  --text-base: clamp(1rem, 0.957rem + 0.228vw, 1.125rem);

  /* MD (formerly h4): 18px -> 22.5px */
  --text-md: clamp(1.125rem, 1.027rem + 0.521vw, 1.406rem);

  /* LG (h3): 22px -> 28px */
  --text-lg: clamp(1.375rem, 1.246rem + 0.685vw, 1.75rem);

  /* XL (h2): 26px -> 35px */
  --text-xl: clamp(1.625rem, 1.431rem + 1.032vw, 2.1875rem);

  /* 2XL (h1): 32px -> 44px */
  --text-2xl: clamp(2rem, 1.741rem + 1.378vw, 2.75rem);

  /* 3XL (hero): 40px -> 60px */
  --text-3xl: clamp(2.5rem, 2.068rem + 2.294vw, 3.75rem);
}

Applying the scale:

body {
  font-size: var(--text-base);
  line-height: 1.6;
}

h1 { font-size: var(--text-2xl); line-height: 1.15; }
h2 { font-size: var(--text-xl); line-height: 1.2; }
h3 { font-size: var(--text-lg); line-height: 1.25; }
h4 { font-size: var(--text-md); line-height: 1.3; }

.caption { font-size: var(--text-sm); }
.label { font-size: var(--text-xs); }

Generating clamp() Values Without Manual Calculation

The manual calculation above is useful for understanding the mechanism, but for production use, automated tools remove the error-prone arithmetic.

Utopia.fyi type calculator:
Utopia's type calculator accepts minimum and maximum viewport widths, a base font size range, and a scale ratio. It generates the complete CSS clamp() expressions for an entire type scale. The output can be copied directly into a CSS custom properties block.

CSS Clamp() generator:
The Fluid Typography tool on the Min-Max-Value blog accepts two viewport/size pairs and returns the clamp() expression - useful for one-off calculations when you know the exact minimum and maximum you want.

For large projects, generating the scale values programmatically as part of a design token pipeline is worth considering:

function fluidClamp(minSize, maxSize, minVw = 375, maxVw = 1280) {
  const minSizeRem = minSize / 16;
  const maxSizeRem = maxSize / 16;
  const minVwRem = minVw / 16;
  const maxVwRem = maxVw / 16;

  const slope = (maxSizeRem - minSizeRem) / (maxVwRem - minVwRem);
  const intercept = minSizeRem - slope * minVwRem;

  const slopeVw = (slope * 100).toFixed(4);
  const interceptRem = intercept.toFixed(4);

  return `clamp(${minSizeRem}rem, ${interceptRem}rem + ${slopeVw}vw, ${maxSizeRem}rem)`;
}

// Generate a complete scale
const scale = {
  xs: fluidClamp(11, 12.8),
  sm: fluidClamp(13, 14.4),
  base: fluidClamp(16, 18),
  md: fluidClamp(18, 22.5),
  lg: fluidClamp(22, 28),
  xl: fluidClamp(26, 35),
  '2xl': fluidClamp(32, 44),
  '3xl': fluidClamp(40, 60),
};

Testing Fluid Typography in the Browser

The primary testing method is resizing the browser window and watching text scale. But a more systematic approach uses DevTools:

Chrome/Firefox responsive mode: Set exact viewport widths with the device emulator (375px, 768px, 1024px, 1280px) and verify the font sizes at each breakpoint match your intended minimums and maximums.

Computed styles check: In DevTools Elements panel, select a text element and check the Computed styles. The resolved font-size shows the pixel value at the current viewport width, which you can compare against your expected output.

Quick verification snippet:

// Run in DevTools console at different viewport widths
const elements = document.querySelectorAll('h1, h2, h3, p');
elements.forEach(el => {
  const style = window.getComputedStyle(el);
  const tag = el.tagName;
  const size = parseFloat(style.fontSize).toFixed(1);
  console.log(`${tag}: ${size}px`);
});

Fluid Spacing to Match the Fluid Scale

Once you have fluid type, fluid spacing maintains proportional relationships between text and surrounding space as the viewport scales:

:root {
  /* Fluid spacing that scales with viewport */
  --space-xs: clamp(0.5rem, 0.4rem + 0.5vw, 0.75rem);
  --space-sm: clamp(0.75rem, 0.6rem + 0.75vw, 1.25rem);
  --space-md: clamp(1rem, 0.8rem + 1vw, 1.75rem);
  --space-lg: clamp(1.5rem, 1.2rem + 1.5vw, 2.5rem);
  --space-xl: clamp(2rem, 1.6rem + 2vw, 3.5rem);
}

h1 {
  font-size: var(--text-2xl);
  margin-bottom: var(--space-lg);
}

p + p {
  margin-top: var(--space-md);
}

The combined effect of fluid type and fluid spacing is a layout that feels proportionally consistent at any viewport width - not just at the specific breakpoints where your media queries fire.

The full typography system design approach - type scale ratios, font selection, spacing systems, and responsive behavior - is covered in the guide on web typography system design. The CSS implementation patterns here are the production version of the concepts covered there.

References

MDN CSS clamp() documentation is the authoritative reference for the function syntax and browser compatibility
Utopia.fyi - fluid type and space generator used by many production design systems
web.dev article on CSS clamp() for responsive typography covers clamping in the context of responsive design with worked examples
MDN CSS custom properties guide explains the full cascade behavior and fallback syntax for CSS variables - essential when combining fluid clamp() values with a multi-layer custom property architecture
The Goldilocks of font sizing - an in-depth guide on fluid typography at CSS-Tricks covers the history and evolution of fluid type approaches

For front-end development projects where design system implementation is part of the scope, 137Foundry web development services handle CSS architecture including fluid type systems, design token pipelines, and component-level typography implementation.

How to Set Up Jest for AI-Assisted Unit Test Generation in JavaScript

137Foundry — Sun, 10 May 2026 15:07:53 +0000

AI coding assistants generate better unit tests when the testing environment is properly configured. A project with Jest set up correctly, with examples of the testing style you want, and with TypeScript types available produces significantly higher-quality AI-generated tests than a project where the AI is inferring everything from scratch.

This guide walks through configuring Jest for an AI-assisted workflow: the setup steps, the configuration choices that affect generated test quality, and the prompt patterns that work well once the environment is in place.

Photo by Daniil Komov on Pexels

Step 1: Install Jest and Configure It

For a new project, install Jest and the necessary tooling:

npm install --save-dev jest @types/jest
# For TypeScript projects:
npm install --save-dev ts-jest @babel/preset-typescript

For TypeScript projects, configure ts-jest in jest.config.ts:

import type { Config } from 'jest';

const config: Config = {
  preset: 'ts-jest',
  testEnvironment: 'node',
  testMatch: ['**/__tests__/**/*.test.ts', '**/*.spec.ts'],
  collectCoverageFrom: [
    'src/**/*.ts',
    '!src/**/*.d.ts',
    '!src/**/index.ts',
  ],
  coverageThreshold: {
    global: {
      branches: 70,
      functions: 80,
      lines: 80,
      statements: 80,
    },
  },
};

export default config;

The coverage thresholds matter for AI-assisted workflows: if you're using generated tests to hit coverage targets, setting thresholds prevents generated tests from just covering happy paths while leaving branch coverage low.

For JavaScript without TypeScript, use Babel:

npm install --save-dev babel-jest @babel/core @babel/preset-env

And a minimal babel.config.js:

module.exports = {
  presets: [
    ['@babel/preset-env', { targets: { node: 'current' } }],
  ],
};

Step 2: Write Two or Three Example Tests First

Before using AI generation, write two or three tests manually for a simple function. These serve as style examples for AI prompts.

Your example tests should establish:

Naming convention (describe block naming, test description patterns)
Assertion style (which Jest matchers you prefer)
Mock setup pattern (how you structure jest.mock() calls and beforeEach setup)
Error testing pattern (expect(() => fn()).toThrow(ErrorType))

Example:

import { calculateDiscount } from '../discount';

describe('calculateDiscount', () => {
  describe('valid inputs', () => {
    it('applies percentage discount correctly', () => {
      expect(calculateDiscount(100, 0.1)).toBe(90);
    });

    it('returns full price when discount is zero', () => {
      expect(calculateDiscount(100, 0)).toBe(100);
    });
  });

  describe('edge cases', () => {
    it('throws RangeError when discount exceeds 1', () => {
      expect(() => calculateDiscount(100, 1.5)).toThrow(RangeError);
    });

    it('returns zero when price is zero', () => {
      expect(calculateDiscount(0, 0.1)).toBe(0);
    });
  });
});

This three-minute investment in manual examples produces substantially better AI-generated tests because the AI matches your conventions rather than generating its own.

Step 3: Configure Coverage Reporting

Add coverage scripts to package.json:

{
  "scripts": {
    "test": "jest",
    "test:watch": "jest --watch",
    "test:coverage": "jest --coverage",
    "test:coverage:report": "jest --coverage --coverageReporters=html"
  }
}

Run npm run test:coverage after adding AI-generated tests to verify branch coverage, not just statement coverage. AI generation tends to cluster on statement coverage (executed lines) while missing branch coverage (both sides of conditionals). The coverage report will show exactly which branches aren't tested.

Step 4: Structure Your AI Prompts

With Jest configured and examples in hand, the effective prompt pattern includes four parts:

The function you want tested (paste the full implementation)
A reference to your example test file ("match the style in the example below")
An explicit list of test categories ("include: happy path, null inputs, empty array inputs, and TypeError for invalid types")
The function's dependencies (if it imports other modules)

An example prompt:

"Write Jest unit tests for the processUserData function below. Match the test style from the example file. Cover: successful processing of a valid user object, missing required fields (should throw ValidationError), empty string in required field, and null user input. The function imports validateUser from ./validators - mock that module."

Explicit test category requests are more reliable than open-ended prompts. "Write comprehensive tests" produces a different test suite than "Write happy path, null input, missing field, and error propagation tests."

Step 5: Review Generated Tests Systematically

For each AI-generated test file, apply this review checklist:

[ ] Does each test name describe the behavior being tested (not just "returns value")?
[ ] Does the test actually fail if you break the function it's testing? (Mutation check)
[ ] Are mocks replacing external dependencies at the right boundary?
[ ] Are error assertions specific enough (type + message where applicable)?
[ ] Do async tests use proper await syntax?
[ ] Are there tests for both sides of every conditional?

The mutation check is the most important step. Change one line of the function (flip a comparison, remove a null check, change a return value) and run the test. If the test still passes, it's not verifying the behavior you changed.

Common Jest + AI Configuration Issues

Missing moduleNameMapper for path aliases. If your project uses TypeScript path aliases (@/components/), add moduleNameMapper to jest.config.ts so AI-generated imports resolve correctly.

Wrong testEnvironment for browser APIs. Functions using DOM APIs need testEnvironment: 'jsdom'. AI-generated tests for browser code may fail in the default node environment.

Inadequate mock cleanup. AI-generated tests sometimes miss jest.clearAllMocks() in afterEach, causing test pollution between test cases. Add it to your global setup.

For the comprehensive workflow on AI test generation beyond Jest configuration, see how to generate unit tests with AI coding assistants. The 137Foundry AI automation services team works with AI-assisted development workflows including test generation as part of broader code quality engagements.

Jest documentation and TypeScript documentation are the reference sources for configuration options - useful when AI-generated configuration has edge cases that need adjustment for your specific project.

Automating Test Quality Checks in CI

Once Jest is configured and AI-generated tests are committed, enforce quality through CI rather than relying on individual review.

A minimal GitHub Actions workflow that runs tests and coverage:

name: Test
on: [push, pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: '20'
      - run: npm ci
      - run: npm run test:coverage

If branch coverage falls below the threshold configured in jest.config.ts, the CI step fails and the PR is blocked. This creates a hard gate that catches when AI-generated tests are accepted without adequate coverage review.

For mutation testing in CI, Stryker Mutator can be run as a scheduled job (weekly or before release cuts) rather than on every PR, since mutation testing is slower than regular test runs. Its reports identify which tests are passing but not catching mutations - the most actionable quality signal for AI-generated test suites.

The Vitest alternative to Jest offers faster test execution on Vite-based projects and supports the same coverage configuration. If you're setting up a new project rather than configuring an existing one, Vitest is worth evaluating alongside Jest. The prompting workflow described in this guide applies to both; the configuration syntax differs in minor ways documented in Vitest's official docs.

OpenAI and other AI providers continue to improve test generation quality with each model update - the setup described here is forward-compatible because it's based on providing explicit context rather than relying on model inference. Better models with the same prompt structure produce better tests without requiring workflow changes.

Free AI Coding Tools That Generate Unit Tests (And How Well They Work)

137Foundry — Sun, 10 May 2026 15:07:51 +0000

The difference between AI tools for test generation isn't just about code quality - it's about how the tool integrates into your actual workflow. A tool that generates excellent tests but requires copy-pasting between interfaces has a higher friction cost than a tool that generates acceptable tests directly in your editor. This roundup covers the free-tier options worth evaluating, what each is genuinely good at, and where each falls short for test generation specifically.

Photo by Katerina Holmes on Pexels

GitHub Copilot (Free Tier)

GitHub Copilot is the most widely used AI coding assistant and has direct IDE integration. The free tier offers a limited number of completions per month.

What it does well for tests: Inline completion makes test generation feel like typing. Write it('should return, and Copilot suggests test code based on the function visible in the file. For functions that are already in the same file or recently opened files, context is good. For functions in other modules, you need to open those files before generating tests.

Limitations: Copilot's inline completion format is not ideal for generating a complete test suite in one operation. It tends to generate one test at a time, following the pattern of whatever test is above the cursor. Getting a comprehensive test suite requires either writing many individual completions or using the chat interface.

Best for: Developers who want inline test completion during development, adding tests incrementally alongside the code they're writing.

GitHub Copilot Chat (Free with Copilot)

The chat interface in GitHub Copilot allows more structured prompts: you can paste a function and ask for a complete test suite rather than waiting for inline completions.

What it does well: Responding to explicit prompts with structured test cases. Explaining why it generated a particular test. Revising tests when you describe what's wrong.

Limitations: The context window in chat doesn't automatically include your codebase. You need to paste the function, any dependencies, and example tests explicitly. Results are better than inline completion for complete suites but require more setup.

Cursor (Free Tier)

Cursor is an AI-native editor built around an LLM with access to your full codebase context.

What it does well for tests: The codebase indexing means Cursor can see your existing test patterns without you pasting examples. Ask "write tests for this function that match the style of my existing tests" and it finds the examples itself. This significantly reduces prompt overhead.

Limitations: The free tier has a limited number of AI requests per month. Test generation is request-intensive since review rounds ("add a null input test," "fix the mock setup") each consume a request.

Best for: Projects where consistency with existing test style matters and manual prompt setup is a friction bottleneck.

Claude.ai (Free Tier with claude.ai)

Anthropic's Claude is accessible via the web at no cost (with daily message limits) and has a large context window well-suited to pasting full modules.

What it does well: Handling large context (paste an entire module with tests), generating tests with detailed explanations, and iterative revision. Claude tends to produce more explanation with its test output - useful when reviewing why a test case was included.

Limitations: Not IDE-integrated on the free tier. You paste code into the web interface and copy results back. The friction cost is real for iterative workflows.

Best for: One-time test generation for a module where you want to understand the reasoning behind each test case, or for establishing test patterns for a new module type.

Amazon Q Developer (Free Tier)

Amazon Q Developer (formerly CodeWhisperer) is free for individual use with IDE plugins for VS Code, IntelliJ, and others.

What it does well: Java and Python support is strong. AWS service integration is predictably good. Test generation inline is functional and the free tier is more generous than Copilot's.

Limitations: JavaScript/TypeScript test generation is less reliable than its Python and Java support. The suggestions can be less idiomatic for front-end frameworks.

Best for: Java or Python codebases, especially those with AWS integration.

ChatGPT (Free Tier with GPT-3.5/4o)

OpenAI provides free access to GPT-4o via the ChatGPT web interface.

What it does well: Responding to well-structured prompts with test suites that follow explicit requirements. Good at generating multiple variant tests and explaining each.

Limitations: No IDE integration on the free tier. Same paste-and-copy friction as Claude.ai. GPT-4o's code quality is slightly lower than Claude or GPT-4 Turbo for complex codebases, though adequate for most test generation tasks.

Best for: Developers who already use ChatGPT for other tasks and want to add test generation to their workflow without adopting a new tool.

Evaluating Quality: The Mutation Test

Regardless of which tool you use, apply the mutation test to any AI-generated test suite before committing it. Introduce one intentional bug into the function being tested (flip a comparison, remove a null check) and run the tests. If they pass, the tests are not verifying what you think they're verifying.

This single check catches more false-confidence tests than any other review method. Tools that generate structurally correct tests that fail to catch real bugs are worse than fewer, better tests because they create false confidence about test coverage.

The Workflow That Works

Choose the tool that fits your editor and language
Provide explicit context: function + dependencies + style examples + list of test cases
Generate and run the tests immediately
Run the mutation check on the most critical function
Review AI-generated mock setup specifically - this is the highest-failure area across all tools

For the full workflow on prompting, reviewing, and integrating AI-generated tests, see how to generate unit tests with AI coding assistants. The 137Foundry AI and web development services team evaluates and integrates AI coding tools as part of development workflow optimization - the specific tool choice is less important than the review process applied to whatever it generates.

Jest documentation and Pytest documentation are the reference points for testing framework specifics, since each tool above generates framework-appropriate syntax when you specify the target framework in your prompt.

Which Tool to Start With

If you're new to AI-assisted test generation, start with whichever tool is already in your editor. The friction of switching between interfaces (copy-pasting to a web chat) adds up quickly across a full day of test generation work, and the quality difference between tools is smaller than the friction difference.

If you have no existing preference: Cursor on a TypeScript project and ChatGPT for quick one-off generation sessions represent a reasonable baseline. Add mutation testing with Stryker Mutator to verify that whatever any tool generates is actually catching real bugs. The tool generates the structure; the mutation check validates it. Neither step is optional if you want a test suite you can rely on.

Vitest is worth mentioning for modern JavaScript projects - it's the test runner recommended for Vite-based projects and works with the same prompt patterns as Jest. All the tools above generate Vitest-compatible output when you specify the framework. The GitHub repository ecosystem is also a useful source of real-world test examples in your language and framework, which you can paste as style references in any of the tools above.

How to Prepare a Legacy Codebase for AI-Assisted Refactoring

137Foundry — Sat, 09 May 2026 11:09:29 +0000

Jumping into a legacy codebase with an AI coding assistant and no preparation produces predictably mixed results. The AI generates plausible-looking refactors that miss critical business logic embedded in unexpected places. You spend more time verifying output than the AI saved you in generation time. And the refactored code, while cleaner-looking, may have subtle behavioral changes that surface in production six weeks later.

The difference between this outcome and a productive AI-assisted modernization session is preparation. Specifically: giving the AI the context it needs to reason correctly about your specific codebase rather than reasoning from generic patterns.

This guide covers the preparation steps that make AI-assisted legacy refactoring significantly safer and more productive.

Step 1: Establish Scope and Document It

Before any AI interaction, define the boundary of what you are working on. Legacy codebases have a way of expanding scope because everything touches everything. Resist this.

Choose a specific module, class, or set of related functions as your working scope. Write a plain-language description of what that scope is responsible for:

Scope: the discount calculation module (discount.py, approximately 400 lines)
This module is responsible for: calculating the final price a customer pays
after applying applicable discounts, promotions, and loyalty tier benefits.

It is NOT responsible for: fetching customer tier data (done by customer_service.py),
validating promo codes (done by promo_validator.py), or applying tax (done post-discount
by tax_calculator.py).

The most important business constraint: discounts do not stack additively.
A customer with a 20% loyalty discount and a 15% promo code gets 20% off, 
not 35% off. This is intentional and must be preserved in any refactoring.

This description becomes the context header you paste before every AI prompt related to this module. It costs you twenty minutes to write; it saves you from explaining the same context to the AI repeatedly and catching errors that stem from the AI not knowing the "discounts don't stack" rule.

Step 2: Audit Dependencies Before Touching Anything

AI coding assistants will generate refactored code that changes function signatures, return types, or module interfaces without knowing what depends on them. Before you start refactoring, you need a dependency map.

For Python codebases, tools like Python's built-in ast module and import analysis scripts can generate call graphs. For JavaScript, ESLint and module analysis tools serve a similar purpose. GitHub advanced search can help you find all internal references to a specific function across a large repository.

The AI can help with this phase, but its output should be treated as a starting point:

Identify all the places this function is called in the following files.
For each call site, note:
1. The file and line number
2. How the return value is used (stored, compared, iterated over, etc.)
3. Whether the caller passes keyword arguments or positional arguments

[target function] [relevant surrounding files]

Review the AI's output carefully. Dynamic call patterns (calling functions stored in dictionaries, factory patterns, monkey-patching) will not appear in AI dependency analysis. These need manual identification.

The dependency map serves a critical purpose: before you change a function signature or return type, you know what you need to update. Without it, you are refactoring blind.

Step 3: Create a Test Baseline

Legacy code with no tests is the most dangerous to refactor because you have no automated way to verify that behavior is preserved. Before any refactoring, use AI to generate an initial test suite for the module you are working on.

This is one of the highest-value uses of AI assistance in legacy modernization. Even imperfect AI-generated tests are faster to produce than writing them from scratch, and they provide a safety net that makes subsequent refactoring significantly lower-risk.

Important: AI-generated tests tend to cover the happy path and obvious error cases well, and miss edge cases that emerged from production incidents. After getting the AI-generated test suite, review your issue tracker, Git blame history, and incident reports for the module. Add tests for any bugs that were fixed in the module's history - those are the edge cases most likely to be reintroduced by refactoring.

Once your test baseline is in place, configure your CI pipeline to run these tests on every commit. This gives you immediate feedback when a refactoring breaks behavior.

Step 4: Identify and Document the Critical Paths

Not all code in a legacy system is equally risky to modify. The critical paths are the execution flows that:

Handle money or anything irreversible (payments, emails sent, database deletes)
Run under high load or in performance-sensitive paths
Have known security relevance (authentication, authorization, input validation)
Have produced incidents or bugs in the past

These are the paths where AI-generated refactors need the most careful human review. Document them explicitly before starting:

Critical paths in discount.py:
1. Lines 145-190: Final discount application to cart total - this writes to the order record
2. Lines 210-230: Promo code validation bypass for internal employee accounts - security-relevant
3. Lines 280-310: Bulk discount calculation - runs for every item in large orders, performance-sensitive

When AI-generated refactors touch lines in this list, they get extra review. When they do not, you can move faster. This simple classification reduces the time you spend being careful about everything and focuses attention where it matters.

Photo by Bernice Chan on Pexels

Step 5: Set Up a Safe Experimentation Environment

Before merging any AI-assisted refactoring, you need a way to run the original and refactored code side-by-side and compare behavior. The ideal setup:

A feature branch where AI-assisted changes are isolated
Your test baseline running against both the original and the refactored code
If the module has external side effects (database writes, external API calls), a way to stub those out for comparison testing

Martin Fowler's branch-by-abstraction pattern is useful for large-scale refactoring: introduce a seam that lets you run old and new implementations in parallel and compare results before fully switching.

For simpler modules, a straightforward A/B test in a staging environment - routing a portion of traffic to the refactored implementation - gives you confidence before full deployment.

Putting It Together

The preparation sequence - scope definition, dependency audit, test baseline, critical path identification, safe environment setup - takes time. On a module of moderate complexity, expect to spend a day on preparation before writing a line of refactored code.

That investment pays back quickly. With context documents, a test baseline, and a dependency map in hand, each AI-assisted refactoring session produces output that is faster to review, safer to merge, and less likely to produce production incidents.

For the full framework on running these sessions - including prompting patterns for the refactoring phase itself - the guide on using AI coding assistants for legacy code modernization covers the end-to-end process.

137Foundry works with engineering teams on legacy modernization assessments and implementation. The 137Foundry AI automation services include preparation consulting for teams starting this process for the first time.

Prettier and ESLint are useful tools for establishing consistent code style as a baseline before starting structural refactoring - style differences in a diff make behavioral changes harder to spot. OWASP provides useful checklists for security-critical code review that apply directly to the critical path review step.

Legacy modernization done well is not fast. But with the right preparation, AI assistance makes it substantially less expensive than it used to be.

How to Build a Dependency Map of a Legacy Codebase Using AI Tools

137Foundry — Sat, 09 May 2026 11:09:28 +0000

Before you refactor anything in a legacy codebase, you need to know what depends on what. Change a function signature without knowing its callers, and you break things in unexpected places. Rename a class without understanding its inheritance tree, and you introduce failures that are hard to trace. The dependency map is the safety information that makes everything else in a legacy modernization project lower-risk.

Building a complete dependency map manually is expensive. AI tools accelerate the process significantly - with important caveats about where they fail that you need to know upfront.

What a Dependency Map Needs to Capture

A useful dependency map for legacy modernization is not just an import graph. It needs to capture:

Direct imports and module dependencies: what each file imports from where
Function call relationships: which functions call which other functions
Data flow: what data structures are created in one module and consumed in another
External dependencies: third-party libraries and their versions
Configuration dependencies: environment variables, config files, and feature flags the code depends on
Database schema dependencies: tables and columns the code reads from and writes to

The import graph is the easiest layer to generate automatically. The others require more work, and the deeper you go, the more valuable the map becomes.

Step 1: Generate the Static Import Graph

Start with what static analysis can tell you. For Python, the built-in ast module lets you walk any Python file and extract all import statements:

import ast
import os
from pathlib import Path

def extract_imports(filepath):
    """Extract all imports from a Python file."""
    with open(filepath) as f:
        tree = ast.parse(f.read())

    imports = []
    for node in ast.walk(tree):
        if isinstance(node, ast.Import):
            imports.extend(alias.name for alias in node.names)
        elif isinstance(node, ast.ImportFrom):
            module = node.module or ''
            imports.append(module)
    return imports

def build_import_graph(root_dir):
    """Build a dependency graph for all Python files in a directory."""
    graph = {}
    root = Path(root_dir)
    for py_file in root.rglob('*.py'):
        relative = str(py_file.relative_to(root))
        graph[relative] = extract_imports(py_file)
    return graph

This gives you a starting point: a dictionary of every Python file and its direct imports. For JavaScript and TypeScript codebases, ESLint plugins and tools like madge (available on GitHub) provide similar static import analysis.

Step 2: Use AI to Enrich the Graph with Call Relationships

Static import analysis tells you which modules depend on which other modules. It does not tell you which specific functions are called across that dependency. This is where AI assistance becomes valuable.

For each module that has dependencies you care about, prompt the AI to identify the call relationships:

Analyze the following two files. I need to understand the call relationship between them.

For every function in module A that calls a function in module B:
1. Name the calling function in module A
2. Name the function being called in module B  
3. Note what arguments are passed
4. Note how the return value is used

Be explicit about any indirect calls (through variables, dictionaries, or dynamic dispatch).

[module A code]
[module B code]

This produces a function-level call graph that is significantly more useful than the module-level import graph for planning refactoring safely.

Important limitation: AI analysis misses dynamic call patterns. If your legacy code stores function references in dictionaries and calls them by key, or if it uses Python's getattr() to call methods by name strings, these dynamic calls will not appear in AI-generated call graphs. You need manual inspection to catch them.

Step 3: Document External Dependencies and Their Versions

Legacy codebases often have complex dependency situations: pinned versions of libraries that are years out of date, circular dependencies between internal packages, and third-party libraries that are no longer maintained.

For Python projects, your requirements.txt or Pipfile.lock is the starting point. For JavaScript, package-lock.json or yarn.lock. Extract the full dependency tree including transitive dependencies:

# For Python: pip can generate a dependency tree
# pip install pipdeptree
# pipdeptree --json > dependencies.json

import json
import subprocess

result = subprocess.run(
    ['pipdeptree', '--json'],
    capture_output=True,
    text=True
)
deps = json.loads(result.stdout)

Feed this dependency list to the AI and ask it to identify:

Which dependencies are significantly out of date
Which dependencies have known security vulnerabilities (cross-reference with OWASP)
Which dependencies appear to have functional overlap (where you might be able to consolidate)
Which dependencies are no longer actively maintained

This produces a prioritized list of dependency modernization work that is separate from but informed by your code-level refactoring plan.

Step 4: Map Database Schema Dependencies

For legacy systems with significant database logic, the schema dependency is often the most complex layer. Business logic about what tables and columns mean gets embedded in code, and changing either requires understanding both.

The AI can help by analyzing SQL queries embedded in your codebase:

Analyze the following Python module. Extract every SQL query (including those
built dynamically through string concatenation). For each query:
1. Identify the tables accessed
2. Identify the specific columns read or written
3. Note whether it is a read or a write operation
4. Note any JOIN relationships

[module with SQL here]

This produces a table-and-column-level dependency map for each module. Modules that share table dependencies need to be refactored in coordination - changing a table schema without updating all the code that touches it is a common source of legacy modernization failures.

Step 5: Assemble and Visualize

With the import graph, call graph, external dependencies, and schema dependencies documented, you have a map that is genuinely useful for planning. The next step is making it navigable.

For complex codebases, a structured Markdown document organized by module works well as a starting point - it is human-readable and can be committed to version control alongside the code. For very large codebases, Git-tracked JSON or YAML dependency files, potentially visualized with a tool like Mermaid (available through GitHub), make the relationships searchable and interactive.

The dependency map is a living document. As you refactor modules, update the map to reflect the new structure. Over time it becomes the accurate documentation of what the codebase actually does, not just what it used to do.

Photo by cottonbro studio on Pexels

What to Do With the Map

The dependency map is most valuable for two decisions:

Refactoring sequence. Modules with few incoming dependencies (few things depend on them) are the safest to refactor first. Modules with many incoming dependencies need the most careful planning and testing before they change. The map tells you which is which.

Blast radius estimation. When you make a change, the dependency map tells you the maximum set of things that could be affected. Combined with your test suite, this lets you know whether you have adequate coverage before you touch something.

The full workflow for using this map during AI-assisted refactoring - including the prompting patterns that work best with this level of context - is covered in the guide on using AI coding assistants for legacy code modernization.

137Foundry provides legacy modernization services that include dependency mapping as a foundational assessment phase. Prettier and ESLint are useful companion tools for enforcing code style consistency as the refactoring proceeds. Node.js and Python.org official documentation are authoritative references for understanding the import and module systems of those runtimes.

A dependency map built before refactoring begins is an investment that pays back in avoided production incidents and faster, more confident changes throughout the modernization project.

Idempotency in Data Pipelines: How to Prevent Duplicate Records

137Foundry — Fri, 08 May 2026 11:12:59 +0000

A pipeline that runs twice should produce the same result as one that runs once. That property is idempotency, and its absence is one of the most common sources of silent data corruption in integration systems. A partially completed run gets retried, the retry reprocesses records that already loaded, and the destination ends up with duplicates that neither the source system nor any monitoring alert ever surfaced.

Designing for idempotency is not complex, but it requires making explicit decisions about state management that are easy to skip when building the initial pipeline.

What Idempotency Means in Data Integration

An idempotent operation produces the same effect when applied once or multiple times. In data integration terms, this means:

Inserting the same record twice produces one record, not two
Running a pipeline over the same time window twice produces the same output as running it once
Retrying a failed partial run does not create duplicates for the records that already loaded

The opposite is a non-idempotent pipeline: every execution adds records, so duplicate runs produce duplicate data. Most pipelines start as non-idempotent because insert operations are simpler to implement than upsert operations, and the duplicate problem only becomes visible after a retry event occurs.

Common Sources of Duplicate Records

Pipeline retries after partial success. A run processes 8,000 of 10,000 records successfully, then fails. The retry starts from the beginning and reprocesses the 8,000 records that already loaded. Without idempotency, these 8,000 records now exist twice.

Parallel execution without coordination. Two instances of the same pipeline run simultaneously, both extracting from the same source window and loading to the same destination. This happens more often than expected with cloud schedulers that retry hung jobs while the original is still running.

Checkpoint failures. A pipeline tracks its progress with checkpoints (offsets, cursors, timestamps). If the checkpoint is written after loading but before the success acknowledgment, a crash between the load and the checkpoint write causes the load to be repeated on the next run.

Upsert as the Foundation of Idempotency

The most reliable approach to idempotency at the storage layer is the upsert operation: insert the record if it does not exist, update it if it does. In SQL terms, this is typically INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) or MERGE (SQL Server, Oracle).

For PostgreSQL, the pattern looks like:

INSERT INTO events (event_id, payload, processed_at)
VALUES ($1, $2, $3)
ON CONFLICT (event_id) DO UPDATE
SET payload = EXCLUDED.payload,
    processed_at = EXCLUDED.processed_at;

The event_id column is the natural key that identifies whether a record already exists. For this to work reliably, every record must have a stable unique identifier that is consistent across extraction runs. If the source does not provide one, you must generate one deterministically from the record's content.

Deterministic ID Generation

When source records do not include a stable unique identifier, you can generate one by hashing the record's identifying fields:

import hashlib
import json

def generate_record_id(record, key_fields):
    """Generate a stable ID from record content."""
    key_data = {field: record[field] for field in key_fields}
    canonical = json.dumps(key_data, sort_keys=True)
    return hashlib.sha256(canonical.encode()).hexdigest()

This approach produces the same ID for the same input data, allowing upserts to detect duplicates even when the source does not provide a unique key. The fields used for hashing must be stable (not timestamps or auto-incremented values) and must uniquely identify the record within the source system.

Time Window Idempotency

For pipelines that extract data by time window, idempotency requires that reprocessing the same window produces the same result. Two approaches work:

Truncate and reload. Before loading data for a time window, delete all existing records for that window and reload from scratch. This is simple and reliable but requires the destination to support deletions and may not work if other processes are writing to the same table concurrently.

Upsert with timestamp tracking. Keep the upsert approach but track which time windows have been fully processed. On retry, skip windows that are marked complete and reprocess only windows that failed mid-run. The Kafka documentation covers offset management patterns that implement this for stream-based pipelines.

Monitoring for Duplicate Records

Even with idempotency in place, monitoring for duplicates provides a safety net:

Count distinct records at source and at destination for the same time window. A destination count higher than the source count (allowing for fan-out) indicates duplicates.
Check cardinality of the natural key at the destination. Any key value with count greater than one is a duplicate.
Alert when the destination record count for a time window increases between run N and run N+1 without a corresponding increase in the source count.

These checks can be run as part of the reconciliation job described in the guide on monitoring data integration pipelines in production.

HTTP Idempotency for API-Based Pipelines

For pipelines that write to destination systems via API, HTTP idempotency keys are the equivalent mechanism. Many modern APIs accept an idempotency key header that causes the server to de-duplicate requests with the same key. The HTTP RFC 7231 defines idempotency at the HTTP method level, and many API providers extend this with explicit idempotency keys.

Submit the same idempotency key with the same payload, and the API returns the previous result without re-processing. This protects against retries caused by network timeouts where the original request succeeded but the response was lost.

Photo by Bùi Hoàng Long on Pexels

Testing Idempotency in Pipeline Code

Idempotency is a property that is difficult to verify by inspection. The only way to confirm a pipeline is truly idempotent is to run it twice against the same input and compare the outputs.

The test structure is straightforward:

Run the pipeline once against a known test dataset.
Capture the destination state: record count, contents of key records, and any generated IDs.
Run the pipeline again against the same dataset without clearing the destination.
Assert that the destination state is identical to what was captured in step 2.

For upsert-based pipelines, the destination record count must not increase on the second run, and the content of each record must match the first run's output.

def test_pipeline_is_idempotent(test_dataset, pipeline, destination):
    # First run
    pipeline.run(test_dataset)
    state_after_first_run = destination.snapshot()
    count_first = len(state_after_first_run)

    # Second run with same input, destination not cleared
    pipeline.run(test_dataset)
    state_after_second_run = destination.snapshot()
    count_second = len(state_after_second_run)

    assert count_first == count_second, (
        f"Duplicate records created: {count_second - count_first}"
    )
    assert state_after_first_run == state_after_second_run

For time-window-based pipelines, the test should cover reprocessing an overlapping window: run for window [T1, T2], then run again for [T0, T2] where T0 is before T1. Records in the T1-T2 overlap should not be duplicated after the second run.

Testing idempotency during development is significantly cheaper than discovering and remediating duplicate data in production. A deduplication job on a production table with tens of millions of records is a multi-hour operation that disrupts normal pipeline runs and may still leave edge cases unresolved if the duplicate detection logic is not precise.

Idempotency as a Design Constraint, Not a Fix

The most important thing about idempotency is that it needs to be designed in from the start. Adding idempotency to an existing non-idempotent pipeline that has been running in production requires auditing the destination for existing duplicates, migrating the storage layer to support upserts, and potentially deduplicating historical data. That is a significant effort compared to building with upserts from day one.

137Foundry builds data integration pipelines with reliability properties including idempotency, dead letter queues, and schema change detection built in. The data integration service and the broader services hub describe the full scope of what we work on.

How to Build a Dead Letter Queue System for Reliable Data Processing

137Foundry — Fri, 08 May 2026 11:12:57 +0000

Every data processing system will eventually receive a record it cannot handle. A missing required field, an unexpected data type, a payload that exceeds size limits, a downstream service that rejects the record with a non-transient error. What happens to that record determines whether your system is reliable or merely operational.

A dead letter queue (DLQ) is the mechanism that handles unprocessable records without stopping the rest of the pipeline. Instead of retrying indefinitely or dropping the record silently, the system writes the failed record to the DLQ and continues processing the next one.

This guide covers what a DLQ needs to contain, how to implement one, when to use it, and how to monitor it.

What Goes in a Dead Letter Queue

A DLQ entry needs enough information to answer three questions later:

What was the original record?
Why did it fail?
At which stage did it fail?

At minimum, each DLQ entry should contain:

The full original record payload (not just an ID reference)
The error message or exception that caused the failure
The stage name where the failure occurred
A timestamp
A correlation ID (run ID, batch ID, or similar)
A failure count (to distinguish first-time failures from repeated failures)

The original payload must be stored in full. A reference to a source record is not sufficient because the source may change or the record may be deleted by the time you investigate. The DLQ is a point-in-time snapshot of the failed state.

Designing the DLQ Storage

DLQ storage depends on the pipeline architecture.

For message-queue-based pipelines: RabbitMQ has native DLQ support through dead letter exchanges. Messages that exceed their retry count or their time-to-live are automatically routed to a designated DLQ exchange. Apache Kafka does not have native DLQ semantics, but the standard pattern is to write failed records to a dedicated topic (<topic>-dlq by convention) and include the failure metadata in the record headers.

For database-backed pipelines: A dedicated table with columns for the original record JSON, error message, stage, timestamp, and failure count works well. Add an index on stage and timestamp to support common query patterns.

For cloud-managed queues: Amazon SQS has a built-in DLQ mechanism where a source queue is configured with a redrive policy that specifies a maximum receive count and a DLQ target. Google Cloud Pub/Sub provides a similar dead letter policy.

Implementation: The Retry-Then-DLQ Pattern

The standard pattern is to attempt processing a fixed number of times before routing to the DLQ:

MAX_RETRIES = 3

def process_record(record, retry_count=0):
    try:
        result = transform_and_load(record)
        return result
    except TransientError as e:
        if retry_count < MAX_RETRIES:
            time.sleep(backoff_delay(retry_count))
            return process_record(record, retry_count + 1)
        else:
            write_to_dlq(record, str(e), stage="transform_and_load", retry_count=retry_count)
    except PermanentError as e:
        # Non-transient errors go directly to DLQ without retry
        write_to_dlq(record, str(e), stage="transform_and_load", retry_count=0)

Distinguishing transient errors (network timeouts, rate limits, temporary service unavailability) from permanent errors (schema validation failures, type errors, records that fail business logic) is important. Retrying permanent errors wastes time and fills the DLQ with duplicate entries.

def write_to_dlq(record, error_message, stage, retry_count):
    dlq_entry = {
        "payload": record,
        "error": error_message,
        "stage": stage,
        "timestamp": datetime.utcnow().isoformat(),
        "retry_count": retry_count,
        "run_id": current_run_id()
    }
    dlq_store.write(dlq_entry)

Monitoring the DLQ

A DLQ that no one watches provides less value than no DLQ at all. Key metrics to track:

DLQ entry count per run: A non-zero count indicates records failed processing. The absolute count tells you the scope of the issue. Zero for many runs followed by a spike indicates a schema change or upstream data quality regression.

DLQ growth rate: DLQ entries accumulating faster than they are being resolved indicate a systematic issue. A DLQ that grows by 500 records per run without any remediation action will overwhelm your ability to investigate individually.

Top error messages: Group DLQ entries by error message. If 90% of failures share the same error, one fix resolves 90% of the backlog. If failures are evenly distributed across dozens of error types, the issue is systemic.

For broader pipeline observability beyond DLQ monitoring, the guide on monitoring data integration pipelines in production covers record counts, alerting, and end-to-end reconciliation.

The DLQ Replay Workflow

A DLQ only provides value if you can act on its contents. The replay workflow is:

Investigate DLQ entries to identify the failure pattern.
Fix the underlying issue (schema validation rule, transformation logic, downstream service configuration).
Re-queue DLQ entries back to the main processing queue.
Confirm records process successfully on the second pass.

The replay step should be treated as a controlled operation, not a bulk re-queue. Before replaying the full DLQ, replay a sample of entries and confirm they process correctly.

Photo by Keysi Estrada on Pexels

DLQ Policies: When to Expire Records

Not every failed record should be kept indefinitely. A DLQ retention policy prevents unbounded growth:

For records that fail due to upstream data quality: retain until the source issue is resolved and a remediation run is possible.
For records that fail due to pipeline logic errors: retain until the fix is deployed and replayed.
For records past a maximum age (typically 30-90 days): log a summary and archive or discard. Replaying records that are too old often produces incorrect results because the system state has changed.

The policy should be documented so the team knows what guarantees the DLQ provides.

Distinguishing Transient From Permanent Errors

The effectiveness of a DLQ depends heavily on correctly classifying errors as transient (retry-able) or permanent (send immediately to DLQ). Misclassifying a permanent error as transient wastes retries and delays DLQ capture. Misclassifying a transient error as permanent loses records that could have been recovered.

Transient errors are conditions expected to resolve on their own: network timeouts, rate limit responses (HTTP 429), temporary service unavailability (HTTP 503), connection resets. These should be retried with exponential backoff.

Permanent errors are conditions that will not resolve without a code or data change: schema validation failures, type conversion errors, business logic violations, records that exceed destination size limits, duplicate key violations on non-idempotent loads. These should go directly to the DLQ without retry.

Some errors are ambiguous. An HTTP 500 from a downstream API could indicate a transient server error or a bug triggered by the specific record. For ambiguous errors, retry once or twice with a short backoff. If the error persists, treat it as permanent and route to DLQ.

Alerting on DLQ Patterns

A DLQ that accumulates records without triggering alerts is almost as bad as no DLQ. Alert thresholds for DLQ monitoring:

Absolute threshold: More than N new DLQ entries in the last pipeline run. The value of N depends on your expected error rate baseline. For a pipeline that historically has zero DLQ entries, any DLQ entry should trigger an investigation.
Growth rate threshold: DLQ depth increasing by more than X% per day. A gradually growing DLQ indicates a systemic issue that is not being resolved.
Error pattern concentration: If 80% of DLQ entries share the same error message, that is a systematic failure worth immediate attention. Group DLQ entries by error message and alert when any single error type exceeds a threshold.

For the broader monitoring architecture that DLQ alerting fits into, including record-level accounting, end-to-end reconciliation, and alert calibration, see the guide on monitoring data integration pipelines in production.

137Foundry designs data integration architectures with built-in reliability patterns including dead letter queues, idempotency, and end-to-end reconciliation. The data integration service covers both new builds and reliability improvements to existing pipelines.