5 Powerful Prompts for Heavy-duty Data Engineering (Using Snowflake Cortex AI)

Unlocking Data Engineering Potential with Advanced AI-Powered Prompts

Back circa 2020/2021, when GenAI was still a niche concept, I had early access to a few GenAI models and remember the peculiar blank canvas challenge. The models were becoming incredibly powerful, but I, like many others, often found myself at a loss for what to prompt. It’s a common sentiment in the field — having this amazing tool at your fingertips but not quite knowing what to ask of it or what to do with it.

www.pixabay.com

For enterprises, this scenario flips the traditional problem-solving approach on its head. Before, it was all about identifying problems and seeking solutions. Now, solutions are getting so powerful so fast that the biggest alpha is in finding the right problems to match the solutions fast.

Never thought I’d be advising anyone to “go look for problems,” but here we are.

To help you get started with potential problems and get past the blank-canvas syndrome or creative block, here are five prompts that data engineers are using with Snowflake Cortex, and you can start using them today.

1. Test & Synthetic Data Generation

Creating realistic datasets for testing and development without risking sensitive production data is crucial. It ensures compliance with data privacy regulations while maintaining the integrity of testing processes.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Generate a synthetic dataset of 50 rows with columns: user_id, user_name, email, signup_date, and purchase_amount. Provide the data in JSON format.'
);

Sample Result:

[
 {
 "user_id": 1,
 "user_name": "John Doe",
 "email": "john.doe@example.com",
 "signup_date": "2023–01–15",
 "purchase_amount": 150.25
 },
 {
 "user_id": 2,
 "user_name": "Jane Smith",
 "email": "jane.smith@example.com",
 "signup_date": "2023–02–20",
 "purchase_amount": 200.50
 },
 …
]

2. Data Masking & Obfuscation

This is crucial for enterprises to protect sensitive information while still allowing for meaningful analysis, enabling them to share data securely with third parties and maintain compliance with data protection laws.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Mask the sensitive columns (user_name, email) in the following dataset: [{"user_id": 1, "user_name": "John Doe", "email": "john.doe@example.com", "signup_date": "2023–01–15", "purchase_amount": 150.25}, {"user_id": 2, "user_name": "Jane Smith", "email": "jane.smith@example.com", "signup_date": "2023–02–20", "purchase_amount": 200.50}].'
);

Sample Result:

[
 {
 "user_id": 1,
 "user_name": "*****",
 "email": "**********@example.com",
 "signup_date": "2023–01–15",
 "purchase_amount": 150.25
 },
 {
 "user_id": 2,
 "user_name": "*****",
 "email": "**********@example.com",
 "signup_date": "2023–02–20",
 "purchase_amount": 200.50
 }
]

3. Translation & Data Localization

As businesses expand globally, the ability to automatically translate and localize data assets is essential for effective communication across different regions and markets, ensuring consistent understanding and analysis of information worldwide.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Translate the following product descriptions to Spanish: [{"product_id": 1, "description": "High-quality leather wallet."}, {"product_id": 2, "description": "Comfortable cotton t-shirt."}].'
);

Sample Result:

[
 {
 "product_id": 1,
 "description": "Cartera de cuero de alta calidad."
 },
 {
 "product_id": 2,
 "description": "Camiseta de algodón cómoda."
 }
]

4. Data Model & Schema Generation

Streamlining the process of designing and implementing database structures saves time and effort. This acceleration in setting up new data systems or modifying existing ones ultimately speeds up project timelines.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Generate a database schema for an e-commerce platform with tables for users, products, orders, and reviews. Provide the schema in SQL format.'
);

Sample Result:

CREATE TABLE users (
 user_id INT PRIMARY KEY,
 user_name VARCHAR(255),
 email VARCHAR(255),
 signup_date DATE
);
CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(255),
 description TEXT,
 price DECIMAL(10, 2)
);
CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 user_id INT,
 order_date DATE,
 total_amount DECIMAL(10, 2),
 FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE reviews (
 review_id INT PRIMARY KEY,
 product_id INT,
 user_id INT,
 rating INT,
 comment TEXT,
 review_date DATE,
 FOREIGN KEY (product_id) REFERENCES products(product_id),
 FOREIGN KEY (user_id) REFERENCES users(user_id)
);

5. Automated Documentation

This is vital for enterprises to maintain up-to-date, comprehensive documentation of their data assets, improving knowledge sharing, compliance, and overall data governance while reducing the manual effort typically required for documentation.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Generate documentation for the following database schema: CREATE TABLE users (user_id INT PRIMARY KEY, user_name VARCHAR(255), email VARCHAR(255), signup_date DATE); CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(255), description TEXT, price DECIMAL(10, 2));'
);

Sample Result:

# Database Schema Documentation
## Users Table
- **user_id**: Integer, Primary Key. Unique identifier for each user.
- **user_name**: String, up to 255 characters. The name of the user.
- **email**: String, up to 255 characters. The email address of the user.
- **signup_date**: Date. The date when the user signed up.
## Products Table
- **product_id**: Integer, Primary Key. Unique identifier for each product.
- **product_name**: String, up to 255 characters. The name of the product.
- **description**: Text. Detailed description of the product.
- **price**: Decimal, up to 10 digits with 2 decimal places. The price of the product.

Bonus: Debugging and Troubleshooting

Advanced capabilities to quickly identify and resolve issues in complex data pipelines and SQL queries are indispensable. They minimize downtime and ensure the reliability of data infrastructure, keeping operations running smoothly.

Prompt:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
 'snowflake-arctic',
 'Analyze the following SQL query and error message. Provide potential causes and solutions:
Query:
SELECT u.user_name, o.order_date, p.product_name
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN "2023–01–01" AND "2023–12–31"
GROUP BY u.user_name, o.order_date, p.product_name
HAVING SUM(oi.quantity * p.price) > 1000;
Error:
SQL compilation error: Object "ORDER_ITEMS" does not exist or not authorized.'
);

Sample Result:

Analysis of the SQL query and error message:
1. Error Cause:
 The error message indicates that the table "ORDER_ITEMS" does not exist in the database or the user does not have permission to access it.
2. Potential Issues:
 a) Misspelling: The table name might be misspelled in the query.
 b) Missing Table: The ORDER_ITEMS table may not have been created in the database.
 c) Schema Issue: The table might exist in a different schema than the one being used.
 d) Permissions: The user executing the query may lack the necessary permissions to access the ORDER_ITEMS table.
3. Suggested Solutions:
 a) Double-check the spelling of "ORDER_ITEMS" in the query.
 b) Verify that the ORDER_ITEMS table exists in the database using SHOW TABLES command.
 c) If the table exists in a different schema, fully qualify the table name with the schema (e.g., "myschema.order_items").
 d) Check user permissions using SHOW GRANTS command and ensure the user has SELECT privileges on the ORDER_ITEMS table.
4. Query Optimization Suggestions:
 - Consider adding appropriate indexes on join columns (user_id, order_id, product_id) to improve query performance.
 - The BETWEEN clause with dates might benefit from a date index on the order_date column.
 - Evaluate if the GROUP BY clause is necessary, as it's currently grouping by all selected columns.
5. Additional Checks:
 - Ensure all other referenced tables (users, orders, products) exist and are accessible.
 - Verify that the date format in the WHERE clause matches your database's expected format.
To resolve the immediate error, focus on confirming the existence and accessibility of the ORDER_ITEMS table. Once resolved, consider the optimization suggestions for better query performance.

Hopefully, these examples illustrate how Snowflake Cortex isn’t just another tool or function to call — it’s a paradigm shift. Besides being handy for building RAG applications, Snowflake cortex can be utilized for various data engineering tasks, enhancing productivity and ensuring data quality and security. Use these as inspiration to craft your own prompts and go after problem areas for your data engineering teams.

Experiment with Snowflake Arctic, or any of the other models supported in Snowflake Cortex: like Snowflake Arctic, Mistral Large, Reka Flash, Mixtral-8x7B, or Llama3–70B.

In future postings, I’ll share other potential problems areas and example Snowflake Cortex prompts for Enterprises.

But, I would love to hear your thoughts on how Snowflake Cortex is transforming everyday workflows for data engineers or data engineering teams.

Jump into the comments below and share your thoughts.

[embed]COMPLETE (SNOWFLAKE.CORTEX) | Snowflake Documentation Given a prompt, generates a response (completion) using your choice of supported language model. Required: A string…docs.snowflake.com

Hello and welcome! Thrilled you’re here. We share insights on Data, AI, Tech trends, and the Future. Thank you for being a part of this community!

🙏 Before you go: If you found value in this post, please clap and follow to stay updated! 👏

Discover more at: **DemoHub.dev (Modern Data Tools) & DaaiC.dev (Data Analytics & AI Conferences), ***YouTube & *LinkedIn