ARA-C01 Question 306
Single answerSparkA data engineering team uses Apache Spark on Databricks to load 8 TB of clickstream data into Snowflake every night. The current job writes directly from Spark to a target Snowflake table using the Snowflake Connector for Spark. The load often runs slowly and occasionally fails when one Spark partition contains malformed records. The architect must improve throughput and make the load more resilient without introducing custom retry logic in Spark. Which approach should be recommended?
- A
Write the Spark DataFrame to Snowflake with the connector while enabling column pruning only, because malformed records are filtered automatically by the connector during INSERT operations.
- B
Use the Snowflake Connector for Spark with an internal or external stage so Spark partitions unload files to cloud storage and Snowflake performs a COPY INTO load, with file-based parallelism and configurable load error handling.
- C
Replace the Snowflake Connector for Spark with JDBC batch inserts, because JDBC provides better parallelism for very large loads and skips only the bad rows by default.
- D
Persist the DataFrame as a temporary view in Spark SQL and use a MERGE statement over JDBC for the entire 8 TB dataset, because MERGE is the most efficient ingestion pattern for raw append-only files.
Show answer and explanation
Correct answer: B
Explanation
For large-scale Spark ingestion into Snowflake, the recommended pattern is to use the Snowflake Connector for Spark, which stages data files and then invokes Snowflake COPY INTO operations rather than relying on row-by-row inserts. This approach leverages Snowflake's elastic, parallel file loading and is significantly better suited for multi-terabyte loads. It also provides more robust handling of malformed records through Snowflake load options and file format controls, reducing the need for custom retry logic in Spark. In contrast, JDBC inserts are typically less performant for bulk loads, and MERGE should be reserved for cases that genuinely require upsert logic. Relevant Snowflake guidance includes documentation for the Snowflake Connector for Spark and bulk loading using COPY INTO, which emphasize staged file-based loading as the efficient pattern for large data volumes.
- A. Incorrect.
Incorrect. The Snowflake Connector for Spark does not rely on row-by-row INSERT behavior for high-volume loads, and column pruning is a query optimization concept, not a mechanism for handling malformed input rows during ingestion. A common misconception is that Spark-side optimizations automatically solve Snowflake load quality issues. They do not address resilient bulk loading or Snowflake-side error handling.
- B. Correct.
Correct. For large Spark-to-Snowflake writes, the Snowflake Connector for Spark uses a staged file transfer pattern and then issues COPY INTO commands in Snowflake. This is the recommended architecture for high-throughput bulk ingestion because Snowflake can load files in parallel. It also improves resilience because COPY INTO supports load error handling options such as continuing past certain bad records or validating data before loading, depending on the chosen settings and file format behavior. This aligns with Snowflake best practices for bulk data loading from Spark.
- C. Incorrect.
Incorrect. JDBC batch inserts are generally less efficient than the Spark connector for large-scale Snowflake ingestion because they do not use Snowflake's optimized staged bulk loading pattern. JDBC is more likely to become a bottleneck at this scale. Also, JDBC does not inherently skip bad rows by default in a way that replaces Snowflake bulk-load error handling.
- D. Incorrect.
Incorrect. Using a Spark temporary view and then issuing a massive MERGE over JDBC is not the recommended ingestion pattern for raw nightly append-only data at 8 TB scale. MERGE is useful for upsert semantics when matching source and target rows, but it is usually more expensive and less efficient than bulk loading with COPY INTO for straightforward ingestion. This option reflects the misconception that MERGE is a universal replacement for optimized bulk loads.