SnowPro Advanced Data EngineerDemo

OBJECT_CONSTRUCT creates a Snowflake OBJECT directly from relational column values; with a wildcard, it uses column names as object keys. This produces one semi-structured object per row without first serializing or parsing JSON, or converting each column individually.

Snowflake supports loading selected staged-file columns through a COPY INTO ... FROM (SELECT ...) transformation. A transformation cannot use WHERE or LIMIT, and VALIDATION_MODE is unsupported for transformed COPY loads and performs validation rather than loading data.

SKIP_HEADER specifies the number of lines at the beginning of a CSV file that Snowflake skips during loading. Setting it to 1 excludes one header record from the loaded data.

Snowflake SQL API requests can authenticate programmatically with an OAuth bearer token or with key-pair authentication, which uses a JWT generated from an assigned public/private key pair. MFA and browser-based SSO methods are not the SQL API request authentication methods.

SnowflakeFile.open opens the supplied staged file for reading. The handler reads its lines, splits each line by commas, and yields a three-value tuple for each valid line, which becomes a row in the UDTF's tabular result. The yielded values reorder the first two CSV fields and retain the third field.

RETURNS NULL ON NULL INPUT causes Snowflake to skip execution of the stored procedure when any input argument is NULL and return NULL instead. Therefore, the INSERT statement does not run, leaving the initially empty table with zero records.

Snowflake streams provide repeatable read isolation, so multiple SQL statements in the same transaction see the same stream records. The stream offset advances to the transaction start time only when that transaction commits.

Snowflake file-format options may be specified directly in a COPY command, encapsulated in a named FILE FORMAT object, or defined for a named STAGE. These mechanisms determine characteristics such as file type, delimiters, compression, and related parsing or output settings for data loads and unloads.

A dedicated virtual warehouse isolates dynamic-table refresh consumption from other workloads, allowing refresh performance and credit usage to be monitored accurately and a cost baseline to be established. The dynamic tables can then be moved to a shared warehouse.

A detached explicit transaction that holds locks must be aborted with SYSTEM$ABORT_TRANSACTION. Snowflake specifies this function for aborting a running transaction after an abrupt session disconnect, which clears the transaction’s locks and allows the blocked work to proceed.

SYSTEM$PIPE_STATUS takes the fully qualified pipe name as one single-quoted string. A case-sensitive identifier within that name must retain double quotes, so the schema is written as "Extract" inside 'test."Extract".my_pipe'. The function returns a JSON value rather than a table result. Snowflake SYSTEM$PIPE_STATUS documentation

Snowflake tag-based masking policies combine object tags with dynamic data masking. A masking policy can allow the researcher and marketing roles to see unmasked values while masking values for other roles. Assigning that policy to a tag and applying the tag to each sensitive table automatically protects the table’s matching columns, while the tag also identifies sensitive tables for governance. This is more scalable than assigning a policy separately to every table.

Snowflake does not permit a database or database object created from an inbound share to be reshared through another share. Attempting to grant USAGE on the secure function to an outbound share returns an error, even though secure UDFs are generally shareable objects.

Snowflake’s ALTER TAG ... ADD ALLOWED_VALUES syntax appends a value to a tag’s existing allowed-values list without removing MARKETING or FINANCE.

Bulk loading stages files in Azure Blob Storage and uses COPY INTO <table> to load them into Snowflake. A scheduled five-minute batch load satisfies the stated refresh interval without the continuous, low-latency ingestion infrastructure of Snowpipe Streaming. Snowflake documents Azure Blob Storage as a supported external source for bulk loading with COPY INTO.

A date clustering key colocates rows with similar date values into micro-partitions, allowing Snowflake to prune the overwhelming majority of partitions for a single-day predicate. This reduces remote disk I/O and data scanned, which are the principal bottlenecks in this workload. Adding warehouse clusters primarily increases concurrency, and increasing warehouse size does not correct the lack of partition pruning.

For an existing Snowflake table, a masking policy is assigned with ALTER TABLE ... MODIFY COLUMN <column_name> SET MASKING POLICY <policy_name>. The policy must be attached to full_name, which is the column requiring protection, rather than to first_name and last_name.

Snowsight and DYNAMIC_TABLE_REFRESH_HISTORY provide dynamic-table refresh status, duration, lag, and related refresh details. Dynamic-table refreshes execute on their assigned virtual warehouse, and QUERY_HISTORY can be correlated with refresh query IDs or filtered by warehouse to assess elapsed time, compute usage, and whether the warehouse is appropriately sized. Monitor dynamic tables and Understanding costs for dynamic tables document these monitoring approaches.

ESCAPE_UNENCLOSED_FIELD defines the escape character for unenclosed fields during data unloading. Snowflake accepts common escape sequences, and a backslash escape character is represented as \\; it allows delimiter characters such as commas to be treated as literal field data when escaped.

A table stream’s STALE_AFTER deadline uses the greater of DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS from the source table. With values of 7 and 10, respectively, the stream data must be consumed within 10 days; consuming it advances the stream offset and staleness deadline.

Recovering data from Fail-safe uses Snowflake-managed serverless compute, so the recovery operation consumes credits. Zero-copy cloning initially shares underlying storage, while MFA and RBAC are security and access-control features.

When a database is dropped, its 7-day database-level Time Travel retention governs recovery, and explicitly different retention values on child tables are not honored. After 8 days, the database has moved beyond Time Travel into Fail-safe; recovery during Fail-safe requires Snowflake Support.

The schedule matches 1:00 AM local time. When clocks fall back in the America/Los_Angeles time zone, 1:00 AM occurs twice, so a wall-clock cron schedule can execute for both matching local times. Cloud Scheduler documentation notes that repeated local times during clocks moving backward can cause execution anomalies.

The INFORMATION_SCHEMA.POLICY_REFERENCES table function supports row access policies with the POLICY_NAME argument and returns a row for each object to which that policy is assigned. The REF_ENTITY_NAME and REF_ENTITY_DOMAIN arguments instead retrieve policies associated with a specified object. Snowflake POLICY_REFERENCES documentation