When you add the static CSV with Add Data, the CSV data will be stored in specified index and would be tied to the timestamp of when it was indexed OR whatever timestamp field you've chosed (or Splunk found it). So, in order to search that data, you'll need to specify appropriate time range. Since it's indexed data, it'll cost Splunk license when ingested and its retention period will be bound to the retention period of the index.
When you add the CSV as lookup, it's still is a static csv data in Splunk and doesn't get indexed (so not license usage OR retention), or is timerange bound. It's a literal csv file available on all Search Head servers (assuming you use SH clustering OR use deployment server to push that lookup, else it'll only be available on the node you uploaded it). Please note that all lookups are part of knowledge bundle which SH sends to it's search peers (indexers), so if your static file is very large, you may see replication failures and errors during search.
Also, the way to you access the data will be different too (regular search vs lookup specific commands).
