Detect AWS cost spikes with SQL

This tutorial builds a flow that keeps its own history of AWS spend and queries it with SQL. A custom schedule runs it every morning, a Date/time transform computes yesterday's date, an AWS node fetches per-service spend from Cost Explorer, and three Datastore nodes record the day, detect spikes with a single SQL statement, and prune old rows. A Filter and a Notification node turn the result into a Slack alert.

Goal and objectives

• Goal: Get a Slack alert naming any AWS service whose spend yesterday ran more than 50% above its trailing 30-day average, with no external database and no warehouse job.

• Objectives: In this tutorial, you'll learn how to:

  • Build a rolling history table with a Datastore Insert node.

  • Write a SELECT with the Datastore Run SQL action and bind a value from an earlier step with a :name parameter.

  • Reference the query's output columns in a filter and a Slack message.

  • Keep the table bounded with a scheduled DELETE.

Below is the complete flow:

注意

Every AWS call in this flow is read-only (ce:GetCostAndUsage). The only writes happen inside your own Datastore table.

Before you begin

1. Make sure your DoiT account has the CloudFlow Editor or CloudFlow Manager permission. See CloudFlow permissions.

2. Create an AWS CloudFlow connection for the account whose spend you want to watch. The role needs the ce:GetCostAndUsage permission.

3. Connect Slack and identify the channel that should receive the alerts.

Create the history table

The flow needs one table to accumulate daily spend.

1. On the CloudFlow landing page, select Tables, then select Create table.

2. In Table name, enter daily_service_spend.

3. Define three columns:

   Column	Data type	Notes
   day	Date	The usage date, one row per service per day.
   service	Text	The AWS service name, for example AWS WAF.
   cost	Text	Cost Explorer returns amounts as strings. The SQL casts this to a number when it does the math.

4. Select Save.

注意

cost is a Text column on purpose. Cost Explorer returns Metrics.UnblendedCost.Amount as a string, and a Numeric column only accepts numeric references. Storing the raw string and casting it in SQL (cost::numeric) keeps the Insert node simple.

Create the flow

1. Sign in to the DoiT console, select Automation and operations from the top navigation mega menu, and then select CloudFlow.

2. Select Create flow, give it a name such as Cost spike sentinel, and add a description.

Configure the schedule trigger

1. Add a Scheduled trigger.

2. Set the frequency to Daily, pick a start date, and choose a run time and time zone. Early morning works well, because Cost Explorer has finalized most of the previous day's usage by then.

Compute yesterday's date

Cost Explorer needs a date, and so does the SQL later on. One transform produces both.

1. Add a Date/time transform node after the trigger.

2. Set Input to the trigger's startTime.

3. Add a Subtract transformation of 1 Day and name the output yesterdayTs.

4. Chain a Format transformation on the previous step with the pattern YYYY-MM-DD and name the output yesterday.

   

The flow now has a yesterday string that every downstream node can reference.

Fetch yesterday's spend per service

1. Add an AWS node, select the Cost Explorer service and the GetCostAndUsage operation, and choose your AWS connection and account.

2. Configure the parameters:

   • TimePeriod → Start: reference yesterday from the Date/time transform.

   • TimePeriod → End: reference currentDate from the trigger. Cost Explorer treats End as exclusive, so this returns exactly one day.

   • Granularity: DAILY.

   • Metrics 1: UnblendedCost.

   • GroupBy 1: Key SERVICE, Type DIMENSION.

   

3. Open the Test tab and select Run test, keeping Save as test data selected. The response contains ResultsByTime[0].Groups, one entry per service, which the next node maps into the table.

Record the day in the table

1. Add a Datastore node, select the daily_service_spend table, and keep the Insert action.

2. Map each column to the Cost Explorer output. The node inserts one row per group automatically.

   • Day: ResultsByTime.TimePeriod.Start

   • Service: ResultsByTime.Groups.Keys

   • Cost: ResultsByTime.Groups.Metrics, then enter UnblendedCost as the map key and select Amount

   

注意

All three columns reference the same node. A node's parameters can reference one non-trigger step at a time, which is why the day comes from the Cost Explorer response rather than from the Date/time transform.

Detect spikes with SQL

This is the node that does the analysis. It compares yesterday against every earlier day in the table.

1. Add a second Datastore node, select the same table, and set the action to Run SQL.

2. Enter the statement:

   WITH baseline AS (
     SELECT service, AVG(cost::numeric) AS avg_cost
     FROM daily_service_spend
     WHERE day < :yesterday::date
       AND day >= :yesterday::date - 30
     GROUP BY service
   )
   SELECT s.service,
          ROUND(s.cost::numeric, 2) AS yesterday_cost,
          ROUND(b.avg_cost, 2) AS avg_cost,
          ROUND(s.cost::numeric / NULLIF(b.avg_cost, 0), 2) AS spike_ratio
   FROM daily_service_spend s
   JOIN baseline b ON b.service = s.service
   WHERE s.day = :yesterday::date
     AND s.cost::numeric > 1.5 * b.avg_cost
   ORDER BY spike_ratio DESC

   The common table expression averages each service's cost over the 30 days before yesterday, the main query joins yesterday's row against that average, and the WHERE clause keeps only services running more than 50% above it. NULLIF avoids a division by zero for services whose history is all zeros.

   Both date bounds matter. Without the lower bound the average would cover every row still in the table, so the comparison window would silently widen as history accumulated.

3. Under Bind parameters, select Add parameter, name it yesterday, then select Add additional parameters, select the yesterday checkbox, and confirm.

4. In the Yesterday field that appears, reference yesterday from the Date/time transform.

   

5. Select Run query to validate. When the statement is valid, Output schema lists the columns the query returns: service, yesterday_cost, avg_cost, and spike_ratio. Those names are what the filter and the Slack message reference.

注意

Parameters bound to step outputs resolve to empty values when you select Run query in the editor. They resolve fully when the flow runs.

Prune old history

Without this step the table grows forever. One statement keeps it at 90 days, which is deliberately longer than the 30-day comparison window so that there is history left to inspect when an alert looks wrong.

1. Add a third Datastore node on the same table with the Run SQL action.

2. Enter the statement:

   DELETE FROM daily_service_spend WHERE day < :yesterday::date - 90

3. Add the yesterday bind parameter the same way as the previous node and reference the Date/time transform again.

Place this node before the filter so that pruning happens on every run, including days with no spike.

Filter to real spikes

1. Add a Filter node.

2. Set Field to the spike detection node's output.

3. Add the condition spike_ratio > 1.5.

   

The SQL already applies this threshold, so the filter is what tells the flow whether there is anything to report.

Send the alert

1. Add a Notification node and select Slack as the provider.

2. Choose the channel that should receive alerts.

3. Write the message, typing @ to reference the filter's fields. For example:

   AWS cost spike detected: yesterday's spend ran more than 50% above the trailing 30-day average for the services below.
   @service spike ratio: @spike_ratio (yesterday @yesterday_cost vs 30-day avg @avg_cost)

4. Select Don't send notification if no results. Without it, the flow posts a message with empty values on days when nothing spiked.

   

Publish and verify

1. Select Publish.

2. Select Run to trigger the flow manually, then open the run details to confirm every step completed.

   

3. Expand the spike detection step to see the rows it returned. On the first run the table has only one day of data, so the baseline is empty and no service can exceed it. That is expected.

4. Open the table from the Tables tab to confirm yesterday's rows landed.

   

After a few days the baseline becomes meaningful and the flow starts alerting on real outliers. Once a spike clears the threshold, the Slack message names the service, the ratio, and both figures behind it:

AWS cost spike detected: yesterday's spend ran more than 50% above the trailing
30-day average for the services below.
Amazon Elastic Compute Cloud - Compute, AWS WAF spike ratio: 1.87, 1.8
(yesterday 0.37, 0.32 vs 30-day avg 0.2, 0.18)

Adapt the pattern

The shape of this flow generalizes to anything you want to watch over time: record a measurement on a schedule, aggregate it with SQL, alert on the outliers, prune the history. A few variations worth trying:

• Group Cost Explorer by LINKED_ACCOUNT instead of SERVICE to watch accounts rather than services.

• Add a HAVING COUNT(*) >= 7 clause to the baseline so a service needs a week of history before it can trigger an alert.

• Change the multiplier from 1.5 to something stricter, or compare against MAX(cost) instead of the average.

• Keep a second table of per-service thresholds and join to it, so noisy services get their own multiplier.