Prepared by: Steve Farmer
Date: July 20, 2026
Platform: Snowflake + FastAPI
Database: FARMER_DB
This project implements a production-minded private credit data platform that unifies three source systems:
The solution uses Snowflake-native ingestion and transformation, a layered RAW → STAGING → MARTS architecture, data-quality logging and quarantine handling, Streams and Tasks for incremental processing, role-based access control, portfolio analytics, and a locally hosted FastAPI service backed by live Snowflake queries.
The final platform successfully demonstrated an end-to-end remittance flow:
FastAPI → RAW → Stream → Task → STAGING → MARTS
| Assessment Area | Implementation |
|---|---|
| Layered architecture | RAW, STAGING, and MARTS schemas |
| Native ingestion | Named file format, internal stage, and COPY INTO |
| Data quality | Standardization, deduplication, conflict resolution, logging, quarantine |
| Incremental processing | Snowflake Stream, capture procedure, processing procedure, and task graph |
| Analytics | Portfolio outstanding, top borrowers, monthly remittances, industry exposure |
| Security | Analyst, Finance, and LP read-only roles |
| REST API | Loans, borrower loans, portfolio summary, remittance creation |
| Documentation | Architecture, assumptions, delta results, design questions, AI disclosure |
The RAW schema preserves source data as landed, including mixed strings for currency, dates, percentages, and source-specific names. Each row includes lineage fields such as source filename, source row number, and load timestamp.
The STAGING schema performs:
The MARTS schema provides business-ready structures with clear grains:
| Object | Grain |
|---|---|
DIM_BORROWER |
One row per canonical borrower or prospect |
FACT_LOAN |
One row per Factorview facility |
FACT_TRANSACTION |
One row per accepted Tenor transaction |
VW_LP_PORTFOLIO_SUMMARY |
One portfolio-level summary row |
LOAN_ID is the Factorview facility identifier.TRANSACTION_ID is the Tenor transaction identifier.FVONLY_.DIM_BORROWER even when they have no current facility.This design can extend to additional systems by adding new source-to-canonical identity mappings without changing the core mart grain.
Factorview is treated as authoritative for current outstanding balance because it is the loan-servicing platform.
Tenor contains transaction activity but is not assumed to be a complete servicing ledger. Reconstructing balances from the provided Tenor extract produced material discrepancies, including transaction-derived balances for paid-off facilities.
Therefore:
FACT_LOAN.OUTSTANDING_BALANCE.Affinity is treated as authoritative for borrower industry because it is the CRM system.
Industry exposure uses a left join from loans to borrowers so Factorview-only borrowers remain visible under an UNMAPPED / UNKNOWN classification.
The normalized status values are:
ACTIVEWATCHLISTCLOSEDPAID_OFFPAID_OFF is intentionally preserved as a separate status because successful repayment is analytically different from a facility closed for another reason.
The implementation detected and handled:
STAGING.DATA_QUALITY_LOG records each issue, source row, action taken, severity, and resolution status.
STAGING.QUARANTINED_RECORDS contains unresolved records requiring investigation.
Deterministic duplicates are:
RESOLVEDDEDUPEDThis keeps quarantine focused on records requiring a human decision.
The incremental pipeline includes:
RAW.TENOR_TRANSACTIONS_STREAMSTAGING.TENOR_INCREMENTAL_BATCHSTAGING.CAPTURE_TENOR_STREAM()STAGING.PROCESS_TENOR_BATCH()STAGING.TASK_CAPTURE_TENOR_STREAMSTAGING.TASK_PROCESS_TENOR_BATCH| Transaction | Facility | Result | Reason |
|---|---|---|---|
INV-70001 |
FV-1001 |
PROCESSED |
Valid transaction |
INV-70002 |
FV-1011 |
PROCESSED |
Valid transaction |
INV-70003 |
FV-1023 |
PROCESSED |
Valid transaction |
INV-70004 |
FV-1036 |
PROCESSED |
Valid transaction |
INV-50100 |
FV-1023 |
DEDUPED |
Exact duplicate of an existing transaction |
INV-70005 |
FV-9201 |
QUARANTINED |
Facility did not exist |
Final batch status:
PROCESSEDDEDUPEDQUARANTINEDA test remittance submitted through FastAPI produced:
| Layer | Result |
|---|---|
| RAW rows | 1 |
| STAGING rows | 1 |
| MART rows | 1 |
| Processing status | PROCESSED |
This verified:
POST /remittances → RAW → Stream → Task → STAGING → MARTS
| Metric | Result |
|---|---|
| Total facilities | 40 |
| Active | 26 |
| Watchlist | 6 |
| Closed | 4 |
| Paid off | 4 |
| Total committed limit | $70,791,900.00 |
| Valid outstanding exposure | $30,409,358.10 |
| Portfolio utilization | 42.96% |
| Weighted-average discount rate | 11.56% |
| Facilities excluded for invalid balance | 3 |
The excluded facilities were:
FV-1008 — missing balanceFV-1013 — negative balanceFV-1022 — missing balance| Rank | Borrower | Exposure | Share |
|---|---|---|---|
| 1 | Harbor Reach Funding | $3,084,485.52 | 10.14% |
| 2 | Longview Trade Credit | $2,762,142.45 | 9.08% |
| 3 | Elmgrove Capital Partners LP | $2,379,216.40 | 7.82% |
| 4 | Kingsford Receivables | $2,279,859.96 | 7.50% |
| 5 | Ashbury Asset Finance | $2,119,612.13 | 6.97% |
The analytics SQL returns monthly 2025 remittance totals by normalized fund. Fund aliases are standardized before aggregation so abbreviated and full fund names report together.
The detailed SQL and result set are preserved in:
05_analytics.sqlIndustry exposure uses a left join from loans to borrower attributes.
The result intentionally retains unmatched borrower exposure:
UNMAPPED / UNKNOWN: $9,979,155.93This prevents unmatched borrowers from silently disappearing from portfolio risk reporting.
FARMER_DB_ANALYST_ROFARMER_DB_FINANCEFARMER_DB_LP_READONLYFuture-object grants were attempted, but the candidate role lacked the account-level MANAGE GRANTS privilege.
Existing-object grants were applied successfully. The intended future-grant SQL and the permission limitation should remain documented in the repository.
The API is a controlled service layer for applications and users who should not connect directly to Snowflake.
Possible consumers include:
The API does not perform ETL. It:
| Method | Path | Purpose |
|---|---|---|
GET |
/health |
Verify Snowflake connection |
GET |
/loans |
Filtered and paginated loan list |
GET |
/borrowers/{borrower_id}/loans |
All loans for one borrower |
GET |
/portfolio/summary |
Live portfolio metrics and top 5 exposures |
POST |
/remittances |
Validate and record a remittance |
GET /loansSupports status, fund, limit, and offset.
GET /loans?status=ACTIVE&limit=10&offset=0POST /remittancesValidates:
YYYY-MM-DDValid requests return HTTP 201.
git clone <repository-url>
cd farmer-snowflake-assessment
py -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txtCopy-Item .env.example .env
notepad .envSNOWFLAKE_ACCOUNT=ORAWGAX-PN44016
SNOWFLAKE_USER=FARMER_USER
SNOWFLAKE_PASSWORD=your_password
SNOWFLAKE_ROLE=CANDIDATE_FARMER
SNOWFLAKE_WAREHOUSE=WH_FARMER
SNOWFLAKE_DATABASE=FARMER_DB
SNOWFLAKE_SCHEMA=MARTSThe .env file is excluded from Git.
Run SQL files in numerical order:
sql/
├── 01_setup_and_raw.sql
├── 02_staging_and_quality.sql
├── 03_marts.sql
├── 04_streams_tasks_and_delta.sql
├── 05_analytics.sql
├── 06_rbac.sql
└── 07_validation.sqlLoad the three initial files first. Load tenor_transactions_delta.csv only after the Stream and Tasks are created.
python -m uvicorn main:app --reloadSwagger:
http://127.0.0.1:8000/docsHealth check:
Invoke-RestMethod http://127.0.0.1:8000/healthIn production, the FastAPI service would be packaged as a Docker container and deployed to a managed runtime such as AWS ECS/Fargate, Azure App Service, Google Cloud Run, or an existing Kubernetes platform.
The runtime would provide:
A dedicated least-privilege Snowflake service role would replace the development candidate role. Operational ownership would normally sit with the data platform or application platform team, with failures routed through the firm's standard incident-management process.
For a source that exposes only a paginated REST API, I would use a small authenticated extraction service with secrets stored in a managed secrets system. The extractor would request pages sequentially and persist a high-water mark or source cursor so failed runs can restart from the last confirmed page. Because the source allows 60 requests per minute, I would intentionally remain below the limit, honor Retry-After, and use bounded exponential backoff with jitter for HTTP 429 and transient 5xx errors. Permanent 4xx responses would stop the run and raise an alert. Each extraction would write immutable, timestamped files to an S3 landing prefix with row counts, source timestamps, and checksums. A Snowflake external stage would expose the files, and Snowpipe or scheduled COPY INTO statements would load RAW. A manifest table would track landed, loaded, and failed objects for auditability and recovery.
I would use a scheduled root Snowflake Task with a CRON expression and downstream tasks for staging, marts, and quality checks. Stream-triggered tasks would process intraday changes, while the scheduled task would provide a predictable daily reconciliation cycle. Each run would write a pipeline-run record containing start time, end time, status, row counts, and error details. Monitoring would query task history and the pipeline-run table. Snowflake Alerts and a notification integration would notify the support channel when a task fails, runs longer than expected, processes an unexpected zero rows, or exceeds a data-quality threshold.
The current pipeline compares incoming transaction IDs with existing cleaned transactions. A resent transaction is classified as DUPLICATE_TRANSACTION, excluded from STAGING and MARTS, logged as resolved, and marked DEDUPED. The canonical row is retained. In production, I would add a deterministic business hash based on source system, transaction ID, facility, date, type, and amount. I would also maintain a processed-file manifest with file checksums and use transactional MERGE statements. These controls protect against duplicate files, repeated rows, renamed copies, and repeated API pages.
Excel users would receive governed access through the Snowflake Excel connector, ODBC, or curated extracts backed by secure views. SQL analysts would receive role-controlled access to documented MARTS tables and views. Leadership would use a governed BI dashboard or narrow API rather than direct access to detailed tables. For AI-assisted querying, I would first create a governed semantic layer containing approved metrics, relationships, synonyms, and row-level security. The AI interface would query only that semantic layer through a restricted service role, log generated SQL, expose source lineage, and clearly handle ambiguous financial terminology.
I used ChatGPT to assist with drafting Snowflake SQL, reviewing data-quality approaches, developing the FastAPI service, debugging errors, and organizing documentation. I executed the work incrementally, reviewed query results, and materially changed generated recommendations when they did not match the business meaning, the assessment requirements, or observed system behavior.
PAID_OFFAI initially normalized both Paid Off and Closed facilities to CLOSED.
I rejected that simplification because successful repayment is analytically different from a facility closed for another reason. I changed the normalized model, mart, summary view, and API output to preserve PAID_OFF as a distinct status.
AI initially placed exact duplicate incremental transactions into the quarantine table and marked their batch status as QUARANTINED.
I reviewed INV-50100 and confirmed that every business field matched the previously loaded transaction. Because the record required no investigation, I changed the processing policy and stored procedure. Exact duplicates are now logged as resolved, marked DEDUPED, and excluded idempotently. Quarantine is reserved for unresolved issues such as unmatched facilities and invalid values.
AI initially configured the Python connector using the account locator FI80546. That produced an invalid hostname and an HTTP 404 during login.
I queried Snowflake using CURRENT_ORGANIZATION_NAME() and CURRENT_ACCOUNT_NAME(), identified the correct connector identifier as ORAWGAX-PN44016, and updated both .env and .env.example.
PAID_OFF remains distinct from CLOSEDfarmer-snowflake-assessment/
├── main.py
├── database.py
├── requirements.txt
├── .env.example
├── .gitignore
├── README.md
└── sql/
├── 01_setup_and_raw.sql
├── 02_staging_and_quality.sql
├── 03_marts.sql
├── 04_streams_tasks_and_delta.sql
├── 05_analytics.sql
├── 06_rbac.sql
└── 07_validation.sql