Load a scheduled Apify web scrape into Postgres

Turn an Apify web scrape into a clean, queryable Postgres table on a daily schedule. This blueprint runs an Apify Store actor, pulls its dataset, reshapes the raw JSON into typed columns, and bulk-loads the rows into Postgres as a fully declarative ELT pipeline. It solves the common problem of scraped data that lands as messy JSON nobody can query: here every run produces a consistent table with an ingestion timestamp, hard cost caps that stop a runaway crawl from becoming a runaway bill, and Slack notifications on both success and failure.

How it works

1. A io.kestra.plugin.core.trigger.Schedule trigger fires every morning at 06:00 (cron 0 6 * * *).
2. The run_actor task runs an Apify actor with io.kestra.plugin.apify.actor.Run, defaulting to the Compass Google Maps Scraper. It enforces maxItems, maxTotalChargeUsd: 5, and memory: MB_2048 so pay-per-result cost stays bounded.
3. The get_dataset task fetches the actor's default dataset with io.kestra.plugin.apify.dataset.Get, and write_json persists it to Kestra internal storage as JSON.
4. The reshape task uses io.kestra.plugin.graalvm.python.FileTransform to map each raw record into typed, ordered fields (title, category, address, phone, website, total_score).
5. The to_csv task serializes the rows with io.kestra.plugin.serdes.csv.IonToCsv.
6. The create_table task runs io.kestra.plugin.jdbc.postgresql.Query to create public.apify_places if it does not exist, with an ingested_at TIMESTAMPTZ DEFAULT now().
7. The load_data task bulk-loads the CSV with io.kestra.plugin.jdbc.postgresql.CopyIn.
8. The notify_success task posts row count and reported cost to Slack, while an errors handler alerts the channel on any failure.

What you get

• A scheduled, repeatable scrape-to-warehouse pipeline with no glue scripts.
• Hard cost ceilings on every Apify run via maxItems and maxTotalChargeUsd.
• A typed Postgres table with an automatic ingestion timestamp.
• Bulk loading through CopyIn instead of slow row-by-row inserts.
• Success and failure notifications in Slack.

Who it's for

• Data engineers building lightweight ELT pipelines from web sources.
• Analysts who need scraped places, listings, or reviews in SQL.
• Growth and ops teams collecting lead or market data on a schedule.

Why orchestrate this with Kestra

Apify's own scheduler can run an actor, but it stops at the dataset. It cannot reshape the output, create a warehouse table, bulk-load Postgres, and notify Slack as one governed pipeline. Kestra wires these steps together declaratively in YAML, with event and schedule triggers, automatic retries, full execution lineage across the scrape, transform, and load, and a single errors block that catches failures anywhere in the flow. You get end-to-end orchestration that the scraping tool alone cannot provide.

Prerequisites

• A Kestra instance with the Apify, JDBC PostgreSQL, GraalVM Python, SerDes, and Slack plugins.
• An Apify account and API token.
• A reachable Postgres database.
• A Slack incoming webhook URL.

Secrets

• APIFY_API_TOKEN
• POSTGRES_HOST
• POSTGRES_USERNAME
• POSTGRES_PASSWORD
• SLACK_WEBHOOK_URL

Quick start

1. Add the five secrets above to your Kestra instance.
2. Add this flow and confirm the Apify, JDBC, GraalVM, SerDes, and Slack plugins are installed.
3. Run it manually with the default inputs (actor_id, search_query, max_results) to validate the path.
4. Check the public.apify_places table in Postgres and the success message in Slack.
5. Leave the daily schedule enabled, or adjust the cron to fit your cadence.

How to extend

• Swap the actor_id input for any of the actors in the Apify Store and adjust the reshape script and table DDL to match its output.
• Point the load at BigQuery, Snowflake, or DuckDB instead of Postgres.
• Raise or lower maxTotalChargeUsd and maxItems per use case.
• Add a dbt or DuckDB transform step downstream for modeling.

Links

• Apify plugin docs
• Apify orchestration with Kestra