SnowPro Advanced: Data Analyst: key terms

Flashcards for this exam SnowPro Advanced: Data Analyst guide Class dates

• Aggregate FunctionA function that computes one value from a set of rows, such as SUM, AVG, COUNT, MIN, MAX, STDDEV or LISTAGG.Foundational
• Anti JoinA join pattern that returns rows from one table that have no match in another.Intermediate
• CASE ExpressionSQL's conditional expression, which returns a value based on the first WHEN condition that is true.Foundational
• CAST FunctionThe ANSI SQL function that explicitly converts a value to another data type: CAST(expression AS type).Foundational
• COALESCE FunctionA function that returns the first non-NULL argument in its list.Foundational
• Common Table Expression (CTE)A named temporary result set defined with WITH that can be referenced in the statement that follows.Intermediate
• COUNT FunctionAn aggregate that counts rows: COUNT(*) counts every row, COUNT(col) counts non-null values, COUNT(DISTINCT col) counts unique non-null values.Foundational
• CROSS JOINA join with no condition that returns every combination of rows from both tables (the Cartesian product).Intermediate
• Dimension TableA table of descriptive attributes (who, what, where, when) used to filter, group and label facts.Foundational
• Dimensional ModelingA data modeling technique that organizes analytic data into facts (measurable events) and dimensions (descriptive context).Intermediate
• Fact TableThe central table in a star schema that stores numeric measurements of business events plus foreign keys to dimensions.Foundational
• FLATTEN FunctionA Snowflake table function that explodes arrays or objects in VARIANT columns into one row per element.Intermediate
• FULL OUTER JOINA join that returns all rows from both tables, matching where possible and filling NULLs on whichever side has no match.Intermediate
• GROUP BY ClauseThe clause that groups rows sharing the same values so that aggregate functions return one row per group.Foundational
• GROUPING SETSA GROUP BY extension that lists exactly which groupings to compute in a single query.Advanced
• Half-Open Date RangeA date filter written as >= start AND < end, which includes the start and excludes the end so datetime values are captured correctly.Intermediate
• HAVING ClauseThe clause that filters groups after GROUP BY, typically using aggregate conditions.Foundational
• INNER JOINA join that returns only rows with matching values in both tables.Foundational
• LAG and LEAD FunctionsWindow functions that return a value from a previous row (LAG) or a following row (LEAD) within a partition.Intermediate
• LATERAL JoinA join in which the right-hand table expression can reference columns from preceding tables in the FROM clause.Advanced
• LEFT OUTER JOINA join that returns every row from the left table plus matching rows from the right, with NULLs where no match exists.Foundational
• LIMIT ClauseA clause that restricts how many rows a query returns; the syntax varies (LIMIT, TOP, FETCH FIRST n ROWS ONLY).Foundational
• Masking PolicyA Snowflake Enterprise schema-level object that dynamically masks column values at query time based on conditions such as the current role.Intermediate
• NTILE FunctionA window function that divides ordered rows into a specified number of roughly equal buckets and returns each row's bucket number.Advanced
• NULLIF FunctionA function that returns NULL if its two arguments are equal, otherwise the first argument.Intermediate
• PIVOT and UNPIVOTSQL operators that rotate data: PIVOT turns row values into columns, and UNPIVOT turns columns into rows.Intermediate
• QUALIFY ClauseA clause in Snowflake, Databricks SQL, BigQuery and Teradata that filters rows on the result of window functions.Intermediate
• Query Execution PlanThe sequence of physical operations (scans, seeks, joins, sorts) that the database chooses to run a query.Intermediate
• Query ProfileSnowsight's graphical breakdown of a Snowflake query's execution plan, operator statistics, pruning and spilling.Intermediate
• RANK and DENSE_RANKRanking window functions: RANK gives tied rows the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps.Intermediate
• Recursive CTEA CTE that references itself to traverse hierarchical or graph data such as org charts or bills of materials.Advanced
• Result CacheSnowflake's cloud-services cache of query results, reused for identical queries for 24 hours (extendable to 31 days) if the underlying data has not changed.Intermediate
• ROLLUP and CUBEGROUP BY extensions that add subtotal rows: ROLLUP for hierarchical subtotals and CUBE for every combination of the grouping columns.Advanced
• ROW_NUMBER FunctionA window function that assigns a unique sequential integer to rows within each partition according to the ORDER BY.Intermediate
• Running TotalA cumulative sum that adds each row's value to the total of all preceding rows in a defined order.Foundational
• Sargable QueryA query whose predicates can use an index seek or pruning (Search ARGument ABLE), typically because columns are not wrapped in functions.Advanced
• Search Optimization ServiceA Snowflake Enterprise feature that builds a persistent search access path to speed selective point lookups, substring searches and VARIANT field searches.Advanced
• Secure Data SharingSnowflake's capability to share live, read-only database objects with other accounts without copying or moving data.Intermediate
• SELECT StatementThe SQL statement that retrieves rows and columns from one or more tables or views.Foundational
• Self JoinA join of a table to itself, using two aliases, to compare rows within the same table.Intermediate
• Semantic LayerA business-friendly abstraction over physical data that defines entities, relationships and metrics once for consistent use across tools.Intermediate
• Semi JoinA join pattern that returns rows from one table that have at least one match in another, without duplicating them.Advanced
• Snowflake MarketplaceSnowflake's marketplace of free and paid listings of live datasets, data services and Native Apps from third-party providers.Intermediate
• SnowsightSnowflake's web interface for worksheets, notebooks, dashboards, data browsing, administration and monitoring.Foundational
• SQL Logical Query Processing OrderThe order in which SQL logically evaluates clauses: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/TOP.Intermediate
• Star SchemaA dimensional model with a central fact table joined directly to denormalized dimension tables, forming a star shape.Foundational
• Streamlit in SnowflakeA feature for building and hosting interactive Python Streamlit data apps directly inside Snowflake, governed by Snowflake roles.Intermediate
• SubqueryA query nested inside another statement, used as a value, a list, or a derived table.Foundational
• Surrogate KeyA system-generated identifier with no business meaning, such as an auto-increment integer or GUID, used as a primary key.Intermediate
• Time TravelA Snowflake feature for querying, cloning or restoring data as it existed at any point within a retention period.Intermediate
• Top-N QueryA query that returns the N highest or lowest rows overall or per group.Intermediate
• TRY_CAST FunctionA conversion function that returns NULL instead of raising an error when the value cannot be converted.Intermediate
• UNION ALLA set operator that stacks the results of two queries and keeps all rows, including duplicates.Foundational
• UNION OperatorA set operator that combines the results of two queries into one set and removes duplicate rows.Foundational
• VARIANT Data TypeA Snowflake data type that stores semi-structured data such as JSON, Avro, ORC, Parquet or XML, with a maximum size of 128 MB per value.Intermediate
• Virtual WarehouseA named cluster of Snowflake compute resources that executes queries and DML; it can be started, stopped and resized independently.Foundational
• Window FrameThe subset of rows within a partition that a window function considers, set with ROWS or RANGE BETWEEN.Advanced
• Window FunctionA function that computes a value across a set of rows related to the current row, defined with OVER(), without collapsing them.Intermediate