# SCD Type 2 customer dimension on Databricks

> A preview of building a customer dimension with history on Databricks, from slots and golden tests to sampling and deploying as an Asset Bundle.

A customer dimension with history is one of the most common pipelines in data engineering, and one of the easiest to get subtly wrong. This tutorial follows the flow on [Databricks](https://emolysis.com/platforms/databricks), the first platform Emolysis targets, using the [SCD Type 2](https://emolysis.com/shapes/scd2) shape.

## What you need

- The Emolysis desktop app and an Emolysis account.
- A Databricks workspace with Unity Catalog, and a development catalog and schema.
- An account that can read your source table, write the dimension's table, use a SQL warehouse, create jobs and write files in its own workspace folder.
- A source table of customers, for example `dev.crm.customers`, with `customer_id`, `name`, `region`, `segment` and `updated_at`.

## Step 1: Connect Databricks

Choose Connect, then Databricks, or run `emo connect databricks`. Confirm the short sign-in code in the browser, then authorize with Databricks' own sign-in. Pick your workspace and the SQL warehouse to use. The Databricks profile loads, and your catalog appears in the app. See [connecting Databricks](https://emolysis.com/docs/connect-databricks).

## Step 2: Ask for the pipeline

Type: "Build a customer dimension from dev.crm.customers that keeps history of region and segment." The agent picks SCD Type 2 and shows the slots it filled from the catalog:

- Business key: `customer_id`.
- Tracked columns: `region`, `segment`.
- Effective date: `updated_at`.
- Open-ended rows: end date null, with an `is_current` flag.
- Surrogate key: generated per version.

It asks about anything it cannot infer. A common question: should a change to `name` create a new version? Say no, and `name` is updated in place on the current row.

## Step 3: Read the filled template

Open the generated pipeline. It is the Databricks rendering of the SCD Type 2 template: a MERGE INTO on a Delta table that closes changed current rows and inserts new versions. The file is tagged with `platform: databricks` and the profile version.

## Step 4: Run the golden tests

The SCD Type 2 golden test runs on DuckDB. Its fixture changes an attribute across three runs and checks that each key has exactly one current row and that validity ranges neither overlap nor leave gaps. It also re-runs a load to check that nothing is duplicated. Expect green in seconds.

## Step 5: Try it on a sample

Ask for a sample: "Sample 5,000 rows of dev.crm.customers." Emolysis reads the slice through your SQL warehouse into the project's local lakehouse; that read runs on your compute. Run the pipeline on the sample and browse the result: look for customers with several versions and check their dates.

## Step 6: Check the traps

Platform lint runs on the rendered pipeline. On Databricks it flags, among other things, Unity Catalog permissions your deploy needs and differences between serverless and classic compute. Read any warnings now.

## Step 7: Review in a pull request (optional)

Open a pull request. With `emo check` in your CI, it shows lint, golden tests and a cost estimate for Databricks. A reusable GitHub Actions workflow, in preview, runs `emo check` in your own CI; it needs the `emo` command line, which is not released yet. The [GitHub App](https://emolysis.com/integrations/github), which adds a preview link, is planned.

## Step 8: Deploy

With everything green, choose Deploy or run `emo run pipeline.deploy`. Emolysis translates the pipeline into Databricks SQL, packages it as a Databricks Asset Bundle with a job and its schedule, and deploys it to your workspace. The job runs on your compute, under your account. The [deploying docs](https://emolysis.com/docs/deploying) describe the flow.

## Step 9: Verify on Databricks

Open the job in your workspace and run it once. Query the dimension: every `customer_id` should have exactly one row with `is_current = true`.

## What to watch in production

- Late changes: an `updated_at` earlier than the current version's start date needs a decision. The agent asks how you want it handled when it sees one. The [Late-arriving](https://emolysis.com/shapes/late-arriving) shape covers the general case.
- Deletes in the source: decide whether a deleted customer closes the current row or is ignored.

---

Page: https://emolysis.com/tutorials/scd-type-2-customer-dimension-databricks. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.