Small Business Lending Intelligence
An end-to-end analytics engineering project that ingests the full public record of SBA small-business lending, enriches it with the U.S. macroeconomic environment at each loan's origination, and answers a question a bank's credit officer actually asks: were these loans underwritten in a disciplined environment, or a dangerous one?
Roughly 2.1 million SBA 7(a) and 504 loan records, joined against Census business formation, BLS employment, and FRED macro series. Data-quality checks gate ingestion; 250 data tests validate every transformation.
The finding
SBA loans originated in 2007 defaulted at 31.7%. Loans from the 2012 vintage defaulted at 4.9%. A 6.5x gap.
The boom-era loans were not doomed by the economy they lived through. They were doomed by the lending standards they were underwritten under. Underwriting discipline at origination predicts defaults better than the macro climate that follows.
Who this is for
A community bank's chief credit officer has one question: is our SBA book a hidden risk?
They have their own loan tape. What they do not have is thirty years of industry-wide outcomes to benchmark against. Is their current underwriting aggressive or conservative relative to history? That gap is what this pipeline fills.
The core fear is timing risk: that loans underwritten in a loose-credit boom default at far higher rates than the same loans written in a disciplined environment, regardless of the borrower. So the question becomes: are we lending like it is 2006 right now?
Every boom vintage from 2004 to 2007 was underwritten while lending standards sat negative. Banks were loosening. Standards flipped sharply positive through the 2008 crash, and the vintages written into that discipline default around five percent.
The lesson is not avoid recessions. Loans written during the scary period did fine. The lesson is that underwriting discipline at origination outweighs the macro climate the loan lives through. Which makes today's lending standards a leading indicator of how today's originations will age.
Show the query
SELECT
approval_year,
SUM(total_loans) AS loans_originated,
ROUND(AVG(true_default_rate) * 100, 1) AS default_rate_pct,
ROUND(AVG(lending_standards), 1) AS lending_standards
FROM SBLI.MARTS.fct_loans_with_macro_context
WHERE approval_year BETWEEN 2004 AND 2013
GROUP BY approval_year
ORDER BY approval_year;Architecture
- Extract
- Python
- SBA, FRED, BLS, Census APIs
- Land
- AWS S3
- Raw JSON, canonical keys
- Load
- COPY INTO
- Batch, into Snowflake RAW
- Model
- dbt
- Staging, intermediate, marts
- Orchestrate
- Dagster
- Assets and DQ checks
- Serve
- Streamlit
- In Snowflake. Cortex Analyst for natural-language queries
Decisions
Anyone can list the tools. The decisions are the thing.
- Native extractors, not Fivetran
- No managed connectors exist for SBA, FRED, BLS, or Census. Paying for a platform that cannot reach your sources is paying for nothing.
- COPY INTO, not Snowpipe
- These sources publish on bursty, irregular cadences. Per-file ingestion overhead buys latency the use case does not need, at a cost it does not justify.
- Raw is sacred
- Nothing gets renamed, cast, or flattened on the way in. Every transformation belongs in dbt where it is tested and reviewable. If I am wrong about a type, I fix a model, not re-ingest 2M rows.
- S3 as a landing zone
- Decouples the failure domains. An API change breaks extraction; it does not corrupt the warehouse. And any load can be replayed without hitting a rate limit.