CREATE TABLE and Data Types

A CREATE TABLE is a contract

Every table in a warehouse began as a CREATE TABLE someone wrote. That statement fixes the columns, their types, and the value the engine fills in for a column a load leaves unset. Downstream models, dashboards, and joins all inherit whatever shape you declare here, so a wrong type or a missing default leaks into a hundred queries. This lesson is only about defining that structure; the next one loads rows into it.

Reading the declaration

Each line is column_name TYPE [DEFAULT expr]. The type documents intent. DEFAULT declares the value the engine substitutes for a column a later load does not supply, so partial rows still land complete. DROP TABLE IF EXISTS stg_customer; at the top of a script makes it re-runnable: a second run drops the old table instead of erroring on "table already exists."

Worked example: defining a staging table

A staging table is just a typed landing zone you define before any data arrives. Here is the DDL for one:

DROP TABLE IF EXISTS stg_customer;

CREATE TABLE stg_customer (
    customer_id   INTEGER,
    email         TEXT,
    country_code  TEXT,
    signup_date   TEXT,               -- ISO-8601 'YYYY-MM-DD'
    is_active     INTEGER DEFAULT 1,  -- 0/1 boolean-as-int
    loaded_at     TEXT DEFAULT (datetime('now'))
);

Read it top to bottom: six columns, each with a declared type, and two of them carry a DEFAULT. is_active defaults to the integer 1; loaded_at defaults to the timestamp at the moment a row is written.

Note the parentheses in DEFAULT (datetime('now')). A function-call default must be wrapped in (); writing DEFAULT datetime('now') is a syntax error. Plain literals need no parentheses, and neither do the bare time keywords CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME: they look like function calls but the grammar accepts them without.

Declaring defaults: literals, strings, and expressions

• Numbers: INTEGER DEFAULT 0, INTEGER DEFAULT 1.
• Strings: TEXT DEFAULT 'pending' (string-literal defaults go in single quotes).
• Expressions and functions: wrapped in parentheses, TEXT DEFAULT (datetime('now')).

A declared default is a promise the schema keeps, so a load never has to remember to supply that column.

Type affinity is advisory in SQLite

SQLite type affinity is only advisory: it will happily store the text 'oops' in an INTEGER column, while Postgres, Snowflake, and BigQuery reject it. Declare the intended type anyway. Your DDL is documentation and should port to a strict engine unchanged.

Interview nuance: a DEFAULT is about substitution, not presence. Declaring is_active INTEGER DEFAULT 1 supplies a value when a load omits the column, but it does not force the column to always hold one. Making a column mandatory is a separate NOT NULL constraint, which you will pair with DEFAULT in the constraints lesson later this level. Interviewers ask this to check that you treat substitution and required-ness as two independent guarantees.

Execution mode: you write a multi-statement DDL script. It runs against a fresh in-memory SQLite database, then hidden assertion queries inspect the schema you defined: the columns, their declared types, and their declared defaults.