In a scenario where you need to build a type 2 dimension table in Snowflake, assume you have a product table (or a sales table). How would you read and implement a type 2 table, including handling target dimension, fact table, and audit files?

To build a type 2 dimension in Snowflake:

1. Staging: Load source data (e.g., product or sales) into a temporary staging table. Use Snowpipe or COPY to ingest.
2. Audit: Create an audit table that records each change with a change_id, change_timestamp, and operation type (INSERT/UPDATE).
3. Merge logic: Use a MERGE statement that compares the staging table with the existing dimension on the natural key. For each row:

• If the key does not exist, INSERT a new row with start_date = current_timestamp, end_date = '9999‑12‑31', current_flag = 1.
• If the key exists and any attribute differs, UPDATE the existing row to set end_date = current_timestamp, current_flag = 0, then INSERT the new row with updated attributes.

1. Fact table: When loading facts, join the fact source to the dimension on the natural key and use the current_flag = 1 row to get the surrogate key.
2. Automation: Schedule the MERGE as a Snowflake task that runs after each ingestion. Use streams to capture incremental changes if the source supports CDC.
3. Performance: Cluster the dimension table on the natural key and surrogate key to speed up joins.

This pattern ensures that every historical state of the dimension is preserved, the fact table always references the correct surrogate key, and audit logs provide traceability for each change.