Skip to content

SLA Datatape

Standardized datatape for SLA portfolio management including loan details, payment history, battery data, and operational metrics for solar loan assets tracking.

Overview

Generation Frequency: Weekly Development Status: Stable

Key Features: - SLA performance tracking - Battery data integration - Operational metrics - Service compliance monitoring

Key Stakeholders: - Treasury Team - Portfolio Management - Operations Team - Financial Reporting

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: 79
  • Columns specified in output_columns config: 46
  • Columns found in SQL and included in output: 25
  • Missing columns (in config but not found in SQL): 25

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

Column Name Required Source Query Source Table Source Field Derived Transformations
Non-prepayment Starting Balance Yes sla_systems sfdc_staging.vwx_quote__c Non-prepayment Starting Balance No
Payment Day Yes sla_systems sfdc_staging.vw_system__c Payment Day No
Performance Production Yes sla_systems sfdc_staging.vwx_quote__c Performance Production No
Quote: Contract Type Yes sla_systems sfdc_staging.vw_system_project__c Quote: Contract Type No
Quote: Installation State Yes sla_systems sfdc_staging.vw_system_project__c Quote: Installation State No
Quote: Partner Account Yes sla_systems sfdc_staging.vwx_account Quote: Partner Account No
Quote: Payment Escalator Yes sla_systems sfdc_staging.vwx_quote__c Quote: Payment Escalator No
Quote: RIS APR ACH w PPMT Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS APR ACH w PPMT No
Quote: RIS CoverPg Monthly Pmt With PPMT Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS CoverPg Monthly Pmt With PPMT No
Quote: RIS CoverPg Monthly Pmt Without PPMT Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS CoverPg Monthly Pmt Without PPMT No
Quote: RIS CoverPg kWh Rate With PPMT Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS CoverPg kWh Rate With PPMT No
Quote: RIS CoverPg kWh Rate Without PPMT Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS CoverPg kWh Rate Without PPMT No
Quote: RIS Program Fee Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS Program Fee No
Quote: RIS SA Scheduled Prepayment Yes sla_systems sfdc_staging.vwx_quote__c Quote: RIS SA Scheduled Prepayment No
Quote: Recurring Payment Yes sla_systems sfdc_staging.vwx_quote__c Quote: Recurring Payment No
Quote: SREC NPV Yes sla_systems sfdc_staging.vwx_quote__c Quote: SREC NPV No
Quote: SREC Owner Yes sla_systems sfdc_staging.vwx_quote__c Quote: SREC Owner No
Quote: Solar Rate Yes sla_systems sfdc_staging.vwx_quote__c Quote: Solar Rate No
Quote: Term (months) Yes sla_systems sfdc_staging.vwx_quote__c Quote: Term (months) No
Quote: Utility Account Yes sla_systems sfdc_staging.vwx_account Quote: Utility Account No
System Project: Sunnova System ID Yes sla_systems sfdc_staging.vw_system__c System Project: Sunnova System ID No
System Project: Sunnova System ID Yes monthly_payments sfdc_staging.vwx_system__c System Project: Sunnova System ID No
System Project: Sunnova System ID Yes battery_data sfdc_staging.vwx_system__c System Project: Sunnova System ID No
System Project: Sunnova System ID Yes cloudlending sfdc_staging.vw_system__c System Project: Sunnova System ID No
System Project: Sunnova System ID Yes ancillary sfdc_staging.vwx_system__c System Project: Sunnova System ID 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
ancillary database ancillary.sql SQL query for ancillary data
cloudlending database cloudlending.sql SQL query for cloudlending data
battery_data database battery_data.sql SQL query for battery_data data
monthly_payments database monthly_payments.sql SQL query for monthly_payments data
sla_systems database sla_systems.sql SQL query for sla_systems data

Configuration

Portfolio Groups

sla_portfolios: - Sunnova Asset Portfolio 7 Holdings LLC - Sunnova AP6 Warehouse II LLC - Sunnova EZ-Own Portfolio LLC - Sunnova Asset Portfolio 8 LLC - Sunnova Asset Portfolio 9 Holdings LLC - Sunnova Asset Portfolio 9 LLC

Database Configuration

  • dbcreds_environment: fusionods

Paths

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

SQL Queries

SELECT s."Asset Portfolio - Partner",
  s."Asset Portfolio - Customer",
  s."System Name" as "System Project: Sunnova System ID", -- FINAL KEY NAME
  s."Status",
  q."Quote",
  s."Contract Type", -- Keep original name if needed only for filtering here
  q."Change Order",
  qli."Quote Line Item Name",
  p."Product Name", -- Intermediate name ok for aggregation
  qli."EPC Cost" -- Intermediate name ok for aggregation
FROM sfdc_staging.vwx_system__c s
  JOIN sfdc_staging.vwx_system_project__c sp ON s."Record ID" = sp."System"
  JOIN sfdc_staging.vwx_quote__c q ON sp."Quote" = q."Record ID" AND q."Status" = 'Executed'
  JOIN sfdc_staging.vw_quote_line_item__c qli ON qli."Quote" = q."Record ID"
  JOIN sfdc_staging.vw_product2 p ON qli."Product" = p."Product ID"
    AND (
      p."Product Name" ILIKE '%Roof%'
      OR p."Product Name" ILIKE '%SMARTPWR360%'
      OR p."Product Name" ILIKE '%Main Panel Upgrade%'
      OR p."Product Name" ILIKE '%Tree Trimming%'
      OR p."Product Name" ILIKE '%Chargepoint%'
      OR p."Product Name" ILIKE '%Generator%'
    )
WHERE s."Status" IN ('Under Construction', 'InService')
  AND s."Asset Portfolio - Customer" IN :portfolio_names
  AND s."Contract Type" IN ( -- Use original name for filter if easier
    'EZ-Own',
    'Loan',
    'EZ-Own Storage',
    'Battery Loan',
    'Loan Storage',
    'Accessory Loan'
  )

SELECT
    a."CL Contract ID",
    a."Status",
    b."System Name" as "System Project: Sunnova System ID", -- FINAL KEY NAME
    a."Loan Type",
    a."Loan Amount",
    a."Loan Balance",
    a."Fees Paid",
    a."Fees Remaining",
    a."Interest Paid",
    a."Interest Accrued",
    a."Interest Remaining",
    a."Principal/Advance Paid",
    a."Principal Adjustment - Add",
    a."Principal Adjustment - Sub",
    a."Principal/Advance Remaining",
    a."Total Amount Paid"
FROM sfdc_staging.vw_loan__loan_account__c a
    -- Assuming the join key b."System ID18" correctly links to a."System"
    JOIN sfdc_staging.vw_system__c b ON a."System" = b."System ID18"
WHERE a."Loan Type" IN ('Scheduled Prepayment', 'Primary', 'Battery Loan', 'EZ Own', 'Accessory Loan')
-- Filter based on the portfolio names associated with the system 'b'
AND b."Asset Portfolio - Customer" IN :portfolio_names

SELECT
   s."System Name" as "System Project: Sunnova System ID", -- FINAL KEY NAME
   s."Asset Portfolio - Customer",
   s."Asset Portfolio - Partner",
   acc."Account Name" as "Manufacturer", -- Intermediate name ok for aggregation
   a."Model Number", -- Intermediate name ok for aggregation
   RT."Name", -- Intermediate name ok for aggregation
   a."Quantity", -- Intermediate name ok for aggregation
   a."Operational End Date" -- Keep if needed
   -- s."Record ID" -- Add if needed for joining back after aggregation
FROM sfdc_staging.vwx_system__c s
-- Ensure joins are correct for filtering and getting needed fields
JOIN sfdc_staging.vwx_system_project__c sp ON s."Record ID"::text = sp."System"::text
JOIN sfdc_staging.vwx_asset a ON a."System"::text = s."Record ID"::text
JOIN sfdc_staging.vwx_account acc ON acc."Account ID (2)"::text = a."Account ID"::text
JOIN sfdc_staging.vw_recordtype AS RT ON RT."Record Type ID" = a."Record Type ID"
WHERE a."Record Type ID" = '01244000000DIkmAAG' -- Asset Record Type for Battery? Verify.
    AND a."Operational End Date" IS NULL
    AND a."Status"::text <> 'Archived'::text
    AND s."Asset Portfolio - Customer" IN :portfolio_names -- Filter based on portfolio
    AND sp."Stage" NOT IN (
        'NTP',
        'Contract',
        'Cancellation in Progress',
        'Cancelled'
    )

SELECT b."System ID18", -- Keep if needed for join
 b."System Name" as "System Project: Sunnova System ID", -- FINAL KEY NAME
 b."Installation State", -- Keep if needed
 a."Product", -- Intermediate name ok
 -- Use DATE type directly if possible, otherwise keep TO_CHAR
 -- a."Created Date" as "Created Date", -- Prefer DATE type
 to_char(a."Created Date", 'MM/DD/YYYY') as "Created Date", -- Keep if source is not DATE
 a."Unit Price" -- Intermediate name ok
FROM sfdc_staging.vw_c2g__codainvoicelineitem__c a
JOIN sfdc_staging.vwx_system__c b ON b."System ID18" = a."System" -- Join key

WHERE a."Product" LIKE '%Monthly Service Charge%'
    AND ( a."Product" LIKE '%Loan%'
         OR a."Product" LIKE 'EZ-Own%'
         OR a."Product" LIKE '%Accessory Loan%' )
    AND b."Asset Portfolio - Customer" IN :portfolio_names -- Filter on portfolio
 -- Subquery to get the latest record per system/product

    AND (a."System",
         a."Product",
         a."Created Date") IN
        ( SELECT "System",
                 "Product",
                 MAX("Created Date") as "Created Date"
         FROM sfdc_staging.vw_c2g__codainvoicelineitem__c
         WHERE "Product" LIKE '%Monthly Service Charge%'
             AND ( "Product" LIKE '%Loan%'
                  OR "Product" LIKE 'EZ-Own%'
                  OR "Product" LIKE '%Accessory Loan%' )
         GROUP BY "System",
                  "Product" )
ORDER BY "System Project: Sunnova System ID",
         a."Product",
         a."Created Date" DESC

-- Optimized version with proper indexing

SELECT s."Asset Portfolio - Partner", -- s."Record ID" as "SystemID", -- Original - Changed Below
 s."System Name" as "System Project: Sunnova System ID", -- FINAL KEY NAME
 ua."Account Name" AS "Quote: Utility Account",
 opa."Account Name" AS "Quote: Partner Account",
 sp."Stage",
 s."Primary Customer Name",
 sp."Substantial Stage Date",
 sp."Final Stage Date",
 sp."PTO Received Date",
 sp."InService Date",
 sp."Installation State" as "Quote: Installation State",
 sp."Contract Type" as "Quote: Contract Type",
 sp."Committed Capital",
 sp."System Size",
 q."Term (months)" as "Quote: Term (months)",
 q."Recurring Payment" as "Quote: Recurring Payment",
 q."Payment Escalator" as "Quote: Payment Escalator",
 q."Expected Production - Contract" as "Performance Production", -- FINAL NAME
 q."Solar Rate (2)" as "Quote: Solar Rate",
 sp."FICO High",
 s."Payment Date" as "Payment Day", -- FINAL NAME (represents day)
 q."RIS SA Starting Balance" as "Non-prepayment Starting Balance", -- FINAL NAME
 q."RIS SA Scheduled Prepayment" as "Quote: RIS SA Scheduled Prepayment",
 q."RIS APR ACH w PPMT" as "Quote: RIS APR ACH w PPMT",
 q."RIS CoverPg Monthly Pmt With PPMT" as "Quote: RIS CoverPg Monthly Pmt With PPMT",
 q."RIS CoverPg Monthly Pmt Without PPMT" as "Quote: RIS CoverPg Monthly Pmt Without PPMT",
 q."RIS CoverPg kWh Rate With PPMT" as "Quote: RIS CoverPg kWh Rate With PPMT",
 q."RIS CoverPg kWh Rate Without PPMT" as "Quote: RIS CoverPg kWh Rate Without PPMT",
 q."RIS Program Fee" as "Quote: RIS Program Fee",
 q."SREC NPV" as "Quote: SREC NPV",
 q."SREC Owner" as "Quote: SREC Owner",
 s."Record ID", -- Keep original Record ID if needed internally or for output
 s."Asset Portfolio - Customer",
 s."Financing Company",
 ipa."Account Name" AS "Installation Partner Account", -- Keep if needed internally
 q."Expected Production - Orig Simulation" -- Keep if needed internally
FROM sfdc_staging.vw_system_project__c sp
JOIN sfdc_staging.vw_system__c s ON s."Record ID"::TEXT = sp."System"::TEXT
JOIN sfdc_staging.vwx_quote__c q ON q."System"::TEXT = s."Record ID"::TEXT
LEFT JOIN sfdc_staging.vwx_account opa ON opa."Account ID (2)"::TEXT = q."Origination Partner Account"::TEXT
LEFT JOIN sfdc_staging.vwx_account ipa ON ipa."Account ID (2)"::TEXT = q."Installation Partner Account"::TEXT
LEFT JOIN sfdc_staging.vwx_account ua ON ua."Account ID (2)"::TEXT = q."Utility Account"::TEXT
WHERE s."Asset Portfolio - Customer" IN :portfolio_names
    AND s."Financing Company" IS NULL
    AND sp."Stage" NOT IN ( 'NTP',
                            'Contract',
                            'Cancellation in Progress',
                            'Cancelled' )
    AND q."Contract Type" IN ( 'EZ-Own',
                               'Loan',
                               'EZ-Own Storage',
                               'Battery Loan',
                               'Loan Storage',
                               'Accessory Loan' )
    AND opa."Account ID (2)" <> '0014W00002efybhQAA' -- Exclude specific account

Data Processing

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

marimo_ui_example

marimo_plotting_example

documentation

sla_processing_logic

SLA DataTape Generator using NovaFDE Framework (Modernized).

add_sla_cli_args(parser: argparse.ArgumentParser)

Adds SLA-specific arguments to the CLI parser.

Source code in datatapes/sla/sla_processing_logic.py 
165
166
167
168
169
170
171
172
173
174
175
176
177
178
def add_sla_cli_args(parser: argparse.ArgumentParser):
    """Adds SLA-specific arguments to the CLI parser."""
    parser.add_argument(
        "--portfolio-group",
        type=str,
        default="sla_portfolios",
        help="Name of the portfolio group key from the YAML config.",
    )
    parser.add_argument(
        "--target-date",
        type=str,
        default=datetime.now().strftime("%Y-%m-%d"),
        help="Target date for processing (YYYY-MM-DD).",
    )

create_sla_query_params(args: argparse.Namespace, config: NovaConfig) -> Dict[str, Any]

Creates the query_params dictionary from parsed args and config.

Source code in datatapes/sla/sla_processing_logic.py 
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
def create_sla_query_params(
    args: argparse.Namespace, config: NovaConfig
) -> Dict[str, Any]:
    """Creates the query_params dictionary from parsed args and config."""
    portfolio_groups = config.model_dump().get("portfolio_groups", {})
    portfolio_list = portfolio_groups.get(args.portfolio_group, [])

    if not portfolio_list:
        raise ValueError(
            f"Portfolio group '{args.portfolio_group}' not found or is empty in config."
        )

    print(
        f"INFO: Loaded {len(portfolio_list)} portfolios for group '{args.portfolio_group}'."
    )

    return {
        "portfolio_names": tuple(portfolio_list),
        "target_date": args.target_date,
    }

process_sla_data_nova(data_frames: Dict[str, pd.DataFrame], config: NovaConfig, console: Optional[Console] = None) -> Optional[pd.DataFrame]

Custom processor for the SLA datatape. This function performs all merging, calculations, and renaming to produce the final DataFrame.

Source code in datatapes/sla/sla_processing_logic.py 
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
def process_sla_data_nova(
    data_frames: Dict[str, pd.DataFrame],
    config: NovaConfig,
    console: Optional[Console] = None,
) -> Optional[pd.DataFrame]:
    """
    Custom processor for the SLA datatape.
    This function performs all merging, calculations, and renaming to produce the final DataFrame.
    """
    console = console or Console()
    console.print("[bold blue]--- Starting Custom SLA Processing Logic ---[/bold blue]")

    # 1. Prepare Primary DataFrame and Join Key
    key_col = "System Project: Sunnova System ID"  # This is the final name we will use

    df = data_frames.get("sla_systems")
    if df is None or df.empty:
        console.print(
            "[red]Error: Primary data source 'sla_systems' is missing or empty.[/red]"
        )
        return None

    # Rename the key column from the SQL output to its final name immediately
    df.rename(columns={"Sunnova System ID": key_col}, inplace=True)
    if key_col not in df.columns:
        console.print(
            f"[red]Error: Key column '{key_col}' not found after renaming. Check sla_systems.sql alias.[/red]"
        )
        return None

    df = df.drop_duplicates(subset=[key_col], keep="first").copy()
    console.print(
        f"Primary source 'sla_systems' loaded and deduplicated. Shape: {df.shape}"
    )

    # 2. Rename initial columns from sla_systems.sql to their final output names
    # This aligns the DataFrame with the `output_columns` list from the start.
    rename_map = {
        "Installation State": "Quote: Installation State",
        "Contract Type": "Quote: Contract Type",
        "Term (months)": "Quote: Term (months)",
        "Recurring Payment": "Quote: Recurring Payment",
        "Payment Escalator": "Quote: Payment Escalator",
        "Solar Rate (1)": "Quote: Solar Rate",
        "Utility Account": "Quote: Utility Account",
        "Partner Account": "Quote: Partner Account",
        # Add other simple renames here if the SQL output doesn't match the final name
    }
    df.rename(columns=rename_map, inplace=True)    # 3. Apply SLA Specific Calculations
    console.print("[blue]Step 1: Applying SLA-specific transformations...[/blue]")
    # TODO: Implement these functions or comment out if not needed
    # df = sla_logic.convert_date_columns(
    #     df,
    #     [
    #         "Substantial Stage Date",
    #         "Final Stage Date",
    #         "InService Date",
    #         "PTO Received Date",
    #     ],
    #     console=console,
    # )
    # df = sla_logic.calculate_first_payment_date(df, console=console)
    # df = sla_logic.calculate_prepayment_deadline(df, console=console)
    # df = sla_logic.calculate_full_loan_balance(df, console=console)

    # 4. Process and Merge Satellite DataFrames

    # Battery Data
    if "battery_data" in data_frames:
        battery_df = data_frames["battery_data"].rename(
            columns={"Sunnova System ID": key_col}
        )
        battery_agg = (
            battery_df.groupby(key_col)
            .agg(
                **{
                    "Quote: Quantity of Batteries": ("Quantity", "sum"),
                    "Battery Manufacturer": ("Manufacturer", "first"),
                    "Battery Model": ("Model Number", "first"),
                }
            )
            .reset_index()
        )
        df = pd.merge(df, battery_agg, on=key_col, how="left")    # Cloudlending Data
    if "cloudlending" in data_frames:
        console.print("[blue]Processing Cloudlending data...[/blue]")
        cl_df = data_frames["cloudlending"].rename(columns={"System": key_col})
        # TODO: Implement cloudlending processing functions
        # cl_filtered = sla_logic.filter_cloudlending_loans(
        #     cl_df,
        #     [
        #         "Scheduled Prepayment",
        #         "Primary",
        #         "Battery Loan",
        #         "EZ Own",
        #         "Accessory Loan",
        #     ],
        #     console=console,
        # )
        # cl_conditional = sla_logic.calculate_conditional_cl_balance(
        #     cl_filtered, console=console
        # )
        # cl_total, cl_prepay = sla_logic.calculate_cl_aggregates(
        #     cl_conditional, key_col=key_col, console=console
        # )
        # df = pd.merge(
        #     df, cl_total[[key_col, "Outstanding Balance"]], on=key_col, how="left"
        # )
        # df = pd.merge(
        #     df,
        #     cl_prepay[[key_col, "Remaining Scheduled Prepayment"]],
        #     on=key_col,
        #     how="left",
        # )
        # df = sla_logic.apply_inservice_balance_logic(df, console=console)    # Ancillary Data
    if "ancillary" in data_frames:
        console.print("[blue]Processing Ancillary data...[/blue]")
        anc_df = data_frames["ancillary"].rename(columns={"System": key_col})
        # TODO: Implement ancillary processing functions
        # df = sla_logic.add_ancillary_flag(
        #     df, anc_df[key_col], key_col=key_col, console=console
        # )
        # ancillary_agg = sla_logic.calculate_ancillary_aggregates(
        #     anc_df, key_col=key_col, console=console
        # )
        # df = pd.merge(df, ancillary_agg, on=key_col, how="left")

    # Monthly Payments Data
    if "monthly_payments" in data_frames:
        console.print("[blue]Processing Monthly Payments data...[/blue]")
        mp_df = data_frames["monthly_payments"].rename(columns={"System": key_col})
        # TODO: Implement monthly payments processing function
        # df = sla_logic.process_latest_monthly_payment(
        #     df, mp_df, key_col=key_col, console=console
        # )
    else:  # Ensure the column exists even if there's no data
        if "Current Monthly Payment" not in df.columns:
            df["Current Monthly Payment"] = df.get("Quote: Recurring Payment")

    console.print(f"Final shape before returning to runner: {df.shape}")
    console.print(
        "[bold green]--- Custom SLA Processing Logic Finished ---[/bold green]"
    )
    return df

marimo_file_format