Implement a slowly changing dimension with MaxCompute

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

2. Create a workflow.

   1. Hover over the icon and click Create Workflow.

   2. In the Create Workflow dialog box, enter a Workflow Name and Description. For this example, name the workflow Tutorial_SCD_Implementation.

   3. Click Create.

3. Create nodes.

   In the directory tree on the left, double-click the workflow that you created in Step 2 to open its panel. Then, drag components from the left pane and connect them on the canvas to build the workflow.

   This example uses two types of nodes: a zero load node and an ODPS SQL node.

   • The zero load node acts as the starting point for the entire SCD implementation, helping to manage the overall workflow.

   • The ODPS SQL node executes SQL tasks to load data into the SCD table.

4. Preview the task workflow.

   This tutorial will create the following workflow. For clarity, this example uses the node group feature to group related nodes. The code for each node is provided in the subsequent steps.

   The Tutorial_SCD_Implementation workflow contains the following nodes in order of dependency: the zero load node SCD_Implementation_Description → the ODPS SQL node ods_order_di (in the Prepare_Incremental_Table node group) → the ODPS SQL node dwd_order (in the Implement_SCD_Table node group) → the ODPS SQL node Query_SCD_Table. In DataStudio, after opening the workflow, drag the zero load node and ODPS SQL node from the node list on the left to the canvas and connect them to establish dependencies.

Task development: Prepare incremental data table

• In the data preparation stage, you need to create two types of nodes:

  • Create a zero load node: SCD_Implementation_Description. This node manages and describes the workflow.

  • Create an ODPS SQL node: ods_order_di. This node generates the source table for order transactions.

• On the workflow canvas, connect the SCD_Implementation_Description zero load node to the ods_order_di SQL node within the Prepare_Incremental_Table node group to configure the dependency.

dwd_order

• Table data: The order fact table (SCD table) stores all active and inactive data. It includes the effective start date (start_date), effective end date (end_date), business key ID, and the status field for tracking order status changes.

• On the editor tab of the dwd_order node, enter the following sample code:

  -- 1. Create the order fact table to store all historical data using the SCD method.
  -- Note: The SCD table adds lifecycle fields: start_date (effective date) and end_date (expiration date). 
  --       An end_date of '9999-12-31' indicates that the record is currently valid.
  CREATE TABLE IF NOT EXISTS dwd_order
  (
      ID            BIGINT COMMENT 'Order ID'
      ,gmt_create   DATE COMMENT 'Creation time, in yyyy-mm-dd format'
      ,gmt_modified DATE COMMENT 'Update time, in yyyy-mm-dd format'
      ,`status`     STRING COMMENT 'Status'
      ,start_date   DATE COMMENT 'Effective date, in yyyy-mm-dd format'
      ,end_date     DATE COMMENT 'Expiration date, in yyyy-mm-dd format. 9999-12-31 indicates the record is active.'
  )
  COMMENT 'Fact detail table for transaction orders (SCD table)'
  ;
  -- 2. SCD storage logic: Insert all new or updated data from ods_order_di for the current business date into dwd_order as new, valid records.
  --    Update the previously active records in dwd_order that have been changed to be inactive.
  INSERT OVERWRITE TABLE dwd_order
  SELECT  id
          ,gmt_create
          ,gmt_modified
          ,`status`
          ,start_date
          ,end_date
  FROM    (
              SELECT  id
                      ,gmt_create
                      ,gmt_modified
                      ,`status`
                      ,start_date
                      ,end_date
                      -- 2.3 Support re-runs: Use a window function to partition by id, gmt_create, gmt_modified, and `status`. Sort by end_date in descending order within the partition and take only the first row.
                      ,ROW_NUMBER() OVER (DISTRIBUTE BY id,gmt_create,gmt_modified,`status` SORT BY end_date DESC ) AS row_num
              FROM    (
                          -- 2.1 Invalidate old records: For records in dwd_order that have been updated, change their status from active to inactive.
                          SELECT  a.id AS id
                                  ,a.gmt_create AS gmt_create
                                  ,a.gmt_modified AS gmt_modified
                                  ,a.`status` AS `status`
                                  ,a.start_date AS start_date
                                  ,CASE   WHEN b.id IS NOT NULL
                                              -- AND CAST(a.end_date AS STRING) > '${biz_date}' THEN DATE'${biz_date}' 
                                              AND CAST(a.end_date AS STRING) == '9999-12-31' THEN DATE'${biz_date}' 
                                          ELSE a.end_date
                                  END AS end_date
                          -- 2.4 Note on scheduling dependencies: Because the calculation for dwd_order on the current day depends on its own output from the previous day, we configure a dependency on "Instances of Current Node" in the Properties > Cross-cycle Dependency section. 
                          FROM    dwd_order AS a
                          LEFT JOIN   (
                                          SELECT  id
                                                  ,gmt_create
                                                  ,gmt_modified
                                                  ,`status`
                                          FROM    ods_order_di
                                          WHERE   pt == '${biz_date}'
                                      ) b
                          ON      a.id == b.id
                          UNION ALL 
                          -- 2.2 Insert new and updated records: Insert all new or updated records from ods_order_di for the current business date into dwd_order as active records.
                          SELECT  id
                                  ,gmt_create
                                  ,gmt_modified
                                  ,`status`
                                  ,DATE'${biz_date}' AS start_date
                                  ,DATE'9999-12-31' AS end_date
                          FROM    ods_order_di
                          WHERE   pt == '${biz_date}'
                      ) 
          ) with_row_num_tb
  -- Support re-runs
  WHERE   row_num == 1
  ;

• Configure scheduling. The following are the key configurations. You can keep the default values for other parameters.

  • Scheduling Parameter: In the Scheduling > Scheduling Parameter section, click Load Parameters in Code to automatically generate the parameter configuration. In the Parameter Value field, enter $[yyyy-mm-dd-1]. For more information about supported formats for scheduling parameters, see Supported formats of scheduling parameters.

    Note

    You can also click Define by expression to manually add the scheduling parameter biz_date=$[yyyy-mm-dd-1]

  • Configure the Cross-cycle Scheduling Dependency. The data processing for the dwd_order table on a given day depends on its own output from the previous day. To ensure data accuracy and continuity, you must configure a self-dependency.

    In the Scheduling > Scheduling Dependency > Cross-cycle Dependency (Original Previous-cycle Dependency) section, select the This node checkbox. This ensures that the data for the current cycle is loaded only after the data from the previous cycle has been successfully processed. This ensures the integrity of the data processing flow.

SCD table data loading logic

• Existing active records in the dwd_order table that are updated on the current business date are marked as inactive.

• All new records from the ods_order_di table for the current business date are inserted into the dwd_order table as active records.

The following steps use the data for the business date of October 5 as an example:

1. Original data: On October 4, the SCD table contains a record for order ID 210001 with the status Created.

2. Status change: On October 5, the status of this order is updated to Paid.

3. Data comparison logic:

   1. Join all active historical data from the SCD table on October 4 with the new data from the 2023-10-05 partition of the incremental table.

   2. Check for status updates: If the order with ID 210001 exists in the SCD table on October 4 with an end_date of 9999-12-31 (meaning it is active), and it also appears in the 2023-10-05 partition of the incremental table, its status has been updated.

4. Update process:

   1. The end_date of the original record in the SCD table is updated from 9999-12-31 to the current business date, 2023-10-05.

   2. If the order ID does not appear in the 2023-10-05 partition of the incremental table, it means there is no update, and the original record remains unchanged.

5. Data overwrite:

   After completing these steps, the processed historical data from the SCD table and all new data from the incremental table are written back into the SCD table, producing the final SCD data for October 5.