Hi,

At the moment, my process is:-

I am using ETL (Data Factory) to land paquet files in a raw landing zone. I keep all the source data here. So I can fully rebuild the data if I need to.

i.e.

source_system/schema/table/loadon_year=2024/loadon_month=08/loadedon_day=01

I mount this landing location as a volume in databricks (Unity Catalog)

1. I ingest the data into the bronze tables using Cloudfiles as a stream append-only. I iterate over a list of tables & locations from metadata. My Bronze table targets my catalog and bronze schema and has a DLT Pipeline setup to process them.

2. I read in the bronze tables as a streaming source for the silver layer and use APPLY INTO to another table as (SCD1/SCD2) as required. This will update any existing records SCD or record history as SCD2.

a. Silver layer tables target my catalog and silver schema and have a separate DLT Pipeline setup to proess them. At the moment you can't target a destination to a different schema in the same DLT pipeline with unity catalog.

3. For the Gold layer, I create a live table (materialised view) which performs all the joins and field selections from silver etc.

a. Gold layer tables target my catalog and gold schema and have a separate DLT Pipeline setup to process them

4. For surrogate keys, I use an md5 hash of the business keys and source system id. That way I don't have to do complex key lookups. The surrogate key pipeline is generated in the Sliver layer.

I have not hit any issues yet, although it is still early days. If you overcomplicate the processing between bronze, silver & gold, it can be a bit tricky.

I want to have a single DLT pipeline, but that would require DTL to be able to cross Catalog & Schema boundaries. Unless, of course, you have it all in one schema, which I don't like.

Regards

Toby.
https://thedatacrew.com