As of Date Tables
An As of Date table contains a single column of dates used to construct the history in tables like PITs and Bridges.
The date range and the date interval size is often informed by the current reporting period and can be changed on the fly. An example of a usual date range could be all dates from the last 3 months.
Periodically, the As of Date table would be refreshed, to accommodate for the new reporting period.
Structure¶
The As of Date table consists of a single date/datetime column, and is currently generated using the dbt_utils.date_spine macro.
Note
dbt_utils is another package for dbt, provided by dbt Labs. The date_spine macro above is provided by this package. For more information, go read the date_spine docs here. AutomateDV includes dbt_utils as a dependency, and is installed alongside the AutomateDV package.
It will require the following input:
Date interval (datepart)¶
This is the parameter that defines the granularity of the date intervals. It needs to be a unit of time (e.g. "hour", "day", "week").
Start Date (start_date)¶
This parameter defines the earliest possible date for the date range. The value for start_date
will be included in the
list of values for AS_OF_DATE
, if it is aligned (i.e. of the same - or lower - granularity) with datepart
.
End Date (end_date)¶
The end_date
parameter defines the latest possible date for the date range. The value for end_date
will not be
included in the list of values for AS_OF_DATE
.
Setting up an As of Date model¶
Create a new dbt model. We'll call this one as_of_date
.
1 2 3 4 5 |
|
To create an As of Date model, simply copy and paste the above template into a model named as_of_date
(or similar).
With the help of dbt_utils date_spine
function, the template will generate an As of Date table using the parameters
provided in the next steps.
Materialisation¶
The recommended materialisation for an As of Date table is table
.
To refresh the As of Dates to reflect the new reporting period, you need to change the values in the parameters and run the dbt model again.
Adding the metadata¶
Let's look at the metadata we need to provide to the As of Dates template.
Parameter | Value |
---|---|
datepart | day |
start_date | to_date('2021/01/01', 'yyyy/mm/dd') |
end_date | to_date('2021/04/01', 'yyyy/mm/dd') |
When we provide the metadata above, our model should look like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Running dbt¶
With our metadata provided and our model complete, we can run dbt to create our As of Dates, as follows:
dbt run -s as_of_date
The resulting As of Date table will look like this:
AS_OF_DATE |
---|
2021-01-01 |
2021-01-02 |
. |
. |
2021-03-30 |
2021-03-31 |