DEA-C02 — Snowflake SnowPro Advanced: Data Engineer Cheat Sheet

• 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.

The real exam rewards practical Snowflake judgment: choosing the right ingestion pattern, designing reliable pipelines, optimizing cost and performance, handling semi-structured data, and applying security/governance controls without overengineering.

Storage, compute, and services

Key implications:

• A suspended warehouse does not delete data.
• Scaling compute does not physically repartition existing data.
• Multiple warehouses can query the same data independently.
• Credits are driven by compute/service usage, not simply data volume.
• Query performance is often about pruning, SQL shape, and spill avoidance, not only warehouse size.

Micro-partitions and pruning

Snowflake stores table data in immutable micro-partitions with metadata such as value ranges and statistics. Query pruning skips micro-partitions that cannot match the filter.

High-yield points:

• Good pruning depends on data organization and filter predicates.
• Load order can create natural clustering.
• Repeated filtering by date, tenant, region, or business key can make clustering valuable on very large tables.
• Small tables rarely need clustering.
• Highly volatile tables can make clustering maintenance expensive.
• Expressions used in predicates may reduce pruning if they obscure the stored column values.

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);

Stages

Strong data engineering designs usually use named stages, clear file formats, and storage integrations rather than embedded credentials.

COPY INTO for bulk load

Use COPY INTO <table> when files already exist in a stage and you need controlled batch loading.

Review these options/concepts:

Common traps:

• Bad CSV options often appear as column shifts, unexpected nulls, or row parse errors.
• COPY INTO tracks loaded files; duplicate file names and forced reloads are exam-relevant.
• COPY INTO <location> is for unloading data to a stage, not loading into a table.
• Semi-structured formats may load into VARIANT or be mapped into relational columns depending on design.

Snowpipe

Use Snowpipe for continuous file ingestion when new files land in cloud storage or a stage.

Candidate trap: Snowpipe is not the same as Snowpipe Streaming. Snowpipe loads files; Snowpipe Streaming ingests rows through a streaming API pattern.

Snowpipe Streaming

Use Snowpipe Streaming when applications need to send rows directly with lower latency and without first writing files to cloud storage.

Good fit:

• Event or application data.
• Lower-latency ingestion.
• Avoiding file staging as the primary transport.

Review risk:

• You still need downstream modeling, deduplication, monitoring, and error handling.
• It does not automatically replace transformation pipelines.

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;

Dynamic tables

Dynamic tables define target tables as SQL queries and let Snowflake refresh them to meet a target lag.

When dynamic tables are a strong answer

When dynamic tables are weaker

Candidate trap: Dynamic tables simplify transformation refresh; they do not replace every orchestration, ingestion, or procedural requirement.

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

Snowflake commonly stores semi-structured data in VARIANT, OBJECT, and ARRAY.

Core functions and access patterns

Important distinction:

• PARSE_JSON('{"a":1}') creates a semi-structured object.
• Treating JSON text as a plain string does not make it queryable as JSON.

Semi-structured traps

• Forgetting LATERAL when flattening a column from the left table.
• Multiplying rows unexpectedly when flattening multiple arrays.
• Assuming all records have the same JSON shape.
• Casting too early and failing on malformed values; use tolerant functions where needed.
• Ignoring case sensitivity and path syntax.
• Using SELECT * after flattening and creating confusing duplicate columns.

Practical pattern

1. Land raw data with metadata columns such as source file, load timestamp, and batch ID.
2. Store the original payload when traceability matters.
3. Parse and validate into curated relational columns.
4. Use FLATTEN for repeated nested structures.
5. Deduplicate and merge into target tables with stable business keys.

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.

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);

Query Profile Review Checklist

Warehouse Sizing Decisions

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

Final review checklist before practice

Before starting original practice questions, make sure you can explain:

• Difference between COPY INTO, Snowpipe, and Snowpipe Streaming.
• How streams advance offsets and why stream staleness matters.
• When to choose dynamic tables instead of streams and tasks.
• How task graphs are scheduled, triggered, monitored, and debugged.
• How micro-partition pruning, clustering, and search optimization differ.
• Why scaling up and scaling out solve different warehouse problems.
• How to load, query, and flatten semi-structured data.
• How to design idempotent incremental MERGE pipelines.
• How table types, cloning, and retention affect recovery and cost.
• How RBAC, managed access, masking, row access, and secure sharing fit together.
• How to troubleshoot slow queries, failed loads, suspended tasks, and access errors.

Candidate trap: multi-cluster is not a magic accelerator for a single query. It primarily helps concurrency.

Query Profile review checklist

When a performance question describes a slow query, think in this order:

1. Is the query scanning too much data?

   • Review partitions scanned.
   • Check filter selectivity.
   • Consider clustering, materialized view, or search optimization.

2. Is the query spilling?

   • Local or remote spill suggests memory pressure.
   • Consider a larger warehouse or query rewrite.

3. Is there join explosion or skew?

   • Check join predicates.
   • Avoid accidental cross joins.
   • Pre-aggregate where appropriate.

4. Is concurrency causing queues?

   • Use multi-cluster warehouse or workload isolation.

5. Is cache behavior misleading?

   • A cached result may hide real runtime.
   • Changing data, non-deterministic functions, or different query forms can prevent reuse.

Performance features at a glance

Candidate trap: querying a stream with SELECT is not the same as consuming it in a DML transaction.

Common stream use cases:

• Incremental MERGE into a dimension or fact table.
• Capturing new rows from a landing table.
• Processing CDC events.
• Triggering tasks only when data exists.

Tasks

Tasks run SQL, stored procedures, or pipeline steps on a schedule or dependency graph.

Strong task design:

• Keep each task purpose clear.
• Use idempotent MERGE logic.
• Guard stream-processing tasks with data-exists checks.
• Monitor failures and skipped runs.
• Avoid one huge task that hides which step failed.

Streams + tasks pipeline pattern

    flowchart LR
	    A[Files or app data] --> B[Raw landing table]
	    B --> C[Stream tracks changes]
	    C --> D[Task checks stream]
	    D --> E[MERGE into curated table]
	    E --> F[Downstream marts or features]

Use this pattern when you need procedural control over incremental processing, error handling, or custom merge logic.

Exam decisions often ask whether to load raw first or transform immediately. Raw landing is usually safer when auditability, replay, schema drift, and troubleshooting matter.

MERGE and idempotency

Use MERGE when applying incremental changes to a target table.

Good MERGE design:

• Match on stable business keys or surrogate keys.
• Deduplicate source changes before merge.
• Handle deletes if the source CDC includes them.
• Store audit columns such as effective timestamp or load batch.
• Make reruns safe.

Common mistake: merging a stream with duplicate keys without first qualifying the latest change. Use windowing logic such as ROW_NUMBER and QUALIFY when appropriate.

Table types and retention

High-yield traps:

• A zero-copy clone is not a deep physical copy at creation.
• Changes after cloning create independent data changes.
• Temporary tables can shadow permanent tables with the same name in a session.
• Transient objects are a poor choice for data that cannot be recreated.

Common exam traps:

• Granting table privileges is not enough if the role lacks database/schema usage.
• Stage access may require stage privileges plus storage integration/cloud permissions.
• Ownership and usage are different.
• Future grants do not fix existing object privileges.
• Managed access schemas change who can manage grants.

Data protection controls

Candidate trap: A normal view can simplify access but is not always the right control for sensitive logic or secure data sharing.

Data sharing

Snowflake data sharing can provide access without copying data.

Review:

• Providers share selected database objects.
• Consumers access shared data through a database created from the share.
• Secure views can expose only approved rows/columns.
• Reader accounts may be used when consumers do not have their own Snowflake account.
• Shares require careful privilege and governance design.

Do not claim affiliation with Snowflake or assume data sharing removes the need for access review.