Designed and ran 6 ETL/ELT pipelines
Designed, implemented, and administered 6 ETL/ELT pipelines,
utilizing Google BigQuery, MSSQL, PostgreSQL, Shell scripting,
PL/pgSQL, and Transact‑SQL, integrating data for efficient Python
API processing.
2022 – 2024

Situation. The organization needed to consolidate and process large
volumes of tabular data from a remote data warehouse located behind
an IPsec VPN. This data was crucial for powering internal analytics
dashboards and external Python APIs used by clients and partners.

Task. The objective was to design, implement, and maintain a set of
robust and automated ETL/ELT pipelines to securely retrieve,
transform, and load data into Google BigQuery, ensuring data
accuracy, performance, and scalability across systems.

Action. 6 end‑to‑end ETL/ELT pipelines were designed, implemented,
and administered, spanning multiple technologies including Google
BigQuery, MSSQL, PostgreSQL, Shell scripting, PL/pgSQL, and
Transact‑SQL.

  * Established secure connections to a remote server protected by
an IPsec VPN to automate data retrieval.

  * Scheduled and executed downloads of compressed data archives
containing Parquet, CSV, and .bak files.

  * Developed Bash and Python scripts to extract and classify files
based on type and schema.

  * For .bak files, backups were restored into a local MSSQL Server
instance using RESTORE DATABASE workflows and schema integrity
validated.

  * Loaded tabular data into staging schemas in MSSQL and
PostgreSQL, using bcp, psql, and SSIS tools, depending on the source
format.

  * Wrote modular and reusable PL/pgSQL and T‑SQL procedures to
clean, normalize, and enrich the data based on business logic.

  * Exported curated datasets and tables into intermediate formats,
compressed them using gzip, and securely transferred them to a
Google Cloud Storage bucket.

  * Automated the upload and schema mapping processes to Google
BigQuery, using bq CLI and Python‑based data ingestion scripts.

Result. These automated pipelines significantly reduced the manual
overhead and processing time — from 3+ hours of manual work to under
20 minutes end‑to‑end, while improving data freshness from weekly to
daily syncs. The Python APIs consuming this data saw a 30%
performance improvement, and the enhanced visibility helped business
analysts deliver faster insights to stakeholders. The solution
remains scalable and extensible for onboarding new data sources as
the business grows.

Categories:
1Databases	/categories/databases/	data.engineer.company	70
1PostgreSQL	/categories/postgresql/	data.engineer.company	70
1SQL	/categories/sql/	data.engineer.company	70
1Data Warehousing	/categories/data-warehousing/	data.engineer.company	70
1Data Engineering	/categories/data-engineering/	data.engineer.company	70
1Data Pipelines (ETL/ELT)	/categories/data-pipelines/	data.engineer.company	70
1Cloud	/categories/cloud/	data.engineer.company	70
1Automation & CI/CD	/categories/automation/	data.engineer.company	70
1Networking & VPN	/categories/networking/	data.engineer.company	70
1Python	/categories/python/	data.engineer.company	70

Services:
1Backend & API Development	/services/backend-development/	data.engineer.company	70
1Database Design & Modeling	/services/database-design/	data.engineer.company	70
1Data Pipeline Development (ETL/ELT)	/services/data-pipeline-development/	data.engineer.company	70
1Data Warehouse Design	/services/data-warehousing/	data.engineer.company	70
1AI Data Readiness	/services/ai-data/	data.engineer.company	70

hThis page on the web	URL:https://data.engineer.company/portfolio/designed-and-ran-6-etl-elt-pipelines/	data.engineer.company	70
