Partly due to a multi-year company-wide effort to get the most out of our SFDC investment, I've been spending a good deal of time getting data from SFDC, through Alteryx, and ultimately into dashboards via Tableau.
My experiences with SFDC as a source of data have been quite varied, but overall trend positive. On the upside, it is a versatile front-end instrument for all our sales efforts, and leveraging that data is a boon for any sales oriented tasks. What I can acquire by querying SFDC is what salespeople, account managers, and executives create, alter, and act upon. That's awesome!
Further, the design of SFDC's standard table structure is quite intuitive, with primary keys and secondary keys in ready supply; in most situations, I can reliably infer what keys are used to traverse tables, particularly when I'm able to stick within the standard tables.
Spirit Guides are really just Subject Matter Experts equipped with tribal knowledge!
A key hurdle to integration of SFDC data are our custom tables and the custom fields within the standard tables. From my experiences, there are a lot of cooks in our SFDC kitchen. Not present in our implementation are the strict governance requirements often found in conventional EDW databases. As a result, a wild-west environment tends to flourish given that, you may find yourself in need of a spirit guide.
For example, obsolete fields must be distinguished from their replacements. In one particularly humbling instance, two attributes existed, literally Program_Status_Account and Status_Program_Account. Only one was appropriate and it was not intuitive which one was the correct instrumentation. In other cases, you may find some data is shaped differently, due to prior requirements, or an ETL gone awry.
In the best cases, your observations will shed light on these issues: null records can be populated, munged data reloaded, obsolete attributes deprecated.
A query that pulls a count of registered accounts and a count of purchasing accounts in the current fiscal year.
From my perspective, this task is made more tricky due to a lack of transparency when viewing front-end facing reports. There are API names that don't align to their front end counterparts, for example. I suspect there are folks who can pull reporting that aligns API fields to front end fields, but I have yet to meet those privileged admins. Fortunately for me, STX has a stable of really talented analysts who are experts on SFDC for me to collaborate with. I help them synthesize and automate their data with other data sets, and they keep me informed on the SFDC goings on.