Date and Month Spine

Create date and month level spine objects for use with date dimensions and time series analysis.

This Data Productivity Cloud transformation pipeline creates:

• A view named DATE_DIM_VIEW containing one record per day
• A view named MONTH_DIM_VIEW containing one record per month

Each object contains:

• A natural key column (either a date, or a month expressed as a string like 2020-04)
• A surrogate key column (the date or month value as an integer - e.g. 20200415 or 202004)
• Additional derived columns for a type 1 Date or Month dimension

There are many more ways to manage dimensions, especially with time variance. Take a look at these Data Productivity Cloud Slowly Changing Dimensions pipelines for reference.

Creating a date spine using Matillion Copilot

Use the following prompt:

Create a view named DATE_DIM_VIEW, containing one record for every day in the 2020's.
Let the DATE_DIM_VIEW have the following columns:
- DATE_KEY - the surrogate key, as an integer from the date YYYYMMDD
- DATE_VALUE - the date value
- Other columns suitable for a date dimension

Matillion Copilot Demonstration

Downloads

Licensed under: Matillion Free Subscription License

• Download Date-Month-Spine-DBKS.zip
  • Target: Databricks
• Download Date-Month-Spine-SF.zip
  • Target: Snowflake

Installation Instructions