Choose how a database dataset refreshes
When you save a database query as a dataset, you choose when it refreshes and what each refresh does: replace every row, or add only new rows told apart by a key column. You set this in the Save as a dataset dialog of the connector workspace, after querying the database, and can change it later in the dataset's settings.
Choose what happens on each refresh
- Click Save as dataset when the rows look right.
- In Save as a dataset, enter a Name and pick a folder.
- Under Keep it up to date, choose Daily at 06:00 or Only when I ask.
- The dialog states what each refresh does. Click Change to pick another option under On each refresh (see the table below).
- For Add only new rows and Keep only the newest rows, pick a Key column: a column whose values only ever go up, like an ID or a date.
- Click Save as dataset.
| Option | What it does | When to use |
|---|---|---|
| Replace all rows | Matches the source after every refresh | Most tables, lookups and snapshots |
| Add only new rows | Keeps what you have and adds rows that are new, told apart by a key column | Building up history from a table that only grows |
| Add every row again | Adds a full copy of the query result on each refresh. Shown when the result has no column that can serve as a key. | Rare: when you want a copy per refresh |
| Keep only the newest rows | Empties the dataset, then brings in rows since the last refresh. Needs a key column. | Keeping only the latest batch |
Without a key column, Add only new rows brings in a full copy each time, and Keep only the newest rows can't be saved.
Choosing Daily at 06:00 also creates a Dataset Refresh automation for the dataset, with its first run the next morning.
Worked example: keep a growing orders table up to date
Your orders table only ever gets new rows, and order_id always goes up. Choose Daily at 06:00, then Add only new rows with Key column order_id. Each morning Mammoth adds only orders with an ID above the last one it imported.
Change the update method after saving
A saved connector dataset has an Update method you can change later from the dataset's settings (see Dataset settings and history). The change applies from the next refresh; your current data stays as it is until then.
For connectors that support a key column (including PostgreSQL, MySQL, SQL Server, Amazon Redshift, Snowflake, Google BigQuery, Databricks, Apache Hadoop and MongoDB):
| Option | What it does |
|---|---|
| Replace all data | Rebuilds the dataset on every refresh, so it always matches the source |
| Add new rows only | Each refresh adds the rows that appeared since the last one, using a Key column |
| Keep only the newest rows | Each refresh empties the dataset and brings in only the rows since the last one |
For connectors whose refresh doesn't use a key column, including SAP HANA, Oracle Database, Microsoft Dynamics, Monday.com, NetSuite and Google Ads, the options are Replace all data and Add to existing data, which adds every row it brings in.
With a key column, only rows above the last value are imported. Rows changed or deleted at the source afterwards are not updated in Mammoth.
Related
- Query a database and choose how it refreshes: write the query you save.
- Refresh a dataset: schedule refreshes.
- Batches: see and fix what arrived: each refresh adds or replaces batches.
- Speed up pipelines and connectors: incremental refresh and query filtering.
Something wrong? support@mammoth.io