Snowflake Data Engineer

Loading looks simple until a file is malformed. You choose formats and options, load with COPY INTO, validate and capture errors, size files for parallelism, and keep a record that prevents duplicate loads.

• File formats and format options
• COPY INTO options, validation and error handling
• File sizing and parallel loading
• Load history and avoiding duplicate loads
• Schema detection and evolution
• Loading semi-structured files

You build A bulk load with validation, error capture and an audit of what loaded.

Not every source lands in a bucket. You call REST APIs from inside Snowflake with external access, move on-premises data with drivers, connectors and the command line, and pick a pattern that suits each source.

• External access integrations and network rules
• Calling a REST API from a function or procedure
• Secrets for outbound calls
• Partner and open-source connectors
• Moving on-premises data with drivers and the CLI
• Choosing a pattern for each source

You build A pipeline that pulls from a REST API and an on-premises database into Snowflake.

Transformation is where pipelines hold their logic. You write advanced SQL, use Snowpark where Python fits better, package logic as functions and procedures, and flatten semi-structured records into tables.

• Advanced SQL for transformation
• Snowpark DataFrames and when they fit
• User-defined functions and table functions
• Stored procedures for multi-step logic
• FLATTEN and VARIANT for semi-structured data
• Testing transformation code

You build A transformation written in SQL and Snowpark with tests for its logic.

Reprocessing everything each run is wasteful. You capture changes with streams, apply them to targets with MERGE, maintain slowly changing dimensions, and learn what makes a stream go stale.

• Standard, append-only and insert-only streams
• Stream offsets and consumption
• MERGE for upserts and deletes
• Slowly changing dimensions
• Streams on views and external tables
• Preventing and recovering stale streams

You build A change-capture flow using a stream and MERGE that keeps a dimension table current.

Freshness has a cost and a limit. You compare Snowpipe, Snowpipe Streaming and the Kafka connector on latency, cost and delivery behavior, and handle duplicates, ordering and errors.

• Snowpipe and auto-ingest from cloud events
• Snowpipe Streaming and its client SDKs
• The Kafka connector and its delivery behavior
• Latency and cost of each approach
• Duplicates, ordering and error handling
• Monitoring pipe health

You build A near real-time ingestion path with measured latency from event to table.

Ingestion is only the first leg. You chain streams, tasks and dynamic tables into a flow with a stated freshness target, choose between them for each step, and alert when the flow falls behind.

• Task graphs triggered by streams
• Dynamic tables and target lag
• Choosing tasks, streams or dynamic tables
• Serverless tasks and their cost
• Error handling and alerting in a flow
• Measuring end-to-end freshness

You build A streaming flow with a defined freshness target, built two ways and compared.

Data often has to live in more than one place. You replicate databases and failover groups, share data with other accounts, account for the cost and lag of crossing regions, and keep track of provenance across copies.

• Database replication and failover groups
• Replication schedules and cost
• Secure data sharing and listings
• Cross-region and cross-cloud considerations
• Replicating pipelines and policies along with data
• Tracking provenance across copies

You build A replication and sharing design for data used in two regions, with its cost estimated.

A bigger warehouse is not always a faster pipeline. You size warehouses to the work, use multi-cluster scaling for concurrency, choose Snowpark-optimized compute for memory-heavy jobs, and isolate workloads so they do not compete.

• Warehouse sizes and their effect on runtime and cost
• Multi-cluster warehouses and concurrency
• Snowpark-optimized warehouses for memory-heavy work
• Separating workloads by warehouse
• Auto-suspend, auto-resume and scheduling
• Serverless features and when to use them

You build A compute design that assigns each pipeline stage to a sized, isolated warehouse.

Tuning starts with evidence. You read query profiles, history and load views to see where time goes, recognize spilling, queuing and poor pruning, and find the slowest step of a pipeline.

• The query profile: operators and time
• Query history and warehouse load views
• Spilling, queuing and pruning
• Finding the slowest step of a pipeline
• Writing a monitoring query

You build A performance review of a slow pipeline that names its bottleneck with evidence.

Once you know the bottleneck you can choose the fix. You apply clustering keys, search optimization and query acceleration where they pay off, use caches deliberately, and measure each change against the baseline.

• Clustering keys and reclustering cost
• The search optimization service
• The query acceleration service
• Result and warehouse caching
• Rewriting queries for better pruning
• Measuring a fix before and after

You build A tuning change applied to a slow table, with before and after numbers.

You build one pipeline from lake and API sources to a shared, near real-time result, size its compute, and tune it from its own metrics. Then you explain each choice and what it costs.

• Scoping sources, freshness and consumers
• Building ingestion, transformation and streaming
• Sizing compute and isolating workloads
• Profiling and tuning the finished flow
• Presenting the pipeline and its trade-offs

You build A working near real-time pipeline with a tuning log and cost estimate.