Senior Data Engineer
Location: Oaks PA | Onsite
Duration: 6+ months
Locals preferred for onsite
Job Description:
We are seeking a Senior Data Engineer with 10-15 years of experience, including deep hands-on expertise in dbt and Apache Airflow, to architect, build, and own scalable, production-grade data transformation and orchestration platforms. The successful candidate will set technical direction for dbt project design and Airflow orchestration patterns, establish engineering standards and data-quality practices, mentor other engineers, and partner with architects and business stakeholders to deliver reliable, well-governed data products. Strong SQL, solid Oracle database knowledge, and proficient Python skills are expected. Hands-on dbt expertise on Snowflake is mandatory, and the candidate must have practical, hands-on Apache Airflow experience in both creating new DAGs and executing, monitoring, and managing DAG runs.
Key Responsibilities
- 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.
Required Skills and Qualifications
- 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.
Preferred Qualifications
- 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.
