SnowCLI is overly aggressive in determining template characters like &.

[sfc-gh-myoung]

SnowCLI version

3.13.1

Python version

3.14.2 and 3.11.2

Platform

macOS-15.7.2-arm64-arm-64bit-Mach-O

What happened

SnowCLI is overly aggressive in detecting template characters like &. I've seen this happen multiple times when & is embedded in a DDL COMMENT or in a Semantic View synonym.

The issue seems to happen when there are no spaces around &. For example Principal & Interest within a comment or synonym won't trigger the issue. However, Principal&Interest will trigger it.

I've included the full DDL below, however this is the section with the problematic behavior:

FACTS (
    PAYMENT.scheduled_payment AS scheduled_payment
      WITH SYNONYMS ('monthly payment', 'principal and interest payment', 'scheduled amount', 'Principal&Interest payment')
      COMMENT = 'Scheduled monthly payment amount with principal&interest',

This is the full Semantic View DDL where both the comment and synonym have the &:

  TABLES (
    PAYMENT AS FREEDOM_MORTGAGE_DEMO.LOAN_SERVICING.FACT_LOAN_PAYMENTS
      PRIMARY KEY (payment_id)
      WITH SYNONYMS ('payments', 'payment transactions', 'payment history')
      COMMENT = 'Monthly payment transactions',
    
    ORIG AS FREEDOM_MORTGAGE_DEMO.LOAN_ORIGINATION.FACT_LOAN_ORIGINATIONS
      PRIMARY KEY (loan_id)
      WITH SYNONYMS ('loans', 'originations')
      COMMENT = 'Loan origination details',
    
    BORROWER AS FREEDOM_MORTGAGE_DEMO.LOAN_ORIGINATION.DIM_BORROWER
      PRIMARY KEY (borrower_id)
      WITH SYNONYMS ('borrowers', 'customers')
      COMMENT = 'Borrower information',
    
    DELINQUENCY AS FREEDOM_MORTGAGE_DEMO.LOAN_SERVICING.FACT_DELINQUENCIES
      PRIMARY KEY (delinquency_id)
      WITH SYNONYMS ('delinquencies', 'past due events')
      COMMENT = 'Delinquency events'
  )
  RELATIONSHIPS (
    PAYMENT_TO_ORIG AS PAYMENT(loan_id) REFERENCES ORIG(loan_id),
    PAYMENT_TO_BORROWER AS PAYMENT(borrower_id) REFERENCES BORROWER(borrower_id),
    DELINQ_TO_PAYMENT AS DELINQUENCY(payment_id) REFERENCES PAYMENT(payment_id)
  )
  FACTS (
    PAYMENT.scheduled_payment AS scheduled_payment
      WITH SYNONYMS ('monthly payment', 'principal and interest payment', 'scheduled amount', 'Principal&Interest payment')
      COMMENT = 'Scheduled monthly payment amount with principal&interest',
    
    PAYMENT.actual_payment AS actual_payment
      WITH SYNONYMS ('amount paid', 'payment received', 'paid amount')
      COMMENT = 'Actual amount paid by borrower',
    
    PAYMENT.principal_paid AS principal_paid
      WITH SYNONYMS ('principal payment', 'principal portion')
      COMMENT = 'Principal portion of payment',
    
    PAYMENT.interest_paid AS interest_paid
      WITH SYNONYMS ('interest payment', 'interest portion')
      COMMENT = 'Interest portion of payment',
    
    PAYMENT.escrow_paid AS escrow_paid
      WITH SYNONYMS ('escrow payment', 'escrow portion', 'taxes and insurance')
      COMMENT = 'Escrow portion (taxes and insurance)',
    
    PAYMENT.late_fee AS late_fee
      WITH SYNONYMS ('late charge', 'penalty', 'late payment fee')
      COMMENT = 'Late fee charged on payment',
    
    PAYMENT.remaining_balance AS remaining_balance
      WITH SYNONYMS ('outstanding balance', 'principal balance', 'balance after payment')
      COMMENT = 'Remaining loan balance after payment',
    
    DELINQUENCY.days_past_due AS days_past_due
      WITH SYNONYMS ('DPD', 'days delinquent', 'past due days')
      COMMENT = 'Number of days payment is past due',
    
    DELINQUENCY.outstanding_balance AS outstanding_balance
      WITH SYNONYMS ('past due balance', 'arrears balance', 'delinquent balance')
      COMMENT = 'Outstanding balance in delinquency',
    
    DELINQUENCY.late_charges AS late_charges
      WITH SYNONYMS ('accumulated late fees', 'late fee total')
      COMMENT = 'Total late charges accumulated',
    
    DELINQUENCY.total_amount_due AS total_amount_due
      WITH SYNONYMS ('total past due', 'total arrears', 'amount owed', 'total delinquent amount')
      COMMENT = 'Total amount due including principal, interest, and fees',
    
    ORIG.funded_amount AS funded_amount
      WITH SYNONYMS ('original balance', 'original loan amount', 'initial principal')
      COMMENT = 'Original funded loan amount'
  )
  DIMENSIONS (
    PAYMENT.payment_id AS payment_id
      WITH SYNONYMS ('transaction ID', 'payment number')
      COMMENT = 'Unique payment transaction identifier',
    
    PAYMENT.loan_id AS loan_id
      WITH SYNONYMS ('loan number', 'loan ID')
      COMMENT = 'Loan identifier',
    
    PAYMENT.borrower_id AS borrower_id
      WITH SYNONYMS ('customer ID', 'borrower number')
      COMMENT = 'Borrower identifier',
    
    PAYMENT.payment_number AS payment_number
      WITH SYNONYMS ('payment sequence', 'payment count')
      COMMENT = 'Sequential payment number (1, 2, 3...)',
    
    PAYMENT.payment_due_date AS payment_due_date
      WITH SYNONYMS ('due date', 'scheduled date', 'payment date')
      COMMENT = 'Date payment was due',
    
    PAYMENT.payment_date AS payment_date
      WITH SYNONYMS ('paid date', 'received date', 'actual payment date')
      COMMENT = 'Date payment was actually received',
    
    PAYMENT.payment_status AS payment_status
      WITH SYNONYMS ('status', 'payment state', 'timeliness')
      COMMENT = 'Payment status (On Time, Late 1-30 days, etc)',
    
    ORIG.loan_type AS loan_type
      WITH SYNONYMS ('product type', 'mortgage type')
      COMMENT = 'Loan product type (Conventional, FHA, VA, Jumbo)',
    
    ORIG.loan_program AS loan_program
      WITH SYNONYMS ('program', 'loan product')
      COMMENT = 'Loan program (30-Year Fixed, ARM, etc)',
    
    ORIG.loan_status AS loan_status
      WITH SYNONYMS ('status', 'current status')
      COMMENT = 'Current loan status (Active, Paid Off, etc)',
    
    ORIG.funding_date AS funding_date
      WITH SYNONYMS ('origination date', 'closing date', 'start date')
      COMMENT = 'Date loan was funded',
    
    BORROWER.state AS state
      WITH SYNONYMS ('borrower state', 'borrower location', 'customer state')
      COMMENT = 'Borrower state (2-letter code)',
    
    BORROWER.city AS city
      WITH SYNONYMS ('borrower city', 'city name', 'customer city')
      COMMENT = 'Borrower city name',
    
    DELINQUENCY.delinquency_id AS delinquency_id
      COMMENT = 'Unique delinquency event identifier'
  )
  METRICS (
    PAYMENT.payment_count AS COUNT(DISTINCT payment_id)
      WITH SYNONYMS ('total payments', 'number of payments', 'payment volume')
      COMMENT = 'Total number of payment transactions',
    
    PAYMENT.on_time_payment_count AS COUNT(DISTINCT CASE WHEN payment_status = 'On Time' THEN payment_id END)
      WITH SYNONYMS ('on-time payments', 'current payments', 'timely payments')
      COMMENT = 'Number of on-time payments',
    
    PAYMENT.late_payment_count AS COUNT(DISTINCT CASE WHEN payment_status != 'On Time' THEN payment_id END)
      WITH SYNONYMS ('late payments', 'delinquent payments', 'past due payments')
      COMMENT = 'Number of late payments',
    
    DELINQUENCY.delinquency_event_count AS COUNT(DISTINCT delinquency_id)
      WITH SYNONYMS ('delinquency count', 'number of delinquencies')
      COMMENT = 'Number of delinquency events',
    
    PAYMENT.total_scheduled AS SUM(scheduled_payment)
      WITH SYNONYMS ('total scheduled payments', 'scheduled amount')
      COMMENT = 'Total scheduled payment amount',
    
    PAYMENT.total_actual AS SUM(actual_payment)
      WITH SYNONYMS ('total payments received', 'collected amount')
      COMMENT = 'Total actual payments received',
    
    PAYMENT.total_principal_paid AS SUM(principal_paid)
      WITH SYNONYMS ('total principal', 'principal collected')
      COMMENT = 'Total principal payments collected',
    
    PAYMENT.total_interest_paid AS SUM(interest_paid)
      WITH SYNONYMS ('total interest', 'interest income', 'interest collected')
      COMMENT = 'Total interest payments collected',
    
    PAYMENT.total_escrow_paid AS SUM(escrow_paid)
      WITH SYNONYMS ('total escrow', 'escrow collected')
      COMMENT = 'Total escrow payments collected',
    
    PAYMENT.total_late_fees AS SUM(late_fee)
      WITH SYNONYMS ('late fees collected', 'penalty income')
      COMMENT = 'Total late fees charged',
    
    DELINQUENCY.total_delinquent_balance AS SUM(total_amount_due)
      WITH SYNONYMS ('total past due', 'total arrears')
      COMMENT = 'Total amount past due across all delinquencies',
    
    PAYMENT.avg_scheduled_payment AS AVG(scheduled_payment)
      WITH SYNONYMS ('average payment', 'mean monthly payment')
      COMMENT = 'Average scheduled payment amount',
    
    PAYMENT.avg_remaining_balance AS AVG(remaining_balance)
      WITH SYNONYMS ('average balance', 'mean outstanding balance')
      COMMENT = 'Average remaining loan balance',
    
    DELINQUENCY.avg_days_past_due AS AVG(days_past_due)
      WITH SYNONYMS ('average DPD', 'mean delinquency days')
      COMMENT = 'Average days past due for delinquent loans'
  )
  COMMENT = 'Loan servicing analytics for Cortex Analyst: payment performance, delinquency tracking, and portfolio health metrics'
  COPY GRANTS;

Console output

I've tested with snowcli installed via `homebrew` and via `uvx`.

When `&` is in the synonym of a semantic view:

% uvx --from snowflake-cli snow sql -f 08__create_semantic_view_servicing.sql
Warning: &{ ... } syntax is deprecated and will no longer be supported. Use <% ... %> syntax instead.
SQL template rendering error: 'Interest' is undefined
╭─ Error ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ SQL rendering error                                                                                                                             │
╰─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

When `&` is in a comment:

% uvx --from snowflake-cli snow sql -f 08__create_semantic_view_servicing.sql
Warning: &{ ... } syntax is deprecated and will no longer be supported. Use <% ... %> syntax instead.
SQL template rendering error: 'interest' is undefined

How to reproduce

Create a DDL statement with & in a comment or synonym where there is no space around the &. Then change the DDL to use a space & and you'll find the error goes away.

[sfc-gh-myoung]

Addition testing shows that P&I will trigger the template rendering error, but P & I does not.