Scenario

You work for a manufacturing company that tracks products and their associated manufacturing processes, but both datasets contain duplicate rows due to data quality issues.

Task

Write a Snowflake SQL query that:

1. Removes duplicate rows from {{ ref("products") }} using SELECT DISTINCT or a ROW_NUMBER window function
2. Removes duplicate rows from {{ ref("manufacturing") }} in the same way
3. Joins the deduplicated results on product_id using an INNER JOIN
4. Returns these columns in order: product_id, product_name, category, process_id, process_name, duration

Schema

products

manufacturing

Example

products (note the duplicate row for product_id 1):

manufacturing:

Expected Output:

Note: The duplicate row for product_id 1 in products must be removed before joining. Without deduplication, Bolt M6 would appear twice per process, producing incorrect results.