Snowflake - given a start and end date column, break out each month and count number of days for the month into separate rows

You should add another column to indicate the type of count you're calculating, but you can accomplish this with datediff,last_day, and date_trunc(to get first of month). Here is an example

set start_date='2022-01-25'::date;
set end_date='2022-02-04'::date;

select $start_date,
       $end_date,
       'Start_Remainder' as label,
       datediff(day,$start_date,last_day($start_date))+1 as days_count
union all
select $start_date,
       $end_date,
       'End_Accrued' as label,
       datediff(day,date_trunc('month',$end_date),$end_date)+1 as days_count

If I were you, I would have the counts in separate columns

select $start_date,
       $end_date,
       datediff(day,$start_date,last_day($start_date))+1 as Start_Remainder_Days,
       datediff(day,date_trunc('month',$end_date),$end_date)+1 as End_Accrued_Days

With one CTE to provide the data, and a couple of pre-conditioning CTE's

WITH data_table(start_date, end_date) as (
    SELECT * from values
        ('2022-01-25'::date, '2022-02-04'::date),
        ('2018-08-31'::date, '2018-10-05'::date)
), large_range as (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
), pre_condition as (
    SELECT 
        start_date
        ,end_date
        ,datediff('month', start_date, end_date) as m
    FROM data_table
)
SELECT
    date_trunc('month', dateadd('month', r.rn, d.start_date)) as month
    ,d.start_date
    ,d.end_date
    ,datediff('day', greatest(d.start_date, month), least(d.end_date, dateadd('day', -1, dateadd('month', 1, month))))+1 as days_count
FROM pre_condition as d
JOIN large_range as r ON r.rn <= d.m
ORDER BY d.start_date, month;

gives:

And if you don't like the look for CTE's

SELECT
    date_trunc('month', dateadd('month', r.rn, d.start_date)) as month
    ,d.start_date
    ,d.end_date
    ,datediff('day', greatest(d.start_date, month), least(d.end_date, dateadd('day', -1, dateadd('month', 1, month))))+1 as days_count
FROM (
    SELECT 
        start_date
        ,end_date
        ,datediff('month', start_date, end_date) as m
    FROM data_table
) as d
JOIN (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
) as r ON r.rn <= d.m
ORDER BY d.start_date, month;