Tree

Dev4Snow

Go back to Contents – Go back to previous page

Tree

The tree contains all of the databases and schemas selected by the user with their corresponding Tables, Views, Functions and Stored Procedures.

The tree is the starting point for all of the actions available in the software. You can initiate a drag & drop operation from the tree by dragging an object to the SQL, Query Builder, Data Analysis, Data Lineage and Data Modeler tabs.

Available Drag & Drop operations:

Dragging the object below from the tree…	into SQL tab	into Query Builder tab	into Data Analysis tab	into Data Lineage tab	into Data Modeler tab
Schema	-	-	-	-	Reverse-Engineer the schema data model
Table	Bring Fully Qualified table name	Add table to Query Builder	Run data analysis for table	Run Data Lineage for table	Add table and tables referenced by it
View	Bring Fully Qualified view name	Add view to Query Builder	Run data analysis for view	Run Data Lineage for view	-

Tree Icons:

Filtering: Use it to filter all objects in the tree that match the specified substring. Use the icon with the minus sign to remove the filter and collapse the tree:

Select Favorite databases and schemas:

Refresh shown databases and schemas. Usually this is not needed since refresh is already happening at least once a day, and also after running a DDL statement (for create, delete and rename of objects):

Toggle DDL Panel: When this panel is open, every time you click on an object (table, view, function, procedure) the DDL for the object will be retrieved and shown in this panel.

Tree Context Menus:

For Databases:

Refresh this branch: Refresh the cache for all the schemas that changed in this database.

Script… > Script this branch: This option will ask for a script file name and start a process in background that will create a script for this database containing all of its schemas and their tables, views, functions and stored procedures.

Scan Data Model: Opens a new window where you can scan a Data Model. Please refer to Scan Data Model for further details.

Where-used List: Find references to columns, table/view names or texts that are found in the DDL of objects located in the same database, favorite databases or all databases. Please refer to Where-used List for further details

For Schemas:

Refresh this branch: Refresh the cache for the schema in case it changed.

Action… > Find duplicate PKs in tables: Since Snowflake does not enforce Primary Keys there may be instances where due to an error in some ETL/ELT process, duplicate records exist. This feature will check if duplicate keys exist in the tables of this schema and generate a list of tables containing duplicate records.

Action… > Compare Definition (DDL)… : Compares all object definitions (DDL) from the schema with another schema from the same or different Snowflake tenant, database or schema name. Please refer to Object Definition Comparison for further details.

Action… > Add tables to ERD: This option will execute a Reverse-Engineering of all of the tables in the schema adding them to the Data Modeler / ERD. It is the same as dragging the schema from the tree and dropping it in the Data Modeler / ERD.

Script… > Script this branch: This option will ask for a script file name and start a process in background that will create a script for this schema containing all of its tables, views, functions and stored procedures.

Script… > De-deduplication of Primary Keys: Since Snowflake does not enforce Primary Keys there may be instances where due to an error in some ETL/ELT process, duplicate records exist. This feature will check if duplicate keys exist for the tables in the schema –only tables with Primary Keys- and if there are any duplicates it will generate a script to remove them.

Scan Data Model: Opens a new window where you can scan a Data Model. Please refer to Scan Data Model for further details.

For Tables:

Select top 1000 rows: It will immediately query 1,000 rows for this table and show the results.

Select count: It will immediately query the number of rows for this table and show the result.

Edit top 1000 rows: Starts the Data Edit Mode for this table. Click on the link to find more info.

Edit definition (DDL): Retrieves the definition (DDL) and puts it in a SQL code empty window. The name of the table is fully qualified to include the database and schema names.

Action… > Find duplicate PKs or custom keys: This option checks if there are duplicate records comparing by Primary Key or by a custom key selected by the user.

Action… > Compare Definition (DDL)… : Compares an object definition (DDL) with another object definition from the same or different Snowflake tenant, databae, schema or object name. Please refer to Object Definition Comparison for further details.

Action… > Add to Query Builder: Adds the table to Query Builder. It does not delete the existing objects in Query Builder.

Action… > Run Data Analysis: Opens the object in Data Analysis and starts data analysis.

Action… > Run Data Lineage: Opens the object in Data Lineage and starts the data lineage analysis.

Action… > Add to Data Modeler/ERD: Adds the table to Data Modeler/ERD. It does not delete the existing objects in the diagram.

Script… > Select <expanded columns>: Scripts a “Select” statement enumerating all the available columns. For example:

select
      C_CUSTKEY,
      C_NAME,
      C_ADDRESS,
      C_NATIONKEY,
      C_PHONE,
      C_ACCTBAL,
      C_MKTSEGMENT,
      C_COMMENT
from
      SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

Script… > De-duplication of PKs or custom keys: Creates a script for de-duplication of Primary Keys or custom keys selected by the user. This option will not check if there are duplicates in the table, it will always script a de-duplication for the table. It is safe to run the script even if there are no duplicates.

Script… > Data recovery… > Recover replaced table: Use this option in case by mistake you ran a CREATE OR REPLACE for a table that had records. It scripts the recovery of the table structure and data.

Script… > Time Travel > Select top 1000 before today: Creates a script to select the first 1000 records that existed on a table at the end of the previous day. Use it as an example of how to query the Time Travel data.

Script… > Time Travel > Select top 1000 at 5 minutes ago: Similar to previous option but for records that existed 5 minutes ago. Use it as an example of how to query data that existed before running a DELETE or UPDATE statement.

Script… > Time Travel > Select top 1000 before statement: Similar to previous option, but you have to indicate the ID of the statement that modified the table. You can get the query ID from the Query History.

Clone to table data from yesterday: Scripts a table cloning using data from yesterday. Use it as an example of how to clone Time Travel data.

For Views:

Select top 1000 rows: It will immediately query 1,000 rows for this view and show the results.

Select count: It will immediately query the number of rows for this view and show the result.

Edit definition (DDL): Retrieves the definition (DDL) and puts it in a SQL code empty window. The name of the view is fully qualified to include the database and schema names.

Action… > Find duplicate custom keys: This option checks if there are duplicate records comparing by a custom key selected by the user.

Action… > Compare Definition (DDL)… : Compares an object definition (DDL) with another object definition from the same or different Snowflake tenant, database, schema or object name. Please refer to Object Definition Comparison for further details.

Action… > Add to Query Builder: Adds the table to Query Builder. It does not delete the existing objects in Query Builder.

Action… > Run Data Analysis: Opens the object in Data Analysis and starts data analysis.o

Action… > Run Data Lineage: Opens the object in Data Lineage and starts the data lineage analysis.

Script… > Select <expanded columns>: Scripts a “Select” statement enumerating all the available columns. For example:

select
      C_CUSTKEY,
      C_NAME,
      C_ADDRESS,
      C_NATIONKEY,
      C_PHONE,
      C_ACCTBAL,
      C_MKTSEGMENT,
      C_COMMENT
from
      SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

Script… > Time Travel > Select top 1000 before today: Creates a script to select the first 1000 records that existed on a view at the end of the previous day. Use it as an example of how to query the Time Travel data.

Script… > Time Travel > Select top 1000 before statement: Similar to previous option, but you have to indicate the ID of the statement that modified one of the underlying tables of a view. You can get the query ID from the Query History.

For Stored Procedures and Functions:

Edit definition (DDL): Retrieves the DDL and puts it in a SQL code empty window. Some conversions are applied to the DDL statement: the name of the Stored Procedure or Function is fully qualified to include the database and schema names, and the single quotes that enclose the full body of the object are replaced by $$, and all the double single quotes that appear across the code are replaced by a single quote. These changes are applied to ease the code edition, since you won’t need to add double single quotes to enclose literals, plus that the syntax highlighting will not show the full body as a literal. This doesn’t affect the objects since when running the DDL statement Snowflake will convert them back to single quotes –or double single quotes when used for literals.

Script… > Call statement: Scripts an example of how to call the Stored Procedure or Function.

Script… > Definition (DDL) w/o changes: Scripts the DDL in a new SQL window exactly as it is stored in Snowflake. This is slightly different from “Edit Definition (DDL)” since it won’t add database and schema to the Stored Procedure or Function name, nor replace the single quotes. Use this option if you experience any issues editing the DDL with “Edit definition (DDL)”.