Write code that downloads data from an API and cleans it up (e.g. transform with pandas).
Asked in the Power Day technical stage. For this SQL exercise, assume the API response has been loaded into raw_api_transactions, with customer reference data in customer_reference. Clean the records by validating fields, retaining the latest duplicate, and excluding records without a valid customer match.
transaction_id, customer_id, customer_name, occurred_at, amount, and status.occurred_at ascending, then transaction_id ascending.| Column | Type | Description |
|---|---|---|
| transaction_id | VARCHAR(30) | Transaction identifier received from the API |
| customer_id | VARCHAR(20) | Customer identifier received from the API |
| amount_text | VARCHAR(30) | Transaction amount as raw text |
| occurred_at_text | VARCHAR(40) | Transaction timestamp as raw text |
| status_text | VARCHAR(20) | Transaction status as raw text |
| ingested_at | TIMESTAMP | Time the API record was loaded |
| Column | Type | Description |
|---|---|---|
| customer_idPK | VARCHAR(20) | Verified customer identifier |
| customer_name | VARCHAR(100) | Canonical customer name |