Snowflake stores names you write without quotes in upper case. orders, Orders and ORDERS all mean the same table, and an alias like AS total comes back as TOTAL.
Double quotes make a name exact. A tool that created a table as "vendor_feed" made a lowercase name, and you have to write "vendor_feed", quotes and all, every time. Without quotes, Snowflake looks for VENDOR_FEED and reports that the object doesn't exist. The same goes for columns: "sku" isn't SKU, and a name with a space, like "Unit Cost", always needs quotes.
That's names. Data is different: text values in single quotes are compared exactly, so 'tent' doesn't match 'Tent'. To compare text in any case, use ILIKE, Snowflake's case-insensitive LIKE, or put both sides through LOWER().