Building a decision tool from 17.1M+ CFPB complaint records.
I built Customer Resolution Intelligence to turn 17.1M+ CFPB complaint records into trends, priority signals, and actions. The hard part wasn’t loading the data into Snowflake. It was figuring out which patterns I could trust, what the data could actually tell me, and where the product needed to stop.
Role
Product Manager and builder
Data
Dec 2011 – Jul 2026
Stack
Snowflake, dbt, Next.js, Vercel
Status
Built and deployed
0.0M+
Complaint records modeled
0
Complete months, Dec 2011 – Jul 2026
0
dbt models across four stages
0
Automated tests on the pipeline
What I was trying to solve
I wanted someone looking at this data to be able to answer three basic questions.
What is changing?
How volume is changing by category and issue, compared with the 176 months that came before it.
What deserves attention?
A priority and a reason for every record, based on rules I defined rather than a score I couldn’t explain.
Where should I look next?
Start with a month, see which issues changed, and then drill into the companies behind those changes.
The product organizes 17.1M+ records published since December 2011 across 11 product categories and 178 issue areas. I kept the definitions consistent so a change could be judged against what came before it.
The archive, month by month
2,536 complaints in the first month. 700,437 in the most recent one.
16,896,978
Records inside complete months
+171,802
Largest single-month rise, Jan 2025
Monthly published complaint volume. Dashed lines mark the two CFPB taxonomy renames. Hover any point for that month’s total.
Product Categories
Hover a segment to see the label those records were filed under.
I mapped the published labels back to a common product category so a change in naming wouldn’t be mistaken for a change in the complaint pattern itself.
The same credit reporting complaints were published as “Credit reporting,” then “Credit reporting, credit repair services, or other personal consumer reports,” then “Credit reporting or other personal consumer reports.” Read literally, the first two look like products that died and a third that appeared from nowhere in 2023.
Grouped into one lineage, they are 13.7M records — 80% of the whole archive, and a single continuous trend line rather than three broken ones.
Eleven categories in total, mapped from twenty-one published labels. Hover any segment to see the label those records were filed under.
How I built it
I kept the pipeline deliberately simple: preserve the source, transform it in stages, test the assumptions, and only expose the data the product actually needs. I wasn’t trying to expose every possible metric. I wanted a smaller set of metrics I could explain, test, and trust.
CFPB Source
Structured public complaint data
- Consumer Complaint Database, bulk CSV archive
- 17.1M published records, 16 fields
- Product, issue, response, date, and state fields
- No narrative text, no consumer identifiers
Snowflake Load
Keep the source intact
- Chunked loading of the bulk extract
- Record count validated against source metadata
- Landed unchanged in the RAW schema
dbt Transform
Give each model one clear job
Staging
Clean and standardize the source fields
stg_cfpb_complaintsIntermediate
Join records, calculate metrics, derive context
int_complaint_status_contextint_issue_daily_volumeint_company_issue_patternsint_issue_trendsint_resolution_signalsMarts
Publish the analytical datasets the product reads
dim_issue_taxonomyfct_complaintsfct_issue_daily_metricsoperations_overview_metricsagent_case_contextDecisioning
Apply policy precedence, produce priority and reason
int_priority_policy_applicationresolution_action_queueSnowflake Serve
Make the finished models available to the product
- Analytics schema, materialized mart tables
- Analytical views, decisioning surfaces
- Curated metrics only
Next.js Publish
Send only the data the product needs
- Read-only analytical access
- Explicit column allowlist
- Reviewed aggregate output
- Versioned JSON, tracked in Git
Vercel Experience
Two ways to use the same underlying data
- Public app: overview, insights, exploration
- Streamlit operations console: pipeline health, freshness, quality checks
Hover a dbt layer to see the models it holds. Scroll sideways to follow the pipeline.
Keep the source intact
I wanted a copy of the original data I could always go back to. The raw layer stays unchanged, so when a number looked wrong downstream I could trace it back to the source instead of guessing where the problem started.
Make each transformation explainable
I broke the transformation work into models with one job each. That made it easier to test individual assumptions, see where a metric came from, and change one part of the pipeline without losing track of the rest.
The architecture in one sentence
Snowflake stores and serves the analytical data. dbt transforms and tests it. The application only sees curated analytical output — never the raw records.
Snowflake
Storage and the warehouse both the load and the analytics run on
dbt
Transformation, testing, and the dependency graph between models
Next.js
The application, reading only the reviewed aggregates
Vercel
Deployment and hosting for both surfaces
Streamlit
The operations console: pipeline health, freshness, quality checks
From patterns to a next step
I defined a small set of rules that look for specific patterns in the data and assign a priority and a reason. Each result keeps the context behind the decision, so someone reviewing it can understand why it was flagged rather than being handed a score with no explanation.
Priority assigned to all 17.1M records
How many records each rule fired on
Most records are meant to come out LOW
Every record leaves the queue with a priority and the rule that set it. 12.1M of them — 70.7% — come out LOW, and that is the point: the rules exist so the 19,363 records that need real attention can be found at all. A queue that flags everything is the same as a queue that flags nothing.
The four priorities partition the archive exactly. The rule counts underneath do not: a record can trip more than one rule, so they sum to 17.6M against a population of 17.1M. I kept them as counts rather than shares for that reason — presenting them as slices of a whole would be arithmetic that quietly double-counts.
These are the rules run against the full dataset, producing the same output the live product reads. The working surface, with filters and drill-down, is at /explore.
13 Models built in dbt
Clean and standardize the source fields
Join records, calculate metrics, derive context
Publish the analytical datasets the product reads
Apply policy precedence, produce priority and reason
stg_cfpb_complaints
view
int_complaint_status_context
view
dim_issue_taxonomy
table · 2,642 rows
int_issue_daily_volume
view
int_company_issue_patterns
view
fct_complaints
table · 17,119,581 rows
int_issue_trends
view
fct_issue_daily_metrics
table · 314,733 rows
int_resolution_signals
view
int_priority_policy_application
view
resolution_action_queue
table · 17,119,581 rows
operations_overview_metrics
table · 328,994 rows
agent_case_context
table · 17,119,581 rows
Hover a model to see its grain, what it does, and what it connects to. Columns are build order; scroll sideways to follow the graph.
What I learned
What I liked about this project was being able to move between the data and the product myself. If a number looked strange, I could trace it through the model, figure out what was happening, change the logic, and see the effect in the product.
That also changed how I think about dbt. Before building this, I mostly thought of it as a tool for transforming data. Working through the models myself made the bigger value obvious: I could define exactly how a metric was calculated, test the assumption behind it, and make sure the same definition was used everywhere.
Snowflake became the part of the stack I spent the most time exploring because it let me work directly with the data and test ideas quickly. I used Streamlit as a fast way to try those ideas and make operational decisions before turning them into the main product experience.
Next work
Vel’Afrika