Markdown

Screen a Loan Portfolio in SQL

Combine SQL filtering and ordering with a native portfolio stress calculation, then export a typed Parquet report. DuckDB handles the relational operations; a Codon UDF evaluates the custom rule without a Python callback for each row.

Uses: DuckDB and Data SDKs. Services: none. Difficulty: intermediate.

The rule below is a teaching example, not a credit model: stressed loss is balance times an annual default probability, a sector multiplier, and a 45% loss-given-default assumption. It returns a missing estimate when probability is missing. Production models need validation, governance, versioning, and approved assumptions.

Query With a Native Rule

portfolio.codon

import exaloop.databases.duckdb as duckdb
import exaloop.data as data

def stressed_loss(balance: float, probability, sector: str) -> Optional[float]:
    if probability is None:
        return None
    if not 0.0 <= probability <= 1.0 or balance < 0.0:
        raise ValueError("invalid portfolio input")
    multiplier = 1.5 if sector == "retail" else 1.0
    return balance * min(1.0, probability * multiplier) * 0.45

identifiers: list[int] = []
losses = []
with duckdb.connect(threads=2) as connection:
    connection.create_function("stressed_loss", stressed_loss,
                               Tuple[float, Optional[float], str], Optional[float])
    connection.execute("CREATE TABLE loans (id BIGINT, balance DOUBLE, probability DOUBLE, sector VARCHAR)")
    connection.execute("INSERT INTO loans VALUES (1, 1000, 0.02, 'retail'), (2, 2000, 0.01, 'utility'), (3, 500, NULL, 'retail')")
    with connection.query("SELECT id, stressed_loss(balance, probability, sector) FROM loans ORDER BY id",
                          stream=True) as result:
        for batch in result.batches(Tuple[int, Optional[float]], size=1024):
            for identifier, loss in batch:
                identifiers.append(identifier)
                losses.append(loss)

assert abs(losses[0] - 13.5) < 1e-9
assert abs(losses[1] - 9.0) < 1e-9
assert losses[2] is None
with data.table({"loan_id": data.array(identifiers),
                 "stressed_loss": data.array(losses)}) as report:
    report.write("portfolio-risk.parquet")
known = sum(loss for loss in losses if loss is not None)
print("known_loss", known, "missing_estimates", sum(int(loss is None) for loss in losses))

Terminal

exaloop --with duckdb --with data run -release portfolio.codon

Output

known_loss 22.5 missing_estimates 1

Do not interpret the known-loss total as the whole portfolio's risk: one account has no estimate. The null is preserved in the report, and the output explicitly counts the missing estimate. Floating point is used for modeled quantities, not for posting ledger entries.

Scale the Query, Not the Fixture

Replace the fixture table with a controlled Parquet scan, for example FROM read_parquet('loans.parquet'), after validating its column types. The embedded SDK includes Parquet support; it does not need to download an extension. SQL text here is application-controlled. The embedded API does not expose bound parameters, so never interpolate user-supplied file paths or SQL fragments.

The callback is deterministic and has no mutable shared state, so DuckDB can run it on worker threads. Do not put network calls or queries on the same connection inside the callback. For batch calculations, consider the vectorized interface.

Streaming bounds row conversion batches, not DuckDB's sorting or join memory. This small example accumulates all output rows before writing. For a large job, write each result batch as a separate Parquet part, or use DuckDB's SQL COPY for a direct file export when no additional Data SDK processing is needed.

The same boundary is useful for eligibility rules, insurance scenarios, and inventory replenishment. For server-side deployments rather than an embedded application, see PostgreSQL functions and MySQL functions; those require host-specific build and deployment checks.