Snowflake Data Architect - washington,DC

Job Title: Snowflake Data Architect

Location: Washington DC (onsite) - Local

Job Description:

Purpose

Build and manage the modern cloud data platform across ingestion, transformation, and delivery layers.

Role Summary:

This role combines Snowflake engineering, Fivetran-based ingestion, and dbt analytics engineering into one end-to-end data engineering position. Will modernize legacy SQL Server / SSIS / SSAS workloads into a scalable cloud platform with stronger automation, validation, lineage, and reporting readiness.

This role leads the technical implementation of data storage, transformation, access patterns, and performance optimization in Snowflake, while ensuring the platform supports business reporting, historical analysis, governance, and future AI-enabled use cases.

Key Responsibilities:

Develop, Design, configure, and maintain the Snowflake platform, including databases, schemas, warehouses, roles, security policies, and environment setup across development, test, and production.
Implement and manage cloud-based ingestion using Fivetran for legacy, operational, and SaaS source systems, including full-load, incremental, and CDC-based patterns.
Build and optimize the target data architecture across raw, staging, curated, and reporting-ready layers, aligned to medallion-style or equivalent modular design patterns.
Develop, test, and maintain dbt models that replace legacy SSIS transformation logic and support curated business-ready data assets for reporting, reconciliation, and analytics.
Translate legacy constructs such as SCD Type 2 handling, lookup logic, conditional branching, and other ETL patterns into modern Snowflake and dbt implementations.
Configure AWS integrations required for the data platform, including S3 stages, IAM roles, storage integrations, encryption support, and secure connectivity patterns.
Establish and support source-to-target mappings, metadata consistency, schema evolution handling, and documentation of field-level lineage across ingestion and transformation layers.
Implement automated data quality checks, freshness checks, reconciliation controls, and exception handling to improve trust in the data before it reaches reporting layers.
Monitor ingestion pipelines, connector health, transformation runs, and Snowflake workloads; troubleshoot failures, schema drift, performance issues, and data incidents.
Optimize Snowflake cost and performance through workload isolation, warehouse sizing, clustering, query tuning, and platform monitoring.
Support downstream analytics and reporting teams by delivering trusted, well-documented, analytics-ready data structures compatible with Sigma and other governed reporting tools.
Contribute to CI/CD, release automation, and Git-based engineering workflows for dbt, Snowflake, and data pipeline changes.
Produce operational documentation, configuration standards, runbooks, and handover materials for ongoing support and client operations teams.
Work closely with architects, analysts, reporting teams, and client stakeholders to ensure the solution improves automation, reduces manual dependency, and supports a more scalable operating model.

Required Skills and Experience:

Strong hands-on experience implementing Snowflake in enterprise environments.
Deep knowledge of SQL, query optimization, performance tuning, and warehouse design.
Experience migrating from legacy EDW platforms such as SQL Server, SSIS, and SSAS.
Exp. with dbt, Fivetran, AWS, and Sigma BI/reporting integrations.
Hands-on experience with Fivetran or comparable cloud ingestion tools.
Strong AWS fundamentals: S3, IAM, KMS, VPC, PrivateLink for Snowflake connectivity
Strong understanding of source-to-target mapping, CDC concepts, and incremental loading.
Experience working with Snowflake and cloud-based data platforms.
Ability to write SQL for data validation and reconciliation between source and Bronze landing tables

NICE TO HAVE:

Snowflake SnowPro Core or Advanced: Data Engineer certification
Experience with Snowflake cost optimization on large-scale financial data workloads
Familiarity with dbt project structure and Snowflake-specific dbt materializations (dynamic tables