Archive Slack Channel History into BigQuery

Archive Slack channel history into BigQuery so your team keeps a durable, queryable record of conversations for analytics, compliance, and long-term retention. Slack's free and standard plans cap how far back you can search, and the in-app history eventually rolls off, which makes audits, usage analysis, and incident reviews hard. This blueprint pulls messages out of a Slack channel, converts them into a BigQuery-friendly format, and loads them into a warehouse table on a daily schedule, turning ephemeral chat into structured data you can query with SQL.

How it works

1. export_history uses io.kestra.plugin.slack.app.conversations.History to fetch the channel's messages to Kestra internal storage. It reads the channel from inputs.channel_id and limits the window with oldest set to {{ now() | dateAdd(-1, 'DAYS') }}, capturing the previous day.
2. convert_to_json uses io.kestra.plugin.serdes.json.IonToJson to transform the exported Ion file into newline-delimited JSON, because BigQuery Load accepts CSV, JSON, AVRO, PARQUET, and ORC but not Ion.
3. load_to_bigquery uses io.kestra.plugin.gcp.bigquery.Load to load the JSON into the table from inputs.destination_table, using format: JSON and authenticating with the service account and project from secrets.
4. The daily_export trigger (io.kestra.plugin.core.trigger.Schedule) runs the flow every morning at 0 6 * * *. It ships disabled so you can review it before enabling.

What you get

• A queryable BigQuery table of Slack messages refreshed daily.
• Conversation history that outlives Slack's in-app retention window.
• Clean newline-delimited JSON ready for SQL analysis and BI tools.
• A parameterized flow driven by channel_id and destination_table inputs.

Who it's for

• Data and analytics engineers building reporting on team communication.
• Platform and IT teams meeting audit or retention requirements.
• Community or developer-relations teams measuring channel activity.

Why orchestrate this with Kestra

Slack has no native scheduler to push history into a warehouse, and BigQuery cannot reach into Slack on its own. Kestra fills that gap with an event-driven Schedule trigger, automatic retries on transient API or load failures, full execution lineage across the export, convert, and load steps, and a declarative YAML definition you can version, review, and reuse. The chained outputs between tasks keep the pipeline traceable end to end.

Prerequisites

• A Slack app with access to the channel you want to archive.
• An existing BigQuery dataset for the destination table.
• A GCP service account with permission to load into that dataset.

Secrets

• SLACK_TOKEN: Slack token with channels:history (and groups:history for private channels).
• GCP_SERVICE_ACCOUNT: service account key JSON with BigQuery load permissions.
• GCP_PROJECT_ID: GCP project hosting the destination dataset.

Quick start

1. Add SLACK_TOKEN, GCP_SERVICE_ACCOUNT, and GCP_PROJECT_ID as secrets in your Kestra namespace.
2. Pass the Slack channel_id (right-click the channel, Copy Link, take the trailing ID).
3. Set destination_table in project.dataset.table form.
4. Run once to verify the load, then enable the daily_export trigger.

How to extend

• Widen the oldest window or remove it to backfill the full channel history.
• Loop over multiple channels with a parallel or ForEach task.
• Add a downstream io.kestra.plugin.gcp.bigquery.Query task to transform or deduplicate rows.
• Notify a channel when the load completes for a closed feedback loop.

Links

• Google Cloud plugin docs
• BigQuery orchestration
• Slack orchestration