monimiller

select
    c.custkey,
    c.name,
    c.address,
    OL.orderstatus,
    OL.orderdate,
    OL.shipdate,
    OL.shipinstruct,
 OL.shipmode

monimiller

CREATE TABLE customer (
  name varchar,
  address varchar)
WITH (type='iceberg');

monimiller

CREATE TABLE events (   
  id number,    
  day_id varchar,   
  other columns… )  
WITH (   
  type=’hive’,    
  format=’parquet’,    
  partitioned_by=array[’day_id’] 
);

monimiller

CREATE TABLE events ( 
  id number, 
  day_id varchar, 
  other columns … ) 
WITH ( 
  type = ‘delta’, 
  partitioned_by = array[’day_id’] 
);

monimiller

ALTER TABLE <GALAXY_DESTINATION_TABLE_NAME>
    EXECUTE OPTIMIZE
    WHERE CAST(<TIMESTAMP_PARTITIONING_COLUMN> AS DATE) >= CAST(now() - <SQL_TIME_INTERVAL> AS DATE);

-- Daily Example:
ALTER TABLE test_table
   EXECUTE OPTIMIZE
   WHERE CAST(timestamp AS DATE) >= CAST(now() - INTERVAL '2' DAY AS DATE);

monimiller

ALTER TABLE <GALAXY_DESTINATION_TABLE_NAME>
   EXECUTE expire_snapshots(retention_threshold => '7d');

-- Daily Example:
ALTER TABLE test_table
   EXECUTE expire_snapshots(retention_threshold => '7d');

monimiller

DELETE FROM <GALAXY_DESTINATION_TABLE_NAME>
    WHERE <TIMESTAMP_PARTITIONING_COLUMN> < date_trunc('day', now() AT TIME ZONE <TIMEZONE> - <SQL_TIME_INTERVAL>);

-- Daily Example:
DELETE FROM test_table
    WHERE timestamp < date_trunc('day', now() AT TIME ZONE 'UTC' - INTERVAL '30' DAY);

monimiller

ALTER TABLE <GALAXY_DESTINATION_TABLE_NAME>
    ADD COLUMN <NEW_COLUMN_NAME> <NEW_COLUMN_TYPE>;
    
-- Example:
ALTER TABLE test_table
    ADD COLUMN new_column VARCHAR;

monimiller

ALTER TABLE <GALAXY_DESTINATION_TABLE_NAME>
    DROP COLUMN <NEW_COLUMN_NAME>;
    
-- Example:
ALTER TABLE test_table
    DROP COLUMN new_column;

monimiller

create schema aws_covid_data_lake with (location='s3://dbt-aws-<username>/');