Senior Data Warehouse Architect

Prima Systems is seeking a seasoned Data Warehouse Specialist with extensive experience in designing, developing, and maintaining high-performance data warehouse solutions. The ideal candidate will have deep expertise in Microsoft SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and PostgreSQL. This role requires hands-on skills in data modeling, ETL processes, and query optimization to support scalable and reliable data environments. Candidates must have a strong SQL background and proven experience in integrating data from multiple sources, developing ETL workflows, and delivering accurate reporting datasets. Experience with ClickHouse and modern data warehousing techniques is a plus. This position requires onsite work in Islamabad, and only applicants with relevant Data Warehouse experience will be considered.

Key Responsibilities
- Design, develop, and maintain scalable data warehouse architectures using Microsoft SQL Server and PostgreSQL.
- Create and manage data warehouse schemas, including dimensional models, fact and dimension tables, star and snowflake schemas.
- Develop, deploy, and maintain ETL packages using SQL Server Integration Services (SSIS).
- Extract, transform, validate, and load data from diverse sources such as SQL Server, PostgreSQL, APIs, flat files, and other business applications.
- Optimize complex SQL queries, stored procedures, views, functions, and data transformation logic for performance and accuracy.
- Develop and enhance SQL Server Reporting Services (SSRS) reports, including parameterized and drill-down reports with scheduled delivery.
- Implement data loading strategies covering full loads, incremental loads, historical data, and change data capture where applicable.
- Improve ETL packages, SQL queries, indexes, and execution plans to boost data processing efficiency and reporting speed.
- Apply indexing, table partitioning, aggregation, and storage strategies tailored to workload demands.
- Ensure data quality through validation, reconciliation, and integrity checks for consistent reporting.
- Monitor and troubleshoot SSIS package failures, SQL Server Agent jobs, scheduled data loads, and database performance issues.
- Automate ETL workflows, data refreshes, and reporting processes using SQL Server Agent or other orchestration tools.
- Integrate operational system data into centralized data warehouse environments while maintaining data consistency and traceability.
- Maintain comprehensive documentation including data dictionaries, ETL mappings, source-to-target mappings, data lineage, and operational procedures.
- Support business intelligence and analytics teams by preparing reliable datasets for dashboards and reports.
- Implement database security, role-based access controls, backup, recovery, and data retention policies.
- Manage schema changes, ETL deployments, and data warehouse enhancements with minimal disruption.
- Collaborate with software engineering, BI, DevOps, and infrastructure teams to enhance data availability, reliability, and performance.
- Identify and implement improvements to data warehouse architecture, automation, and reporting efficiency.

Required Qualifications
- Bachelor’s degree in Computer Science, Software Engineering, Information Technology, Data Engineering, or a related field.
- Over 10 years of professional experience in data warehousing, SQL development, ETL development, or related data engineering roles.
- Strong hands-on expertise with Microsoft SQL Server, including database development, stored procedures, indexing, query optimization, and execution plan analysis.
- Practical experience with SQL Server Integration Services (SSIS) for ETL package development, deployment, scheduling, and troubleshooting.
- Proficiency in SQL Server Reporting Services (SSRS), including report creation, parameterization, subscriptions, and performance tuning.
- Advanced SQL skills covering complex joins, CTEs, window functions, aggregations, temporary tables, and performance tuning.
- Experience with PostgreSQL, including schema design, SQL development, query optimization, and data integration.
- Solid understanding of data warehousing concepts such as dimensional modeling, star and snowflake schemas, slowly changing dimensions (SCD).
- Proven ability to design and maintain ETL/ELT workflows, incremental data loads, data transformations, and historical data management.
- Experience consolidating data from multiple heterogeneous sources.
- Knowledge of data quality, validation, reconciliation, integrity, and error-handling best practices.
- Experience scheduling and monitoring data processing jobs and troubleshooting ETL failures.
- Familiarity with database security, access controls, backup, recovery, and data governance principles.
- Strong analytical, troubleshooting, documentation, and collaboration skills.

Preferred Qualifications and Additional Skills
- Experience with ClickHouse, including columnar storage, MergeTree engines, partitioning, and analytical query optimization.
- Familiarity with cloud data warehouse platforms such as Snowflake, Amazon Redshift, Google BigQuery, or Azure Synapse Analytics.
- Knowledge of data orchestration tools like Apache Airflow or dbt.
- Experience with Python or PowerShell for automation and scripting.
- Understanding of Change Data Capture (CDC), incremental extraction, and data synchronization techniques.
- Exposure to Power BI or other BI and visualization platforms.
- Familiarity with Git, version control, CI/CD, and automated deployment of database objects and SSIS packages.
- Experience with SQL Server performance monitoring, indexing strategies, partitioning, and workload optimization.
- Awareness of data lake and lakehouse architectures and modern data engineering practices.
- Experience migrating or integrating data between SQL Server, PostgreSQL, and analytical database platforms.

Applicants must be willing to work onsite in Islamabad and possess relevant data warehousing experience to be considered for this opportunity.