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

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

3

  1. Binary data types can be of max 8MB
  2. While cloning schema or database, pipes that refer external stage are cloned and the ones that reference internal stage are not cloned
  3. VARCHAR without length is same as VARCHAR with max length
  4. Only Enterprise+ accounts can opt for early release access
  5. Resource Monitors with "Suspend Immediate" may still incur charges while the VWH is being suspended
  6. 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
  7. converting char to boolean: (false, 0, no) all abbreviations and cases
  8. Only AccountAdmin can create a SHARE
  9. Standard views cannot be shared, only secured views can be

4

  1. set auto_suspend=NULL (not 0) to ensure WH never suspends
  2. Internal named stage cannot be cloned
  3. Use WAREHOUSE_METERING_HISTORY in either information_schema or account_usage, or Web UI to find out credit used
  4. Sharing data from/to VPS edition isn't supported
  5. 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
  6. Load metadata expire after 64 days
    • Snowpipe history is stored in Pipe's (as opposed to table's) metadata and available for 14 days
  7. Experiment with WH sizes (as opposed to scaling up or down until you don't see any changes)
  8. altering a WH down affects only when there are no SQLs are running on the warehouse

5

  1. COPY INTO command has validation_mode = 'return_errors' that allows checking data without actually loading in
  2. WH can be resized even if it's suspended
  3. Federated authentication and MFA are available to all editions
  4. Uncompressed size of a micro-partition is 50-500MB
  5. COPY INTO can transform semi-structured data during load
  6. EXECUTE TASK account level privilege is needed by the task owning role in addition to perform the actual operation being performed in the task
  7. 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

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

5

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

Udemy AA

1

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

2

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

3

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

4

  1. By default the max unload file size is 16MB
  2. Privileges on objects are not replicated

5

  1. PUT on GCP always overwrites

6

  1. Snowflake replicates data to three availability zones
  2. 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