Loading…
Recipe: Data sync and ETL to a warehouse
TL;DR: On a schedule, this playbook pulls records from a source API, transforms them in a Code step, and loads them into your warehouse (Snowflake or BigQuery) with incremental updates. Trigger is a schedule; output is fresh, transformed data in the warehouse.
Who this is for
Data teams that move data between systems and a warehouse on a recurring basis.
What you'll need
- Connectors: Source API (HTTP), Snowflake or BigQuery.
- Secrets: Source API key, warehouse credentials.
- Trigger: Schedule (cron) for the sync cadence.
How to build it with Ask Ballet
- Open the Console on Home.
- Paste the prompt below; set source, destination table, and cadence.
- Review the plan and confirm.
- Connect the source and warehouse, then set the schedule.
Build a playbook that runs every hour. Call the Stripe API for charges created since
the last successful run (use a stored cursor/timestamp). Transform each record into our
warehouse schema in a Code step (flatten nested fields, convert currency to cents,
normalize timestamps to UTC). Upsert the rows into the Snowflake table analytics.charges
keyed by charge id. Store the new high-water timestamp for the next run and log the row
count.
What the playbook does
- Schedule trigger — runs on the configured interval.
- Code step (read cursor) — loads the last high-water mark.
- HTTP step (extract) — pulls new/changed records from the source.
- Code step (transform) — maps records to the warehouse schema.
- HTTP/Code step (load) — upserts into the warehouse and saves the new cursor.
Variations and tips
- Use incremental sync with a high-water mark to avoid reprocessing all data.
- Make the load idempotent (upsert by key) so retries are safe.
- Surface the loaded tables in Scheduled reports.