DTSE House Price Prediction Pipeline

This application provides a robust ETL and Machine Learning prediction pipeline. 
It ingests housing.csv data file, performs Data Quality (DQ) validation, stores records in SQLite, and generates price predictions using a Random Forest Regressor. 
Duplicate records are ignored and not repeated. Also records having prediction already stored are skipped in processing.


Execution Instructions
Option 1: Docker (Recommended)

This method ensures environment consistency and handles all dependencies automatically.

    1/ Build the Image: docker build -t house-pred-app .

    2/ Run the Container: docker run --rm -v $(pwd)/dbfile:/app/dbfile house-pred-app

Option 2: Local Execution (pyenv + venv)

This is an optional method for local development using a specific Python version.

    1/ Install Python 3.9.21: pyenv install 3.9.21 pyenv local 3.9.21

    2/ Setup Virtual Environment: python -m venv ve_dtse source ve_dtse/bin/activate

    3/ Install Dependencies: pip install --upgrade pip pip install -r requirements.txt

    4/ Run the Process: python dtse_main.py


Pipeline Logic and Features
1. Data Ingestion and Deduplication

    Rowhash PK: The system generates a SHA-256 hash for every row based on its content.

    Duplicate Prevention: Before insertion into the destination table, the application verifies the ROWHASH. If the hash already exists, the record is skipped.

2. Data Quality (DQ) Checks

    The pipeline executes automated DQ checks including range validation, null checks, and type casting.

    Non-Destructive Storage: Records failing DQ checks are flagged with DQ_ISSUE = 1 in the database to facilitate investigation without data loss.

3. Feature Engineering

    Categorical data (e.g., OCEAN_PROXIMITY) is One-Hot Encoded via SQL CASE statements during the migration from raw staging to production tables.

4. Timestamps

    DB stores 2 timestamps - INSERT_TS, MODEL_PREDICTION_TS. Both can be used for incremental processing or further downstream actions


SQLite Database Data Definition 

Table name: HOUSE_PRICE_PREDICTIONS

Column	            Data Type	    Description
ROWHASH	            TEXT	        Primary Key. SHA-256 hash of the input row for deduplication.
LONGITUDE	        REAL	        Geographic coordinate (longitude).
LAT	                REAL	        Geographic coordinate (latitude).
MEDIAN_AGE	        REAL	        Median age of housing in the block.
ROOMS	            REAL	        Total number of rooms.
BEDROOMS	        REAL	        Total number of bedrooms.
POP	                REAL	        Total population in the block.
HOUSEHOLDS	        REAL	        Total number of households.
MEDIAN_INCOME	    REAL	        Median income for households.
MEDIAN_HOUSE_VALUE  REAL	        Target variable (Price) from source CSV.
OCEAN_PROXIMITY	    TEXT	        Original categorical string from source.
OP_1H_OCEAN	        BOOLEAN	        One-Hot Encoded: 1 if <1H OCEAN, else 0.
OP_INLAND	        BOOLEAN	        One-Hot Encoded: 1 if INLAND, else 0.
OP_ISLAND	        BOOLEAN	        One-Hot Encoded: 1 if ISLAND, else 0.
OP_NEAR_BAY	        BOOLEAN	        One-Hot Encoded: 1 if NEAR BAY, else 0.
OP_NEAR_OCEAN	    BOOLEAN	        One-Hot Encoded: 1 if NEAR OCEAN, else 0.
DQ_ISSUE	        BOOLEAN	        Flag: 1 if record failed quality checks, 0 if clean.
INSERT_TS	        TEXT	        ISO-8601 timestamp of record creation.
MODEL_PREDICTION	REAL	        Calculated output from the Random Forest model.
MODEL_PREDICTION_TS	TEXT	        ISO-8601 timestamp of when the prediction was run.


Known Limitations

    Input Handling: The input CSV is currently a local hardcoded dependency.

    Future Integration: Future updates will enable dynamic data fetching from AWS S3, Azure Blob Storage (ABFSS), or REST APIs etc.

    Output: Results are stored in the SQLite DB and can be accessed only using Helper .py file. Future development requires different destination or it can be exported back to CSV.


Maintenance and Debugging

    To run the database helper script independently to inspect table statistics or DQ flags. Please modify query/queries stored in .py file to your needs.

    docker run --rm -v $(pwd)/dbfile:/app/dbfile house-pred-app python dtse_db_runquery_hlpr.py


Logging
Application prints simple info logs in the console during the run. Example:
    2026-01-27 01:19:29 [TBL_CREATE] [INFO] Table verified. Columns: ['ROWHASH', 'LONGITUDE', 'LAT', 'MEDIAN_AGE', 'ROOMS', 'BEDROOMS', 'POP', 'HOUSEHOLDS', 'MEDIAN_INCOME', 'MEDIAN_HOUSE_VALUE', 'OP_1H_OCEAN', 'OP_INLAND', 'OP_ISLAND', 'OP_NEAR_BAY', 'OP_NEAR_OCEAN', 'AGENCY', 'DQ_ISSUE', 'INSERT_TS', 'MODEL_PREDICTION', 'MODEL_PREDICTION_TS']
    2026-01-27 01:19:29 [CSV_IMPORT] [INFO] Ingesting 20639 rows into RAW_HOUSING
    2026-01-27 01:19:29 [CSV_IMPORT] [INFO] DQ Check: 219 records flagged with issues.
    2026-01-27 01:19:29 [CSV_IMPORT] [INFO] Successfully inserted 0 rows to HOUSE_PRICE_PREDICTIONS
    2026-01-27 01:19:29 [CSV_IMPORT] [INFO] Import process finished.
    2026-01-27 01:19:30 [PREDICTION] [INFO] No new records found for prediction.


-----------------------------------------------
Required PIP packages in v0.1 (initial version)

    joblib==1.2.0
    numpy==1.23.4
    pandas==1.5.0
    python-dateutil==2.9.0.post0
    pytz==2022.4
    scikit-learn==1.1.2
    scipy==1.13.1
    six==1.17.0
    threadpoolctl==3.6.0