Dwh.dev documentation
  • What is Dwh.dev?
  • Features
    • Data catalog
      • PIPEs
      • Fancy SQL Highlight
    • Data Lineage
      • Object-level lineage
      • Column-level Lineage
      • In-query lineage
      • Navigation
      • JOIN and WHERE
      • Equals column lineage
      • Strong and Weak dependencies
      • Default and Virtual Columns
      • Implicit Types casting
      • Circle dependencies
      • Argument forwarding
    • TASKs
  • Integrations
    • Snowflake
      • Offline mode
      • Secured online mode
      • Snowflake Marketplace
    • DBT
    • ETL
    • BI
      • Looker
      • Other
    • API
  • Snowflake SQL Syntax and Behavior
    • Identifiers
    • Reusing column aliases
    • SELECT * ILIKE EXCLUDE REPLACE RENAME
    • Scalar functions name resolution special behavior
    • Functions overloading
    • CTE as an expression alias
    • ASOF Join
    • UDF named arguments
    • Objects auto renaming
    • Columns auto renaming
Powered by GitBook
On this page

Was this helpful?

  1. Snowflake SQL Syntax and Behavior

ASOF Join

PreviousCTE as an expression aliasNextUDF named arguments

Last updated 8 months ago

Was this helpful?

Perhaps not everyone knows, but Snowflake now has a new type of connection with an additional condition - .

An ASOF JOIN operation combines rows from two tables based on timestamp values that follow each other, precede each other, or match exactly. For each row in the first (or left) table, the join finds a single row in the second (or right) table that has the closest timestamp value. The qualifying row on the right side is the closest match, which could be equal in time, earlier in time, or later in time, depending on the specified comparison operator.

SELECT l.c1 as a, l.c4 as b, r.c4 as c
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
    ON(l.c1=r.c1 and l.c2=r.c2)
  ORDER BY l.c1, l.c2

The MATCH_CONDITION is now displayed next to the ON condition in the . All columnar relationships are highlighted on click:

ASOF JOIN
in-query lineage