Skip to content

Test questions

Youtube

1

  • Only single ((eg in File_Format) and double quotes (identifiers) are allowed as value enclosures
  • Comments are -- or // (probably for Javascript)
  • 12 - Use STRIP_OUTER_ELEMENT to remove XML root element
  • 20 - Only the Data Cache (Warehouse Cache) can be controlled by the user (by suspending WH thus clearing it)
  • 21 - Data load rate is affected by: physical location (cross-region etc) and compression efficiency (high IO if uncompressed)
  • 48 - Variant Types: 1) Optimized storage based on repeated elements 2) can be queried using JSON path notation
  • 50 - Streams can be created on temporary and external tables
  • 51 - A task can have only max of 100 child tasks
  • 52 - Query Statements encryption is supported on BC edition
  • 53 - MFA can be used with JDBC driver

2

  • 3: AccountAdmin and SecurityAdmin can create network policies
  • 16: Tech Ecosystem partner categories: ALL (Data Integration, BI, SQL Editors, Security & Governance, Advanced Analytics, Programmatic Interface)
  • 17: Type of stream allowed on external table: Insert Only
  • 21: Tasks tree can only refer to other tasks in the same schema
  • 22: When a Task is created initially, it's state is Suspended
  • 28: BC edition encrypts all data, including within the VPC
  • 31: Triple redundancy for critical Snowflake services
  • 32: Resource Monitors: can suspend warehouse at specific date and time regardless of credit used

3

  • 21: Binary data types can be of max 8MB
  • 23: While cloning schema or database, pipes that refer external stage are cloned and the ones that reference internal stage are not cloned
  • 25: VARCHAR without length is same as VARCHAR with max length
  • 31: Only Enterprise+ accounts can opt for early release access
  • 34: Resource Monitors with "Suspend Immediate" may still incur charges while the VWH is being suspended
  • 36: Snowflake's full release consists of 3 stages spread out over 2 days, i.e. stage 1, all early access stage 2: all standard stage 3: all enterprise+.
  • state 1 happens on day 1, Stage 2 may happen on day 1 or 2, stage 3 happens on day 2
  • patch release moved to all customers on the same day
  • 40: converting char to boolean: (false, 0, no) all abbreviations and cases
  • 57: Only AccountAdmin can create a SHARE
  • 58: Standard views cannot be shared, only secured views can be

4

  • 6: set auto_suspend=NULL (not 0) to ensure WH never suspends
  • 21: Internal named stage cannot be cloned
  • 25: Use WAREHOUSE_METERING_HISTORY in either information_schema or account_usage, or Web UI to find out credit used
  • 27: Sharing data from/to VPS edition isn't supported
  • 30: SQLs start running after all servers have been provisioned within a warehouse, but on a server failure, Snowflake starts running SQL when at least 50% of the servers are available
  • 31: Load metadata expire after 64 days
  • Snowpipe history is stored in Pipe's (as opposed to table's) metadata and available for 14 days
  • 42: Experiment with WH sizes (as opposed to scaling up or down until you don't see any changes)
  • 55: altering a WH down affects only when there are no SQLs are running on the warehouse

5

  • 15: COPY INTO command has validation_mode = 'return_errors' that allows checking data without actually loading in
  • 17: WH can be resized even if it's suspended
  • 18: Federated authentication and MFA are available to all editions
  • 24: Uncompressed size of a micro-partition is 50-500MB
  • 26: COPY INTO can transform semi-structured data during load
  • 45: EXECUTE TASK account level privilege is needed by the task owning role in addition to perform the actual operation being performed in the task
  • 49: When cloning schema or database, the contained objects retain privileges of the source objects

Udemy

1

  • 6: Cloud services provide Infrastructure Management in addition to Metadata Management and Authentication
  • 31: Can't grant ownership of an existing share to some other role
  • 32: NULLs are sorted higher than the other values, (sorted by numeric byte value of each character for ASCII tables?)
  • 52: Clones can load files again from stages => TRUE
  • 62: An idle query holding locks will abort in 4 hours if blocking, 5 minutes otherwise
  • 72: INFORMATION_SCHEMA table functions can be used to obtain account-level information such as storage, warehouses and queries
  • 73: Queries against INFORMATIOIN_SCHEMA views will return error if they return too much data
  • 75: MAX_FILE_SIZE to limit COPY INTO file size
  • 79: COPY INTO allows unloading to S3 locations besides internal and external stages
  • 82: GET supports PARALLEL option to use multiple threads
  • 94: Snowpipe can load file again if modified => FALSE because de-dup works only on filename
    • Bulk-copy de-dup works on filename+MD5
  • 95: Snowpipe REST API works with both, internal and external stages

2

  • 12: Commands to find out if a stream has become stale: SHOW STREAM or DESCRIBE STREAM
  • 13: TASK_HISTORY view required permission: ownership or monitor or operate on task, monitor execution, accountadmin
  • 16: Only the table owner can create initial stream, additional streams require USAGE on database and schema, CREATE STREAM and SELECT on table
  • 39: COPY INTO with PURGE=TRUE doesn't report error if the file has been successfully loaded but cannot be removed for some reason
  • 44: COPY INTO <location> requires s3:PutObject and s3:DeleteObject permissions, but s3:ListObject is not required
  • 59: system$stream_get_table_timestamp gets current offset
  • 62: For a multi-threaded application use, synchronous queries with separate connection per thread instead of asynchronous queries with single connection shared between threads
  • 73: SI parameter name that limits locations that can be accessed: STORAGE_ALLOWED_LOCATIONS
  • 89: To add clustering, USAGE or ownership privileges on schema and database are required in addition to ownership privilege on the table
  • 91: Default USER_TASK_TIMEOUT_MS is 1 hour before Snowflake kills a long running task
  • 93: Cloning a schema that contains table and a stream, will cause the unconsumed records in cloned stream to be inaccessible
  • 99: create storage integration required params: name, type, enabled, storage_allowed_locations

3

  • 5: Partner categories: Data Integration, Business Intelligence, Security & Governance and ML and data science
  • 24: Time travel cannot be disabled at account level, but can be at database, schema and table levels
  • 25: RESOURCE MONITOR can be reassigned from account-level to warehouse-level and vice-versa
  • 36: Cannot use time-travel with shares
  • 57: Continuous data loading options: Snowpipe, Kafka-connector and third-party DI tools
  • 58: Neither Streams nor time-travel are supported for materialized views
  • 64: PIPE_EXECUTION_PAUSED can be set at Account, Schema and Pipe level (but not Database)
  • 73: Empty table has a clustering depth of zero
  • 74: range predicates on high-cardinality columns that are clustered will perform poorly
  • 75: Temporary tables can have clustering key
  • 77: Parquet file size limit is 1GB

4

  • 1) HIPAA requires signed agreement between customer and Snowflake before storing PHI data

5

  • 1) Data compression applies only to CSV and JSON: FALSE, Parquet can also specify compression

Udemy AA

1

  • 4: PIPE doesn't support: FILES=(..), MATCH_BY_COLUMN_NAME, FORCE, PURGE, ON_ERROR=ABORT_STATEMENT, VALIDATION_MODE, RETURN_FAILED_ONLY
  • 9: MCW, new cluster started by default: immediately either query is queued or there's one more query than current clusters can execute
  • economy
  • 26: Who can grant permission EXECUTE TASK? It's a global privilege, so only ACCOUNTADMIN
  • 32: NULLs are ordered higher than any other values; use NULL FIRST/LAST to override
  • values are sorted by the byte value of the ASCII table
  • 37: loadHistoryScan should not be called too quickly and should be narrow; e.g. read the last 10 minutes every 8 minutes
  • 46: name of the parameter to disable query cache: USE_CACHED_RESULT
  • 47: COPY INTO for unloading supports all SQLs including JOIN
  • 53: Extending time-travel: 1) any data in fail-safe has no impact 2) any data already in time-travel will be extended
  • 94: Snowpipe doesn't de-dup by eTag or MD5, just the filename

2

  • 5: Snowpipe insertFiles supports 1) up to 5000 files, 2) file path <= 1024 when serialized to UTF-8
  • 11: Make files on external stage available with low latency: use Snowpipe (not MV)
  • 24: VALIDATION_MODE: RETURN_ALL_ERRORS
  • 26: COMPRESSION=AUTO supports all except BROTLI
  • 33: Can't create clustering keys on VARIANT but you can create on a variant path, and also on expressions
  • 44: Unload permissions needed: s3:DeleteObject and s3:PutObject
  • 91: Maximum task run-time: 60 minutes before it's cancelled
  • 94: streams cannot be created on materialized views

3

  • 17: Cloning a PIPE will cause Snowpipe to load files again into the source table
  • 24: revoke USAGE, SELECT access with CASCADE to prevent your owned objects from being shared by the share owner
  • 27: Snowflake collects stats on parts of VARIANT data, so VARIANT columns can be used for pruning

4

  • 2: By default the max unload file size is 16MB
  • 6: Privileges on objects are not replicated

5

  • 28: PUT on GCP always overwrites

6

  • 14 Snowflake replicates data to three availability zones
  • 70 Compression applies to, besides JSON and CSV, parquet files too

Udemy PT

1

  • WH credit usage: INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY and ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  • Use Account Tab for warehouse usage (not Warehouse tab)

2

  1. COPY transformations include concatenation of columns
  2. Both TO_NUMBER and TO_NUMERIC are valid casting functions
  3. Cloning