# SCDs

{% hint style="warning" %}

#### Workshop - Slowly changing dimensions (SCDs)

Maintain a dimension table as attributes change over time.\
Use **Dimension Lookup/Update** for both **Type 1** and **Type 2** behavior.

**What you’ll do**

* Create a `DIM_SCD` table in `sampledata`
* Generate test rows with **Data Grid**
* Add timestamps with **Get System Info**
* Configure **Dimension Lookup/Update** for:
  * Lookup mode (read-only enrichment)
  * Update mode (writes)
* Test field strategies:
  * **Update** (Type 1 overwrite)
  * **Insert** (Type 2 new version)
  * **Punch through** (update all versions)

**Prerequisites**

* PDI installed and running
* A working MySQL connection to `sampledata`
* Familiarity with natural keys vs surrogate keys

**Estimated time:** 40 minutes
{% endhint %}

{% embed url="<https://www.loom.com/share/3ec2d5123814460d92085851c18daaee?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
Dimension Lookup
{% endembed %}

***

{% hint style="info" %}
**Workshop files**

Download the following files.

Keep the filenames unchanged.

Save them in your workshop folder.
{% endhint %}

{% file src="<https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2FkwOWs8BZ4RvNsoPsUbQg%2Fdim_scd%20script.txt?alt=media&token=4c921a7c-1d13-4062-99bd-5f107118b9dc>" %}

{% file src="<https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2FlCykYbcIbOEle9YiLDpi%2Ftr_scd.ktr?alt=media&token=588aea7a-6a25-42ad-a4c2-391ecc835da1>" %}

***

{% hint style="info" %}
**Create a new transformation**

Use any of these options to open a new transformation tab:

* Select **File** > **New** > **Transformation**
* Use `Ctrl+N` (Windows/Linux) or `Cmd+N` (macOS)
  {% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-21f920f3328549ec4aa8c352173439652e11d937%2FDIM_SCD%20trans.png?alt=media" alt=""><figcaption><p>tr_scd</p></figcaption></figure>

***

{% tabs %}
{% tab title="Transformation" %}
{% hint style="danger" %}

#### Transformation

Ensure the tr\_scd.ktr has been created before you explore the Workflows.
{% endhint %}

{% tabs %}
{% tab title="1. DIM\_SCD Table" %}
{% hint style="info" %}

#### **SCD table**

First step in these series of workshops, is to create a simple DIM\_SCD table that has the required fields to illustrate a Type 1 change - just overwrite the value - and Type 2 - where you need to record when any change in the value occurs.
{% endhint %}

1. Start PDI.

{% hint style="info" %}
{% tabs %}
{% tab title="Windows (PowerShell)" %}

```powershell
Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat
```

{% endtab %}

{% tab title="macOS / Linux" %}

```bash
cd ~/Pentaho/design-tools/data-integration
./spoon.sh
```

{% endtab %}
{% endtabs %}
{% endhint %}

{% hint style="warning" %}
Create the Table in MySQL sampledata database.
{% endhint %}

2. In your DB management tool, execute the following statement.

```sql
CREATE TABLE `DIM_SCD` (
  `TK` bigint(10) NOT NULL,
  `version` int(11) DEFAULT '0',
  `id` int(11) DEFAULT '0',
  `city` tinytext,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  PRIMARY KEY (`TK`),
  KEY `idx_DIM_SCD_lookup` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
```

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-a3c0f798c4f255b8e2b896105d3bd7d5affcdd81%2FDIM_SCD%20table.png?alt=media" alt=""><figcaption><p>DIM_SCD</p></figcaption></figure>
{% endtab %}

{% tab title="2. Data Grid" %}
{% hint style="info" %}

#### **Data grid**

This step allows you to enter a static list of rows in a grid. This is usually done for testing, reference or demo purposes.
{% endhint %}

1. Drag the Data Grid step onto the canvas.
2. Open the Data Grid properties dialog box.
3. Ensure the following details are configured, as outlined below:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-3b17c9e3af27ee415e8fb56f8c14269ea9431a56%2Fdg%20scd.png?alt=media" alt=""><figcaption><p>Data grid - meta</p></figcaption></figure>

4. On the Data tab enter the following values:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-d1ace024bfc8b1b64c102879a3ad596938a89abe%2Fdg%20data%20scd.png?alt=media" alt=""><figcaption><p>Data grid - data</p></figcaption></figure>
{% endtab %}

{% tab title="3. Get System Info" %}
{% hint style="info" %}

#### **Get system info**

The Get System Info step retrieves information from the Kettle environment.

This step generates a single row with the fields containing the requested information. It also accepts input rows.
{% endhint %}

1. Drag the Get System Info step onto the canvas.
2. Open the Get System Info properties dialog box.
3. Ensure the following details are configured, as outlined below:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-f8276f2062f6039c92ec55a90113aeebecbba4cc%2Fimage.png?alt=media" alt="" width="563"><figcaption><p>Get system info</p></figcaption></figure>
{% endtab %}

{% tab title="4. Dimension lookup/update" %}
{% hint style="info" %}

#### **Dimension lookup/update**

The Dimension Lookup/Update step allows you to implement Ralph Kimball's slowly changing dimension for both types: Type I (update) and Type II (insert) together with some additional functions. Not only can you use this step to update a dimension table, it may also be used to look up values in a dimension.
{% endhint %}

1. Drag the Dimension lookup/update step onto the canvas.
2. Open the Dimension lookup/update properties dialog box.
3. Ensure the following details are configured, as outlined below:

* **Connection:** your `sampledata` connection
* **Target table:** `DIM_SCD`
* **Technical key field:** `TK`
* **Version field:** `version`
* **Lookup key:** `id`

{% hint style="info" %}
You will switch between **Lookup mode** and **Update mode** in the workflows below.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}

{% tab title="Workflow 1 - Lookup" %}
{% hint style="info" %}
**Lookup**

This step is rarely used for lookups because specific steps exist. However, it does illustrate how referential integrity is maintained.
{% endhint %}

1. Add ‘London’ to the Data Grid.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-34f1cf94f99f9fb18ee7ada03b6bddf5674bfc26%2FDG%20-%20lookup.png?alt=media" alt=""><figcaption><p>Data grid - lookup</p></figcaption></figure>

{% hint style="warning" %}
To initially configure the fields set the step to Update mode.

Once completed remember to reset to lookup mode.
{% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-dc69fa129ea1f9f51a8a57c56603aa77707b05dc%2Fconfigure%20scd.png?alt=media" alt=""><figcaption><p>Configure fields</p></figcaption></figure>

2. Ensure that the Dimension Lookup / Update is set to: Lookup Mode.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-1c3ea70c4b83d5ceed6419826d5db8706b298e29%2Fdim%20scd%20set%20lookup.png?alt=media" alt=""><figcaption><p>set lookup mode</p></figcaption></figure>

{% hint style="info" %}
Sets the step to Update Mode with lookup keys: id

There’s no point adding the `last_update` field because we’re dealing with Type 1 changes.
{% endhint %}

3. Click on the Fields tab.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-a271878f76a032b1f9625648dfa7eb569eb673f5%2Fdim%20scd%20city%20string.png?alt=media" alt=""><figcaption><p>Set city to string</p></figcaption></figure>

***

{% hint style="info" %}
**Run the transformation**

Preview the output from **Dimension lookup/update**.
{% endhint %}

1. Click the Run button in the Canvas Toolbar.
2. Preview data for the **Dimension lookup/update** step.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-a375a9bc6ec294f8d50c41fc376178f0d162eb4d%2Fview%20lookup%20data.png?alt=media" alt=""><figcaption><p>Preview data</p></figcaption></figure>

{% hint style="info" %}
**Why does London have a TK 0 and a value of NULL?**

To maintain referential integrity, the record is assigned a TK 0, and as it doesn’t exist in the database, the value returned is NULL. As this is a lookup, no record is written to the database table.
{% endhint %}
{% endtab %}

{% tab title="Workflow 2 - Type 1" %}
{% hint style="info" %}
The Dimension Lookup/Update step allows you to implement Ralph Kimball's slowly changing dimension for both types:

**Type 1**

Overwriting the old value. In this method, no history of dimension changes is kept in the database. The old dimension value is simply overwritten with the new one. This type is easy to maintain and is often used for data where changes are caused by processing corrections (for example, removal of special characters, correcting spelling errors).
{% endhint %}

1. Open the Dimension Lookup / Update properties dialog box.
2. Ensure the following details are configured, as outlined below:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-cdd29c4f78cec2fae7634e61b1b4adb8d553bcbd%2Fdim%20scd%20update.png?alt=media" alt=""><figcaption><p>Type 1</p></figcaption></figure>

3. Click on the Get Fields button.

{% hint style="info" %}
This will add the key fields used in the Lookup.

The keys that map dimension table rows to stream rows are: `id`
{% endhint %}

4. Click on the Fields tab and map the stream name field to the dimension name field and ensure:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-47511818addd65baf29d2916de52cffe7a81c530%2Fdim%20scd%20fields.png?alt=media" alt=""><figcaption><p>Field mapping and set database strategy</p></figcaption></figure>

{% hint style="info" %}
There are several options available to insert / update the dimension record (row).

**Insert:**

This option implements a Type I & II slowly changing dimension policy. If the difference is detected for one or more mappings that have the Insert option, then a row is added to the dimension table.

**Update:**

This option simply updates the matched row. It can be used to implement a Type I slowly changing dimension.

**Punch through:**

The punch through option also performs an update. But instead of only updating the matched dimension row, it will update all versions of the row in a Type II slowly changing dimension.

**Date of last insert or update (without stream field as source):**

Use this option to let the step automatically maintain a date field that records the date of the insert or update using the system date field as source.

**Date of last insert (without stream field as source):**

Use this option to let the step automatically maintain a date field that records the date of the last insert using the system date field as source.

**Date of last update (without stream field as source):**

Use this option to let the step automatically maintain a date field that records the date of the last update using the system date field as source.

**Last version (without stream field as source):**

Use this option to let the step automatically maintain a flag that indicates if the row is the last version.
{% endhint %}

***

{% hint style="success" %}
**RUN Transformation**

At the moment it's all Type I changes .. Insert / Update record and Update when that record change occurred.
{% endhint %}

1. Click the Run button in the Canvas Toolbar.
2. Click on the Preview tab:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-4fce4756be128c8cafe55c058b4a3395eeff638b%2Fpreview%20scd.png?alt=media" alt=""><figcaption><p>Preview data</p></figcaption></figure>

3. Examine the table in your SQL Query Tool:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-4873176f010ed7708b9bf1417b9679efb93dd2b8%2Fdim%20scd%20.png?alt=media" alt=""><figcaption><p>DIM_SCD - view data</p></figcaption></figure>

{% hint style="info" %}
**So what's happening behind TK 0?**

Kettle automatically inserts an additional record with a technical key of value 0 (for default or unknown values). This will only happen in the first execution. Below this record, you find the one record (London) from our sample dataset.

In update mode (update option is enabled) the step first performs a lookup of the dimension entry. The result of the lookup is different though. Not only the technical key is retrieved from the query, but also the dimension attribute fields. A field-by-field comparison then follows. The result can be one of the following situations:

* The record was not found, we insert a new record in the table.
* The record was found and any of the following is true:
* One or more attributes were different and had an "Insert" (Kimball Type II) setting: A new dimension record version is inserted.
* One or more attributes were different and had a "Punch through" (Kimball Type I) setting: These attributes in all the dimension record versions are updated.
* One or more attributes were different and had an "Update" setting: These attributes in the last dimension record version are updated.
* All the attributes (fields) were identical: No updates or insertions are performed.

If you mix Insert, Punch Through and Update options in this step, this algorithm acts like a Hybrid Slowly Changing Dimension. (it is no longer just Type I or II, it is a combination)
{% endhint %}

**Try different scenarios**

{% tabs %}
{% tab title="Insert / Update" %}

1. Double-click on the Data Grid step.
2. Add the following City: Madrid

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-be901db4e1ec0ca8aed087061457de08625967cd%2FDG%20-%20update.png?alt=media" alt=""><figcaption><p>data Grid - Madrid</p></figcaption></figure>

3. Double-click on the Dimension lookup/update step.
4. Click on the Fields tab and set the following:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-3154c33e2e1ff301029d42a8b4108eebdd4a6c37%2Fupdate%20fields.png?alt=media" alt=""><figcaption><p>Field mapping and set database operation</p></figcaption></figure>

5. Execute the transformation and view the results.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-5e346b95823120afb25bca093654b268a2c073b9%2FDIM_SCD%20update.png?alt=media" alt=""><figcaption><p>DIM_SCD update</p></figcaption></figure>

{% hint style="info" %}
Note that if the record does not exist (Madrid), then it gets inserted. Both records are updated with the same last\_update timestamp.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}

{% tab title="Workflow 3 - Type 2" %}
{% hint style="info" %}
The Dimension Lookup/Update step allows you to implement Ralph Kimball's slowly changing dimension for both types:

**Type 2**

Creating a new additional record. In this methodology, all history of dimension changes are kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key (or another durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start\_date and end\_date, the end\_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in Type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.
{% endhint %}

1. Double-click on the Data Grid step.
2. Add the following City: Pariss (intentionally misspelled)

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-adb1a8b3c9d17252cfe92ccc06cd9ddf24cb1b38%2FDG%20-%20Pariss.png?alt=media" alt=""><figcaption><p>Data grid - Pariss</p></figcaption></figure>

3. Keep the Fields strategy the same:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-3154c33e2e1ff301029d42a8b4108eebdd4a6c37%2Fupdate%20fields.png?alt=media" alt=""><figcaption><p>Field mapping and set database strategy</p></figcaption></figure>

**RUN Transformation**

{% hint style="success" %}
At the moment it's all Type I changes .. Insert / Update record and Update when that record change occurred.
{% endhint %}

1. Click the Run button in the Canvas Toolbar.
2. Click on the Preview tab:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-dc96dcc4e980bb0e8f93553f03755f05391873f3%2Fpreview%20data%20-%20pariss.png?alt=media" alt=""><figcaption><p>Preview data</p></figcaption></figure>

3. Examine the table in your SQL Query Tool:
4. Execute the transformation and view the results.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-19d2a8eb02e6a0ffd2e99a13a7b67143839e6054%2FDIM_SCD%20Pariss.png?alt=media" alt=""><figcaption><p>DIM_SCD - view data</p></figcaption></figure>

{% hint style="info" %}
As expected the Pariss record is inserted. Notice that the natural primary keys (id) are the same as the TK (Technical Key) and we're on version 1 for each record.
{% endhint %}

{% hint style="warning" %}
Let's now do a bit of data entry .. Correct the entry: Pariss to Paris
{% endhint %}

**Try out different Strategies**

{% hint style="info" %}
The workflows below will give you an idea of the different strategies that can be implemented.
{% endhint %}

{% tabs %}
{% tab title="Type 2" %}
{% hint style="info" %}
Any records that are changed are:

* Archived with date\_from to date\_to timestamp.
* Record is assigned Version 1. New record Version 2
* Note the Keys.
  {% endhint %}

1. Double-click on the Data Grid step.
2. Edit the Pariss value to: Paris

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-45754ba36a00f88004994d7f25ed6641d9f0f8a9%2FDG%20-%20paris.png?alt=media" alt=""><figcaption><p>Data grid - edit Paris</p></figcaption></figure>

3. Double-click on the Dimension lookup/update step.
4. Click on the Fields tab and set the following:

{% hint style="warning" %}
The reason for selecting Insert is obvious.. you need to insert a new record that tracks the change. If you select Update, then a Type 1 change occurs; i.e. the original record is updated.
{% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-e240f193bcea77979b96ece6b4bd19c47b1e7964%2FDIM_SCD%20insert%20city%20record.png?alt=media" alt=""><figcaption><p>Field mapping and set database strategy</p></figcaption></figure>

**RUN the Transformation**

1. Click the Run button in the Canvas Toolbar.
2. Examine the table in your SQL Query Tool:.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-151885dbc6073f169dfdf1f7fd8d57624dce6864%2FDIM_SCD%20Pariss%20track.png?alt=media" alt=""><figcaption><p>DIM_SCD - view data</p></figcaption></figure>

{% hint style="info" %}

* A new record has been inserted, with the value ‘Paris’, with an updated date\_from and last\_update timestamp, and version.
* Notice that the `last_update` field has also been updated for the other records.
  {% endhint %}
  {% endtab %}

{% tab title="Punch through" %}
{% hint style="info" %}
**Punch through:**

The punch through option also performs an update. But instead of only updating the matched dimension row, it will update all versions of the row in a Type II slowly changing dimension.

So let's update all versions to Paris.
{% endhint %}

1. Let's start with a clean table to clearly illustrate the results. Truncate the table.

```sql
TRUNCATE TABLE DIM_SCD;
```

2. Repeat the workflow above and check the results.

{% hint style="warning" %}

* Ensure you have set 'Pariss' as the value for city in the data grid step.
* Set update as your initial strategy.
  {% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-19d2a8eb02e6a0ffd2e99a13a7b67143839e6054%2FDIM_SCD%20Pariss.png?alt=media" alt=""><figcaption><p>Preview data</p></figcaption></figure>

3. Double-click on the Data Grid step.
4. Edit the Pariss value to: Paris

   <figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-45754ba36a00f88004994d7f25ed6641d9f0f8a9%2FDG%20-%20paris.png?alt=media" alt=""><figcaption><p>Data grid - edit Paris</p></figcaption></figure>
5. Double-click on the Dimension lookup/update step.
6. Click on the Fields tab and set the following:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-e298eebfcee94baaa2c48f261f87b2e66070d34a%2FInsert%20-%20update%20fields.png?alt=media" alt=""><figcaption><p>Field mapping and set database strategy</p></figcaption></figure>

**RUN the Transformation**

1. Click the Run button in the Canvas Toolbar.
2. Examine the table in your SQL Query Tool:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-151885dbc6073f169dfdf1f7fd8d57624dce6864%2FDIM_SCD%20Pariss%20track.png?alt=media" alt=""><figcaption><p>DIM_SCD - view data</p></figcaption></figure>

**Punch through**

1. Edit the Fields tab in the Dimension Lookup/update step to Punch through:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-60a4f5830fb8fb2db0e3dc303fbc2fc0358952c8%2Fpunch%20through%20strategy.png?alt=media" alt=""><figcaption><p>Field mapping and set database strategy</p></figcaption></figure>

**RUN the Transformation**

1. Click the Run button in the Canvas Toolbar.
2. Examine the table in your SQL Query Tool:

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-94d1f20ba139a9d40d815a8a71c0499aac8f81e3%2FDIM_SCD%20punch%20through.png?alt=media" alt=""><figcaption><p>DIM_SCD - view data</p></figcaption></figure>

{% hint style="info" %}
What happened

Remember a Punch through updates the fields where the records match ..

* All the records have a last\_update field .. so this will be updated
* As both our Pariss & Paris records have a matching key id=3 then the archived record will be updated with the current city value .. Paris
  {% endhint %}
  {% endtab %}
  {% endtabs %}
  {% endtab %}
  {% endtabs %}