BasekickLabs

Continuous Queries

Downsample Arc Enterprise data into materialized measurements, with the licensed scheduler running each continuous query on a cron schedule across the cluster.

Continuous queries enable automatic downsampling and aggregation of data into materialized views, reducing storage requirements while maintaining queryable historical data.

Automatic Scheduling Available

Arc Enterprise includes automatic CQ execution. See Automated Scheduling to configure scheduled execution.

Overview

Continuous queries in Arc help you:

  • Downsample Data: Aggregate high-frequency data into lower-frequency summaries
  • Reduce Storage: Store aggregated data instead of raw metrics
  • Maintain History: Keep long-term trends without full granularity
  • Improve Query Performance: Query pre-aggregated data for faster results
  • Create Materialized Views: Automatically maintain aggregated datasets

How it works

Continuous queries use standard analytical SQL to aggregate data from source measurements into destination measurements:

  1. Define Query: Specify aggregation logic using SQL
  2. Set Schedule: Configure time intervals for grouping (e.g., hourly, daily)
  3. Execute Manually: Trigger execution via API with start/end times
  4. Store Results: Write aggregated data to a new measurement
  5. Apply Retention: Optionally set custom retention for aggregated data

Architecture

Source Measurement (cpu)
    ↓
Continuous Query (AVG, MAX, MIN, etc.)
    ↓
Destination Measurement (cpu_hourly)
    ↓
Optional: Retention Policy

Execution model and semantics

Continuous queries are recomputed once per interval, not maintained as an incremental/streaming aggregate. There is no running state carried between runs.

On each scheduled run, Arc:

  1. Computes the time window [last_processed_time, now) — the slice since the previous successful run (on the very first run it defaults to the last hour).
  2. Substitutes that range into your query's {start_time} / {end_time} placeholders and runs the aggregation fresh over the full set of source rows in that window (read from Parquet).
  3. Writes the results to the destination measurement, stamped with the window start time and tagged so duplicate windows can be deduped (see Idempotency and tag_columns).
  4. Advances last_processed_time to the window's end.

Since Arc 27.01.1, a run whose window is empty does nothing: the previous run ended in this same second, or last_processed_time is ahead of the clock by up to a minute (a manual run's end_time may be that far ahead). Arc runs no aggregation, writes no execution history row and leaves last_processed_time as it is. A last_processed_time more than a minute ahead of the clock is still an error, so a query stuck behind clock skew stays visible in the logs.

Because the watermark advances to the end of each processed window, windows are tumbling and non-overlapping — the next run starts where the previous one ended; earlier windows are not revisited.

Important semantics

  • Late / out-of-order data is not reprocessed. A window is computed once and the watermark then moves past it. Rows that arrive after their window has already been processed (late events, corrections, backfills) are not folded back into that window's aggregate. There is no lookback/grace window today.
  • Window boundaries are processing-time based (now at run time), not event-time. A window's correctness assumes its data has been ingested by the time the interval fires.
  • Output is idempotent at compaction, not atomically exactly-once. A retry, an overlapping manual re-run, or a crash between the write and the watermark update re-emits a window's rows, so duplicates can appear transiently. A re-emitted row identical to the first is collapsed the next time the destination partition compacts — provided you declared the grouping dimensions in tag_columns (a query with no grouping is deduped automatically). Until compaction runs, a query over the destination may see the duplicate rows. Since 26.09.4, a re-run whose aggregate differs (for example, late data arrived in between) keeps both rows; up to 26.09.3 one of them, usually the stale one, was kept.

This model fits periodic roll-ups and downsampling of in-order, timely data (for example building 1m/5m bars from a clean feed). If your workload involves late corrections, out-of-order events, or strict exactly-once aggregation, design around these semantics (e.g. reprocess on a delay, or do the final roll-up as a query-time aggregation).

In a cluster

Since 27.01.1 a cluster keeps its continuous queries, and each one's last_processed_time, in the cluster state, not in each node's own database (arc#1324). Only the primary writer runs them, as before.

Before 27.01.1. A continuous query lived only on the node that received it, and so did its last_processed_time. So:

  • a query created on any other node answered 201 but never ran;
  • after a failover, the new primary ran only its own queries, from its own last_processed_time. If it had none, its first run covered only the last hour and earlier data was never aggregated. If it had an old one, it aggregated those windows again.

From 27.01.1.

  • Write anywhere. Create, update or delete a continuous query on any node where continuous queries are enabled; every node's scheduler follows it, so the primary runs it whichever node received it. A create answers 202 in the rare case the cluster took it but this node has not applied it yet: list the queries to see it.
  • Read anywhere. Every node answers with the same queries, IDs and last_processed_time.
  • Continues across a failover. After every run the primary flushes the run's output to storage, then records the window's end in the cluster before the next run of that query. A new primary continues from there, and a recorded window's output is never only in the old primary's memory.
  • History stays per node. The execution history and last_execution_* stay on the node that ran the query.
  • Backups carry them. Every node that runs continuous queries keeps its own continuous_queries table equal to the cluster's queries; last_processed_time there is refreshed at least once a minute.

A standalone node is unchanged.

If the primary dies mid-run

If the primary dies, or is demoted, after writing a run's output and before the cluster recorded the window's end, the new primary runs that window again, so that window's rows are written a second time. If the primary cannot reach the cluster to record a window's end, it skips that query's next runs until it can, so this never grows past one window.

continuous_query.enabled and the scheduler's licence are per node: a primary where they are off runs no continuous queries.

Taking over the existing queries

Once every node runs 27.01.1, the cluster turns on cluster-wide continuous queries by itself: cluster_policies.cq_enabled in GET /api/v1/cluster. Until then the leader lists the nodes still on an earlier release in cq_blocking_nodes.

The leader then reads every node's own continuous queries. It takes them over by itself only when:

  • every node answers;
  • no query name has two different definitions on two nodes (the definition includes is_active and description: a query paused on one node only, or described differently, counts as two);
  • every active query has completed a run on some node. Inactive queries are taken over as they are.

Each query takes the newest last_processed_time any node holds for the same definition.

Otherwise each node keeps its own queries and the primary goes on running its own, as before; cluster_policies.kinds.cq.last_error says why. An admin chooses a node's queries on the Raft leader, as for retention policies: GET /api/v1/cluster/policies?kind=cq lists every node's queries with a hash, and POST /api/v1/cluster/policies/adopt with {"kind": "cq", "node": "<id>", "hash": "<hash>"} takes them over.

When they are taken over:

  • IDs. Queries that all come from one node keep their IDs, as long as no other node ever issued those IDs. Otherwise every query gets a new ID above any ID a node held, and the old IDs answer 404.
  • The other nodes' queries. A query the cluster did not take over moves, with its history, to continuous_queries_dormant and continuous_query_executions_dormant in that node's database. It never runs again.
  • Writes wait a few seconds. While the leader reads the nodes' queries, a continuous query write answers 503; retry it.
  • A restarted node waits. A node whose own tables already hold the cluster's queries answers with 503, and runs nothing, until it has caught up with the cluster after a restart.

No rolling back a single node

Once cluster-wide continuous queries are on, a node running an earlier release can no longer join. Roll back the whole cluster from a backup, never one node in place.

Enable continuous queries

Arc serves the continuous-query API only when enabled is true in the [continuous_query] section. When it is off, every continuous-query endpoint answers 404 Not Found and every arcli cq command fails with continuous queries are disabled on this server (continuous_query.enabled=false).

Arc's built-in default is true. The arc.toml shipped in the .deb, RPM, Arch package and Docker image sets it to false up to 26.09.3 and to true from 26.09.4. Whether an upgraded install picks up the new value depends on the package:

  • .deb: up to 26.09.x, every upgrade replaces /etc/arc/arc.toml with the packaged file, so upgrading to 26.09.4 turns continuous queries on (and discards your other edits). From 27.01.1 the .deb keeps an existing /etc/arc/arc.toml, so an install that has enabled = false keeps it.
  • Docker: each image carries its own arc.toml, so a container started from a 26.09.4 or later image has continuous queries on, unless you mount your own arc.toml or set the environment variable.
  • RPM and Arch: an edited /etc/arc/arc.toml is kept across upgrades, with the new packaged file written next to it as arc.toml.rpmnew or arc.toml.pacnew. An edited install keeps enabled = false; an unedited file is replaced, which turns continuous queries on.

To turn continuous queries on, set the key in /etc/arc/arc.toml:

[continuous_query]
enabled = true

Or set ARC_CONTINUOUS_QUERY_ENABLED=true in Arc's environment (-e ARC_CONTINUOUS_QUERY_ENABLED=true for Docker; on a package install, with sudo systemctl edit arc, as shown in Move the data directory). Then restart Arc:

sudo systemctl restart arc
sudo journalctl -u arc | grep -i "continuous queries"

The startup log shows Continuous queries enabled when the API is on, and Continuous queries DISABLED when it is off.

API endpoints

Create continuous query

Define a new continuous query:

POST /api/v1/continuous_queries

Request Body:

{
  "name": "cpu_hourly_avg",
  "database": "telegraf",
  "source_measurement": "cpu",
  "destination_measurement": "cpu_hourly",
  "query": "SELECT time_bucket(INTERVAL '1 hour', time) AS time, host, AVG(usage_idle) AS avg_usage_idle, AVG(usage_user) AS avg_usage_user, COUNT(*) AS sample_count FROM telegraf.cpu WHERE time >= {start_time} AND time < {end_time} GROUP BY 1, host",
  "interval": "1h",
  "tag_columns": ["host"],
  "is_active": true
}

Parameters:

  • name (string, required): Unique query identifier
  • database (string, required): Database the query reads from and writes to
  • source_measurement (string, required): Source measurement to aggregate
  • destination_measurement (string, required): Where to store results
  • query (string, required): SQL aggregation query. It must contain the {start_time} and {end_time} placeholders, which Arc replaces with the window's quoted RFC 3339 timestamps on each run.
  • interval (string, required): How often the query runs (1m, 5m, 1h, 1d, etc.)
  • tag_columns (array of strings, optional): The grouping dimension columns in the query's output (e.g. ["host"] for GROUP BY host). See Idempotency and tag_columns below — set this for any GROUP BY query so re-runs don't produce duplicate rows.
  • description (string, optional): Free-text description
  • retention_days, delete_source_after_days (integer, optional): Stored with the definition and returned by the API, but not acted on: Arc does not expire the destination or delete the source because of them. Use a retention policy for that.
  • is_active (boolean, optional, default true): Whether the query runs. Create one paused with false.

Idempotency and tag_columns

Continuous-query output is made idempotent by Arc's compaction step: duplicate emissions of the same window (from a retry, an overlapping manual run, or a crash between the write and the watermark advance) are collapsed when the destination partition compacts. Since 26.09.4 a row is removed only when another row with the same (grouping dimensions, time) is identical in every column, so a re-run that produced a different value keeps both rows; up to 26.09.3 one row per (grouping dimensions, time) was kept, whatever its value.

For this to work, Arc must know which output columns are the grouping dimensions:

  • A query with GROUP BY <dimension> (e.g. GROUP BY host) must list those dimensions in tag_columns (e.g. "tag_columns": ["host"]). Arc writes them as Parquet tag metadata and dedups exact copies within each (tags, time).
  • A query with no grouping (one row per window, e.g. SELECT AVG(x) …) needs no tag_columns — Arc detects it produces one row per timestamp and dedups exact copies automatically.
  • If you group but forget to declare tag_columns, Arc detects the multiple-rows-per-timestamp output and does not dedup it (to avoid deleting distinct series). The query still runs and its rows are correct, but duplicate windows will accumulate; a warning is logged asking you to add tag_columns.

tag_columns may not include time (time is always part of the dedup key). Names must be plain identifiers (letters, digits, _, -) and may not start with _, because Arc does not store columns whose names start with _. Output-row timestamps are stamped with the window's start time; a query that does not select a time column now gets the correct window timestamp instead of the ingestion wall-clock.

Upgrading existing CQs

This is not a breaking change. Existing continuous queries keep running with no action — the CQ database is migrated automatically and tag_columns is optional. Add tag_columns to a grouped CQ when you want its duplicate windows to collapse; until you do, it behaves exactly as before (append-only). Note that a CQ which does not select a time column now stamps output with the window start rather than the ingestion wall-clock, so its destination has a one-time timestamp discontinuity at the upgrade.

List continuous queries

Retrieve all continuous queries:

GET /api/v1/continuous_queries

Response:

[
  {
    "id": 1,
    "name": "cpu_hourly_avg",
    "description": null,
    "database": "telegraf",
    "source_measurement": "cpu",
    "destination_measurement": "cpu_hourly",
    "query": "SELECT time_bucket(INTERVAL '1 hour', time) AS time, host, … WHERE time >= {start_time} AND time < {end_time} GROUP BY 1, host",
    "interval": "1h",
    "tag_columns": ["host"],
    "retention_days": null,
    "delete_source_after_days": null,
    "is_active": true,
    "last_execution_time": "2024-01-20T02:00:00Z",
    "last_execution_status": "completed",
    "last_processed_time": "2024-01-20T02:00:00Z",
    "last_records_written": 24,
    "created_at": "2024-01-15T10:30:00Z",
    "updated_at": "2024-01-15T10:30:00Z"
  }
]

Get single query

Retrieve a specific continuous query:

GET /api/v1/continuous_queries/{query_id}

Update continuous query

Update an existing continuous query:

PUT /api/v1/continuous_queries/{query_id}

Request Body: any subset of the create fields. A field you leave out keeps its stored value; a field you send is checked with the create rules and written. To pause a continuous query:

{ "is_active": false }

Send null to clear description, tag_columns, retention_days or delete_source_after_days. Leaving one of them out does not clear it. null for any other field, or a body with no fields, is refused with 400.

Delete continuous query

Remove a continuous query:

DELETE /api/v1/continuous_queries/{query_id}

Deleting a CQ keeps its data

Deleting a continuous query does not delete the destination measurement or its data. The aggregated data remains queryable.

Execute continuous query

Manually trigger a continuous query:

POST /api/v1/continuous_queries/{query_id}/execute

Request Body:

{
  "start_time": "2024-01-01T00:00:00Z",
  "end_time": "2024-01-31T23:59:59Z",
  "dry_run": false
}

Parameters:

  • start_time (string, optional): Start timestamp (RFC 3339). Defaults to last_processed_time, or one hour ago on a query's first run.
  • end_time (string, optional): End timestamp (RFC 3339). Defaults to now. Since 27.01.1, an end_time more than a minute in the future is refused with 400 (not for a dry run): the run's end becomes last_processed_time, and a future one made every later run fail until the clock caught up.
  • dry_run (boolean, optional): Test without writing data (default: false)

Since Arc 27.01.1, when you leave both start_time and end_time out and there is nothing new since last_processed_time, the answer is 200 with records_written: 0, start_time and end_time both equal to last_processed_time, and an empty execution_id: nothing ran, so no execution history row is written. A window you pass yourself whose start_time is not before its end_time, an end_time earlier than last_processed_time with start_time left out, or a last_processed_time more than a minute ahead of the clock answers 400 start_time must be before end_time.

A run that is not a dry run moves last_processed_time to its end_time, backwards too when you pass an earlier one, except a run with nothing to process, which leaves it. In a cluster (see In a cluster) the response carries watermark_status (absent when the run had nothing to process):

ValueMeaning
setlast_processed_time is now this run's end_time.
movedAnother run, or another primary, moved it while this one ran; theirs is kept.
pendingThe cluster has not taken it yet; the next run settles it first.

A run answers 503 while the previous run's last_processed_time has not reached the cluster, or while this node cannot confirm it holds the latest one. Retry it.

Response:

{
  "query_id": 1,
  "query_name": "cpu_hourly_avg",
  "execution_id": "cq-exec-3f2a9c1e",
  "status": "completed",
  "start_time": "2024-01-01T00:00:00Z",
  "end_time": "2024-01-31T23:59:59Z",
  "records_read": null,
  "records_written": 720,
  "execution_time_seconds": 2.5,
  "destination_measurement": "cpu_hourly",
  "dry_run": false,
  "executed_at": "2024-02-01T09:00:00Z"
}

A failed run returns 500 and is recorded in the execution history. Since Arc 27.01.1, a run with nothing to process (see the parameters above) is not recorded, and its empty execution_id matches no history row.

View execution history

View past executions of a continuous query:

GET /api/v1/continuous_queries/{query_id}/executions?limit=50

Response:

{
  "query_id": 1,
  "executions": [
    {
      "id": 42,
      "query_id": 1,
      "execution_id": "cq-sched-3f2a9c1e",
      "execution_time": "2024-01-20T02:00:00Z",
      "status": "completed",
      "start_time": "2024-01-20T01:00:00Z",
      "end_time": "2024-01-20T02:00:00Z",
      "records_read": null,
      "records_written": 24,
      "execution_duration_seconds": 1.2,
      "error_message": null
    }
  ]
}

Since Arc 27.01.1, a query ID with no continuous query behind it, mistyped or deleted, answers 404 Continuous query not found, as GET of the query does, and a query that has not run yet lists "executions": []. In a cluster, a node that has not yet caught up with the cluster's continuous queries after a restart answers 503; retry it. Earlier versions answered 200 with "executions": null for an unknown ID and for a query that had not run, and in a cluster a deleted query's history could still be listed for a moment after the delete.

Query syntax

Continuous queries use standard analytical SQL with temporal optimizations.

time is already a timestamp, so pass it to date_trunc() directly to bucket it:

SELECT
    date_trunc('hour', time) AS time,
    host,
    AVG(usage_idle) AS avg_usage_idle,
    MAX(usage_user) AS max_usage_user,
    MIN(usage_system) AS min_usage_system,
    COUNT(*) AS sample_count
FROM telegraf.cpu
GROUP BY date_trunc('hour', time), host

Common aggregations

These are common examples. Arc supports the full analytical SQL aggregate set — MEDIAN, MODE, QUANTILE_CONT, APPROX_QUANTILE, ARG_MAX, HISTOGRAM, CORR, REGR_* and the rest all work. See the Querying guide for more.

  • AVG() - Average values
  • SUM() - Sum of values
  • MIN() - Minimum value
  • MAX() - Maximum value
  • COUNT() - Row count
  • STDDEV() - Standard deviation
  • PERCENTILE_CONT() - Percentile calculations

Time bucketing

Using date_trunc():

-- Hourly buckets
date_trunc('hour', time)

-- Daily buckets
date_trunc('day', time)

-- 5-minute buckets
time_bucket(INTERVAL '5 minutes', time)

Including sample counts

Always include COUNT(*) to track how many raw samples each aggregate represents:

SELECT
    date_trunc('hour', time) AS time,
    host,
    AVG(usage_idle) AS avg_usage_idle,
    COUNT(*) AS sample_count  -- Important for data quality
FROM telegraf.cpu
GROUP BY date_trunc('hour', time), host

Usage examples

Example 1: hourly CPU metrics

Aggregate per-second CPU metrics into hourly averages:

import os
import requests

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Create continuous query
response = requests.post(
    "http://localhost:8000/api/v1/continuous_queries",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "name": "cpu_hourly",
        "database": "telegraf",
        "source_measurement": "cpu",
        "destination_measurement": "cpu_hourly",
        "query": """
            SELECT
                date_trunc('hour', time) AS time,
                host,
                AVG(usage_idle) AS avg_usage_idle,
                AVG(usage_user) AS avg_usage_user,
                AVG(usage_system) AS avg_usage_system,
                MAX(usage_user) AS max_usage_user,
                COUNT(*) AS sample_count
            FROM telegraf.cpu
            WHERE time >= {start_time} AND time < {end_time}
            GROUP BY date_trunc('hour', time), host
        """,
        "interval": "1h",
        "is_active": True
    }
)

query_id = response.json()["id"]

# Execute for the last 30 days
from datetime import datetime, timedelta

end_time = datetime.utcnow()
start_time = end_time - timedelta(days=30)

result = requests.post(
    f"http://localhost:8000/api/v1/continuous_queries/{query_id}/execute",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "start_time": start_time.isoformat() + "Z",
        "end_time": end_time.isoformat() + "Z"
    }
)

print(f"Generated {result.json()['records_written']} aggregated rows")

Example 2: daily request summary

Aggregate API request logs into daily summaries:

import os
import requests

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Create daily request summary
response = requests.post(
    "http://localhost:8000/api/v1/continuous_queries",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "name": "requests_daily",
        "database": "logs",
        "source_measurement": "api_requests",
        "destination_measurement": "api_requests_daily",
        "query": """
            SELECT
                date_trunc('day', time) AS time,
                endpoint,
                status_code,
                COUNT(*) AS total_requests,
                AVG(response_time_ms) AS avg_response_time,
                MAX(response_time_ms) AS max_response_time,
                PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95_response_time
            FROM logs.api_requests
            WHERE time >= {start_time} AND time < {end_time}
            GROUP BY date_trunc('day', time), endpoint, status_code
        """,
        "interval": "1d",
        "is_active": True
    }
)

Example 3: 5-minute sensor readings

Downsample IoT sensor data to 5-minute intervals:

import os
import requests

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Create 5-minute sensor aggregation
response = requests.post(
    "http://localhost:8000/api/v1/continuous_queries",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "name": "sensors_5min",
        "database": "iot",
        "source_measurement": "temperature",
        "destination_measurement": "temperature_5min",
        "query": """
            SELECT
                time_bucket(INTERVAL '5 minutes', time) AS time,
                sensor_id,
                location,
                AVG(temperature) AS avg_temperature,
                MIN(temperature) AS min_temperature,
                MAX(temperature) AS max_temperature,
                COUNT(*) AS sample_count
            FROM iot.temperature
            WHERE time >= {start_time} AND time < {end_time}
            GROUP BY time_bucket(INTERVAL '5 minutes', time), sensor_id, location
        """,
        "interval": "5m",
        "is_active": True
    }
)

Example 4: dry run testing

Test a continuous query before execution:

import os
import requests

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Create the query
response = requests.post(
    "http://localhost:8000/api/v1/continuous_queries",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={...}
)

query_id = response.json()["id"]

# Test with dry run
dry_run = requests.post(
    f"http://localhost:8000/api/v1/continuous_queries/{query_id}/execute",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "start_time": "2024-01-01T00:00:00Z",
        "end_time": "2024-01-02T00:00:00Z",
        "dry_run": True
    }
)

print("Would run:", dry_run.json()['executed_query'])

# If satisfied, execute for real
if dry_run.ok:
    result = requests.post(
        f"http://localhost:8000/api/v1/continuous_queries/{query_id}/execute",
        headers={"Authorization": f"Bearer {ARC_TOKEN}"},
        json={
            "start_time": "2024-01-01T00:00:00Z",
            "end_time": "2024-01-02T00:00:00Z",
            "dry_run": False
        }
    )

Storage benefits

Continuous queries significantly reduce storage requirements:

Before downsampling

Raw CPU metrics (1-second intervals):

  • 1 year = 31,536,000 rows per host
  • 10 hosts = 315,360,000 rows
  • Storage: ~20GB

After downsampling to hourly

Hourly aggregates:

  • 1 year = 8,760 rows per host
  • 10 hosts = 87,600 rows
  • Storage: ~50MB

Reduction: ~400x smaller while maintaining hourly trend visibility.

Multi-tier strategy

Combine different granularities for optimal storage:

# Tier 1: Keep raw data for 7 days
# Tier 2: Hourly aggregates for 90 days
requests.post("/api/v1/continuous_queries", json={
    "name": "cpu_hourly",
    "interval": "1h",
})

# Tier 3: Daily aggregates for 2 years
requests.post("/api/v1/continuous_queries", json={
    "name": "cpu_daily",
    "source_measurement": "cpu_hourly",  # Aggregate the hourly data
    "destination_measurement": "cpu_daily",
    "interval": "1d",
})

# Use retention policy to delete raw data after 7 days
requests.post("/api/v1/retention", json={
    "database": "telegraf",
    "measurement": "cpu",
    "retention_days": 7
})

Best practices

1. Start conservative

Begin with longer intervals and adjust based on actual needs:

# Start with hourly
{"interval": "1h"}

# If too coarse, reduce to 15 minutes
{"interval": "15m"}

2. Preserve source data initially

Keep raw data while testing aggregations:

# Create continuous query
create_query(...)

# Test aggregations thoroughly
execute_dry_run(...)
execute_for_real(...)

# Only after validation, apply retention to raw data
requests.post("/api/v1/retention", json={
    "measurement": "cpu",
    "retention_days": 30  # Keep raw for 30 days
})

3. Use dry run extensively

Always test queries with dry run before full execution:

# Test on small time range first
dry_run(start="2024-01-01", end="2024-01-02")

# Gradually expand
dry_run(start="2024-01-01", end="2024-01-07")

# Finally, full execution
execute(start="2024-01-01", end="2024-12-31")

4. Include sample counts

Track the number of raw samples in each aggregate:

SELECT
    date_trunc('hour', time) AS time,
    COUNT(*) AS sample_count,  -- Essential for data quality
    AVG(value) AS avg_value
FROM measurement
GROUP BY date_trunc('hour', time)

This helps identify:

  • Missing data (low sample counts)
  • Data quality issues
  • Unexpected patterns

5. Monitor execution performance

Track continuous query execution times:

result = execute_query(...)

print(f"Execution time: {result['execution_time_seconds']:.1f}s")
print(f"Rows written: {result['records_written']}")

# Alert if execution takes too long
if result['execution_time_seconds'] > 60:  # 1 minute
    print("Warning: Slow execution!")

6. Use appropriate intervals

Match intervals to data characteristics:

High-Frequency Data (IoT sensors at 1-second intervals):

  • 5-minute aggregates for recent analysis
  • Hourly aggregates for medium-term
  • Daily aggregates for long-term trends

Medium-Frequency Data (Application metrics at 1-minute intervals):

  • Hourly aggregates for recent analysis
  • Daily aggregates for long-term

Low-Frequency Data (Business metrics at hourly intervals):

  • Daily aggregates
  • Monthly aggregates for multi-year analysis

Troubleshooting

No rows written

Problem: Execution returns records_written: 0.

Solutions:

  • If start_time equals end_time (and execution_id is empty), there was nothing new since the previous run (Arc 27.01.1+). This is not an error; the next run picks up from last_processed_time.
  • Verify source measurement contains data in the specified time range
  • Check that the query syntax is correct
  • Ensure GROUP BY clause matches aggregation columns
  • Use dry run to inspect query results

Query syntax errors

Problem: Execution fails with SQL error.

Solutions:

  • Test the query directly using the /query endpoint
  • Verify column names exist in source measurement
  • Check for dialect-specific syntax requirements
  • Pass time to date_trunc() directly; epoch_us(time) is an integer, which date_trunc() rejects

Slow execution

Problem: Continuous query takes longer than expected.

Solutions:

  • Reduce the time range per execution
  • Ensure source measurement is properly compacted
  • Consider creating indexes on frequently grouped columns
  • Monitor query engine performance

Duplicate data

Problem: Re-running the query creates duplicate aggregates.

Solutions:

  • Delete destination measurement data before re-execution:
    requests.post("/api/v1/delete", json={
        "database": "telegraf",
        "measurement": "cpu_hourly",
        "where": f"time >= '{start_time}' AND time <= '{end_time}'"
    })
  • Or use UPSERT semantics if supported (future feature)

On this page