Senior Data Engineer in Oaks PA

Senior Data Engineer

Location: Oaks PA | Onsite

Duration: 6+ months

Locals preferred for onsite

Job Description:

• Lead the design and architecture of dbt projects, including project structure, layering conventions (staging, intermediate, marts), naming standards, and reusable package strategy.
• Design and develop advanced dbt models using complex SQL, choosing optimal materializations including views, tables, ephemeral, and incremental models with appropriate unique keys, merge strategies, and on_schema_change handling.
• Build, deploy, and optimize dbt models on Snowflake, applying Snowflake-specific configurations (transient tables, clustering keys, query tags, grants, and warehouse selection) and tuning dbt workloads for performance and credit consumption.
• Apply advanced dbt capabilities including Jinja macros, custom generic tests, snapshots (SCD Type 2), seeds, hooks, exposures, model contracts, versions, groups, access controls, and environment-specific configuration via profiles and vars.
• Build and maintain shared dbt macros and packages (e.g., dbt_utils, dbt_expectations) and promote reuse and consistency across teams.
• Own dbt documentation, lineage, and metadata, and use state-based selection (state:modified, deferral) and slim CI to speed up development and deployment.
• Create new Apache Airflow DAGs from scratch and architect Airflow orchestration, designing scalable, idempotent DAGs with clear dependency management, retries, SLAs, alerting, and failure-handling patterns.
• Execute and operate DAGs hands-on: trigger scheduled, manual, and parameterized runs through the Airflow UI and CLI, monitor task execution, review logs, clear and re-run failed tasks, and perform backfills.
• Apply advanced Airflow concepts including TaskFlow API, dynamic task mapping, task groups, sensors and deferrable operators, datasets/data-aware scheduling, XComs, pools, priority weights, custom operators and hooks, and connections and variables management.
• Integrate dbt with Airflow using proven patterns (e.g., Astronomer Cosmos, BashOperator/KubernetesPodOperator, or dbt Cloud operators), enabling model-level visibility, selective runs, and safe re-runs and backfills.
• Define and enforce orchestration standards for scheduling, catchup and backfill strategy, parameterization, logging, observability, and environment promotion.
• Write and tune high-performance SQL using joins, CTEs, aggregations, window functions, and set-based logic; analyze execution plans and optimize for performance and cost at scale.
• Work with Oracle databases to understand and optimize schemas, partitioning, indexing, data types, constraints, query behavior, and connectivity requirements.
• Develop Python utilities, custom Airflow components, and automation for validation, file handling, database interaction, and operational support.
• Establish a data-quality and testing strategy across unit tests, dbt tests, reconciliation controls, freshness checks, and pre-production validation.
• Lead root-cause analysis for complex pipeline failures, drive durable fixes, and coordinate re-runs and backfills without introducing data inconsistencies.
• Lead technical design, code reviews, and CI/CD practices for dbt and Airflow, and contribute to platform upgrades and roadmap decisions.
• Mentor and coach data engineers on dbt, Airflow, and SQL best practices, and act as the go-to technical expert for the team.
• Create and maintain technical documentation, runbooks, and design standards for models, DAGs, dependencies, and operational procedures.
• Collaborate with architects, analysts, QA teams, platform teams, and business stakeholders to translate requirements into dependable, well-governed data products.

• 7-10 years of relevant experience in data engineering, analytics engineering, or ETL/ELT development, including at least 3-4 years of hands-on production experience with dbt and Apache Airflow.
• Must have: Expert-level, hands-on dbt experience, including designing and maintaining large dbt projects, advanced incremental strategies, Jinja macros, custom tests, snapshots, packages, model contracts, documentation, and lineage.
• Required: Hands-on Apache Airflow experience, including creating new DAGs from scratch and executing DAGs (triggering manual and parameterized runs, monitoring task execution, reviewing logs, clearing and re-running failed tasks, and running backfills), plus advanced features such as dynamic task mapping, TaskFlow API, sensors and deferrable operators, custom operators and hooks, SLAs, and alerting.
• Proven experience orchestrating dbt with Airflow in production and operating these pipelines at scale.
• Expert SQL skills, including performance tuning, execution-plan analysis, and troubleshooting complex transformation logic.
• Solid understanding of Oracle Database concepts, including schemas, database objects, partitioning, indexing, query optimization, and connectivity.
• Required: Good knowledge of Snowflake and hands-on experience building and running dbt projects on Snowflake, including the dbt-snowflake adapter, Snowflake-specific materializations and incremental strategies (merge, delete+insert), transient tables, clustering keys, query tags, warehouse sizing for dbt runs, and role-based access controls.
• Proficiency in Python for automation, data processing, and extending Airflow with custom components.
• Strong experience with Git, branching strategies, pull requests, and CI/CD pipelines for dbt and Airflow deployments.
• Demonstrated experience leading technical design, conducting code reviews, and mentoring engineers.
• Excellent analytical, debugging, problem-solving, documentation, and stakeholder communication skills.

• Experience with data warehousing concepts, dimensional modelling (Kimball), Data Vault, and enterprise data lineage.
• dbt Analytics Engineering Certification and/or Astronomer Certification for Apache Airflow.
• Experience with Astronomer Cosmos, dbt Cloud, dbt Semantic Layer/MetricFlow, or data contracts.
• Experience running Airflow on Kubernetes, Astronomer, Amazon MWAA, or Google Cloud Composer, including upgrades and capacity planning.
• Experience with data observability and quality tools (e.g., Great Expectations, Elementary, Monte Carlo), secrets management, and environment configuration.
• Exposure to cloud data platforms such as Databricks, BigQuery, or Redshift, or additional relational databases.
• Familiarity with Agile delivery, release management, and production support in a regulated or financial-services environment.