Redshift Subject Matter Expert

Role – Redshift Subject Matter Expert (Senior) 
Statewide Agile Resources and Teams 2024–Labor Category – Subject Matter Expert (Senior) 
Maryland-Department of General Services-- Maryland Benefits/Maryland Department of Public Safety & Correctional  
$60/hr on c2c
visa : H4-EAD,L2-EAD, GC, USC, GC-EAD
Position Description: AWS Redshift Subject Matter Expert (SME) will be providing deep technical leadership, optimization, and strategic guidance for an organization's cloud data warehousing solutions, covering both Provisioned and Serverless deployments. This role requires a blend of hands-on technical skills, architectural oversight, and strong communication to ensure Redshift environments are scalable, cost-efficient, and highly performant.
1. Architectural & Strategic Leadership
A. Solution Design: Architect, design, and implement scalable, secure, and cost-effective data warehousing solutions using both Redshift Provisioned and Redshift Serverless.
B. Deployment Strategy: Determine the optimal deployment model (Provisioned vs. Serverless) for various workloads, considering factors like predictability, cost, concurrency, and required fine-grained control.
C. Modernization: Lead efforts to modernize existing on-premise or cloud data warehouses (e.g., Teradata, Snowflake, etc.) to AWS Redshift, including developing migration strategies and execution plans.
D. Integration: Define and implement integration patterns with the broader AWS ecosystem, including Amazon S3 (for Redshift Spectrum/Data Lake), AWS Glue, Amazon Kinesis, Amazon EMR, and machine learning services (Redshift ML).
2. Performance and Optimization
A. Query Tuning: Expertly analyze, optimize, and refactor complex SQL queries for maximum performance and cost efficiency, utilizing features like Workload Management (WLM) in Provisioned clusters and capacity settings in Serverless.
B. Design Optimization: Tune data warehouse performance by implementing best practices for data distribution keys, sort keys, and compression encodings for both Provisioned nodes (RA3) and Serverless data.
C. Monitoring and Troubleshooting: Establish and manage advanced monitoring using Amazon CloudWatch and Redshift system tables to preemptively identify and resolve performance bottlenecks, resource contention, and system failures.
D. Cost Management: Implement effective cost controls, managing node types/reserved instances for Provisioned clusters and defining Redshift Processing Units (RPUs) capacity and usage limits for Serverless environments to ensure predictable spending.
3. Data Governance and Operations
Security: Enforce enterprise-level data security, including implementing encryption (at rest and in transit), managing access control via AWS IAM and database user management, and configuring VPC endpoints.
A. High Availability & Disaster Recovery (HA/DR): Define and implement backup/restore strategies using snapshots, cross-region replication, and leverage the inherent HA features of Redshift Serverless and Multi-AZ for Provisioned.
B. Data Modeling & ETL/ELT: Advise on and implement best-practice data models (e.g., Dimensional, Data Vault) and design efficient Extract, Transform, and Load (ETL)/Extract, Load, and Transform (ELT) pipelines.
Category Key Skills & Experience
AWS Redshift Deep, hands-on experience with both Provisioned (RA3 nodes) and Serverless architectures.
Data Warehousing Expert knowledge of data warehousing concepts, ETL/ELT methodologies, and dimensional modeling.
SQL Mastery Advanced proficiency in complex SQL, DDL, and DML, with a focus on Redshift-specific commands (e.g., COPY, UNLOAD).
Performance Tuning Query optimization, WLM configuration, distribution styles, sort keys, and understanding query execution plans.
AWS Ecosystem Hands-on experience with core AWS services: S3, Glue, IAM, CloudWatch, Lambda, Lake Formation, and Kinesis.
Programming Scripting/Automation skills using Python (for ETL/API interaction) or similar languages.
Education: This position requires a Bachelor’s in Computer Science, Information Systems, or a related field or equivalent work experience. (Note: A Master’s degree is preferred.)
A. General Experience: The proposed candidate must have at least three (15) years of experience in data warehousing, with at least 3 years focused specifically on AWS Redshift.
B. Certifications: AWS Certified Data Analytics - Specialty or AWS Certified Solutions Architect - Professional is highly desirable.
C. Communication: Exceptional ability to communicate complex technical concepts to both technical teams and executive stakeholders.
D. Mentorship: Act as a technical mentor for Data Engineers, Administrators, and Analysts.