Hi @sjmb ,
There are many questions here but hopefully I'll be able to provide a summarized answer that helps you understand the lakehouse, the medallion architecture and other concepts a bit better.
In the Medallion architecture, the usage of managed and non-managed tables, autoloader/cloud files, and the apply changes into function can vary depending on the layer (Bronze, Silver, Gold) and the specific use case.
As a general rule, use Managed Tables over External Tables because new product features, such as background auto compaction, and auto-optimize, will only work on Managed Tables. Managed tables are also recommended when you want to provision a governed location for your content without the overhead of creating and managing external locations and storage credentials. Managed tables always use the Delta table format. Use External Tables when you are already using them, when you are upgrading from Hive Metastore to Unity Catalog and you want a quick and seamless “one-click” upgrade without moving data. External tables are also recommended if you have DR requirements for this data, if you have requirements for external readers or writers to interact with this data outside of Databricks Clusters and DBSQL, or if you have requirements to enforce a particular naming or hierarchical storage convention. External tables support non-Delta tables, such as parquet/avro/orc etc.
The Bronze layer is where all the data from external source systems is landed. The table structures in this layer correspond to the source system table structures "as-is". It's generally a good idea to keep the data in the Bronze layer in Delta format for subsequent reads from the Bronze layer for ETL to be performant and to allow updates in Bronze to write CDC changes. Managed tables are typically used in this layer for their enhanced performance and low maintenance. In the Silver layer, the data from the Bronze layer is matched, merged, conformed, and cleaned. Only minimal or "just-enough" transformations and data cleansing rules are applied while loading the Silver layer. Both managed and external tables can be used in this layer, depending on your specific requirements. The Gold layer is for reporting and uses more de-normalized and read-optimized data models with fewer joins. The final layer of data transformations and data quality rules are applied here. Managed tables are typically used in this layer for their enhanced performance and low maintenance.
Autoloader is a tool that efficiently loads files from cloud storage into Delta tables. It's typically used in the Bronze layer when new files come to the landing zone.
The 'apply changes into' API supports updating tables with SCD type 1 and type 2. Use SCD type 1 to update records directly where history is not retained for records that are updated. Use SCD type 2 to retain a history of records, either on all updates or on updates to a specified set of columns. Note that history is also tracked on all delta tables, meaning you can query previous versions of a table. Please note that the REFRESH command and the APPLY CHANGES INTO command serve different purposes and are used in different contexts. The REFRESH command is used for refreshing materialized views, while the APPLY CHANGES INTO command is used for handling changes in data in the context of Delta Live Tables
To summarize, in general, the Bronze layer often deals with raw data in its native format, the Silver layer contains cleansed and conformed data, and the Gold layer holds curated business-level tables. The specific tables or file types used in each layer, as well as the loading style, can vary depending on the specific use case and data requirements.
Hope that helps.
Gab
References:
Create tables in Unity Catalog
Simplified change data capture with the APPLY CHANGES API in Delta Live Tables
What is the Medallion Lakehouse Architecture
