Schedule an Incremental Data Refresh
Fetch a file on a schedule, merge it into a table by key so repeats never duplicate, validate it, refresh a dashboard, then backfill a past period.
What you will build
A pipeline that keeps a table up to date from a public file, checks the table and refreshes a dashboard. It uses idempotent steps, so running it twice is always safe.
Before you start
- A public CSV address that changes over time, or any CSV to test with
- A dataset you can use as the dashboard's source (the pipeline will create the table; you can build a chart on it afterwards)
- The Studio must stay open for scheduled runs to happen
Step 1: Start from the template
Open Pipelines → Templates and choose Keep a table up to date from a source. It creates three steps: Fetch from URL, Upsert Rows and Validate Data.
Step 2: Set the source
Click Fetch from URL and paste the address. The step only publishes a new version when the downloaded content changed.
Step 3: Choose the key
Click Upsert Rows. Choose the key column(s), the columns that identify a row. A row whose key already exists replaces the old one; a row with a new key is added; everything else stays. Because merging the same rows twice gives the same table, a retried or repeated step cannot duplicate anything, and a run that changes nothing adds no version.
Step 4: Add rules
Click Validate Data and add rules: required columns, a minimum row count, or constraints saved on the dataset in the schema editor. Leave the action on fail, so a broken rule stops the run before anything downstream sees the data.
Step 5: Run it once
Press Publish & Run. Open Tables kept up to date by pipelines from the library and you should see the new table. Open it in the Data Hub: it is a dataset with a version per change.
Step 6: Schedule it
Open the Schedule section and choose a timed trigger, such as Every hour or Daily at a time you pick, or a cron expression (the next fire time is previewed). Alternatively choose When a dataset gets a new version and pick the dataset to watch. Decide what a missed run should do: catch up once or skip. Runs only happen while the Studio is open in a tab.
Step 7: Take only what is new
For a source that grows, add New Rows before Upsert. It passes on only the rows that arrived since the last successful run. When there is nothing new, the step finishes without output and the steps that depend on it are skipped, which is not a failure.
Step 8: Refresh the dashboard
Add Refresh Dashboard after the validation and choose the dataset the dashboard reads. It checks that the dashboard's fields still exist before applying the new data.
Step 9: Backfill a period
To fill in history, open Backfill, give a first date, a date to stop before and a window length (days, weeks or months). The pipeline runs once per window, oldest first, setting "from" and "to" parameters you can use in a filter. Swap Upsert for Replace Partitions keyed on the date column, so re-running a window replaces its rows instead of adding to them.
Step 10: Keep an eye on it
Open the Job Centre to watch runs and retry failures. In Settings → Data and pipelines you can change how many runs are kept and how failed steps retry. In Settings → Notifications you can ask for a desktop alert when a run ends.