Snowflake SQL patterns for revenue reporting

By Malek Damiri

Most revenue reporting problems are not hard SQL. They’re the same handful of problems showing up in different tables: gaps in the calendar, days cut at the wrong midnight, duplicate records, totals that need a comparison, data that quietly stopped arriving, and two systems that disagree about the same month. I’ve settled on a small set of Snowflake patterns for them. Here they are, with generic table names. Swap in your own.

Start from a date spine

If a day had no orders, a plain GROUP BY leaves it out and the chart skips the day as if it never happened. Generate the calendar first and left join the facts onto it, so a quiet day shows up as zero instead of vanishing. If you already have a dim_date table, join to that instead of the generator. SEQ4 can have gaps, so use ROW_NUMBER for the offset.

with spine as (
  select dateadd(day, row_number() over (order by seq4()) - 1, '2025-01-01'::date) as report_date
  from table(generator(rowcount => 1000))
)
select s.report_date,
       coalesce(sum(o.amount_usd), 0) as revenue_usd
from spine s
left join orders o
  on o.order_date = s.report_date
where s.report_date <= current_date()
group by 1
order by 1;

Cut days in the reporting time zone

Timestamps usually arrive in UTC, and a month that closes at midnight UTC is a different month from one that closes at midnight where the business operates. Pick one reporting time zone, convert before you truncate, and do it in one shared view rather than in every dashboard. If created_at is a TIMESTAMP_NTZ holding UTC, the three-argument form of CONVERT_TIMEZONE does the conversion.

select convert_timezone('UTC', 'America/New_York', created_at)::date as order_day,
       count(*) as orders,
       sum(amount_usd) as revenue_usd
from orders
group by 1
order by 1;

Dedupe to the latest record with QUALIFY

Systems that sync by upsert or replay often land several versions of the same invoice, and a SUM over all of them overstates revenue by exactly the amount nobody can explain. Keep the latest version per key and drop the rest. QUALIFY filters on the window function directly, with no wrapping subquery, which keeps the intent visible to the next reader.

select *
from invoices
qualify row_number() over (
  partition by invoice_id
  order by updated_at desc, loaded_at desc
) = 1;

Running totals and period-over-period

Most executive questions are “compared to what?” This month against last month. This year to date against the same point last year. Aggregate to the grain you report at first, then let window functions do the comparing. One caveat: LAG counts rows, not months. If a month can be missing, run this on top of the spine above or the comparison will shift by a row.

with monthly as (
  select date_trunc('month', trade_date)::date as report_month,
         sum(fee_usd) as fees_usd
  from trades
  group by 1
)
select report_month,
       fees_usd,
       sum(fees_usd) over (
         partition by date_trunc('year', report_month)
         order by report_month
       ) as fees_ytd,
       lag(fees_usd, 1) over (order by report_month) as prior_month_usd,
       lag(fees_usd, 12) over (order by report_month) as prior_year_usd
from monthly
order by report_month;

A freshness check the dashboard can show

A dashboard that shows yesterday’s numbers with full confidence is worse than one that admits it’s stale. Compute freshness from the data itself, not from when the page loaded, and put the result where readers see it. Watch the time zones here too. CURRENT_TIMESTAMP follows the session time zone, so if loaded_at is stored as UTC, compare against SYSDATE instead, or convert one side before the subtraction.

with latest as (
  select max(loaded_at) as last_loaded_at,
         datediff('hour', max(loaded_at), current_timestamp()) as hours_since_load
  from orders
)
select last_loaded_at,
       hours_since_load,
       iff(hours_since_load > 24, 'stale', 'ok') as status
from latest;

Reconcile two systems with a FULL OUTER JOIN

When the warehouse and the ledger disagree, you want every period from both sides, including the months that exist in only one of them. An inner join hides exactly the rows you’re looking for. Use a FULL OUTER JOIN, COALESCE the keys and the amounts, and add a tolerance so you’re not chasing rounding. Anything outside the tolerance gets a written explanation.

with warehouse as (
  select date_trunc('month', invoice_date)::date as report_month,
         sum(amount_usd) as warehouse_usd
  from invoices
  group by 1
),
ledger as (
  select date_trunc('month', posting_date)::date as report_month,
         sum(amount_usd) as ledger_usd
  from ledger_entries
  group by 1
)
select coalesce(w.report_month, l.report_month) as report_month,
       coalesce(w.warehouse_usd, 0) as warehouse_usd,
       coalesce(l.ledger_usd, 0) as ledger_usd,
       coalesce(w.warehouse_usd, 0) - coalesce(l.ledger_usd, 0) as variance_usd
from warehouse w
full outer join ledger l
  on w.report_month = l.report_month
where abs(coalesce(w.warehouse_usd, 0) - coalesce(l.ledger_usd, 0)) > 1 -- tolerance in dollars
order by 1;

None of these are clever, and that’s the point. The queries behind an executive dashboard should be boring enough that the next person can read them in one sitting. Put them in version control, name the CTEs plainly, and reuse the same few shapes until people stop asking how a number was built.