Week 94 – Intermediate

As fans of dbt , we’re quite happy with the new Jinja2 template support for EXECUTE IMMEDIATE FROM !

This time our intern got the following assignement :
Create a sql script that using Jinja2 templating that does the following based on my input :

• Deployment Differentiation:
  • Dynamically adapts to different deployment types (prod and non-prod).
  • Uses distinct environment names (prod1, prod2 for production and dev, qa, staging for non-production).
• Schema Creation:
  • Creates specific schemas (finance, sales, hr for production and development, testing, support for non-production).
• Database Setup:
  • Creates databases named according to the environment ({{ environment }}_db).
• Schema-Specific Table Creation:
  • Creates multiple tables within each schema according to the environment ({{ environment }}_{{schema}}_employees for example):
    • orders table with columns id, item, quantity, and order_date.
    • customers table with columns id, name, and email.
    • products table with columns product_id, product_name, and price.
    • employees table with columns employee_id, employee_name, and position
• Production-Specific Setup:
  • Creates a separate prod_analytics_db database.
  • Within this database, creates an analytics_reports table with columns report_id, report_name, and created_date.

Professor Frosties input should be similar to the following :

EXECUTE IMMEDIATE FROM @setup_stage/setup_env_made_by_intern.sql
USING (DEPLOYMENT_TYPE => ‘prod’);