Data Warehouse Specialist

About the Role Prima Systems is looking for an experienced Data Warehouse Specialist to design, develop, optimize, and maintain scalable, reliable, and high-performance data warehouse solutions. The role focuses on Microsoft SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), PostgreSQL, data modeling, ETL processes, and analytical query optimization.

The ideal candidate will have strong SQL expertise and hands-on experience building and maintaining data warehouse solutions, integrating data from multiple sources, developing ETL workflows, and delivering reliable reporting datasets. Experience with ClickHouse and modern data warehousing practices will be an added advantage.

• Design, develop, and maintain scalable data warehouse architectures using Microsoft SQL Server and PostgreSQL.
• Develop and maintain data warehouse schemas, dimensional models, fact tables, dimension tables, star schemas, and snowflake schemas.
• Design, develop, deploy, and maintain ETL packages using SQL Server Integration Services (SSIS).
• Extract, transform, validate, and load data from multiple sources, including SQL Server, PostgreSQL, APIs, flat files, and other business applications.
• Develop and optimize complex SQL queries, stored procedures, views, functions, and data transformation logic in Microsoft SQL Server and PostgreSQL.
• Develop, maintain, and enhance SQL Server Reporting Services (SSRS) reports, including parameterized reports, drill-down reports, and scheduled report delivery.
• Design and maintain data warehouse loading strategies, including full loads, incremental loads, historical data loading, and change data capture where applicable.
• Optimize ETL packages, SQL queries, stored procedures, indexes, and execution plans to improve data processing efficiency and reporting performance.
• Implement appropriate indexing, table partitioning, aggregation, and data storage strategies based on workload requirements.
• Design and maintain data validation, reconciliation, data quality, and integrity checks to ensure accurate and consistent reporting.
• Monitor and troubleshoot SSIS package failures, SQL Server Agent jobs, scheduled data loads, report execution issues, and database performance problems.
• Schedule and automate ETL workflows, data refreshes, and reporting processes using SQL Server Agent and other suitable orchestration mechanisms.
• Integrate data from operational systems into centralized data warehouse environments while maintaining consistency and traceability.
• Maintain data dictionaries, technical documentation, ETL mappings, source-to-target mappings, data lineage, and operational procedures.
• Support business intelligence and analytics teams by preparing reliable, well-structured datasets for dashboards, reports, and analytical applications.
• Implement appropriate database security, role-based access controls, backup, recovery, and data retention practices.
• Manage database schema changes, ETL deployments, data warehouse enhancements, and migrations with minimal disruption to business operations.
• Collaborate with software engineering, business intelligence, DevOps, and infrastructure teams to improve data availability, reliability, and performance.
• Identify opportunities to improve data warehouse architecture, automate repetitive processes, reduce processing times, and enhance reporting efficiency.

• Bachelor's degree in computer science, Software Engineering, Information Technology, Data Engineering, or a related field.
• 10+ years of professional experience in data warehousing, SQL development, ETL development, or a related data engineering role.
• Strong hands-on experience with Microsoft SQL Server, including database development, stored procedures, views, indexing, query optimization, and execution plan analysis.
• Practical experience with SQL Server Integration Services (SSIS) for developing, deploying, scheduling, and troubleshooting ETL packages.
• Experience with SQL Server Reporting Services (SSRS), including report development, parameterization, data sources, subscriptions, and report performance optimization.
• Strong proficiency in SQL, including complex joins, CTEs, window functions, aggregations, temporary tables, and performance tuning.
• Hands-on experience with PostgreSQL, including SQL development, schema design, query optimization, and data integration.
• Strong understanding of data warehousing concepts, dimensional modeling, fact and dimension tables, star and snowflake schemas, and slowly changing dimensions (SCD).
• Experience designing and maintaining ETL/ELT workflows, incremental data loads, data transformations, and historical data management.
• Experience integrating and consolidating data from multiple databases and heterogeneous data sources.
• Knowledge of data quality, data validation, reconciliation, data integrity, and error-handling practices.
• Experience scheduling and monitoring data processing jobs and troubleshooting failed ETL workflows.
• Understanding of database security, access management, backup, recovery, and data governance principles.
• Strong analytical, troubleshooting, documentation, and cross-functional collaboration skills.

• Experience with ClickHouse, including columnar storage, MergeTree engines, partitioning, ordering keys, materialized views, and analytical query optimization.
• Experience with cloud data warehouse platforms such as Snowflake, Amazon Redshift, Google BigQuery, or Azure Synapse Analytics.
• Familiarity with data orchestration tools such as Apache Airflow or dbt.
• Experience with Python or PowerShell for data processing, automation, and operational scripting.
• Knowledge of Change Data Capture (CDC), incremental extraction, and data synchronization techniques.
• Experience with Power BI or other business intelligence 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.
• Understanding of data lake and lakehouse architectures and modern data engineering practices.
• Experience migrating or integrating data between SQL Server, PostgreSQL, and analytical database platforms.