ETL Tetser

Role: : ETL Tester

Location: San Antonio Texas

Client: HCL

Job Description

Informatica PowerCenter, dbt (Data Build Tool), SQL, Snowflake, Git, JIRA

QA Strategy and Planning

• Define QA strategy for a hybrid ETL stack involving Informatica, and dbt for transformation/modeling.
• Analyze STTMs (Source-to-Target Mapping documents), business rules, and dbt model definitions to derive test scope.
• Plan and manage test phases across Program Increments (PIs) or releases: unit testing support, system testing, integration testing, and UAT support.

Informatica-Specific QA Responsibilities

• Review and validate Informatica mappings, workflows, sessions, and parameter files.
• Validate:
• Source data extraction (flat files, RDBMS, mainframes, etc.)
• Data transformations via mappings (joins, lookups, aggregations, filters, etc.)
• Load strategies to staging/ODS layers (insert/update strategies, truncate/load, CDC).
• Use SQL queries and data comparison tools to perform reconciliation and transformation validation.
• Validate job dependencies and control table mechanisms (if used).
• Monitor job runs via Informatica Monitor/Workflow logs, and ensure correct execution paths.

Dbt-Specific QA Responsibilities

• Validate dbt models, tests, and documentation in the analytics layer.

Review and test:

• Model SQL logic for business rule accuracy and performance
• YAML files for correct metadata and testing configurations
• dbt tests (unique, not null, accepted values, relationships, custom tests)
• Execute dbt test suites using dbt test and investigate failures.
• Collaborate with analytics engineers to validate , materialization strategies (table, view, incremental), and source freshness logic.
• Review generated SQL in target/compiled to ensure correct transformations.

Data Validation & Reconciliation

• Perform full and sampled data validation across source staging ODS analytics data layers.
• Write complex SQL queries to compare record counts, aggregates, and field-level data across layers.
• Ensure data consistency and integrity using both manual validation and dbt tests.
• Validate PII masking and data obfuscation where applicable.

Automation and DevOps Integration

• Build and maintain reusable SQL-based test automation scripts for recurring validations.
• Integrate QA checks in CI/CD pipelines using git Actions.
• Monitor and validate Informatica batch schedules and dbt Cloud jobs (or CLI triggers).
• Contribute to QA dashboards and test reporting automation.

Defect Management & Reporting

• Log and track defects in JIRA, categorize by layer (Informatica/Dbt), and severity.
• Facilitate daily defect triage calls with data engineering teams.
• Provide detailed test execution status reports and test coverage metrics by data domain.

Coordination & Stakeholder Engagement

• Work closely with data engineers, analytics engineers, data modelers, and product owners.
• Coordinate test data provisioning and masking with data stewards.
• Participate in scrum ceremonies, sprint planning, and retrospectives.
• Represent QA during PI Planning and system demos (SAFe).