Extracting DDL in Snowflake: A Guide to the GET_DDL Function

In database management, the capability to recreate objects is crucial for disaster recovery, environment migration, and version control tasks. Within the Snowflake data warehouse, the GET_DDL function is your powerful ally in retrieving the Data Definition Language (DDL) statements necessary for this replication. Let’s delve into how you can harness this function effectively.

What is GET_DDL?

The GET_DDL function in Snowflake returns a string containing the DDL statement that could be used to recreate a specified database object. It supports a wide array of object types, including:

• Schemas
• Databases
• Tables
• Views
• Stages (Internal and External)
• File Formats
• Sequences
• Procedures
• Functions
• Tasks
• …and even more

Syntax

The fundamental syntax for the GET_DDL function is:

SQL

GET_DDL( ‘object_type’, ‘[database_name.]schema_name.]object_name’ [, true/false ] )

Use code with caution.

content_copy

Let’s break down the parameters:

• ‘object_type’: The type of object you want the DDL for (e.g., ‘TABLE,’ ‘VIEW,’ ‘SCHEMA’).
• ‘[database_name.]schema_name.]object_name’: The fully qualified name of the specific object. You can optionally include the database and schema.
• True/false (optional): This boolean parameter controls whether the DDL output includes the fully qualified names for any referenced objects within the DDL statement. The default is ‘false’.

Illustrative Examples

1. Retrieving Table DDL:
2. SQL
3. SELECT GET_DDL(‘TABLE’, ‘MY_DATABASE.PUBLIC.CUSTOMER_DATA’);
4. Use code with caution.
5. content_copy
6. Generating Schema DDL (with fully-qualified references):
7. SQL
8. SELECT GET_DDL(‘SCHEMA’, ‘MY_DATABASE.ANALYTICS’, true);
9. Use code with caution.
10. content_copy
11. Database DDL (recursive):
12. SQL
13. SELECT GET_DDL(‘DATABASE’, ‘MY_DATABASE’);
14. Use code with caution.
15. content_copy
16. Note: This is a recursive operation and will include DDL for all supported objects within the database.

Key Use Cases

• Replication and Backups: Preserve the structural definitions of your Snowflake objects to simplify the restoration process in case of accidental deletions or replicating environments.
• Version Control: By tracking DDL changes, you maintain a historical record of how your database objects have morphed over time.
• Change Management: The GET_DDL function helps you compare DDL between different Snowflake environments (development, testing, production) to streamline the deployment of changes.

Limitations to Consider

• The GET_DDL function might not retrieve the complete DDL for all object types. Stored procedures, for instance, may need to include their complete procedural logic.
• Grants and object-level permissions are not included in the GET_DDL output.

Best Practices

• Regularly store the DDL of your critical Snowflake objects as part of your backup and disaster recovery strategy.
• Consider systematically integrating the GET_DDL function into a version control system to track DDL revisions.

Let’s Get Practical!

Feel free to experiment with the GET_DDL function in your Snowflake environment to gain hands-on experience.