The insert by period materialization for TSQL

The insert_by_period materialization provides the ability to iteratively insert rows into a table one period (day, week, month, year etc) at a time.

Basic functionality

The way this works is that you define the config block and a placeholder flag in the following way:

{{
  config(
    materialized = 'insert_by_period',
    period = 'day',
    timestamp_field = 'SnapshotDate',
    start_date = '2018-01-01',
    stop_date = '2018-01-10',
  )
}}

with payments as (

  select *
  from {{ ref('payments') }}
  where __PERIOD_FILTER__ -- This will be replaced with a filter in the materialization code

)

Config values defined:

materialized = insert_by_period
period = A calendar period such as day, week, month, year.
timestamp_field = A datetime or date field that contains the dates you want to iterate through.
start_date = The period to start at.
stop_date = The period to stop at. If this is not defined, the current date is chosen.

Extended functionality

In its basic form, the insert_by_period materialization uses an "append" strategy. However if you specify a unique_key, it will user a incremental-like "upsert" strategy.

Help required

This is my first PR to a public repository so please feel free to let me know if there are specific things I should or should not be doing.
I have no idea how to write a test for this and where to put it. Internally, we have testing models that we build off our data but I don't know how to do that externally.

Thanks

dbt-msft / tsql-utils

The insert by period materialization for TSQL #56