Medallion architecture, how to update Gold tables?

Mohammad Saber 591

Assume that I have a data source that is ingested to a few bronze tables, and transformed to a silver table. Ans next, a gold table is created by aggregating the silver table.

If new records arrive in the data source, bronze and silver tables are updated by appending new records. Since the gold table contains aggregated values, using "append" is meaningless.

I'd like to know which approach is recommended to update gold tables in case of having a large dataset:

Drop the current gold table, and re-create it
Overwrite the gold table

The first option seems to be slower if we deal with a large dataset. However, I'd like to know if there is any risk with option 2 (e.g. if the table is not overwritten correctly).

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-03-15T06:55:02.5+00:00

Hello @Mohammad Saber ,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-03-08T06:06:28.33+00:00
Hello @Mohammad Saber ,

Thanks for the question and using MS Q&A platform.

The recommended approach to update the gold table depends on the specific requirements of your use case.

If you have a large dataset and the cost of recreating the gold table is high, you can use the overwrite option to update the gold table. This option is faster than recreating the table, but it requires that you have a mechanism to ensure that the overwrite operation is atomic and consistent.

If you have a small dataset, you can use the overwrite option without any risk. However, if you have a large dataset, there is a risk that the overwrite operation may fail or be inconsistent. To mitigate this risk, you can use the following approach:

Create a new gold table with the updated data.

Rename the new gold table to replace the old gold table.

This approach ensures that the gold table is always in a consistent state, even if the overwrite operation fails.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Mohammad Saber 591 Reputation points

2023-03-08T18:36:06.2833333+00:00

@PRADEEPCHEEKATLA-MSFT

Thanks for your reply.

Could you elaborate on why "overwrite" might be inconsistent for large datasets? Or refer me to the related documents? If "overwrite" fails, I can revert back to the previous version of the table.

And about the suggested option:

Assume that I read the silver table as a DataFrame. And I apply the required aggregation to it.

Now, I want to write the DataFrame on the storage account.

How does the suggested mechanism work? I mean, how to rename the new DataFrame to the stored gold table on the storage account?

In case of full refresh or overwriting, I can delete the current files on the storage account and write the DataFrame, or write DataFrame using "overwrite" mode.

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-03-09T06:06:43.6333333+00:00

@Mohammad Saber ,

Regarding the potential inconsistency of the overwrite option for large datasets, it is possible that the overwrite operation may fail due to network issues, storage issues, or other factors. If the overwrite operation fails, the gold table may be left in an inconsistent state, which can lead to incorrect results when querying the table.

To mitigate this risk, you can use the approach of creating a new gold table with the updated data and then renaming the new gold table to replace the old gold table. This approach ensures that the gold table is always in a consistent state, even if the overwrite operation fails.

To implement this approach, you can use the write method of the DataFrame API to write the aggregated data to a new table in the storage account. Then, you can use the rename method of the storage account API to rename the new table to the name of the old gold table.

Hope this helps. Do let us know if you any further queries.

Mohammad Saber 591 Reputation points

2023-03-09T10:46:56.9066667+00:00

@PRADEEPCHEEKATLA-MSFT

Thanks for your help.

What I have found in the documentation is that "overwrite" is recommended.

https://docs.databricks.com/delta/best-practices.html#replace-the-content-or-schema-of-a-table

There are multiple benefits with this approach:

Overwriting a table is much faster because it doesn’t need to list the directory recursively or delete any files.

The old version of the table still exists. If you delete the wrong table you can easily retrieve the old data using Time Travel

It’s an atomic operation. Concurrent queries can still read the table while you are deleting the table.

Because of Delta Lake ACID transaction guarantees, if overwriting the table fails, the table will be in its previous state.

The approach that you suggested, is it documented? I want to refer to the documentation for our client.

Thanks.

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-03-13T05:32:26.3666667+00:00

@Mohammad Saber ,

Yes, this has been already documentated here: https://learn.microsoft.com/en-us/azure/databricks/delta/best-practices#--replace-the-content-or-schema-of-a-table

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-03-23T07:20:37.79+00:00

@Mohammad Saber - Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Mohammad Saber 591 Reputation points

2023-03-23T21:31:15.1266667+00:00

Thanks.

The documentation is about overwriting a table which is the option I decided to use.

I couldn't find information about renaming tables.

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-04-03T05:15:01.56+00:00

@Mohammad Saber - Azure Databricks supports column mapping for Delta Lake tables, which enables metadata-only changes to mark columns as deleted or renamed without rewriting data files. It also allows users to name Delta table columns using characters that are not allowed by Parquet, such as spaces, so that users can directly ingest CSV or JSON data into Delta without the need to rename columns due to previous character constraints.

For more details, refer to Rename and drop columns with Delta Lake column mapping.

Hope this helps.

PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee

2023-04-06T05:38:55.3833333+00:00

@Mohammad Saber - Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Share via

Medallion architecture, how to update Gold tables?

1 answer