ETL Tester

Position Summary:

We are looking for a Quality Engineer to ensure the accuracy, completeness, and reliability of data onboard from the Yardi Diamond database into our Snowflake platform.

This role is responsible for designing and executing comprehensive test strategies covering data pipelines, stored procedures, staging tables, views, and outbound file exports.

The QE will validate that Diamond data integrates correctly with the existing Yardi EMEA IAR data estate and meets business and regulatory requirements.

Key Responsibilities:

- Test Strategy & Planning: Define and document the end-to-end test strategy for the Yardi Diamond onboarding, covering data extraction validation, staging layer accuracy, transformation correctness, and outbound file integrity.

- Source-to-Target Validation: Design and execute source-to-target reconciliation tests comparing Yardi Diamond SQL Server source data against Snowflake staging tables. Validate row counts, column-level data integrity, data types, NULL handling, and boundary values across all onboarded entities.

- Stored Procedure Testing: Test all new and modified SQL stored procedures, verifying correct data transformation, SOURCEDB tagging, ETL logging to CIM.ETL_LOG_SNOWFLAKE, error handling, and exception path behavior.

- Incremental & Historical Load Testing: Validate incremental load correctness, historical backfill accuracy, and idempotency of pipeline re-runs.

- Multi-Currency & Exchange Rate Validation: Verify that Diamond data correctly integrates with the currency framework, including exchange rate lookups, base currency conversions, and multi-currency transaction amounts.

- Fund & Investor Data Validation: Validate fund hierarchy, batch transactions, fund transactions, capital roll-forward data, and the entity tree for Diamond-sourced records.

- KYC & Compliance Data Testing: Ensure KYC_DATA and ACHDATA records from Diamond meet compliance requirements - validate mandatory fields, data masking, sensitive data handling, and regulatory field completeness.

- File Export Validation: Verify outbound CSV files generated by export procedures - validate file format, header structure, field mapping, delimiters, encoding, and content against the specification.

- View & Consumption Layer Testing: Test all new views and the downstream C_IAR_TM consumption layer to ensure Diamond data is correctly surfaced for Transfer Agent reporting and analytics.

- Regression Testing: Build and maintain a regression test suite ensuring that Diamond onboarding does not break existing EMEA IAR data, ETL procedures, views, or downstream reports.

- Data Quality Rules & Monitoring: Implement automated data quality checks using Snowflake Data Metric Functions (DMFs), dbt tests, or custom SQL assertions for ongoing monitoring of Diamond data quality.

- Performance & Load Testing: Validate ETL pipeline performance under expected Diamond data volumes. Identify bottlenecks and work with the developer to optimise queries, warehouse sizing, and clustering.

- Defect Management: Log, track, and retest defects. Provide clear defect reports with SQL evidence, expected vs. actual results, and root cause analysis.

- UAT Support: Support business users during User Acceptance Testing by preparing test data, writing test scripts, and triaging reported issues.

Required Skills & Experience:

- Data Quality Engineering: 3+ years' experience in data/ETL testing within a data warehouse or data platform environment.

- SQL: Strong SQL skills for writing validation queries - aggregations, joins, EXCEPT/MINUS comparisons, window functions, and data profiling queries in Snowflake.

- Snowflake: 2+ years' experience working with Snowflake - querying INFORMATION_SCHEMA, ACCOUNT_USAGE, understanding stages, file formats, stored procedures, tasks, and streams.

- Source-to-Target Testing: Proven experience designing and executing source-to-target reconciliation for enterprise data migration or onboarding projects.

- Test Automation: Experience with data test automation frameworks - dbt tests, Great Expectations, Soda, custom SQL-based test harnesses, or Snowflake DMFs.

- Yardi (Desirable): Familiarity with Yardi Voyager or Diamond database structures and property management / fund accounting domain concepts.

- Python (Desirable): Ability to write Python scripts for data validation, test data generation, or automated reporting.

- Test Management: Experience with test case management tools (JIRA, Zephyr, TestRail) and defect tracking workflows.

- Real Estate / Investment Management Domain: Understanding of fund transactions, capital calls, investor reporting, KYC processes, and multi-currency accounting is a strong advantage.

- Regulatory Awareness: Familiarity with data handling requirements for financial services - GDPR, AML/KYC regulations, and data sensitivity classifications.

Technical Environment:

Snowflake, SQL, Yardi Diamond, SQL Server, Python, dbt tests, Great Expectations / Soda, JIRA, Comply advantage, Git.

Skills

SQL, ETL, Testing, Snowflake DB, Data Build Tool, ETL/Datawarehouse Testing, Test Strategy, Regression Testing