MetaData Framework for Multi-Level Silver Layer PK/FK Creation

SantiNath_Dey — Sun, 06 Sep 2026 14:02:56 GMT

Hi Team,
Our source data originates from MongoDB as hierarchical JSON and is ingested into our Silver layer, where it is already normalized into tabular structures. However, primary and foreign key constraints are not enforced during ingestion. We must establish and maintain PK/FK relational integrity post-loading through an automated post-processing step.The Challenge:The source system features a deep, multi-level parent-child tree hierarchy (e.g., emp -> emp_addr -> emp_addr_ofc). We require a metadata-driven modeling framework and script execution strategy to dynamically generate surrogate keys, resolve parent-child references top-down, and enforce relational integrity across all levels.

Please find below screen shot for your reference.

Re: MetaData Framework for Multi-Level Silver Layer PK/FK Creation

Ashwin_DSA — Sun, 06 Sep 2026 15:29:53 GMT

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.

Re: MetaData Framework for Multi-Level Silver Layer PK/FK Creation

SantiNath_Dey — Sun, 06 Sep 2026 16:11:17 GMT

Thank you for quick response

topic Re: MetaData Framework for Multi-Level Silver Layer PK/FK Creation in Databricks Free Edition Help

MetaData Framework for Multi-Level Silver Layer PK/FK Creation

Re: MetaData Framework for Multi-Level Silver Layer PK/FK Creation

Re: MetaData Framework for Multi-Level Silver Layer PK/FK Creation