Business Problem

You are a Data Engineer at Amazon. You are building a new ETL (Extract, Transform, Load) pipeline to populate a new Dim_Product dimension table in the company's data warehouse. The source products table has data in various formats, but the dimension table requires a single, standardized format for all product information.

Your task is to write a SQL transformation query that cleans and standardizes the data from the source products table. The query must produce a list with the following standardized fields:

A normalized_sku (all uppercase, with hyphens removed).
The product_name in all uppercase.
The category in all uppercase.
The price converted from a decimal value to an integer representing cents.

The final output should be sorted by the .

Business Problem

Your task is to write a SQL transformation query that cleans and standardizes the data from the source products table. The query must produce a list with the following standardized fields:

A normalized_sku (all uppercase, with hyphens removed).
The product_name in all uppercase.
The category in all uppercase.
The price converted from a decimal value to an integer representing cents.

The final output should be sorted by the .

Column Name	Type
product_id	INTEGER
sku	TEXT
product_name	TEXT
category	TEXT
price	REAL
currency	TEXT

Column Name	Type
product_id	INTEGER
sku	TEXT
product_name	TEXT
category	TEXT
price	REAL
currency	TEXT

product_id	normalized_sku	name_upper	category_upper	price_cents
1	SKU10001	CASPER ORIGINAL MATTRESS QUEEN	HOME	109500
2	SKU10002	DYSON SUPERSONIC HAIR DRYER	BEAUTY	42999
3	SKU10003	CALDIGIT TS4 THUNDERBOLT DOCK	OFFICE	39999

product_id	normalized_sku	name_upper	category_upper	price_cents
1	SKU10001	CASPER ORIGINAL MATTRESS QUEEN	HOME	109500
2	SKU10002	DYSON SUPERSONIC HAIR DRYER	BEAUTY	42999
3	SKU10003	CALDIGIT TS4 THUNDERBOLT DOCK	OFFICE	39999

2501. Normalize Product SKUs and Names

Relevant companies

Tags

2501. Normalize Product SKUs and Names

Relevant companies

Tags

Sign in to Practice