Ingesting with CTAS, CREATE OR REPLACE TABLE, and COPY INTO

SQL-based ingestion in Databricks centers on CTAS, CREATE OR REPLACE TABLE (CRAS), and COPY INTO, each suited to different loading patterns. CTAS and CRAS create Delta tables from query results with inferred schemas, while COPY INTO idempotently and incrementally loads files from cloud storage into an existing Delta table.

1 · Learn the must-know

• 1CTAS (CREATE TABLE AS SELECT) creates a new managed Delta table and populates it in one step, automatically inferring the schema from the query results, so column comments, constraints, and explicit data types cannot be set inline.
• 2CREATE OR REPLACE TABLE (often paired with AS SELECT, i.e., CRAS) fully drops and recreates the table definition and data, allowing schema changes such as altering column types or adding partitioning, unlike INSERT OVERWRITE which preserves the existing schema/history.
• 3COPY INTO requires the target Delta table to already exist and is used to incrementally and idempotently load new files from a cloud storage location, automatically tracking previously ingested files so re-running the command does not duplicate data.
• 4COPY INTO supports specifying file format options (e.g., header, delimiter, inferSchema) and works well for repeatable, scheduled ingestion jobs, whereas CTAS/CRAS are typically used for one-time or full-refresh table creation from a query.
• 5Both CTAS and CREATE OR REPLACE TABLE support Delta Lake features like partitioning and table properties via clauses (e.g., PARTITIONED BY, TBLPROPERTIES) added to the statement.
• 6CREATE OR REPLACE TABLE breaks table lineage for time travel purposes at the point of replacement (a new version is created), so historical versions before the replace remain in Delta history but the table's schema/state resets going forward.

2 · Check your understanding