A unit test case for SQL transformations, see dbt-labs/dbt-core#2354

ddl.sql

# file: 000_basic_order/input/ddl.sql

create table source_order.sales_order (
  id int,
  placed timestamp,
  updated timestamp
);

create table source_order.sales_order_item (
  id int,
  order_id int,
  pc_product_id int,
  created timestamp
);

# ... and so on

reporting.dim_order.yml

# file: 000_basic_order/expected_output/reporting.dim_order.yml

# This file contains assertions about records in reporting.dim_order

- sales_order_id: 1000

  placed: 2020-01-01 12:00:00
  updated: 2020-01-02 12:00:00

- sales_order_id: 2000

  placed: 2020-01-02 12:00:00
  updated: 2020-01-02 12:00:00

reporting.dim_order_item.yml

# file: 000_basic_order/expected_output/reporting.dim_order_item.yml

# This file contains assertions about records in reporting.dim_order_item

- sales_order_id: 1000
  sales_order_item_id: 1003

  created: 2020-01-01 12:00:00
  updated: 2020-01-02 12:00:00
  placed: 2020-01-01 12:00:00

- sales_order_id: 2000

  created: 2020-01-01 12:00:00
  updated: 2020-01-02 12:00:00
  placed: 2020-01-02 12:00:00

source_order_1000.yml

# file: 000_basic_order/input/source_order_1000.yml

#
# Scenario:
# - This order was placed on 2020-01-01 12:00:00.
# - There was an dim_order record created that night.
# - The order was updated on 2020-01-02 12:00:00.
#
# Expectation:
# - The dim_order record is updated after SQL transformation is run.
#

# Transactional system's order
source_order.order:
  - id: 1000
    placed: 2020-01-01 12:00:00
    updated: 2020-01-02 12:00:00


# Transactional system's order item
source_order.sales_order_item:
  - id: 1002
    order_id: 1000
    pc_product_id: 1002
    created: 2020-01-01 12:00:00


# Reporting system's existing order record
reporting.dim_order:
  - order_id: 1000
    placed: 2020-01-01 12:00:00
    updated: 2020-01-01 12:00:00


# Reporting system's existing order item record
reporting.dim_order_item
  - sales_order_id: 1000
    sales_order_item_id: 1001
    created: 2020-01-01 12:00:00
    updated: 2020-01-01 12:00:00
    placed: 2020-01-01 12:00:00

reporting.dim_product:
  - pc_product_id: 1002

source_order_2000.yml

# file: 000_basic_order/input/source_order_2000.yml

#
# Scenario:
# - This order was placed on 2020-01-02 12:00:00.
#
# Expectation:
# - There was an reporting.dim_order record created after SQL transformation.
# - There was an reporting.dim_order_item record created after SQL transformation.
#

# Transactional system's order
source_order.sales_order:
  - id: 2000
    placed: 2020-01-02 12:00:00
    updated: 2020-01-02 12:00:00

# Transactional system's order
source_order.sales_order_item:
  - id: 2001
    order_id: 2000
    pc_product_id: 2002
    created: 2020-01-01 12:00:00

reporting.dim_product:
  - pc_product_id: 2002