Data Warehouse Engineer

Thompson Hine LLP

About the Role

Seeking a Data Warehouse Engineer to own the movement of data from source systems into Snowflake and maintain an on-premises SQL Server environment. This role focuses on developing high-quality data pipelines, a trusted Snowflake data warehouse, and well-modeled datasets to power analytics and decision-making.

Key Responsibilities

Pipeline Development

• Design, build, and maintain ingestion pipelines from source databases (SQL Server and other relational systems) into Snowflake, using batch, incremental, or change-data-capture patterns.
• Integrate data from third-party SaaS platforms such as CRM, product analytics, and finance systems.
• Monitor pipeline runs, resolve failures, and set up alerting, logging, and data-quality checks.
• Document source-to-target mappings, refresh schedules, and dependencies.

Data Warehouse Design and Modeling

• Build and maintain a layered warehouse: raw (landing) tables, cleansed and transformed staging layers, and curated presentation layers.
• Write and optimize transformation logic in SQL, including slowly changing dimensions, surrogate keys, and late-arriving data.
• Design and deliver dimensional data marts and data cubes (star schemas, aggregates, semantic or OLAP models) for finance, operations, and other business groups.
• Partner with report developers and analysts to translate business requirements into well-defined, tested data products.

Snowflake Administration

• Manage warehouses, databases, schemas, and roles in line with least-privilege access and naming conventions.
• Tune performance and control cost: warehouse sizing, clustering, query profiling, and resource monitors.
• Support masking policies and other data-protection controls and promote changes from development to production through a controlled release process.

SQL Server Administration

• Manage a small environment of SQL Server instances: installation, patching, configuration, capacity planning, and security.
• Tune performance: indexing, statistics, query plans, wait-stat analysis, and blocking or deadlock resolution.
• Own backup and recovery: design backup schedules, verify backups, and perform point-in-time restores.
• Plan, document, and test disaster recovery, including high availability options such as Always On availability groups, log shipping, or failover clustering.
• Maintain SQL Agent jobs, maintenance plans, and monitoring.

Operations and Collaboration

• Keep runbooks, data dictionaries, and architecture diagrams current.
• Produce clear technical documentation, including pipeline designs and data flow diagrams.
• Follow change management and secure development practices and take part in on-call or after-hours support for critical loads and outages.
• Partner with analytics and business stakeholders to translate requirements into well-modeled, reliable datasets.

Requirements & Tech Stack

• Strong SQL skills, including complex joins, window functions, CTEs, stored procedures, and performance tuning.
• Hands-on experience building and supporting data pipelines from source databases into a cloud data warehouse, preferably Snowflake.
• Working knowledge of dimensional modeling (Kimball), including fact and dimension design and slowly changing dimensions.
• 3+ years administering SQL Server in production: performance tuning, backup and restore, and disaster recovery planning and testing.
• Experience with ETL or ELT tooling and with scheduling or orchestration of data jobs.
• Ability to troubleshoot across systems, communicate clearly with technical and non-technical users, and document work thoroughly.

Qualifications

• Bachelor's degree in computer science, information systems, or a related field, or equivalent practical experience.
• Up to 5 years of experience in data engineering or data warehousing, including at least one warehouse built from scratch: raw tables, transformation layers, data marts, and data cubes.

Preferred Qualifications

• Snowflake SnowPro Core or Advanced certification, or a Microsoft SQL Server or Azure data certification.
• Experience with legal or professional services data, such as practice management and billing systems like Aderant Expert.
• Experience with SSRS, Power BI, or similar reporting tools, and with building semantic layers for them.
• Experience with dbt, Python, or PowerShell for transformation, automation, and testing.
• Familiarity with Snowflake features such as Streams, Tasks, Snowpipe, dynamic tables, role-based access control, and Cortex.
• Knowledge of secure coding practices (such as OWASP) and of data governance, masking, and audit requirements for confidential client data.
• Experience with source control and CI/CD for database and pipeline code.

This position requires the employee to work onsite at a specified Thompson Hine office location.