DEA-C02 — Snowflake SnowPro Advanced: Data Engineer Quick Reference

How to Use This Quick Reference

This independent Quick Reference supports preparation for the Snowflake SnowPro Advanced: Data Engineer (DEA-C02) exam from Snowflake. It focuses on practical decisions a data engineer must make in Snowflake: ingestion pattern, transformation design, stream/task orchestration, table optimization, governance, and troubleshooting.

Use it to review:

• Which Snowflake feature fits a scenario.
• How ingestion, CDC, and orchestration components interact.
• High-yield SQL patterns and common traps.
• Performance and reliability signals to check during troubleshooting.

Core Snowflake Architecture Map

Feature Selection Matrix

Data Loading and Ingestion

Loading Pattern Decision Table

Stage and File Format Reference

High-Yield COPY INTO Pattern

CREATE OR REPLACE FILE FORMAT ff_json
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;

COPY INTO raw.events
  (payload, source_file, loaded_at)
FROM (
  SELECT
    $1,
    METADATA$FILENAME,
    CURRENT_TIMESTAMP()
  FROM @raw_event_stage
)
FILE_FORMAT = (FORMAT_NAME = ff_json)
ON_ERROR = 'CONTINUE';

Key points:

• Use named stages and file formats for repeatability.
• Persist source file metadata for lineage and replay.
• Monitor rejected rows/files; ON_ERROR = 'CONTINUE' is not a data quality solution by itself.
• COPY maintains load metadata, so reloading the same file requires intentional design.

Snowpipe Reference

CREATE OR REPLACE PIPE ingest.event_pipe
  AUTO_INGEST = TRUE
AS
COPY INTO raw.events
FROM @raw_event_stage
FILE_FORMAT = (FORMAT_NAME = ff_json);

Transformation and Orchestration

Streams, Tasks, and Dynamic Tables

Stream Metadata

Task and Stream Pattern

CREATE OR REPLACE STREAM stg.orders_stream
  ON TABLE stg.orders;

CREATE OR REPLACE TASK mart.merge_orders_task
  WAREHOUSE = etl_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
  WHEN SYSTEM$STREAM_HAS_DATA('stg.orders_stream')
AS
MERGE INTO mart.orders AS t
USING (
  SELECT *
  FROM stg.orders_stream
  WHERE METADATA$ACTION = 'INSERT'
) AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
  t.status = s.status,
  t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
  (order_id, status, updated_at)
VALUES
  (s.order_id, s.status, s.updated_at);

ALTER TASK mart.merge_orders_task RESUME;

Exam reminders:

• Consuming a stream occurs when the DML statement using it commits.
• Wrap multi-step stream consumption in a transaction when consistency matters.
• SYSTEM$STREAM_HAS_DATA is useful as a task WHEN condition, but the downstream SQL still needs to be idempotent.
• Separate ingestion, validation, merge, and serving layers when troubleshooting clarity matters.

Dynamic Table Pattern

CREATE OR REPLACE DYNAMIC TABLE mart.daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = etl_wh
AS
SELECT
  order_date,
  SUM(net_amount) AS net_sales,
  COUNT(*) AS order_count
FROM clean.orders
GROUP BY order_date;

Table Design and Storage

Table Type Reference

Micro-Partition and Clustering Reference

Choose clustering when:

• Table is large enough that pruning matters.
• Queries repeatedly filter on predictable columns.
• Natural load order does not already cluster data well.
• Maintenance cost is justified by query improvement.

Avoid clustering when:

• Table is small.
• Filters are unpredictable.
• High-churn DML causes excessive maintenance.
• Query bottleneck is joins, spilling, or concurrency rather than scan pruning.

Optimization Feature Comparison

Semi-Structured Data

Data Types and Functions

SELECT
  payload:customer.id::STRING AS customer_id,
  item.value:sku::STRING AS sku,
  item.value:quantity::NUMBER AS quantity
FROM raw.events,
LATERAL FLATTEN(input => payload:items) AS item;

High-yield distinctions:

• JSON null and SQL NULL are not always equivalent in processing.
• Cast extracted values before joining, grouping, or applying numeric logic.
• Flatten only the level required; excessive flattening can explode row counts.
• For frequently queried attributes, consider projecting into typed columns or derived tables.

Snowpark, UDFs, and Stored Procedures

Practical design rules:

• Prefer SQL set operations for relational transformations.
• Use Snowpark when code libraries, complex logic, or developer language preference justify it.
• Keep large datasets inside Snowflake; avoid collecting data to the client.
• Use stored procedures for orchestration, not as a substitute for set-based SQL performance.

Security, Governance, and Data Protection

RBAC and Grants

Example least-privilege pattern:

GRANT USAGE ON WAREHOUSE etl_wh TO ROLE data_engineer_role;
GRANT USAGE ON DATABASE analytics TO ROLE data_engineer_role;
GRANT USAGE ON SCHEMA analytics.stg TO ROLE data_engineer_role;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA analytics.stg
  TO ROLE data_engineer_role;

Governance Feature Reference

Data Protection and Recovery

CREATE TABLE dev.orders_clone
  CLONE prod.orders;

CREATE TABLE recovery.orders_before_change
  CLONE prod.orders
  AT (TIMESTAMP => '2026-06-18 10:00:00'::TIMESTAMP);

Data Sharing and Collaboration

Exam traps:

• Sharing exposes objects through grants; it does not copy table data into the consumer account.
• Secure views are common when the provider must restrict shared data.
• Consumer performance depends on consumer-side compute for querying shared data.
• Governance policies must be tested from the consumer’s effective role/context.

Performance and Troubleshooting

Symptom-to-Action Table

Query Profile Review Checklist

Warehouse Sizing Decisions

Operational Monitoring Reference

Useful monitoring query patterns:

SELECT
  query_id,
  warehouse_name,
  execution_status,
  start_time,
  bytes_scanned
FROM snowflake.account_usage.query_history
WHERE warehouse_name = 'ETL_WH'
ORDER BY start_time DESC;

SELECT *
FROM TABLE(information_schema.task_history(
  TASK_NAME => 'MERGE_ORDERS_TASK'
))
ORDER BY scheduled_time DESC;

Data Engineering Design Patterns

Bronze / Silver / Gold Mapping

Idempotent Pipeline Checklist

• Use deterministic file naming and load tracking.
• Capture METADATA$FILENAME and load timestamp.
• Separate raw ingestion from business transformation.
• Use MERGE for upserts where duplicate events are possible.
• Process streams in transactions when multiple target updates must stay consistent.
• Design replay procedures before production failure.
• Quarantine bad records instead of silently discarding them.
• Monitor task, pipe, and copy history.
• Keep transformations set-based wherever possible.

Data Quality Controls

High-Yield Exam Traps

Final DEA-C02 Review Checklist

Before taking DEA-C02, make sure you can quickly answer:

• Which ingestion option fits batch files, continuous files, and low-latency streaming rows?
• How do COPY INTO, Snowpipe, stages, file formats, and load history interact?
• When should you choose streams/tasks versus dynamic tables?
• How do stream metadata columns affect MERGE logic?
• What table type fits production, staging, session-only, and externally stored data?
• How do micro-partitions, pruning, clustering, and search optimization differ?
• When should you scale a warehouse up versus scale out?
• How do masking policies, row access policies, secure views, and tags work together?
• How do Time Travel, cloning, UNDROP, replication, and Fail-safe differ?
• What monitoring view or history area would you check for a failed load, task, pipe, or slow query?

Next step: turn each table above into scenario drills, then practice timed Snowflake data engineering questions that force you to choose the best ingestion, transformation, security, and performance pattern for the stated constraints.