Revised 26 October 2026 · v1.0.0 · what changed

Code: github.com/herrrickshaw/ltm-warehouse-tests

Watch the walkthrough

Watch on YouTube: youtu.be/gA6xuLKGHZ4

Why it is useful

• Who benefits: anyone maintaining scripts that write into a price or fundamentals warehouse, who wants to re-check a change instead of trusting it.
• What it replaces: running backfill scripts against live data to see what happens.
• Evidence from the run: all three test files passed on a throwaway Postgres 17 on port 55432, the thin-history finder flagged 4 of 7 seeded symbols, and the dry run persisted 0 rows.
• Each seeded case encodes a real past bug: ticker already carrying a suffix, a symbol tagged under two countries, an NSE series switching mid-history.
• The database is created and dropped by the script, even when a test fails.

What it is for

The repo verifies four maintenance scripts for an LTM (long-term) warehouse: exchange resolution (exchange_map.py), thin-history detection (find_thin_history_tickers.py), exchange-list building, and the multi-tier backfill (Yahoo, then NSE, then SerpApi estimates) in backfill_missing_history.py.

run_tests.sh creates ltm_warehouse_test, applies a schema that mirrors the real tables, seeds synthetic symbols, generates a thin_history.csv fixture, runs test_backfill_logic.py, test_backfill_cli.py and test_nse_tier.py, then drops the database.

Tests check deduplication against existing dates, routing (Yahoo into the fact table, SerpApi into a separate low-fidelity estimate table), that a dry run never persists, and that the NSE tier fires only for India and collapses series switches to one row per date.

Who it is for

• Data engineers who maintain market-data backfills
• Anyone who wants a pattern for throwaway-database tests with Postgres
• Readers learning why exchange-qualified tickers matter

What you need

Get the code and an AI assistant

You do not have to be a programmer. Get the repository, give it to an AI assistant, and ask in plain language. Any of these routes works:

git clone https://github.com/herrrickshaw/ltm-warehouse-tests.git
cd ltm-warehouse-tests
claude      # or: gemini

Install

1. Clone the repo

   git clone https://github.com/herrrickshaw/ltm-warehouse-tests.git
   cd ltm-warehouse-tests

2. Create a virtual environment and install requirements, pinning SQLAlchemy below 2.1

   python3 -m venv .venv && . .venv/bin/activate
   pip install -r requirements.txt "sqlalchemy<2.1"

3. Optional: start a disposable Postgres of your own on another port so nothing shared is touched

   initdb -D ./pgdata -A trust -U tester
   pg_ctl -D ./pgdata -o "-p 55432 -k /tmp/pgsock" -l pg.log start

Quick start: commands and what you should see

Run the whole suite (default local Postgres)

./run_tests.sh

Creates the test database, applies the schema, seeds data, runs three test files and ends with 'All tests passed.' then drops the database.

Run against a throwaway server on port 55432

export PGPORT=55432 PGHOST=127.0.0.1 PGUSER=tester
PG_ADMIN_URL=postgresql://tester@127.0.0.1:55432/postgres ./run_tests.sh

Same output. Note that run_tests.sh builds TEST_DB as postgresql://localhost/<name> with no port, so on a non-default port you must set the PG* variables or edit that line.

Use another database name

TEST_DB_NAME=my_ltm_test ./run_tests.sh

The same flow with your name.

Sample outputs

Real output from a run on the date shown in the sources line; trimmed to fit.

Seeding and the thin-history finder (run 19 Oct 2026)

./run_tests.sh
Seeded 6975 rows across 7 symbols
Checked 7 symbols. Flagged 4 with < 5.0y history or < 85% day-coverage within their span.

SYMBOL    COUNTRY  REASON         SPAN(y)  COVERAGE  MISSING(y)
NEWCO     US       short_history  0.49     100.0     4.51
OLDIN     IN       short_history  3.0      100.0     2.0
BARC      GB       short_history  4.49     100.0     0.51
GAPCO     US       gappy          5.98     20.1      0.0

Synthetic symbols, not real market data.

Backfill logic tests

python3 tests/test_backfill_logic.py
OK dedup_and_insert_yahoo: added 1435 new rows, rerun added 0
OK dry_run_does_not_persist: reported 2 rows, actually persisted 0
OK serpapi_fallback: added 2 estimate rows (overlap correctly skipped); fact table untouched (still 783 real rows)
OK no_source_available: note='no yahoo/nse data; no --serpapi-key given for fallback'

Run inside run_tests.sh.

NSE tier tests

python3 tests/test_nse_tier.py
OK nse_tier_used_only_for_india_and_dedups_series_switch: added 2 rows (expected 2)
OK non_india_country_skips_nse_tier: source='none'
OK india_symbol_with_no_data_anywhere: note='no yahoo/nse data; no --serpapi-key given for fallback'

All NSE-tier tests passed.
All tests passed.

Final lines of the same run, ending with the throwaway database being dropped.

Ways to use it

Sample prompts for your AI assistant

Copy, paste and edit the parts in [brackets].

• Explain the ltm-warehouse-tests repository in plain English: what problem it solves, what the main files do, and what I need installed.
• Run ./run_tests.sh against a throwaway Postgres on port 55432 and summarise which checks passed.
• Explain what the four seeded edge cases in tests/seed_test_data.py are and which past bug each one prevents.
• Add a test where a symbol already carries a .HK suffix and show me the assertion you would write.
• Why does run_tests.sh build TEST_DB without a port, and how would you change it to honour PG_ADMIN_URL's port?
• Read exchange_map.py and explain how a bare ticker is resolved to an exchange-qualified one.

Limits and honest caveats

Sources: ltm-warehouse-tests README, run_tests.sh, tests/ and scripts/; run_tests.sh run on 19 October 2026 on a fresh clone against a throwaway Postgres 17 on port 55432 in a scratch directory (stopped and deleted afterwards), with SQLAlchemy 2.0.55 in a throwaway virtual environment. The Colab notebook was not run.

About this article: Researched, written and edited by Umashankar Triplicane Dwarakanathan, with AI research assistance; every figure is meant to trace to the primary source cited. See the Editorial Policy for how sourcing, AI use and corrections work.