Data Warehouse Specialist

Prima Systems is seeking a seasoned Data Warehouse Specialist to design, develop, and maintain robust, scalable, and high-performance data warehouse solutions. This role primarily involves working with Microsoft SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and PostgreSQL. The successful candidate will be responsible for data modeling, ETL process development, and optimizing analytical queries to support reliable reporting and business intelligence needs. Experience with ClickHouse and modern data warehousing techniques will be considered an advantage.

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 using SSIS, ensuring efficient data extraction, transformation, validation, and loading from various sources such as SQL Server, PostgreSQL, APIs, and flat files.
- Write and optimize complex SQL queries, stored procedures, views, and functions to enhance data transformation and reporting performance.
- Develop and maintain SSRS reports, including parameterized and drill-down reports, with scheduled delivery.
- Implement and maintain data loading strategies such as full loads, incremental loads, historical data loading, and change data capture where applicable.
- Optimize ETL workflows, SQL queries, indexing, and execution plans to improve processing efficiency and reporting speed.
- Apply indexing, table partitioning, aggregation, and data storage strategies based on workload requirements.
- Ensure data quality through validation, reconciliation, and integrity checks to deliver accurate and consistent reports.
- Monitor and troubleshoot SSIS package failures, SQL Server Agent jobs, scheduled data loads, report execution issues, and database performance.
- Automate ETL workflows, data refreshes, and reporting processes using SQL Server Agent or other orchestration tools.
- Integrate data from operational systems into centralized data warehouses while maintaining 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, reports, and analytical applications.
- Implement database security measures, role-based access controls, backup, recovery, and data retention policies.
- Manage database schema changes, ETL deployments, and data warehouse enhancements with minimal disruption.
- Collaborate with software engineering, BI, DevOps, and infrastructure teams to improve data availability, reliability, and performance.
- Identify opportunities to enhance data warehouse architecture, automate processes, reduce processing times, and improve 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 SSIS for ETL package development, deployment, scheduling, and troubleshooting.
- Proficiency in SSRS report development, including parameterization, data sources, subscriptions, and performance tuning.
- Advanced SQL skills covering complex joins, CTEs, window functions, aggregations, temporary tables, and performance tuning.
- Hands-on experience with PostgreSQL, including schema design, SQL development, query optimization, and data integration.
- Deep understanding of data warehousing concepts such as dimensional modeling, star and snowflake schemas, fact and dimension tables, and slowly changing dimensions (SCD).
- Experience designing and maintaining ETL/ELT workflows, incremental data loads, data transformations, and historical data management.
- Proven ability to integrate and consolidate data from multiple heterogeneous sources.
- Knowledge of data quality, validation, reconciliation, integrity, and error-handling best practices.
- Experience scheduling, monitoring, and troubleshooting ETL workflows and data processing jobs.
- 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 Python or PowerShell for automation and operational scripting.
- 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 database platforms.

This role offers the opportunity to work with cutting-edge data technologies and contribute to the continuous improvement of data warehousing practices in a dynamic environment.