KLIM Datatape¶

Comprehensive loan information for KLIM portfolio, processed with a unified customer data pipeline including loan details, customer demographics, payment history, and account information.

Overview¶

Generation Frequency: Monthly Development Status: Stable

Key Features: - Unified customer pipeline - Comprehensive loan tracking - Payment history analysis - Customer demographics

Key Stakeholders: - Treasury Reporting Team - KLIM

Output Columns¶

The following columns are included in the final datatape output (filtered from SQL queries based on configuration):

Column Analysis Summary¶

Total columns found in SQL queries: 95
Columns specified in output_columns config: 75
Columns found in SQL and included in output: 31
Missing columns (in config but not found in SQL): 46

Note: Only columns that exist in SQL queries are included in the final output.

Column Name	Required	Source Query	Source Table	Source Field	Derived
Address1	Yes	main_loan_data_query	filtered_systems	Address1	No
City1	Yes	main_loan_data_query	filtered_systems	City1	No
CurrentRate	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	CurrentRate	No
Dealer	Yes	main_loan_data_query	sfdc_staging.vw_account	Dealer	No
DraftDate	Yes	main_loan_data_query	filtered_systems	DraftDate	No
FICO	Yes	main_loan_data_query	sfdc_staging.vw_system_project__c	FICO	No
FeesBalance	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	FeesBalance	No
FirstPaymentDueDate	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	FirstPaymentDueDate	No
FullPayoffBalance	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	FullPayoffBalance	No
In-Service Date	Yes	main_loan_data_query	sfdc_staging.vw_system_project__c	In-Service Date	No
InterestAccruedThrough	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	InterestAccruedThrough	No
InterestBalance	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	InterestBalance	No
LastPaymentAmount	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	LastPaymentAmount	No
LastPaymentDate	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	LastPaymentDate	No
LoanNumber	Yes	main_loan_data_query	filtered_systems	LoanNumber	No
LoanStatus	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	LoanStatus	No
MaturityDate	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	MaturityDate	No
MonthlyPaymentAmount	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	MonthlyPaymentAmount	No
NextPaymentDueToBePaid	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	NextPaymentDueToBePaid	No
OriginalLoanAmount	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	OriginalLoanAmount	No
OriginationDate	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	OriginationDate	No
OriginationState	Yes	main_loan_data_query	filtered_systems	OriginationState	No
PaymentFrequency	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	PaymentFrequency	No
PaymentsMade	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	PaymentsMade	No
Portfolio	Yes	main_loan_data_query	filtered_systems	Portfolio	No
PrincipalBalance	Yes	main_loan_data_query	sfdc_staging.vwx_loan__loan_account__c	PrincipalBalance	No
State1	Yes	main_loan_data_query	filtered_systems	State1	No
System	Yes	main_loan_data_query	filtered_systems	System	No
System	Yes	ach_info_query	sfdc_staging.vw_system__c	System	No
System	Yes	customer_data_query	filtered_systems	System	No
ZIP1	Yes	main_loan_data_query	filtered_systems	ZIP1	Yes

Note: Columns marked as 'Derived' are calculated or transformed rather than directly selected from the source table.

Data Sources¶

The configuration pulls data from the following sources:

Source Name	Type	Query/File	Description
main_loan_data_query	database	main_loan_data_query.sql	SQL query for main_loan_data_query data
ach_info_query	database	ach_info_query.sql	SQL query for ach_info_query data
customer_data_query	database	customer_data_query.sql	SQL query for customer_data_query data
product_info_query	database	product_info_query.sql	SQL query for product_info_query data
latest_dunning_query	database	latest_dunning_query.sql	SQL query for latest_dunning_query data

Configuration¶

Portfolio Groups¶

klim_portfolios: - LibertyAgg2

Database Configuration¶

dbcreds_environment: fusionods

Paths¶

sql_dir: ./sql
cache_dir: ./query_cache
output_dir: ./completed_output
log_dir: ./logs

SQL Queries¶

main_loan_data_queryach_info_querycustomer_data_queryproduct_info_querylatest_dunning_query

-- file: sql/main_loan_data_query.sql
-- Description: (Optimized) Retrieves the main loan account data for the specified portfolios.

WITH filtered_systems AS (
    SELECT 
        "Record ID",
        "Asset Portfolio - Customer",
        "System Name",
        "Installation State",
        "Payment Date",
        "Installation Address Line 1",
        "Installation Address City",
        "Installation Address State",
        "Installation Address Zip Code",
        "Contract Type",
        "UCC Filing Date",
        "UCC Termination Date",
        "Oldest Outstanding Due Date"
    FROM 
        sfdc_staging.vw_system__c
    WHERE 
        "Asset Portfolio - Customer" IN :portfolio_names
)
SELECT
    s."Record ID" AS "System",
    s."Asset Portfolio - Customer" AS "Portfolio",
    s."System Name" AS "LoanNumber",
    s."Installation State" AS "OriginationState",
    s."Payment Date" AS "DraftDate",
    s."Installation Address Line 1" AS "Address1",
    s."Installation Address City" AS "City1",
    s."Installation Address State" AS "State1",
    s."Installation Address Zip Code"::text AS "ZIP1",
    s."Contract Type",
    cl."Contract Date" AS "OriginationDate",
    cl."Maturity Date" AS "MaturityDate",
    cl."Last Interest Posting Date" AS "InterestAccruedThrough",
    cl."Interest Rate" AS "CurrentRate",
    cl."Term",
    cl."Payment Frequency" AS "PaymentFrequency",
    cl."Loan Amount" AS "OriginalLoanAmount",
    cl."Principal/Advance Remaining" AS "PrincipalBalance",
    cl."Interest Remaining" AS "InterestBalance",
    cl."Fees Remaining" AS "FeesBalance",
    cl."Payment Amount" AS "MonthlyPaymentAmount",
    cl."Last Payment Amount" AS "LastPaymentAmount",
    cl."Last Payment Date" AS "LastPaymentDate",
    cl."First Payment Date" AS "FirstPaymentDueDate",
    cl."Next Due Date" AS "NextPaymentDueToBePaid",
    cl."Repayments Count" AS "PaymentsMade",
    cl."Status" AS "LoanStatus",
    cl."Today's Payoff" AS "FullPayoffBalance",
    sp."Stage",
    sp."FICO High" AS "FICO",
    sp."InService Date" AS "In-Service Date",
    sp."Primary Customer Name First Name",
    sp."Primary Customer Name Last Name",
    ac."Legal Name" AS "Dealer",
    s."UCC Filing Date",
    s."UCC Termination Date",
    s."Oldest Outstanding Due Date"
FROM 
    filtered_systems s
JOIN 
    sfdc_staging.vwx_loan__loan_account__c cl ON cl."System" = s."Record ID"
JOIN 
    sfdc_staging.vw_system_project__c sp ON sp."System" = s."Record ID"
LEFT JOIN 
    sfdc_staging.vw_account ac ON ac."Account ID (2)" = sp."Installation Partner Account";

-- file: sql/ach_info_query.sql
-- Description: (Optimized) Determines if a loan account has an ACH payment method on record.

WITH filtered_loan_accounts AS (
    -- Get all loan accounts for the target portfolios
    SELECT 
        cl."Record ID" AS "Loan Account", 
        s."Record ID" AS "System"
    FROM 
        sfdc_staging.vw_system__c s
    JOIN 
        sfdc_staging.vwx_loan__loan_account__c cl ON cl."System" = s."Record ID"
    WHERE 
        s."Asset Portfolio - Customer" IN :portfolio_names
),
ach_payment_accounts AS (
    -- Find all unique loan accounts that have ever used ACH
    SELECT DISTINCT lpt."Loan Account"
    FROM sfdc_staging.vw_loan__loan_payment_transaction__c lpt
    JOIN sfdc_staging.vw_loan__payment_mode__c lpm ON lpm."Record ID" = lpt."Payment Mode"
    WHERE lpm."Payment Mode Name" = 'ACH'
)
SELECT
    fla."System",
    CASE
        WHEN apa."Loan Account" IS NOT NULL THEN 'Yes'
        ELSE 'No'
    END AS "Is ACH"
FROM 
    filtered_loan_accounts fla
LEFT JOIN 
    ach_payment_accounts apa ON fla."Loan Account" = apa."Loan Account";

-- file: sql/customer_data_query.sql
-- Description: (Optimized and Corrected) Retrieves data for all customers associated with the target systems.

WITH filtered_systems AS (
    SELECT 
        "Record ID",
        "System Name",
        "Asset Portfolio - Customer",
        "Installation Address Line 1",
        "Installation Address Line 2", -- Assuming this is the correct column name for line 2
        "Installation Address City",
        "Installation Address State",
        "Installation Address Zip Code",
        "Installation Address Country", -- Using the installation country
        "Multi Signer",
        "Status"
    FROM 
        sfdc_staging.vw_system__c
    WHERE 
        "Asset Portfolio - Customer" IN :portfolio_names
        AND "Status" NOT IN ('Cancelled', 'Terminated')
)
SELECT
    s."Record ID" AS "System",
    s."System Name",
    s."Asset Portfolio - Customer",
    cn."First Name",
    cn."Last Name",
    c."Suffix",
    c."Birthdate",
    cn."Phone",
    c."Work Phone",
    c."Home Phone",
    c."Preferred Phone Type",
    CASE 
        -- For Primary contact, prefer the system's installation address, fall back to contact's mailing street.
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address Line 1", c."Mailing Street") 
        ELSE c."Mailing Street" 
    END AS "Mailing Street",
    CASE 
        -- For Primary contact, use system's line 2. For others, there is no line 2 from vw_contact.
        WHEN cn."Role" = 'Primary' THEN s."Installation Address Line 2"
        ELSE NULL -- vw_contact does not have a standard "Mailing Street 2"
    END AS "Mailing Street 2",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address City", c."Mailing City") 
        ELSE c."Mailing City" 
    END AS "Mailing City",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address State", c."Mailing State/Province") 
        ELSE c."Mailing State/Province" 
    END AS "Mailing State/Province",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address Zip Code", c."Mailing Zip/Postal Code") 
        ELSE c."Mailing Zip/Postal Code" 
    END AS "Mailing Zip/Postal Code",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address Country", c."Mailing Country") 
        ELSE c."Mailing Country" 
    END AS "Mailing Country",
    cn."Email",
    c."Billing Contact",
    c."Accept Electronic Communications  T&Cs",
    cn."Role",
    s."Multi Signer"
FROM 
    filtered_systems s
JOIN 
    sfdc_staging.vw_contact_system__c cn ON cn."System" = s."Record ID"
JOIN 
    sfdc_staging.vw_contact c ON cn."Contact" = c."Contact ID";

-- file: sql/product_info_query.sql
-- Description: (Optimized) Aggregates product descriptions for the specified portfolios.

WITH filtered_systems AS (
    SELECT "Record ID"
    FROM sfdc_staging.vw_system__c
    WHERE "Asset Portfolio - Customer" IN :portfolio_names
)
SELECT
    s."Record ID",
    STRING_AGG(DISTINCT qli."Description", ', ') AS "Unique Descriptions"
FROM 
    filtered_systems s
JOIN 
    sfdc_staging.vw_quote_line_item__c qli ON qli."System" = s."Record ID"
JOIN 
    sfdc_staging.vw_product2 p ON p."Product ID"::text = qli."Product"::text
WHERE 
    qli."Description" NOT LIKE '%Accessory Loan%'
GROUP BY 
    s."Record ID";

-- file: sql/latest_dunning_query.sql
-- Description: (Optimized) Retrieves the latest payment delinquency information.

WITH filtered_systems AS (
    SELECT "Record ID"
    FROM sfdc_staging.vwx_system__c
    WHERE "Asset Portfolio - Customer" IN :portfolio_names
)
SELECT DISTINCT ON (dunning."System")
    dunning."System",
    dunning."Days Past Due",
    dunning."Outstanding Balance"
FROM 
    sfdc_staging.vw_billingdunning__c dunning
JOIN 
    filtered_systems s ON dunning."System" = s."Record ID"
ORDER BY 
    dunning."System", dunning."Created Date" DESC;

Data Processing¶

Data processing is handled by: datatapes/klim/run_klim.py

Processing Steps¶

The data processing includes: 1. SQL query execution 2. Data transformation and cleaning 3. Column mapping and validation 4. Output file generation