Hi Madams,

Thank you for your reply. 

Let me explain in detail about the use case that we have.

The raw data is ingested in the Iron layer on batch processing. The file would be in JSON format that we receive in batches. 

IRON Layer - The first level where the data is been ingested. So, each and every batch file will be treated as one record in the Delta live table.

BRONZE Layer - The first level of transformation happens here which is the flattening of the JSON file to the objects. The

SILVER Layer - The delta actual tables are getting created here but all the tables are SCD Type 2. The historical changes have also been recorded with the data timestamp. The last updated record would be active record. For example: Consider a user details table

The user table above is created in the Silver layer as streaming DLT Tables. The changes made will be recorded since they are SCD Type 2.

---GOLD Layer

I need help in deciding the Gold layer. My requirements are:

To create a dataset like in the Gold layer.

- The final user dataset  in the gold layer should have only the last updated records of each user. The user id should be the primary key.

- Sometimes I would have to make a join with other tables to bring some columns.

- The column names needs to be changed.

- Default values will be applied in NULL columns. 

These are pretty much the required transformations for the dataset to be in Gold layer.

My questions are:

1. Should the dataset in the GOLD layer be created with streaming tables/ Materialized Views? The data will be keep on growing and relatively these tables are large datasets.

2. some QC checks can be applied in the future if not now. 

3. Need to define the primary key constraints on the datasets.