Data Engineering

An architect is designing a pipeline to transform data from a raw landing zone to a gold-tier reporting layer. The pipeline requires complex multi-table joins and aggregations that must stay updated within a five-minute latency window. Which Snowflake feature provides the most simplified declarative approach for this requirement?

A data engineer is designing a batch transformation pipeline using Dynamic Tables. The source table is updated hourly, and the target Dynamic Table must reflect changes within 30 minutes. The transformation involves a complex join and aggregation. Which approach best meets the freshness requirement while minimizing cost?

A Snowflake architect is implementing a Type-2 slowly changing dimension (SCD2) on the CUSTOMER_DIM table using a Stream on the source table CUSTOMER_RAW and a task that runs every 5 minutes. The task currently reads the stream and applies a MERGE that only handles inserts and updates. Historical versions are lost. The architect must preserve prior attribute values for changed customers and mark each row with effective and end timestamps. Which approach should the architect use to meet this requirement?

Which object acts as the logical container for storing data in Snowflake, and how is it related to the underlying physical storage?

When designing an ELT pipeline using Snowflake, why is it recommended to perform transformations inside Snowflake rather than using an external ETL tool?

Refer to the exhibit. What is the primary purpose of the WHEN clause in this Task definition?

A healthcare analytics company stores patient encounter data in a Snowflake table with columns: encounter_id (NUMBER), patient_id (NUMBER), encounter_date (DATE), and diagnosis_code (VARCHAR). The table is 20 TB and grows by 100 GB per day. Most queries filter on encounter_date and join to a patient dimension on patient_id. The architect must design a clustering key to optimize these queries while minimizing reclustering cost. Which clustering key should the architect choose?

A retail company ingests a daily 2 TB CSV file into a Snowflake table. The file is stored in an internal stage. The COPY INTO command currently runs with a single large file, and the load takes over 4 hours. The architect wants to reduce load time by leveraging parallelism. Which action should the architect take?

A data architect needs to load data from an external stage into a Snowflake table. The data files are in Parquet format and are updated daily. The architect wants to minimize storage costs and avoid data duplication. Which command should be used to load only new or changed files?

Refer to the exhibit. Task T2 is a child task of T1. If T1 completes successfully but the stream 'S1' is empty, what will be the status and behavior of Task T2?

A data engineer is observing high costs associated with Snowpipe for a high-volume ingestion pipeline where many small files arrive every second. What is the most effective architectural change to reduce Snowpipe costs while maintaining near real-time ingestion?

Refer to the exhibit. An architect reviews the status of a Snowpipe and notices a high 'pendingFileCount'. The warehouse is not under heavy load. What is the most effective way to improve the ingestion throughput for this pipe?

A data engineer is concerned about the performance of a large-scale batch transformation that runs every night. Which TWO techniques can be used to improve the performance of a complex join between two very large tables (billions of rows)?

When designing a multi-layered architecture (Raw -> Silver -> Gold), which Snowflake object type is best suited for building the 'Silver' layer for incremental transformations?

An architect is designing a near-real-time ingestion path using Snowpipe streaming into a target table. The team must choose TWO design decisions that reduce end-to-end latency and cost for continuously arriving events. (Choose two.)

A data engineer needs to share data with an external organization without moving or copying the data. Which feature is the most efficient and secure way to implement this?

A healthcare analytics team uses Snowflake to analyze patient records. They have a large fact table 'ENCOUNTERS' that is clustered by 'PATIENT_ID' and 'ENCOUNTER_DATE'. The team frequently runs queries that filter on 'FACILITY_ID' and 'DIAGNOSIS_CODE', which are not part of the clustering key. These queries perform poorly. The architect needs to improve performance without changing the existing clustering key, as it benefits other queries. What should the architect do?

A Snowflake architect is designing a pipeline that ingests semi-structured JSON events from an internal stage and needs to write them into a VARIANT column. The events contain nested keys that vary in depth and casing across sources, and the team wants to flatten only a fixed set of known top-level keys while preserving the remaining structure for later analysis. Which approach best satisfies these requirements?

A data engineer is designing an automated ingestion pipeline using Snowpipe Streaming to ingest high-frequency clickstream data from Kafka into Snowflake tables. The architecture requires low latency and cost-effective continuous loading. Which underlying Snowflake architectural feature makes Snowpipe Streaming uniquely capable of bypassing the traditional internal staging phase?

An enterprise data engineering team is designing a real-time ingestion pipeline into Snowflake using Snowpipe streaming. The target is a transactional table that requires low latency and high frequency inserts from Java applications. Which architectural consideration is critical for optimizing performance and maintaining transactional integrity when using Snowpipe streaming?

An organization requires that all data ingested into Snowflake must be encrypted at rest with customer-managed keys. Which feature enables this security requirement for data stored in Snowflake?

A data engineer is loading data into a Snowflake table using the COPY INTO command. The source files are in an external stage and have a consistent schema. The engineer wants to ensure that any errors during loading do not cause the entire load to fail, but rather log the errors for later review. Which COPY INTO option should the engineer use?

A Snowpipe is configured to load data from an S3 bucket. The architect notices that some files are failing to load due to a schema mismatch, but no alerts are being generated. What is the most robust way to implement automated error notification for Snowpipe?

A data engineer is loading a large CSV file into Snowflake using the COPY INTO command. They want the load to continue even if some rows have errors, and they need to review the errors later. Which parameter should be added to the COPY command?

A data engineering team needs to merge late-arriving dimension updates into a large target table. The source is a staging table containing both inserts and updates, and the target has a natural business key. The team wants a single statement that applies all changes atomically and avoids duplicate rows when the source contains multiple records for the same key. Which approach should the architect recommend?

A retail company ingests point-of-sale data into a Snowflake table using Snowpipe. The data arrives as JSON files in an internal stage. The architect needs to transform the semi-structured JSON into a relational format and load it into a reporting table. The transformation involves flattening nested arrays and applying several business rules. The volume is high, and the team wants to minimize latency and cost. Which approach should the architect recommend?

Refer to the exhibit. An architect observes these statistics in the Query Profile for a nightly batch job. What is the most effective architectural change to address the performance bottleneck shown?

An e-commerce company wants to analyze JSON data stored in an external stage on Amazon S3. The JSON files contain nested arrays and objects, and the schema varies between files. The architect needs to query this data with Snowflake while minimizing data duplication and storage costs. Which approach should the architect recommend?

A data architect is designing a pipeline that uses Snowpipe to load data from an external stage. The architect wants to ensure that data is loaded as soon as files are available and that the load is triggered automatically. Which mechanism should be used to notify Snowpipe of new files?

A data architect is designing a pipeline that ingests streaming data into a Snowflake table. The data must be transformed with a Python UDF that calls an external API for enrichment. The architect wants to minimize latency and ensure the UDF can scale independently. Which Snowflake feature should be used?

An architect needs to implement a Change Data Capture (CDC) process for data stored in an external S3 bucket without moving all data into Snowflake first. Which TWO features must be combined to track new and modified files efficiently? (Select TWO)

A data engineer needs to copy data from an internal stage into a Snowflake table. The stage contains files with a mix of valid and malformed records. The engineer wants to load all valid records and capture the malformed ones for later analysis without failing the entire load. Which COPY INTO option should be used?

A company is moving towards an Open Data Lakehouse architecture using Snowflake Iceberg Tables. They want to ensure that the data is stored in Parquet format in their own S3 bucket but still benefit from Snowflake's performance. Which configuration should the architect recommend for the Iceberg Table's catalog?

A data architect is designing a pipeline that requires data freshness within 5 minutes across a series of five interdependent tables. The architect wants to minimize the operational overhead of managing task schedules and manual dependency logic. Which Snowflake feature should be prioritized to meet these requirements?

A data engineer needs to load data from a local file system into a Snowflake table. The file is 10 GB in size and contains CSV data. The engineer wants to use the most efficient method for a one-time bulk load. Which Snowflake feature should the engineer use?

A healthcare analytics team stores patient encounter records in a Snowflake table that is updated continuously by an external ETL tool using MERGE statements. The team needs to build a downstream transformation that incrementally processes only the rows that were inserted or changed since the last run. They want to avoid reprocessing the entire table and do not want to add triggers or modify the ETL tool. Which Snowflake feature should the architect use to capture these changes?

A data engineering team needs to load data from an on-premises Oracle database into Snowflake on a recurring basis. The data volume is large (several terabytes), and the team wants to minimize the load on the source system. They also require the ability to perform incremental loads based on a timestamp column. Which Snowflake feature should the architect recommend?

A data engineer must load a daily batch of CSV files from an internal stage into a staging table. The files use a pipe delimiter, include a header row, and contain date values in a non-default format. The team wants to validate that the load succeeded and capture any rejected rows for review. Which configuration should the architect specify?

An architect is building a Dynamic Table that reads from a base table receiving continuous inserts. The refresh is configured with TARGET_LAG = '1 minute' and the warehouse is a dedicated XSMALL. Monitoring shows refreshes frequently take longer than one minute and sometimes overlap with the next scheduled run. The team wants to reduce refresh latency without changing the query logic. Which change is most appropriate?

A retail company ingests JSON clickstream events into a Snowflake table using Snowpipe streaming. The events contain a nested field 'user' with subfields 'id' and 'name'. The architect needs to query only the 'id' subfield without scanning the entire JSON. Which approach is most efficient?

An architect is designing a staging area for a daily ETL process where data is loaded, transformed, and then moved to a permanent production table. The staging data is only needed for 24 hours and does not require long-term Fail-safe protection. Which table type is most cost-effective?

A financial services firm uses Snowflake to store transactional data. The data engineering team needs to implement a process that automatically copies new files from an external stage into a raw table and then triggers a series of dependent transformations. The team wants to minimize manual intervention and ensure that transformations run only when new data is available. They also want to avoid running transformations on empty batches. Which combination of Snowflake features should the architect use?

An architect is building a near-real-time pipeline that reads JSON events from a Kafka topic and must land them into Snowflake with sub-minute latency. The team has a Snowpipe streaming setup using the Snowflake Ingest SDK and writes to a table with a VARIANT column. They observe that the ingestion service occasionally reports channel errors and some events are missing after a client restart. Which configuration change best addresses the missing events?

A data engineering team needs to transform raw JSON events into a curated table that refreshes automatically as new data arrives, without writing or scheduling any orchestration code. The target must reflect changes within a defined lag and be queryable like a regular table. Which Snowflake feature should the architect recommend?

A data engineer is setting up Snowpipe to ingest data from an S3 bucket. The engineer wants to ensure that Snowpipe is notified immediately when a new file arrives without polling the stage. What is the standard Snowflake recommendation for this configuration?

A company is implementing a Data Lakehouse architecture and wants to store their data in the Apache Iceberg format on S3 while still using Snowflake for high-performance analytics. What is the most important architectural consideration when using Snowflake-managed Iceberg tables?