Senior Data Warehouse Architect

Prima Systems is seeking a highly skilled Data Warehouse Specialist with extensive experience in designing, developing, optimizing, and maintaining robust data warehouse solutions. The ideal candidate will have deep expertise in Microsoft SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), PostgreSQL, and data modeling. This role requires hands-on experience in building scalable ETL workflows, integrating data from diverse sources, and delivering reliable reporting datasets. Familiarity with modern data warehousing practices and analytical query optimization is essential. Candidates must be willing to work onsite in Islamabad and possess relevant Data Warehouse experience.

Key Responsibilities

- Design, develop, and maintain scalable data warehouse architectures using Microsoft SQL Server and PostgreSQL.
- Develop and manage data warehouse schemas, including dimensional models, fact and dimension tables, star and snowflake schemas.
- Create, deploy, and maintain ETL packages with SQL Server Integration Services (SSIS).
- Extract, transform, validate, and load data from multiple 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 reliability.
- Develop and enhance SQL Server Reporting Services (SSRS) reports, including parameterized and drill-down reports, with scheduled delivery.
- Implement data loading strategies including full, incremental, historical loads, and change data capture where applicable.
- Improve ETL packages, SQL queries, indexes, and execution plans to increase data processing efficiency and reporting speed.
- Apply indexing, table partitioning, aggregation, and data storage techniques based on workload requirements.
- Design and maintain data validation, reconciliation, and data quality checks to ensure accurate and consistent reporting.
- Monitor and troubleshoot SSIS package failures, SQL Server Agent jobs, report execution, and database performance issues.
- Automate ETL workflows, data refreshes, and reporting processes using SQL Server Agent or other orchestration tools.
- Integrate operational data into centralized data warehouse environments while maintaining consistency and traceability.
- Maintain comprehensive documentation including data dictionaries, ETL mappings, source-to-target mappings, and operational procedures.
- Support business intelligence and analytics teams by preparing well-structured datasets for dashboards and analytical applications.
- Implement database security, role-based access controls, backup, recovery, and data retention policies.
- Manage schema changes, ETL deployments, data warehouse enhancements, and migrations with minimal business disruption.
- Collaborate with software engineering, BI, DevOps, and infrastructure teams to improve 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) report development, parameterization, subscriptions, and performance tuning.
- Advanced SQL skills including complex joins, CTEs, window functions, aggregations, temporary tables, and performance tuning.
- Hands-on experience with PostgreSQL for SQL development, schema design, query optimization, and data integration.
- Solid understanding of data warehousing concepts such as dimensional modeling, star and snowflake schemas, slowly changing dimensions (SCD).
- Experience designing and maintaining ETL/ELT workflows, incremental data loads, data transformations, and historical data management.
- Ability to integrate and consolidate data from multiple heterogeneous sources.
- Knowledge of data quality, validation, reconciliation, data integrity, and error-handling best practices.
- Experience scheduling, monitoring, and troubleshooting data processing jobs and ETL workflows.
- Understanding of database security, access management, backup, recovery, and data governance principles.
- Strong analytical, troubleshooting, documentation, and collaboration skills.

Preferred Qualifications and Benefits

- Experience with ClickHouse, including columnar storage, MergeTree engines, partitioning, materialized views, and 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 scripting languages such as Python or PowerShell for automation and operational tasks.
- Understanding of Change Data Capture (CDC), incremental extraction, and data synchronization techniques.
- Experience with Power BI or other BI and visualization platforms.
- Familiarity with Git, version control, CI/CD pipelines, and automated deployment of database objects and SSIS packages.
- Expertise in 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 platforms.

Candidates meeting the above criteria and willing to work onsite in Islamabad are encouraged to apply. Only applications with relevant Data Warehouse experience will be considered.