Schedule CloudQuery Data Ingestion into DuckDB for Analytics and Exploration

Move data from any CloudQuery source into DuckDB on a reliable schedule without writing custom extract-load scripts. This blueprint runs a daily CloudQuery sync that pulls records from the Hacker News API and lands them in a local DuckDB database, ready for analytics and ad hoc exploration. It solves the common problem of stitching together ELT extraction, scheduling, backfills, and failure handling: CloudQuery handles the connector logic while Kestra handles orchestration, time windowing, and recovery, all declared in YAML.

How it works

• A io.kestra.plugin.core.trigger.Schedule trigger fires every day (cron: "@daily") in the US/Eastern timezone.
• The hn_to_duckdb task (io.kestra.plugin.cloudquery.Sync) runs a CloudQuery sync with two configs: a source config for the cloudquery/hackernews plugin (all tables, item_concurrency: 100) and a destination config for cloudquery/duckdb writing to hn.db with write_mode: overwrite-delete-stale.
• The source start_time is computed as {{ trigger.date ?? execution.startDate | dateAdd(-1, 'DAYS') }}, so each run ingests roughly the last day of data and the same expression works for manual backfills.
• The CLOUDQUERY_API_KEY is passed to the task through the env map, which CloudQuery uses to authenticate premium connectors.
• With incremental: false, the run fetches a full time-bounded window; flip it to true to persist a sync cursor and fetch only new records on subsequent runs.

What you get

• A repeatable, scheduled extract-load pipeline from an external API into DuckDB.
• Time-windowed ingestion that avoids reprocessing the entire history on every run.
• High-throughput extraction via configurable item_concurrency.
• A clean separation between connector config (CloudQuery) and orchestration (Kestra).

Who it's for

• Data and analytics engineers building lightweight ELT pipelines.
• Teams using DuckDB for local or embedded analytics and warehousing.
• Anyone replacing brittle cron-plus-script ingestion jobs with declarative flows.

Why orchestrate this with Kestra

CloudQuery is excellent at extraction, but its own runner cannot give you event-driven scheduling, automatic retries on transient failures, execution-level observability, and data lineage across many pipelines in one place. With Kestra you declare the schedule as a first-class trigger, recompute the start_time window from the trigger date for clean backfills, add retries and alerting around the sync, and chain this ingestion into downstream transformation or quality tasks. Everything stays in version-controlled YAML instead of scattered config files and shell wrappers.

Prerequisites

• A Kestra instance with the CloudQuery plugin available.
• A CloudQuery API key for premium connectors (generate one at https://docs.cloudquery.io/docs/deployment/generate-api-key).

Secrets

• CLOUDQUERY_API_KEY: the CloudQuery API key used to authenticate connectors. Reference it as {{ secret('CLOUDQUERY_API_KEY') }} in the task env instead of hardcoding the value.

Quick start

1. Add the CLOUDQUERY_API_KEY secret to your Kestra instance. The task env already reads it as {{ secret('CLOUDQUERY_API_KEY') }}.
2. Add the flow to a namespace and save it.
3. Trigger an execution manually to confirm the sync writes to hn.db.
4. Let the @daily schedule take over for ongoing ingestion.

How to extend

• Swap the cloudquery/hackernews source for any other CloudQuery connector (browse https://www.cloudquery.io/integrations and paste its YAML config).
• Point the destination at Postgres, BigQuery, or Snowflake instead of DuckDB by changing the destination config.
• Set incremental: true to switch from time-windowed to cursor-based ingestion.
• Add a downstream DuckDB or dbt task to transform the freshly loaded tables.

Links

• CloudQuery plugin documentation