Snowflake Certification Practice Test 02
Practice Test 02
Question 1 of 20
📋 Multiple ChoiceQuestion 21
A retail database administrator accidentally executes an unqualified DELETE statement:
`DELETE FROM customers WHERE status = 'ACTIVE';`
This erroneously purged 800,000 active customer records from an enterprise production table.
Which Snowflake capability enables the team to recover these deleted records immediately without restoring from backup media?
A. Fail-safe Recovery Request
B. Zero-Copy Database Clone
✓
C. Time Travel
D. Search Optimization Service
Answer
✅ C. Time Travel
Explanation
1. Concept Overview Snowflake **Time Travel** enables querying, restoring, and cloning data from historical points in time across a configurable retention window (0 to 90 days for Enterprise edition). It operates at the micro-partition metadata level, allowing instant recovery from accidental DML operations (DELETE, UPDATE, DROP) using the `AT` or `BEFORE` clauses.
2. Why the Correct Option is Right **Time Travel** allows the administrator to query the table as it existed immediately prior to the erroneous `DELETE` statement using the query ID (`BEFORE (statement => '<query_id>')`) or timestamp. The deleted records can be restored instantly using `INSERT INTO ... SELECT ... FROM ... BEFORE(...)` or by using `UNDROP TABLE`.
3. Why Other Options Are Incorrect - **Fail-safe Recovery Request:** Fail-safe is a non-configurable 7-day disaster recovery period accessible *only* by Snowflake Support after Time Travel expires. It cannot be queried directly by users and takes hours or days. - **Zero-Copy Database Clone:** While cloning is fast, cloning the *current* state of the table after the delete occurred will simply replicate the already-truncated table unless combined with Time Travel syntax. - **Search Optimization Service:** SOS accelerates point-lookup queries on large tables; it has no recovery or point-in-time historical rollback capabilities.
4. Production Scenario / Code Example
-- Step 1: Identify the Query ID of the accidental DELETE in Query History
-- Query ID: 01b45678-0001-2345-0000-12340001abcd
-- Step 2: Restore missing records directly from the point BEFORE the query executed
INSERT INTO production.customers
SELECT *
FROM production.customers
BEFORE (statement => '01b45678-0001-2345-0000-12340001abcd')
WHERE status = 'ACTIVE';
-- Alternatively, if the entire table was dropped:
-- UNDROP TABLE production.customers;5. Real-World Architecture Context Because Snowflake micro-partitions are immutable, updating or deleting records does not overwrite physical blocks; it simply marks old micro-partitions as inactive and writes new ones. Time Travel works by reading the micro-partition pointers active at that historical timestamp.
6. Common Pitfalls & Anti-Patterns - **Relying on Time Travel for Transient/Temporary tables:** Transient tables have a maximum Time Travel retention of only 1 day and zero Fail-safe. - **Exceeding the retention period:** Once data ages past `DATA_RETENTION_TIME_IN_DAYS`, it moves to Fail-safe and cannot be restored by users.
7. Performance & Cost Considerations Retaining historical micro-partitions incurs standard cloud storage fees. For high-churn tables with millions of daily updates, setting retention to 90 days can substantially increase storage costs.
8. Exam / Interview Tip Remember: Standard edition supports 0 or 1 day of Time Travel. Enterprise edition supports 0 to 90 days on permanent tables. Transient and temporary tables are strictly limited to 0 or 1 day.
9. Key Takeaways - Time Travel recovers data from accidental DML operations using `AT` or `BEFORE`. - Operates at zero compute restore latency using immutable micro-partition metadata. - Configured via `DATA_RETENTION_TIME_IN_DAYS` (up to 90 days on Enterprise).
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Zero-Copy Cloning in Snowflake](/tutorials/snowflake/snowflake-zero-copy-cloning)`
Answered: 0 / 20
Question Navigator (Test 02)0% Answered
Snowflake Certification Practice Test 02
Practice Test 02
Insightful Saga — Modern Data Engineering Certification Preparation
Question 21
## Question 21 A retail database administrator accidentally executes an unqualified DELETE statement: `DELETE FROM customers WHERE status = 'ACTIVE';` This erroneously purged 800,000 active customer records from an enterprise production table. Which Snowflake capability enables the team to recover these deleted records immediately without restoring from backup media?
A. Fail-safe Recovery Request
B. Zero-Copy Database Clone
C. Time Travel
D. Search Optimization Service
Answer: ✅ C. Time Travel
Explanation:
1. Concept Overview Snowflake **Time Travel** enables querying, restoring, and cloning data from historical points in time across a configurable retention window (0 to 90 days for Enterprise edition). It operates at the micro-partition metadata level, allowing instant recovery from accidental DML operations (DELETE, UPDATE, DROP) using the `AT` or `BEFORE` clauses.
2. Why the Correct Option is Right **Time Travel** allows the administrator to query the table as it existed immediately prior to the erroneous `DELETE` statement using the query ID (`BEFORE (statement => '<query_id>')`) or timestamp. The deleted records can be restored instantly using `INSERT INTO ... SELECT ... FROM ... BEFORE(...)` or by using `UNDROP TABLE`.
3. Why Other Options Are Incorrect - **Fail-safe Recovery Request:** Fail-safe is a non-configurable 7-day disaster recovery period accessible *only* by Snowflake Support after Time Travel expires. It cannot be queried directly by users and takes hours or days. - **Zero-Copy Database Clone:** While cloning is fast, cloning the *current* state of the table after the delete occurred will simply replicate the already-truncated table unless combined with Time Travel syntax. - **Search Optimization Service:** SOS accelerates point-lookup queries on large tables; it has no recovery or point-in-time historical rollback capabilities.
4. Production Scenario / Code Example
-- Step 1: Identify the Query ID of the accidental DELETE in Query History
-- Query ID: 01b45678-0001-2345-0000-12340001abcd
-- Step 2: Restore missing records directly from the point BEFORE the query executed
INSERT INTO production.customers
SELECT *
FROM production.customers
BEFORE (statement => '01b45678-0001-2345-0000-12340001abcd')
WHERE status = 'ACTIVE';
-- Alternatively, if the entire table was dropped:
-- UNDROP TABLE production.customers;5. Real-World Architecture Context Because Snowflake micro-partitions are immutable, updating or deleting records does not overwrite physical blocks; it simply marks old micro-partitions as inactive and writes new ones. Time Travel works by reading the micro-partition pointers active at that historical timestamp.
6. Common Pitfalls & Anti-Patterns - **Relying on Time Travel for Transient/Temporary tables:** Transient tables have a maximum Time Travel retention of only 1 day and zero Fail-safe. - **Exceeding the retention period:** Once data ages past `DATA_RETENTION_TIME_IN_DAYS`, it moves to Fail-safe and cannot be restored by users.
7. Performance & Cost Considerations Retaining historical micro-partitions incurs standard cloud storage fees. For high-churn tables with millions of daily updates, setting retention to 90 days can substantially increase storage costs.
8. Exam / Interview Tip Remember: Standard edition supports 0 or 1 day of Time Travel. Enterprise edition supports 0 to 90 days on permanent tables. Transient and temporary tables are strictly limited to 0 or 1 day.
9. Key Takeaways - Time Travel recovers data from accidental DML operations using `AT` or `BEFORE`. - Operates at zero compute restore latency using immutable micro-partition metadata. - Configured via `DATA_RETENTION_TIME_IN_DAYS` (up to 90 days on Enterprise).
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Zero-Copy Cloning in Snowflake](/tutorials/snowflake/snowflake-zero-copy-cloning)`
Question 22
## Question 22 A Data Engineering team needs to create a complete copy of a 50 TB production analytics database so that quality assurance engineers can test end-to-end transformation pipelines against real production data. Which Snowflake command achieves this instantly without duplicating physical storage blocks or incurring additional storage costs at creation time?
A. COPY INTO qa_db FROM prod_db;
B. CREATE DATABASE qa_db CLONE prod_db;
C. BACKUP DATABASE prod_db TO STAGE qa_stage;
D. EXPORT DATABASE prod_db AS PARQUET;
Answer: ✅ B. CREATE DATABASE qa_db CLONE prod_db;
Explanation:
1. Concept Overview Snowflake **Zero-Copy Cloning** creates an exact, independent copy of a database, schema, or table without duplicating the underlying physical micro-partitions. The clone operation is metadata-only: Snowflake points the new object's metadata catalog to the existing immutable micro-partitions of the source.
2. Why the Correct Option is Right `CREATE DATABASE qa_db CLONE prod_db;` executes in seconds regardless of database size (even hundreds of terabytes). Because no physical data files are copied, zero additional storage fees are incurred at creation time. QA engineers can perform full read/write operations on `qa_db` independently without impacting `prod_db`.
3. Why Other Options Are Incorrect - `COPY INTO qa_db FROM prod_db;`: Invalid SQL syntax; `COPY INTO` is used for loading data from stages into tables or unloading from tables to stages. - `BACKUP DATABASE prod_db TO STAGE qa_stage;`: Snowflake has no `BACKUP DATABASE` command; backups are handled natively via continuous replication, Time Travel, and Fail-safe. - `EXPORT DATABASE prod_db AS PARQUET;`: Incurs massive compute and external egress storage costs and takes hours to unload 50 TB of data.
4. Production Scenario / Code Example
-- Instant zero-copy clone of entire production database for QA testing
CREATE DATABASE qa_analytics_db CLONE prod_analytics_db;
-- QA engineers can modify tables without altering production data
UPDATE qa_analytics_db.marts.fct_orders
SET order_status = 'CANCELLED'
WHERE order_date >= '2024-01-01';
-- Only the modified micro-partitions in qa_analytics_db consume additional storage!5. Real-World Architecture Context Zero-copy cloning revolutionized data engineering testing. Instead of maintaining stale synthetic test environments or paying double for separate physical development clusters, teams can spin up ephemeral, full-scale production clones for every CI/CD pull request and drop them when testing completes.
6. Common Pitfalls & Anti-Patterns - **Assuming clones are read-only:** Cloned tables are fully read/write. As either the source or clone modifies rows, new micro-partitions are created, and storage costs begin to diverge (Copy-on-Write). - **Cloning external tables or stages:** External stages and external tables can be cloned, but external data files remain in cloud object storage.
7. Performance & Cost Considerations At creation time, cloning consumes zero storage cost. Storage charges only accrue when rows are updated or deleted in the clone, causing Snowflake to preserve the original micro-partitions while allocating new ones.
8. Exam / Interview Tip Cloning is available for Databases, Schemas, Tables, Streams, and Tasks. You *cannot* clone users, roles, warehouses, or resource monitors.
9. Key Takeaways - Zero-copy cloning duplicates metadata pointers, not physical micro-partitions. - Instantaneous creation regardless of table or database size. - Cloned objects are fully independent and support both reads and writes.
10. Related Tutorials & Resources - [Zero-Copy Cloning Explained](/tutorials/snowflake/snowflake-zero-copy-cloning) - [Snowflake Architecture — Storage, Compute & Cloud Services](/tutorials/snowflake/snowflake-architecture)`
Question 23
## Question 23 A company wants developers to test complex schema migrations, column drops, and index reorganizations safely without risking production downtime or contaminating real customer records. What is the Snowflake-recommended architectural pattern for staging these migrations?
A. Clone the Production Database to a Development Database using Zero-Copy Cloning
B. Run schema migrations during scheduled Sunday 2:00 AM production maintenance windows
C. Export production data to CSV files and reload into an on-premises SQLite database
D. Grant developers direct SYSADMIN access to the production database
Answer: ✅ A. Clone the Production Database to a Development Database using Zero-Copy Cloning
Explanation:
1. Concept Overview Database testing in traditional RDBMS environments required days of restore time and expensive parallel hardware. In Snowflake, **Zero-Copy Cloning** enables instantaneous creation of isolated staging environments that perfectly mirror production schema, data, and constraints.
2. Why the Correct Option is Right **Cloning the Production Database using Zero-Copy Cloning** gives developers a 100% accurate replica of production data and schema within seconds. Developers can test destructive DDL changes (`ALTER TABLE DROP COLUMN`, schema refactoring) in total isolation. If a migration script fails, the dev database can be dropped and recloned instantly.
3. Why Other Options Are Incorrect - **Running schema migrations in production maintenance windows:** Highly risky; schema errors or broken constraints in production cause downtime and emergency rollbacks. - **Exporting to CSV and reloading into SQLite:** CSV exports lose data types, micro-partition metadata, and Snowflake-specific SQL behaviors (VARIANT, FLATTEN). - **Granting SYSADMIN access to production:** Violates the principle of least privilege, compliance standards (SOC2, HIPAA), and invites catastrophic human error.
4. Production Scenario / Code Example
-- Step 1: Create an isolated sandbox database from production
CREATE OR REPLACE DATABASE dev_sandbox CLONE prod_enterprise;
-- Step 2: Test migration script in the clone
USE DATABASE dev_sandbox;
ALTER TABLE sales.orders DROP COLUMN obsolete_code;
ALTER TABLE sales.orders ADD COLUMN tax_rate_code VARCHAR(10);
-- Step 3: Verify downstream views and queries
SELECT * FROM sales.vw_daily_revenue LIMIT 100;
-- Step 4: Clean up when testing is complete
DROP DATABASE dev_sandbox;5. Real-World Architecture Context CI/CD data engineering pipelines integrate zero-copy cloning into automated test suites. A GitHub Actions workflow clones `prod_db` to `ci_test_db_<pr_number>`, runs dbt tests against real data volume, and drops the clone upon test completion.
6. Common Pitfalls & Anti-Patterns - **Leaving test clones running indefinitely:** While initial clone storage is zero, if upstream production processes frequently update records, the historical micro-partitions referenced by the stagnant clone cannot be purged, driving up storage costs. - **Cloning across different Snowflake accounts:** Zero-copy cloning is restricted to within the same Snowflake account. For cross-account sharing, use Secure Data Sharing.
7. Performance & Cost Considerations Cloning takes 5 to 15 seconds regardless of data scale. Dropping stale dev clones after feature validation ensures unreferenced historical partitions are reclaimed by Snowflake's automated storage manager.
8. Exam / Interview Tip Whenever a scenario mentions *"safe schema migration testing"*, *"isolated developer sandbox"*, or *"instant staging without hardware cost"*, the answer is **Zero-Copy Cloning**.
9. Key Takeaways - Zero-copy cloning enables risk-free staging against real production data. - Clones are completely isolated from source environments. - Easily automated via CI/CD pipelines for pull-request testing.
10. Related Tutorials & Resources - [Zero-Copy Cloning Explained](/tutorials/snowflake/snowflake-zero-copy-cloning) - [Databases, Schemas & Object Hierarchy](/tutorials/snowflake/snowflake-object-hierarchy)`
Question 24
## Question 24 A Snowflake enterprise table has its `DATA_RETENTION_TIME_IN_DAYS` parameter set to 7 days. On Day 8, a table is accidentally dropped, and no Time Travel queries can locate the dropped table. What is the status of the historical data micro-partitions immediately after the Time Travel retention period expires?
A. The data is permanently erased and unrecoverable by any means
B. The data is automatically compressed and exported to an internal stage
C. The data is archived to an external S3 Glacier bucket
D. The data enters Snowflake Fail-safe for a mandatory 7-day disaster recovery period
Answer: ✅ D. The data enters Snowflake Fail-safe for a mandatory 7-day disaster recovery period
Explanation:
1. Concept Overview Snowflake data protection follows a defined lifecycle: 1. **Active Data:** Currently referenced micro-partitions. 2. **Time Travel:** Historical micro-partitions accessible via SQL (`AT` / `BEFORE`) for 0 to 90 days. 3. **Fail-safe:** A non-configurable 7-day period immediately following Time Travel where data can *only* be recovered by Snowflake Support. 4. **Purged:** Completely released and unrecoverable.
2. Why the Correct Option is Right When the 7-day Time Travel retention window expires, **the data enters Snowflake Fail-safe for a 7-day disaster recovery period**. During Fail-safe, standard users and administrators cannot query the data directly; only Snowflake Support can retrieve the partitions in the event of extreme disaster.
3. Why Other Options Are Incorrect - **Permanently erased immediately:** Data is not permanently erased until both Time Travel and the 7-day Fail-safe period expire. - **Exported to an internal stage:** Snowflake never automatically unloads expiring data to stages; data lifecycle is governed strictly by retention parameters. - **Archived to external S3 Glacier:** Snowflake does not automatically offload customer tables to external cloud vendor archival tiers.
4. Production Scenario / Code Example
-- Inspecting Time Travel retention and Fail-safe storage consumption
SELECT
table_name,
active_bytes / POWER(1024, 3) AS active_gb,
time_travel_bytes / POWER(1024, 3) AS time_travel_gb,
failsafe_bytes / POWER(1024, 3) AS failsafe_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE table_name = 'ENTERPRISE_ORDERS';5. Real-World Architecture Context Fail-safe serves as Snowflake's catastrophic recovery mechanism (e.g., severe cloud provider hardware failures or security events). Permanent tables always have 7 days of Fail-safe. Transient and temporary tables have **zero days of Fail-safe**, making them ideal for ETL staging.
6. Common Pitfalls & Anti-Patterns - **Opening a Support ticket for regular user errors during Time Travel:** If data is still within Time Travel, use `UNDROP` or `BEFORE` SQL statements. Support should only be contacted if data has entered Fail-safe. - **Using Permanent tables for high-turnover staging:** High-churn ETL staging tables on permanent tables accumulate massive Fail-safe storage fees. Always use `TRANSIENT` tables for ETL staging.
7. Performance & Cost Considerations Storage used by Fail-safe is billed at standard cloud storage rates. For a 10 TB permanent table replaced daily, 7 days of Fail-safe will consume 70 TB of billed storage! Using Transient tables eliminates Fail-safe storage costs entirely.
8. Exam / Interview Tip - Permanent tables: 0–90 days Time Travel + 7 days Fail-safe. - Transient tables: 0–1 day Time Travel + 0 days Fail-safe. - Temporary tables: 0–1 day Time Travel + 0 days Fail-safe.
9. Key Takeaways - After Time Travel expires, data moves into a 7-day Fail-safe period. - Fail-safe is recoverable *only* by Snowflake Support for catastrophic events. - Transient tables incur zero Fail-safe storage costs.
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Understanding Snowflake Storage & Compute Costs](/tutorials/snowflake/snowflake-costs-best-practices)`
Question 25
## Question 25 What is the PRIMARY purpose of Snowflake's 7-day Fail-safe feature?
A. Point-in-time SQL self-service querying by database administrators
B. Disaster recovery accessible solely by Snowflake Support in catastrophic failure scenarios
C. Automated compaction of small micro-partitions
D. Dynamic data masking of personally identifiable information
Answer: ✅ B. Disaster recovery accessible solely by Snowflake Support in catastrophic failure scenarios
Explanation:
1. Concept Overview **Fail-safe** is a core data protection mechanism in Snowflake providing a 7-day safety net after Time Travel expires. It is designed specifically as a catastrophic disaster recovery mechanism for extraordinary circumstances.
2. Why the Correct Option is Right **Disaster recovery accessible solely by Snowflake Support** is the precise definition of Fail-safe. Customers cannot query, view, or restore Fail-safe data using SQL commands. If a critical data loss incident occurs and Time Travel has passed, customers must submit a formal request to Snowflake Support to perform recovery.
3. Why Other Options Are Incorrect - **Point-in-time self-service querying:** That is the purpose of **Time Travel** using `AT` or `BEFORE` clauses, not Fail-safe. - **Automated compaction of micro-partitions:** Handled automatically by Snowflake's background clustering and micro-partition management services. - **Dynamic data masking:** A security feature that masks sensitive column data dynamically based on user roles; unrelated to storage recovery.
4. Production Scenario / Code Example
-- Checking storage allocated across Active, Time Travel, and Fail-safe
SHOW TABLES LIKE 'CUSTOMER_TRANSACTIONS';
-- The 'retention_time' column reflects Time Travel days (e.g., 30)
-- Fail-safe is fixed at 7 days for all permanent tables and cannot be changed by users.5. Real-World Architecture Context In enterprise compliance discussions (e.g., SOC 1/2, ISO 27001), Fail-safe satisfies the requirement for durable data protection against unforeseen catastrophic events without requiring external third-party backup tape infrastructure.
6. Common Pitfalls & Anti-Patterns - **Treating Fail-safe as an operational backup tool:** Recovery via Snowflake Support can take hours or days and is intended only as an absolute last resort. Operational rollbacks should always be designed to complete within the configured Time Travel window. - **Not realizing Fail-safe applies only to permanent tables:** Creating staging tables as permanent tables leads to unnecessary Fail-safe storage bills.
7. Performance & Cost Considerations Fail-safe incurs standard storage charges per TB-month. Organizations optimizing cloud spend should convert scratch, landing, and intermediate ETL tables to `TRANSIENT` to avoid paying for Fail-safe storage.
8. Exam / Interview Tip Exam questions consistently verify two facts: 1. Who can recover data from Fail-safe? -> **Only Snowflake Support**. 2. How long is Fail-safe? -> **7 days (fixed and non-configurable)**.
9. Key Takeaways - Fail-safe provides 7 days of disaster recovery after Time Travel expires. - Accessible exclusively through Snowflake Support. - Applies only to permanent tables; transient and temporary tables have 0 days of Fail-safe.
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Snowflake Costs & Best Practices](/tutorials/snowflake/snowflake-costs-best-practices)`
Question 26
## Question 26 A data engineering team wants to process only new and modified records from an orders table during each hourly pipeline run, rather than reprocessing the entire 200-million-row table. Which Snowflake native object tracks Change Data Capture (CDC) metadata across inserts, updates, and deletes on a source table?
A. Sequence
B. Pipe
C. Stream
D. Task
Answer: ✅ C. Stream
Explanation:
1. Concept Overview A Snowflake **Stream** is a lightweight Change Data Capture (CDC) object created on top of a table, directory table, or view. It tracks all DML changes (INSERT, UPDATE, DELETE) made to the source object by recording micro-partition change offsets.
2. Why the Correct Option is Right A **Stream** captures the delta of changes since the stream was last consumed. When queried, a stream presents the exact row-level changes along with three metadata columns (`METADATA$ACTION`, `METADATA$ISUPDATE`, `METADATA$ROW_ID`), enabling efficient incremental processing.
3. Why Other Options Are Incorrect - **Sequence:** Generates sequential unique integer numbers (e.g., auto-incrementing primary keys); it does not track change history or CDC events. - **Pipe:** Wraps a `COPY INTO` statement for continuous, automated loading of files from stages; it does not track changes occurring inside Snowflake tables. - **Task:** A scheduler that executes SQL statements or stored procedures on a schedule; it does not capture CDC deltas itself (though it is often paired with a Stream).
4. Production Scenario / Code Example
-- Step 1: Create a stream on the source orders table
CREATE OR REPLACE STREAM orders_cdc_stream ON TABLE raw_orders;
-- Step 2: Ingest new records into raw_orders
INSERT INTO raw_orders (order_id, amount, status) VALUES (101, 250.00, 'PAID');
-- Step 3: Query the stream to inspect CDC metadata columns
SELECT
order_id,
amount,
status,
METADATA$ACTION, -- 'INSERT' or 'DELETE'
METADATA$ISUPDATE, -- TRUE if row change was part of an UPDATE
METADATA$ROW_ID -- Unique identifier for the row
FROM orders_cdc_stream;5. Real-World Architecture Context Streams are the foundation of Snowflake's continuous ELT pipelines. Instead of expensive full-table scans or relying on unreliable timestamp columns (which miss deleted rows), Streams provide guaranteed, transactionally consistent CDC tracking.
6. Common Pitfalls & Anti-Patterns - **Letting a Stream go stale:** If a stream is not consumed within the source table's Time Travel retention period, the stream becomes **stale** and unreadable. The table's retention period must accommodate the stream's consumption frequency. - **Consuming a stream with a SELECT statement:** A standard `SELECT` from a stream does *not* advance the stream offset. The offset only advances when the stream is consumed inside a DML transaction (`INSERT`, `MERGE`, `CREATE TABLE AS SELECT`).
7. Performance & Cost Considerations Streams have zero storage footprint! They do not duplicate table data; they merely record an offset pointer against the source table's existing historical micro-partitions.
8. Exam / Interview Tip Remember: A stream only advances its offset when consumed within a successful DML transaction (like `INSERT INTO ... SELECT FROM stream` or `MERGE INTO ... USING stream`). A standalone `SELECT` does NOT advance the offset.
9. Key Takeaways - Streams track table changes (CDC) using micro-partition offsets. - Provides `METADATA$ACTION`, `METADATA$ISUPDATE`, and `METADATA$ROW_ID`. - Consumed inside DML operations to advance the offset.
10. Related Tutorials & Resources - [Streams — Change Data Capture at Table Level](/tutorials/snowflake/snowflake-streams) - [Incremental Loading (CDC) in Snowflake](/tutorials/snowflake/snowflake-incremental-loading-cdc)`
Question 27
## Question 27 When querying a Snowflake Stream, which metadata columns are automatically surfaced to describe the nature of each recorded table change?
A. METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
B. CDC$TYPE, CDC$TIMESTAMP, CDC$USER
C. SYS$OPERATION, SYS$VERSION, SYS$HASH
D. LOG$RECORD_TYPE, LOG$COMMITTED_AT, LOG$OFFSET
Answer: ✅ A. METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
Explanation:
1. Concept Overview Snowflake Streams append three virtual columns to the source table's schema when queried. These metadata columns allow downstream transformation logic to differentiate between row insertions, row deletions, and updates.
2. Why the Correct Option is Right The three native stream metadata columns are: 1. **METADATA$ACTION:** Indicates the DML operation: `INSERT` or `DELETE`. 2. **METADATA$ISUPDATE:** A boolean indicating whether the action was part of an `UPDATE` statement (`TRUE`) or a distinct insert/delete (`FALSE`). 3. **METADATA$ROW_ID:** An immutable, unique row identifier string used to track a specific record across multiple operations.
3. Why Other Options Are Incorrect - `CDC$TYPE, CDC$TIMESTAMP, CDC$USER`: Fictitious column names not recognized by Snowflake. - `SYS$OPERATION, SYS$VERSION, SYS$HASH`: Non-existent internal placeholders. - `LOG$RECORD_TYPE, LOG$COMMITTED_AT, LOG$OFFSET`: Fabricated naming conventions reminiscent of Apache Kafka or WAL logs.
4. Production Scenario / Code Example
-- Consuming stream CDC data using a MERGE statement
MERGE INTO target_dim_customers AS target
USING (
SELECT
customer_id,
customer_name,
email,
METADATA$ACTION,
METADATA$ISUPDATE
FROM customer_stream
) AS src
ON target.customer_id = src.customer_id
WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' AND src.METADATA$ISUPDATE = FALSE THEN
DELETE
WHEN MATCHED AND src.METADATA$ACTION = 'INSERT' AND src.METADATA$ISUPDATE = TRUE THEN
UPDATE SET target.customer_name = src.customer_name, target.email = src.email
WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
INSERT (customer_id, customer_name, email)
VALUES (src.customer_id, src.customer_name, src.email);5. Real-World Architecture Context In an `UPDATE` statement, Snowflake treats the change as a pair of operations: a `DELETE` of the old row image followed by an `INSERT` of the new row image. In both records, `METADATA$ISUPDATE` is set to `TRUE`, enabling elegant SCD Type 1 and Type 2 implementations.
6. Common Pitfalls & Anti-Patterns - **Filtering only for INSERT and forgetting updates:** If you filter `WHERE METADATA$ACTION = 'INSERT'`, you may capture updated rows without purging their obsolete pre-update versions unless you check `METADATA$ISUPDATE`. - **Querying metadata columns on a base table:** These columns exist only on Stream objects, not on standard base tables.
7. Performance & Cost Considerations Using `METADATA$ACTION` and `METADATA$ISUPDATE` in `MERGE` statements allows the Snowflake query planner to execute targeted partition joins, minimizing warehouse compute time.
8. Exam / Interview Tip Know how an UPDATE appears in a stream: - Two rows: - Row 1: ACTION='DELETE', ISUPDATE=TRUE (the old record). - Row 2: ACTION='INSERT', ISUPDATE=TRUE (the new record).
9. Key Takeaways - Stream metadata columns: `METADATA$ACTION`, `METADATA$ISUPDATE`, `METADATA$ROW_ID`. - Enable full CDC replication and merge logic. - `ISUPDATE` flags whether an insert/delete originated from an `UPDATE` statement.
10. Related Tutorials & Resources - [Streams — Change Data Capture at Table Level](/tutorials/snowflake/snowflake-streams) - [Incremental Loading (CDC) in Snowflake](/tutorials/snowflake/snowflake-incremental-loading-cdc)`
Question 28
## Question 28 A financial organization needs to execute a complex SQL stored procedure every night at 01:00 AM UTC to aggregate daily ledger accounts and refresh dimensional data marts. Which Snowflake native database object is designed to schedule and automate periodic SQL execution?
A. Virtual Warehouse
B. Resource Monitor
C. File Format
D. Task
Answer: ✅ D. Task
Explanation:
1. Concept Overview A Snowflake **Task** is a first-class database object used to schedule the automated execution of a single SQL statement or stored procedure. Tasks can be scheduled at fixed cron intervals or chained together to form Directed Acyclic Graphs (DAGs) of dependent tasks.
2. Why the Correct Option is Right A **Task** is built specifically for scheduled automation within Snowflake. By specifying a schedule using standard CRON syntax (`USING CRON 0 1 * * * UTC`), the task wakes up automatically, triggers a designated virtual warehouse (or uses Serverless compute), runs the SQL procedure, and suspends.
3. Why Other Options Are Incorrect - **Virtual Warehouse:** Provides the raw CPU and RAM compute resources to execute queries, but has no built-in scheduling or timer capabilities. - **Resource Monitor:** Tracks and imposes credit consumption limits on warehouses to prevent budget overruns; it does not execute SQL workflows. - **File Format:** Defines parsing parameters for files (e.g., delimiters, headers); it cannot schedule or execute transformations.
4. Production Scenario / Code Example
-- Creating a scheduled Task using CRON syntax
CREATE OR REPLACE TASK nightly_ledger_aggregation_task
WAREHOUSE = etl_wh
SCHEDULE = 'USING CRON 0 1 * * * UTC'
AS
CALL finance.procedures.sp_aggregate_daily_ledger();
-- IMPORTANT: Tasks are created in a SUSPENDED state by default!
-- You must explicitly resume the task:
ALTER TASK nightly_ledger_aggregation_task RESUME;5. Real-World Architecture Context Prior to Snowflake Tasks, organizations had to run external scheduling servers (cron boxes, Airflow clusters, Jenkins) just to trigger SQL statements in Snowflake. Tasks eliminate this external infrastructure, providing self-contained, fully managed scheduling directly inside the database.
6. Common Pitfalls & Anti-Patterns - **Forgetting to RESUME the task:** A newly created task is always in a `SUSPENDED` state. If you do not run `ALTER TASK ... RESUME`, the task will never execute! - **Scheduling multiple tasks independently instead of using DAGs:** When tasks have dependencies, chaining them using `AFTER <parent_task>` is much safer than guessing cron offsets.
7. Performance & Cost Considerations Snowflake supports **Serverless Tasks**, where Snowflake manages the compute resources dynamically without requiring a pre-allocated virtual warehouse. Serverless compute is billed per second only for the exact runtime of the SQL statement.
8. Exam / Interview Tip Two critical Task facts frequently tested: 1. Newly created tasks are ALWAYS created in a **SUSPENDED** state. 2. Root tasks use `SCHEDULE`, child tasks use `AFTER <parent_task_name>`.
9. Key Takeaways - Tasks schedule automated SQL execution natively in Snowflake. - Support CRON expressions and interval minutes. - Support Serverless compute or user-managed virtual warehouses. - Must be explicitly resumed with `ALTER TASK ... RESUME`.
10. Related Tutorials & Resources - [Tasks — How Scheduling Works Internally](/tutorials/snowflake/snowflake-tasks) - [Automation of Data Loads Using Tasks (Cron Style)](/tutorials/snowflake/snowflake-tasks-cron)`
Question 29
## Question 29 A data engineering pipeline uses a Stream to capture row updates on an incoming transactions table. The team wants a downstream Task to execute and merge changes only when the Stream actually contains new records, avoiding empty warehouse runs and unnecessary credit consumption. Which conditional clause should be added to the Task definition?
A. WHERE ROW_COUNT > 0
B. WHEN SYSTEM$STREAM_HAS_DATA('my_stream')
C. CHECK STREAM_STATUS('my_stream') = 'DIRTY'
D. IF EXISTS (SELECT 1 FROM my_stream)
Answer: ✅ B. WHEN SYSTEM$STREAM_HAS_DATA('my_stream')
Explanation:
1. Concept Overview Running scheduled tasks when no new data has arrived wastes warehouse credits. Snowflake provides the system evaluation function **SYSTEM$STREAM_HAS_DATA('<stream_name>')**, which evaluates whether a stream contains unconsumed change data without requiring a virtual warehouse to spin up.
2. Why the Correct Option is Right Adding `WHEN SYSTEM$STREAM_HAS_DATA('my_stream')` to a Task's definition instructs the Snowflake Cloud Services layer to inspect the stream's metadata before starting the warehouse. If the stream is empty, the task run is skipped entirely, consuming zero warehouse compute credits.
3. Why Other Options Are Incorrect - `WHERE ROW_COUNT > 0`: Invalid syntax for a Task declaration; `WHERE` filters query output, not task execution conditions. - `CHECK STREAM_STATUS(...) = 'DIRTY'`: Fabricated function name not supported in Snowflake. - `IF EXISTS (SELECT 1 FROM my_stream)`: While logically true in a procedural script, writing this in a task condition requires waking up a virtual warehouse to evaluate the query, defeating the goal of zero-credit pre-checks.
4. Production Scenario / Code Example
-- Creating an efficient CDC-triggered Task
CREATE OR REPLACE TASK process_orders_cdc_task
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO mart_orders AS target
USING orders_stream AS src
ON target.order_id = src.order_id
WHEN MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
UPDATE SET target.amount = src.amount, target.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
INSERT (order_id, amount, updated_at) VALUES (src.order_id, src.amount, CURRENT_TIMESTAMP());
ALTER TASK process_orders_cdc_task RESUME;5. Real-World Architecture Context In event-driven architectures, data arrives in unpredictable bursts. Checking `SYSTEM$STREAM_HAS_DATA` allows tasks to run on aggressive schedules (e.g., every 1 or 2 minutes) without incurring compute charges during idle periods.
6. Common Pitfalls & Anti-Patterns - **Passing the stream name without quotes:** The argument to `SYSTEM$STREAM_HAS_DATA` must be a string enclosed in single quotes, e.g., `'my_stream'`. - **Assuming the function returns FALSE on empty tables:** The function evaluates whether the *stream offset* has pending records, not whether the source table has rows.
7. Performance & Cost Considerations Because `SYSTEM$STREAM_HAS_DATA` is evaluated entirely within the Cloud Services layer, skipped task evaluations consume no compute warehouse credits and minimal cloud services overhead.
8. Exam / Interview Tip Remember: `SYSTEM$STREAM_HAS_DATA('stream_name')` is the standard Snowflake pattern for building cost-efficient, stream-triggered data pipelines.
9. Key Takeaways - `WHEN SYSTEM$STREAM_HAS_DATA('stream_name')` skips task execution when no changes exist. - Evaluated in Cloud Services before waking up the virtual warehouse. - Prevents idle warehouse credit consumption in near-real-time pipelines.
10. Related Tutorials & Resources - [Streams — Change Data Capture at Table Level](/tutorials/snowflake/snowflake-streams) - [Tasks — How Scheduling Works Internally](/tutorials/snowflake/snowflake-tasks)`
Question 30
## Question 30 A streaming telemetry application deposits thousands of micro-batch JSON files every hour into an external AWS S3 bucket. The data team requires that newly landed files be automatically loaded into Snowflake within seconds of arrival without manual polling or keeping a virtual warehouse running 24/7. Which Snowflake service is specifically designed for this automated, serverless file ingestion?
A. SnowSQL CLI Cron Daemon
B. Zero-Copy Clone Sync
C. Snowpipe
D. Materialized View Refresher
Answer: ✅ C. Snowpipe
Explanation:
1. Concept Overview **Snowpipe** is Snowflake's continuous, event-driven data ingestion service. It leverages serverless compute resources to load data from external stages (Amazon S3, Azure Blob, Google Cloud Storage) into Snowflake tables in micro-batches as soon as files are written to cloud storage.
2. Why the Correct Option is Right **Snowpipe** provides automated, continuous loading using cloud event notifications (e.g., AWS SQS, Azure Event Grid, GCP Pub/Sub). When a file lands in S3, an SQS notification triggers Snowpipe to load the file immediately using Snowflake-managed serverless compute, eliminating the need to maintain an active virtual warehouse.
3. Why Other Options Are Incorrect - **SnowSQL CLI Cron Daemon:** Requires maintaining dedicated on-premises or cloud VMs running scheduled bash scripts; it is not serverless, native, or event-driven. - **Zero-Copy Clone Sync:** Clones existing Snowflake database objects; it cannot ingest external unstructured or semi-structured files from cloud buckets. - **Materialized View Refresher:** Maintains precomputed query results inside Snowflake; it does not ingest external raw data files.
4. Production Scenario / Code Example
-- Step 1: Create an external stage pointing to AWS S3
CREATE OR REPLACE STAGE telemetry_stage
URL = 's3://company-telemetry-bucket/json/'
STORAGE_INTEGRATION = s3_int
FILE_FORMAT = (TYPE = 'JSON');
-- Step 2: Create a Snowpipe with AUTO_INGEST enabled
CREATE OR REPLACE PIPE telemetry_pipe
AUTO_INGEST = TRUE
AS
COPY INTO raw_telemetry
FROM @telemetry_stage;
-- Step 3: Configure AWS S3 event notifications to send 'ObjectCreated' events
-- to the SQS ARN surfaced by:
SHOW PIPES LIKE 'telemetry_pipe';
-- Inspect the 'notification_channel' column!5. Real-World Architecture Context Traditional batch ETL loads files once an hour or once a day using large warehouses. Snowpipe shifts the paradigm to continuous event-driven loading, providing near-real-time data availability for analytics while charging strictly for the compute utilized per file loaded.
6. Common Pitfalls & Anti-Patterns - **Using Snowpipe for massive batch backfills:** Snowpipe is optimized for continuous micro-batches (files between 100 MB and 250 MB). For multi-terabyte initial historical backfills, `COPY INTO` with a dedicated large warehouse is faster and more cost-effective. - **Ingesting billions of tiny 1 KB files:** Loading tiny files increases Snowpipe overhead charges. Files should ideally be sized between 100 MB and 250 MB compressed.
7. Performance & Cost Considerations Snowpipe compute is billed on a serverless model proportional to the volume of data loaded. There is no idle warehouse charge, making it far more economical than running an XS warehouse 24 hours a day for sporadic file arrivals.
8. Exam / Interview Tip Key Snowpipe configurations to know: 1. `AUTO_INGEST = TRUE` listens to cloud event notifications (SQS / Event Grid). 2. Uses serverless compute (does NOT use a user virtual warehouse). 3. `SYSTEM$PIPE_STATUS('pipe_name')` checks pipe health.
9. Key Takeaways - Snowpipe provides automated, continuous, event-driven data ingestion. - Powered by Snowflake-managed serverless compute. - Uses cloud storage notifications (SQS/PubSub) to trigger loads instantly upon file arrival.
10. Related Tutorials & Resources - [Snowpipe — Real-Time Streaming Load](/tutorials/snowflake/snowflake-snowpipe) - [External Stages Setup](/tutorials/snowflake/snowflake-external-stages)`
Question 31
## Question 31 When new CSV files land in an external S3 bucket configured with Snowpipe (`AUTO_INGEST = TRUE`), what latency profile and ingestion model best describes this loading process?
A. Near real-time continuous ingestion within seconds or minutes of file arrival
B. Scheduled 24-hour batch synchronization executing at midnight
C. Strict sub-millisecond atomic memory streaming directly to RAM
D. Weekly bulk loading triggered exclusively via manual administrator intervention
Answer: ✅ A. Near real-time continuous ingestion within seconds or minutes of file arrival
Explanation:
1. Concept Overview Data ingestion models vary by latency: - **Batch Loading (`COPY INTO`):** Scheduled bulk loads running hourly or daily. - **Continuous Ingestion (Snowpipe):** Near real-time loading where files are ingested within seconds or a few minutes of landing in cloud storage. - **Real-Time Streaming (Snowflake Streaming API):** Row-by-row low-latency streaming directly into memory channels (sub-second to seconds).
2. Why the Correct Option is Right **Near real-time continuous ingestion within seconds or minutes** accurately characterizes Snowpipe. As files land in object storage, cloud event notifications trigger Snowpipe's serverless queue, which typically begins loading within seconds to minutes depending on file size and queue depth.
3. Why Other Options Are Incorrect - **Scheduled 24-hour batch at midnight:** Characteristic of traditional nightly batch jobs using `COPY INTO`, not automated Snowpipe event processing. - **Sub-millisecond atomic memory streaming:** Snowpipe loads *files* from cloud storage; sub-millisecond memory loading is impossible over object store latency. (Low latency row streaming requires Snowflake's Snowpipe Streaming API). - **Weekly bulk loading via manual intervention:** Completely contradicts the purpose of automated, continuous `AUTO_INGEST` pipelines.
4. Production Scenario / Code Example
-- Checking the latency and ingestion history of Snowpipe
SELECT
file_name,
stage_location,
last_load_time,
row_count,
error_count,
status
FROM TABLE(information_schema.copy_history(
TABLE_NAME => 'raw_telemetry',
START_TIME => DATEADD(hours, -1, CURRENT_TIMESTAMP())
));5. Real-World Architecture Context In modern data lakehouse architectures, business stakeholders require operational data to be available shortly after generation. Snowpipe bridges the gap between batch files and streaming ingestion without the operational complexity of running Kafka Connect or Spark Streaming clusters.
6. Common Pitfalls & Anti-Patterns - **Assuming Snowpipe is zero-second streaming:** Cloud object storage write consistency and event notification propagation introduce a natural latency of a few seconds to a minute before Snowpipe begins reading. - **Sending files without compression:** Always compress files (e.g., gzip) before sending to S3 to minimize network transfer time and storage costs.
7. Performance & Cost Considerations Snowpipe pricing includes a small overhead fee per file loaded in addition to compute credits. Ingesting fewer, moderately sized files (100–250 MB) is significantly cheaper than ingesting millions of 1 KB files.
8. Exam / Interview Tip Snowpipe is described in Snowflake documentation and certification exams as **near real-time continuous ingestion**.
9. Key Takeaways - Snowpipe provides near real-time ingestion from cloud object storage. - Event-driven via cloud notifications (AWS SQS, Azure Event Grid, GCP PubSub). - Balances low latency with serverless cost efficiency.
10. Related Tutorials & Resources - [Snowpipe — Real-Time Streaming Load](/tutorials/snowflake/snowflake-snowpipe) - [Working with Large Files & Optimization Tips](/tutorials/snowflake/snowflake-large-files-optimization)`
Question 32
## Question 32 A data architect wants to automate data ingestion from cloud stages into Snowflake tables without having to size, monitor, or manage virtual warehouses for the ingestion workload. Which compute architecture does Snowpipe utilize to process incoming files?
A. A dedicated user-created XS virtual warehouse that runs 24/7
B. The client application's local CPU memory
C. An on-premises Hadoop YARN worker cluster
D. Snowflake-managed serverless compute resources
Answer: ✅ D. Snowflake-managed serverless compute resources
Explanation:
1. Concept Overview Snowflake provides two primary compute models: 1. **User-Managed Virtual Warehouses:** Virtual compute clusters (XS, S, M, L, etc.) created, sized, and managed by customers. 2. **Snowflake-Managed Serverless Compute:** Fully managed compute pools where Snowflake automatically provisions, scales, and manages resources behind the scenes.
2. Why the Correct Option is Right Snowpipe utilizes **Snowflake-managed serverless compute resources**. Customers do not specify a `WAREHOUSE` parameter when defining a Pipe. Snowflake automatically allocates the exact compute capacity required to parse and load the arriving files and charges the customer only for the compute used down to the second.
3. Why Other Options Are Incorrect - **A dedicated user-created XS virtual warehouse running 24/7:** Maintaining a running user warehouse incurs continuous credit consumption even when no files are arriving. - **The client application's local CPU memory:** Ingestion occurs entirely inside Snowflake's cloud infrastructure; client machine CPU is not used. - **An on-premises Hadoop YARN cluster:** Snowflake is a pure cloud-native SaaS data platform and does not rely on on-premises YARN clusters for file ingestion.
4. Production Scenario / Code Example
-- Notice the CREATE PIPE syntax: NO WAREHOUSE IS SPECIFIED!
CREATE OR REPLACE PIPE sales_pipe
AUTO_INGEST = TRUE
AS
COPY INTO analytics.sales_raw
FROM @sales_s3_stage
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);
-- In contrast, a standard COPY INTO command requires an active user warehouse:
-- USE WAREHOUSE etl_wh;
-- COPY INTO analytics.sales_raw FROM @sales_s3_stage;5. Real-World Architecture Context Serverless compute decouples operational overhead from data engineering. Engineers no longer need to calculate whether an XS warehouse will be overwhelmed during ingestion spikes or whether an L warehouse will sit idle during quiet night hours.
6. Common Pitfalls & Anti-Patterns - **Specifying WAREHOUSE in CREATE PIPE:** Attempting to assign a user warehouse to a pipe will result in a syntax error; Snowpipe exclusively uses serverless compute. - **Unmonitored serverless spending:** While serverless compute is efficient, misconfigured upstream applications writing millions of corrupt tiny files can generate unexpected serverless charges. Always configure Account Usage alerts.
7. Performance & Cost Considerations Serverless billing scales seamlessly from zero. If no files land for three days, zero compute credits are billed. During heavy burst ingestion, Snowflake scales serverless threads automatically without queuing delays.
8. Exam / Interview Tip Exam questions often test: *"Does Snowpipe require a virtual warehouse?"* The answer is **NO**; it uses Snowflake-managed serverless compute.
9. Key Takeaways - Snowpipe relies entirely on Snowflake-managed serverless compute. - No virtual warehouse configuration or sizing needed. - Billed per second strictly for actual data processed.
10. Related Tutorials & Resources - [Snowpipe — Real-Time Streaming Load](/tutorials/snowflake/snowflake-snowpipe) - [Snowflake Architecture — Storage, Compute & Cloud Services](/tutorials/snowflake/snowflake-architecture)`
Question 33
## Question 33 A company clones an entire 100 TB production database named `PROD_DB` to create `STAGING_DB` using Snowflake Zero-Copy Cloning. Immediately following the clone operation, how does this action affect the organization's billed storage consumption?
A. Storage consumption doubles immediately by 100 TB
B. Storage consumption remains virtually unchanged because the clone references existing micro-partitions via metadata
C. Storage consumption triples due to Fail-safe overhead
D. Storage consumption drops to zero as source files are compressed
Answer: ✅ B. Storage consumption remains virtually unchanged because the clone references existing micro-partitions via metadata
Explanation:
1. Concept Overview Snowflake's multi-cluster shared data architecture separates metadata from physical storage. Physical data is stored as immutable micro-partitions in cloud object storage (S3/ADLS/GCS). The metadata layer maintains a catalog of pointers mapping each table to its constituent micro-partitions.
2. Why the Correct Option is Right Because Zero-Copy Cloning duplicates only the metadata catalog entries, **storage consumption remains virtually unchanged** immediately following the clone. The cloned tables point to the exact same immutable micro-partitions as the source database. The only storage cost incurred is for a few kilobytes of metadata tracking the new object definitions.
3. Why Other Options Are Incorrect - **Storage consumption doubles immediately by 100 TB:** That would occur in legacy databases that physically copy data blocks; Snowflake does not copy blocks upon cloning. - **Storage consumption triples due to Fail-safe:** Fail-safe only applies to historical micro-partitions after retention periods expire; it does not multiply storage on clone creation. - **Storage drops to zero:** Cloning does not compress or alter existing source storage blocks.
4. Production Scenario / Code Example
-- Creating an instant 100 TB clone
CREATE DATABASE staging_db CLONE prod_db;
-- Check storage impact in ACCOUNT_USAGE
SELECT
database_name,
database_id,
deleted,
retention_time
FROM snowflake.account_usage.databases
WHERE database_name IN ('PROD_DB', 'STAGING_DB');
-- Notice both exist independently in metadata, but underlying micro-partitions are shared!5. Real-World Architecture Context In enterprise IT, provisioning a 100 TB QA database traditionally required requisitioning 100 TB of SAN/NAS storage, copying data for 24 hours, and paying thousands of dollars monthly in redundant storage fees. Zero-copy cloning eliminates both the time delay and the infrastructure expense.
6. Common Pitfalls & Anti-Patterns - **Expecting storage to remain free forever:** As soon as `STAGING_DB` performs DML operations (e.g., updates, inserts, deletes), new micro-partitions are written, and storage charges will begin to accrue for those modified blocks. - **Dropping production and expecting clone storage to disappear:** If `PROD_DB` is dropped, the micro-partitions remain alive and billed to `STAGING_DB` as long as the clone references them.
7. Performance & Cost Considerations Organizations can clone production databases multiple times for development, QA, regression testing, and security audits with negligible initial storage expenditure.
8. Exam / Interview Tip Exam questions frequently ask: *"What is the initial storage cost of a cloned table or database?"* The answer is **Zero additional storage (metadata only)**.
9. Key Takeaways - Zero-copy cloning creates metadata references to existing micro-partitions. - Initial storage impact is virtually zero. - Storage costs diverge only as modifications (DML) occur in source or clone.
10. Related Tutorials & Resources - [Zero-Copy Cloning Explained](/tutorials/snowflake/snowflake-zero-copy-cloning) - [Understanding Snowflake Compute & Storage Costs](/tutorials/snowflake/snowflake-costs-best-practices)`
Question 34
## Question 34 Which native Snowflake feature enables point-in-time access to historical data at any specific timestamp, statement ID, or offset within a designated retention window?
A. Object Tagging
B. Resource Monitors
C. Time Travel
D. Virtual Warehouses
Answer: ✅ C. Time Travel
Explanation:
1. Concept Overview Snowflake **Time Travel** provides native SQL access to past versions of tables, schemas, and databases. By preserving historical micro-partitions across the defined retention window, Snowflake allows users to query or restore data exactly as it existed at a past point in time.
2. Why the Correct Option is Right **Time Travel** is the specific Snowflake feature providing point-in-time querying. It supports three historical lookup clauses: 1. `AT(TIMESTAMP => ...)` - view data at a specific date and time. 2. `AT(OFFSET => -60*5)` - view data as it was 5 minutes ago. 3. `BEFORE(STATEMENT => '<query_id>')` - view data immediately prior to a specific SQL statement execution.
3. Why Other Options Are Incorrect - **Object Tagging:** A metadata classification feature used to assign business tags (e.g., `cost_center = 'finance'`) for tracking and compliance. - **Resource Monitors:** Administrative controls that alert or suspend warehouses when credit thresholds are reached. - **Virtual Warehouses:** Compute clusters that execute queries; they have no historical versioning or point-in-time recovery logic.
4. Production Scenario / Code Example
-- Querying table state at an exact point in time
SELECT *
FROM production.orders
AT(TIMESTAMP => '2024-06-15 10:30:00 -07:00'::timestamp_tz);
-- Querying table state 30 minutes in the past
SELECT COUNT(*)
FROM production.orders
AT(OFFSET => -60*30);
-- Querying state immediately before a faulty batch update
SELECT *
FROM production.orders
BEFORE(STATEMENT => '01a2b3c4-0000-1234-0000-56780001abcd');5. Real-World Architecture Context Time Travel replaces complex operational backup and restore procedures. Instead of requesting a full database backup restore from cold tapes, analysts and engineers can self-serve historical data retrieval in seconds directly from the SQL worksheet.
6. Common Pitfalls & Anti-Patterns - **Using Time Travel after a table is permanently purged:** Once Time Travel retention expires, data enters Fail-safe and cannot be queried with `AT` or `BEFORE`. - **Querying beyond the retention limit:** Attempting to query an offset greater than `DATA_RETENTION_TIME_IN_DAYS` results in an error: *"Time travel data is not available."*
7. Performance & Cost Considerations Time Travel queries use standard virtual warehouse compute and execute with performance comparable to standard table queries, benefiting from micro-partition pruning and caching.
8. Exam / Interview Tip Know the three parameters accepted by `AT` and `BEFORE`: 1. `TIMESTAMP` 2. `OFFSET` (negative seconds relative to current time) 3. `STATEMENT` (query ID)
9. Key Takeaways - Time Travel enables historical point-in-time querying using `AT` or `BEFORE`. - Supports TIMESTAMP, OFFSET, and STATEMENT query ID targets. - Configurable up to 90 days on permanent tables (Enterprise edition).
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Snowflake Architecture Explained](/tutorials/snowflake/snowflake-architecture)`
Question 35
## Question 35 A data engineering team wants to construct a fully automated, native CDC pipeline inside Snowflake that: 1. Detects row-level changes (inserts, updates, deletes) on a raw staging table. 2. Automatically triggers a SQL procedure to merge those changes into a curated dimension table whenever new changes arrive. Which combination of Snowflake native database objects is designed to build this workflow?
A. Stream + Task
B. Sequence + Pipe
C. Resource Monitor + Materialized View
D. Stage + File Format
Answer: ✅ A. Stream + Task
Explanation:
1. Concept Overview The canonical architectural pattern for continuous ELT inside Snowflake is the **Stream and Task** combination. A **Stream** continuously tracks table changes (CDC), while a **Task** schedules and executes the transformation logic conditional upon whether the stream contains unconsumed data.
2. Why the Correct Option is Right **Stream + Task** work synergistically: - The **Stream** captures the change delta without altering the base table. - The **Task** is scheduled (e.g., every 10 minutes) with the condition `WHEN SYSTEM$STREAM_HAS_DATA('my_stream')`. If changes are detected, the Task executes a `MERGE` statement and consumes the stream.
3. Why Other Options Are Incorrect - **Sequence + Pipe:** A Sequence generates sequential integers; a Pipe loads files from external stages. Neither can detect or transform CDC deltas between internal tables. - **Resource Monitor + Materialized View:** A Resource Monitor controls credit consumption; a Materialized View precomputes query results. Neither can schedule procedural CDC workflows. - **Stage + File Format:** Defines storage locations and parsing rules for loading external files, not internal table CDC orchestration.
4. Production Scenario / Code Example
-- Step 1: Create a stream on the landing table
CREATE OR REPLACE STREAM customers_landing_stream ON TABLE landing_customers;
-- Step 2: Create a Task triggered by stream data
CREATE OR REPLACE TASK merge_customers_task
WAREHOUSE = etl_wh
SCHEDULE = '10 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('customers_landing_stream')
AS
MERGE INTO dim_customers AS tgt
USING customers_landing_stream AS src
ON tgt.customer_id = src.customer_id
WHEN MATCHED AND src.METADATA$ACTION = 'INSERT' AND src.METADATA$ISUPDATE = TRUE THEN
UPDATE SET tgt.name = src.name, tgt.email = src.email, tgt.updated_at = CURRENT_TIMESTAMP()
WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' THEN
DELETE
WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT' THEN
INSERT (customer_id, name, email, updated_at)
VALUES (src.customer_id, src.name, src.email, CURRENT_TIMESTAMP());
-- Step 3: Activate the task
ALTER TASK merge_customers_task RESUME;5. Real-World Architecture Context This architecture is known as Snowflake's "Continuous Data Pipeline". It replaces external orchestrators like Airflow for simple intradatabase transformations, reducing moving parts and ensuring end-to-end transaction integrity.
6. Common Pitfalls & Anti-Patterns - **Chaining multiple tasks that read the same stream without understanding offset advance:** When a Task successfully executes a DML against a stream, the stream offset advances immediately. A second task reading the same stream will see zero records! If multiple targets need the stream, create separate streams. - **Leaving tasks suspended:** Always remember to execute `ALTER TASK ... RESUME`.
7. Performance & Cost Considerations By pairing the Stream with `SYSTEM$STREAM_HAS_DATA`, the Task only consumes compute credits when real data needs processing, making it exceptionally cost-effective.
8. Exam / Interview Tip Whenever an exam asks how to implement continuous CDC or automated incremental pipelines within Snowflake, the answer is **Stream + Task**.
9. Key Takeaways - Stream tracks change data capture (CDC). - Task schedules and executes transformation logic. - `SYSTEM$STREAM_HAS_DATA` ensures tasks only run when data is present.
10. Related Tutorials & Resources - [Streams — Change Data Capture at Table Level](/tutorials/snowflake/snowflake-streams) - [Tasks — How Scheduling Works Internally](/tutorials/snowflake/snowflake-tasks)`
Question 36
## Question 36 A healthcare analytics organization needs to provide a data science team with an exact snapshot of a sensitive 30 TB patient database for model training. The data science team must have full permissions to alter tables, drop columns, and run experimental feature engineering pipelines without any risk of affecting the live production database. Which Snowflake feature satisfies these requirements instantly and with zero initial storage duplication?
A. Virtual Warehouse Resizing
B. Snowpipe Continuous Ingestion
C. Fail-safe Data Recovery
D. Zero-Copy Clone
Answer: ✅ D. Zero-Copy Clone
Explanation:
1. Concept Overview Snowflake **Zero-Copy Cloning** creates an independent, fully isolated replica of a database or schema at a specific point in time without physically duplicating the underlying data blocks. It achieves this by duplicating the metadata catalog entries pointing to existing immutable micro-partitions.
2. Why the Correct Option is Right **Zero-Copy Clone** is specifically engineered for this use case. The data science team receives a complete, independent copy of the 30 TB database in seconds. They have unrestricted read/write permissions on the clone. Modifications made by the data science team write new micro-partitions for the clone, ensuring complete isolation from production.
3. Why Other Options Are Incorrect - **Virtual Warehouse Resizing:** Scales compute cluster hardware up or down; it has nothing to do with database replication or sandbox environments. - **Snowpipe Continuous Ingestion:** Ingests external files into tables; it cannot duplicate existing internal databases. - **Fail-safe Data Recovery:** Disaster recovery period managed by Snowflake Support; it cannot be used to provision development or data science sandboxes.
4. Production Scenario / Code Example
-- Step 1: Clone production database for data science experimentation
CREATE DATABASE patient_datascience_sandbox CLONE patient_production_db;
-- Step 2: Grant full control to data science role
GRANT ALL PRIVILEGES ON DATABASE patient_datascience_sandbox TO ROLE data_scientist_role;
-- Step 3: Data scientists can experiment freely without affecting production
USE DATABASE patient_datascience_sandbox;
ALTER TABLE clinical.vitals ADD COLUMN bmi_category VARCHAR(20);
UPDATE clinical.vitals SET bmi_category = 'NORMAL' WHERE bmi BETWEEN 18.5 AND 24.9;5. Real-World Architecture Context In regulated industries like healthcare and finance, allowing data scientists or developers direct access to production databases violates data governance and creates severe operational risk. Zero-copy clones provide complete sandbox safety with zero infrastructure delay.
6. Common Pitfalls & Anti-Patterns - **Forgetting that clones diverge in cost over time:** While initial storage is zero, heavy DML updates in the sandbox create new micro-partitions that will accrue storage charges over time. - **Cloning sensitive data without masking:** Clones inherit the exact data of the source. If sensitive PII/PHI is cloned, dynamic data masking policies or role-based access controls must remain enforced on the clone.
7. Performance & Cost Considerations Cloning a 30 TB database takes under 10 seconds because it is purely a metadata operation. Ephemeral sandboxes can be created for model training runs and dropped immediately after validation to keep storage bills minimal.
8. Exam / Interview Tip Remember: Zero-copy clones are fully read/write! They are NOT read-only views or snapshots.
9. Key Takeaways - Zero-Copy Clone provides instant, writable database copies. - Modifying cloned data does not affect production. - Zero initial storage cost due to shared immutable micro-partitions.
10. Related Tutorials & Resources - [Zero-Copy Cloning Explained](/tutorials/snowflake/snowflake-zero-copy-cloning) - [Snowflake Architecture — Storage, Compute & Cloud Services](/tutorials/snowflake/snowflake-architecture)`
Question 37
## Question 37 A company wants to execute an hourly SQL script that rebuilds a customer summary table. Which Snowflake object is used to schedule periodic SQL execution natively within the database?
A. Virtual Warehouse
B. Task
C. Storage Integration
D. External Stage
Answer: ✅ B. Task
Explanation:
1. Concept Overview A Snowflake **Task** enables periodic, scheduled execution of SQL statements, stored procedures, or procedural scripts. It natively handles scheduling using intervals (e.g., every 60 minutes) or CRON expressions.
2. Why the Correct Option is Right A **Task** is the database object specifically created to schedule SQL workloads. Setting `SCHEDULE = '60 MINUTE'` or `SCHEDULE = 'USING CRON 0 * * * * UTC'` executes the SQL query reliably every hour without requiring third-party orchestrators.
3. Why Other Options Are Incorrect - **Virtual Warehouse:** Supplies compute power (CPU and memory) to execute queries, but does not provide scheduling logic or timers. - **Storage Integration:** A secure authentication object that establishes trusted cloud identity federation between Snowflake and cloud storage (AWS IAM / Azure Tenant); it cannot schedule SQL scripts. - **External Stage:** Specifies a pointer to an external cloud bucket; it cannot execute or schedule SQL transformations.
4. Production Scenario / Code Example
-- Creating an hourly scheduled Task
CREATE OR REPLACE TASK hourly_customer_summary_task
WAREHOUSE = analytics_wh
SCHEDULE = '60 MINUTE'
AS
CREATE OR REPLACE TABLE customer_summary AS
SELECT
customer_id,
COUNT(order_id) AS total_orders,
SUM(order_amount) AS total_spend,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;
-- Activate task
ALTER TASK hourly_customer_summary_task RESUME;5. Real-World Architecture Context Using native Snowflake Tasks for intradatabase rollups reduces system dependencies. If your entire workflow resides inside Snowflake, there is no need to stand up an external scheduler or manage API keys.
6. Common Pitfalls & Anti-Patterns - **Overlapping Task runs:** If a task's execution duration exceeds its scheduled interval (e.g., a 60-minute task takes 75 minutes to finish), Snowflake automatically skips the next scheduled run to prevent concurrent execution conflicts. - **Not granting EXECUTE TASK privilege:** The role executing the task must have the global `EXECUTE TASK` privilege on the account.
7. Performance & Cost Considerations Tasks can use user-managed virtual warehouses or Serverless compute. If the hourly script runs in 15 seconds, using Serverless Tasks charges only for 15 seconds of compute, avoiding the 60-second minimum charge of traditional warehouses.
8. Exam / Interview Tip Tasks can be scheduled in two ways: 1. Interval in minutes: `SCHEDULE = '60 MINUTE'` 2. Cron syntax: `SCHEDULE = 'USING CRON 0 * * * * UTC'`
9. Key Takeaways - Tasks schedule native SQL execution in Snowflake. - Can be scheduled via minute intervals or CRON expressions. - Can run on user warehouses or Snowflake serverless compute.
10. Related Tutorials & Resources - [Tasks — How Scheduling Works Internally](/tutorials/snowflake/snowflake-tasks) - [Automation of Data Loads Using Tasks](/tutorials/snowflake/snowflake-tasks-cron)`
Question 38
## Question 38 A telecommunications enterprise receives hundreds of compressed log files every hour across 20 different regional S3 buckets. Manual batch loading scripts are failing to keep up with data volume and frequently fail due to human scheduling errors. Which automated, serverless Snowflake feature should be implemented to ingest these arriving files continuously?
A. Snowpipe with automated event notifications
B. Virtual Warehouse auto-rescaling
C. Zero-Copy Database Replication
D. Dynamic Data Masking Policies
Answer: ✅ A. Snowpipe with automated event notifications
Explanation:
1. Concept Overview Continuous file loading from cloud storage at enterprise scale requires automation. **Snowpipe** provides serverless, event-driven micro-batch ingestion that monitors cloud storage buckets and loads files automatically as they land.
2. Why the Correct Option is Right **Snowpipe with automated event notifications (`AUTO_INGEST = TRUE`)** listens to cloud storage notification services (such as AWS SQS). As soon as an upstream telecom logger deposits a file, S3 publishes an event to SQS, notifying Snowpipe to load the file into the target table using serverless compute without human intervention.
3. Why Other Options Are Incorrect - **Virtual Warehouse auto-rescaling:** Scales compute clusters horizontally to manage concurrent user queries; it does not listen to cloud storage events or automate file ingestion. - **Zero-Copy Database Replication:** Replicates Snowflake databases across regions or cloud providers; it does not ingest raw log files from S3. - **Dynamic Data Masking Policies:** Masks sensitive column data during query execution; unrelated to automated data ingestion.
4. Production Scenario / Code Example
-- Production Snowpipe setup for continuous log ingestion
CREATE OR REPLACE PIPE telecom_logs_pipe
AUTO_INGEST = TRUE
AS
COPY INTO telecom.raw_network_logs
FROM @telecom_s3_stage
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'GZIP' FIELD_DELIMITER = '|');
-- Check pipe status to confirm auto-ingest listener is active
SELECT SYSTEM$PIPE_STATUS('telecom_logs_pipe');5. Real-World Architecture Context In high-throughput logging architectures, traditional batch scripts fail because file arrival rates are irregular. Snowpipe automatically adjusts to variable ingestion velocity, absorbing traffic spikes smoothly without requiring engineers to over-provision warehouse sizes.
6. Common Pitfalls & Anti-Patterns - **Misconfigured Cloud IAM permissions:** If the SQS queue policy or IAM role does not grant Snowflake permission to read SQS notifications, Snowpipe will sit idle and never load arriving files. - **Modifying files in place in cloud storage:** Snowpipe tracks loaded files by path and checksum. Overwriting a file in S3 with the same name will not trigger a reload unless `ALTER PIPE ... REFRESH` is used.
7. Performance & Cost Considerations Snowpipe eliminates the need to run an idle virtual warehouse 24/7. Serverless compute charges are billed strictly for the compute consumed during active micro-batch loading.
8. Exam / Interview Tip Remember: `AUTO_INGEST = TRUE` requires setting up cloud storage event notifications (AWS SQS, Azure Event Grid, or GCP Pub/Sub) pointing to the Snowpipe notification ARN.
9. Key Takeaways - Snowpipe automates file loading via cloud event notifications. - Operates serverlessly with zero warehouse management. - Handles variable file arrival velocities effortlessly.
10. Related Tutorials & Resources - [Snowpipe — Real-Time Streaming Load](/tutorials/snowflake/snowflake-snowpipe) - [External Stages Setup](/tutorials/snowflake/snowflake-external-stages)`
Question 39
## Question 39 During a data backfill operation, an engineer accidentally executes: `UPDATE customer_balances SET balance = 0;` without including a `WHERE` clause, overwriting the account balances of 5 million customers. Which Snowflake feature allows the engineer to restore the previous account balances within minutes without contacting technical support?
A. Fail-safe Disaster Recovery Request
B. Warehouse Query Acceleration Service
C. Time Travel using the BEFORE statement clause
D. Materialized View Rollback
Answer: ✅ C. Time Travel using the BEFORE statement clause
Explanation:
1. Concept Overview Accidental DML modifications are among the most common causes of data corruption. Snowflake **Time Travel** allows users to restore data from a point in time immediately prior to an erroneous query using the `BEFORE (STATEMENT => '<query_id>')` syntax.
2. Why the Correct Option is Right Using **Time Travel with the BEFORE statement clause**, the engineer can retrieve the Query ID of the bad `UPDATE` statement from Query History and immediately restore the original balances: `CREATE OR REPLACE TABLE customer_balances AS SELECT * FROM customer_balances BEFORE (STATEMENT => '<bad_query_id>');` This completes in seconds without restoring from backup files or opening a support ticket.
3. Why Other Options Are Incorrect - **Fail-safe Disaster Recovery Request:** Fail-safe is intended strictly for catastrophic disasters after Time Travel expires; it can only be performed by Snowflake Support and can take days. - **Warehouse Query Acceleration Service:** Accelerates parts of large queries by offloading work to shared serverless compute; it has no data recovery capabilities. - **Materialized View Rollback:** Materialized views maintain precomputed queries; they have no concept of rolling back accidental base table DML operations.
4. Production Scenario / Code Example
-- Step 1: Find the query_id of the rogue UPDATE statement in QUERY_HISTORY
-- Let query_id = '01a5b6c7-0002-3456-0000-78900001abcd'
-- Step 2: Overwrite the damaged table with the pristine state immediately before that statement
CREATE OR REPLACE TABLE customer_balances AS
SELECT *
FROM customer_balances
BEFORE (STATEMENT => '01a5b6c7-0002-3456-0000-78900001abcd');
-- Alternatively, update only the affected column from the historical version:
-- UPDATE customer_balances t
-- SET t.balance = h.balance
-- FROM customer_balances BEFORE (STATEMENT => '01a5b6c7-0002-3456-0000-78900001abcd') h
-- WHERE t.customer_id = h.customer_id;5. Real-World Architecture Context In traditional on-premises databases, fixing an unqualified update across 5 million rows required taking the database offline, restoring a multi-terabyte backup to an auxiliary server, extracting the customer table, and merging it back. In Snowflake, Time Travel resolves this incident in less than two minutes.
6. Common Pitfalls & Anti-Patterns - **Waiting too long to remediate:** If the table retention period is set to 1 day and the team discovers the error on Day 3, the data has moved into Fail-safe and can no longer be self-restored. - **Dropping the table and recreating it without UNDROP:** If you accidentally drop a table, use `UNDROP TABLE customer_balances;` rather than running a new CREATE TABLE.
7. Performance & Cost Considerations Because Time Travel reads micro-partitions already resident in cloud storage, restoration speed is governed by metadata pointers and standard warehouse read bandwidth.
8. Exam / Interview Tip Whenever an exam scenario presents an accidental `UPDATE` or `DELETE` and asks how to recover self-service within the retention period, the answer is **Time Travel**.
9. Key Takeaways - Time Travel restores accidental DML modifications within minutes. - Uses `BEFORE (STATEMENT => '<query_id>')` for exact statement-level recovery. - Completely self-service without requiring Snowflake Support intervention.
10. Related Tutorials & Resources - [Time Travel & Fail-Safe in Snowflake](/tutorials/snowflake/snowflake-time-travel-fail-safe) - [Snowflake Architecture — Storage, Compute & Cloud Services](/tutorials/snowflake/snowflake-architecture)`
Question 40
## Question 40 An enterprise architecture team is designing an incremental ELT framework. Instead of performing full table reloads that scan billions of rows every hour, downstream pipelines should process only the exact rows that were inserted, updated, or deleted in upstream tables. Which Snowflake capability provides this table-level Change Data Capture (CDC) mechanism?
A. Streams
B. Dynamic Data Masking
C. Virtual Warehouse Multi-Cluster Scaling
D. Search Optimization Service
Answer: ✅ A. Streams
Explanation:
1. Concept Overview Incremental data processing requires identifying the delta of changes since the previous processing run. In Snowflake, a **Stream** provides native table-level Change Data Capture (CDC) by tracking micro-partition changes on source tables.
2. Why the Correct Option is Right **Streams** record transactional offsets on source tables. When queried, a stream returns only the records that were inserted, updated, or deleted since the stream's offset was last advanced, eliminating the need for expensive full-table scans or fragile timestamp comparison columns.
3. Why Other Options Are Incorrect - **Dynamic Data Masking:** Column-level security mechanism that redacts sensitive data (e.g., masking credit card digits) during query execution. - **Virtual Warehouse Multi-Cluster Scaling:** Automatically adds or removes virtual warehouse clusters to handle high user query concurrency; unrelated to CDC. - **Search Optimization Service:** Accelerates needle-in-a-haystack point lookups on large tables; does not track CDC deltas.
4. Production Scenario / Code Example
-- Creating a stream on an enterprise orders table
CREATE OR REPLACE STREAM orders_table_stream ON TABLE enterprise_orders;
-- Querying the stream returns only new or modified rows:
SELECT
order_id,
customer_id,
order_total,
METADATA$ACTION,
METADATA$ISUPDATE
FROM orders_table_stream;
-- When consumed inside an INSERT or MERGE, the stream offset advances:
INSERT INTO daily_orders_summary
SELECT order_id, customer_id, order_total
FROM orders_table_stream
WHERE METADATA$ACTION = 'INSERT';