sqlmesh icon indicating copy to clipboard operation
sqlmesh copied to clipboard

Add Incremental Macro

Open eakmanrq opened this issue 2 years ago • 0 comments

This adds a macro that users can utilize in their incremental pipelines to make it easier to filter tables based on a date range. By default it filters based on the start and end date of the model that is calling it but you can also override this behavior.

Example of it in use:

SELECT
  table2.id, 
  table1.ds
FROM
  @incremental('db.table1', start=-1) as table1
  left outer join @incremental('db.table2') as table2
    on table1.match_id = table2.match_id

Keyword arguments for the macro:

  • name [str]: Name of either the internal or external model being referenced
  • start [int, str]: The amount of to add to the start date from the calling model. If int, then the unit is assumed to match the cadence unit of the calling model. For example: If the model being defined runs daily a a value of -1 was provided then that would mean to subtract 1 day. If string then we allow the ability to say things like "-1 day" or other formats similar to what we support in the plan tool.
  • end [int, str]: Similar to the start field but for the end date.
  • column_name [str]: Required if referencing an external model. The name of the column to filter on.
  • column_format [str]: Required if referencing an external model. The format of the column being referenced.

When documenting incremental we want the documentation to lead with an example of defining an incremental pipeline using this macro but then follow that with information on how to define this manually if they want using the standard between filter.

eakmanrq avatar Feb 17 '23 23:02 eakmanrq