How it’s built

From structured public data to decision-ready intelligence

A batch ELT pipeline loads CFPB complaint data into Snowflake, transforms it through a dependency-driven dbt model DAG, applies analytical and decisioning logic, and publishes curated data for exploration.

  1. CFPB

    Source

    Structured public complaint data

    • Consumer Complaint Database
    • Bulk CSV extract
    • Published complaint records
    • Product, issue, response, date and state fields
  2. Snowflake

    Ingest

    Source-preserving load into Snowflake RAW

    • Chunked loading of the bulk extract
    • Record count validated against source metadata
    • Landed unchanged in the RAW schema
  3. dbt

    Transform

    A dependency-driven model DAG, executed inside Snowflake

    StagingClean and standardize source fieldsstg_cfpb_complaints
    IntermediateJoin records, calculate metrics, derive analytical context, evaluate business logicint_complaint_status_contextint_issue_daily_volumeint_issue_trendsint_resolution_signalsint_company_issue_patterns
    MartsPublish reusable analytical datasetsdim_issue_taxonomyfct_complaintsfct_issue_daily_metricsoperations_overview_metricsagent_case_context
    DecisioningApply policy precedence; produce priorities, reason codes and confidenceint_priority_policy_applicationresolution_action_queue
  4. Snowflake

    Analytics

    Where dbt models are materialized and served

    • Analytics schema
    • Materialized mart tables
    • Analytical views
    • Decisioning surfaces
    • Curated metrics
  5. Next.js

    Application data

    The analytical fields the product needs, and nothing else

    • Read-only analytical access
    • Explicit column allowlist
    • Reviewed aggregate output
    • Versioned JSON, tracked in Git
  6. Vercel

    Experience

    Two consumption surfaces on one analytical platform

    • Overview
    • Insights
    • Exploration
    • Decision support
    Streamlit operations consolePipeline health, data freshness, model status, quality checks
    Streamlit

The architecture in one sentence

Snowflake stores and serves the analytical data, dbt transforms and tests it, dbt Cloud orchestrates production runs, and the application consumes curated analytical outputs.

Snowflake
Data platform and warehouse
dbt
Transformation and analytical modeling
dbt Cloud
Orchestration, scheduling, CI and job execution
Next.js
Application experience
Vercel
Application deployment and hosting
17.1MPublished complaint records modeled
13dbt models
3Transformation layers
91Automated tests
2.53 GBRaw and analytics storage

The dbt models

Each model has a defined grain and a specific analytical responsibility. The dependency graph is generated from dbt ref() relationships, so the visual reflects the actual project DAG. Columns are build order; hover a model to isolate its dependencies.

stg_cfpb_complaintsview
int_complaint_status_contextview
dim_issue_taxonomytable · 2,642 rows
int_issue_daily_volumeview
int_company_issue_patternsview
fct_complaintstable · 17,119,581 rows
int_issue_trendsview
fct_issue_daily_metricstable · 314,733 rows
int_resolution_signalsview
int_priority_policy_applicationview
resolution_action_queuetable · 17,119,581 rows
operations_overview_metricstable · 328,994 rows
agent_case_contexttable · 17,119,581 rows
Hover any model to see its grain, what it does, and which dependencies it sits between.
Staging · 1 model Intermediate · 5 models Marts · 5 models Decisioning · 2 modelsMarts persist as tables; staging and intermediate stay views, so the warehouse holds 6 model tables and 3 seeds.

What each model is responsible for

Staging1

Cleans and standardizes the source fields once, so nothing downstream repeats it.

stg_cfpb_complaints

1 published complaint record, as received

Types every field once and drops rows missing anything decision-critical

Materialized as a view · feeds int_complaint_status_context, dim_issue_taxonomy

Intermediate5

Joins records, calculates metrics and derives the analytical context.

int_complaint_status_context

1 published complaint record

Labels each record's completeness so nothing downstream re-derives it

Materialized as a view · feeds int_issue_daily_volume, int_company_issue_patterns, fct_complaints, int_resolution_signals

int_issue_daily_volume

1 calendar date x product x issue

Counts complaints per day, product and issue — the grain every trend uses

Materialized as a view · feeds int_issue_trends

int_company_issue_patterns

1 calendar date x company x product x issue

Bounded company context, constrained so it cannot become a ranking

Materialized as a view

int_issue_trends

1 calendar date x product x issue x trend policy

Rolling window, baseline, change and share — decides what qualifies as emerging

Materialized as a view · feeds fct_issue_daily_metrics, int_resolution_signals

int_resolution_signals

1 published complaint record

Joins each record to the pattern it belongs to

Materialized as a view · feeds int_priority_policy_application, resolution_action_queue, agent_case_context

Marts5

Publishes the reusable datasets everything else reads.

dim_issue_taxonomy

1 product x sub-product x issue x sub-issue combination

The canonical product and issue label set

Materialized as a table · 2,642 rows · feeds fct_complaints

fct_complaints

1 canonical published complaint record

The canonical fact table every population count is taken from

Materialized as a table · 17,119,581 rows · feeds agent_case_context

fct_issue_daily_metrics

1 calendar date x product x issue

The trusted daily metric layer; its count is the only one safe to sum across dates

Materialized as a table · 314,733 rows · feeds operations_overview_metrics

operations_overview_metrics

1 metric date x dashboard dimension x metric name

Pre-aggregated display metrics so the app never recomputes at read time

Materialized as a table · 328,994 rows

agent_case_context

1 published complaint record

The agent-safe surface: factual context per record, no narrative text

Materialized as a table · 17,119,581 rows

Decisioning2

Evaluates the policies and turns analytical signals into one action per record.

int_priority_policy_application

1 complaint record x policy rule evaluated

Evaluates all six policies against every record and keeps each result

Materialized as a view · feeds resolution_action_queue

resolution_action_queue

1 complaint record x final recommendation run

Applies precedence and lands one action per record with its reason codes

Materialized as a table · 17,119,581 rows · feeds operations_overview_metrics, agent_case_context

How one complaint becomes a prioritized action

The path a single published record takes, and the model responsible at each step.

Complaint

A published CFPB complaint enters the pipeline.

Cleaned record

Types and required fields are standardized.

stg_cfpb_complaints
Issue context

The complaint's product and issue contribute to daily volume.

int_issue_daily_volume
Trend signal

The issue is compared against its own historical baseline.

int_issue_trends
Policy evaluation

The defined priority rules are evaluated against the record.

int_priority_policy_application
Recommended action

Policy precedence produces one recommendation with supporting reason codes and confidence.

resolution_action_queue