Combine sources with different schemas

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Schema Drift. About 14 minutes. Part of the Pro drill bank.

Data for the same thing often arrives from several sources with different columns. A web feed has no platform, a store feed has a store_id. If you stack them with a plain union, Spark matches columns by position and gives an error or, worse, puts values in the wrong columns.

Three feeds describe orders but have different columns. web has id, user and amount. mobile has id, user, amount and platform. store has id, user and store_id. Combine all rows into one table that has every column found in any feed (id, user, amount, platform, store_id), plus a column source that says where the row came from: web, mobile or store. When a feed does not have a column, that column is NULL. Do not lose rows and do not duplicate any. The editor creates the 3 tables and ends with result = web. Change the last line to build your answer. unionByName with allowMissingColumns=True is the key tool.

Requirements

• Add a source column to each feed with the right text.
• Combine the feeds with unionByName(..., allowMissingColumns=True).
• The result has id, user, amount, platform, store_id and source.
• Keep all 4 rows and store the DataFrame in result.

Constraints

• union matches columns by position. unionByName matches them by name.
• A missing column is filled with NULL when allowMissingColumns=True.
• Each input row appears exactly once in the result.

Examples

Input: web id | user | amount 1 | ann | 20 2 | bo | 35.5 mobile id | user | amount | platform 3 | cy | 12 | ios store id | user | store_id 4 | di | 7 Output: id | user | amount | source | platform | store_id 1 | ann | 20 | web | NULL | NULL 2 | bo | 35.5 | web | NULL | NULL 3 | cy | 12 | mobile | ios | NULL 4 | di | NULL | store | NULL | 7 4 rows in total. amount is NULL for the store row, platform is NULL for web and store rows, store_id is NULL for web and mobile rows.

Topics: lakebench, pyspark, unionByName, schema drift, lit.

More PySpark interview questions · All interview problems · Learn data engineering