🚕 Overview
The NYC Taxi dataset is a well-known public dataset that records millions of yellow and green cab trips across New York City.
I chose it for this project because it’s large, messy, and realistic — a perfect sandbox to demonstrate performance, automation, and modern data-engineering techniques.
🎯 Goals
- Convert Parquet → PSV (pipe-separated) for transparent, row-based inspection.
- Validate schemas and generate data dictionaries automatically.
- Bulk insert into SQL Server and PostgreSQL.
- Build a pipeline that is repeatable, automated, and documented.
- Downstream: showcase integration with Airflow, Databricks, and Power BI.
🛠️ Key Steps
- Validation scripts (Python + DuckDB + Pandas) ensure schema alignment and catch errors.
- Conversion pipeline produces clean PSV files ready for database ingest.
- Bulk insert scripts for both SQL Server and PostgreSQL.
- Repo hygiene:
.gitignore, venv isolation, and structured docs (setup_steps.md,validation_steps.md,run_steps.md).
# Example: PSV Bulk Insert (SQL Server)
/* Minimal BULK INSERT template (matches working style: no column list) */
DECLARE @DataDir NVARCHAR(4000) = N'D:\appdev\nyctaxi\nyctaxi-pipeline\data_out';
DECLARE @File NVARCHAR(4000) = N'yellow_tripdata_2024-01.psv';
DECLARE @FullPath NVARCHAR(4000) = @DataDir + CASE WHEN RIGHT(@DataDir,1) IN ('/','\') THEN '' ELSE '\' END + @File;
DECLARE @SqlStatement NVARCHAR(MAX);
SET @SqlStatement = N'BULK INSERT [dbo].[yellow_tripdata]
FROM ''' + @FullPath + '''
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0A'',
CODEPAGE = ''65001'',
DATAFILETYPE = ''char'',
TABLOCK,
MAXERRORS = 100,
BATCHSIZE = 100000,
ERRORFILE = N''D:\appdev\nyctaxi\nyctaxi-pipeline\logs\yellow_tripdata.bulk_errors''
);';
PRINT CONCAT('Loading: ', @FullPath);
EXEC sp_executesql @SqlStatement;