POST_START

Creating Tables from Query Results with CTAS

I recently needed to analyze customer data specific to Germany for a sales report. I had a table called training.sales.customers that contained customer information, including their country of residence. To isolate the German customers, I decided to create a new table directly from the query results using the CREATE TABLE ... AS SELECT (CTAS) statement.

Creating the German Customers Table

I started by writing a CTAS statement to create a new table named training.sales.german_customers that would contain all the records from the customers table where the country column was equal to ‘Germany’. This approach allowed me to create a new, filtered dataset without modifying the original table.

CREATE TABLE training.sales.german_customers AS SELECT * FROM training.sales.customers WHERE country = 'Germany';

I saw a representative result like this:

The command executed successfully, and the new table was created with the filtered data. This meant I could now work with a dedicated dataset for the German customers without affecting the original table.

Verifying the New Table

To make sure the table was created correctly, I ran a SELECT statement to retrieve some data from the training.sales.german_customers table. This helped me confirm that the query had been applied correctly and that the new table contained the expected rows.

SELECT * FROM training.sales.german_customers;

I saw a representative result like this:

customer_id	customer_name	region	status
1001	Maria Keller	EU	ACTIVE
1002	Daniel Smith	US	ACTIVE
1003	Sofia Rossi	EU	INACTIVE

Looking at the output, I noticed that the table contained only the records where the country was ‘Germany’, which confirmed that the CTAS statement had filtered the data correctly. This was exactly what I needed for my sales analysis.

Understanding the Table Structure

To ensure I understood the structure of the new table, I ran the DESCRIBE TABLE command to see the columns and their data types. This was important to make sure I could work with the data correctly in subsequent queries.

DESCRIBE TABLE training.sales.german_customers;

I saw a representative result like this:

col_name	data_type	comment
customer_id	bigint	customer identifier
customer_name	string	customer display name
region	string	sales region

The output showed that the new table had the same structure as the original customers table, which was expected. This meant I could proceed confidently with further analysis using the german_customers table.

By using the CTAS statement, I was able to quickly create a new table with the data I needed, saving time and ensuring data integrity. This approach is especially useful when you need to work with a subset of data without altering the original dataset.