SCD Type 2 customer dimension on Databricks

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, the first platform Emolysis targets, using the SCD Type 2 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.

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, 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 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 shape covers the general case.
• Deletes in the source: decide whether a deleted customer closes the current row or is ignored.