Inspect the source
Review schemas, data types, nested application fields and source-table coverage.
Data analytics case study · Portfolio risk
A $3.08B portfolio needs more than one risk limit.
I used BigQuery and Looker Enterprise to validate 270,299 loan records, model exposure and loss signals, and design a monitoring framework that connects an executive alert to the geography, status, purpose and vintage behind it.
Portfolio control map
Review triggered
Exposure against the internal monitoring level
The balance exceeds the illustrative $3.00B threshold. The supporting measures show why the response cannot stop at the aggregate.
Outstanding exposure
$3.08B
$0
$3.00B monitoring level
Top-five states41.81%Geographic concentration
Current status87.89%Largest status exposure
Purpose loss rate9.31%Small-business loans
Outstanding exposure$3.08B
Above the illustrative $3.00B internal monitoring level.
Top-five state share41.81%
Material geographic concentration across the largest state balances.
Largest purpose exposure$1.83B
Debt consolidation dominates outstanding portfolio volume.
Highest purpose loss rate9.31%
Small-business lending carries the highest observed purpose-level rate.
Looker Enterprise
One monitoring surface, several levels of investigation.
The dashboard keeps the executive signal visible while cross-filtering reveals the states, years, purposes and borrower records behind it.
Dashboard evidence
The image will appear when the corresponding site asset is available.
Risk lenses
What explains the portfolio-level alert?
Most displayed status exposure is current, but the portfolio still contains a material charged-off component and a delinquency pipeline that requires separate monitoring.
Current exposure dominates the status mix.
The current category holds most of the displayed status exposure. Charged-off and delinquent balances still require their own controls because their business meaning differs from the active portfolio.
Largest displayed share
87.89%
Select a row to update the interpretation. Values come from the validated analytical outputs used in the project.
Method and data quality
Build trust before building the dashboard.
The analysis begins with row-level validation and explicit risk definitions. Reusable marts then connect every executive measure to documented SQL and a traceable source grain.
BigQuery evidence
The analytical layer beneath the interface.
Schema inspection established the available fields, nested application structure and data types before transformation logic was written.
BigQuery evidence
The image will appear when the corresponding site asset is available.
Validate the grain
Compare row counts with distinct loan IDs and test core fields and mappings.
Define risk logic
Create explicit outstanding, delinquency and loss flags at loan level.
Build reusable marts
Aggregate statuses, states, regions, purposes, years and executive findings.
Activate monitoring
Connect the analytical layer to filters, cross-filtering and threshold formatting.
Validated grain270,299 loans
Geographic mapping52 jurisdictions
Analytical outputs7 reusable tables
Technical evidenceInspect the BigQuery layer
Representative BigQuery SQLInspect the loan-level risk logic
CREATE OR REPLACE TABLE fintech.loan_risk_mart AS
SELECT
l.loan_id,
l.customer_id,
l.loan_status,
l.loan_amount,
l.state,
sr.subregion,
sr.region,
l.int_rate,
CAST(l.issue_year AS INT64) AS issue_year,
COALESCE(l.application.purpose, 'Unknown') AS purpose,
l.loan_status != 'Fully Paid' AS is_outstanding,
l.loan_status IN (
'Late (16-30 days)',
'Late (31-120 days)',
'In Grace Period'
) AS is_delinquent,
l.loan_status IN ('Charged Off', 'Default') AS is_loss
FROM fintech.loan AS l
LEFT JOIN fintech.state_region AS sr
ON l.state = sr.state;Decision logic
One aggregate can alert. It cannot explain.
The threshold becomes useful only when the same analytical layer explains which concentrations, segments and cohorts are driving the signal.
Signal
The threshold creates visibility, not a diagnosis.
Implication
Outstanding exposure reached approximately $3.08B, above the illustrative $3.00B monitoring level. The breach should trigger investigation rather than a single undifferentiated response.
Signal
Geographic concentration changes the downside.
Implication
California holds about $419.53M in outstanding exposure. Together, the five largest states account for 41.81% of the portfolio.
Signal
High volume and high loss rate are different risks.
Implication
Debt consolidation dominates outstanding exposure, while small-business loans have the highest observed purpose-level loss amount rate at 9.31%. They require different controls.
Signal
Growth requires cohort context.
Implication
Outstanding exposure rose sharply for originations after 2015 and reached $796.72M for the 2019 cohort. Vintage analysis separates scale from changing performance.
Monitoring framework
Move from one limit to connected controls.
The strongest operating model links the executive threshold to the concentration and segment measures that explain it, creating a direct path from signal to investigation.
Trace the breach
Identify which years, states, statuses and purposes contribute most to the outstanding balance.
- Threshold-aware total
- Connected filters
- Clear investigation path
Control concentration
Monitor state share and the combined top-five exposure, then define review points for sustained increases.
- State watchlist
- Top-five concentration
- Regional context
Separate risk types
Distinguish volume concentration from higher observed loss rates and add migration and vintage measures when time-series data becomes available.
- Purpose-level controls
- Status migration
- Vintage monitoring
Risk-monitoring architectureSignal-led, not dashboard-first
Executive signalTotal outstanding exposure
Diagnostic layerGeography, status, purpose and vintage
Management actionTriggers, review and targeted controls
Scope and interpretation
Useful for monitoring, bounded by the available data.
The project demonstrates a traceable analytical workflow and decision framework. Clear limitations prevent the portfolio snapshot from being interpreted as a live credit model.
It uses a training dataset and does not represent a live lender portfolio.
It is not a regulatory capital limit or a universal industry benchmark.
Alternative outstanding, delinquency or loss definitions would change the results.
Migration models, roll rates and repayment curves cannot be derived from this snapshot alone.
Project evidence
Inspect the work behind the dashboard.
The public files separate analytical outputs, documented SQL and the concise hiring-level narrative.
CSV · Analytical exports
Validated outputs for portfolio review.
Portfolio summary, yearly growth, purpose risk, state watchlist, regional status mix and borrower detail.
SQL · BigQuery scriptsDocumented logic from source to mart.
Source validation, geographic mapping, risk-mart creation, executive summaries and analytical queries.
PDF · Executive summaryA concise decision memo for employers.
Business context, method, principal findings, recommendations and project limitations.
Contact
Need a clearer answer from complex data?
I combine analytical discipline with multilingual SEO and editorial judgement to turn evidence into decisions people can act on.