Join Types and Lake vs Warehouse | Dataford Interview Questions

Explain LEFT JOIN and the difference between a data lake and a data warehouse.

Demonstrate the join behavior by listing every data lake asset and any corresponding warehouse table. Also explain how unmatched rows are represented and when each storage architecture is appropriate.

Output

One row per data lake asset and matching warehouse table
Columns: asset_name, storage_format, warehouse_table, warehouse_layer
Include assets without matches, show them with NULL warehouse columns, and order by asset_name and warehouse_table

Schema

data_lake_assets

Column	Type	Description
asset_idPK	INTEGER	Unique identifier for a data lake asset
asset_name	VARCHAR(100)	Name of the lake asset
storage_format	VARCHAR(30)	Physical or logical format used by the asset

warehouse_tables

Column	Type	Description
warehouse_table_idPK	INTEGER	Unique identifier for a warehouse table
source_asset_id	INTEGER	Lake asset used as the source
table_name	VARCHAR(100)	Warehouse table name
layer	VARCHAR(30)	Warehouse processing layer

Your question is Join Types and Lake vs Warehouse. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem