CONCORD Datatape¶

Streamlined datatape for Concord-managed portfolios with detailed, processed customer information including payment history, system details, and financial metrics.

Overview¶

Generation Frequency: Weekly Development Status: In Progress

Key Features: - Customer details - Customer payment history tracking - Multi-system data integration

Key Stakeholders: - Treasury Reporting Team - Revenue Management - Concord

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: 225
Columns specified in output_columns config: 156
Columns found in SQL and included in output: 91
Missing columns (in config but not found in SQL): 76

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

Column Name	Required	Source Query	Source Table	Source Field	Derived
25D Tax Credit - Months	Yes	optimized_loan_query	scheduled_prepayment_transactions	25D Tax Credit - Months	Yes
25D Tax Credit - Months	Yes	optimized_loan_query	system_loans	25D Tax Credit - Months	Yes
ACH APR/Interest rate	Yes	combined_core_system	sfdc_staging.vwx_quote__c	ACH APR/Interest rate	No
Account Status Code	Yes	combined_core_system	sfdc_staging.vwx_system__c	Account Status Code	No
Actual Interest Rate	Yes	optimized_loan_query	system_loans	Actual Interest Rate	No
Annual Escalator %	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Annual Escalator %	No
Anticipated First Payment Date	Yes	combined_core_system	sfdc_staging.vw_system_project__c	Anticipated First Payment Date	Yes
Anticipated In-Service Date	Yes	combined_core_system	sfdc_staging.vw_system_project__c	Anticipated In-Service Date	Yes
Anticipated Maturity Date	Yes	combined_core_system	q, sp	Anticipated Maturity Date	Yes
Bankruptcy / Foreclosure / Write-off Status	Yes	invoices_info_optimized	sfdc_staging.vw_c2g__codainvoice__c	Bankruptcy / Foreclosure / Write-off Status	No
Battery Manufacturer	Yes	equipment	acc_battery, bs	Battery Manufacturer	Yes
Battery Model	Yes	equipment	sfdc_staging.vwx_asset	Battery Model	Yes
Battery Storage Warranty Term (years)	Yes	equipment	sfdc_staging.vwx_asset	Battery Storage Warranty Term (years)	Yes
Chase Profile ID	Yes	payment_method		Chase Profile ID	No
ConvAccruedInterestBalance	Yes	optimized_loan_query	latest_transactions	ConvAccruedInterestBalance	Yes
ConvYtdInterestPaid	Yes	combined_core_system	sfdc_staging.vwx_system__c	ConvYtdInterestPaid	No
Current 25D Tax Credit- Amounts	Yes	optimized_loan_query	sl, lpt	Current 25D Tax Credit- Amounts	Yes
Current 25D Tax Credit- Amounts	Yes	optimized_loan_query	scheduled_prepayment_transactions	Current 25D Tax Credit- Amounts	Yes
Current Balance	Yes	combined_core_system	sfdc_staging.vwx_system__c	Current Balance	No
Current Battery Loan Balance ($)	Yes	optimized_loan_query	system_loans	Current Battery Loan Balance ($)	No
Current Billing Interest Rate	Yes	optimized_loan_query	latest_transactions	Current Billing Interest Rate	Yes
Current Monthly Payment ($)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Current Monthly Payment ($)	No
Current Primary Balance ($)	Yes	optimized_loan_query	system_loans	Current Primary Balance ($)	Yes
Current Scheduled Prepayment Balance	Yes	optimized_loan_query	system_loans	Current Scheduled Prepayment Balance	No
Date of Contract or Purchase Agreement	Yes	combined_core_system	sfdc_staging.vw_system_project__c	Date of Contract or Purchase Agreement	No
Delinquency (# of days past due)	Yes	combined_core_system	sfdc_staging.vwx_system__c	Delinquency (# of days past due)	No
Delinquency Status	Yes	combined_core_system	sfdc_staging.vwx_system__c	Delinquency Status	No
Estimated Production in Year 1	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Estimated Production in Year 1	No
Expected Annual Degradation (%)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Expected Annual Degradation (%)	No
Expected Year 1 Production (kWh)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Expected Year 1 Production (kWh)	No
FICO	Yes	combined_core_system	sfdc_staging.vw_system_project__c	FICO	No
First Payment Date	Yes	combined_core_system	sfdc_staging.vw_system_project__c	First Payment Date	Yes
Guaranteed Production (kWh)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Guaranteed Production (kWh)	No
Guaranteed Production in Year 1	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Guaranteed Production in Year 1	No
In-Service Date	Yes	combined_core_system	sfdc_staging.vw_system_project__c	In-Service Date	No
Initial Term of Contract	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Initial Term of Contract	No
Installation Dealer	Yes	combined_core_system	sfdc_staging.vwx_account	Installation Dealer	No
Interest Payment Amount	Yes	optimized_loan_query	lt, rd	Interest Payment Amount	Yes
Inverter Manufacturer	Yes	equipment	acc_inverter	Inverter Manufacturer	No
Inverter Model	Yes	equipment	sfdc_staging.vwx_asset	Inverter Model	Yes
Inverter Type	Yes	equipment	sfdc_staging.vwx_asset	Inverter Type	Yes
Inverter Warranty Term (years)	Yes	equipment	sfdc_staging.vwx_asset	Inverter Warranty Term (years)	Yes
Last Payment Amount	Yes	optimized_payments		Last Payment Amount	No
Last Payment Date	Yes	optimized_payments		Last Payment Date	No
Last Reamortization Date	Yes	optimized_loan_query	repayment_data	Last Reamortization Date	No
Last Service Period Billed	Yes	service_period_info_optimized	sfdc_staging.vwx_service_period__c	Last Service Period Billed	No
Late Charge Amount	Yes	optimized_loan_query	latest_transactions	Late Charge Amount	No
Late Charge Percent	Yes	optimized_loan_query	latest_transactions	Late Charge Percent	Yes
Late Charge Percent	Yes	optimized_loan_query	latest_transactions	Late Charge Percent	No
Latest Invoice Date	Yes	invoices_info_optimized		Latest Invoice Date	Yes
Latest Invoice Date	Yes	invoices_info_optimized	i, s	Latest Invoice Date	Yes
Module Manufacturer	Yes	equipment	acc_module	Module Manufacturer	No
Module Model	Yes	equipment	sfdc_staging.vwx_asset	Module Model	Yes
Monthly Payment (Original)	Yes	combined_core_system	aa, q	Monthly Payment (Original)	Yes
Next Payment Date	Yes	combined_core_system	sfdc_staging.vwx_system__c	Next Payment Date	Yes
Non-ACH APR/Interest rate	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Non-ACH APR/Interest rate	No
Number of Payments Made	Yes	optimized_payments	sfdc_staging.vw_customer_payment__c	Number of Payments Made	Yes
Original 25D Tax Credit	Yes	optimized_loan_query	system_loans	Original 25D Tax Credit	Yes
Original 25D Tax Credit	Yes	optimized_loan_query	scheduled_prepayment_transactions	Original 25D Tax Credit	Yes
Original Battery Loan Balance	Yes	optimized_loan_query	system_loans	Original Battery Loan Balance	No
Original Loan Balance ($)	Yes	optimized_loan_query	system_loans	Original Loan Balance ($)	No
Original Loan Balance ($)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Original Loan Balance ($)	Yes
Original Primary Balance ($)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Original Primary Balance ($)	No
Original Primary Balance ($)	Yes	optimized_loan_query	system_loans	Original Primary Balance ($)	Yes
Original Scheduled Prepayment Balance ($)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Original Scheduled Prepayment Balance ($)	No
Original Scheduled Prepayment Balance ($)	Yes	optimized_loan_query	system_loans	Original Scheduled Prepayment Balance ($)	No
Outstanding AR Balance	Yes	combined_core_system	sfdc_staging.vwx_system__c	Outstanding AR Balance	No
Paper Bill (Y/N)	Yes	combined_core_system	sfdc_staging.vwx_system__c	Paper Bill (Y/N)	Yes
Past Due Balance	Yes	combined_core_system	sfdc_staging.vwx_system__c	Past Due Balance	Yes
Past Due Interest Amount	Yes	optimized_loan_query	sl, rd	Past Due Interest Amount	Yes
Past Due Principal Amount	Yes	optimized_loan_query	sl, rd	Past Due Principal Amount	Yes
Payment Election	Yes	payment_method		Payment Election	No
Payment Frequency	Yes	invoices_info_optimized	sfdc_staging.vw_c2g__codainvoice__c	Payment Frequency	No
Payment Type	Yes	payment_method		Payment Type	No
Primary Customer Account ID	Yes	combined_core_system	sfdc_staging.vwx_system__c	Primary Customer Account ID	No
Principal Payment Amount	Yes	optimized_loan_query	lt, rd	Principal Payment Amount	Yes
Production Degradation Factor	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Production Degradation Factor	No
RHS Contract Sale	Yes	combined_core_system	sfdc_staging.vwx_quote__c	RHS Contract Sale	No
Remaining Term (months)	Yes	combined_core_system	q, sp	Remaining Term (months)	Yes
Scheduled Prepayment Deadline	Yes	optimized_loan_query	system_loans	Scheduled Prepayment Deadline	No
Solar Rate (Current)	Yes	combined_core_system	sfdc_staging.vwx_quote__c	Solar Rate (Current)	No
Solar Rate (Original)	Yes	combined_core_system	aa, q	Solar Rate (Original)	Yes
System Name	Yes	payment_method		System Name	No
System Size	Yes	combined_core_system	sfdc_staging.vw_system_project__c	System Size	Yes
Total Battery Capacity	Yes	equipment	battery_storage	Total Battery Capacity	Yes
Total Battery Capacity	Yes	equipment	a, me	Total Battery Capacity	Yes
Total Battery Count	Yes	equipment	battery_storage	Total Battery Count	Yes
Total Battery Count	Yes	equipment	sfdc_staging.vwx_asset	Total Battery Count	Yes
Total Late Charges Due	Yes	optimized_loan_query	latest_transactions	Total Late Charges Due	No
Total Late Charges Due	Yes	optimized_loan_query	latest_transactions	Total Late Charges Due	No
Utility	Yes	combined_core_system	sfdc_staging.vwx_account	Utility	No

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
service_period_info_optimized	database	service_period_info_optimized.sql	SQL query for service_period_info_optimized data
invoices_info_optimized	database	invoices_info_optimized.sql	SQL query for invoices_info_optimized data
optimized_payments	database	optimized_payments.sql	SQL query for optimized_payments data
optimized_loan_query	database	optimized_loan_query.sql	SQL query for optimized_loan_query data
payment_method	database	payment_method.sql	SQL query for payment_method data
customer_data	database	customer_data.sql	SQL query for customer_data data
combined_core_system	database	combined_core_system.sql	SQL query for combined_core_system data
equipment	database	equipment.sql	SQL query for equipment data

Configuration¶

Portfolio Groups¶

concord_portfolios: - Sunnova ABS Holdings XIV LLC - Sunnova Asset Portfolio 7 Holdings LLC - Sunnova TEP Developer LLC - Sunnova TEP I Developer LLC - Sunnova Lease Vehicle 3-HI LLC - Sunnova RAYS I Holdings LLC - Sunnova Sol II Holdings LLC - Sunnova Sol III Holdings LLC - Sunnova Sol IV Holdings LLC - Sunnova Sol V Holdings LLC - Sunnova Sol VI Holdings LLC - Sunnova Sol IX Holdings LLC

Database Configuration¶

dbcreds_environment: fusionods

Paths¶

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

SQL Queries¶

service_period_info_optimizedinvoices_info_optimizedoptimized_paymentsoptimized_loan_querypayment_methodcustomer_datacombined_core_systemequipment

-- service_period_info_optimized.sql
WITH latest_service_periods AS (
    SELECT 
        s."Asset Portfolio - Customer",
        s."System Name",
        sp."Service Period ID" as "Last Service Period Billed",
        ROW_NUMBER() OVER (
            PARTITION BY sp."System" 
            ORDER BY sp."Start Date" DESC, sp."Last Modified Date" DESC
        ) as rn
    FROM sfdc_staging.vwx_service_period__c sp
    JOIN sfdc_staging.vwx_system__c s ON s."Record ID" = sp."System"
    WHERE s."Asset Portfolio - Partner" IN :portfolio_names
        AND s."Status" IN ('InService', 'Under Construction')
        AND sp."System" IS NOT NULL
)
SELECT 
    "Asset Portfolio - Customer",
    "System Name",
    "Last Service Period Billed"
FROM latest_service_periods
WHERE rn = 1
ORDER BY "System Name";

-- invoices_info_optimized.sql
WITH ranked_invoices AS (
    SELECT 
        i."System" AS "System ID",
        s."System Name",
        s."Asset Portfolio - Customer",
        s."Asset Portfolio - Partner",
        COALESCE(i."Invoice Date", s."Latest Invoice Date") AS "Latest Invoice Date",
        i."Interval" AS "Payment Frequency",
        i."Write-Off Status" AS "Bankruptcy / Foreclosure / Write-off Status",
        ROW_NUMBER() OVER (PARTITION BY i."System" ORDER BY i."Invoice Date" DESC NULLS LAST) AS rn
    FROM sfdc_staging.vw_c2g__codainvoice__c i
    JOIN sfdc_staging.vwx_system__c s ON s."Record ID" = i."System"
    WHERE s."Asset Portfolio - Partner" IN :portfolio_names
        AND s."Status" IN ('InService', 'Under Construction')
)
SELECT 
    "System ID",
    "System Name",
    "Asset Portfolio - Customer",
    "Asset Portfolio - Partner",
    "Latest Invoice Date"::DATE AS "Latest Invoice Date",
    "Payment Frequency",
    "Bankruptcy / Foreclosure / Write-off Status"
FROM ranked_invoices
WHERE rn = 1
ORDER BY "System ID";

-- optimized_payments.sql
WITH payment_data AS (
    SELECT 
        s."Asset Portfolio - Customer",
        s."Asset Portfolio - Partner",
        s."System Name",
        cp."System" AS "System ID",
        cp."Payment Date",
        cp."Billing Contact",
        cp."Customer Payment ID",
        cp."Amount",
        cp."Status",
        cp."Created By ID",
        cp."Record Type Name",
        cp."Payment Method",
        -- Use window functions for aggregation and ranking
        COUNT(*) OVER (PARTITION BY cp."System") AS "Number of Payments Made",
        ROW_NUMBER() OVER (PARTITION BY cp."System" ORDER BY cp."Payment Date" DESC) AS rn
    FROM sfdc_staging.vw_customer_payment__c cp
    JOIN sfdc_staging.vwx_system__c s ON cp."System" = s."Record ID"
    WHERE 
        s."Asset Portfolio - Partner" IN :portfolio_names
        AND s."Status" IN ('InService', 'Under Construction')
        AND cp."Status" = 'Successful'
        AND cp."Record Type Name" NOT IN ('Advance_Payment_Refund', 'Downpayment_Refund', 'Refund')
        AND DATE_TRUNC('month', cp."Payment Date") <= CURRENT_DATE
)
SELECT 
    "System Name",
    "Asset Portfolio - Customer",
    "Asset Portfolio - Partner",
    "Amount" AS "Last Payment Amount",
    "Payment Date" AS "Last Payment Date",
    "Status",
    "Record Type Name",
    "Number of Payments Made",
    "Billing Contact",
    "Customer Payment ID",
    "Payment Method",
    rn AS "Rank"
FROM payment_data
WHERE rn = 1
ORDER BY "System ID";

-- optimized_loan_query.sql
WITH system_loans AS (
    SELECT 
        s."Record ID" AS system_id,
        s."System Name",
        l."Record ID" AS loan_id,
        l."Contract Type",
        l."Loan Type",
        l."Maturity Date",
        l."Interest Rate",
        l."Loan Amount" AS original_amount,
        l."Principal/Advance Remaining" AS current_balance,
        l."Contract Finish Date(30% loan)" AS prepayment_deadline,
        l."Days Past Due",
        -- Categorize loan amounts in main query
        CASE WHEN l."Loan Type" = 'Scheduled Prepayment' 
             THEN l."Loan Amount" ELSE 0 END AS original_prepayment,
        CASE WHEN l."Loan Type" = 'Scheduled Prepayment' 
             THEN l."Principal/Advance Remaining" ELSE 0 END AS current_prepayment,
        CASE WHEN l."Contract Type" IN ('Battery', 'Battery Loan') 
             THEN l."Loan Amount" ELSE 0 END AS original_battery,
        CASE WHEN l."Contract Type" IN ('Battery', 'Battery Loan') 
             THEN l."Principal/Advance Remaining" ELSE 0 END AS current_battery
    FROM sfdc_staging.vwx_system__c s
    JOIN sfdc_staging.vwx_loan__loan_account__c l ON l."System" = s."Record ID"
    WHERE s."Asset Portfolio - Partner" IN :portfolio_names
        AND s."Status" NOT IN ('Cancelled', 'Terminated')
        AND s."Contract Type" IN ('EZ-Own', 'Loan', 'EZ-Own Storage', 
                                  'Battery Loan', 'Loan Storage', 'Accessory Loan')
        AND l."Status" LIKE 'Active%'
),
scheduled_prepayment_transactions AS (
    SELECT 
        sl.loan_id,
        -- Sum of transactions for scheduled prepayment loans
        SUM(CASE 
            WHEN sl."Loan Type" = 'Scheduled Prepayment' AND NOT lpt."Reversed"
            THEN lpt."Transaction Amount" 
            ELSE 0 
        END) AS "Current 25D Tax Credit- Amounts",
        -- Original loan amount for scheduled prepayment
        MAX(CASE 
            WHEN sl."Loan Type" = 'Scheduled Prepayment' 
            THEN sl.original_amount 
            ELSE 0 
        END) AS "Original 25D Tax Credit",
        -- Fixed 18 months for scheduled prepayment
        MAX(CASE 
            WHEN sl."Loan Type" = 'Scheduled Prepayment' 
            THEN 18 
            ELSE 0 
        END) AS "25D Tax Credit - Months"
    FROM system_loans sl
    LEFT JOIN sfdc_staging.vwx_loan__loan_payment_transaction__c lpt 
        ON lpt."Loan Account" = sl.loan_id
    GROUP BY sl.loan_id
),
-- Get all repayment schedule data in one pass
repayment_data AS (
    SELECT 
        rs."Loan Account",
        -- Last reamortization date
        MAX(rs."Last Modified Date") AS last_reamortization_date,
        -- Current month payments
        SUM(CASE 
            WHEN date_trunc('month', rs."Due Date") = date_trunc('month', now() AT TIME ZONE 'America/Chicago')
            THEN rs."Due Principal" 
            ELSE 0 
        END) AS principal_payment_amount,
        SUM(CASE 
            WHEN date_trunc('month', rs."Due Date") = date_trunc('month', now() AT TIME ZONE 'America/Chicago')
            THEN rs."Due Interest" 
            ELSE 0 
        END) AS interest_payment_amount,
        -- Past due amounts (using Days Past Due from loan account)
        SUM(CASE 
            WHEN rs."Due Date" < CURRENT_DATE 
            THEN rs."Due Principal" 
            ELSE 0 
        END) AS past_due_principal,
        SUM(CASE 
            WHEN rs."Due Date" < CURRENT_DATE 
            THEN rs."Due Interest" 
            ELSE 0 
        END) AS past_due_interest
    FROM sfdc_staging.vw_loan__repayment_schedule__c rs
    JOIN system_loans sl ON rs."Loan Account" = sl.loan_id
    GROUP BY rs."Loan Account"
),
-- Get latest transaction using window function
latest_transactions AS (
    SELECT 
        lt."Loan Account",
        lt."Transaction Date",
        lt."Balance",
        lt."Principal",
        lt."Interest",
        lt."Interest Rate",
        lt."Late Charge (Principal)",
        (lt."Late Charge (Principal)" / NULLIF(lt."Transaction Amount", 0)) * 100 AS "Late Charge Percent",
        lt."Late Charge (Principal)" as "Total Late Charges Due",
        ROW_NUMBER() OVER (PARTITION BY lt."Loan Account" ORDER BY lt."Transaction Date" DESC, lt."Transaction Time" DESC) as rn
    FROM sfdc_staging.vwx_loan__loan_payment_transaction__c lt
    JOIN system_loans sl ON lt."Loan Account" = sl.loan_id
    WHERE lt."Transaction Date" <= CURRENT_DATE
        AND NOT lt."Reversed"
)

SELECT 
    sl."System Name",
    sl.loan_id AS "Loan Account ID",
    COALESCE(lt."Principal", rd.principal_payment_amount) AS "Principal Payment Amount",
    COALESCE(lt."Interest", rd.interest_payment_amount) AS "Interest Payment Amount",
    sl."Interest Rate" AS "Actual Interest Rate",
    ROUND((lt."Interest Rate" / 100.0)::numeric, 4) AS "Current Billing Interest Rate",
    CASE 
        WHEN sl."Days Past Due" > 0 THEN COALESCE(rd.past_due_principal, 0)
        ELSE 0
    END AS "Past Due Principal Amount",
    CASE 
        WHEN sl."Days Past Due" > 0 THEN COALESCE(rd.past_due_interest, 0)
        ELSE 0
    END AS "Past Due Interest Amount",
    sl."Maturity Date",
    COALESCE(spt."Current 25D Tax Credit- Amounts", 0) AS "Current 25D Tax Credit- Amounts",
COALESCE(spt."Original 25D Tax Credit", 0) AS "Original 25D Tax Credit",
COALESCE(spt."25D Tax Credit - Months", 0) AS "25D Tax Credit - Months",
    sl.original_prepayment AS "Original Scheduled Prepayment Balance ($)",
    (sl.original_amount - sl.original_prepayment - sl.original_battery) AS "Original Primary Balance ($)",
    sl.original_battery AS "Original Battery Loan Balance",
    sl.original_amount AS "Original Loan Balance ($)",
    sl.current_prepayment AS "Current Scheduled Prepayment Balance",
    (sl.current_balance - sl.current_prepayment - sl.current_battery) AS "Current Primary Balance ($)",
    sl.current_battery AS "Current Battery Loan Balance ($)",
    sl.prepayment_deadline AS "Scheduled Prepayment Deadline",
    lt."Late Charge (Principal)" AS "Late Charge Amount",
    lt."Late Charge Percent" AS "Late Charge Percent",
    lt."Total Late Charges Due" AS "Total Late Charges Due",
    rd.last_reamortization_date AS "Last Reamortization Date",
    ROUND(
        (lt."Balance" * (lt."Interest Rate" / 100.0 / 360) * 
        (CURRENT_DATE - lt."Transaction Date"::date))::numeric, 2
    ) AS "ConvAccruedInterestBalance"
FROM system_loans sl
LEFT JOIN repayment_data rd ON rd."Loan Account" = sl.loan_id
LEFT JOIN scheduled_prepayment_transactions spt ON spt.loan_id = sl.loan_id
LEFT JOIN latest_transactions lt ON lt."Loan Account" = sl.loan_id AND lt.rn = 1
ORDER BY sl."System Name";

WITH ranked_payment_methods AS (
    SELECT
        filter_sys."Record ID" AS system_id,
        filter_sys."System Name" AS system_name,
        CASE
            WHEN ach."Record Type ID" = '012E0000000oQVLIA2' THEN 'ACH_Payment_Method'
            WHEN ach."Record Type ID" = '012E0000000oQVQIA2' THEN 'Non_ACH_Payment_Method'
            ELSE ach."Record Type ID"
        END AS payment_election,
        CASE
            WHEN ach."AutoPay"  THEN TRUE
            ELSE FALSE
        END AS OnSpCpa,
        CASE
            WHEN  ach."AutoPay"  THEN FALSE
            ELSE TRUE
        END AS OffSpCpa,
        ach."Account Type" AS payment_type,
        ach."Chase Profile Id" AS chase_profile_id,
        ROW_NUMBER() OVER (
            PARTITION BY filter_sys."Record ID" 
            ORDER BY 
                CASE WHEN ach."Account Type" IS NOT NULL THEN 1 ELSE 2 END,
                CASE
                    WHEN ach."Record Type ID" = '012E0000000oQVLIA2' THEN 1
                    WHEN ach."Record Type ID" = '012E0000000oQVQIA2' THEN 2
                    ELSE 3
                END
        ) AS rn
    FROM sfdc_staging.vwx_system__c filter_sys
    LEFT JOIN sfdc_staging.vwx_ach_payment_method__c ach
        ON filter_sys."Billing Contact" = ach."Billing Contact ID18"
    WHERE filter_sys."Asset Portfolio - Partner" in :portfolio_names
    AND filter_sys."Status" IN ('InService', 'Under Construction')
)
SELECT 
    system_id AS "System ID",
    system_name AS "System Name",
    payment_election AS "Payment Election",
    OnSpCpa AS "OnSpCpa",
    OffSpCpa AS "OffSpCpa",
    payment_type AS "Payment Type",
    chase_profile_id AS "Chase Profile ID"
FROM ranked_payment_methods
WHERE rn = 1 OR (rn IS NULL AND payment_election IS NULL)
ORDER BY "System ID";

-- Customer Data Query
-- This query retrieves data for customers associated with systems
-- Customer type determination will be handled in Python

SELECT
    s."System Name",
    s."Asset Portfolio - Customer",
    cn."First Name",
    cn."Last Name",
    c."Birthdate",
    cn."Phone",
    c."Work Phone",
    c."Preferred Phone Type",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address Line 1", c."Mailing Street") 
        ELSE c."Mailing Street" 
    END AS "Mailing Street",
    CASE 
        WHEN cn."Role" = 'Primary' THEN COALESCE(s."Installation Address Line 2", c."Mailing Address") 
        ELSE c."Mailing Address" 
    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",
    cn."Role",
    s."Multi Signer"
FROM 
    sfdc_staging.vw_system__c 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"
WHERE 
    s."Asset Portfolio - Partner" in :portfolio_names
    AND s."Status" NOT IN ('Cancelled', 'Terminated')

-- combined_core_system.sql
SELECT 
    -- Portfolio and System identification
    s."Asset Portfolio - Partner",
    s."Asset Portfolio - Customer",
    s."System Name",
    s."Status" as "Account Status Code",

    -- Contract and dates
    sp."Contract Stage Date" as "Date of Contract or Purchase Agreement",
    sp."PTO Received Date",
    sp."InService Date" as "In-Service Date",
    sp."InService Date" + interval '1 month' as "First Payment Date",
    q."Contract Type",

    -- Payment information
    q."Recurring Payment" as "Current Monthly Payment ($)",
    q."Solar Rate (2)" as "Solar Rate (Current)",
    q."Payment Escalator" as "Annual Escalator %",

    -- Production information
    q."Expected Production - Orig Simulation" as "Estimated Production in Year 1",
    q."Expected Production - Guaranteed" as "Guaranteed Production in Year 1",

    -- System details
    sp."Stage",
    sp."NTP Stage Date",
    q."Term (months)" as "Initial Term of Contract",
    COALESCE(sp."System Size", 0) AS "System Size",

    -- Financial information
    q."RIS SA Scheduled Prepayment" as "Original Scheduled Prepayment Balance ($)",
    q."RIS SA Starting Balance" as "Original Primary Balance ($)",
    COALESCE(q."RIS SA Scheduled Prepayment", 0) + COALESCE(q."RIS SA Starting Balance", 0) as "Original Loan Balance ($)",
    ipa."Account Name" as "Installation Dealer",
    ua."Account Name" as "Utility",
    q."RIS RHIA Downpayment" as "Downpayment",
    s."Active Billing Status",
    s."Active Billing Status Detail",

    -- Additional fields from system_info
    s."Customer Account ID" as "Primary Customer Account ID",
    s."Collections" as "Delinquency Status",
    sp."FICO High" as "FICO",
    sp."Committed Capital",
    case
        when s."Days Past Due" > 0 then s."Outstanding Balance"
        else 0
    end as "Past Due Balance",
    s."Outstanding Balance" as "Outstanding AR Balance",
    case
        when s."Days Past Due" > 0 then s."Oldest Outstanding Due Date"
        else s."Next Payment Date"
    end as "Next Payment Date",
    sp."Cancellation Reason",
    s."Current Principal Balance" as "Current Balance",
    s."Billing Contact",
    s."Interest Paid YTD" as "ConvYtdInterestPaid",
    s."Days Past Due" as "Delinquency (# of days past due)",
    q."Annual Degradation" as "Production Degradation Factor",
    q."Annual Degradation" as "Expected Annual Degradation (%)",
    q."Expected Production - Orig Simulation" as "Expected Year 1 Production (kWh)",
    q."Expected Production - Guaranteed" as "Guaranteed Production (kWh)",
    q."RIS APR No ACH w PPMT" as "Non-ACH APR/Interest rate",
    q."RIS RHIA Contract Price" as "RHS Contract Sale",
    q."RIS APR ACH w PPMT" as "ACH APR/Interest rate",
    Case WHEN s."Paper Bill?" then 'Y' ELSE 'N' END as "Paper Bill (Y/N)",

    -- From annual_attributes
    aa."Contract Prepayment Amount",
    coalesce(aa."Customer Payment Amount", aa."Monthly Payment", q."Recurring Payment") as "Monthly Payment (Original)",
    coalesce(aa."Original Solar Rate (ACH)", q."Solar Rate (1)") as "Solar Rate (Original)",

    -- Calculated fields
    CASE 
        WHEN sp."InService Date" IS NOT NULL THEN sp."InService Date"
        ELSE 
            CASE sp."Stage"
                WHEN 'Contract' THEN CURRENT_DATE + INTERVAL '100 days'
                WHEN 'NTP' THEN CURRENT_DATE + INTERVAL '95 days'
                WHEN 'Substantial' THEN CURRENT_DATE + INTERVAL '40 days'
                WHEN 'Final' THEN CURRENT_DATE + INTERVAL '10 days'
                WHEN 'Completed' THEN CURRENT_DATE - INTERVAL '10 days'
                ELSE NULL
            END
    END AS "Anticipated In-Service Date",

    DATE_TRUNC('month', 
        CASE 
            WHEN sp."InService Date" IS NOT NULL THEN sp."InService Date"
            ELSE 
                CASE sp."Stage"
                    WHEN 'Contract' THEN CURRENT_DATE + INTERVAL '100 days'
                    WHEN 'NTP' THEN CURRENT_DATE + INTERVAL '95 days'
                    WHEN 'Substantial' THEN CURRENT_DATE + INTERVAL '40 days'
                    WHEN 'Final' THEN CURRENT_DATE + INTERVAL '10 days'
                    WHEN 'Completed' THEN CURRENT_DATE - INTERVAL '10 days'
                    ELSE CURRENT_DATE
                END
        END
    ) + INTERVAL '1 month' AS "Anticipated First Payment Date",

    CASE 
        WHEN q."Term (months)" IS NOT NULL THEN
            (CASE 
                WHEN sp."InService Date" IS NOT NULL THEN sp."InService Date"
                ELSE 
                    CASE sp."Stage"
                        WHEN 'Contract' THEN CURRENT_DATE + INTERVAL '100 days'
                        WHEN 'NTP' THEN CURRENT_DATE + INTERVAL '95 days'
                        WHEN 'Substantial' THEN CURRENT_DATE + INTERVAL '40 days'
                        WHEN 'Final' THEN CURRENT_DATE + INTERVAL '10 days'
                        WHEN 'Completed' THEN CURRENT_DATE - INTERVAL '10 days'
                        ELSE NULL
                    END
            END) + (q."Term (months)" * INTERVAL '1 month')
        ELSE NULL
    END AS "Anticipated Maturity Date",

    CASE
        WHEN q."Term (months)" IS NOT NULL THEN
            CASE 
                WHEN sp."InService Date" IS NOT NULL THEN 
                    q."Term (months)" - EXTRACT(MONTH FROM AGE(CURRENT_DATE, sp."InService Date"))
                    - (EXTRACT(YEAR FROM AGE(CURRENT_DATE, sp."InService Date")) * 12)
                ELSE q."Term (months)"
            END
        ELSE NULL
    END AS "Remaining Term (months)"

FROM sfdc_staging.vwx_system__c s
JOIN sfdc_staging.vw_system_project__c sp ON sp."System" = s."Record ID"
left JOIN sfdc_staging.vwx_quote__c q ON q."Record ID" = sp."Quote" AND q."Status" = 'Executed'
-- Installation Dealer Account
LEFT JOIN sfdc_staging.vwx_account ipa ON ipa."Account ID (2)" = q."Installation Partner Account"
-- Utility Account
LEFT JOIN sfdc_staging.vwx_account ua ON ua."Account ID (2)" = q."Utility Account"
-- Annual attributes
LEFT JOIN sfdc_staging.vw_annual_attributes__c aa 
    ON aa."Quote" = q."Record ID" 
    AND aa."Year (2)" = 1
WHERE s."Asset Portfolio - Partner" IN :portfolio_names
    AND s."Status" IN ('InService', 'Under Construction');

-- equipment.sql
WITH 
-- Get manufacturer and model data
asset_data AS (
    SELECT 
        a."System",
        -- Module 
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIksAAG' AND rn = 1 THEN a."Account ID" END) AS "Module Account ID",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIksAAG' AND rn = 1 THEN a."Model Number" END) AS "Module Model",
        -- Inverter
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkqAAG' AND rn = 1 THEN a."Account ID" END) AS "Inverter Account ID",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkqAAG' AND rn = 1 THEN a."Model Number" END) AS "Inverter Model",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkqAAG' AND rn = 1 THEN a."Material Warranty Length Years" END) AS "Inverter Warranty Term (years)",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkqAAG' AND rn = 1 THEN a."Product Description" END) AS "Inverter Type",
        -- Battery
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkmAAG' AND rn = 1 THEN a."Account ID" END) AS "Battery Account ID",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkmAAG' AND rn = 1 THEN a."Model Number" END) AS "Battery Model",
        MAX(CASE WHEN a."Record Type ID" = '01244000000DIkmAAG' AND rn = 1 THEN a."Material Warranty Length Years" END) AS "Battery Storage Warranty Term (years)"
    FROM (
        SELECT 
            a.*,
            row_number() OVER (PARTITION BY a."System", a."Record Type ID" ORDER BY a."Created Date" DESC) AS rn
        FROM sfdc_staging.vwx_asset a
        WHERE 
            a."Record Type ID" IN (
                '01244000000DIkmAAG', -- Battery
                '01244000000DIksAAG', -- Module
                '01244000000DIkqAAG'  -- Inverter
            )
    ) a
    GROUP BY a."System"
),
-- Get battery quantity and capacity from master equipment
battery_storage AS (
    SELECT 
        a."System",
        me."Manufacturer Name" AS "Battery Manufacturer_ME",
        SUM(a."Quantity") AS "Total Battery Count",
        SUM(a."Quantity" * me."Battery Max Capacity") AS "Total Battery Capacity"
    FROM sfdc_staging.vw_master_equipment__c me
    LEFT JOIN sfdc_staging.vwx_asset a ON a."Master Equipment" = me."Record ID"
    WHERE me."Type" = 'Battery' 
        AND a."Operational End Date" IS NULL 
        AND a."Status" <> 'Archived'
    GROUP BY a."System", me."Manufacturer Name"
)
SELECT 
    s."Asset Portfolio - Partner",
    s."Asset Portfolio - Customer",
    s."System Name",
    sp."Customer Count Date",
    -- Module information
    acc_module."Account Name" AS "Module Manufacturer",
    ad."Module Model",
    -- Inverter information
    acc_inverter."Account Name" AS "Inverter Manufacturer",
    ad."Inverter Model",
    ad."Inverter Warranty Term (years)",
    ad."Inverter Type",
    -- Battery information (prefer manufacturer from asset data, fallback to master equipment)
    COALESCE(acc_battery."Account Name", bs."Battery Manufacturer_ME") AS "Battery Manufacturer",
    ad."Battery Model",
    ad."Battery Storage Warranty Term (years)",
    -- Battery quantity and capacity from master equipment
    COALESCE(bs."Total Battery Count", 0) AS "Total Battery Count",
    COALESCE(bs."Total Battery Capacity", 0) AS "Total Battery Capacity"
FROM sfdc_staging.vwx_system__c s
JOIN sfdc_staging.vwx_system_project__c sp ON sp."System" = s."Record ID"
-- Left join to asset data for manufacturer info
LEFT JOIN asset_data ad ON s."Record ID" = ad."System"
-- Left joins to account tables for manufacturer names
LEFT JOIN sfdc_staging.vwx_account acc_module ON acc_module."Account ID (2)" = ad."Module Account ID"
LEFT JOIN sfdc_staging.vwx_account acc_inverter ON acc_inverter."Account ID (2)" = ad."Inverter Account ID"
LEFT JOIN sfdc_staging.vwx_account acc_battery ON acc_battery."Account ID (2)" = ad."Battery Account ID"
-- Left join to battery storage for quantity and capacity
LEFT JOIN battery_storage bs ON s."Record ID" = bs."System"
WHERE s."Asset Portfolio - Partner" IN :portfolio_names 
    AND s."Status" IN ('InService', 'Under Construction');

Data Processing¶

Data processing is handled by: datatapes/concord/run_concord_datatape.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

Python Modules¶

This datatape includes additional Python modules with business logic:

concord_output_profiling¶

Concord Datatape Data Coverage & Quality Report - Optimized

Run Guide:¶

Update the CSV_PATH in the CONFIG section below to point to your data file
Run the notebook with: marimo run
The notebook will cache expensive computations for responsive UI interactions
All semantic groups and thresholds are configurable in the CONFIG section

Requirements: - marimo >= 0.13.15 - duckdb - altair - matplotlib - polars (optional, for future optimization)

`_(mo)` ¶

Display final summary and export options.

Source code in datatapes/concord/concord_output_profiling.py

@app.cell
def _(mo):
    """Display final summary and export options."""

    def create_summary_section():
        """Create the final summary section with key insights."""
        return mo.vstack(
            [
                mo.md("## 📋 Summary & Insights"),
                mo.md("""
            ### Key Features of this Report:

            1. **Performance Optimized**: All expensive computations are cached in DuckDB tables
            2. **Interactive Filtering**: Adjust thresholds without re-computing statistics  
            3. **Semantic Grouping**: Columns are organized into logical business categories
            4. **Visual Analytics**: Missing data patterns visualized through interactive heatmaps

            ### Next Steps:

            - Review columns with high missing percentages in critical semantic groups
            - Focus on 'Billing & Customer Information' and 'Financial Details' for data quality improvements
            - Use the heatmap to identify patterns of missingness across categories
            - Empty columns are displayed separately in the connection status for easy reference
            """),
                mo.callout(
                    mo.md(
                        "💾 **Export Options**: Use browser print/save functionality to capture visualizations"
                    ),
                    kind="info",
                ),
            ]
        )

    summary_section = create_summary_section()
    summary_section
    return

concord_explorer_nb¶

yaml_config_extension¶

YAML Config Extension Module

This module extends the EnhancedYamlConfig class with methods for handling customer processing configuration.

`get_customer_processing_config(self)` ¶

Get customer processing configuration.

Returns¶

dict Customer processing configuration

Source code in datatapes/concord/yaml_config_extension.py

def get_customer_processing_config(self):
    """
    Get customer processing configuration.

    Returns
    -------
    dict
        Customer processing configuration
    """
    return self.get_value("customer_processing", {})

`get_customer_type_mappings(self)` ¶

Get column mappings for each customer type.

Returns¶

dict Dictionary mapping customer types to their column rename mappings

Source code in datatapes/concord/yaml_config_extension.py

def get_customer_type_mappings(self):
    """
    Get column mappings for each customer type.

    Returns
    -------
    dict
        Dictionary mapping customer types to their column rename mappings
    """
    config = self.get_customer_processing_config()
    type_mappings = {}

    if "type_mappings" in config:
        for ctype, type_config in config["type_mappings"].items():
            if "columns" in type_config:
                type_mappings[ctype] = type_config["columns"]

    return type_mappings

CONCORD Datatape¶

Overview¶

Output Columns¶

Column Analysis Summary¶

Data Sources¶

Configuration¶

Portfolio Groups¶

Database Configuration¶

Paths¶

SQL Queries¶

Data Processing¶

Processing Steps¶

Python Modules¶

concord_output_profiling¶

Run Guide:¶

_(mo) ¶

concord_explorer_nb¶

yaml_config_extension¶

get_customer_processing_config(self) ¶

Returns¶

get_customer_type_mappings(self) ¶

Returns¶

concord_cache_explorer¶

`_(mo)` ¶

`get_customer_processing_config(self)` ¶

`get_customer_type_mappings(self)` ¶