Database Developer - ClickHouse/PostgreSQL

Database Developer

Type : Full-Time

Experience : 3- 6 Years

Number of positions : 2

About the Role :

We are looking for a skilled Database Developer to design, develop, and optimize database solutions across our insurance and wealth management analytics platform. The role is primarily focused on writing complex queries, stored procedures, functions, and supporting data pipelines across multiple database engines with a strong emphasis on ClickHouse (OLAP analytics), PostgreSQL, and SQL Server.

Key Responsibilities :

Query Development & Optimization :

- Write, review, and optimize complex SQL queries across ClickHouse, PostgreSQL, and SQL Server

- Develop and maintain stored procedures, functions, views, and triggers

- Analyze and tune slow queries using execution plans, indexing strategies, and query profiling

- Implement efficient pagination, filtering, and aggregation patterns for large datasets

- Write parameterized queries with proper SQL injection prevention (escaping, parameterization)

ClickHouse Development (Primary) :

- Develop and optimize analytical queries for dashboards covering Gross Sales, AUM, Revenue, Cash Flow, Pipeline, and Client data

- Design and implement MergeTree table engines, partitioning strategies, and materialized views

- Build time-series aggregations with MTD, QTD, YTD, and custom date range filters

- Optimize multi-dimensional queries (tier-wise, product-wise, category-wise, hierarchy-wise)

- Support direct SQL streaming for CSV report generation from ClickHouse HTTP interface

PostgreSQL Development :

- Write and maintain PostgreSQL functions following schema.fn_function_name conventions

- Develop functions for hierarchy resolution, employee-partner mapping, and master data lookups

- Optimize Npgsql/.NET integration understanding EF Core function calls and raw ADO.NET patterns

- Manage schema design across ciprevamp and partnermaster databases

SQL Server Development :

- Develop queries and procedures for task management and mid-office data (taskmanager DB)

- Support reporting and audit data requirements from SQL Server

Data Modeling & Design :

- Design normalized and denormalized schemas appropriate for OLTP vs OLAP use cases

- Model hierarchical data (employee - team - hierarchy structures)

- Design efficient schemas for time-filtered, multi-dimensional analytics data

- Define indexing strategies for high-read, analytics-heavy workloads

Collaboration & Integration :

- Work closely with backend developers (.NET/C#) to optimize DB operation classes (-DbOperations)

- Support DataTableToList
reflection mapping ensure column naming conventions align with C# DTOs

- Review and contribute to ClickHouse query strings in TransactionDbOperations, EngagementDbOperations, InsuranceDbOperations etc.

- Collaborate with DBA on version upgrades (PostgreSQL 14 - 18, MongoDB, Elasticsearch)

- Participate in code reviews for data access layer changes

Reporting & Data Pipelines :

- Build queries powering downloadable reports (CSV via ClickHouse HTTP, XLSX via downstream services)

- Support Apache Airflow pipeline queries and transformations

- Work with Talend ETL pipelines for data ingestion and transformation

- Assist in Elasticsearch index mapping and query DSL for search features

- Optimize MongoDB aggregation pipelines for document store queries

Required Skills :

- Primary DB : ClickHouse MergeTree engines, partitioning, materialized views, HTTP interface

- Relational DB : PostgreSQL 14+, SQL Server 2019+

- Query Skills : Complex joins, window functions, CTEs, subqueries, aggregations

- Stored Routines : PostgreSQL functions/procedures, SQL Server stored procedures

- Performance : Query plans, index design, query profiling, partitioning

- NoSQL : MongoDB aggregation pipelines, basic Elasticsearch query DSL

- Scripting : SQL, basic Python or Shell for data tasks

- Version Control : Git

- OS : Linux (basic log access, file permissions)

Good to Have :

- Experience with ClickHouse at scale distributed queries, sharding, ReplacingMergeTree, AggregatingMergeTree

- Understanding of .NET / C# data access patterns ADO.NET, EF Core, Npgsql, DataTable mapping

- Knowledge of Redis data structures for caching query results

- Familiarity with AWS RDS (PostgreSQL) instance characteristics vs on-prem

- Experience with Apache Airflow DAG - DB interaction

- Exposure to Talend or other ETL tools for pipeline-to-database flows

- Understanding of financial/wealth management data AUM, NAV, SIP, portfolio structures

- Knowledge of data security column masking, row-level security, audit logging

Qualifications :

- Bachelor's degree in Computer Science, Information Technology, or equivalent

- 3- 6 years of hands-on database development experience in production environments

- Strong understanding of relational database design and normalization

- Experience developing for OLAP/analytics workloads (not just OLTP)

- Demonstrated ability to debug and optimize poorly performing queries

- Good communication skills for working across Dev, DBA, and Analytics teams

Technical Context (What You'll Work On) :

The platform serves Relationship Managers (RMs) and Fund Relationship Managers (FRMs) in an insurance/wealth management firm. Databases power :

- Gross Sales, AUM, Revenue : ClickHouse (High-volume OLAP reads)

- Cash Flow, Pipeline : ClickHouse (Time-filtered aggregations)

- Client Directory, Engagement : ClickHouse (Paginated, filtered queries)

- Employee/Partner Hierarchy : PostgreSQL (partnermaster) (Function-based lookups)

- CIP Platform Data : PostgreSQL (ciprevamp) (Transactional + function calls)

- Task / Mid-office Data : SQL Server (taskmanager) (Workflow queries)

- Document Storage : MongoDB (Aggregation pipelines)

- Search : Elasticsearch (Query DSL)

- Session / Cache : Redis (Key-value, hash structures)

Immediate Priorities (First 90 Days) :

1. Understand existing ClickHouse schema table engines, partitioning, key query patterns

2. Review and optimize slow queries flagged from Serilog production logs

3. Contribute to PostgreSQL 14 - 18 migration validate stored functions for compatibility

4. Document all PostgreSQL functions in FunctionProcedureConstantPg with parameter/return descriptions

5. Establish query performance baselines across all major dashboard endpoints

(ref:hirist.tech)