Hi @SantiNath_Dey,
Databricks has solid building blocks for this, and there's even a purpose-built metadata framework you can lean on.
At its core, your pipeline needs to do three things...generate surrogate keys at each level, resolve parent references top-down, and declare the constraints. Here's how to approach each of them individually.
For the first one... surrogate key generation... Databricks supports generated columns using GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY on Delta tables. However, identity columns disable concurrent writes and don't survive a full table refresh (a rebuild can reassign different IDs to the same entity). For your use case involving hierarchical data that may be reprocessed, Databricks recommends using deterministic surrogate keys derived from the natural key instead. For example, you could derive an order-preserving surrogate from a composite natural key rather than using sha2() (which scatters data and hurts clustering performance). Check this.
For the second one... top-down parent-child resolution... Since your hierarchy is emp → emp_addr → emp_addr_ofc, you process tables in dependency order. Generate the surrogate key for emp first, then when processing emp_addr, look up the parent's surrogate key via the natural key relationship and store it as the foreign key column. Repeat for emp_addr_ofc referencing emp_addr. A metadata config table can automatically drive this ordering.
And for the constraints... once your tables are loaded with the correct key columns, you register the relationships using ALTER TABLE... ADD CONSTRAINT.
-- Level 1: emp
ALTER TABLE silver.emp ADD CONSTRAINT pk_emp PRIMARY KEY (emp_sk);
-- Level 2: emp_addr
ALTER TABLE silver.emp_addr ADD CONSTRAINT pk_emp_addr PRIMARY KEY (emp_addr_sk);
ALTER TABLE silver.emp_addr ADD CONSTRAINT fk_emp_addr_emp
FOREIGN KEY (emp_sk) REFERENCES silver.emp;
-- Level 3: emp_addr_ofc
ALTER TABLE silver.emp_addr_ofc ADD CONSTRAINT pk_emp_addr_ofc PRIMARY KEY (emp_addr_ofc_sk);
ALTER TABLE silver.emp_addr_ofc ADD CONSTRAINT fk_emp_addr_ofc_addr
FOREIGN KEY (emp_addr_sk) REFERENCES silver.emp_addr;
These are informational constraints in Unity Catalog. They are not enforced at write time, but they serve as metadata for BI tools, query optimizers, and documentation. If you need actual enforcement, pair them with NOT NULL constraints and CHECK constraints, or validate in your pipeline logic before writing.
Rather than hand-coding each table's key generation and constraint registration, you can define a metadata configuration (JSON or a Delta table) that describes the hierarchy:
[
{"table": "emp", "natural_key": ["emp_id"], "surrogate_key": "emp_sk", "parent": null},
{"table": "emp_addr", "natural_key": ["emp_id", "addr_id"], "surrogate_key": "emp_addr_sk", "parent": {"table": "emp", "join_key": ["emp_id"]}},
{"table": "emp_addr_ofc", "natural_key": ["emp_id", "addr_id", "ofc_id"], "surrogate_key": "emp_addr_ofc_sk", "parent": {"table": "emp_addr", "join_key": ["emp_id", "addr_id"]}}
]
A generic Python script reads this config, topologically sorts by parent dependencies, and for each table: (1) generates the deterministic surrogate key from the natural key columns, (2) joins to the parent table to resolve the parent's surrogate key as the FK column, and (3) issues the ALTER TABLE ADD CONSTRAINT statements.
If you want a production-grade metadata-driven framework, take a look at sdp-meta (formerly dlt-meta) from Databricks Labs. It's a metaprogramming framework designed for exactly this pattern... you maintain JSON/YAML metadata describing your tables, and it dynamically generates Lakeflow pipelines for your bronze and silver layers. The benefits are consistency across hundreds of tables, less custom code, and easier maintenance since you're updating config files rather than pipeline logic.
If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.
Regards,
Ashwin | Delivery Solution Architect @ Databricks
Helping you build and scale the Data Intelligence Platform.
***Opinions are my own***
