Incremental windows and cursors

An incremental load reads only part of its source: the rows inside a date window. This page explains how that window is built, how the loop type steps through it, and what a stream's cursor must be. To set the fields, see Configure a table; for the sync types themselves, see Choose a sync type.

How a run builds its window

• It ends on the run's date. The window always ends on the date the run is for.
• It covers the last Synchronize Days. The table's Date Column decides which rows fall inside the window. The day count is subtracted from the run's date to find where the window starts, so a smaller value narrows the window and a larger one reaches further back. Set it on Table > General (see Configure a table).
• It reads whole days. A run reads from the start of the first day to the end of the last day, so the window never covers part of a day. Which calendar its day boundaries follow depends on the source's datetime settings; see Timezones and datetime values.
• Rows outside it are not read. Only the days inside the window are loaded. What a run then replaces in the stored table is on Reloads and partitions.
• File tables. For a file table, the window can follow the date in each file's name instead of a column inside the files; the file rules are on Load files.

Step through the window with the loop type

Incremental Reload Loop (Truncate Insert) covers the same window as a plain incremental load, but walks it in steps of Loop Increment days.

• Each step covers whole days, and the next step starts where the last one ended, so the steps leave no day behind and load no day twice.
• The last step covers the days that remain, so it can be shorter than the others.
• What each step replaces in the stored table is on Reloads and partitions.

Cursors for API streams

For a table on a Connect Any API source, the window runs on the stream's cursor, not on any date column you choose.

• The stream's incremental option is the datetime cursor its recipe declares. The table's Date Column is that cursor; another date column cannot drive an incremental load.
• If the recipe declares no datetime cursor, the table offers no incremental type and can only be reloaded in full. See Connect any API and Choose a sync type.
• The cursor must hold a datetime or date value. When it does not, the run stops and its error in the Sync Log reads ANY API incremental column "…" must be a datetime/date column, not "…" (integer cursors are not supported yet; a string column needs format: date-time in the stream schema).
• A string cursor needs the date-time format in the stream schema. An integer cursor cannot drive an incremental load; its stream imports as Full. See Hosts, streams and data access.

Month, quarter and year cursors

• A recipe can declare its stream's cursor to step in whole calendar periods: months, quarters or years. When it does, the window snaps to whole calendar periods.
• The window's first day moves to the first day of the period: a monthly cursor starts the window on the first of the month, a quarterly one on the first day of the quarter, and a yearly one on the first day of the year.
• The period comes from the stream's recipe, not from the table's settings. See Connect any API for the recipe.

Questions, answered

How far back does one run read, and where do I change it?

The run reads the last Synchronize Days set on its table, ending on the date the run is for. The day count is subtracted from that date to find where the window starts, so a larger value reaches further back. Change it on Table > General; see Configure a table.

Do two runs read the same days?

Yes, where their windows overlap: each window ends on its own run's date, so the days both windows hold are read again. Rows outside the window are never read. For what a run does to those days in storage, see Reloads and partitions.

Can I pick a different date column for an API table?

No. A Connect Any API table can sync incrementally only on the datetime cursor its recipe declares, so the table's Date Column is that cursor. If the recipe declares no datetime cursor, the table has no incremental type at all. See Connect any API.

My API run says the incremental column must be a datetime/date column. What now?

The stream's cursor is not a datetime or date value, so the run stops instead of loading an empty window. Give a string cursor the date-time format in the stream schema, or use a datetime cursor; integer cursors cannot drive incremental loads. The full message is under Cursors for API streams.

Why does my monthly run start on the first of the month?

The stream's recipe declares its cursor to step in whole calendar periods, and a period cursor moves the window's first day to the first day of the period. Whether a stream steps in months, quarters or years comes from its recipe, not from the table's settings; see Connect any API.

Does an incremental file table read every file?

Not when its window follows _file_date: only files whose date falls inside the window are read, and a file whose name has no readable date stays outside it. See Load files.

Which date does the window end on?

The date the run is for: for a manual run, the date you pick in Synchronize Table, which the Sync Log records as Current Date. A scheduled run has its own date. See Run, stop and troubleshoot loads.

How do I set the step size of the loop type?

With Loop Increment on Table > General; it is shown only for the loop type. It sets how many days each step loads, while Synchronize Days still sets how deep the whole window is. See Configure a table.