Repository for Apache Beam ETL project on HM Land Registry Price Paid Data.
The HM Land Registry Price Paid Data set is a valuable resource containing comprehensive information about property transactions in the United Kingdom. Analyzing this dataset can offer insights into real estate trends, market dynamics, and property valuation. However, working with big data sets like this requires an efficient and scalable ETL (Extract, Transform, Load) pipeline.
Pipeline to transform and load Historical Price Paid data in MongoDB Database.
Full pipeline code available in the Collab Notebook.
Link to Building a Scalable Big Data ETL Pipeline I Medium Article.
ETL 2 represtation: 
Pipeline to ingest new monthly data
Link to Building a Scalable Big Data ETL Pipeline II, medium article.
The key for aggregation is generated using a combination of the POAN (Primary Addressable Object Name), SOAN (Secondary Addressable Object Name), Street, Locality, and the first half of the Postcode. This logic is implemented to handle various scenarios and ensure the uniqueness of each property in the aggregation process.
The specific combination of attributes serves the following purposes:
-
Postcode Changes: Some properties may have experienced changes in the postcode over time due to administrative updates or renumbering, and in almost all cases, the last half is the one to undergo change. Therefore, to maintain consistency, we utilize other details of the property address.
-
Missing Property Information: In certain cases, one or more values such as PAON, SAON, Street, or Locality may be missing for a property. To ensure the uniqueness of each property, we combine all four values in the key.
-
Similar Property Information: There may be instances where multiple properties share the same PAON, SAON, Street, or any combination of these values. To differentiate between these similar properties, we include all four values along with the first characters of the postcode in the key.
The propkeyid_gen() function is employed to generate the property key identifier based on the postcode. This function utilizes a dictionary named dict_post to keep track of the count of properties with the same postcode. The function checks if the postcode exists in the dictionary, updates the count accordingly, and generates a four-digit count, zero-padded if necessary. The postcode and count are then combined to form the property key identifier. We then store the dictionary in a pickle file to later use to generate Property ID for new properties.
Data Source : https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads
Data Definition : https://www.gov.uk/guidance/about-the-price-paid-data#download-options
Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0.